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

Основни положения при оптимизация на mySQL

Обикновено проблемите с бавни SQL заявки се дължат на грешно проектирана база данни, липса или грешно използване на индекси и най-често неправилно написани SQL заявки.

Статията разглежда няколко примера от практиката, които дават идеи за нов начин на писане на оптимизирани SQL заявки, при който не се прехвърлят много данни, за да се увеличи бързодействието.

Мониторинг на SQL заявки

На първо място препоръчвам на всеки използващ mySQL да ползва лог на бавните заявки. Активира се лесно и записва всяка заявка, която отнеме за изпълнението си повече от предварително зададено време (обикновено 10 сек.). Все пак не може да решиш проблем, ако не знаеш, че съществува 😉
Използвай EXPLAIN пред SELECT за информация относно заявката - кой индекс се използва, колко реда се преглеждат и др.
За мониторинг в реално време препоръчвам mytop - приложение написано на Perl, което показва всички активни заявки и колко време отнемат.

Няколко бързи съвета за начинаещи

1. Ползвай колона id с атрибути PRIMARY и auto_increment за всяка таблица, дори да ти се струва, че никога няма да ти трябва.
2. Избягвай винаги "SELECT * FROM ..." вместо това пиши "SELECT col1, col2, col3... FROM ..." - изброявай нужните колони вместо да вземаш всички данни.
3. Никога не ползвай "SELECT (*) FROM ...", а пиши "SELECT (id) FROM ...", където id е колона с уникални стойности.
4. При създаването на таблицата създай индекси по всяка една колона, по която в бъдеще се очаква да сортираш данните или да извличаш данни като търсиш по стойност в тази колона.
5. Почти винаги т.4 е недостатъчна - трудно е да създадеш всички необходими индекси предварително. При написването на всяка нова заявка проверявай какви са колоните, по които се търси и провери дали има индекси по тях - ако няма ги създай веднага!
6. Ползвай сложни индекси. Ако имаш заявка, в която се търси по повече от една колона например съдържа "... WHERE user_id = 8 AND date > '2010-01-01' ", в този случай ако имаш отделни индекси по user_id и по date ще бъде автоматично избран и ползван само единия. Ако много често се изпълнява такава заявка (или си я открил в лога с бавни заявки) ти трябва сложен индекс по двете колони. Очаквай отделна статия по въпроса за комбинираните индекси.
7. Нормализиране на таблици. Тук няма да влизам в детайли, а само ще спомена, че ако в някоя колона имаш данни, които изглеждат като: "4,5,13,45,345" - няколко стойности разделени със запетая, това почти винаги е знак за грешно проектирана база данни.
8. Когато свързваш две таблици трябва типа на колоните, по които се свързва да съвпада точно, например ако едната е от тип INT(8), а другата е от тип INT(11) може да се очакват проблеми.

Аксиома на gan за оптимизация на SQL

Стреми се да пишеш SQL заявките си така, все едно уеб сървъра и SQL сървъра са на различни машини - пренесените данни между двата трябва да са минимални!

Откриване на позицията на ред след сортиране на таблица

Нека да предположим, че имаме сайт за игра и има таблица за класиране UserTop с колони username и points, в която за всеки потребител се записва, колко точки е спечелил общо от всички игри. Колоната username съдържа уникални стойности. Пример за SQL таблица UserTop:

След приключване на всяка игра по потребителско име се намира реда за играча и точките му се увеличават с тези от последната игра. Нека да кажем, че в момента е логнат потребител ganbox и искаме да покажем на играча на коя позиция е в класацията. Затова трябва да сортираме таблицата по колона points в намаляващ ред и да видим на коя позиция се намира ganbox. Един начинаещ програмист ще направи следното:

Грешно решение:

$query = "SELECT * FROM UserTop ORDER BY points DESC ";
$results = $db->GetAll($query); # всички резултати в масив (ползва се ADODB библиотека за краткост)
$pos=0;
for($i=0; $i
if($result[$i]['username'] == 'ganbox'){
$pos = $i+1;
}
}

Тук има няколко грешки:
1. Взема се цялото съдържание на таблица UserTop - може да бъде огромно количество данни.
2. На всяка итерация на цикъла for се оценява големината на масива $results.
3. След като е намерена и записана позицията в pos, цикъла не се прекъсва, а продължава да се върти безсмислено.
Не можете да си представите, колко много хора пишат по този небрежен начин. Затова много сайтове за игри работят отчайващо бавно при използване от много хора.

Следва малко по-добро, но все още неправилно решение.
Друго грешно решение:

$query = "SELECT username FROM UserTop ORDER BY points DESC ";
$results = $db->GetAll($query); # всички резултати в масив
$pos=0;
$size=count($results);
for($i=0; $i<$size; $i++){
if($result[$i]['username'] == 'ganbox'){
$pos = $i+1;
break;
}
}

Подобренията се виждат:
1. Избират се само данните от колона username - все още връща всички редове и данните са прекалено много.
2. Оценката на големината на масива е изнесена извън цикъла.
3. След откриване на позицията цикъла спира.
По точки 2. и 3. сме ОК, но т. 1 е голям проблем. Представи си, че уеб сървъра и SQL сървъра са на отделни машини - всички данни от колона username трябва да минат по мрежата, ако таблицата съдържа много редове и се ползва от много хора, това ще доведе до значително забавяне. Така стигаме до идеята, че тази задача трябва да се оптимизира, като обработката се изнесе към SQL сървъра.

Почти правилно решение:

$query = "SELECT COUNT( username ) AS pos FROM UserTop WHERE points >= (SELECT points FROM UserTop WHERE username='ganbox' ) ";
$pos = $db->GetOne($query); # един резултат

Първо се изпълнява заявката в скобите, която връща точките на текущия играч (низа 'ganbox' трябва да се замести с потребителското име на логнатия играч). След това се вземат всички редове, които имат повече точки (включително тези които имат равен брой точки с играча) и се преброяват. Връща се едно единствено число - прекрасно!
Има един малък проблем. Ако има играчи с брой точки равен на броя точки на текущия адрес, позицията няма да е вярна. Затова:

Правилно решение:

$query = "SELECT COUNT( username ) AS pos FROM UserTop WHERE points > (SELECT points FROM UserTop WHERE username='ganbox' ) ";
$pos = $db->GetOne($query); # един резултат
$pos++; # това е търсената позиция

Знакът е > защото в класация сортираме от по-голямо към по-малко.

.

Ако ти е станало интересно продължавай да четеш, ще усложним задачите с примери от практиката.
Нека освен таблица UserTop, която е класация имаме UserLog, в която в края на всяка игра се прави запис, като се записва време на приключване на играта и кой потребител колко точки е спечелил през последната игра:

Задачата е да подредим потребителите в класацията UserTop така, че ако няколко души имат равен брой точки по-напред да излиза този, който е играл по-скоро. В примера vox и ganbox имат еднакъв брой 250 точки, но vox трябва да излиза по-напред, защото е играл последен.

Сортиране на данни по два критерия в различни таблици

Заявката, която ще изведе играчите сортирани първо по брой точки, а тези с еднакъв брой по времето на последната активност:

$query = "SELECT T.username, T.points FROM UserTop T LEFT JOIN UserLog L USING(username) GROUP BY username ORDER BY T.points DESC , L.timestamp DESC";

Дотук добре. Сега освен страницата с тази класация искаме да показваме последните 10 действия - последните 10 играчи, които са играли. Там разбира се може да има повторения. Заявката е елементарна, но ни подготвя за следващата задача.

$query = "SELECT username, points FROM `UserLog` ORDER BY `UserLog`.`timestamp` DESC LIMIT 10";

Забеляза ли, че не ползвам "SELECT * FROM", а избирам тези които ми трябват (не го забравяй!).

Добре стигнахме до интересното. Следват два примера от практиката.

Откриване на класирането на играч в края на игра

Искаме в края на всяка игра освен да добавим запис в UserLog и да увеличим точките на играча в UserTop, да намерим новата позиция на играча в класацията и общия брой на точките му, за да може да му съобщим тези данни.

Правилно решение:

$query = " SET @num:=0; SELECT username, pos, points FROM (SELECT @num := @num + 1 AS pos, T.points, T.username FROM UserTop T LEFT JOIN UserLog L USING(username) GROUP BY T.username ORDER BY T.points DESC, L.timestamp DESC) as R WHERE username = 'ganbox' ";

Вижда се, че ganbox е на 3-то място с 250 точки, а vox на второ пак с 250, но е играл по-скоро.
Заявката отново е супер оптимизирана според аксиомата на gan 🙂 така, че да върне само нужните данни.
Използва вътрешна SQL променлива @num, която първоначално се нулира. След това се изпълнява вътрешната заявка, която написана отделно:

$query = " SET @num := 0; SELECT @num := @num + 1 AS pos, T.points, T.username FROM UserTop T LEFT JOIN UserLog L USING(username) GROUP BY T.username ORDER BY T.points DESC, L.timestamp DESC" ;

ще върне всички играчи в класацията с добавена колона с име pos, в която е позицията на всеки играч. По този начин външната заявка има задачата само да филтрира данните за играча, който ни интересува.

Откриване на запис отпаднал от последните 10 действия

Искаме още в края на всяка игра, да намерим кой играч е отпаднал на 11-място в лога (т.е. името му не се вижда вече в последните 10) и само ако името му го няма в първите 10 резултата да предприемем действие - например да го поканим да играе отново.

Как да решим тази задача? Можем лесно да вземем играча на позиция 11, както и да изведем всички от първите 10 позиции, но как да проверим дали този от 11-та се съдържа в първите 10?
Единия подход е с една заявка да вземем играча X от 11 позиция. След това с втора заявка да вземем потребителските имена на всички от първите 10, да ги заредим в PHP масив и да потърсим X в масива. Както вече се досети този подход не ми харесва, защото за 10 не е страшно, но ако са 100, а ако са 1000? Трябва ни отново оптимизирано решение с минимално прехвърляне на данни.

Правилно решение:

$query = "SET @L:=(SELECT username FROM UserLog ORDER BY timestamp DESC LIMIT 10,1); SELECT @L AS username,SUM(F) AS cnt FROM (SELECT IF(username=@L,1,0) AS F FROM UserLog ORDER BY timestamp DESC LIMIT 0,10) AS T";

Тази заявка ще върне резултат:

Array
(
[username] => 'someuser',
[cnt] => 1
);

Наистина ако сортираш UserLog от примера по време в обратен ред, someuser е на 11 позиция. Също така се среща на 6-та позиция и затова cnt е 1, като cnt показва не само дали се среща, но и колко пъти се среща в позициите от 1 до 10. По този начин предприемаш действие само ако cnt=0.

Това бяха само някои идеи за нов стил на писане на SQL заявки с цел оптимизиране на бързодействието. За пълнота ще спомена, че в много случаи отлични резултати могат да се постигнат при използване на кеширане на SQL заявки. Самото кеширане на заявки се програмира лесно. Трудното е да се определи в кой момент да се ползва кеш. Темата ще бъде разгледана в друга статия.

Всички SQL заявки са тествани и работят. Ще се радвам на коментари по темата.