Оптимизиране на заявки

[Avalanche]

За тия почти 6 години не си спомням темата да е обсъждана тука, и все някакси се остава на заден план. Ще използвам заявките, които направихме от темата с последната задача. Променил съм имената на таблиците и полетата да са с малки букви, че заявките са трудни за четене:

DATABASE SCHEMA

Код: MySQL

1. CREATE TABLE `gamers` (
2.   `id` INT(11) NOT NULL,
3.   `gamerid` INT(11) NOT NULL,
4.   `firstname` VARCHAR(45) DEFAULT NULL,
5.   `lastname` VARCHAR(45) DEFAULT NULL,
6.   PRIMARY KEY (`id`)
7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
8.  
9. CREATE TABLE `points` (
10.   `id` INT(11) NOT NULL,
11.   `gamerid` INT(11) NOT NULL,
12.   `week` INT(11) NOT NULL,
13.   `score` INT(11) NOT NULL,
14.   PRIMARY KEY (`id`)
15. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
16.  
17. // INSERT DATA
18.  
19. INSERT INTO `points` VALUES (1,1,1,4),(2,2,1,1),(3,3,1,3),(4,1,2,0),(5,2,2,4),(6,3,2,1),(7,3,3,1),(8,2,3,5),(9,1,3,2),(10,2,4,2),(11,1,4,3),(12,3,4,0);
20.  
21. INSERT INTO `gamers` VALUES (1,1,'jazzman','jazz'),(2,2,'canoncho','canon'),(3,3,'Avalanche','Ava'),(4,4,'HD','HD'),(5,1,'jazzman','jazz');

Условието на задачата може да се свери в темата на задачката, тука искам да обсъдим оптимизацията на заявките. Искам да сравним тези на Jazzman и моята (заявката на канона е идентична с моята, нека да каже ако иска да я включим и нея). Те правят едно и също нещо, но по различни начини:

Заявка 1 на Джази-Бази:

Код: MySQL

1. SELECT
2.     g.firstname,
3.     g.lastname,
4.     p.gamerid,
5.     p.week,
6.     p.scoreweek,
7.     tot.scoreTOTAL
8. FROM gamers g
9. INNER JOIN (
10.     SELECT gamerid, week, SUM(score) AS scoreweek
11.     FROM points
12.     WHERE week = (
13.         SELECT MAX(week)
14.         FROM points
15.     )
16. GROUP BY gamerid) AS p ON (g.gamerid=p.gamerid)
17. INNER JOIN (
18.     SELECT
19.         gamerid,
20.         SUM(score) AS scoreTOTAL
21.     FROM points
22.     GROUP BY gamerid
23.     ) AS tot ON (tot.gamerid = p.gamerid)
24. GROUP BY gamerid
25. ORDER BY scoreTOTAL DESC;

Заявка 2 - моята:

Код: MySQL

1. SELECT
2.     g.firstname,
3.     g.lastname,
4.     g.gamerid,
5.     p.week,
6.     SUM(score) AS ScoreWeek,
7.     (
8.         SELECT SUM(score)
9.         FROM points
10.         WHERE gamerid = g.gamerid
11.     ) AS ScoreMonth
12. FROM gamers AS g
13. LEFT JOIN points AS p
14.     ON g.gamerid = p.gamerid
15. WHERE week = (
16.     SELECT MAX(week)
17.     FROM points
18. )
19. GROUP BY firstname
20. ORDER BY ScoreMonth DESC;

Как се оптимизира заявка? Който е бил на интервю за работа на позиция PHP Backend или нещо от сорта е чувал този въпрос (ако не сте - пропуснали сте). Са някои ще кажат, "да се изброят полетата вместо да се вземат със звездичка", други ще кажат "сложи индекс". Но как и къде да ги направя тия неща? Ами MySQL предлага командата EXPLAIN, с чиято помощ може да получим информация какво трябва да направи query optimizer-а за да вземе информацията, която искаме. Доста информация вади командата, темата няма да ми стигне да обесня всичко, но пък и не съм го наизустил и често се налага да търся референция, ето и какво излиза за заявката на Jaza:

Код: MySQL

1. MariaDB [meow]> EXPLAIN SELECT     g.firstname,     g.lastname,     p.gamerid,     p.week,     p.scoreweek,     tot.scoreTOTAL FROM gamers g INNER JOIN (     SELECT gamerid, week, SUM(score) AS scoreweek     FROM points     WHERE week = (         SELECT MAX(week)         FROM points     ) GROUP BY gamerid) AS p ON (g.gamerid=p.gamerid) INNER JOIN (     SELECT         gamerid,         SUM(score) AS scoreTOTAL     FROM points     GROUP BY gamerid     ) AS tot ON (tot.gamerid = p.gamerid)  GROUP BY gamerid ORDER BY scoreTOTAL DESC;
2. +------+-------------+------------+------+---------------+------+---------+------+------+--------------------------------------------------------+
3. | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
4. +------+-------------+------------+------+---------------+------+---------+------+------+--------------------------------------------------------+
5. |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary; Using filesort                        |
6. |    1 | PRIMARY     | <derived4> | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer (flat, BNL join)        |
7. |    1 | PRIMARY     | g          | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer (incremental, BNL join) |
8. |    4 | DERIVED     | points     | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using temporary; Using filesort                        |
9. |    2 | DERIVED     | points     | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where; Using temporary; Using filesort           |
10. |    3 | SUBQUERY    | points     | ALL  | NULL          | NULL | NULL    | NULL |    1 |                                                        |
11. +------+-------------+------------+------+---------------+------+---------+------+------+--------------------------------------------------------+
12. 6 rows in set (0.01 sec)
13.  

Накратко - имаме списък от какви отделни заявки трябва да се изпълнят за да се сглоби резултата:
id - номер на заявката
select_type - тип на заявката
table - името на таблицата от където се взимат редовете
type - как са join-ати таблиците join type (ALL е най-лошия вариант - ще се прави пълно сканиране на таблицата)
possible_keys - кои индекси може да се ползват за да се открият редовете в таблицата
key - кои индекс query optimizer-а ще използва от всичките възможни от possible_keys
key_len - http://stackoverflow.com/a/7714306/1125161
rows - броя редове, които MySQL предполага че трябва да сканира за да изпълни заявката
extra - допълнителна информация как MySQL ще обработи заявката - обяснение на стойностите

Нека да разгледаме стойностите от extra полето. А те са доста тревожни в случая:
Using temporary това значи че MySQL ще трябва да направи допълнителна временна таблица в която да запише резултата на твърдия диск, което по дефиниция отнема време.
Using filesort означава че MySQL ще трябва да направи допълнително сортиране като мине отново през всички записи - в случая има малко записи, но в таблица с хиляди записи това е ще отнеме доста време.
Using where е точно това, което гласи и името му - в заявката се използва WHERE клауза. Кофтито тука е когато join_type-а е ALL и няма индекс, който да се ползва - пак ще се сканира цялата таблица.
Using join buffer (flat, BNL join) гледам че е алгоритъм на MariaDB, в MySQL най-вероятно ще има друга стойност (нямам в момента друга база да тествам). Но накратко изглежда в този случаи се използват редове записани в буфера при предишен join.


Накратко е това, надявам се да съм събудил интерес поне у някои от вас. Поиграите си с таблиците, слагайте индекси и вижде как се променя изхода от EXPLAIN командата, ако има желаещи може да продължим темата.

[jazzman]

Типа е най-важен за мен.  Играл съм си много и големи успехи и бързина не съм постигнал.  Сложете по един индекс на gamerid, понеже join-a е върху този key и ползвайте инаги inner join когато може, вместо right или left.

[jazzman]

Ето и как трябва да бъде заявката според sql standart-a от 92 г ( за друг не знам) . Поствам заявката от Firebird.

Код: SQL

1. SELECT g.FIRSTNAME, g.LASTNAME, g.GAMERID, p.WEEK, p.SCOREWEEK, tot.TOTAL FROM GAMERS g
2. JOIN (
3. SELECT GAMERID, WEEK, SUM(SCORE) AS SCOREWEEK FROM POINTS
4. WHERE WEEK = (
5. SELECT MAX(WEEK) FROM POINTS)
6. GROUP BY GAMERID, WEEK) AS p ON (g.GAMERID=p.GAMERID)
7. JOIN (
8. SELECT GAMERID, SUM(SCORE) AS TOTAL
9. FROM POINTS
10. GROUP BY GAMERID) AS tot ON (tot.GAMERID = g.GAMERID)
11. GROUP BY FIRSTNAME, LASTNAME, GAMERID, WEEK, SCOREWEEK, TOTAL
12. ORDER BY TOTAL DESC
13.  

Когато се ползват sql aggregate functions като sum, count, max, min и т.н заедно с колона от друг тип, винаги трябва да има group by. В противен случай базата няма да знае, върху коя точно колона да групира резултатите от агрегатните функции. Разбира се, много бази кaто MySQL, DB 2 и други частично групират резултата и парсъра не извежда грешки.