Како оптимизовати SQL упите на практичан и ефикасан начин

Када апликација почне да се споро извршава, многи људи се окрећу серверу, мрежи или чак корисниковој машини... али у многим случајевима, прави кривац су лоше дизајнирани SQL упити . Једноставна промена WHERE клаузуле или индекса може направити разлику између времена одзива од милисекунди или неколико секунди.

Најтежи део је то што утврђивање зашто је упит спор може потрајати сатима или данима : преглед планова извршавања, тестирање варијација, анализа проблема са блокирањем, статистика и тако даље. Добра вест је да постоје веома уобичајени обрасци дизајна који скоро увек узрокују проблеме са перформансама, а њихово брзо препознавање ће вам уштедети огромну количину времена за дијагностику.

SQL обрасци који обично веома лоше функционишу

Постоје одређени SQL обрасци кода који представљају праву бомбу за перформансе и треба их пажљиво пратити: сложени ИЛИ, претраге са џокерима у стринговима, масивна писања, упити са превише табела или злоупотреба назнака, између осталог.

Практични циљ је да, када наиђете на апликацију која споро ради, можете скенирати упите и брзо пронаћи те опасне обрасце како бисте усмерили тестирање тачно тамо где је проблем највероватније узрок.

Увек би требало да валидирате помоћу стварних мерења (времена извршавања, логичка читања, коришћење процесора итд.), али знање где започети истрагу знатно скраћује процес.

Проблеми са OR у JOIN и WHERE на више колона

Логички оператори могу деловати невино, али коришћење ИЛИ на више колона или табела може уништити ефикасност , посебно у системима попут SQL Server-а, иако се идеја односи и на друге системе.

Док су услови И међусобно искључиви и омогућавају постепено смањење скупа података, ИЛИ је инклузиван и приморава механизам да процени сваку грану засебно . Што се тиче плана извршења, ово се претвара у више пролазака кроз табеле и, често, претерано велики број читања.

Најозбиљнији случај се јавља када оператор OR комбинује више колона или колона из више табела . Оптимизатор је приморан да прати сваку путању оператора OR, а затим комбинује резултате. У табелама са стотинама хиљада редова, ово може довести до милиона логичких читања, чак и ако саме табеле нису толико велике.

Уобичајена тактика за побољшање ових ситуација је елиминисање OR клаузуле преписивањем упита у неколико наредби , свака са својом SELECT наредбом, које се потом спајају са UNION или UNION ALL. Свака SELECT наредба затим може бити појединачно оптимизована од стране претраживача.

У пракси, то значи да једноставни ИЛИ упити могу постати неколико мало дужих упита , али заузврат, план је обично стабилнији, са мање читања и краћим временима извршавања. Цена је што понекад читате исте табеле више пута; чак и тако, побољшање обично надмашује трошкове, посебно када вас ИЛИ клаузула спречава да искористите предности одговарајућих индекса.

Кључно је да се не ослањате на ИЛИ услове у различитим колонама или више табела . Ако ревидирате споре упите и видите ИЛИ клаузуле раштркане по клаузулама JOIN или WHERE, сматрајте их главним осумњиченим и тестирајте их одвојено у више упита.

Претраге џокер знакова и претраге целог текста

Претраге текста су још један класичан пример проблема са перформансама. Претраживање произвољних подстринга унутар колона текста је инхерентно скупо : претраживач нема начин да „скочи“ на исправну тачку и завршава скенирањем ред по ред и знак по знак.

За често приступане текстуалне колоне, вреди размотрити неколико основних питања: Да ли постоје индекси на овим колонама? Да ли образац претраге дозвољава коришћење ових индекса? Можемо ли користити FULLTEXT индексе или алтернативно решење попут хешева или n-грама?

У базама података као што су SQL Server или MySQL, постављање џокера % на почетак обрасца прекида употребу конвенционалних индекса B-стабла . То јест, клаузула WHERE LastName LIKE '%For%' приморава на потпуно скенирање, чак и ако имате индекс на LastName. Исто важи и за обрасце који се завршавају са %, ​​али у опадајућем редоследу: предност редоследа индекса се губи.

У малим табелама то можда и не представља проблем, али у табелама са милионима редова, секвенцијално скенирање за сваку претрагу представља огромно уско грло . Зато дизајн ових упита треба пажљиво размотрити.

Пре него што се упустимо у сложене оптимизације, веома је корисно преиспитати сам функционални захтев: да ли корисник заиста треба да претражује било где унутар стринга? Понекад су довољне претраге по префиксу („For%“ уместо „%For%“), или можете наметнути употребу других филтера (по датуму, статусу, категорији итд.) који драстично смањују број редова које је потребно проверити.

Поред прилагођавања функционалног дизајна, индекси целог текста нуде моћну алтернативу када су претраге текста честе или сложене. Они вам омогућавају да пронађете речи и фразе и извршите напредније лингвистичке претраге користећи одређене операторе и генерално раде на структурама података оптимизованим за текст.

Међутим, индексирање целог текста је додатна функција: потребно га је инсталирати, конфигурисати и одржавати . У апликацијама које су у великој мери фокусиране на текстуални садржај, то је често веома исплатива инвестиција, али додаје сложеност и трошкове одржавања.

За релативно кратке низове знакова, као што су имена или кодови, могу се користити и n-грам технике : свака вредност се дели на мале фрагменте фиксне дужине (нпр. 3 карактера) који се чувају у посебној табели, заједно са референцом на оригинални ред.

На овај начин, уместо скенирања огромне NVARCHAR табеле , врши се тачна претрага на n-грам табели, која се може добро индексирати. Оригинални редови се затим преузимају помоћу идентификатора. Цена ове технике је двострука: прво, n-грам табела може веома брзо да расте , и друго, мора се одржавати са сваким уметањем, ажурирањем или брисањем, што је чини разумном само за кратке текстове.

Укратко за овај блок: претраге са интерним џокерима су скупе по дизајну , и најбоље што можете да урадите је да прилагодите дизајн апликације (уклоните почетне џокере, додате филтере) или користите специјализоване структуре попут FULLTEXT или n-грама када заиста нема алтернативе.

Индекси: врсте, дизајн и одржавање

Ако постоји једна полуга перформанси која се стално појављује, то је индексирање. Најдиректнији начин да се убрза упит јесте да се креирају одговарајући индекси на колонама које се појављују у клаузули WHERE и условима JOIN . Али, као и све у базама података , постоји једна зачкољица: превише индекса је такође проблем.

Индекс је у основи структура (обично Б-стабло) која вам омогућава да брзо пронађете редове који испуњавају услов без потребе за читањем целе табеле. MySQL, на пример, чува већину својих индекса у Б-стаблима: PRIMARY KEY, UNIQUE, INDEX, а такође и FULLTEXT (иако имају своје интерне карактеристике).

Поред индекса заснованих на B-стаблима, MySQL користи R-стабла за просторне податке и хеш индексе за табеле у меморији . Свака структура има своје предности у зависности од типа података и обрасца приступа: B-стабла су идеална за опсеге и сортирање; R-стабла за просторне упите; а хеш индекси за веома брзе претраге једнакости у меморији.

Што се тиче врста логичких индекса, најчешћи су: примарни кључеви, страни кључеви, јединствени индекси, нормални индекси, индекси са више колона, индекси целог текста и функционални индекси . Сваки од њих служи другачијој потреби и важно је разумети њихове импликације и за читање и за писање.

На пример, композитни индекс на (contact_last_name, contact_first_name) биће користан када претражујете само по презимену или и по презимену и по имену , али неће помоћи ако филтрирате само по имену. Овакви детаљи праве велику разлику при дизајнирању индекса табела.

Такође постоји опција индексирања само префикса из текстуалне колоне како би се смањила величина индекса. Ако је `nombre_cliente` VARCHAR(50), можда ће бити довољно индексирати првих 20 или 25 знакова, под условом да се већина вредности разумљиво разликује унутар тих знакова. Циљ је пронаћи равнотежу између селективности и величине индекса.

У MySQL-у, почев од верзије 8.0.13, можете креирати функционалне индексе на основу резултата израза . Ово је посебно корисно за упите који користе функције попут YEAR(payment_date) у WHERE клаузули: уместо разбијања индекса на колони payment_date, креирате директан индекс на YEAR(payment_date), а оптимизатор може то искористити.

Индекси се могу креирати помоћу CREATE INDEX, ALTER TABLE или директно у дефиницији табеле помоћу CREATE TABLE . Свака опција је корисна у различитим временима: почетно креирање шеме, накнадно рефакторисање или специфична подешавања перформанси.

Такође је кључно бити у могућности да проверите који индекси постоје и како се користе . У MySQL-у можете користити SHOW INDEX или DESCRIBE да бисте видели индексе и типове кључева, и команде попут EXPLAIN да бисте проверили да ли упит заправо користи те индексе или и даље врши потпуно скенирање (тип = ALL, веома велики број редова итд.).

Што се тиче одржавања, имате алате као што су OPTIMIZE TABLE и ANALYZE TABLE . OPTIMIZE помаже у дефрагментацији табеле и реорганизацији индекса, док ANALYZE поново израчунава дистрибуцију кључева, што је основа за многе одлуке оптимизатора (редослед спајања, избор индекса итд.). Њихово покретање након великих учитавања или великих промена помаже у одржавању разумних планова извршавања.

Међутим, постоје уобичајене грешке које треба избегавати: прекомерно индексирање табеле, остављање табеле готово без икаквих индекса или недостатак груписаног индекса/примарног кључа . Превише индекса кажњава писање (свака операција INSERT, UPDATE или DELETE мора да их све ажурира) и заузима много простора на диску и резервних ресурса. С друге стране, премало индекса приморава на континуирано читање целе табеле.

У SQL Server-у, сам механизам такође предлаже недостајуће индексе на основу планова извршења , било путем Management Studio-а, XML датотеке плана или динамичких приказа. Ове препоруке су корисне као полазна тачка, али их треба критички размотрити: често предлажу превелике индексе са много INCLUDE колона или дуплирају сличне индексе.

Пре него што прихватите предлог за недостајући индекс, вреди се запитати: Да ли већ постоји сличан индекс који би се могао проширити? Да ли су ми потребне све INCLUDE колоне? Који је процењени утицај на побољшање? Да ли се овај упит покреће довољно често да би се оправдао?

Коначно, ако ваша апликација има табеле без груписаног индекса или примарног кључа, то би требало да упозори на проблем . Чисти хипови обично лошије функционишу за многа радна оптерећења и отежавају креирање ефикасних негруписаних индекса. Дефинисање доброг примарног кључа и груписаног индекса је обично висок приоритет пре него што се пређе на финије подешавање.

Масивно писање, блокови и раст логова

Није све у SELECT наредбама. Операције писања великих размера такође могу изазвати озбиљне проблеме са перформансама и сукобима . Велика ажурирања, уметања или брисања могу закључати целе табеле на дуже време, драстично повећати величину дневника трансакција и оставити друге кориснике да чекају.

Сваки пут када измените податке, систем поставља закључавања како би осигурао конзистентност и спречио сукобе . Ово је добро за интегритет, али када операција траје предуго, постаје уско грло: други упити се блокирају, јављају се истека времена, а убрзо следе жалбе да је „база података ужасна“.

Шта представља „велику операцију“? То у великој мери зависи од шеме: броја индекса, окидача, страних кључева итд. У једноставној табели, 100.000 редова може бити управљиво у једној трансакцији; у табели са много ограничења, 2.000 редова већ може бити проблем. Једини поуздан начин да се то сазна јесте да се тестира у стварним или веома сличним условима.

Поред закључавања, масивна писања узрокују брзи раст дневника трансакција . Ако не пратите његову величину, могли бисте завршити са пуним дневником или чак са самим диском. Ово је посебно критично током задатака одржавања, ETL учитавања или миграција, где се много писања дешава у кратком периоду.

Разуман приступ је разбити велике операције на мање групе . Уместо ажурирања милион редова одједном, то радите у групама (на пример, 10.000) са међуизменама, чиме се смањује трајање закључавања и величина сваке трансакције у дневнику. За процесе ван шпица (прозоре одржавања), можете себи приуштити веће групе; у продукцији ћете можда морати бити прилично конзервативни.

Такође је вредно прегледати које операције генеришу масовна писања: додавање и попуњавање нових колона, промена типова података, увоз, историјске датотеке и периодично чишћење . Разумевање њиховог утицаја вам помаже да планирате различите временске оквире, прилагодите величине група и избегнете непријатна изненађења током имплементације или критичног одржавања.

Упити са много табела и експлозијом плана

Још један образац који изазива доста главобоља је коришћење масивних упита који спајају огроман број табела . SQL оптимизатори (SQL Server, Oracle, DB2, MySQL, итд.) су дизајнирани да брзо пронађу „добар“ план, али простор претраге расте експоненцијално са сваком додатном табелом.

У упиту са много табела, оптимизатор мора да одлучи којим редоследом ће их спојити, коју врсту спајања ће користити у сваком случају, када ће применити филтере и агрегације и тако даље. Број могућих планова расте факторијално, или још горе, у зависности од облика стабла спајања (линеарније или разгранатије).

На пример, са око 12 табела већ можете елиминисати десетине милијарди могућих теоријских планова ако је упит веома густ. Очигледно је да оптимизатор не истражује све, већ мора брзо да сузи многе опције и понекад се заустави на кандидату који није најбољи, једноставно зато што не може да посвети више времена претраживању.

То не значи да су сви сложени упити лоши, али свака додатна табела повећава ризик да ваш изабрани план неће бити оптималан . Штавише, одржавање и дебаговање SQL-ова са 20, 30 или 40 табела је ноћна мора за сваки тим.

Стратегије за побољшање овог сценарија укључују: премештање метаподатака или табела за претрагу како би се одвојили упити који своје резултате чувају у привременим табелама, елиминисање непотребних спајања, подела упита на неколико мањих и, у веома честим случајевима употребе, креирање индексираних приказа који унапред израчунавају део посла.

Када поделите велики упит на неколико мањих, морате се уверити да нема значајних промена података између њих које би поништиле резултат . Ово може захтевати употребу трансакција, одговарајућих нивоа изолације или експлицитних закључавања, у зависности од механизма и критичности података.

Међутим, у многим случајевима је могуће реорганизовати преузимање података у мање, разумљивије логичке јединице : прво, преузимате кључни подскуп (на пример, ИД-ове који испуњавају одређене услове), а затим, у другом упиту, преузимате детаље. Ово вам такође омогућава да уклоните непотребне колоне и поједноставите логику.

Опште најбоље праксе за писање упита

Поред веома специфичних образаца, постоји низ општих препорука које имају тенденцију да прилично доследно побољшају перформансе. На пример, избегавајте `SELECT *` и изаберите само колоне које су вам заиста потребне . Свака додатна колона значи више података који се крећу преко мреже, више меморије, веће улазно/излазно оптерећење и понекад вас спречава да оптимално користите одређене индексе.

Још једна важна ствар је избегавање прекомерне употребе DISTINCT и UNION када нису потребни . Оба оператора укључују операције сортирања или дедупликације, које су међу најскупљим деловима упита. У многим ситуацијама, UNION ALL (који не врши дедупликацију) је довољан и много бржи.

Што се тиче JOIN-ова, пожељније је користити INNER JOIN-ове када вам заправо нису потребни „сирочад“ редови из једне од табела . Спољашњи спајања (LEFT/RIGHT OUTER JOIN-ови) ограничавају флексибилност оптимизатора и често доводе до мање ефикасних планова. Штавише, предикати из спољашње табеле треба да буду смештени у ON клаузулу, а не у WHERE клаузулу, како би оптимизатор могао да их правилно примени.

Такође је препоручљиво дуплирати константне услове на спојеним колонама обе табеле када је то могуће (на пример, A.id = B.id и A.id IN (10,12) и B.id IN (10,12)). Ово даје оптимизатору додатне назнаке о опсегу релевантних вредности у свакој табели и може побољшати избор индекса и редослед извршавања.

Клаузула ORDER BY треба да се користи само када заиста треба да се поређају резултати. Без ORDER BY, редослед враћеног скупа није загарантован , чак и ако понекад изгледа као да је случајан. Свака ORDER BY клаузула подразумева поређавање, а са великим скуповима резултата, ово може постати један од најскупљих корака у упиту.

У Oracle-у можете користити и уобичајене табеларне изразе (CTE) и специфичну синтаксу регуларних израза како бисте помогли оптимизатору да креира ефикасније средње привремене табеле. Преписивање одређених упита помоћу добро дизајнираних CTE-ова може омогућити оптимизатору да „гурне“ предикате надоле у ​​приказе, филтрирајући податке раније и смањујући величину накнадних спајања.

ОБЈАШЊЕЊЕ, статистика и предлози за консултације (савети)

Један алат који би требало да буде у сваком алатном сету је EXPLAIN (и његове варијанте попут EXPLAIN ANALYZE). EXPLAIN вам показује како претраживач планира да изврши упит : које индексе користи, тип приступа (ALL, index, ref, range…), колико редова процењује да ће прочитати, редослед спајања и тако даље.

Са тим информацијама можете видети, на пример, да ли се ваш потпуно нови индекс земаља користи или не , да ли упит и даље врши потпуно скенирање табеле (тип = СВЕ), да ли је број редова претерано висок или да ли се уместо претраге СВИЂА употребљава FULLTEXT.

У MySQL-у, након креирања индекса и поновног покретања EXPLAIN-а, требало би да видите промену у колони `type` ка селективнијим вредностима (ref, range, итд.) и приметно смањење процене реда . Ова поређења пре и после су непроцењива за проверу да ли ваша оптимизација функционише.

Још један кључни елемент је расподела вредности статистике (АНАЛИЗА ТАБЕЛЕ, аутоматско ажурирање статистике итд.). Оптимизатор у великој мери одлучује који план ће изабрати на основу ове статистике; ако је застарела, можете завршити са веома лошим плановима. Након великих оптерећења или масовних промена, препоручљиво је експлицитно ажурирати статистику.

Што се тиче предлога упита или назнака, разуман приступ је да се користе као последње средство и са великом уздржаношћу . Назнака је експлицитна инструкција оптимизатору: она приморава тип спајања (MERGE, HASH, LOOP), вредност параметра за оптимизацију (OPTIMIZE FOR), ниво изолације (NOLOCK), спречава поновну употребу плана (RECOMPILE) итд.

Проблем је што наговештај који данас решава гранични случај може постати препрека сутра када се подаци, шема или обрасци коришћења промене. Штавише, могу прикрити дубље проблеме: недостајуће индексе, непотребну количину података, лоше дизајнирану пословну логику и тако даље.

Нека типична упозорења: NOLOCK може вратити недоследне податке (прљава читања), тако да га не треба користити тамо где је квалитет података важан; RECOMPILE на веома честом упиту може генерисати брутално оптерећење; форсирање HASH/MERGE/LOOP ограничава опције оптимизатора и може довести до ужасних планова на средњи рок; и OPTIMIZE FOR може постати застарео чим се промене обрасци коришћења апликације.

Разуман начин рада је да се прво исцрпе „чисте“ алтернативе (одговарајући индекси, преписивање SQL-а, подешавање параметара, ажурирање статистике) и само ако нема другог излаза, примени веома специфичан савет, добро документован и периодично преиспитан.

Генерално, оптимизација SQL упита је комбинација темељног разумевања начина рада система, препознавања ризичних образаца (компликоване ИЛИ наредбе, погрешно постављени џокери, прекомерне табеле, лоше дизајнирани индекси) и коришћења алата попут EXPLAIN-а и системских приказа за доношење информисаних одлука. Комбиновањем доброг дизајна индекса, чистих упита и извесног степена процене како би се избегли типични анти-обрасци, ваше базе података ће реаговати много брже без потребе за огромним хардвером или чудотворним решењима.