Наркы моделине көз каранды эмес Cool SQL оптималдаштыруу. 1-бөлүк

Метаберorштерге (б.а. чектөөлөргө) жана суроонун өзүнө гана негизделген беш жөнөкөй оптималдаштыруу Биз сизге Лукас Эдердин макаласынын адаптациясын сунуштайбыз, ал маалыматтар базалары жана SQL жөнүндө жалпы түшүнүккө ээ болгондор үчүн, ошондой эле DBMS менен бир аз практикалык тажрыйбага ээ. . Чыгымдарды оптималдаштыруу - бул заманбап маалымат базаларында SQL сурамдарын оптималдаштыруунун стандарттуу жолу. Мына ушундан улам 3GLде (үчүнчү муундагы программалоо тилдеринде) татаал алгоритмди кол менен жазуу абдан кыйын , анын аткаруусу заманбап оптимизатор тарабынан түзүлгөн динамикалык эсептелген аткаруу планынан ашат. Бүгүн биз чыгымдарды оптималдаштырууну, башкача айтканда, маалымат базасынын нарктык моделинин негизинде оптималдаштырууну талкуулабайбыз. Биз кыйла жөнөкөй оптималдаштырууларды карап чыгабыз. Метаберorштер (б.а. чектөөлөр) жана суроо-талаптын негизинде гана ишке ашырылышы мүмкүн болгондор. Адатта, алардын маалымат базасы үчүн ишке ашырылышы Ньютон биномиалы ​​эмес, анткени бул учурда ар кандай оптималдаштыруу индекстердин, маалыматтардын көлөмүнүн жана маалыматтарды бөлүштүрүүнүн кыйшаюусунун болушуна карабастан, жакшыраак аткаруу планына алып келет. "Ньютон биномиалы ​​эмес" оптималдаштырууну ишке ашыруу канчалык оңой экенин эмес, бирок аны жасоо керекпи деген мааниде эмес. Бул оптималдаштыруулар керексиз, кошумча жумуштарды [деректер базасы үчүн] жок кылат ( мен буга чейин жазган керексиз, талап кылынган жумуштан айырмаланып ).

Бул оптималдаштыруу эмне үчүн колдонулат?

Алардын көбү үчүн колдонулат:

• суроо-талаптардагы мүчүлүштүктөрдү оңдоо;
• Көрүнүштөрдү маалымат базасы иш жүзүндө көрүү логикасын аткарбастан кайра колдонууга мүмкүндүк берет.

Биринчи учурда, кимдир бирөө: "Эмне, бул келесоо SQL суроосун оңдоңуз" деп айтууга болот. Бирок эч качан жаңылбаган адам биринчи мага таш ыргытсын. Экинчи учур өзгөчө кызыктуу: ал бизге бир нече катмарда кайра колдонула турган көрүнүштөрдүн жана table функцияларынын татаал китепканаларын түзүүгө мүмкүнчүлүк берет.

Колдонулган маалымат базалары

Бул макалада биз 10 SQL оптималдаштырууну эң көп колдонулган беш DBMS менен салыштырабыз ( деректер базасынын рейтингине ылайык ):

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

Башка рейтинг аны дээрлик кайталайт. Адаттагыдай эле, бул макалада мен Сакила маалымат базасын сурайм . Бул жерде оптималдаштыруунун бул он түрлөрүнүн тизмеси:

1. өтмө жабуу;
2. мүмкүн эмес предикаттар жана керексиз стол чалуулар;
3. JOIN жок кылуу;
4. "мааниси жок" предикаттарды жок кылуу;
5. EXISTS подсуроолорундагы проекциялар;
6. предикаттарды бириктирүү;
7. далилдүү бош топтомдор;
8. constraints 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 DBMS учурда бул суроону аткаруу планын карап көрөлү:

--------------------------------------------------------------
| 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 tableсына да, FILM_ACTOR tableсына да колдонулат. Эгерде:

• 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 tableсындагы саптардын саны ашыкча бааланган, ал эми 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 эки жолу көрсөтүлгөнү эки table тең туруктуу маанини издеп жатканын көрсөтүп турат. Ошол эле учурда, 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 болуп саналат, ал үч маанилүү логикага ылайык , ЖАЛГАНга барабар. Бул өзүнчө эле түшүнүктүү, ошондуктан келгиле, бул оптималдаштырууну кайсы маалымат базалары аткарарын түз эле карап көрөлү.

DB2

Ооба!

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

Көрүнүп тургандай, АКТОР столуна кирүү пландан толугу менен алынып салынган. Ал нөл саптарды түзгөн 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 tableсына кирүү жөнүндө сөз болуп жатканын көрүп жатабыз жана саптардын күтүлгөн саны дагы 200, бирок ошондой эле Id=1 менен чыпкалоо операциясы (ФИЛТР) бар, мында эч качан ЧЫНДЫК болбойт. Oracle стандарттык SQL логикалык маалымат түрүн жактырбагандыктан , Oracle планда жөн эле ЖАЛГАН эмес, NULL IS NOT NULL көрсөтөт. О, жакшы... Бирок, олуттуу, бул предикатты караңыз. Мен 1000 саптык ички дарактар ​​жана өтө кымбат баалуулуктар менен аткаруу пландарынын мүчүлүштүктөрүн оңдоого мүмкүнчүлүк алдым, бирок NULL IS NOLL NULL чыпкасы тарабынан бүт ички дарак "кесorп" жатканынан кийин гана байкадым. Бир аз көңүл чөгөт, мен сага айтам.

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дерди жок кылуу

Мурунку бөлүмдө биз бир tableдагы суроо-талаптарда керексиз tableга кирүүнү байкадык. Бирок JOIN бир нече tableга кирүүнү талап кылбаса эмне болот? 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

"...-to-one" түрүндөгү INNER JOIN алмаштырылышы мүмкүн, эгерде нөл боло турган тышкы ачкыч бар болсо. Эгерде тышкы ачкыч 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

Бул мисалдардын бардыгы мурунку макалада кылдат изилденген, ошондуктан мен өзүмдү кайталабайм, бирок ар кандай маалымат базалары жок кыла турган нерселердин бардыгын кыскача айтып берем: Тилекке каршы, бардык маалымат базалары байланыштардын бардык түрлөрүн чече алbyte. DB2 жана SQL Server бул жерде талашсыз лидер болуп саналат! Уландысы бар