Students Save 30%! Learn & create with unlimited courses & creative assets Students Save 30%! Save Now
Advertisement
  1. Code
  2. MySQL
Code

Пишемо вражаюче швидкі MySQL запити

by
Difficulty:IntermediateLength:LongLanguages:

Ukrainian (українська мова) translation by Anton Bagaiev (you can also view the original English article)

Різниця між правильним і неправильним SQL є величезною, і під час виконання на робочих серверах під великим навантаженням вони можуть значно впливати на поведінку і надійність сервісів. В цій статті я розгляну, як писати швидкі запити і фактори, що роблять їх повільними.

Чому MySQL?

Сьогодні йде багато розмов про Dig Data та нові технології. NoSQL та хмарні рішення це супер, але багато популярного софту (такого як WordPress, phpBB, Drupal, VBulletin Forum software) і досі працює на MySQL. Міграція на найновіші рішення може вилитись не лише у зміну конфігурацій на серверах. До того ж, ефективність MySQL досі на рівні, особливо версія Percona.

Не робіть розповсюджену помилку, викидаючи все більше і більше заліза на вирішення проблеми повільних запитів і високого навантаження серверів — краще звернутись до витоків проблем. Збільшення потужності процесорів та жорстких дисків та додавання оперативної пам'яті це також певний вид оптимізації, проте це не те, про що ми будемо говорити у цій статті. Також, не маючи оптимізованого сайту і вирішуючи проблему залізом, навантаження буде рости лише у геометричній прогресії. Тому це лише короткострокове рішення.

Гарне розуміння SQL це найважливіший інструмент для веб-розробника, що дозволить ефективно оптимізувати і використовувати реляційні бази даних. В цій статті ми сфокусуємось на популярній відкритій базі даних, що часто використовується у зв'язці з PHP, і це є MySQL.

Для кого ця стаття?

Для веб-розробників, архітекторів і розробників баз даних та системних адміністраторів, що добре знайомі з MySQL. Якщо раніше ви не використовували MySQL, ця стаття може не принести вам користі, проте я все одно буду намагатися бути якомога інформативнішим і корисним навіть для новачків у MySQL.

Спочатку бекап

Я рекомендую робити наступні кроки на базі MySQL, з якою ви працюєте, проте на забудьте зробити резервну копію. Якщо ви не маєте бази даних, з якою ви можете працювати, я буду надавати приклади для створення власної бази даних, де це буде доречно.

Робити бекапи MySQL просто, використовуючи утиліту mysqldump:

Ви можете дізнатися більше про mysqldump.

Що робить запит повільним?

Ось загальний нарис факторів, що впливають на швидкість виконання запитів і навантаження серверу:

  • індекси таблиць
  • умова WHERE (та використання внутрішніх функцій MySQL, наприклад, таких як IF або DATE)
  • сортування з ORDER BY
  • часте повторення однакових запитів
  • тип механізму зберігання даних (InnoDB, MyISAM, Memory, Blackhole)
  • не використання версії Percona
  • конфігурації серверу (my.cnf / my.ini)
  • великі видачі даних (більше за 1000 рядків)
  • нестійке з'єднання
  • розподілена або кластерна конфігурація
  • слабке проектування таблиць

Далі ми звернемось до всіх цих проблем. Також, встановіть Percona, якщо ви досі не використовуєте цю вбудовану заміну стандартному MySQL, що додасть сильне збільшення потужності бази даних. Щоб подивитись порівняння Percona та MySQL, подивіться цю статтю.

Що таке індекси?

Індекси використовуються у MySQL для пошуку рядків з вказаними значеннями колонок, наприклад, з командою WHERE. Без індексів, MySQL має, починаючи з першого рядка, прочитати всю таблицю у пошуках релевантних значень. Чим більша таблиця, тим більше витрат.

Якщо таблиця має індекси на колонках, що будуть використані у запиті, MySQL швидко знайде розташування необхідної інформації без перегляду всієї таблиці. Це набагато швидше за послідовний пошук у кожному рядку.

Нестійке з'єднання?

Коли ваш додаток під'єднується до бази даних та налаштоване стійке з'єднання, воно буде використовуватись кожного разу без потреби кожного разу відкривати нове з'єднання. Це оптимальне рішення для робочого середовища, що має бути застосованим.

Користувачі PHP можуть прочитати більше у довіднику PHP.

Зменшуємо часте повторення однакових запитів

Найбільш швидкий і ефективний спосіб, що я знайшов для цього це створення сховища запитів і результатів їх виконання за допомогою Memcached або Redis.

Із Memcache ви можете легко покласти у кеш результат виконання вашого запиту, наприклад, таким чином:

Тепер важкий запит, що використовуює LEFT JOIN, буде виконуватись лише раз за кожні 86 400 секунд (тобто, раз на добу), що значно зменшить навантаження MySQL серверу, залишивши ресурси для інших з'єднань.

Примітка: Допишіть p: на початку аргументу хосту MySQLi для створення постійного з'єднання.

Розподілена або кластерна конфігурація

Коли даних стає все більше і швидкість вашого сервісу йде під ухил, паніка може опанувати вас. Швидким рішенням для впевненості того, що всі сервіси працюєть, є розподілення ресурсів (sharding). Проте я не рекомендую робити це, оскільки розподілення за своєю суттю робить структури даних надскладними. І в статті у блозі Percona все дуже ясно сказано — не розподіляйте.

Слабке проектування таблиць

Створення схем баз даних не є складною роботою, якщо слідувати таким золотим правилам, як робота з обмеженнями та знання того, що буде ефективним. Наприклад, зберігання зображень у комірках типу BLOB дуже бендежить — краще зберігайте шлях до файлу у комірці VARCHAR, що є набагато кращим рішенням.

Забезпечення правильного проектування для потрібного використання є першорядним у створенні вашого додатку. Зберігайте різні дані у різних таблицях (такі як категорії та статті) та переконайтесь, що стосунки багато до одного (many to one) та один до багатьох (one to many) можуть бути легко пов'язані з ідентифікаторами (ID). Використання FOREIGN KEY у MySQL ідеально підходить для зберігання каскадних даних у таблицях.

Під час створення таблиці, пам'ятайте наступне:

  • Створюйте ефективні таблиці для вирішення ваших задач, не нагромаджуючи таблиці зайвими даними та зв'язками.
  • Не очікуйте від MySQL виконання вашої бізнес логіки або програмності — дані мають бути готові до вставки рядка вашою скриптовою мовою. Наприклад, якщо вам треба відсортувати список у випадковому порядку, зробіть це у масиві PHP, не використовуючи ORDER BY з арсеналу MySQL.
  • Використовуйте індексні типи UNIQUE для унікальних наборів даних та застосовуйте ON DUPLICATE KEY UPDATE, щоб зберігати дату оновленою, наприклад, для того, щоб знати, коли рядок було в останній раз відмічено.
  • Використовуйте тип даних INT для збереження цілих чисел. Якщо ви не вкажете розмір типу даних, MySQL зробить це за вас.

Основи оптимізації

Для ефективної оптимізації, ми маємо застосовувати три підходи до вашого додатку:

  1. Аналіз (логування повільних запитів, вивчення системи, аналіз запитів та проектування бази даних)
  2. Вимоги до виконання (скільки користувачів, що потребується виконувати)
  3. Обмеження технологій (швидкість заліза, недолучне чи неправильне використання MySQL)

Аналіз може бути зроблений декількома шляхами. Спочатку ми розглянемо найбільш очевидні способи, щоб зазирнути під капот вашої MySQL, у якому виконуються запити. Найперший інструмент оптимізації у вашому арсеналі це EXPLAIN. Якщо додати цей оператор перед вашим запитом із SELECT, результат запиту буде таким:

Колонки, що ви бачите, зберігають важливу інформацію про запит, що буде виконано. Колонки, на які ви маєте звернути найбільну увагу це possible_keys та Extra.

possible_keys покаже індекси, до який рушій MySQL мав доступ, щоб виконати запит. Іноді потрібно призначити індекси, щоб запит виконувався швидше.

Колонка Extra покаже, чи були використані додаткові WHERE чи ORDER BY. Найбільш важливо звернути увагу, чи є Using Filesort у виводі. Переглянемо наступний приклад:

Цей запит може навантажувати систему із-за умовного виразу WHERE, що ми можемо перевірити за допомогою EXPLAIN:

Але, як ми бачимо, цей запит має можливість шукати за двома індексами і зараз причиною навантажень системи є Using filesort у колонці Extra.

Що робить Using Filesort, зазначено у довідці MySQL:

"MySQL має виконати додатковий прохід, щоб зрозуміти, як повернути рядки у відсортованому вигляді. Це сортування відбувається проходом по всім рядкам відповідно до типу об'єднання і зберігає ключ до сортування і вказівник на рядок для всіх рядків, що співпадають з умовним виразом WHERE. Ключі сортуються і рядки повертаються у потрібному порядку."

Зайвий прохід уповільнить ваш додаток, чого потрібно уникати, чого б це не вартувало. Інший критичний результат Extra, що ми маємо уникати, це Using temporary, який говорить про те, що MySQL довелось створити тимчасову таблицю для виконання запиту. Очевидно це жахливе використання MySQL, яке ми маємо уникати всіма методами, доки не отримаємо кращу оптимізацію під наші потреби. В такому випадку результат запиту має бути збережений у Redis або Memcache і не виконуватись користувачами зайвий раз.

Щоб уникнути проблему з Using Filesort ми маємо запевнитись, що MySQL використовує INDEX. Зараз вказано декілька ключів у possible_keys, з яких можна обирати, але MySQL може обрати тільки один індекс для фінального запиту. Також індекси можуть бути складені з декількох колонок, також ви можете ввести підказки (хінти) для оптимізатора MySQL, вказуючи на індекси, що ви створили.

Хінтінг індексів

Оптимізатор MySQL буде використовувати статистику, засновану на запитах таблиць, щоб обрати найкращий індекс для виконання запиту. Він діє досить просто, засновуючись на вбудованій статистичній логіці, тому маючи декілька варіантів не завжди робить правильний вибір без зовнішньої допомоги хінтингом. Щоб упевнитись, що був використаний правильний (або неправильний) ключ, скористуйтесь ключовими словами FORCE INDEXUSE INDEX та IGNORE INDEX у вашому запиті. Ви можете прочитати більше про хінтінг індексів у довідці MySQL.

Щоб вивести ключі таблиці, використовуйте команду SHOW INDEX.

Ви можете задати декілька хінтів для використання оптимізатором, наприклад:

Тепер MySQL має index_status з таблиці для використання, тож запит виправлено.

На додаток до EXPLAIN існує ключове слово DESCRIBE. Разом із DESCRIBE можна переглядати інформацію з таблиці наступним чином:

Додаємо інтекси

Для додавання індексів у MySQL треба використовувати синтаксис CREATE INDEX. Є декілька видів індексів. FULLTEXT застосовується для повнотестового пошуку, також є UNIQUE для зберігання унікальних даних.

Щоб додати індекс у вашу таблицю, використовуйте наступний синтакс:

Це створить індекс на таблиці users, що буде використовувати перші 10 букв з колонки , яка зберігає імена користувачів і має тип varchar.

В цьому випадку, будь-який пошук із запитом WHERE на імені користувача зі збігом до 10 символів буде давати такий же результат, як і перегляд усією таблиці від початку до кінця.

Композитні індекси

Індекси мають великий вплив на швидкість виконання запитів. Лише призначення головного на унікального ключа недостатньо — композитні ключі є реальною областю застосування у налаштуванні MySQL, що іноді вимагає деяких A/B перевірок із використанням EXPLAIN.

Наприклад, якщо нам потрібно посилатись на дві колонки в умові виразу WHERE, композитний ключ буде ідеальним до застосування.

Щойно ми створили ключ на основі колонки username, в якому зберігається ім'я користувача та колонки active типу ENUM, що визначає, чи активний його акаунт. Тепер все оптимізовано для запиту, що буде використовувати WHERE для пошуку валідного імені користувача із активним акаунтом (active = 1).

Наскільки швидкий ваш MySQL?

Увімкнемо профілювання, щоб детальніше розглянути MySQL запити. Це можна зробити, виконавши команду set profiling=1, після чого для перегляду результату треба виконати show profiles.

Якщо ви використовуєте PDO, виконайте наступний код:

Те ж саме можна зробити за допомогою mysqli:

Це поверне вам профільовані дані, що містять час виконання запиту у другому елементі асоціативного масиву.

Цей запит виконувався 0.00024300 секунд. Це досить швидко, тож не будемо турбуватися. Але коли числа стають більшими, ми повинні дивитись глибше.

Перейдіть до вашого додатку, щоб потренуватись на робочому прикладі. Перевірте константу DEBUG у конфігурації вашої бази даних, а потім почніть вивчати систему, увімкнувши вивід результатів профілювання за допомогою функцій var_dump чи print_r. Так ви зможете переходити зі сторінки на сторінку у вашому додатку, отримавши зручне профілювання системи.

Повний аудит вашого додатку

Щоб зробити повний аудит ваших запитів, увімкніть логування. Деякі розробники, з якими я працював, хвилювались про те, що логування сильно впливає на виконання і додатково уповільнює запити. Проте, практика показує, що різниця незначна.

Щоб увімкнути логування у MySQL 5.1.6 використовуйте глобальну змінну log_slow_queries, також ви можете зазначити файл для логування за допомогою змінної slow_query_log_file. Це можна зробити, виконавши такий запит:

Також це можна вказати у файлах конфігурації /etc/my.cnf чи my.ini вашого серверу.

Після внесення змін не забудьте перезавантажити MySQL сервер необхідною командою, наприклад service mysql restart, якщо ви використовуєте Linux.

У версіях MySQL після 5.6.1 змінна log_slow_queries позначена як застаріла і замість неї використовується slow_query_log. Також для більш зручного дебагу можна увімкнути вивід у таблиці, задавши змінній log_output значення TABLE, проте ця функція доступна лише з MySQL 5.6.1.

Змінна long_query_time визначає кількість секунд, за перебігом яких виконання запиту вважається повільним. Стандартне значення це 10, мінімум це 0. Також можна вказати мілісекунди, використовуючи дріб; зараз я вказав одну секунду. Тож тепер кожен запит, що буде виконуватись довше за 1 секунду, буде записаний до логу в таблиці.

Логування буде вестись у таблицях mysql.slow_log та mysql.general_log вашої MySQL бази даних.

Щоб вимкнути логування, змініть значення log_output на NONE.

log_queries_not_using_indexes є корисним перемикачем, що у застосуванні з логуванням повільних запитів, означає що тільки запити, від яких очікується отримати всі рядки, будуть логуватися.

Це налаштування не завжи означає, що індекси не будуть застосовані. Наприклад, коли запит використовує повнотекстовий пошук, це буде записано у лог, тому що індекс не буде обмежувати кількість рядків.

Логування на робочому сервері

На робочому сервері, що обслуговує клієнтів, краще застосовувати логування тільки на короткий період і для моніторингу навантаження, щоб не створювати зайвого навантаження. Якщо ваш сервіс перевантажено і потрібне невідкладне втручання, спробуйте виділити проблему, виконавши SHOW PROCESSLIST, або зверніться до таблиці information_schema.PROCESSLIST напряму, виконавши SELECT * FROM information_schema.PROCESSLIST;.

Логування всіх запитів на робочому сервері може дати вам багато інформації і стати хорошим засобом для дослідницьких цілей під час перевірки проекту, проте логи за великі періоди не дадуть вам багато корисної інформації у порівнянні з логами за період до 48 годин (намагайтесь відслідковувати пікові навантаження, щоб мати шанс краще дослідити виконання запитів).

Примітка: якщо ви маєте сайт, що переживає хвилі трафіку та часи майже без нього, як, наприклад, спортивний сайт в несезон, тоді використайте цю інформацію для побудови і вивчення логування. Не припускайте, що сайт працює швидко. Зробіть дослідження і, що важливо, створіть графік.

Логування і pt-query-digest від Percona

Percona має деякі зручні вбудовані інструменти, і одна з них це pt-query-digest, що є термінальним інструментов для аналізу логів запитів, процесів та мережевого трафіку.

Ви можете використовувати pt-query-digest наступним чином:

Аналізувати log файл (що є результатом виводу логування ваших повільних запитів):

Повідомляти про найповільніші запити з хосту у рельному часі, що є надзвичайно зручним:

Використовуйте tcpdump для повідомлення про найповільніші запити з протоколу MySQL:

Наприкінці ми можемо зберегти вивід повільного запиту з одного хосту на інший для подальшого вивчення. Так ми збережемо огляд запитів у файл slow.log хосту host2:

Для подальшої інформації про використання pt-query-digest читайте довідку Percona.

Продуктивність MySQL і сервера на графіку

InnoDB Row Operations

Цей графік використання таблиці InnoDB показує виконані операції: читання, редагування, видалення та вставку нових рядків.

Це тема для окремої статті, тож я зачеплю лише те, що необхідне для старту. Тут варто зазначити, що спостереження за всіма вашими сервісами необхідне, щоб розуміти їх навантаження та використання.

Щоб зробити це, я рекомендую встановлення інструменту, заснованому на рішенні RRDTool, такому, як Cacti у конфігурації MySQL. Отримайте шаблон Cacti від хлопців з Percona.

Щойно ви встановили налаштування Cacti та підготувализь до аналізування своїх додатків, зачекайте певний час на збір інформації для побудови графіків. Після декількох днів ви почнете бачити денні та нічні ритми вашого трафіку та побачите, наскільки дійсно завантажений ваш сервер.

Якщо вам потрібен автоматизований інструмент для оповіщення та повідомлень, подивіться на monit, відкритий інструмент для моніторингу Unix систем. Використовуючи monit ви можете створювати правила для вашого серверу, щоб отримувати повідомлення, коли навантаження зростає, перехопивши проблему під час її винекнення.

Логування повільних запитів

Важливо знати не лише про запити, що виконуються довше за секунду, також необхідно мати на увазі запити, що виконуються сотні разів. Навіть якщо запити виконуються швидко, у навантаженій системі вони можуть відтягти всі ресурси на себе.

Ось чому завжди потрібно бути насторожі після внесення змін на живому проекті — це найбільш критичний час для роботи будь-якої бази даних. Ми маємо правило у моїй команді ніколи не впроваджувати зміни функціоналу на робочому проекті після середи. Всі роботи мають бути виконаті на початку тижня, не пізніше за середу, щоб команда могла споглядати і відповідно надавати підримку.

Перед впровадженням змін, ви маєте провести тест на ефективність системи за допомогою такого інструменту, як ab. Коли ви запускаєте тест на ефективність системи (або бенчмарк), ви маєте дивитись на результат SHOW PROCESSLIST, також увімкнути логування і моніторити систему такими інструментами, як top, free, та iostat. Це важливий крок перед введеням нового запиту у робоче оточення. Але це не повна гарантія, тому що живий трафік може поводитись відмінно від комп'ютерного тесту.

Щоб почати бенчмарк з ab, переконайтесь, що цей пакет інстальовано:

Тепер можна починати тестувати ваш додаток:

Опція -k означає тип з'єднання keep-alive, опція -c 350 означає номер одночасних з'єднань, тобто номер людей/клієнтів, що зайдуть на сайт одночасно. Нарешті, опція -n 20000 це кількість запитів, що будуть виконані на сторінці my-domain.com.

Тож, виконання цієї команди спричинить лавину запитів до http://my-domain.com/ з 350 одночасними з'єднаннями, що виконають 20000 запитів, і все це буде виконано з заголовками keep-alive.

Після завершення 20000 запитів, ви отримаєте інформацію про становище. Це покаже, як сайт повів себе під навантаженням, яке ви визначили у параметрах команди. Це гарний спосіб дізнатись у автоматизованому режимі, чи зміни у запити були успішними.

Гарячий та холодний кеш

Кількість запитів і навантаження серверу має сильний вплив на виконання, що може вплинути на час виконання запитів. В більшості випадків ви маєте увімкнути логування повільних запитів на робочому сервері, а під час розробки ви маєте взяти за правило, що виконання кожного запиту має бути не довше за частку мілісекунди (0.0xx чи швидше) на вільному сервері.

Застосування Memcache матиме сильний ефект на навантаження серверів, що звільнить ресурси, що виконують запити. Переконайтесь, що ви використовуєте Memcached ефективно і протестували ваш додаток з гарячим кешем (підвантаженими даними) та із холодним кешем.

Щоб уникнути запуск на робочому сервері із пустим кешем, гарною ідеєю буде скрипт, що збере весь необхідний кеш перед запуском серверу, щоб великий наплив клієнтів не знизив час завантаження системи.

Виправлення повільних запитів

Тепер, коли логування налаштоване, ви могли знайти декілька повільних запитів у вашому додатку. Давайте виправимо їх! Для прикладу я продемонструю декілька поширених проблем, що ви можете зустріти та логіку їх виправлення.

Якщо ви поки не знайшли жодного повільного запиту, перевірте налаштування long_query_time, якщо ви користуєтесь цим методом логування. Інакше, перевіривши всі ваші запити профілювання (set profiling=1), складіть список запитів, що забирають більше часу, ніж частка мілісекунд (0.000x секунд) і почнемо з тих.

Поширені проблеми

Ось шість найпоширеніших проблем, які я знаходив, оптимізуючи MySQL запити:

ORDER BY та файлсорт

Запобігання файлсортингу тут неможливе із-за виразу ORDER BY name. Незалежно від того, як ви переставите індекси, найкраще, що ви отримаєте, буде Using where; Using Filesort у колонці Extra. Для оптимізації збережіть цей результат до Memcache, або виконайте сортування у логіці вашого додатку.

Використання ORDER BY разом з WHERE та LEFT JOIN

ORDER BY надзвичайно обтяжує виконання запитів. Для прикладу, нижче ви можете побачити звичайний запит з LEFT JOIN, що об'єднує таблиці products та categories за значенням цифрового ідентифікатора. Якщо прибрати сортування, буде використаний файл-сортинг, тобто вихідний порядок сортування елементів у таблиці.

Якщо це можливо, намагайтесь не використовувати ORDER BY. Якщо ж вам необідно сортування, використовуйте сортування за індексами.

Застосування ORDER BY за тимчасовими колонками

Просто не робіть цього. Якщо вам потрібно об'єднати результати, зробіть це у логіці вашого додатку; не застосовуйте фільтрацію або сортування у тимчасовій таблиці запиту MySQL. Це потребує забагато ресурсів.

Ігнорування індексу FULLTEXT

Використання LIKE це найкраший спосіб зробити повнотекстовий пошук повільним. Застосуйте повнотестовий пошук і отримайте переваги блискучої особливості MySQL:

Безпричинний вибір великої кількості рядків

Забувши про LIMIT у вашому запиті ви можете сильно збільшити час виконання вибірки із бази даних, залежно від розміру таблиць.

Надмірне використання JOIN замість створення композитних таблиць або представлення.

Коли в одному запиті ви користуєтесь більше ніж трьома-чотирма операторами LEFT JOIN, спитайте себе, чи все тут вірно? Продовжуйте, якщо у вас є на те вагома причина, наприклад — запит використовується не часто для виводу у панелі адміністратора, або результат виводу може бути збережений у кеші, а після оброблений чи виведений. Якщо ж вам потрібно виконувати запит з великою кількістю операцій об'єднання таблиць, тоді краще задуматись про створення композитних таблиць із необхідних стовпчиків, або використати представлення.

Отже

Ми обговорили основи оптимізації та інструмени, що потрібні для роботи. Ми маємо вивчати систему, застосовуючи профілювання, інструмент pt-query-digest та оператор EXPLAIN, щоб побачити, що відбувається із базою даних, та зрозуміти, як можна зробити краще проектування.

Також ми подивились на декілька прикладів та класичних пасток, у які ви можете потрапити, використовуючи MySQL. Використовуючи хінтінг індексів ми можемо запевнитись у тому, що MySQL обере необхідні індекси, особливо при декількох виборках у одній таблиці. Щоб продовжити вивчення теми, перегляньте Percona project або MySQL Performance blog, щоб отримати більше інформації.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.