https://blog.superhosting.bg/sql-queries-part1.html

Натиснете ENTER, за да видите резултатите или ESC за изход.

Блог на SuperHosting.BG

Бърз сайт = бързи SQL заявки! (част 1)

2012-01-11
Съвети от support-a
SQL, SQL заявка, база данни, неоптимални, оптимални, оптимизация, MySQL, order by, group by, inner join, primary key

SQL QueriesВ една от предишните статии на “Съвети от 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

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

Съвет от support- a!

Когато разработвате приложения, винаги тествайте SQL заявките с голям обем от данни. Проблеми с бързодействието често се получават при увеличаване обема на информацията в базата както по редове, така и по колони. При тестове с големи бази от данни, ще можете да локализирате проблеми при разработката на сайта и да бъдат отстранени още в началото. Така, убедени в коректното поведение на сайта и при голям обем от данни, единственото, за което ще мислите, е как да развивате успешно онлайн проекта си с повече уникално съдържание и повече посетители. 🙂 А не е ли това всъщност целта, която трябва да постигне всеки успешен сайт!? 🙂

Очакваме вашите коментари, мнения и предложения за още оптимизации на SQL заявки! Споделете и за какво още искате да пишем по тази тема?

СуперХостинг.БГ

СуперХостинг.БГ е СуперЕкипът! Ежедневно всеки дава най-доброто от себе си, за да можете вие, клиентите на СуперХостинг.БГ, да сте номер едно. СуперСилата на СуперЕкипа е, че винаги е готов да ви даде точната информация, тази от която се нуждаете и за която питате.

500px270px
SuperHosting.BG

Препоръчани статии

Оставете коментар

8 Коментара към "Бърз сайт = бързи SQL заявки! (част 1)"

Уведоми ме при
avatar

Dimitar
Гост
13 януари 2012 3:02

Благодаря за полезната информация, както винаги удряте по-едно рамо в помощ на потребителите 🙂

Милен Стоянов
Гост
11 януари 2012 18:15

Статията ми харесва, наистина бързината на зареждане е от голямо значение ще следя с интерес 🙂

dzver
Гост
11 януари 2012 15:01
Подходът не е добър – нито order by rand, нито корелираните subquery-та са добра идея (към момента) в MySQL. Дори при някакви много малки обеми данни това да е ок, неизбежно ще се забави с нарастване на обема информация. По-устойчиво решение би било нещо от сорта: SELECT id FROM blah ORDER BY id DESC LIMIT 1; — или select max(id) После в скрипта: $id = rand(0, $id – 1 ); и после N пъти SELECT * FROM blah WHERE id > $id ORDER BY id DESC LIMIT 1; По този… още »
Виктор Илиев
Гост
11 януари 2012 16:46

А ако е изтрит някой запис и върне празен резултат? 🙂

dzver
Гост
11 януари 2012 17:15

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

Слави Асенов
Гост
Слави Асенов
12 януари 2012 3:11
Ясно е че има много варианти, както и че всички имат недостатъци. Но всеки вариант може да се окаже добро решение за даден казус. Както е ясно, че от суперхостинг се опитват да отсеят най-фрапантните случай. Вярвам, че на повечето им клиенти ще им бъде трудно да си подменят sql заявката с някоя от техните примери. А задачата да променят и php код ще е невъзможна мисия. За останалите е ясно, че най-малкото могат да прочетат как да си решат проблема 😉 А относно limit и max() за тях може… още »
dzver
Гост
12 януари 2012 14:28

Това е несъществено, на фона на идеята да се ползва корелирано субкюъри с order by rand()

Слави Асенов
Гост
Слави Асенов
12 януари 2012 18:59

Принципно си прав

wpDiscuz