Pengoptimuman SQL hebat yang tidak bergantung pada model kos. Bahagian 5

Pengoptimuman SQL hebat yang tidak bergantung pada model kos. Bahagian 1 Pengoptimuman SQL hebat yang tidak bergantung pada model kos. Bahagian 2 Pengoptimuman SQL hebat yang tidak bergantung pada model kos. Bahagian 3 Pengoptimuman SQL hebat yang tidak bergantung pada model kos. Bahagian 4

10. Menolak predikat

Pengoptimuman ini tidak sesuai sepenuhnya di sini, kerana ia tidak boleh dikatakan bahawa ia tidak berdasarkan sama sekali pada model kos. Tetapi oleh kerana saya tidak dapat memikirkan satu sebab mengapa pengoptimum tidak boleh menolak predikat ke dalam jadual terbitan, saya akan menyenaraikannya di sini, bersama-sama dengan pengoptimuman bukan kos yang lain. Pertimbangkan permintaan:

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

Jadual terbitan dalam pertanyaan ini tidak masuk akal dan harus dihapuskan dengan mengurangkan bilangan tahap bersarang pertanyaan. Tetapi mari kita abaikan itu buat masa ini. Anda boleh mengharapkan pangkalan data untuk melaksanakan pertanyaan berikut dan bukannya di atas:

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

Dan kemudian, sekali lagi, mungkin menghapuskan permintaan luaran. Contoh yang lebih kompleks diperoleh menggunakan 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';

Hasil daripada pertanyaan ini:

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

Sekarang, adalah bagus jika pengoptimum pangkalan data akan menjalankan pertanyaan seperti ini sebaliknya:

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;

Iaitu, supaya ia menolak predikat ke dalam jadual terbitan, dan dari situ menjadi dua subquery UNION ALL , memandangkan, lagipun, kami mempunyai indeks pada kedua-dua lajur ACTOR.LAST_NAME dan lajur CUSTOMER.LAST_NAME . Sekali lagi, penukaran ini mungkin berdasarkan anggaran kos dalam kebanyakan pangkalan data, tetapi saya masih fikir ia tidak perlu difikirkan kerana, dengan sebarang algoritma, hampir selalu lebih baik untuk mengurangkan bilangan tupel yang diproses seawal mungkin. Jika anda mengetahui kes di mana transformasi sedemikian ternyata idea yang tidak baik, saya akan gembira mendengar komen anda! Saya akan sangat berminat. Jadi mana antara pangkalan data kami boleh melakukan ini? (Dan tolong, ia sangat mudah dan sangat penting, biarkan jawapannya: semuanya)

DB2

Jadual terbitan ringkas 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)

Jadual terbitan dengan UNION Juga 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')

Juga, dalam kedua-dua kes, jadual terbitan (pandangan) telah dikecualikan daripada rancangan kerana ia sebenarnya tidak diperlukan.

MySQL

Jadual terbitan ringkas Ya

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

Akses kunci utama biasa dengan nilai malar digunakan. Jadual terbitan dengan UNION Oops, tidak.

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

Penukaran manual menghasilkan rancangan:

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

Ini adalah masalah serius apabila menggunakan pertanyaan bersarang kompleks dalam MySQL!

Oracle

Jadual terbitan ringkas Ya, ia berfungsi.

---------------------------------------------------------------------------
| 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)

Dan bilangan tahap bersarang telah dikurangkan. Jadual terbitan dengan UNION juga berfungsi:

---------------------------------------------------------------------------------
| 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')

Walau bagaimanapun, tanpa mengurangkan bilangan tahap bersarang. Id=1 "Paparan" menunjukkan bahawa jadual terbitan masih ada. Dalam kes ini ia bukan masalah besar, cuma mungkin kos tambahan yang kecil.

PostgreSQL

Jadual terbitan mudah Ya, ia berfungsi:

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

Walau bagaimanapun, ambil perhatian bahawa PostgreSQL kadangkala tidak menggunakan kunci utama untuk mencari satu baris, sebaliknya mengimbas keseluruhan jadual. Dalam kes ini, 200 baris × 25 bait setiap baris ("lebar") muat dalam satu blok, jadi apa gunanya mengganggu bacaan indeks selain menjana operasi I/O yang tidak perlu untuk mengakses jadual kecil sedemikian? Jadual terbitan dengan UNION Ya, ia juga berfungsi:

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)

Sekali lagi, indeks pada lajur ACTOR.LAST_NAME tidak digunakan, tetapi indeks pada lajur CUSTOMER.LAST_NAME digunakan kerana jadual PELANGGAN adalah lebih besar.

Pelayan SQL

Jadual terbitan ringkas Ya, ia berfungsi

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

Jadual terbitan dengan UNION juga berfungsi.

|--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]))

Ringkasan

Harapan saya tidak menjadi kenyataan. MySQL 8.0.2 belum menyokong sepenuhnya pengoptimuman mudah ini. Bagaimanapun, semua orang menyokong.

Kesimpulan

Senarai yang dibentangkan di sini masih jauh dari lengkap. Terdapat banyak transformasi SQL mudah lain yang tidak (atau tidak sepatutnya) sukar untuk dilaksanakan oleh pangkalan data, walaupun sebelum pengoptimum kos terlibat. Mereka menghapuskan kerja tambahan yang tidak perlu [untuk pangkalan data] ( berbanding dengan kerja yang tidak perlu, diperlukan , yang telah saya tulis tentang ). Ini adalah alat penting untuk:

1. Kesilapan [pembangun] bodoh tidak memberi kesan kepada prestasi. Ralat tidak dapat dielakkan, dan apabila projek berkembang dan pertanyaan SQL menjadi lebih kompleks, ralat ini boleh terkumpul, mudah-mudahan tanpa sebarang kesan.


2. Menyediakan keupayaan untuk menggunakan semula blok kompleks, seperti paparan dan fungsi jadual, yang boleh dibenamkan dalam pertanyaan SQL induk, diubah atau dipadamkan atau ditulis semula sebahagiannya.

Keupayaan ini adalah penting untuk titik 2. Tanpa mereka, adalah sangat sukar untuk mencipta pertanyaan SQL 4000 baris dengan prestasi biasa berdasarkan perpustakaan komponen SQL yang boleh digunakan semula. Sangat mengecewakan pengguna PostgreSQL dan MySQL, kedua-dua pangkalan data sumber terbuka yang popular ini masih jauh untuk pergi berbanding pesaing komersial mereka DB2, Oracle dan SQL Server, yang mana DB2 telah menunjukkan prestasi terbaik, dengan Oracle dan SQL Server secara kasarnya dan leher.belakang sikit.