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

การเพิ่มประสิทธิภาพง่ายๆ ห้าประการที่สามารถใช้งานได้โดยอิงตามข้อมูลเมตาเท่านั้น (เช่น ข้อจำกัด) และการสืบค้นเอง เราขอเสนอการดัดแปลงบทความของ Lukas Eder ซึ่งออกแบบมาสำหรับผู้ที่มีความเข้าใจทั่วไปเกี่ยวกับฐานข้อมูลและ SQL รวมถึงประสบการณ์เชิงปฏิบัติกับ DBMS . การเพิ่มประสิทธิภาพต้นทุนเป็นวิธีมาตรฐานในการเพิ่มประสิทธิภาพการสืบค้น SQL ในฐานข้อมูลสมัยใหม่ ด้วยเหตุนี้จึงเป็นเรื่องยากมากที่จะเขียนอัลกอริธึมที่ซับซ้อนด้วยตนเองใน3GL (ภาษาโปรแกรมรุ่นที่สาม)ซึ่งประสิทธิภาพจะสูงกว่าแผนการดำเนินการที่คำนวณแบบไดนามิกที่สร้างโดยเครื่องมือเพิ่มประสิทธิภาพสมัยใหม่ วันนี้เราจะไม่พูดถึงการเพิ่มประสิทธิภาพต้นทุน นั่นคือ การเพิ่มประสิทธิภาพตามแบบจำลองต้นทุนของฐานข้อมูล เราจะดูการเพิ่มประสิทธิภาพที่ง่ายกว่ามาก สิ่งที่สามารถนำมาใช้ได้โดยอิงตามข้อมูลเมตาเท่านั้น (เช่น ข้อจำกัด) และตัวคำขอเอง โดยปกติแล้วการใช้งานฐานข้อมูลจะไม่ใช่ทวินามของนิวตัน เนื่องจากในกรณีนี้ การเพิ่มประสิทธิภาพใด ๆ จะนำไปสู่แผนการดำเนินการที่ดีขึ้น โดยไม่คำนึงถึงการมีดัชนี ปริมาณข้อมูล และความเบ้ของการกระจายข้อมูล "ไม่ใช่ทวินามของนิวตัน" ไม่ใช่ในแง่ที่ว่าการปรับให้เหมาะสมนั้นทำได้ง่ายเพียงใด แต่ขึ้นอยู่กับว่าควรทำหรือไม่ การเพิ่มประสิทธิภาพเหล่านี้ช่วยลดการทำงานพิเศษที่ไม่จำเป็น [สำหรับฐานข้อมูล] ( ซึ่งตรงข้ามกับงานที่ไม่จำเป็นและจำเป็น ซึ่งฉันได้เขียนไปแล้ว )

การเพิ่มประสิทธิภาพเหล่านี้ใช้เพื่ออะไร?

ส่วนใหญ่ใช้สำหรับ:

• แก้ไขข้อผิดพลาดในการสืบค้น
• อนุญาตให้ใช้มุมมองซ้ำโดยไม่ต้องฐานข้อมูลดำเนินการตรรกะมุมมองจริง ๆ

ในกรณีแรก ใครๆ ก็สามารถพูดว่า: “แล้วไงล่ะ ดำเนินการต่อและแก้ไขแบบสอบถาม SQL โง่ๆ นี้ได้เลย” แต่ให้ผู้ไม่เคยทำผิดขว้างก้อนหินใส่เราก่อน กรณีที่สองน่าสนใจเป็นพิเศษ: มันทำให้เราสามารถสร้างไลบรารีที่ซับซ้อนของมุมมองและฟังก์ชันตารางที่สามารถนำมาใช้ซ้ำได้ในหลายเลเยอร์

ฐานข้อมูลที่ใช้

ในบทความนี้เราจะเปรียบเทียบการปรับให้เหมาะสม SQL 10 รายการใน DBMS ห้ารายการที่ใช้กันอย่างแพร่หลายที่สุด ( ตามการจัดอันดับฐานข้อมูล ):

• ออราเคิล 12.2;
• มายเอสคิวแอล 8.0.2;
• เซิร์ฟเวอร์ SQL 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5

คะแนน อีกเกือบจะสะท้อนมัน ตามปกติในบทความนี้ ฉันจะสอบถามฐานข้อมูลSakila นี่คือรายการของการเพิ่มประสิทธิภาพทั้ง 10 ประเภทเหล่านี้:

1. การปิดสกรรมกริยา;
2. เพรดิเคตที่เป็นไปไม่ได้และการเรียกตารางที่ไม่จำเป็น
3. กำจัดการเข้าร่วม;
4. การกำจัดภาคแสดงที่ "ไร้ความหมาย"
5. การคาดการณ์ในแบบสอบถามย่อย EXISTS;
6. การรวมภาคแสดง;
7. ชุดว่างที่พิสูจน์ได้;
8. ตรวจสอบข้อจำกัด;
9. การเชื่อมต่อแบบสะท้อนกลับที่ไม่จำเป็น
10. ภาคแสดงการกดลง

วันนี้เราจะมาพูดถึงหน้า 1-3 ในส่วนที่สอง - 4 และ 5 และในส่วน 3 - 6-10

1. การปิดสกรรมกริยา

เรามาเริ่มด้วยสิ่งที่ง่ายกว่านี้กันก่อน: การปิดสกรรมกริยา นี่เป็นแนวคิดเล็กๆ น้อยๆ ที่ใช้ได้กับการดำเนินการทางคณิตศาสตร์หลายอย่าง เช่น ตัวดำเนินการความเท่าเทียมกัน สามารถกำหนดได้ในกรณีนี้ดังนี้: ถ้า A = B และ B = C ดังนั้น A = C

ไม่ยากใช่ไหม? แต่สิ่งนี้มีผลกระทบที่น่าสนใจสำหรับเครื่องมือเพิ่มประสิทธิภาพ SQL ลองดูตัวอย่าง มาแยกภาพยนตร์ทั้งหมดที่มี ACTOR_ID = 1:

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

ผลลัพธ์จะเป็นดังนี้:

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

ตอนนี้เรามาดูแผนการดำเนินการสืบค้นนี้ในกรณีของ Oracle DBMS:

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

ส่วนภาคแสดงมีความน่าสนใจเป็นพิเศษที่นี่ เพรดิเคต ACTOR_ID = 1 เนื่องจากการปิดสกรรมกริยา ใช้กับทั้งตาราง ACTOR และตาราง FILM_ACTOR ถ้า:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

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

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

แผนของเขา:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

อย่างที่คุณเห็น จำนวนแถวในตาราง FILM_ACTOR ถูกประเมินไว้สูงเกินไป ในขณะที่ NESTED LOOP ถูกประเมินต่ำไป นี่คือค่าที่น่าสนใจสองสามค่า:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

ผลลัพธ์:

19
27.315

นี่คือที่มาของการประมาณการ หากฐานข้อมูลรู้ว่าเรากำลังพูดถึง ACTOR_ID = 1 ก็สามารถรวบรวมสถิติจำนวนภาพยนตร์ของนักแสดงคนนี้ได้ หากไม่เป็นเช่นนั้น (เนื่องจากกลไกการเก็บสถิติมาตรฐานไม่มีความสัมพันธ์ระหว่าง FIRST_NAME/LAST_NAME กับ ACTOR_ID) เราก็จะได้จำนวนภาพยนตร์โดยเฉลี่ยสำหรับนักแสดง ทุกคน ข้อผิดพลาดที่เรียบง่ายและไม่สำคัญในกรณีนี้ แต่ในแบบสอบถามที่ซับซ้อน ข้อผิดพลาดนี้สามารถแพร่กระจายเพิ่มเติม สะสม และนำไปสู่แบบสอบถามเพิ่มเติม (สูงกว่าในแผน) ไปยังตัวเลือก JOIN ที่ไม่ถูกต้อง ดังนั้นเมื่อใดก็ตามที่คุณทำได้ ให้ออกแบบการรวมและภาคแสดงอย่างง่ายของคุณเพื่อใช้ประโยชน์จากการปิดสกรรมกริยา ฐานข้อมูลอื่นใดที่สนับสนุนคุณลักษณะนี้?

ดีบี2

ใช่!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

อย่างไรก็ตาม หากคุณชอบแผนการดำเนินการที่ยอดเยี่ยมเช่นนี้ ลองดูสคริปต์ ของ Markus Winand

MySQL

น่าเสียดายที่แผนการดำเนินการ MySQL ไม่เหมาะกับการวิเคราะห์ประเภทนี้ เพรดิเคตนั้นหายไปจากข้อมูลเอาต์พุต:

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

แต่ข้อเท็จจริงที่มีการระบุ const สองครั้งในคอลัมน์ REF แสดงว่าทั้งสองตารางกำลังค้นหาค่าคงที่ ในเวลาเดียวกัน แผนการสืบค้นกับ FIRST_NAME/LAST_NAME มีลักษณะดังนี้:

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

และอย่างที่คุณเห็น ขณะนี้ REF อ้างอิงคอลัมน์จากเพรดิเคต JOIN คะแนนคาร์ดินัลลิตี้เกือบจะเหมือนกับใน Oracle ใช่แล้ว MySQL รองรับการปิดสกรรมกริยาด้วย

PostgreSQL

ใช่!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

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

ใช่!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

สรุป

ฐานข้อมูลทั้งหมดของเรารองรับการปิดแบบสกรรมกริยา อย่างไรก็ตาม โปรดรอ #6 ในส่วนถัดไปของบทความ มีกรณีที่ซับซ้อนของการปิดสกรรมกริยาซึ่งบางฐานข้อมูลไม่สามารถจัดการได้

2. ภาคแสดงที่เป็นไปไม่ได้และการเรียกตารางที่ไม่จำเป็น

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

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

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

ดีบี2

ใช่!

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

อย่างที่คุณเห็น การเข้าถึงตาราง ACTOR นั้นไม่รวมอยู่ในแผนโดยสิ้นเชิง ประกอบด้วยการดำเนินการ GENROW เท่านั้น ซึ่งจะสร้างแถวเป็นศูนย์ สมบูรณ์แบบ.

MySQL

ใช่!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

ครั้งนี้ MySQL ใจดีพอที่จะแจ้งให้เราทราบเกี่ยวกับ WHERE clause ที่เป็นไปไม่ได้ ขอบคุณ! ทำให้การวิเคราะห์ง่ายขึ้นมาก โดยเฉพาะเมื่อเปรียบเทียบกับฐานข้อมูลอื่นๆ

ออราเคิล

ใช่!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

เราเห็นว่าแผนยังคงกล่าวถึงการเข้าถึงตาราง ACTOR และจำนวนแถวที่คาดหวังยังคงเป็น 200 แถว แต่ยังมีการดำเนินการกรอง (FILTER) ด้วย Id=1 โดยที่ไม่มีวันเป็นจริง เนื่องจาก Oracle ไม่ชอบประเภทข้อมูล SQL Boolean มาตรฐาน Oracle จึงแสดง NULL IS NOT NULL ในแผน แทนที่จะเป็นเพียง FALSE โอ้... แต่เอาจริงๆ นะ ดูภาคแสดงนั้นสิ ฉันมีโอกาสแก้ไขแผนการดำเนินการด้วยทรีย่อย 1,000 บรรทัดและมูลค่าต้นทุนที่สูงมาก เพียงเพื่อจะค้นพบหลังจากข้อเท็จจริงที่ว่าทรีย่อยทั้งหมดถูก "ตัดออก" โดยตัวกรอง NULL IS NOT NULL ท้อใจนิดหน่อยนะบอกเลย

PostgreSQL

ใช่!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

ดีขึ้นแล้ว. ไม่มีการเรียกตาราง ACTOR ที่น่ารำคาญ และภาคแสดง FALSE เล็กๆ น้อยๆ ที่เรียบร้อย

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

ใช่!

|--Constant Scan

SQL Server เรียกสิ่งนี้ว่า " การสแกน อย่างต่อเนื่อง " ซึ่งเป็นการสแกนที่ไม่มีอะไรเกิดขึ้น - คล้ายกับ DB2 ฐานข้อมูลทั้งหมดของเราสามารถยกเว้นภาคแสดงที่เป็นไปไม่ได้:

3. กำจัด JOIN

ในส่วนก่อนหน้านี้ เราสังเกตเห็นการเข้าถึงตารางที่ไม่จำเป็นในการสืบค้นแบบตารางเดียว แต่จะเกิดอะไรขึ้นหาก JOIN ไม่ต้องการการเข้าถึงตารางอย่างใดอย่างหนึ่ง ฉันได้เขียนเกี่ยวกับการยกเลิก JOIN ในโพสต์ก่อนหน้าจากบล็อกของฉันแล้ว เอ็นจิ้น SQL สามารถระบุได้ว่าจำเป็นต้องใช้ JOIN เฉพาะในเคียวรีที่กำหนดหรือไม่ โดยขึ้นอยู่กับประเภทของเคียวรีและการมีอยู่ของคีย์หลักและคีย์ต่างประเทศ หรือการตัดออกจะไม่ส่งผลต่อความหมายของเคียวรีหรือไม่ ในสามตัวอย่างถัดไปทั้งหมด ไม่จำเป็นต้อง JOIN การเข้าร่วม ...-to-one ภายในสามารถกำจัดออกได้โดยใช้คีย์ต่างประเทศ NOT NULL แทน:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

ฐานข้อมูลสามารถทำสิ่งต่อไปนี้:

SELECT first_name, last_name
FROM customer c

INNER JOIN ประเภท "...-to-one" สามารถแทนที่ได้หากมีคีย์นอกที่เป็นโมฆะ แบบสอบถามข้างต้นใช้งานได้หากคีย์ต่างประเทศอยู่ภายใต้ข้อจำกัด NOT NULL หากไม่เป็นเช่นนั้น ดังเช่นในคำขอนี้:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

จากนั้น JOIN ก็ยังสามารถถูกกำจัดได้ แต่คุณจะต้องเพิ่มเพรดิเคต NOT NULL เช่นนี้

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

OUTER JOIN ของประเภท "...-to-one" สามารถลบออกได้หากมีคีย์เฉพาะ แทนสิ่งนี้:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

ฐานข้อมูลสามารถทำสิ่งต่อไปนี้ได้อีกครั้ง:

SELECT first_name, last_name
FROM customer c

... แม้ว่าจะไม่มีคีย์ต่างประเทศสำหรับ customer.ADDRESS_ID ก็ตาม การเชื่อมต่อภายนอกที่ไม่ซ้ำกัน (DISTINCT OUTER JOIN) ของประเภท "...-to-many" สามารถลบออกได้ แทนสิ่งนี้:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

ฐานข้อมูลสามารถทำสิ่งต่อไปนี้:

SELECT DISTINCT first_name, last_name
FROM actor a

ตัวอย่างทั้งหมดนี้ได้รับการศึกษาอย่างละเอียดในบทความที่แล้ว ดังนั้นฉันจะไม่ทำซ้ำ แต่จะสรุปทุกสิ่งที่ฐานข้อมูลต่างๆ สามารถกำจัดได้: ขออภัย ไม่ใช่ทุกฐานข้อมูลที่สามารถแก้ไขการเชื่อมต่อได้ทุกประเภท DB2 และ SQL Server เป็นผู้นำที่ไม่มีปัญหาที่นี่! ยังมีต่อ