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

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

8. ตรวจสอบข้อจำกัด

โอ้ นี่มันสิ่งดีๆ! ฐานข้อมูลSakila ของเรา มี ข้อจำกัดใน การตรวจสอบใน คอลัมน์ FILM.RATING :

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

อย่างจริงจัง ใช้ ข้อจำกัด CHECKเพื่อรับรองความสมบูรณ์ของข้อมูล ค่าใช้จ่ายในการเพิ่มนั้นต่ำมาก - น้อยกว่าข้อจำกัดอื่น ๆ เช่นPRIMARY , UNIQUEหรือFOREIGN KEYเนื่องจากไม่ต้องการดัชนีในการทำงาน ดังนั้นคุณจึงใช้งานได้จริง "ฟรี" แต่มีความแตกต่างที่น่าสนใจเกี่ยวกับการเพิ่มประสิทธิภาพ! พิจารณาคำถามต่อไปนี้:

ภาคแสดงที่เป็นไปไม่ได้

เราได้พบกับเพรดิเคตที่เป็นไปไม่ได้แล้ว แม้จะ ไม่ใช่ข้อจำกัด NULL (ซึ่งจริงๆ แล้วเป็นชนิดพิเศษของCHECK constraint ) แต่อันนี้เจ๋งกว่า:

SELECT *
FROM film
WHERE rating = 'N/A';

ไม่มีภาพยนตร์ดังกล่าว และไม่สามารถมีได้ เนื่องจาก ข้อจำกัด CHECKป้องกันการแทรก (หรืออัปเดต) อีกครั้ง สิ่งนี้ควรแปลเป็นคำสั่งที่จะไม่ทำอะไรเลย แล้วคำขอนี้ล่ะ?

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

ต้องขอบคุณดัชนีด้านบน เพียงแค่สแกนดัชนีอย่างรวดเร็วและนับภาพยนตร์ทั้งหมดที่มีเรตติ้ง = 'NC-17' ก็ เพียงพอ แล้ว เนื่องจากเหลือเพียงเรตติ้งเดียวเท่านั้น ดังนั้นควรเขียนแบบสอบถามใหม่ดังนี้:

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

ซึ่งควรเป็นกรณีนี้โดยไม่คำนึงถึงดัชนี เนื่องจากการเปรียบเทียบคอลัมน์กับค่าเดียวจะเร็วกว่าการเปรียบเทียบกับ 4 ดังนั้นฐานข้อมูลใดที่สามารถทำได้?

ดีบี2

ภาคแสดงที่เป็นไปไม่ได้ (คะแนน = 'N/A') เจ๋ง!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

Predicate Information
 2 - RESID (1 = 0)

ภาคแสดงย้อนกลับ (เรตติ้ง = 'NC-17') ไม่...

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

แม้ว่าขั้นตอน ID=3 จะใช้ดัชนี และแม้ว่าจำนวนสมาชิกถูกต้อง การสแกนแบบเต็มเกิดขึ้นเนื่องจากแผนไม่มีเพรดิเคตช่วง มีเพียงเพรดิเคต "SARG" เท่านั้น ดูบทวิจารณ์ของ Marcus Wynand เพื่อดูราย ละเอียด คุณยังสามารถสาธิตสิ่งนี้ได้โดยการกลับภาคแสดงด้วยตนเองและรับ:

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

ตอนนี้เรามีภาคแสดงพิสัยที่ต้องการแล้ว

MySQL

MySQL รองรับ ไวยากรณ์ข้อจำกัด CHECKแต่ด้วยเหตุผลบางอย่างไม่ได้บังคับใช้ ลองสิ่งนี้:

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

และคุณจะได้รับ:

A
-
0

ศูนย์คะแนนสำหรับ MySQL (จริง ๆ แล้วทำไมไม่รองรับ ข้อจำกัด CHECK เท่านั้น )

ออราเคิล

ภาคแสดงที่เป็นไปไม่ได้ (คะแนน = 'N/A')

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

ขอย้ำอีกครั้งว่าตัวกรองที่แปลกมากNULL IS NOT NULLตัดFULL TABLE SCANซึ่งสามารถลบออกจากแผนได้อย่างง่ายดายเช่นกัน แต่อย่างน้อยมันก็ได้ผล! ภาคแสดงย้อนกลับ (คะแนน = 'NC-17') อ๊ะ:

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

เพรดิเคตไม่สามารถกลับด้านได้ การประเมินจำนวนนับนั้นง่อยมาก นอกจากนี้เรายังได้รับINDEX FAST FULL SCANแทนINDEX RANGE SCANและ เพรดิเคต ตัวกรองแทน เพรดิเคต การเข้าถึง แต่นี่คือสิ่งที่เราควรได้รับ ตัวอย่างเช่น โดยการกลับภาคแสดงด้วยตนเอง:

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

แย่จัง!

PostgreSQL

โปรดทราบว่าฐานข้อมูลSakila เวอร์ชัน PostgreSQL ใช้ ประเภท ENUM แทนข้อจำกัดCHECKในคอลัมน์RATING ฉัน ทำซ้ำตารางโดยใช้ข้อจำกัด CHECKแทน ภาคแสดงที่เป็นไปไม่ได้ (คะแนน = 'N/A') ใช้ไม่ได้:

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

ภาคแสดงย้อนกลับ (เรตติ้ง = 'NC-17') ใช้ไม่ได้เช่นกัน:

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

เสียใจมาก! หมายเหตุ:ตามที่David Rowley กรุณาชี้ให้เราทราบในความคิดเห็นคุณลักษณะนี้สามารถเปิดใช้งานได้โดยการตั้งค่าพารามิเตอร์:

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

ภาคแสดงย้อนกลับ (คะแนน = 'NC-17') ใช่ด้วย!

|--Compute Scalar
     |--Stream Aggregate
          |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

สรุป

9. การเชื่อมต่อแบบสะท้อนกลับที่ไม่จำเป็น

เมื่อแบบสอบถามของคุณซับซ้อนมากขึ้น คุณอาจจำเป็นต้องดำเนินการรวมแบบสะท้อนกลับบนโต๊ะโดยยึดตามคีย์หลัก เชื่อฉันเถอะ นี่เป็นแนวทางปฏิบัติทั่วไปในการสร้างมุมมองที่ซับซ้อนและเชื่อมต่อเข้าด้วยกัน ดังนั้นการตรวจสอบให้แน่ใจว่าฐานข้อมูลให้ความสำคัญกับสิ่งนี้เป็นส่วนสำคัญในการปรับโค้ด SQL ที่ซับซ้อนให้เหมาะสม ฉันจะไม่แสดงตัวอย่างที่ซับซ้อน แค่ตัวอย่างง่ายๆ ก็เพียงพอแล้ว เช่น

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

นี่ถือเป็นกรณีพิเศษ ของการ กำจัดJOINเนื่องจากเราไม่ต้องการการรวมA2 จริงๆ เราจึงสามารถทำทุกอย่างที่เราต้องการด้วยเพียงตารางA1 ถัดไปการกำจัด INNER JOIN จะทำงานได้อย่างถูกต้องหากมี FOREIGN KEYซึ่งเราไม่มีที่นี่ แต่ต้องขอบคุณคีย์หลักโดยACTOR_IDเราสามารถพิสูจน์ได้ว่าในความเป็นจริงA1 = A2 ในแง่หนึ่ง นี่คือการปิดสกรรมกริยาอีกครั้ง คุณสามารถไปได้ไกลกว่านี้และใช้คอลัมน์จากทั้งตารางA1และA2 :

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

ในกรณีคลาสสิกของJOIN elimination จะไม่สามารถกำจัดได้อีกต่อไป เนื่องจากทั้งสองตารางถูกฉายภาพ แต่เนื่องจากเราได้พิสูจน์แล้วว่าA1 = A2จึงสามารถใช้แทนกันได้ ดังนั้นเราจึงสามารถแปลงแบบสอบถามเป็น:

SELECT first_name, last_name
FROM actor;

DBMS ใดที่สามารถทำได้?

ดีบี2

การฉายภาพเฉพาะตาราง A1 เท่านั้น ใช่:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

การฉายภาพตาราง A1 และ A2 ... ใช่ด้วย:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

MySQL

การฉายภาพเฉพาะตาราง A1 เท่านั้น

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

การฉายภาพตาราง A1 และ A2 ... หมายเลขเช่นกัน

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

ผิดหวังเต็มๆ...

ออราเคิล

การฉายภาพของตาราง A1 เท่านั้น ใช่

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

การฉายภาพตาราง A1 และ A2 ใช่อีกครั้ง

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

การฉายภาพเฉพาะตาราง A1 เท่านั้น หมายเลข:

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

การฉายภาพตาราง A1 และ A2 และไม่มีอีกครั้ง:

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

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

การฉายภาพตาราง A1 เท่านั้น ผิดปกติพอไม่มี! (แต่โปรดจำไว้ว่าฉันใช้ SQL Server 2014 เวอร์ชันใหม่กว่าอาจมีการแก้ไขนี้ ฉันสามารถใช้การอัพเกรดได้อย่างแน่นอน!)

|--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
     |--Index Scan(OBJECT:([a2]))
     |--Sort(ORDER BY:([a1].[actor_id] ASC))
          |--Table Scan(OBJECT:([a1]))

การฉายตาราง A1 และ A2 ไม่ได้อีกแล้วและแผนก็เปลี่ยนไปในทางที่แย่ลง:

|--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

สรุป

พูดตามตรง ฉันคาดหวังว่าการเพิ่มประสิทธิภาพนี้จะดำเนินการกับฐานข้อมูลทั้งหมด แต่ฉันคิดผิดมากอย่างน่าเศร้า นอกเหนือจากการกำจัด JOIN แล้วนี่ยังเป็นหนึ่งในการปรับปรุงประสิทธิภาพที่สำคัญที่สุด ซึ่งช่วยให้คุณสร้างคำสั่ง SQL ขนาดใหญ่จากส่วนที่นำมาใช้ซ้ำได้ เช่น มุมมองและฟังก์ชันตาราง น่าเสียดายที่ฐานข้อมูลทั่วไป 3 ใน 5 นี้ไม่รองรับ