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 1

4. Pag-aalis ng mga "walang kahulugan" na panaguri

Ang parehong walang kahulugan ay mga panaguri na (halos) laging totoo. Tulad ng maaari mong isipin, kung ikaw ay nagtatanong:

SELECT * FROM actor WHERE 1 = 1;

...kung gayon ang mga database ay hindi aktwal na isasagawa ito, ngunit babalewalain lamang ito. Minsan akong sumagot ng isang tanong tungkol dito sa Stack Overflow at iyon ang dahilan kung bakit napagpasyahan kong isulat ang artikulong ito. Iiwan ko ang pagsubok na ito bilang isang ehersisyo sa mambabasa, ngunit ano ang mangyayari kung ang panaguri ay medyo "walang kahulugan"? Halimbawa:

SELECT * FROM film WHERE release_year = release_year;

Kailangan mo ba talagang ihambing ang halaga sa sarili nito para sa bawat hilera? Hindi, walang halaga kung saan magiging FALSE ang predicate na ito , tama ba? Ngunit kailangan pa rin nating suriin ito. Bagama't ang panaguri ay hindi maaaring katumbas ng FALSE , maaaring ito ay katumbas ng NULL sa lahat ng dako , muli dahil sa tatlong halagang lohika. Ang RELEASE_YEAR column ay nullable, at kung ang alinman sa mga row ay may RELEASE_YEAR IS NULL , pagkatapos ay NULL = NULL ang mga resulta sa NULL at ang row ay dapat na alisin. Kaya ang kahilingan ay nagiging sumusunod:

SELECT * FROM film WHERE release_year IS NOT NULL;

Aling mga database ang gumagawa nito?

DB2

Oo!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL

Ito ay isang kahihiyan, ngunit ang MySQL, muli, ay hindi naglalagay ng mga predicate sa mga plano sa pagpapatupad, kaya ang pag-uunawa kung ang MySQL ay nagpapatupad ng partikular na pag-optimize na ito ay medyo nakakalito. Maaari kang magsagawa ng pagtatasa ng pagganap at tingnan kung may ginagawang malalaking paghahambing. O maaari kang magdagdag ng index:

CREATE INDEX i_release_year ON film (release_year);

At makakuha ng mga plano para sa mga sumusunod na kahilingan bilang kapalit:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

Kung gumagana ang pag-optimize, ang mga plano ng parehong mga query ay dapat na halos pareho. Ngunit sa kasong ito hindi ito ang kaso:

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

Gaya ng nakikita mo, malaki ang pagkakaiba ng aming dalawang query sa mga halaga ng POSSIBLE_KEYS at FILTERED na mga column . Kaya gusto kong makipagsapalaran ng isang makatwirang hula na hindi ito ino-optimize ng MySQL.

Oracle

Oo!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

Sa kasamaang palad hindi!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

Iba-iba ang mga plano at gastos. Lalo na, tingnan ang pagsusuri ng cardinality, na talagang hindi maganda, habang ang predicate na ito:

SELECT * FROM film WHERE release_year IS NOT NULL;

nagbibigay ng mas mahusay na mga resulta:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

Bummer!

SQL Server

Kakatwa, hindi rin ito ginagawa ng SQL Server:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

Gayunpaman, batay sa hitsura ng plano, ang pagtatasa ng cardinality ay tama, gayundin ang gastos. Ngunit sa aking karanasan sa SQL Server, sasabihin ko na sa kasong ito, walang nangyayaring pag-optimize, dahil ipapakita ng SQL Server ang aktwal na naisakatuparan na predicate sa plano (upang makita kung bakit, tingnan ang mga halimbawa ng pagpilit ng CHECK sa ibaba). Paano naman ang mga "walang kahulugan" na predicates sa NOT NULL columns? Ang conversion sa itaas ay kailangan lang dahil RELEASE_YEAR ay maaaring hindi matukoy. Ano ang mangyayari kung patakbuhin mo ang parehong walang kabuluhang query sa, halimbawa, sa FILM_ID column ?

SELECT * FROM film WHERE film_id = film_id

Ngayon ba ito ay tumutugma sa walang panaguri sa lahat? O hindi bababa sa kung paano ito dapat. Ngunit ito ba?

DB2

Oo!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

Walang mga predicate ang inilapat at pinipili namin ang lahat ng mga pelikula.

MySQL

Oo! (Muli, edukadong hula)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

Pansinin na ang EXTRA column ay walang laman na ngayon, na parang wala tayong WHERE clause!

Oracle

Oo!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

Muli, walang nalalapat na panaguri.

PostgreSQL

Wow, hindi na naman!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

Ang filter ay inilapat at ang cardinality score ay 5 pa rin. Bummer!

SQL Server

At eto ulit no!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

Buod

Tila isang simpleng pag-optimize, ngunit hindi ito ginagamit sa lahat ng DBMS; sa partikular, kakaiba, hindi ito ginagamit sa SQL Server!

5. Projection sa EXISTS subquery

Kapansin-pansin, palagi akong tinatanong tungkol sa mga ito sa aking master class, kung saan ipinagtatanggol ko ang punto ng view na ang SELECT * ay karaniwang hindi nagdudulot ng anumang kabutihan. Ang tanong ay: posible bang gamitin ang SELECT * sa isang EXISTS subquery ? Halimbawa, kung kailangan nating maghanap ng mga aktor na gumanap sa mga pelikula...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

At ang sagot ay oo. Pwede. Ang asterisk ay hindi nakakaapekto sa kahilingan. Paano ka nakakasigurado dito? Isaalang-alang ang sumusunod na query:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

Ang lahat ng mga database na ito ay nag-uulat ng isang dibisyon sa pamamagitan ng zero error. Tandaan ang isang kawili-wiling katotohanan: sa MySQL, kapag hinati namin sa zero, makakakuha kami ng NULL sa halip na isang error, kaya kailangan naming gumawa ng isa pang ilegal na aksyon. Ngayon, ano ang mangyayari kung isagawa natin, sa halip na nasa itaas, ang mga sumusunod na query?

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

Ngayon wala sa mga database ang nagbabalik ng error. Lahat sila ay nagbabalik ng TAMA o 1 . Nangangahulugan ito na wala sa aming mga database ang aktwal na sinusuri ang projection (iyon ay, ang SELECT clause ) ng EXISTS subquery . Halimbawa, ipinapakita ng SQL Server ang sumusunod na plano:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

Tulad ng makikita mo, ang CASE expression ay na-convert sa isang pare-pareho at ang subquery ay inalis. Ang iba pang mga database ay nag-iimbak ng subquery sa plano at huwag magbanggit ng anuman tungkol sa projection, kaya tingnan natin muli ang orihinal na plano ng query sa Oracle:

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Ang plano sa query sa itaas ay ganito ang hitsura:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

Inoobserbahan namin ang impormasyon tungkol sa projection sa Id=3 . Sa katunayan, hindi namin ina-access ang FILM_ACTOR table dahil hindi namin kailangan. Ang EXISTS predicate ay maaaring isagawa gamit ang isang foreign key index sa isang column na ACTOR_ID - lahat ng kailangan para sa query na ito - kahit na isinulat namin ang SELECT * .

Buod

Sa kabutihang-palad, lahat ng aming mga database ay nag-aalis ng projection mula sa EXISTS subquery : Manatiling nakatutok para sa Bahagi 3 , kung saan tatalakayin natin ang iba pang mga cool na pag-optimize ng SQL.