Оптимизиране на 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:

username points
someuser 150
otheruser 50
ganbox 250
randomuser 350
vox 250

След приключване на всяка игра по потребителско име се намира реда за играча и точките му се увеличават с тези от последната игра. Нека да кажем, че в момента е логнат потребител 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, в която в края на всяка игра се прави запис, като се записва време на приключване на играта и кой потребител колко точки е спечелил през последната игра:

timestamp username poits
2010-01-10 11:12:23 someuser 100
2010-01-11 10:24:33 otheruser 20
2010-01-12 15:14:13 ganbox 60
2010-01-15 19:15:55 randomuser 90
2010-02-15 11:52:54 vox 80
2010-04-08 22:04:44 someuser 50
2010-04-08 22:05:07 otheruser 30
2010-04-08 22:05:34 ganbox 190
2010-04-08 22:06:17 randomuser 260
2010-04-08 22:06:51 vox 140
2010-04-08 22:07:58 vox 30

Задачата е да подредим потребителите в класацията 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 заявки са тествани и работят. Ще се радвам на коментари по темата.

Прочетена:16859
1 - лоша2 - слаба3 - средна4 - добра5 - отлична (3 гласа, оценка: 5,00 от 5. Моля изберете оценка!)
Loading...
Георги Стефанов
Георги Стефанов
Магистър по информатика, който се занимава с компютри от 1988 г., професионално с програмиране от 1998 г., а с уеб технологии от 2002 г. Има богат опит при оптимизиране на бизнес сайтове. Повече от 12 години развива успешно свои уеб проекти и работи с български и международни компании за постоянно подобряване на техните сайтове и увеличаване на онлайн продажбите. 

14 comments on “Оптимизиране на SQL заявки”

  1. Налага ми се да правя сложен индекс на две колони. Проблемът е, че основата на базата е InnoDB и едната от колоните е ключ към друга такава. Това автоматично и създава собствен индекс, който не мога да променям (не мога да добавя втората към него). Сложно стана за обясняване...

  2. Не съм сигурен, че разбирам. Дай пример. Ако имаш предвид таблица като тази:
    [cc]CREATE TABLE `UserTop` (
    `username` varchar(50) NOT NULL,
    `points` int(11) NOT NULL,
    `test` int(11) NOT NULL,
    UNIQUE KEY `username` (`username`),
    KEY `points` (`points`),
    KEY `test` (`test`),
    KEY `points_2` (`points`,`test`)
    ) ENGINE=InnoDB DEFAULT
    [/php]
    Имам индекс по колоните points, test и също така комбиниран индекс по двете. InnoDB го позволява, но в този случай индекса KEY `points` (`points`) е излишен.

  3. Не знам дали в новата версия на MySQL е оправено, но сме наблюдавали в старите версии как при свързване на две таблици MySQL-а "забравя" да ползва индекса, ако типа не съвпада съвсем точно.

  4. "8. Когато свързваш две таблици трябва типа на колоните, по които се свързва да съвпада точно, например ако едната е от тип INT(8), а другата е от тип INT(11) може да се очакват проблеми."
    Не съм 100% сигурен, но този пример ми се струва неудачен...

  5. То това число в скобите не влияе на типа на полето, все си е INT, та затова ми се струва странно.
    Ама знае ли човек 🙂

  6. Можете ли да ми кажете как ще изглежда заявката с обикновен израз без ADODB библиотеката?
    На мястото на $db->GetOne($query) какво ще е?

  7. Реших, че е очевидно. Този метод изпълнява SQL SELECT заявка, като извлича стойността на само един ред и само една колона и я връща в променлива. Ако $query = "SELECT pos FROM Table WHERE id = 1"; Тогава може да земестим горния ред
    $pos = $db->GetOne($query);
    с редовете:
    $result = mysql_query($query);
    $row = mysql_fetch_assoc($result);
    $pos = $row['pos'];
    като добрия стил на програмиране изисква след първия ред да има и обработка на евентуална грешка. Това показва, че ползване на някаква библиотека за работа с база данни е добра идея, защото най-малкото спестява излишно писане.

  8. Добре всичко мина добре много се раздвам и съм благодарен.
    Само излезе един проблем, че ми ги показва ако имат равни точки 1,1,3,3 :X

  9. ок оптимизиране оптимизиране нищо по прекрасно от това само че как аджеба да лимитирам със самата заявка да си взема по 5 резултата от всяка категория а не да въртя един куп цикли и проверки за да стигна до крайния резултат
    [cc lang="sql"]
    SELECT
    p.pid,
    p.pic_name,
    p.description,
    l.BG_bg,
    u.username,
    FROM
    `pw_category` as c,
    `pw_pictures` as p,
    `pw_users` as u,
    `pw_lang` as l
    WHERE
    p.uid = u.uid and
    c.lid = l.lid and
    p.cid = c.cid
    [/php]

  10. Това е задачата от вида "Първите X от всяка група".
    За да извадиш от таблица Items последните 5 записа от всяка категория:
    [cc lang="SQL"]
    SELECT date,id,category_id
    FROM Items
    WHERE (
    SELECT count(id) FROM Items AS L
    WHERE L.category_id = Items.category_id AND L.date > Items.date
    ) <= 4 [/php] Забележки: - За първите 5 пишеш 4, защото се броят от 0. - Тази заявка има алгоритъм от квадратична сложност и може да се оптимизира.

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

  12. Квадратична сложност означава сравнение "всеки със всеки" означава се с O(n) x O(n) и при големи n (при много редове в таблицата) е много бавен. С други думи ако таблицата е малка (и няма да нараства в бъдеще) тази заявка ще свърши работа, но иначе трябва да се оптимизира. Темата е доста обширна и заслужава отделна статия.

  13. Определено статията е страхотна. Имаше какво да научи човек от нея. Определено има тънкости в SQL заявките и особено, ако става въпрос за огромни таблици със хиляди записи в тях.
    Браво.

споделиха
phone linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram