Pengoptimalan SQL keren yang tidak bergantung pada model biaya. Bagian 5

Pengoptimalan SQL keren yang tidak bergantung pada model biaya. Bagian 1 Optimasi SQL keren yang tidak bergantung pada model biaya. Bagian 2 Optimasi SQL keren yang tidak bergantung pada model biaya. Bagian 3 Optimasi SQL keren yang tidak bergantung pada model biaya. Bagian 4

10. Mendorong predikat

Optimalisasi ini kurang tepat di sini, karena tidak dapat dikatakan tidak didasarkan sama sekali pada model biaya. Namun karena saya tidak dapat memikirkan satu alasan pun mengapa pengoptimal tidak boleh memasukkan predikat ke dalam tabel turunan, saya akan mencantumkannya di sini, bersama dengan pengoptimalan non-biaya lainnya. Pertimbangkan permintaannya:

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

Tabel turunan dalam kueri ini tidak masuk akal dan harus dihilangkan dengan mengurangi jumlah tingkat kumpulan kueri. Tapi mari kita abaikan itu untuk saat ini. Anda dapat mengharapkan database untuk mengeksekusi query berikut, bukan yang di atas:

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

Dan sekali lagi, mungkin menghilangkan permintaan eksternal. Contoh yang lebih kompleks diperoleh dengan 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 dari kueri ini:

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

Sekarang, akan lebih bagus jika pengoptimal database menjalankan kueri seperti ini:

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;

Yaitu, sehingga mendorong predikat ke dalam tabel turunan, dan dari sana ke dalam dua subkueri UNION ALL , karena, bagaimanapun juga, kita memiliki indeks pada kolom ACTOR.LAST_NAME dan kolom CUSTOMER.LAST_NAME . Sekali lagi, konversi ini mungkin didasarkan pada perkiraan biaya di sebagian besar database, tapi menurut saya ini tidak perlu dipikirkan lagi karena, dengan algoritma apa pun, hampir selalu lebih baik untuk mengurangi jumlah tupel yang diproses sedini mungkin. Jika Anda mengetahui kasus di mana transformasi seperti itu ternyata merupakan ide yang buruk, saya akan senang mendengar komentar Anda! Saya akan sangat tertarik. Jadi database kami yang mana yang bisa melakukan ini? (Dan tolong, ini sangat sederhana dan penting, biarlah jawabannya: semuanya)

DB2

Tabel turunan sederhana 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 turunan 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')

Selain itu, dalam kedua kasus tersebut, tabel turunan (tampilan) dikeluarkan dari rencana karena sebenarnya tidak diperlukan.

MySQL

Tabel turunan sederhana Ya

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

Akses kunci primer biasa dengan nilai konstan digunakan. Tabel turunan dengan UNION Ups, no.

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

Konversi manual menghasilkan 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

Ini adalah masalah serius saat menggunakan kueri bersarang yang kompleks di MySQL!

Peramal

Tabel turunan sederhana Ya, berhasil.

---------------------------------------------------------------------------
| 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 jumlah level bersarang telah dikurangi. Tabel turunan 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')

Namun, tanpa mengurangi jumlah level bersarang. Id=1 "View" menunjukkan bahwa tabel turunan masih ada. Dalam hal ini bukan masalah besar, mungkin hanya sedikit biaya tambahan.

PostgreSQL

Tabel turunan sederhana Ya, berfungsi:

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

Namun, perhatikan bahwa PostgreSQL terkadang bahkan tidak menggunakan kunci utama untuk mencari satu baris, melainkan memindai seluruh tabel. Dalam hal ini, 200 baris × 25 byte per baris ("lebar") muat dalam satu blok, jadi apa gunanya mengganggu pembacaan indeks selain menghasilkan operasi I/O yang tidak perlu untuk mengakses tabel sekecil itu? Tabel turunan dengan UNION Ya, ini 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 kolom ACTOR.LAST_NAME tidak digunakan, namun indeks pada kolom CUSTOMER.LAST_NAME digunakan karena tabel CUSTOMER jauh lebih besar.

SQLServer

Tabel turunan sederhana Ya, berhasil

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

Tabel turunan 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 sepenuhnya mendukung optimasi sederhana ini. Namun, semua orang mendukungnya.

Kesimpulan

Daftar yang disajikan di sini masih jauh dari lengkap. Ada banyak transformasi SQL sederhana lainnya yang tidak (atau seharusnya tidak) sulit diterapkan oleh database, bahkan sebelum pengoptimal biaya terlibat. Mereka menghilangkan pekerjaan ekstra yang tidak perlu [untuk database] ( sebagai lawan dari pekerjaan yang tidak diperlukan dan diperlukan , yang sudah saya tulis ). Ini adalah alat penting untuk:

1. Kesalahan bodoh [pengembang] tidak berdampak pada kinerja. Kesalahan tidak bisa dihindari, dan seiring pertumbuhan proyek dan kueri SQL menjadi lebih kompleks, kesalahan ini dapat terakumulasi, semoga tidak menimbulkan dampak apa pun.


2. Menyediakan kemampuan untuk menggunakan kembali blok kompleks, seperti tampilan dan fungsi tabel, yang dapat disematkan dalam kueri SQL induk, diubah, atau dihapus sebagian atau ditulis ulang.

Kemampuan ini sangat penting untuk poin 2. Tanpa mereka, akan sangat sulit untuk membuat kueri SQL 4000 baris dengan performa normal berdasarkan pustaka komponen SQL yang dapat digunakan kembali. Yang sangat mengecewakan pengguna PostgreSQL dan MySQL, kedua database open source yang populer ini masih memiliki jalan yang panjang dibandingkan dengan kompetitor komersial mereka, DB2, Oracle dan SQL Server, dimana DB2 memiliki kinerja terbaik, dengan Oracle dan SQL Server yang bersaing ketat. leher, sedikit di belakang.