Optimizations SQL Cool sing ora gumantung ing model biaya. Bagean 5

Optimizations SQL Cool sing ora gumantung ing model biaya. Part 1 Optimizations SQL Cool sing ora gumantung ing model biaya. Part 2 Optimizations SQL Cool sing ora gumantung ing model biaya. Part 3 Optimizations SQL Cool sing ora gumantung ing model biaya. Bagean 4

10. Predikat nyurung

Optimasi iki ora sakabehe cocok ing kene, amarga ora bisa diarani ora adhedhasar model biaya. Nanging amarga aku ora bisa mikir siji alesan kenapa optimizer ngirim ora push predikat menyang tabel asalé, Aku bakal dhaftar kene, bebarengan karo liyane saka optimizations non-biaya. Coba panjaluk kasebut:

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

Tabel asale ing pitakon iki ora ana gunane lan kudu diilangi kanthi nyuda jumlah level nesting query. Nanging ayo digatekake saiki. Sampeyan bisa ngarepake database kanggo nglakokake pitakon ing ngisor iki tinimbang ing ndhuwur:

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

Banjur, maneh, bisa uga ngilangi panjaluk eksternal. Conto sing luwih rumit dipikolehi nggunakake UNION :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

Asil saka pitakon iki:

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

Saiki, bakal luwih apik yen pangoptimal database bakal mbukak pitakon kaya mangkene:

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

Yaiku, supaya predikat kasebut nyurung predikat menyang tabel asale, lan saka ing kono dadi rong subkueri UNION ALL , amarga, sawise kabeh, kita duwe indeks ing kolom ACTOR.LAST_NAME lan kolom CUSTOMER.LAST_NAME . Maneh, konversi iki mbokmenawa adhedhasar prakiraan biaya ing paling database, nanging aku isih mikir iku ora-brainer wiwit, karo algoritma sembarang, iku meh tansah luwih apik kanggo ngurangi jumlah tuples diproses minangka awal sabisa. Yen sampeyan ngerti kasus sing owah-owahan kasebut dadi ide sing ala, aku bakal seneng ngrungokake komentar sampeyan! Aku bakal banget kasengsem. Dadi endi database kita sing bisa nindakake iki? (Lan monggo, iku prasaja lan penting, supaya jawabane: kabeh)

DB2

Tabel asale prasaja Ya

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

Tabel asale karo UNION Uga ya:

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

Kajaba iku, ing loro kasus kasebut, tabel asale (tampilan) ora kalebu saka rencana amarga ora dibutuhake.

MySQL

Tabel asale prasaja Ya

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

Akses tombol primer biasanipun kanthi nilai konstan digunakake. Tabel asale karo UNION Oops, ora.

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

Hasil konversi manual ing rencana:

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

Iki minangka masalah serius nalika nggunakake pitakon nested kompleks ing MySQL!

Oracle

Tabel asale prasaja Ya, kerjane.

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

Lan jumlah tingkat nesting wis suda. Tabel asale karo UNION uga bisa digunakake:

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

Nanging, tanpa ngurangi jumlah tingkat nesting. Id=1 "View" nuduhake yen tabel asale isih ana. Ing kasus iki ora masalah gedhe, mung mbok menawa biaya tambahan cilik.

PostgreSQL

Tabel turunan prasaja Ya, kerjane:

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

Elinga, Nanging, PostgreSQL kadhangkala ora nggunakake tombol utami kanggo nggoleki baris siji, nanging mindai kabeh tabel. Ing kasus iki, 200 larik × 25 bita saben baris ("jembaré") pas ing siji blok, supaya apa gunane keganggu karo indeks maca liyane saka generate I / O operasi rasah kanggo ngakses tabel cilik kuwi? Tabel asale karo UNION Ya, uga bisa digunakake:

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

Maneh, indeks ing kolom ACTOR.LAST_NAME ora digunakake, nanging indeks ing kolom CUSTOMER.LAST_NAME digunakake amarga tabel CUSTOMER luwih gedhe.

SQL Server

Tabel asale prasaja Ya, kerjane

  |--Nested Loops(Inner Join)
       |--Index Seek(SEEK:([actor_id]=(1)))
       |--RID Lookup(OBJECT:([actor]))

Tabel asale karo UNION uga bisa digunakake.

  |--Concatenation
       |--Compute Scalar(DEFINE:([Expr1003]='actor'))
       |    |--Nested Loops(Inner Join)
       |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
       |         |--RID Lookup(OBJECT:([actor]))
       |--Compute Scalar(DEFINE:([Expr1007]='customer'))
            |--Nested Loops(Inner Join)
                 |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
                 |--RID Lookup(OBJECT:([customer]))

Ringkesan

Pangarep-arepku ora kelakon. MySQL 8.0.2 durung ndhukung optimasi prasaja iki. Kabeh wong liya, Nanging, ndhukung.

Kesimpulan

Dhaptar sing diwenehake ing kene adoh saka lengkap. Ana akeh transformasi SQL prasaja liyane sing ora (utawa ora kudu) angel kanggo database kanggo ngleksanakake, malah sadurunge optimizer biaya bakal melu. Padha ngilangi karya ekstra sing ora perlu [kanggo basis data] ( minangka gantos kanggo karya sing ora perlu , sing wis daktulis babagan ). Iki minangka alat penting kanggo:

1. Kesalahan [pangembang] bodho ora duwe pengaruh marang kinerja. Kesalahan ora bisa dihindari, lan nalika proyek tuwuh lan pitakon SQL dadi luwih rumit, kesalahan kasebut bisa nglumpukake, muga-muga tanpa ana pengaruh.


2. Nyedhiyani kemampuan kanggo nggunakake maneh blok kompleks, kayata tampilan lan fungsi tabel, sing bisa diselehake ing pitakon SQL induk, diowahi, utawa sebagian dibusak utawa ditulis maneh.

Kapabilitas kasebut kritis kanggo titik 2. Tanpa wong-wong mau, bakal angel banget kanggo nggawe pitakon SQL 4000-baris kanthi kinerja normal adhedhasar perpustakaan komponen SQL sing bisa digunakake maneh. Akeh kuciwane pangguna PostgreSQL lan MySQL, rong basis data open source sing populer iki isih adoh banget dibandhingake karo pesaing komersial DB2, Oracle lan SQL Server, sing DB2 wis nindakake paling apik, kanthi Oracle lan SQL Server kira-kira gulu lan gulu. rada mburi.