Шығын үлгісіне тәуелді емес керемет SQL оңтайландырулары. 1 бөлім

Тек метадеректерге (яғни шектеулерге) және сұраудың өзіне негізделген іске асырылуы мүмкін бес қарапайым оңтайландыру. Біз сізге Лукас Эдер мақаласының бейімделуін ұсынамыз, ол дерекқор мен SQL туралы жалпы түсінігі бар адамдарға, сондай-ақ ДҚБЖ-мен практикалық тәжірибесі бар адамдарға арналған. . Шығындарды оңтайландыру шын мәнінде қазіргі дерекқорлардағы SQL сұрауларын оңтайландырудың стандартты тәсілі болып табылады. Сондықтан 3GL-де (үшінші буынды бағдарламалау тілдерінде) күрделі алгоритмді қолмен жазу өте қиын , оның өнімділігі заманауи оңтайландырушы жасаған динамикалық есептелген орындау жоспарынан асып түседі. Бүгін біз шығындарды оңтайландыруды, яғни деректер қорының шығын үлгісіне негізделген оңтайландыруды талқыламаймыз. Біз әлдеқайда қарапайым оңтайландыруларды қарастырамыз. Тек метадеректерге (яғни шектеулер) және сұраудың өзі негізінде жүзеге асырылуы мүмкін. Әдетте олардың дерекқор үшін орындалуы Ньютон биномиалы ​​емес, өйткені бұл жағдайда кез келген оңтайландыру индекстердің болуына, деректер көлеміне және деректерді таратудың қиғаштығына қарамастан жақсырақ орындау жоспарына әкеледі. «Ньютон биномиалы ​​емес» оңтайландыруды жүзеге асыру қаншалықты оңай екенін емес, оны жасау керек пе деген мағынада емес. Бұл оңтайландырулар қажетсіз, қосымша жұмыстарды [деректер базасы үшін] жояды ( мен бұрын жазған қажетсіз, талап етілетін жұмысқа қарсы ).

Бұл оңтайландырулар не үшін пайдаланылады?

Олардың көпшілігі келесі мақсаттарда қолданылады:

• сұраулардағы қателерді түзету;
• көрініс логикасын нақты орындамайтын дерекқорсыз көріністерді қайта пайдалануға мүмкіндік береді.

Бірінші жағдайда: «Ендеше, осы ақымақ SQL сұрауын түзетіңіз» деп айтуға болады. Бірақ ешқашан қателеспеген адам бірінші маған тас лақтырсын. Екінші жағдай әсіресе қызықты: ол бізге бірнеше қабаттарда қайта пайдалануға болатын көріністердің және кесте функцияларының күрделі кітапханаларын жасау мүмкіндігін береді.

Қолданылатын мәліметтер базалары

Бұл мақалада біз 10 SQL оңтайландыруын ең көп қолданылатын бес ДҚБЖ салыстырамыз ( деректер базасының рейтингісіне сәйкес ):

• 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. Pushdown предикаттары

Бүгін біз pp. 1-3, екінші бөлікте - 4 және 5, ал 3-бөлімде - 6-10.

1. Өтпелі жабу

Қарапайымырақ нәрседен бастайық: өтпелі жабу . Бұл теңдік операторы сияқты көптеген математикалық операцияларға қолданылатын тривиальды ұғым. Бұл жағдайда оны былай тұжырымдауға болады: егер А = В және В = С болса, онда А = С.

Қиын емес, иә? Бірақ бұл 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
...

Енді 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 кестесіндегі жолдар саны асыра бағаланған, ал NESTED LOOP төмен бағаланған. Міне, бірнеше қызықты құндылықтар:

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

Бірақ REF бағанында const екі рет көрсетілген факт екі кестенің де тұрақты мәнді іздейтінін көрсетеді. Сонымен қатар, 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 әрқашан АҚИҚАТ болғанымен, 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 бар сүзгілеу операциясы (СҮЗГІ) бар, мұнда ешқашан TRUE болмайды. Oracle стандартты SQL логикалық деректер түрін ұнатпағандықтан , Oracle жоспарда жай ЖАЛҒАН емес, NULL IS NOLL NO NULL мәнін көрсетеді. О, жақсы... Бірақ байыпты түрде, бұл предикатты қараңыз. Менде 1000 жолдық ішкі ағаштар мен өте жоғары құны мәндері бар орындау жоспарларын түзету мүмкіндігі болды, тек бүкіл ішкі ағаш NULL IS NULL ЕМЕС сүзгісі арқылы «қиып» жатқанын білу үшін ғана. Мен сізге айтамын.

PostgreSQL

Иә!

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

Енді жақсырақ. Ешқандай тітіркендіргіш АКТОР кесте қоңыраулары және ұқыпты кішкентай ЖАЛҒАН предикаты.

SQL serverі?

Иә!

|--Constant Scan

SQL serverі мұны « тұрақты сканерлеу» деп атайды, бұл DB2 сияқты ештеңе болмайтын сканерлеу. Біздің барлық дерекқорларымыз мүмкін емес предикаттарды жоққа шығара алады:

3. JOINдарды жою

Алдыңғы бөлімде біз бір кестелік сұрауларда кестеге қажет емес қатынасты байқадық. Бірақ JOIN бірнеше кестеге кірудің біреуін қажет етпесе не болады? JOIN жою туралы мен блогымның алдыңғы жазбасында жазғанмын . SQL механизмі сұрау түріне және бастапқы және сыртқы кілттердің болуына негізделген нақты JOIN нақты сұрауда қажет пе немесе оны жою сұраудың семантикасына әсер етпейтінін анықтай алады. Келесі үш мысалдың барлығында JOIN қажет емес. Ішкі ...-бірге біріктіруді NOT 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

«...-бірге» түріндегі INNER JOIN ауыстырылуы мүмкін, егер нөлдік сыртқы кілт болса. Жоғарыдағы сұрау егер сыртқы кілт NOT 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 - бұл сөзсіз көшбасшылар! Жалғасы бар