В една от предишните статии на “Съвети от support- a” говорихме за процесорното време и съвети за оптимизация. В тази статия ще обърнем внимание на друг вид оптимизация – заявките към базата данни. Практиката показва, че неоптимални заявки към базата данни са една от основните причини за забавяне работата на сайтовете. Това е нещо, за което ние от хостинг компания СуперХостинг.БГ често се налага да съдействаме на клиентите си и решихме да го споделим и бъдем полезни на всички. (Ще очакваме и вашите коментари дали сме успели! :))
Много важна част при разработката на динамичен сайт е да се обърне внимание на заявките, които се изпълняват към SQL сървъра. Те трябва да бъдат добре структурирани и да обработват само необходимата информация, без излишно избрани редове и колони. Така ще се избегнат проблеми със заявки, които се изпълняват неоптимално.
В следващите редове разглеждаме често срещан пример от нашата практика за SQL заявка, която при нарастване на обема данни в базата, започва да се изпълнява бавно и неоптимално. Целта на заявката е да върне резултати, сортирани на случаен принцип:
SELECT * FROM [tablename] ORDER BY RAND()
На пръв поглед елементарна заявка, която се изпълнява бързо… С нарастване на обема на данните, обаче, времето за изпълнение също нараства и това може да доведе до сериозно натоварване на MySQL сървъра.
Ако в [tablename] има много редове (например, повече от 10 000), заявката ги обработва, след което връща резултат – всички редове (10 000), подредени на случаен принцип. (А когато редовете станат 1 милион!?)
В случай, че в заявката има и limit, например 1: SELECT * FROM [tablename] ORDER BY RAND() LIMIT 1 , то за да се върне точно един резултат, отново се обхождат първо всичките редове.
Основната причина за забавянето на тази SQL заявка е, че за изпълнението на ORDER BY клаузата, MySQL използва временна таблица, която първоначално се създава в паметта, а при големите таблици – на диска на сървъра. (Създаване на временни таблици се използва от MySQL за изпълнението на различни типове заявки като order by(), group by() и др.)
Един вариант за оптимизация е да се избират по-малко на брой колони в самата заявка. Друг вариант е заявката SELECT * FROM [tablename] ORDER BY RAND() да се пренапише по следния начин:
SELECT * FROM [tablename] vc inner join (SELECT vc2.id FROM [tablename] vc2 ORDER BY RAND() ) as i on vc.id = i.id
В посочения пример имаме колона ID, която е Primary Key. В случай, че нямаме Primary key е необходимо или да създадем такъв, или да използваме друга ключова колона.
Ако имаме LIMIT, например: SELECT * FROM [tablename] ORDER BY RAND() LIMIT 1, заявката може да се пренапише оптимално по следния начин:
SELECT * FROM [tablename] vc inner join (SELECT vc2.id FROM [tablename] vc2 ORDER BY RAND() LIMIT 1 ) as i on vc.id = i.id
Причината тази оптимизация да сработва е, че за вътрешната заявка се използва много по-малка по обем временна таблица. При увеличаване на обема на данните, обаче, посоченото решение няма да дава достатъчно добри резултати. Тогава трябва да се използват съвсем различни алгоритми за постигане на целта. Например, кеширане на резултатите и др.
Когато разработвате приложения, винаги тествайте SQL заявките с голям обем от данни. Проблеми с бързодействието често се получават при увеличаване обема на информацията в базата както по редове, така и по колони. При тестове с големи бази от данни, ще можете да локализирате проблеми при разработката на сайта и да бъдат отстранени още в началото. Така, убедени в коректното поведение на сайта и при голям обем от данни, единственото, за което ще мислите, е как да развивате успешно онлайн проекта си с повече уникално съдържание и повече посетители. 🙂 А не е ли това всъщност целта, която трябва да постигне всеки успешен сайт!? 🙂
Очакваме вашите коментари, мнения и предложения за още оптимизации на SQL заявки! Споделете и за какво още искате да пишем по тази тема?
