Оптимизатсияи сард SQL, ки аз модели хароҷот вобаста нестанд. Қисми 1

Панҷ оптимизатсияи оддиро, ки танҳо дар асоси метамаълумотҳо (яъне маҳдудиятҳо) ва худи дархост амалӣ кардан мумкин аст, Мо ба шумо мутобиқсозии мақолаи Лукас Эдерро пешниҳод менамоем, ки барои онҳое, ки фаҳмиши умумии пойгоҳи додаҳо ва SQL доранд, инчунин баъзе таҷрибаи амалӣ бо DBMS тарҳрезӣ шудаанд. . Оптимизатсияи хароҷот воқеан як роҳи стандартии оптимизатсияи дархостҳои SQL дар пойгоҳи додаҳои муосир мебошад. Аз ин рӯ, ба таври дастӣ навиштани алгоритми мураккаб дар 3GL (забонҳои барномасозии насли сеюм), ки иҷрои он аз нақшаи иҷрои динамикӣ ҳисобшуда, ки аз ҷониби оптимизатори муосир тавлид шудааст, зиёдтар хоҳад буд, хеле душвор аст. Имрӯз мо оптимизатсияи хароҷотро баррасӣ намекунем, яъне оптимизатсия дар асоси модели арзиши пойгоҳи додаҳо. Мо оптимизатсияҳои хеле соддатарро дида мебароем. Онҳое, ки метавонанд танҳо дар асоси метадата (яъне маҳдудиятҳо) ва худи дархост амалӣ карда шаванд. Одатан татбиқи онҳо барои пойгоҳи додаҳо биноми Нютон нест, зеро дар ин ҳолат ҳама гуна оптимизатсия новобаста аз мавҷудияти индексҳо, ҳаҷми додаҳо ва каҷ будани тақсимоти додаҳо ба нақшаи беҳтари иҷро оварда мерасонад. "На як биноми Нютон" ба маънои он нест, ки татбиқи оптимизатсия то чӣ андоза осон аст, аммо оё он бояд анҷом дода шавад. Ин оптимизатсияҳо корҳои нолозим ва изофаро [барои пойгоҳи додаҳо] бартараф мекунанд ( бар хилофи кори нолозим ва зарурӣ, ки ман аллакай дар бораи он навишта будам ).

Ин оптимизатсияҳо барои чӣ истифода мешаванд?

Аксарияти онҳо барои:

• ислоҳи хатогиҳо дар дархостҳо;
• имкон медиҳад, ки намоишҳо бидуни пойгоҳи додаҳо мантиқи намоишро иҷро кунанд, дубора истифода шаванд.

Дар ҳолати аввал, метавон гуфт: "Пас, чӣ мешавад, танҳо пеш равед ва ин дархости аблаҳии SQL-ро ислоҳ кунед." Аммо бигзор касе, ки ҳеҷ гоҳ хато накардааст, аввал ба сӯи ман санг партояд. Ҳолати дуюм махсусан ҷолиб аст: он ба мо имкон медиҳад, ки китобхонаҳои мураккаби намудҳо ва вазифаҳои ҷадвалро эҷод кунем, ки онҳоро дар қабатҳои сершумор дубора истифода бурдан мумкин аст.

Пойгоҳҳои истифодашуда

Дар ин мақола мо 10 оптимизатсияи SQL-ро дар панҷ DBMS аз ҳама васеъ истифодашаванда муқоиса хоҳем кард ( аз рӯи рейтинги пойгоҳи додаҳо ):

• Oracle 12.2;
• MySQL 8.0.2;
• SQL Server 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5.

Рейтинги дигар тақрибан ба он мувофиқат мекунад. Чун маъмулӣ, дар ин мақола ман ба пойгоҳи додаҳои Sakila муроҷиат хоҳам кард . Ин аст рӯйхати ин даҳ намуди оптимизатсия:

1. бастани гузаранда;
2. предикатҳои ғайриимкон ва зангҳои нолозими ҷадвал;
3. бартараф кардани JOIN;
4. аз байн бурдани предикатхои «бемаъно»;
5. пешгӯиҳо дар зерпурсишҳои EXISTS;
6. якҷояшавии предикатҳо;
7. маҷмӯи исботшаванда холӣ;
8. маҳдудиятҳо CHECK;
9. алоқаҳои рефлексивии нодаркор;
10. Предикатҳои поён

Имрӯз мо саҳ. 1-3, дар киемхои дуйум — 4 ва 5 ва 3 — 6-10.

1. Бастани гузаранда

Биёед бо чизи соддатар оғоз кунем: басташавии гузаранда . Ин мафҳуми ночиз аст, ки ба бисёр амалҳои математикӣ, ба монанди оператори баробарӣ дахл дорад. Онро дар ин маврид ба таври зайл ифода кардан мумкин аст: агар A = B ва B = C бошад, пас A = C.

Мушкил нест, дуруст? Аммо ин барои оптимизаторҳои SQL чанд оқибатҳои ҷолиб дорад. Биёед як мисолро дида бароем. Биёед ҳамаи филмҳоро бо ACTOR_ID = 1 истихроҷ кунем:

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

Натиҷа чунин аст:

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

Биёед ҳоло нақшаи иҷрои ин дархостро дар мавриди DBMS Oracle дида бароем:

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

Дар ин ҷо бахши предикатҳо махсусан ҷолиб аст. Предикати ACTOR_ID = 1, бинобар баста шудани гузаранда, ҳам ба ҷадвали ACTOR ва ҳам ба ҷадвали FILM_ACTOR дахл дорад. Агар:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

Барои пурсишҳои мураккабтар, ин натиҷаҳои хеле хуб медиҳад. Махсусан, дурустии баҳодиҳии кардиналӣ ба таври назаррас меафзояд, зеро имкон пайдо мешавад, ки ҳисобҳоро дар асоси арзиши мушаххаси доимии предикат интихоб кунед, на масалан, шумораи миёнаи филмҳои актёрҳо, чунон ки дар дархости зерин (баргардонидани ҳамон натиҷа):

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

Нақшаи ӯ:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

Тавре ки шумо мебинед, шумораи сатрҳо дар ҷадвали FILM_ACTOR аз ҳад зиёд ҳисоб карда шудааст, дар ҳоле ки LOOP NESTED ночиз аст. Инҳоянд чанд арзишҳои ҷолиб:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

Натиҷа:

19
27.315

Ин аст, ки тахминҳо аз он ҷо бармеоянд. Агар базаи маълумот донад, ки сухан дар бораи ACTOR_ID = 1 меравад, он метавонад оморро дар бораи шумораи филмҳо барои ин актёри мушаххас ҷамъ оварад . Агар ин тавр набошад (азбаски механизми стандартии ҷамъоварии омор FIRST_NAME/LAST_NAME бо ACTOR_ID мувофиқат намекунад), он гоҳ мо шумораи миёнаи филмҳоро барои ҳамаи актёрҳо мегирем . Хатогии оддӣ ва муҳим дар ин ҳолати мушаххас, аммо дар дархости мураккаб он метавонад минбаъд паҳн шавад, ҷамъ шавад ва минбаъд ба дархост (дар нақша болотар) ба интихоби нодурусти JOIN оварда расонад. Ҳамин тавр, ҳар вақте ки шумо метавонед, пайвастагиҳо ва предикатҳои оддии худро тарҳрезӣ кунед, то аз бастани гузариш истифода баранд. Кадом пойгоҳи додаҳои дигар ин хусусиятро дастгирӣ мекунанд?

DB2

Бале!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

Дар омади гап, агар ба шумо чунин нақшаҳои иҷрои олӣ маъқул бошад, скрипти Маркус Винандро санҷед .

MySQL

Мутаассифона, нақшаҳои иҷрои MySQL барои ин гуна таҳлил чандон мувофиқ нестанд. Худи предикат дар маълумоти баромад мавҷуд нест:

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

Аммо далели он, ки const дар сутуни REF ду маротиба нишон дода шудааст, нишон медиҳад, ки ҳарду ҷадвал арзиши доимиро меҷӯянд. Ҳамзамон, нақшаи дархост бо FIRST_NAME/LAST_NAME чунин менамояд:

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

Ва тавре ки мебинед, REF ҳоло ба сутуни предикати JOIN ишора мекунад. Холи кардиналӣ тақрибан ба ҳамон тавре ки дар Oracle аст. Ҳамин тавр, ҳа, MySQL бастани гузарандаро низ дастгирӣ мекунад.

PostgreSQL

Бале!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

SQL Server

Бале!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

Хулоса

Ҳама пойгоҳи додаҳои мо бастани транзитиро дастгирӣ мекунанд. Бо вуҷуди ин, дар қисми навбатии мақола # 6-ро интизор шавед. Ҳодисаҳои мураккаби бастани гузаранда вуҷуд доранд, ки на ҳама пойгоҳи додаҳо онҳоро ҳал карда метавонанд.

2. Предикатҳои ғайриимкон ва зангҳои нолозими ҷадвал

Ин як оптимизатсияи комилан беақл аст, аммо чаро не? Агар корбарон предикатҳои ғайриимкон нависанд, пас чаро иҷрои онҳоро ташвиш медиҳед? Инҳоянд чанд мисол:

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

Дархости аввал бешубҳа ҳеҷ гоҳ натиҷа намедиҳад, аммо ҳамон изҳорот барои дуюм дуруст аст. Дар ниҳоят, гарчанде ки NULL IS NULL ҳамеша TRUE аст, аммо натиҷаи ҳисобкунии NULL = NULL NULL аст, ки мувофиқи мантиқи се қиматбаҳо ба FALSE баробар аст. Ин хеле фаҳмо аст, аз ин рӯ биёед мустақиман бифаҳмем, ки кадом пойгоҳи додаҳо ин оптимизатсияро иҷро мекунанд.

DB2

Бале!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

Тавре ки шумо мебинед, дастрасӣ ба ҷадвали ACTOR аз нақша комилан хориҷ карда шудааст. Он танҳо амалиёти GENROW-ро дар бар мегирад, ки сатрҳои сифрро тавлид мекунад. Комил.

MySQL

Бале!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

Ин дафъа, MySQL ба қадри кофӣ меҳрубон буд, ки ба мо дар бораи банди имконнопазир WHERE хабар диҳад. Сипос! Ин таҳлилро хеле осон мекунад, махсусан дар муқоиса бо дигар пойгоҳи додаҳо.

Oracle

Бале!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

Мо мебинем, ки нақша ҳанӯз дастрасӣ ба ҷадвали ACTOR-ро зикр мекунад ва шумораи интизории сатрҳо то ҳол 200 аст, аммо амалиёти филтркунӣ (ФИЛТР) бо Id=1 мавҷуд аст, ки дар он ҳеҷ гоҳ ҲАҚИҚАТ нахоҳад буд. Аз сабаби нописандии Oracle ба навъи маълумоти стандартии SQL Boolean , Oracle дар нақша ба ҷои танҳо FALSE NULL IS NULL НЕСТ-ро нишон медиҳад. Оҳ, хуб... Аммо ҷиддӣ, он предикатро тамошо кунед. Ман фурсат доштам, ки нақшаҳои иҷроро бо зердарахтони 1000-сатр ва арзишҳои хеле баландро ислоҳ кунам, танҳо пас аз он фаҳмидам, ки тамоми зердарахт аз ҷониби филтри NULL IS NULL "бурида" шудааст. Каме рӯҳафтода, ба шумо мегӯям.

PostgreSQL

Бале!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

Аллакай беҳтар. Зангҳои озори АКТОР дар ҷадвал ва як предикати нозуки FALSE.

SQL Server?

Бале!

|--Constant Scan

SQL Server инро " скани доимӣ " меномад, ки сканест, ки дар он ҳеҷ чиз рух намедиҳад - ба DB2 монанд аст. Ҳама пойгоҳи додаҳои мо метавонанд предикатҳои имконнопазирро истисно кунанд:

3. Бартараф кардани JOINs

Дар фасли қаблӣ, мо дастрасии нолозим ба ҷадвалро дар дархостҳои як ҷадвал мушоҳида кардем. Аммо агар JOIN яке аз якчанд дастрасии ҷадвалро талаб накунад, чӣ мешавад? Ман аллакай дар бораи нест кардани JOIN дар як паёми қаблии блоги худ навишта будам . Муҳаррики SQL қодир аст дар асоси намуди дархост ва мавҷудияти калидҳои ибтидоӣ ва хориҷӣ муайян кунад, ки оё JOIN-и мушаххас воқеан дар дархости додашуда лозим аст ё нест кардани он ба семантикаи дархост таъсир намерасонад. Дар ҳамаи се мисоли оянда, JOIN лозим нест. Пайвастшавии дохorи ...-ба-якро тавассути доштани калиди хориҷии NO NULL нест кардан мумкин аст. Ба ҷои ин:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

Пойгоҳи маълумот метавонад корҳои зеринро иҷро кунад:

SELECT first_name, last_name
FROM customer c

Дар сурати мавҷуд будани калиди хориҷии беэътибор як JOIN-и дохorи навъи "...-to-one" метавонад иваз карда шавад. Дархости дар боло овардашуда кор мекунад, агар калиди хориҷӣ таҳти маҳдудияти NO NULL бошад. Агар не, масалан, дар ин дархост:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

пас JOIN ҳоло ҳам метавонад нест карда шавад, аммо шумо бояд предикати NO NULL-ро илова кунед, ба монанди:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

Агар калиди ягона мавҷуд бошад, ҶОИНИ БЕРУНИИ навъи "...-ба-як" метавонад хориҷ карда шавад. Ба ҷои ин:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

Пойгоҳи додаҳо боз метавонад корҳои зеринро иҷро кунад:

SELECT first_name, last_name
FROM customer c

... ҳатто агар калиди хориҷӣ барои CUSTOMER.ADDRESS_ID мавҷуд набошад. Пайвасти беҳамтои беруна (DISTINCT OUTER JOIN) навъи "...-ба-бисёр" метавонад хориҷ карда шавад. Ба ҷои ин:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

Пойгоҳи маълумот метавонад корҳои зеринро иҷро кунад:

SELECT DISTINCT first_name, last_name
FROM actor a

Ҳамаи ин мисолҳо дар мақолаи қаблӣ ба таври муфассал омӯхта шуданд, аз ин рӯ ман такрор намекунам, балки ҳама чизеро, ки пойгоҳи додаҳои гуногун бартараф карда метавонанд, ҷамъбаст мекунам: Мутаассифона, на ҳама пойгоҳи додаҳо ҳама намуди пайвастҳоро ҳал карда метавонанд. DB2 ва SQL Server пешвоёни бешубҳа дар ин ҷо ҳастанд! Давом дорад