Оптимизация на заявки съдържащи ORDER BY RAND()

Много важна част при разработката на динамичен сайт е да се обърне внимание на заявките, които се изпълняват към 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

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

Обновена: 17.03.2022

Беше ли Ви полезна тази статия?

Вижте още