Mga cool na SQL optimization na hindi nakadepende sa modelo ng gastos. Bahagi 5

Mga cool na SQL optimization na hindi nakadepende sa modelo ng gastos. Bahagi 1 Mga cool na pag-optimize ng SQL na hindi nakadepende sa modelo ng gastos. Bahagi 2 Mga cool na pag-optimize ng SQL na hindi nakadepende sa modelo ng gastos. Bahagi 3 Mga cool na pag-optimize ng SQL na hindi nakadepende sa modelo ng gastos. Bahagi 4

10. Pagtulak ng mga panaguri

Ang pag-optimize na ito ay hindi ganap na angkop dito, dahil hindi masasabi na hindi ito nakabatay sa lahat sa modelo ng gastos. Ngunit dahil wala akong maisip na isang dahilan kung bakit hindi dapat itulak ng optimizer ang mga predicate sa mga derived table, ililista ko ito dito, kasama ang iba pang mga non-cost optimizations. Isaalang-alang ang kahilingan:

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

Ang nagmula na talahanayan sa query na ito ay walang kahulugan at dapat na alisin sa pamamagitan ng pagbawas sa bilang ng mga antas ng nesting ng query. Ngunit huwag muna nating pansinin iyon sa ngayon. Maaari mong asahan na isasagawa ng database ang sumusunod na query sa halip na sa itaas:

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

At pagkatapos, muli, maaaring alisin ang panlabas na kahilingan. Ang isang mas kumplikadong halimbawa ay nakuha gamit ang 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';

Ang resulta ng query na ito:

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

Ngayon, magiging mahusay kung ang database optimizer ay magpapatakbo ng isang query na tulad nito sa halip:

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;

Iyon ay, upang itulak nito ang panaguri sa hinangong talahanayan, at mula doon sa dalawang UNION ALL subquery , dahil, pagkatapos ng lahat, mayroon kaming index sa parehong ACTOR.LAST_NAME na column at sa CUSTOMER.LAST_NAME na column . Muli, ang conversion na ito ay malamang na nakabatay sa mga pagtatantya ng gastos sa karamihan ng mga database, ngunit sa tingin ko pa rin ito ay isang no-brainer dahil, sa anumang algorithm, halos palaging mas mahusay na bawasan ang bilang ng mga tuple na naproseso nang maaga hangga't maaari. Kung alam mo ang isang kaso kung saan ang gayong pagbabago ay lumalabas na isang masamang ideya, ikalulugod kong marinig ang iyong mga komento! Ako ay magiging lubhang interesado. Kaya alin sa aming mga database ang makakagawa nito? (At pakiusap, napakasimple at napakahalaga nito, hayaan ang sagot: lahat)

DB2

Simple derived table Oo

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)

Hinangong talahanayan na may UNION Oo din:

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

Gayundin, sa parehong mga kaso, ang nagmula na talahanayan (view) ay hindi kasama sa plano dahil hindi ito aktwal na kailangan.

MySQL

Simple derived table Oo

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

Ang karaniwang pangunahing key na pag-access sa pamamagitan ng pare-parehong halaga ay ginagamit. Hinangong talahanayan na may UNION Oops, hindi.

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

Mga resulta ng manu-manong conversion sa plano:

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

Ito ay isang seryosong problema kapag gumagamit ng mga kumplikadong nested query sa MySQL!

Oracle

Simple derived table Oo, gumagana ito.

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

At ang bilang ng mga antas ng nesting ay nabawasan. Gumagana din ang nagmula na talahanayan na may UNION :

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

Gayunpaman, nang hindi binabawasan ang bilang ng mga antas ng nesting. Id=1 "View" ay nagpapakita na ang hinangong talahanayan ay nandoon pa rin. Sa kasong ito, hindi ito isang malaking problema, marahil isang maliit na karagdagang gastos.

PostgreSQL

Simple derived table Oo, gumagana ito:

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

Tandaan, gayunpaman, na minsan ay hindi ginagamit ng PostgreSQL ang pangunahing key upang maghanap ng isang hilera, ngunit sa halip ay ini-scan ang buong talahanayan. Sa kasong ito, 200 row × 25 bytes per row ("lapad") ang magkasya sa isang bloke, kaya ano ang silbi ng pag-abala sa mga index reads maliban sa pagbuo ng hindi kinakailangang mga operasyon ng I/O upang ma-access ang ganoong maliit na talahanayan? Nagmula na talahanayan na may UNION Oo, gumagana rin ito:

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)

Muli, hindi ginagamit ang isang index sa column na ACTOR.LAST_NAME , ngunit ginagamit ang isang index sa column na CUSTOMER.LAST_NAME dahil mas malaki ang talahanayan ng CUSTOMER .

SQL Server

Simple derived table Oo, gumagana ito

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

Gumagana rin ang derived table na may UNION .

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

Buod

Hindi natupad ang pag-asa ko. Hindi pa ganap na sinusuportahan ng MySQL 8.0.2 ang simpleng pag-optimize na ito. Ang iba, gayunpaman, ay sumusuporta.

Konklusyon

Ang listahan na ipinakita dito ay malayo sa kumpleto. Mayroong maraming iba pang mga simpleng pagbabagong-anyo ng SQL na hindi (o hindi dapat) mahirap para sa mga database na ipatupad, kahit na bago pa masangkot ang isang cost optimizer. Inaalis nila ang hindi kailangan, dagdag na trabaho [para sa database] ( kumpara sa hindi kailangan, kinakailangang gawain, na isinulat ko na tungkol sa ). Ang mga ito ay mahalagang kasangkapan upang:

1. Ang mga hangal na pagkakamali ng [developer] ay walang epekto sa pagganap. Ang mga error ay hindi maiiwasan, at habang lumalaki ang isang proyekto at nagiging mas kumplikado ang mga query sa SQL, maaaring maipon ang mga error na ito, sana ay walang epekto.


2. Magbigay ng kakayahang gumamit muli ng mga kumplikadong bloke, tulad ng mga view at function ng talahanayan, na maaaring i-embed sa mga query ng parent SQL, binago, o bahagyang tinanggal o muling isulat.

Ang mga kakayahang ito ay kritikal sa punto 2. Kung wala ang mga ito, magiging napakahirap na lumikha ng 4000-row na mga query sa SQL na may normal na pagganap batay sa isang library ng mga reusable na bahagi ng SQL. Sa labis na pagkabigo ng mga gumagamit ng PostgreSQL at MySQL, ang dalawang sikat na open source database na ito ay mahaba pa ang mararating kumpara sa kanilang mga komersyal na katunggali na DB2, Oracle at SQL Server, kung saan ang DB2 ay pinakamahusay na gumanap, na may Oracle at SQL Server halos leeg at leeg.medyo sa likod.