Diferència entre revisions de la pàgina «Qüestionari: Creació d'índex. Optimització de consultes»
Salta a la navegació
Salta a la cerca
(Es crea la pàgina amb «<pre> sakila.film mysql> SHOW INDEX FROM film; | film | 1 | idx_title | 1 | title | film | 1 | idx_fk_language_id...».) |
|||
| (Hi ha 2 revisions intermèdies del mateix usuari que no es mostren) | |||
| Línia 1: | Línia 1: | ||
| + | __TOC__ | ||
| + | =Introducció= | ||
| + | A la part teòrica hem treballat sobre la taula ''municipis'' de la base de dades ''municipis''. Ara treballarem sobre la base de dades ''sakila'', i les taules ''film'', ''category'' i ''film_category''. Evidentment, és una relació N:M, on una peli pot tenir vàries categories, i una categoria té moltes pelis. | ||
| + | =Índex= | ||
| + | Mirem els índex que hi ha definits sobre ''film'', ''film_category'' i ''category''. Trobarem els índex que s'han creat de forma automàtica sobre les claus primàries i sobre les claus forànees. | ||
<pre> | <pre> | ||
| − | |||
| − | |||
mysql> SHOW INDEX FROM film; | mysql> SHOW INDEX FROM film; | ||
| film | 1 | idx_title | 1 | title | | film | 1 | idx_title | 1 | title | ||
| film | 1 | idx_fk_language_id | 1 | language_id | | film | 1 | idx_fk_language_id | 1 | language_id | ||
| − | + | </pre> | |
| + | <pre> | ||
mysql> SHOW INDEX FROM film_category; | mysql> SHOW INDEX FROM film_category; | ||
+---------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | +---------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | ||
| Línia 14: | Línia 18: | ||
| film_category | 0 | PRIMARY | 2 | category_id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | | film_category | 0 | PRIMARY | 2 | category_id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | ||
| film_category | 1 | fk_film_category_category | 1 | category_id | A | 16 | NULL | NULL | | BTREE | | | YES | NULL | | | film_category | 1 | fk_film_category_category | 1 | category_id | A | 16 | NULL | NULL | | BTREE | | | YES | NULL | | ||
| − | + | </pre> | |
| + | <pre> | ||
mysql> SHOW INDEX FROM category; | mysql> SHOW INDEX FROM category; | ||
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | ||
| Línia 20: | Línia 25: | ||
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | ||
| category | 0 | PRIMARY | 1 | category_id | A | 16 | NULL | NULL | | BTREE | | | YES | NULL | | | category | 0 | PRIMARY | 1 | category_id | A | 16 | NULL | NULL | | BTREE | | | YES | NULL | | ||
| + | </pre> | ||
| − | + | Volem fer la consulta: llistar de títols de pel·lícules i categories. Són dos inner joins: | |
| − | + | <pre> | |
| − | |||
| − | |||
mysql> select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id) limit 10; | mysql> select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id) limit 10; | ||
+---------------------+--------+ | +---------------------+--------+ | ||
| Línia 40: | Línia 44: | ||
| CAMPUS REMEMBER | Action | | | CAMPUS REMEMBER | Action | | ||
... | ... | ||
| − | + | </pre> | |
| − | + | I aquesta consulta s'executa prou ràpid perquè hi ha els índex ben definits (que es van crear de forma automàtica): | |
| + | <pre> | ||
EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id); | EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id); | ||
| Línia 49: | Línia 54: | ||
-> Index lookup on fc using fk_film_category_category (category_id=c.category_id) (cost=0.65 rows=62) (actual time=0.020..0.043 rows=62 loops=16) | -> Index lookup on fc using fk_film_category_category (category_id=c.category_id) (cost=0.65 rows=62) (actual time=0.020..0.043 rows=62 loops=16) | ||
-> Single-row index lookup on f using PRIMARY (film_id=fc.film_id) (cost=0.25 rows=1) (actual time=0.002..0.003 rows=1 loops=1000) | -> Single-row index lookup on f using PRIMARY (film_id=fc.film_id) (cost=0.25 rows=1) (actual time=0.002..0.003 rows=1 loops=1000) | ||
| − | + | </pre> | |
| + | Aquesta consulta internament s'executa amb 5 passos, i sumant el temps tota dóna: | ||
| + | <pre> | ||
total: 3.931 + 0.904 + 0.047 + 0.043 + 0.003 = 4,928 ms | total: 3.931 + 0.904 + 0.047 + 0.043 + 0.003 = 4,928 ms | ||
| + | </pre> | ||
| − | ens | + | Ara ens carregarem l'índex ''fk_film_category_category'' de la taula ''film_category'', que és la clau forànea que apunta a ''category''. Per carregar-nos l'index abans ens hem de carregar la foreign key. Per tant, hem d'executar aquestes dues instruccions: |
| − | + | <pre> | |
| − | + | ALTER TABLE film_category drop foreign key fk_film_category_category; | |
| − | |||
| − | |||
DROP INDEX fk_film_category_category ON film_category; | DROP INDEX fk_film_category_category ON film_category; | ||
| − | + | </pre> | |
| + | Per veure que ja no existeix ''fk_film_category_category'' tornem a executar: | ||
| + | <pre> | ||
| + | mysql> SHOW INDEX FROM film_category; | ||
| + | </pre> | ||
| + | L'únic objectiu que cerquem és veure com ara la consulta que hem fet abans triga més temps: | ||
| + | <pre> | ||
EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id); | EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id); | ||
| Línia 66: | Línia 78: | ||
-> Single-row index lookup on c using PRIMARY (category_id=fc.category_id) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=1000) | -> Single-row index lookup on c using PRIMARY (category_id=fc.category_id) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=1000) | ||
-> Single-row index lookup on f using PRIMARY (film_id=fc.film_id) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=1000) | -> Single-row index lookup on f using PRIMARY (film_id=fc.film_id) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=1000) | ||
| − | + | </pre> | |
| + | Són 7,6 ms: | ||
| + | <pre> | ||
4,845 + 2,353 + 0,490 + 0,001 + 0,002 = 7,691 | 4,845 + 2,353 + 0,490 + 0,001 + 0,002 = 7,691 | ||
| + | </pre> | ||
| + | No és una diferència molt gran, però efectivament ara el temps per realitzar els dos inner joins és més costós. | ||
| − | + | Per deixar-ho com ho teníem al principi, tornem a restaurar les restriccions: (creem la clau forànea, i al mateix temps es crea l'index) | |
| − | + | <pre> | |
ALTER TABLE film_category ADD CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category(category_id); | ALTER TABLE film_category ADD CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category(category_id); | ||
</pre> | </pre> | ||
| + | |||
| + | =Taques a realitzar. Entrega= | ||
| + | Executa les anteriors comandes pas a pas, entenent bé el que fas. | ||
| + | |||
| + | Has d'apuntar els valors de temps i la seva suma, tal com et surten a tu. | ||
| + | |||
| + | Entrega un pdf amb les captures de pantalla i comentaris oportuns, demostrant que ho has realitzat de forma individual i en la teva màquina. | ||
{{Autor}}, febrer 2022 | {{Autor}}, febrer 2022 | ||
Revisió de 20:08, 14 feb 2022
Introducció
A la part teòrica hem treballat sobre la taula municipis de la base de dades municipis. Ara treballarem sobre la base de dades sakila, i les taules film, category i film_category. Evidentment, és una relació N:M, on una peli pot tenir vàries categories, i una categoria té moltes pelis.
Índex
Mirem els índex que hi ha definits sobre film, film_category i category. Trobarem els índex que s'han creat de forma automàtica sobre les claus primàries i sobre les claus forànees.
mysql> SHOW INDEX FROM film; | film | 1 | idx_title | 1 | title | film | 1 | idx_fk_language_id | 1 | language_id
mysql> SHOW INDEX FROM film_category; +---------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +---------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | film_category | 0 | PRIMARY | 1 | film_id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | film_category | 0 | PRIMARY | 2 | category_id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL | | film_category | 1 | fk_film_category_category | 1 | category_id | A | 16 | NULL | NULL | | BTREE | | | YES | NULL |
mysql> SHOW INDEX FROM category; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | category | 0 | PRIMARY | 1 | category_id | A | 16 | NULL | NULL | | BTREE | | | YES | NULL |
Volem fer la consulta: llistar de títols de pel·lícules i categories. Són dos inner joins:
mysql> select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id) limit 10; +---------------------+--------+ | title | name | +---------------------+--------+ | AMADEUS HOLY | Action | | AMERICAN CIRCUS | Action | | ANTITRUST TOMATOES | Action | | ARK RIDGEMONT | Action | | BAREFOOT MANCHURIAN | Action | | BERETS AGENT | Action | | BRIDE INTRIGUE | Action | | BULL SHAWSHANK | Action | | CADDYSHACK JEDI | Action | | CAMPUS REMEMBER | Action | ...
I aquesta consulta s'executa prou ràpid perquè hi ha els índex ben definits (que es van crear de forma automàtica):
EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id);
| -> Nested loop inner join (cost=455.97 rows=1000) (actual time=0.093..3.931 rows=1000 loops=1)
-> Nested loop inner join (cost=105.97 rows=1000) (actual time=0.079..0.904 rows=1000 loops=1)
-> Table scan on c (cost=1.85 rows=16) (actual time=0.037..0.047 rows=16 loops=1)
-> Index lookup on fc using fk_film_category_category (category_id=c.category_id) (cost=0.65 rows=62) (actual time=0.020..0.043 rows=62 loops=16)
-> Single-row index lookup on f using PRIMARY (film_id=fc.film_id) (cost=0.25 rows=1) (actual time=0.002..0.003 rows=1 loops=1000)
Aquesta consulta internament s'executa amb 5 passos, i sumant el temps tota dóna:
total: 3.931 + 0.904 + 0.047 + 0.043 + 0.003 = 4,928 ms
Ara ens carregarem l'índex fk_film_category_category de la taula film_category, que és la clau forànea que apunta a category. Per carregar-nos l'index abans ens hem de carregar la foreign key. Per tant, hem d'executar aquestes dues instruccions:
ALTER TABLE film_category drop foreign key fk_film_category_category; DROP INDEX fk_film_category_category ON film_category;
Per veure que ja no existeix fk_film_category_category tornem a executar:
mysql> SHOW INDEX FROM film_category;
L'únic objectiu que cerquem és veure com ara la consulta que hem fet abans triga més temps:
EXPLAIN ANALYZE select title, name from film f inner join film_category fc using (film_id) inner join category c using (category_id);
| -> Nested loop inner join (cost=801.00 rows=1000) (actual time=0.079..4.845 rows=1000 loops=1)
-> Nested loop inner join (cost=451.00 rows=1000) (actual time=0.069..2.353 rows=1000 loops=1)
-> Index scan on fc using PRIMARY (cost=101.00 rows=1000) (actual time=0.056..0.490 rows=1000 loops=1)
-> Single-row index lookup on c using PRIMARY (category_id=fc.category_id) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=1000)
-> Single-row index lookup on f using PRIMARY (film_id=fc.film_id) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=1000)
Són 7,6 ms:
4,845 + 2,353 + 0,490 + 0,001 + 0,002 = 7,691
No és una diferència molt gran, però efectivament ara el temps per realitzar els dos inner joins és més costós.
Per deixar-ho com ho teníem al principi, tornem a restaurar les restriccions: (creem la clau forànea, i al mateix temps es crea l'index)
ALTER TABLE film_category ADD CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category(category_id);
Taques a realitzar. Entrega
Executa les anteriors comandes pas a pas, entenent bé el que fas.
Has d'apuntar els valors de temps i la seva suma, tal com et surten a tu.
Entrega un pdf amb les captures de pantalla i comentaris oportuns, demostrant que ho has realitzat de forma individual i en la teva màquina.
creat per Joan Quintana Compte, febrer 2022
