การเพิ่มประสิทธิภาพ SQL ที่ยอดเยี่ยมที่ไม่ขึ้นอยู่กับโมเดลต้นทุน ส่วนที่ 1
คอยติดตามส่วนที่ 3ซึ่งเราจะพูดถึงการปรับแต่ง SQL เจ๋งๆ อื่นๆ
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!ฐานข้อมูล | ภาคแสดงที่ไม่มีความหมาย แต่จำเป็น (ความหมาย NULL) | ภาคแสดงที่ไม่มีความหมายและไม่จำเป็น (ความหมายที่ไม่ใช่ NULL) |
---|---|---|
DB2 LUW 10.5 | ใช่ | ใช่ |
มายเอสแอล 8.0.2 | เลขที่ | ใช่ |
ออราเคิล 12.2.0.1 | ใช่ | ใช่ |
PostgreSQL 9.6 | เลขที่ | เลขที่ |
เซิร์ฟเวอร์ SQL 2014 | เลขที่ | เลขที่ |
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 :ฐานข้อมูล | มีการฉายภาพอยู่ |
---|---|
DB2 LUW 10.5 | ใช่ |
มายเอสแอล 8.0.2 | ใช่ |
ออราเคิล 12.2.0.1 | ใช่ |
PostgreSQL 9.6 | ใช่ |
เซิร์ฟเวอร์ SQL 2014 | ใช่ |
GO TO FULL VERSION