Википедија

Оптимизација упита

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

Упит (енгл. Query) је захтев за информацијама из базе података. Резултати упита се генеришу приступањем и манипулацијом релевантних података унутар базе података у зависности од тражене информације. Пошто је структура база података комплексна, у већини случајева, упити су комлексни и понекад захтев за подацима из упита могу бити прикупљани од стане различитих база података којима се приступа на различите начине. Сваки другачији начин приступа бази података захтева различито време обраде. Па тако, време обраде може варирати од једног делића секунде до више сати, зависно од изабраног начина приступа бази података. Сврха оптимизације упита, који је аутоматизован процес, јесте да се пронађе начин да се дати упит обради у минималном времену. Проналажење тачног оптималног пута за извршавање упита, међу свим могућим, је веома сложен, дуготрајан процес, који може пуно да кошта по цени времена а често и не може да се пронађе. Тако оптимизација упита обично покушава проналазак оптималног пута упоређивањем неколико разумних алтернатива које се одвијају у разумном року и да тако пронађе "довољно добар" план који обично много не одступа од најбољег могућег резултата.

Историја уреди

Историја оптимизације упита почиње настанком првих упитних језика, па је тако развијањем база података и упитних језика за те базе података, развијана и оптимизација упита.

Узроци лоших перформанси упита уреди

Ситуације које лоше утичу на перформансе SQL (енгл. Structured Query Language) упита а самим ти и саме апликације могу бити различите, а неке од њих су: лоше индексирање, нетачна статистика, превише међусобних блокирања трансакција и застоја, операције које нису засноване на раду са скуповима података, лоша спецификација упита, лоша организација базе података, план избршења упита који се не кешира, честа рекомпилација плана извршења, неправилна употреба курсора.

  • Лоше индексирање

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

  • Нетачна статистика

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

  • Превише међусобног блокирања трансакција и застоја

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

  • Операције које нису засноване на раду са скуповима података

Операције које нису засноване на раду са скуповима података него се заснивају на обради података употребом курсора и петљи, значајно могу да наруше перформансе упита.

  • Лоша спецификација упита

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

  • Лоша организација базе података

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

  • План извршења упита који се не кешира

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

  • Честа рекомпилација плана извршења

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

  • Неправилна употреба курсора

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

Имплементација оптимизације упита у SQL уреди

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

  • рад са малим скуповима података
  • ефективна употреба индекса
  • избегавати давање упута оптимизатору
  • користити доменски и референцијални интегритет
  • избјегавати упите који интензивно користе ресурсе
  • смањити комуникацију преко мреже
  • смањити трајање трансакција

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

Рад са малим скуповима података уреди

Смањењем и ограничењем скупа података (колона и редова) над којима се извршава упит смањује се употреба ресурса и повећава ефикасност индекса, што позитивно утиче на перформансе упита. Један од начина за смањење скупа података у упиту је да се листа колона у оквиру SELECT листе упита ограничи само на колоне које су неопходне, односно да се избјегава упит типа SELECT *. Други начин за смањење скупа података у упиту је да се употребом WHERE клаузуле смањује број редова уколико је то могуће, јер се на тај начин оптимизација упита реализује ефикаснијом употребом индекса. Уколико се као резултат упита кориснику треба презентовати велика количина података, онда се може, уколико је прихватљиво, извршити страничење података (енг. paging), односно на захтјев корисника слати један по један скуп записа.

Ефективна употреба индекса уреди

Познато је да индекси (clustered index – подаци су смештени на диску редоследом како су сортирани и non-clustered indexes – сортирани су само индекси на податке који се налазе разбацани на диску) дефинисани над одговарајућим колонама (најчешће кориштеним у упитима) убрзавају извршење упита. Међутим, подједнако је важно да ови индекси буду ефикасно искориштени приликом дефинисања упита. Ефикасна употреба индекса над колоном која се налази у оквиру where клаузуле упита зависи од израза, односно операција које се врше над том колоном у оквиру wхере клаузуле (брза Index Seek операција извршава се уколико се ради о високо селективном упиту, односно уколико се за добијање резултата упита мора обрадити мали број редова, 10-15% редова од укупног броја редова у табели). На примјер, оператор „!=“ захтијева скенирање читаве табеле иако је над колоном дефинисан индекс, што представља неефикасну употребу индекса (спорија Index Scan операција). Непожељни оператори, које треба избјегавати над колонама са индексом у оквиру WHERE клаузуле, су оператори искључења (<>, !=, !>, !<, NOT EXISTS, NOT IN и NOT LIKE IN, OR), а некада и оператор LIKE (LIKE '%tekst'). Наравно, не могу се увек избећи ови оператори, али када је могуће предефинисати упит тако да враћа исти резултат кориштењем других оператора, онда то треба размотрити у циљу побољшања перформанси упита. 

  Неоптимизовани LIKE упит:
  
  SELECT zaposleni.ime 
  FROM zaposleni
  WHERE zaposleni.ime LIKE 'Dej%'[1]

  Оптимизован LIKE упит:
  
  SELECT zaposleni.ime 
  FROM zaposleni 
  WHERE zaposleni.ime >= 'Dej' AND zaposleni.ime < 'DeK'[1]

Врло често оптимизатор система за управљање базама података, уколико је то могуће, изврши конверзију упита у оптимизовани облик тако што конвертује непожељни израз у одговарајући пожељни израз (нпр. !< замјени са >=). Да би се постигла ефикаснија употреба индекса, потребно је избегавати аритметичке операције над колонама у оквиру where клаузуле: 

  Неоптимизовани упит:
  
  SELECT zaposleni.ime, zaposleni.pozicija 
  FROM zaposleni 
  WHERE zaposleni.godina * 2 > 60[1]

  Оптимизован упит:
  
  SELECT zaposleni.ime, zaposleni.pozicija 
  FROM zaposleni 
  WHERE zaposleni.godine > 60/2[1]

Избјегавати функције над колонама у оквиру WHERE клаузуле, нпр: 

  Неоптимизовани упит:
  
  SELECT zaposleni.ime 
  FROM zaposleni 
  WHERE SUBSTRING(zaposleni.ime,1,1) = 'D'[1]

  Оптимизован упит:
  
  SELECT zaposleni.ime 
  FROM zaposleni 
  WHERE zaposleni.ime>='F' AND zaposleni.ime<'G'[1]

Избегавати давање упута оптимизатору уреди

Избегавати давање упута оптимизатору. Давањем упута оптимизатору се онемогућава да оптимизатор динамички дефинише план извршења упита у време његовог планирања и реализације. На овај начин се обично нарушавају перформансе упита, па треба избегавати JOIN упуте (енг. join hints), INDEX упуте (енг. index hints) и FORCEPLAN упуте (енг. forceplan hints) приликом дефинисања упита.

Користити доменски и референцијални интегритет уреди

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

  SELECT zaposleni.ime, zaposleni.godine 
  FROM zaposleni 
  WHERE zaposleni.godine != 30[1]

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

  SELECT zaposleni.godine 
  FROM zaposleni 
  WHERE zaposleni.godine != 30 OR zaposleni.godine IS NULL[1]

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

  SELECT zaposleni.zaposleniid, zaposleni.ime, kontakti.adresa 
  FROM zaposleni JOIN kontakti ON zaposleni.kontaktiid = kontakti.kontaktiid
  WHERE zaposleni.zaposleniid = 2345[1]

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

Избегавати упите који интензивно користе ресурсе сервера уреди

Да би упити мање користили ресурсе сервера морају се дефинисати тако да се у оквиру њих:

  • избегава конверзија из једног типа података у други тип приликом поређења компатибилних колона табеле или вредности (нпр. varchar sa nvarchar).
  • за проверу постојања неких података користи EXISTS уместо COUNT(*);
  • кад год је то могуће уместо UNION користи UNION ALL при чему треба имати у виду да се тада, уколико постоје, не елиминишу дупликати у оквиру резултата упита;
  • користе индекси над колонама над којима се користе агрегатне функција или сортирање;
  • да се избегава sp префикс код дефинисања имена угњеждених процедура јер уколико име процедуре почиње са sp онда сервер прво тражи процедуру у системским процедурама и на тај начин губи драгоцено време.

Смањити мрежну комуникацију уреди

Да би се смањила мрежна комуникација, која значајно утиче на време извршавања упита посматрано са стране апликације и корисника, неопходно је смањити број мрежних циклуса (енг. network round-trips) и трајање мрежног циклуса. Трајање мрежног циклуса је време потребно да пакет стигне од извора (рачунара који иницира комуникацију) до дестинације и опет назад до извора комуникације. Смањивање броја мрежних циклуса се може постићи извршавањем више упита заједно као batch упит (један или више SQL исказа који се извршавају као један исказ) или као ускладиштена процедура (енг. stored procedure). Смањивање времена мрежног циклуса се може постићи дефинисањем да се као резултат упита не враћа број записа који су обрађени у оквиру упита (batch упит или 'stored procedura). Број обрађених записа (редова) врло често је небитан податак и може се наредбом SET NOCOUNT ON избећи његово враћање након извршења упита (SET NOCOUNT OFF је супротна операција у оквиру SQL сервера).

Смањити трајање трансакције уреди

Приликом извршавања једног упита, који се посматра као atomic акција (акција која не може бити прекинута другом акцијом и при којој база података прелази из једног конзистентног стања у друго), врши се упис два конзистентна стања у трансакцијски лог базе података (енг. transaction log) на диску. Ово је временски захтевна операција, која значајно утиче на перформансе упита. Број ових операција се може смањити груписањем више упита у једну трансакцију (више упита се извршава као једна atomic акција). 

  Неоптимизован упит:
  
  BEGIN
  INSERT INTO zaposleni VALUE (Milan,Ivić,programer)
  INSERT INTO zaposleni VALUE (Petar,Jović,programer)
  INSERT INTO zaposleni VALUE (Luka,Lukić,programer)
  END[1]

  Оптимизовани упит:
  
  BEGIN TRANSACTION
  BEGIN
  INSERT INTO zaposleni VALUE (Milan,Ivić,programer)
  INSERT INTO zaposleni VALUE (Petar,Jović,programer)
  INSERT INTO zaposleni VALUE (Luka,Lukić,programer)
  END
  COMMIT[1]

Међутим, на овај начин се повећава време када су поједини записи у табели закључани што може негативно утицати на перформансе других упита. Наиме, SQL искази (SELECT, INSERT, UPDATE, DELETE) врше закључавање записа у табелама како би се подаци које обрађују заштитили од других SQL исказа. Закључавање записа повећава чекање других упита и на тај начин се продужава време њиховог извршавања. Почетна поставка сервера база података је да се приликом извршавања једног упита закључавање врши на нивоу записа, тако да се не дешава често да неки други упит захтева приступ закључаном запису и да мора да чека на откључавање тог записа. Међутим, уколико један упит у току извршавања обрађује велику количину записа, а закључавање се врши на нивоу записа, онда ће се наизменично извршавати велики број операција закључавања и откључавања записа, чиме се усложњава управљање овим операцијама и повећава време неопходно за њихово извршавања, а самим тим и време извршавања упита. Ово време се може смањити повећањем нивоа на којем се врши закључавање тако да се на пример закључавање врши на нивоу странице (енг. pagelock) или чак на нивоу целе табеле (енг. tablock): 

  SELECT zaposleni.ime, zaposleni.prezime 
  FROM zaposleni 
  WITH(PAGLOCK ili TABLOCK)[1]

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

  SELECT zaposleni.* 
  FROM zaposleni 
  WITH(NOLOCK)[1]

Примене уреди

Оптимизација упита се употребљава у већини база података, јер данас важну улогу у информационим технологијама игра брзина обраде података. Постоје разни системи база података и сваки систем има своју имплементацију оптимизатора упита. Постоје разни системи база података, а неки од њих су: IBM DB2, Oracle, System R, INGRES, MySQL, PostgreSQL, Microsoft Access, SQL Server, итд.

Референце уреди

  1. ^ а б в г д ђ е ж з и ј к л SQL code Архивирано на сајту Wayback Machine (1. фебруар 2014), приступано 18.01.2014., 19.01.2014.

Литература уреди

  1. Увод у релационе базе података, Гордана Павловић-Лажетић, Математички факултет, Београд
  2. Оптимизација SQL упита, Дејан Стјепановић, Електротехнички факултет, Бања Лука, март 2010.

Спољашње везе уреди

Математички факултет Београд
Увод у релационе базе података, Гордана Павловић-Лажетић, Математички факултет Београд
Query optimization Wikipedia
Преузето из „https://sr.wikipedia.org/w/index.php?title=Оптимизација_упита&oldid=26961056”