Qüestionari: Creació d'índex. Optimització de consultes

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. 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