การเพิ่มประสิทธิภาพ SQL ที่ยอดเยี่ยมที่ไม่ขึ้นอยู่กับโมเดลต้นทุน ส่วนที่ 2

การเพิ่มประสิทธิภาพ SQL ที่ยอดเยี่ยมที่ไม่ขึ้นอยู่กับโมเดลต้นทุน ส่วนที่ 1

4. การกำจัดภาคแสดงที่ "ไร้ความหมาย"

ส่วนที่ไม่มีความหมายพอๆ กันคือภาคแสดงที่ (เกือบ) เป็นความจริงเสมอไป อย่างที่คุณคงจินตนาการได้ หากคุณกำลังถามว่า:

SELECT * FROM actor WHERE 1 = 1;

... จากนั้นฐานข้อมูลจะไม่ดำเนินการจริง แต่จะเพิกเฉยต่อมัน ฉันเคยตอบคำถามเกี่ยวกับเรื่องนี้ใน Stack Overflowและนั่นคือสาเหตุที่ฉันตัดสินใจเขียนบทความนี้ ฉันจะปล่อยให้การทดสอบนี้เป็นแบบฝึกหัดสำหรับผู้อ่าน แต่จะเกิดอะไรขึ้นถ้าภาคแสดงนั้น "ไร้ความหมาย" น้อยลงเล็กน้อย? ตัวอย่างเช่น:

SELECT * FROM film WHERE release_year = release_year;

คุณจำเป็นต้องเปรียบเทียบค่ากับตัวมันเองสำหรับแต่ละแถวจริงๆ หรือไม่? ไม่ ไม่มีค่าใดที่ภาคแสดงนี้จะเป็นFALSEใช่ไหม แต่เรายังต้องตรวจสอบมัน แม้ว่าภาคแสดงจะไม่สามารถเท่ากับFALSE ได้ แต่ก็อาจจะเท่ากับNULL ทุกที่ อีกครั้งเนื่องจากตรรกะสามค่า คอลัมน์RELEASE_YEARเป็นโมฆะได้ และหากแถวใดแถวหนึ่งมีRELEASE_YEAR IS NULLดังนั้นNULL = NULL จะ ให้ผลลัพธ์เป็นNULLและจะต้องตัดแถวนั้นออก ดังนั้นคำขอจึงกลายเป็นดังนี้:

SELECT * FROM film WHERE release_year IS NOT NULL;

ฐานข้อมูลใดที่ทำเช่นนี้?

ดีบี2

ใช่!

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

น่าเสียดาย แต่อีกครั้งที่ MySQL ไม่ได้แมปเพรดิเคตเข้ากับแผนการดำเนินการ ดังนั้นการพิจารณาว่า MySQL ใช้การปรับให้เหมาะสมนี้โดยเฉพาะหรือไม่นั้นค่อนข้างยุ่งยากเล็กน้อย คุณสามารถทำการประเมินประสิทธิภาพและดูว่ามีการเปรียบเทียบขนาดใหญ่หรือไม่ หรือคุณสามารถเพิ่มดัชนี:

CREATE INDEX i_release_year ON film (release_year);

และรับแผนสำหรับคำขอต่อไปนี้เป็นการตอบแทน:

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

หากการปรับให้เหมาะสมใช้งานได้ แผนของการสืบค้นทั้งสองควรจะใกล้เคียงกัน แต่ในกรณีนี้ นี่ไม่ใช่กรณี:

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

อย่างที่คุณเห็น แบบสอบถามทั้ง สอง ของเราแตกต่างกันอย่างมีนัยสำคัญในค่าของ คอลัมน์ POSSIBLE_KEYSและFILTERED ดังนั้นฉันจึงเดาได้อย่างสมเหตุสมผลว่า MySQL ไม่ได้ปรับให้เหมาะสม

ออราเคิล

ใช่!

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

น่าเสียดายที่ไม่!

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

แผนและค่าใช้จ่ายแตกต่างกันไป กล่าวคือ ดูการประเมินภาวะเชิงการนับซึ่งไม่ดีอย่างแน่นอน ในขณะที่ภาคแสดงนี้:

SELECT * FROM film WHERE release_year IS NOT NULL;

ให้ผลลัพธ์ที่ดีกว่ามาก:

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

แย่จัง!

เซิร์ฟเวอร์ SQL

น่าแปลกที่ SQL Server ดูเหมือนจะไม่ทำเช่นนี้:

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

อย่างไรก็ตาม ตามลักษณะของแผน การประเมินจำนวนสมาชิกนั้นถูกต้อง เช่นเดียวกับต้นทุน แต่จากประสบการณ์ของฉันกับ SQL Server ฉันจะบอกว่าในกรณีนี้ ไม่มีการเพิ่มประสิทธิภาพเกิดขึ้น เนื่องจาก SQL Server จะแสดงเพรดิเคตที่ดำเนินการจริงในแผน (เพื่อดูว่าทำไม โปรดดูตัวอย่างข้อ จำกัด การตรวจสอบด้านล่าง) แล้วภาคแสดง "ไร้ความหมาย" ใน คอลัมน์ NOT NULL ล่ะ ? การแปลงข้างต้นจำเป็นเท่านั้นเนื่องจากไม่สามารถกำหนดRELEASE_YEAR ได้ จะเกิดอะไรขึ้นหากคุณเรียกใช้คำค้นหาที่ไม่มีความหมายเดียวกันบน คอลัมน์ FILM_IDเช่น

SELECT * FROM film WHERE film_id = film_id

ตอนนี้มันไม่สอดคล้องกับภาคแสดงเลยเหรอ? หรืออย่างน้อยนั่นคือสิ่งที่ควรจะเป็น แต่มันคืออะไร?

ดีบี2

ใช่!

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

ไม่มีการใช้ภาคแสดงเลย และเราเลือกภาพยนตร์ทั้งหมด

MySQL

ใช่! (อีกครั้งการคาดเดาที่มีการศึกษา)

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

โปรดสังเกตว่า คอลัมน์ EXTRAตอนนี้ว่างเปล่า ราวกับว่าเราไม่มีส่วนคำสั่ง WHERE เลย!

ออราเคิล

ใช่!

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

ขอย้ำอีกครั้งว่าไม่มีภาคแสดง

PostgreSQL

ว้าวไม่มีอีกแล้ว!

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

ใช้ตัวกรองแล้วและคะแนนเชิงจำนวนยังคงเป็น 5 แย่จัง!

เซิร์ฟเวอร์ SQL

และที่นี่อีกครั้งไม่!

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

สรุป

ดูเหมือนเป็นการปรับให้เหมาะสมอย่างง่าย ๆ แต่ไม่ได้ใช้ใน DBMS ทั้งหมด โดยเฉพาะอย่างยิ่งที่แปลกก็คือไม่ได้ใช้ใน SQL Server!

5. การคาดการณ์ในแบบสอบถามย่อย EXISTS

ที่น่าสนใจคือฉันถูกถามเกี่ยวกับพวกเขาตลอดเวลาในชั้นเรียนของฉัน โดยที่ฉันปกป้องมุมมองที่ว่าSELECT *มักจะไม่นำไปสู่สิ่งที่ดีใดๆ คำถามคือ: เป็นไปได้ไหมที่จะใช้SELECT *ใน แบบสอบถามย่อย EXISTS เช่น ถ้าเราต้องการหานักแสดงที่เล่นหนัง...

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
)

และคำตอบคือ...ใช่ สามารถ. เครื่องหมายดอกจันไม่ส่งผลต่อคำขอ คุณจะมั่นใจเรื่องนี้ได้อย่างไร? พิจารณาคำถามต่อไปนี้:

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

ฐานข้อมูลทั้งหมดเหล่านี้รายงานการหารด้วยข้อผิดพลาดเป็นศูนย์ โปรดทราบข้อเท็จจริงที่น่าสนใจ: ใน MySQL เมื่อเราหารด้วยศูนย์ เราจะได้ค่าNULLแทนที่จะเป็นข้อผิดพลาด ดังนั้นเราจึงต้องดำเนินการที่ผิดกฎหมายอีกครั้ง ทีนี้ จะเกิดอะไรขึ้นถ้าเราดำเนินการคำสั่งต่อไปนี้แทนการดำเนินการข้างต้น

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

ตอนนี้ไม่มีฐานข้อมูลใดส่งคืนข้อผิดพลาด พวก เขาทั้งหมดส่งคืนTRUEหรือ1 ซึ่งหมายความว่าไม่มีฐานข้อมูล ใดของเราประเมินการฉายภาพ (นั่นคือSELECT clause ) ของ แบบสอบถามย่อย EXISTS ตัวอย่างเช่น SQL Server แสดงแผนต่อไปนี้:

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

อย่างที่คุณเห็น นิพจน์ CASEได้ถูกแปลงเป็นค่าคงที่ และแบบสอบถามย่อยได้ถูกกำจัดไปแล้ว ฐานข้อมูลอื่นๆ เก็บแบบสอบถามย่อยไว้ในแผนและไม่ได้กล่าวถึงสิ่งใดเกี่ยวกับการฉายภาพ ดังนั้นเรามาดูแผนการสืบค้นดั้งเดิมใน Oracle อีกครั้ง:

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

แผนการสืบค้นข้างต้นมีลักษณะดังนี้:

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

เรา สังเกต ข้อมูลเกี่ยวกับเส้นโครงที่Id=3 ที่จริงแล้ว เราไม่ได้เข้าถึง ตาราง FILM_ACTOR ด้วย ซ้ำเพราะเราไม่จำเป็น เพรดิเคตEXISTSสามารถทำได้โดยใช้ดัชนีคีย์นอกใน คอลัมน์ ACTOR_ID เดียว ซึ่งทั้งหมดนี้จำเป็นสำหรับการสืบค้น นี้ แม้ว่าเราจะเขียนSELECT * ก็ตาม

สรุป

โชคดีที่ฐานข้อมูลทั้งหมดของเราลบการฉายภาพออกจาก แบบสอบถามย่อย EXISTS : คอยติดตามส่วนที่ 3ซึ่งเราจะพูดถึงการปรับแต่ง SQL เจ๋งๆ อื่นๆ