Автор Тема: Оптимизиране на заявки  (Прочетена 2950 пъти)

0 Потребители и 1 Гост преглежда(т) тази тема.

Avalanche

  • Administrator
  • Hero Member
  • *****
  • Благодарности
  • -Казани: 65
  • -Получени: 275
  • Публикации: 2602
  • meow
Оптимизиране на заявки
« -: 08 Юли 2015, 23:32:02 »
За тия почти 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.  
  8. CREATE TABLE `points` (
  9.   `id` INT(11) NOT NULL,
  10.   `gamerid` INT(11) NOT NULL,
  11.   `week` INT(11) NOT NULL,
  12.   `score` INT(11) NOT NULL,
  13.   PRIMARY KEY (`id`)
  14.  
  15.  
  16. 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);
  17.  
  18. 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.     g.firstname,
  2.     g.lastname,
  3.     p.gamerid,
  4.     p.week,
  5.     p.scoreweek,
  6.     tot.scoreTOTAL
  7. FROM gamers g
  8.     SELECT gamerid, week, SUM(score) AS scoreweek
  9.     FROM points
  10.     WHERE week = (
  11.         SELECT MAX(week)
  12.         FROM points
  13.     )
  14. GROUP BY gamerid) AS p ON (g.gamerid=p.gamerid)
  15.     SELECT
  16.         gamerid,
  17.         SUM(score) AS scoreTOTAL
  18.     FROM points
  19.     GROUP BY gamerid
  20.     ) AS tot ON (tot.gamerid = p.gamerid)
  21. GROUP BY gamerid
  22. ORDER BY scoreTOTAL DESC;

Заявка 2 - моята:
Код: MySQL
  1.     g.firstname,
  2.     g.lastname,
  3.     g.gamerid,
  4.     p.week,
  5.     SUM(score) AS ScoreWeek,
  6.     (
  7.         SELECT SUM(score)
  8.         FROM points
  9.         WHERE gamerid = g.gamerid
  10.     ) AS ScoreMonth
  11. FROM gamers AS g
  12. LEFT JOIN points AS p
  13.     ON g.gamerid = p.gamerid
  14.     SELECT MAX(week)
  15.     FROM points
  16. )
  17. GROUP BY firstname
  18. 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

  • Hero Member
  • *****
  • Благодарности
  • -Казани: 25
  • -Получени: 190
  • Публикации: 3624
Re: Оптимизиране на заявки
« Отговор #1 -: 08 Юли 2015, 23:50:49 »
Типа е най-важен за мен.  Играл съм си много и големи успехи и бързина не съм постигнал.  Сложете по един индекс на gamerid, понеже join-a е върху този key и ползвайте инаги inner join когато може, вместо right или left.
Java is to Javascript as fun is to funeral.

http://nau4i.me/forum/index.php/topic,15129.0.html

jazzman

  • Hero Member
  • *****
  • Благодарности
  • -Казани: 25
  • -Получени: 190
  • Публикации: 3624
Re: Оптимизиране на заявки
« Отговор #2 -: 09 Юли 2015, 00:05:01 »
Ето и как трябва да бъде заявката според 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 и други частично групират резултата и парсъра не извежда грешки.
« Последна редакция: 09 Юли 2015, 00:06:43 от jazzman »
Java is to Javascript as fun is to funeral.

http://nau4i.me/forum/index.php/topic,15129.0.html