на лошо формулирани SQL заявки Това са едни от най-честите причини, поради които дадено приложение работи бавно при работа с големи релационни бази данни като MySQL, PostgreSQL, SQL Server, Oracle или DB2. Въпреки че сега разполагаме с мощни сървъри и еластични облаци, неефективните заявки в крайна сметка ще ви струват скъпо. по-високи разходи за инфраструктура, по-висока латентност и по-лошо потребителско изживяване.
Оптимизирането на SQL заявки в големи бази данни далеч надхвърля просто „добавяне на индекс и това е всичко“. То включва Разбиране на начина, по който мисли оптимизаторът на заявкиКак се съхраняват данните, какви модели на достъп използва вашето приложение и какви комбинирани техники ви позволяват да намалите използването на входно/изходни операции, процесор и памет. В следващите раздели ще разгледаме подробно и с примери, Най-ефективните стратегии за извличане на максимална полза от вашите релационни бази данни.
Какво всъщност е оптимизация на SQL заявки и защо е важна?
Оптимизиране на SQL заявка Това означава пренаписването му (и коригирането на контекста му: индекси, статистика, дизайн), така че енджината да връща същия резултат, като същевременно консумира по-малко ресурси и за по-кратко време. Синтаксисът на SQL позволява много начини за изразяване на едно и също нещо, но не всички от тях се изпълняват еднакво бързо, особено когато има... милиони редове или сложни съединения.
Когато разработчикът разбира как работи планирач на заявки С вашия енджин (PostgreSQL, MySQL, SQL Server, Oracle, DB2 и др.) можете да пишете заявки, които използват по-добре индексите, намаляват ненужните четения и минимизират скъпоструващите операции като сортиране, последователни сканирания или повтарящи се корелирани подзаявки.
Важно е обаче да е ясно, че Оптимизацията на заявките не е единственият фактор за производителностДизайнът на схемата (нормализация, първични и външни ключове, типове данни), архитектурата (реплики, дялове, кешове) и самата инфраструктура имат значително влияние. Но дори и с добра архитектура, една-единствена лошо оптимизирана заявка може да се окаже сериозен проблем. брутално пречка.
Сред предимствата на работата в консултации се открояват следните: подобрение на цялостната производителност (повече заявки, обработени за по-кратко време), намаляване на разходите за облачни услуги (по-малко процесор и диск, по-малки размери на инстанциите) и a по-плавно потребителско изживяване чрез намаляване на времето за чакане в обяви, търсения и отчети. Освен това, ясните и добре структурирани заявки са по-лесен за поддръжка и отстраняване на грешки, нещо, което е високо ценено, когато проектът се разрасне.
В приложения, които наистина се стремят към мащабиране, непрекъснатата оптимизация на заявките се превръща в повтаряща се задача: наблюдава, открива, измерва, коригира и преизмерваТова не е еднократно действие, а процес.
Практически пример: същата заявка, много различна производителност
За да осъществите идеите си на практика, представете си маса поръчки с повече от 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, двигателят ще бъде принуден да сканира голяма част от таблицата.
Практическите последици са ясни: Прехвърлени са повече данни от необходимотоПовече работа за backend картографирането на неизползвани колони, много четене на диска и време за изпълнение, което в много големи таблици може да скочи до няколко секунди, което се отразява на цялата система при многократно стартиране.
Същият въпрос, формулиран по-интелигентно, би могъл да изглежда така:
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 (ако има висока кардиналност), системата може да използва сканиране на индекси и да разреши заявката в милисекунди вместо секунди.
Този контраст илюстрира ключова идея: Не е достатъчно заявката да „работи“Трябва да се притеснявате как работи, когато таблицата вече няма стотици редове, а милиони.
Индекси: основният лост за ускоряване на търсенето
Лос Индексите са най-мощният инструмент за ускоряване на заявките в големи бази данни. Вместо да се обхожда цялата таблица ред по ред (последователно сканиране или Seq сканиране), двигателят използва помощни структури (обикновено 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) o FULLTEXT, в зависимост от дизайна, от който се нуждаем.
El USO префиксни индекси Това е полезно, когато имаме дълги низове, но относително малък брой знаци е достатъчен, за да различим почти всички стойности. По този начин намаляваме размера на индексите, без да губим твърде много селективност, което е много полезно в колони като имена на клиенти, където можем да индексираме например първите 25 знака вместо цялото поле.
Изберете само колоните, от които се нуждаете
Злоупотреба SELECT * Това е един от най-често срещаните лоши навици в SQL. Удобен е по време на разработка, но в продуктивна среда се превръща в бреме: Всяка допълнителна колона означава, че повече байтове пътуват от базата данни до вашето приложение, повече памет на клиента и повече работа по десериализация.
Когато една таблица съдържа големи колони (BLOB файлове, големи JSON файлове, огромни текстови файлове, двоични аватари и др.), включването им ненужно увеличава използването на I/O и RAM. Освен това, в двигатели като PostgreSQL, ограничаването на броя на колоните позволява по-добра производителност. Сканиране само на индекса, където базата данни отговаря от индекса, без да отива в heap-а, но това работи само ако всички колони, които заявявате, са в индекса.
Класически пример: маса users с колони като идентификатор, имейл, хеш_на_парола, аватар, създаден_на_адреса, последен_входАко хвърлите SELECT * FROM users WHERE email = 'juan@example.com';Ще получите хеша на паролата и двоичния аватар, дори ако искате да покажете само имейла и датата на последно влизане. Много по-добре е просто да поискате това. id, email, last_login.
Винаги работете с явни списъци с колони Това прави заявките ви по-ясни, предпазва ви от промени в схемата (добавянето на колона не нарушава нищо) и драстично намалява потреблението на ресурси в големи таблици или номерирани списъци, помагайки за... управлява големи количества данни.
JOIN-ове, подзаявки и CTE-ове: как правилно да структурирате сложни заявки
на свързани подзаявки (Тези, които се изпълняват веднъж за всеки ред от външната заявка) може да изглеждат елегантни на хартия, но на практика те се превръщат в пречка за производителността с нарастването на таблиците. Всеки ред в главната таблица задейства допълнително изпълнение на подзаявката, което води до астрономически брой операции.
Винаги, когато е възможно, е за предпочитане тези подзаявки да се трансформират в добре индексирани JOIN-ове о EN CTE (общи таблични изрази) които разделят логиката на ясни стъпки. Оптимизаторът обикновено обработва комбинация от таблици много по-добре, отколкото гнездо от сложни подзаявки.
Например, за да получите продуктите заедно с името на категорията им, вместо да правите подзаявка в SELECT По-ефективно е да се използва JOIN спрямо таблицата с категории. Ако колоните за свързване са индексирани (например, productos.categoria_id y categorias.id), двигателят може да реши съединението с много ниска цена дори на големи таблици.
на CTEs (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'които обикновено налагат пълно сканиране.
Когато трябва да премахнете дубликати, помислете дали a DISTINCT или ако заявката може да бъде преработена с JOINs по-точни или уникални ограничения в модела. И двете DISTINCT като UNION обикновено включват операции за сортиране или групиранекоито са сред най-скъпите в плана за изпълнение.
Поддържане на индекси и статистика в помощ на оптимизатора
Съвременните двигатели на бази данни разчитат на вътрешна статистика Да се оцени колко реда отговарят на всяко условие, кои индекси са най-подходящи и в какъв ред да се съединят таблиците. Ако тези статистики са остарели, планировчикът може да взема много лоши решения и да генерира неефективни планове за изпълнение.
Ето защо е важно периодично да се изпълняват команди като ANALYZE (или техните специфични варианти във всеки двигател) за Обновяване на статистиката след големи зарежданиямиграции или големи обеми INSERT, UPDATE y DELETEВ PostgreSQL, например, автоматичното вакуумиране обикновено се обработва автоматично, но след голям импорт може да е полезно да се изпълни a ANALYZE ръководство.
В MySQL имаме изрази като ANALYZE TABLE, който анализира и съхранява разпределението на ключовете, за да помогне на оптимизатора да реши реда и използването на индекси в JOINsОсвен това, OPTIMIZE TABLE позволява дефрагментиране на таблици, пренареждане и актуализиране на индекси, нещо, препоръчано в таблици, които са претърпели много промени.
За да проверите дали двигателят използва индексите както се очаква, няма нищо по-хубаво от издърпването от EXPLAIN o EXPLAIN ANALYZEТези инструменти ни показват прогнозния план (а в някои двигатели и действителния план с прочетени времена и редове) и показват дали се извършва последователно сканиране (ALL в MySQL, например) или ако a Index Scanколко реда се очакват и колко реално се изиграват.
Да се научите да четете тези планове е може би едно от най-ценните умения за всеки, който иска да оптимизира бази данни: Това ви позволява да откривате пречки, безполезни индекси, лошо селективни филтри и лошо подредени съединения. много преди проблемът да достигне до производството.
Пълнотекстови индекси, регулярни изрази и специални сценарии
когато работите с големи текстови полета (описания, богато HTML съдържание, коментари и др.), търсения с LIKE '%palabra%' Те бързо стават непрактични за големи таблици. За тези случаи, двигатели като MySQL предлагат индекси от тип FULLTEXT и оператори като MATCH() AGAINST()които позволяват много по-ефективно и релевантно търсене.
Con FULLTEXT Можете да избирате между различни режими: естествен език, булева (с оператори) +, -, *(кавички за точни фрази и др.) или разширяване на заявката за разширяване на свързани резултати. Това ви позволява да изграждате доста мощни вътрешни търсачки, без да е необходимо да напускате базата данни.
Има по-сложни сценарии, при които текстът включва например вградени HTML тагове. В този случай може да се наложи комбиниране на индекс. FULLTEXT с функции като REGEXP_REPLACE за почистване на етикетите при сравняване на точни фрази. Типична стратегия е филтрирайте първо, използвайки индекса за пълен текст и след това приложете регулярния израз във второ условие, за да стесните резултата до точната сума, без да сканирате цялата таблица.
Други двигатели, като Oracle, позволяват използването на регулярни таблични изрази Тези функции помагат на оптимизатора да вмъква предикати в изгледите и да намали междинния обем на данните възможно най-бързо. Този подход е много полезен при работа с много вложени изгледи или сложни дефиниции в среди за съвместна работа.
Допълнителни най-добри практики: параметри, материализирани изгледи и разделяне на заявки
Освен индексите и плановете за изпълнение, съществуват редица добри междусекторни практики които допринасят както за производителността, така и за безопасността. Едно от най-важните е използвайте параметризирани заявки Вместо конкатениране на низове за изграждане на динамичен SQL, това намалява риска от SQL инжектиране и позволява на базата данни да използва повторно планове за изпълнение за заявки със същата структура.
В системи с много тежки и повтарящи се запитвания (табла за управление, отчети за ръководни кадри, агрегирани изчисления), материализирани възгледи Те са чудесен съюзник. За разлика от обикновения изглед, те физически съхраняват резултата от заявката, превръщайки се в един вид предварително изчислена таблица, която може да бъде индексирана и заявена много бързо.
PostgreSQL, Oracle и SQL Server (с техните индексирани изгледи) поддържат материализирани изгледи с различни опции за обновяване (ръчно, планирано и дори автоматично в някои случаи). В MySQL, тъй като няма директна поддръжка, това поведение обикновено се емулира с таблици и процеси, които периодично регенерират данните, често чрез тригери или планирани задачи.
Когато заявка свързва твърде много таблици или разчита на сложна мозайка от изгледи, друга валидна стратегия е разделете заявката на няколко стъпкиТова се изразява в изпълнение на първоначална заявка за получаване на по-малък набор (напр. съответните идентификатори) и след това изпълнение на допълнителни заявки за допълване на информацията. Този подход трябва да се използва разумно, тъй като може да увеличи броя на достъпите до базата данни, но в някои случаи драстично намалява сложността на плана и размера на междинните набори.
По време на този процес, инструменти за мониторинг, като например pg_stat_statements, PgHero, PMM, хранилище за заявки, нова реликва или Datadog Те могат да ви помогнат бързо да идентифицирате кои заявки са по-бавни или се изпълняват по-често, така че да можете да приоритизирате усилията за оптимизация там, където е наистина важно.
Оптимизирайте SQL заявки с помощта на изкуствен интелект
През последните години се появиха инструменти, базирани на изкуствен интелект които анализират вашите заявки и схемата на базата данни, за да предложат подобрения: предложения за индексиране, пренаписване на заявки, промени в структурата на таблиците и др. Имена като EverSQL, DBScoop, PGAnalyzer или Redshift Advisor са станали популярни в професионални среди.
Тези решения могат да преглеждат големи обеми от регистрационни файлове на заявки, да ги сравняват със статистики, планове за изпълнение и показатели за производителност и оттам откриване на неефективни модели или пречки това би ни убягнало на пръв поглед. Те също така помагат да се оцени хипотетичното въздействие от създаването или премахването на определени индекси.
Важно е обаче да ги разбираме като подкрепа, а не като заместител Зависи от вашите познания за SQL и разбирането на приложението ви. Може да получите предложение за индекс, което на теория ускорява конкретна заявка, но значително влошава записите в критичен модул. Без бизнес контекст инструментът не знае кое е най-важно.
Идеалната комбинация е екип, който владее принципите на оптимизация (планове, индекси, нормализация, модели на достъп) и използва изкуствен интелект за... ускоряване на анализа и валидиране на хипотезида не вземаме решения на сляпо.
Когато интернализирате целия този набор от техники – внимателно проектиране на индекси, минимален избор на колони, интелигентно използване на JOIN и CTE, ефективно номериране на страници, редовна поддръжка на статистика, използване на материализирани изгледи и дори поддръжка от инструменти с изкуствен интелект – Големите бази данни вече не са неконтролируемо чудовище и те се превръщат в предвидим и мащабируем компонент на вашата архитектура, способен да расте заедно с вашия бизнес, без да разваля потребителското изживяване или бюджета за инфраструктура.
