MySql оптимизация на заявки, полезни плодове

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

1. Оптимизирайте заявките за кеша на заявките

Повечето MySQL сървъри имат активирано кеширане на заявки. Един от най-добрите начини за подобряване на производителността е просто да осигурите кеширане на самата база данни. Когато една заявка се повтаря много пъти, нейният резултат се взема от кеша, което е много по-бързо от директно извикване на база данни. Основният проблем е, че много просто използват заявки, които не могат да бъдат кеширани:

Причината е, че първата заявка използва функцията CURDATE(). Това се отнася за всички функции като NOW(), RAND() и други, чийто резултат е недетерминиран. Ако резултатът от дадена функция може да се промени, тогава MySQL не кешира такава заявка. В този пример това може да бъде предотвратено чрез изчисляване на датата преди изпълнението на заявката.

2. Използвайте EXPLAIN за вашите SELECT заявки

Използвайки EXPLAIN, можете да видите как точно MySQL изпълнява вашата заявка. Това може да ви помогне да се отървете от затруднения в производителността и други проблеми във вашата заявка или структура на таблица. Резултатът от EXPLAIN ще ви покаже кои индекси се използват, как се избират и сортират таблиците и т.н. Вземете вашата заявка SELECT (може да е сложна, с обединения) и добавете ключовата дума EXPLAIN в началото. Можете да използвате phpmyadmin за това. В резултат на това ще получите много интересна маса. Например, позволете ми да забравя да добавя индекс къмтаблицата, участваща в присъединяването:

mysql

След добавяне на индекс за полето group_id:

mysql

Сега вместо 7883 реда се избират само 9 и 16 реда от две таблици. Умножаването на всички числа в колоната с редове ще даде число, което е право пропорционално на производителността на заявката.

3. LIMIT 1, когато е необходим само един ред

Понякога, когато препращате към таблица, знаете със сигурност, че имате нужда само от един определен ред. Например, трябва да получите единичен уникален ред или просто да проверите за съществуването на записи, които отговарят на заявката WHERE. В този случай добавянето на LIMIT 1 към вашата заявка ще бъде по-оптимално. По този начин базата данни ще спре да извлича записи след намирането на първия, вместо да извлича цялата таблица или индекс.

4. Индексирайте полетата, по които търсите

Индексът не е само първичен или уникален ключ. Това също са всички колони в таблицата, които използвате за търсене и могат да бъдат индексирани.

оптимизация

Както можете да видите, това правило важи и за част от низовете, например - "last_name LIKE 'a%'". Когато търсите от началото на низ, MySQL използва индекса на тази колона. Трябва също да знаете, че това няма да работи за регулярни изрази. Например, когато търсите дума (т.е. "WHERE post_content LIKE '%apple%'"), тогава обикновеният индекс няма да свърши никаква работа. Би било по-добре да използвате пълнотекстово търсене или да създадете своя собствена система за индексиране.

5. Индексни полета за обединяване и използване на същите типове колони за тях

Ако вашето приложение съдържа много обединения на таблици, трябва да индексирате полетата за обединяване и в двете таблици. Това ще повлияе на начина, по който MySQL извършва вътрешна оптимизация на съединенията. Също и тези колонитрябва да са от същия тип. Например, ако съедините колона DECIMAL с колона INT от друга таблица, MySQL няма да може да използва нито един от индексите. Дори кодирането на знаци трябва да бъде от един и същи тип за колони с низове.

6. Не използвайте ORDER BY RAND()

(Има предвид избора на един ред. Бележка на преводача)

Това е едно от онези неща, които изглеждат много добре на пръв поглед, но много начинаещи програмисти си падат по него. Нямате представа какво затруднение в производителността ще имате, ако използвате това в заявки. Ако наистина искате да подредите на случаен принцип редовете в заявката си, има по-добри начини да го направите. Разбира се, това ще доведе до допълнителен код, но ще се отърве от затруднението в производителността, което ще се стесни експоненциално с нарастването на данните. Проблемът е, че MySQL ще RAND() (което натоварва процесора) за всеки ред при сортиране, връщайки само един ред.

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

7. Избягвайте SELECT *

Колкото повече данни се четат от таблицата, толкова по-бавна е заявката. Това увеличава времето за работа с хранилището на данни. Освен това, когато сървърът на базата данни е инсталиран отделно от уеб сървъра, ще има голямо забавяне при прехвърляне на данни по мрежата. Добър навик е да посочите кои колони от заявката ви трябват.

8. Опитайте се винаги да създавате ID поле

9. Използвайте ENUM вместо VARCHAR

ENUM е много бърз и компактен тип поле. Стойностите в него се съхраняват по същия начин като TINYINT, но се показват като в поле за низ. Това го прави незаменим в някои случаи. Ако имате поле, къдетоще бъде добре дефиниран набор от стойности, използвайте ENUM вместо VARCHAR. Например, ако има поле „статус“, неговите стойности могат да бъдат „активен“, „неактивен“, „чакащ“, „изтекъл“ и т.н. Можете дори да получите „съвет“ от MySQL как да възстановите таблицата. Ако имате поле VARCHAR, MySQL може да предложи да го промените на ENUM. За това се използва PROCEDURE ANALYSE(), описано по-долу.

10. Използвайте съвети от PROCEDURE ANALYSE()

заявки

Моля, имайте предвид, че това са само съвети. Ако добавите още записи, те може да остареят. В крайна сметка зависи от вас да решите дали да ги използвате или не.

11. Използвайте NOT NULL, ако е възможно

Ако има специални причини да използвате NULL, използвайте го. Но преди това се запитайте - има ли разлика между празен низ и NULL (за INT - 0 или NULL). Ако няма такива причини, използвайте NOT NULL. NULL заема повече място и освен това усложнява сравненията с такова поле. Избягвайте го, ако е възможно. Все пак има основателни причини да използвате NULL, това не винаги е лошо нещо. От документацията на MySQL: „Колоните NULL заемат повече място в запис, поради необходимостта да се маркира, че това е NULL стойност. За MyISAM таблици всяко поле NULL отнема 1 допълнителен бит, който се закръгля до най-близкия байт."

12. Подготвени отчети

13. Небуферирани заявки

Обикновено при подаване на заявка скриптът спира и изчаква резултата от нейното изпълнение. Можете да промените това, като използвате небуферирани заявки. За добро описание вижте документацията на функцията mysql_unbuffered_query():

“mysql_unbuffered_query() изпраща SQL заявка към MySQL, без да извлича или автоматично буферира получените редове, както прави mysql_query(). От една страна, това спестява значителноколичеството памет за SQL заявки, които произвеждат големи набори от резултати. От друга страна, можете да започнете да работите върху резултантния набор след получаване на първия ред: не е нужно да чакате пълната SQL заявка да завърши"

Има обаче определени ограничения. Ще трябва да прочетете всички записи или да извикате mysql_free_result(), преди да можете да изпълните друга заявка. Освен това не можете да използвате mysql_num_rows() или mysql_data_seek() за резултата от функция.

14. Съхранявайте IP в UNSIGNED INT

15. Таблици с фиксиран размер (статични) - по-бързо

Ако всяка колона в таблица има фиксиран размер, тогава се казва, че таблицата е „статична“ или „фиксиран размер“. Пример за колони с нефиксирана дължина: VARCHAR, TEXT, BLOB. Ако включите такова поле в таблица, то вече няма да бъде фиксирано и ще се обработва по различен начин от MySQL. MySQL може да търси записи в тях по-бързо. Когато ред от таблица трябва да бъде избран, MySQL може да изчисли позицията му много бързо. Ако размерът на записа не е фиксиран, той се търси по индекса. Тези таблици също са по-лесни за кеширане и възстановяване след срив на базата данни. Например, ако конвертирате VARCHAR(20) в CHAR(20), записът ще заема 20 байта, независимо от действителното му съдържание. Използвайки метода "вертикално разделяне", можете да преместите колони с променлива дължина на реда в отделна таблица.

16. Вертикално разделяне

17. Разделете големи заявки DELETE и INSERT

Ако трябва да направите голяма заявка за изтриване или вмъкване на данни, трябва да внимавате да не счупите приложението. Изпълнението на голяма заявка може да заключи таблицата и да доведе до неправилнаработа на цялото приложение. Apache може да изпълнява множество паралелни процеси едновременно. Следователно работи по-ефективно, ако скриптовете се изпълняват възможно най-бързо. Ако заключите таблици за дълго време (например 30 секунди или повече), тогава при много трафик на сайта може да има голяма опашка от процеси и заявки, което може да доведе до бавен сайт или дори да срине сървъра. Ако имате такива заявки, използвайте LIMIT, за да ги изпълните на малки партиди.

18. Малките колони са по-бързи

За база данни работата с твърд диск е може би най-слабото място. Малките и компактни записи обикновено са по-добри по отношение на производителността, т.к намалете използването на диска. Документацията на MySQL има списък с изисквания за съхранение на данни за всички типове данни. Ако вашата таблица ще съхранява малко редове, тогава няма смисъл да правите първичния ключ тип INT, може би е по-добре да го направите MEDIUMINT, SMALLINT или дори TINYINT. Ако не е необходимо да съхранявате часа, използвайте DATE вместо DATETIME. Внимавайте обаче да не свършите като Slashdot.

19. Изберете правилния тип маса

Двата основни типа таблици са MyISAM и InnoDB, всяка със своите плюсове и минуси. MyISAM е добър при четене на големи количества данни от таблици, но е лош при писане. Дори ако промените само един ред, цялата таблица е заключена и никой процес не може да прочете нищо от нея. MyISAM е много бърз при изпълнение на заявки като SELECT COUNT(*). InnoDB има по-сложен механизъм за съхранение и може да бъде по-бавен от MyISAM за малки приложения. Но поддържа заключване на редове, което е по-ефективно при мащабиране. Някои допълнителни функции също се поддържат, като операциикато транзакции. Повече информация: MyISAM Storage Engine InnoDB Storage Engine

20. Използвайте ORM

Използвайки ORM, можете да получите известна оптимизация на работата. Всичко, което може да се направи с ORM, може да се направи и ръчно. Но това изисква повече работа и по-високо ниво на знания. ORM е страхотен за мързеливо зареждане на данни. Това означава извличане на данни според нуждите. Но трябва да внимавате, защото това може да доведе до много малки заявки, което ще доведе до влошаване на производителността. ORM може също така да групира множество заявки в партиди, вместо да изпраща всяка отделно. Моят любим ORM за PHP е Doctrine. Вече написах статия за инсталирането на Doctrine в CodeIgniter.

21. Внимавайте с постоянните връзки

Постоянните връзки са предназначени да намалят разходите за установяване на връзка с MySQL. Когато се създаде връзка, тя остава отворена след прекратяване на скрипта. Следващият път този скрипт ще използва същата връзка. mysql_pconnect() в PHP Но това звучи добре само на теория. От моя личен опит (и опита на други), използването на тази функция не е оправдано. Ще имате сериозни проблеми с ограниченията на връзката, паметта и т.н. Apache създава много едновременни нишки. Това е основната причина постоянните връзки да не работят както трябва. Консултирайте се с вашия системен администратор, преди да използвате mysql_pconnect().