The погано сформульовані SQL-запити Це одна з найпоширеніших причин, чому програма працює повільно під час роботи з великими реляційними базами даних, такими як MySQL, PostgreSQL, SQL Server, Oracle або DB2. Хоча зараз у нас є потужні сервери та еластичні хмари, неефективні запити зрештою дорого вам обійдуться. вищі витрати на інфраструктуру, вища затримка та гірший користувацький досвід.
Оптимізація SQL-запитів у великих базах даних виходить далеко за рамки простого «додавання індексу і все». Вона включає Розуміння того, як мислить оптимізатор запитівЯк зберігаються дані, які шаблони доступу використовує ваша програма та які комбіновані методи дозволяють зменшити використання вводу-виводу, процесора та пам'яті. У наступних розділах ми розглянемо досить детально та з прикладами, Найефективніші стратегії для максимального використання ваших реляційних баз даних.
Що таке оптимізація SQL-запитів насправді і чому вона важлива?
Оптимізація SQL-запиту Це означає його переписування (та коригування контексту: індексів, статистики, дизайну) таким чином, щоб механізм повертав той самий результат, споживаючи менше ресурсів та за менший час. Синтаксис SQL дозволяє багато способів виразити одне й те саме, але не всі вони виконуються однаково швидко, особливо коли є… мільйони рядків або складних з'єднань.
Коли розробник розуміє, як це працює планувальник запитів За допомогою вашого рушія (PostgreSQL, MySQL, SQL Server, Oracle, DB2 тощо) ви можете писати запити, які краще використовують індекси, зменшують непотрібні зчитування та мінімізують дороговартісні операції, такі як сортування, послідовне сканування або повторювані корельовані підзапити.
Однак важливо чітко розуміти, що Оптимізація запитів — не єдиний фактор продуктивностіСхема (нормалізація, первинні та зовнішні ключі, типи даних), архітектура (репліки, розділи, кеші) та сама інфраструктура мають значний вплив. Але навіть за наявності пристойної архітектури, один погано оптимізований запит може стати серйозною проблемою. жорстоке вузьке місце.
Серед переваг роботи в консультаційних службах виділяються наступні: загальне покращення продуктивності (більше запитів обробляється за менший час), зниження вартості хмарних послуг (менше процесора та дискового простору, менші розміри екземплярів) та більш плавна робота користувача шляхом скорочення часу очікування у списках, пошуках та звітах. Крім того, чіткі та добре структуровані запити легше обслуговувати та налагоджувати, що дуже цінується, коли проєкт розвивається.
У застосунках, які дійсно прагнуть масштабування, безперервна оптимізація запитів стає повторюваним завданням: моніторити, виявляти, вимірювати, коригувати та повторно вимірюватиЦе не одноразова дія, а процес.
Практичний приклад: той самий запит, дуже різна продуктивність
Щоб втілити свої ідеї в життя, уявіть собі стіл замовлення з більш ніж 20 мільйонами записів На сайті електронної комерції ми хочемо отримати завершені замовлення клієнта за останні 30 днів, і без особливих роздумів ми могли б написати щось на кшталт цього:
SELECT * FROM pedidos
WHERE cliente_id = 456
AND LOWER(estado) = 'completado'
AND fecha_creacion BETWEEN NOW() - INTERVAL '30 days' AND NOW();
Цей запит повертає те, що нам потрібно, але з точки зору продуктивності він трохи заплутаний: він використовує SELECT *, застосовує функцію (LOWER) у стовпці фільтра та поєднує дати з виразами, які можуть перешкоджати використанню індексів. Якщо, крім того, відповідні індекси не існують на client_id, status або creation_date, двигун буде змушений просканувати значну частину таблиці.
Практичні наслідки очевидні: Перенесено більше даних, ніж потрібноБільше роботи для серверної частини, що відображає невикористані стовпці, багато читання з диска та час виконання, який у дуже великих таблицях може зрости до кількох секунд, впливаючи на всю систему при багаторазовому запуску.
Те саме питання, сформульоване більш розумно, може виглядати так:
SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 456
AND estado = 'Completado'
AND fecha_creacion >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY fecha_creacion DESC
LIMIT 100;
Ми на місці вибір лише необхідних стовпцівуникнення функцій у стовпці статусу, спрощення умови дати та обмеження кількості рядків. За допомогою добре розроблених індексів (наприклад, INDEX(cliente_id, fecha_creacion) і один про estado (якщо він має високу кардинальність), механізм може використовувати сканування індексів та вирішувати запит у мілісекунди замість секунд.
Цей контраст ілюструє ключову ідею: Недостатньо, щоб запит "працював"Вам доведеться турбуватися про те, як це працює, коли таблиця вже не має сотень рядків, а мільйонів.
Індекси: головний важіль для пришвидшення пошуку
L Індекси – найпотужніший інструмент для пришвидшення запитів у великих базах даних. Замість того, щоб переглядати всю таблицю рядок за рядком (послідовне сканування або Seq Scan), рушій використовує допоміжні структури (зазвичай B-дерева, R-дерева або хеші, залежно від типу даних та рушія), які дозволяють переходити безпосередньо до рядків-кандидатів.
Наприклад, у MySQL найпоширенішими структурами є дерева Б для індексів типів PRIMARY KEY, UNIQUE, INDEX y FULLTEXT, тоді як просторові індекси використовують R-дерева а таблиці в пам'яті можуть отримувати дані з індексів на основі мішанинаКожен з них оптимізований для певного шаблону доступу.
Однак, справа не в тому, щоб індексувати все. Кожен додатковий індекс Це займає місце на диску та уповільнює вставку, оновлення та видалення.тому що двигун повинен синхронізувати структуру. Хитрощі полягають у знаходженні баланс між кількістю індексів та часом відгуку, зосереджуючись на питаннях критичного читання.
Серед найпоширеніших типів індексів у реляційних механізмах ми знаходимо ті, що первинний ключ (унікально ідентифікувати кожен рядок і не допускати нульових значень), ті з зовнішній ключ (посилання на PK іншої таблиці), то унікальні індекси (гарантують унікальність, але дозволяють нулі) та зведені індекси на кілька стовпців, що дуже корисно під час фільтрації або сортування за кількома полями одночасно.
Також є сценарії, коли корисно використовувати індекси з повторюваними значеннями (для пришвидшення пошуку в неунікальних стовпцях) або повнотекстові індекси (FULLTEXT у MySQL, наприклад) для покращення пошуку в довгих текстових полях. Починаючи з MySQL 8.0.13, їх можна створювати функціональні показникиТобто, на основі результату виразу або функції (наприклад, YEAR(fecha_pago)), що відкриває шлях до розширеної оптимізації.
Ми можемо створювати індекси в MySQL за допомогою різних операторів: CREATE INDEX, додавши їх пізніше; ALTER TABLEщоб змінити існуючу таблицю; або безпосередньо у визначенні за допомогою CREATE TABLEУ всіх трьох випадках дозволені прості, складені, унікальні та префіксні індекси (лише перші N символів VARCHAR) Або FULLTEXT, залежно від потрібного нам дизайну.
Використання префіксні індекси Це корисно, коли у нас довгі рядки, але відносно невеликої кількості символів достатньо, щоб розрізнити практично всі значення. Таким чином, ми зменшуємо розмір індексів, не втрачаючи надмірної вибірковості, що дуже корисно в таких стовпцях, як імена клієнтів, де ми можемо індексувати, наприклад, перші 25 символів замість усього поля.
Виберіть лише потрібні стовпці
Зловживання SELECT * Це одна з найпоширеніших поганих звичок у SQL. Вона зручна під час розробки, але у продакшені стає тягарем: Кожен додатковий стовпець означає, що з бази даних передається більше байтів залежно від вашої програми, більше пам'яті на клієнті та більше роботи з десеріалізації.
Коли таблиця містить великі стовпці (BLOB-об'єкти, великі JSON-файли, величезні текстові файли, бінарні аватари тощо), їх включення без потреби збільшує використання операцій вводу-виводу та оперативної пам'яті. Крім того, в таких рушіях, як PostgreSQL, обмеження кількості стовпців забезпечує кращу продуктивність. Сканування лише за індексом, де база даних відповідає з індексу, не звертаючись до купи, але це працює лише за умови, що всі запитувані вами стовпці є в індексі.
Класичний приклад: стіл users зі стовпцями типу ідентифікатор, електронна пошта, хеш_пароля, аватар, створено_за_адресою, останній_вхідЯкщо ви кинете SELECT * FROM users WHERE email = 'juan@example.com';Ви отримаєте хеш пароля та двійковий аватар, навіть якщо хочете показати лише електронну адресу та дату останнього входу. Набагато краще просто попросити про це. id, email, last_login.
Завжди працюйте з явні списки стовпців Це робить ваші запити зрозумілішими, захищає вас від змін схеми (додавання стовпця нічого не порушує) та значно зменшує споживання ресурсів у великих таблицях або списках із сторінками, допомагаючи керувати великими обсягами даних.
JOIN-запити, підзапити та CTE: як правильно структурувати складні запити
The корельовані підзапити (Ті, що виконуються один раз для кожного рядка зовнішнього запиту) можуть здаватися елегантними на папері, але на практиці вони стають вузьким місцем продуктивності зі зростанням таблиць. Кожен рядок у головній таблиці запускає додаткове виконання підзапиту, що призводить до астрономічної кількості операцій.
Коли це можливо, бажано перетворити ці підзапити на добре індексовані JOIN-з'єднання про ан CTE (загальні табличні вирази) які розбивають логіку на чіткі кроки. Оптимізатор зазвичай обробляє комбінацію таблиць набагато краще, ніж гніздо складних підзапитів.
Наприклад, щоб отримати товари разом з назвою їхньої категорії, замість виконання підзапиту в SELECT Ефективніше використовувати JOIN відносно таблиці категорій. Якщо стовпці об'єднання індексовані (наприклад, productos.categoria_id y categorias.id), двигун може вирішити об'єднання з дуже низькими витратами навіть на великих таблицях.
The КТР (WITH ... AS (...)Вони особливо корисні для запитів звітності, складних агрегацій та покрокової логіки. Хоча вони не завжди покращують продуктивність самі по собі, вони допомагають планувальнику та, перш за все, покращують читабельність, сприяючи подальшій оптимізації, такій як додавання певних індексів або матеріалізація проміжних результатів.
Пагінація та LIMIT для роботи з великими обсягами
У реальних застосунках повернення тисяч рядків одночасно майже ніколи не має сенсу з точки зору взаємодії з користувачем. Список товарів, історія замовлень або журнал подій зазвичай переглядаються сторінка за сторінкою, тому обмежити кількість повернутих рядків Це основна вимога для скелелазіння.
Класичний підхід використовує LIMIT y OFFSET (наприклад, LIMIT 10 OFFSET 20 щоб перейти на «третю» сторінку). Це легко реалізувати та зрозуміти, але є серйозна проблема: двигун повинен пройдіться по всіх рядках перед OFFSET таким самим чином.навіть якщо він повертає лише останні 10. У дуже великих таблицях високі значення OFFSET призводять до дедалі гіршого часу відгуку.
Під час роботи з сотнями тисяч або мільйонами рядків зазвичай краще Пагінація набору ключів або пагінація на основі пошукуУ цьому підході, замість того, щоб вказувати базі даних «пропустити 1000 рядків», ви вказуєте їй «повернути наступні N записів, починаючи з цього відсортованого значення ключа», використовуючи умови типу WHERE fecha_creacion < <última_fecha_vista> з одного ORDER BY послідовний.
Цей метод дозволяє механізму скористатися перевагами прямого індексування відсортованого стовпця (наприклад, fecha_creacion o id), уникаючи витрат на перегляд проміжних сторінок. Крім того, це спрощує пагінацію стабільний до вставок або делецій між сторінками, чого OFFSET не гарантує.
У свою чергу, пагінація набору ключів має той недолік, що Непросто перейти на сторінку 37 Без додаткової інформації, оскільки він працює вперед від логічного курсора (останній отриманий ідентифікатор або дата). Саме тому багато систем поєднують обидва підходи залежно від функціональних потреб.
Уникайте функцій у відфільтрованих стовпцях та ефективно використовуйте речення WHERE
Дуже поширеною причиною втрати продуктивності є застосування функції для стовпців, що беруть участь у фільтрахВирази типу LOWER(nombre), DATE(fecha) o CAST(campo AS ...) у рамках пункту WHERE Зазвичай вони перешкоджають оптимізатору використовувати індекс цього стовпця.
Натомість, краще нормалізувати дані під час вставки або оновлення (наприклад, збереження електронних листів у нижньому регістрі, статуси з однорідним кодуванням) та перетворювати вхідні значення відповідно до цього формату, замість застосування функції до стовпця в кожному порівнянні.
Також варто звернути увагу на сам пункт. WHERE щоб зробити його максимально вибірковим. Хоча порядок умов не завжди має прямий вплив (оптимізатор зазвичай змінює їх порядок), корисно мати добре індексовані предикати та прості порівняння замість дорогих візерунків, таких як LIKE '%texto'які зазвичай примусово виконують повне сканування.
Коли вам потрібно видалити дублікати, подумайте, чи DISTINCT або якщо запит можна було б переробити за допомогою JOINs точніші або унікальніші обмеження в моделі. Обидва DISTINCT як UNION зазвичай включають операції сортування або групуванняякі є одними з найдорожчих у плані впровадження.
Ведення індексів та статистики для допомоги оптимізатору
Сучасні механізми баз даних покладаються на внутрішня статистика Оцінити, скільки рядків відповідає кожній умові, які індекси є найбільш доцільними та в якому порядку об'єднувати таблиці. Якщо ця статистика застаріла, планувальник може приймати дуже погані рішення та генерувати неефективні плани виконання.
Ось чому важливо періодично виконувати такі команди, як ANALYZE (або їх конкретні варіанти в кожному двигуні) для Оновити статистику після масового завантаженняміграції або великі обсяги INSERT, UPDATE y DELETEНаприклад, у PostgreSQL автовакуумування зазвичай обробляється автоматично, але після великого імпорту може бути корисним виконати ANALYZE посібник.
У MySQL є такі оператори, як ANALYZE TABLE, який аналізує та зберігає розподіл ключів, щоб допомогти оптимізатору визначити порядок та використання індексів у JOINsКрім того, OPTIMIZE TABLE дозволяє дефрагментувати таблиці, перевпорядковувати та оновлювати індекси, що рекомендується в таблицях, що зазнали багатьох змін.
Щоб перевірити, чи двигун використовує індекси належним чином, немає нічого кращого, ніж витягування з EXPLAIN o EXPLAIN ANALYZEЦі інструменти показують нам приблизний план (а в деяких пошукових системах також фактичний план із часом та прочитаними рядками) та вказують, чи виконується послідовне сканування (ALL у MySQL, наприклад) або якщо Index Scanскільки рядків очікується і скільки фактично зіграно.
Навчитися читати ці плани, мабуть, одна з найцінніших навичок для тих, хто хоче оптимізувати бази даних: Це дозволяє виявляти вузькі місця, непотрібні індекси, погано вибіркові фільтри та погано впорядковані з'єднання. задовго до того, як проблема досягне виробництва.
Повнотекстові індекси, регулярні вирази та спеціальні сценарії
Коли ви працюєте з великі текстові поля (описи, розширений HTML-контент, коментарі тощо), пошук за допомогою LIKE '%palabra%' Вони швидко стають непрактичними для великих таблиць. Для таких випадків такі рушії, як MySQL, пропонують індекси типу FULLTEXT та такі оператори, як MATCH() AGAINST()що дозволяє здійснювати набагато ефективніший та релевантніший пошук.
з FULLTEXT Ви можете вибрати один з різних режимів: природна мова, булева (з операторами) +, -, *(лапки для точних фраз тощо) або розширення запиту розширити пов’язані результати. Це дозволяє створювати досить потужні внутрішні пошукові системи, не виходячи з бази даних.
Існують складніші сценарії, коли текст містить, наприклад, вбудовані теги HTML. У такому випадку може знадобитися об'єднати індекс. FULLTEXT з такими функціями, як REGEXP_REPLACE щоб очистити мітки під час порівняння точних фраз. Типова стратегія полягає в спочатку відфільтруйте, використовуючи повнотекстовий індекс а потім застосувати регулярний вираз у другій умові, щоб звузити результат до точної суми без сканування всієї таблиці.
Інші двигуни, такі як Oracle, дозволяють використовувати регулярні табличні вирази Ці функції допомагають оптимізатору вставляти предикати всередині представлень та якомога швидше зменшувати проміжний обсяг даних. Такий підхід дуже корисний під час роботи з багатьма вкладеними представленнями або складними визначеннями в середовищах спільної роботи.
Додаткові рекомендації: параметри, матеріалізовані представлення та розділення запитів
Окрім індексів та планів впровадження, існує низка належні міжгалузеві практики які сприяють як продуктивності, так і безпеці. Одним з найважливіших є використовувати параметризовані запити Замість об'єднання рядків для побудови динамічного SQL, це зменшує ризик SQL-ін'єкції та дозволяє базі даних повторно використовувати плани виконання для запитів з такою ж структурою.
У системах с дуже важкі та повторювані запити (інформаційні панелі, звіти для керівництва, агреговані розрахунки), матеріалізовані погляди Вони є чудовим союзником. На відміну від звичайного представлення, вони фізично зберігають результат запиту, стаючи своєрідною попередньо обчисленою таблицею, яку можна дуже швидко індексувати та запитувати.
PostgreSQL, Oracle та SQL Server (з їхніми індексованими представленнями) вбудовано підтримують матеріалізовані представлення з різними варіантами оновлення (ручним, запланованим і навіть автоматичним у деяких випадках). У MySQL, оскільки прямої підтримки немає, така поведінка зазвичай емулюється за допомогою таблиць і процесів, які періодично регенерують дані, часто за допомогою тригерів або запланованих завдань.
Коли запит об'єднує забагато таблиць або спирається на складну мозаїку подань, існує ще одна дійсна стратегія... розділити запит на кілька кроківЦе означає виконання початкового запиту для отримання меншого набору (наприклад, відповідних ідентифікаторів), а потім виконання додаткових запитів для повної інформації. Цей підхід слід використовувати розсудливо, оскільки він може збільшити кількість звернень до бази даних, але в деяких випадках він суттєво зменшує складність плану та розмір проміжних наборів.
Протягом цього процесу використовуються такі інструменти моніторингу, як pg_stat_statements, PgHero, PMM, Query Store, New Relic або Datadog Вони можуть допомогти вам швидко визначити, які запити працюють повільніше або виконуються частіше, щоб ви могли визначити пріоритети оптимізації там, де це дійсно важливо.
Оптимізуйте SQL-запити за допомогою штучного інтелекту
В останні роки з'явилися інструменти на основі штучного інтелекту які аналізують ваші запити та схему бази даних, щоб запропонувати покращення: пропозиції щодо індексів, переписування запитів, зміни в структурі таблиць тощо. Такі назви, як EverSQL, DBScoop, PGAnalyzer або Redshift Advisor, стали популярними в професійному середовищі.
Ці рішення можуть переглядати великі обсяги журналів запитів, зіставляти їх зі статистикою, планами виконання та показниками продуктивності, і на основі цього виявляти неефективні закономірності або вузькі місця що на перший погляд вислизає від нас. Вони також допомагають оцінити гіпотетичний вплив створення або скасування певних індексів.
Однак важливо розуміти їх як підтримка, а не заміна Це залежить від ваших знань SQL та розуміння вашої програми. Ви можете отримати пропозицію індексу, яка теоретично пришвидшує певний запит, але значно погіршує запис у критичний модуль. Без бізнес-контексту інструмент не знає, що найважливіше.
Ідеальне поєднання – це команда, яка володіє принципами оптимізації (плани, індекси, нормалізація, шаблони доступу) та використовує штучний інтелект для… прискорити аналіз та перевірити гіпотезине приймати рішень наосліп.
Коли ви засвоюєте весь цей набір методів — ретельне проектування індексів, мінімальний вибір стовпців, розумне використання JOIN та CTE, ефективну пагінацію, регулярне ведення статистики, використання матеріалізованих представлень і навіть підтримку інструментів штучного інтелекту — Великі бази даних більше не є неконтрольованим монстром і вони стають передбачуваним і масштабованим компонентом вашої архітектури, здатним зростати разом з вашим бізнесом, не руйнуючи взаємодію з користувачем чи бюджет на інфраструктуру.
