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

10. การผลักภาคแสดง
การเพิ่มประสิทธิภาพนี้ไม่เหมาะสมโดยสิ้นเชิงในที่นี้ เนื่องจากไม่สามารถพูดได้ว่าไม่ได้ขึ้นอยู่กับโมเดลต้นทุนเลย แต่เนื่องจากฉันไม่สามารถนึกถึงเหตุผลเดียวว่าทำไมตัวเพิ่มประสิทธิภาพจึงไม่ควรพุชเพรดิเคตลงในตารางที่ได้รับ ฉันจะแสดงรายการไว้ที่นี่ พร้อมด้วยการปรับให้เหมาะสมที่ไม่ต้องใช้ต้นทุนส่วนที่เหลือ พิจารณาคำขอ:SELECT *
FROM (
SELECT *
FROM actor
) a
WHERE a.actor_id = 1;
ตารางที่ได้รับในแบบสอบถามนี้ไม่สมเหตุสมผล และควรถูกกำจัดออกโดยการลดจำนวนระดับการซ้อนแบบสอบถาม แต่ขอเพิกเฉยต่อสิ่งนั้นในตอนนี้ คุณสามารถคาดหวังให้ฐานข้อมูลดำเนินการแบบสอบถามต่อไปนี้แทนข้างต้น:
SELECT *
FROM (
SELECT *
FROM actor
WHERE actor_id = 1
) a;
และอีกครั้ง อาจจะกำจัดคำขอจากภายนอก ได้รับตัวอย่างที่ซับซ้อนมากขึ้นโดยใช้UNION :
SELECT *
FROM (
SELECT first_name, last_name, 'actor' type
FROM actor
UNION ALL
SELECT first_name, last_name, 'customer' type
FROM customer
) people
WHERE people.last_name = 'DAVIS';
ผลลัพธ์ของแบบสอบถามนี้:
FIRST_NAME LAST_NAME TYPE
----------------------------
JENNIFER DAVIS actor
SUSAN DAVIS actor
SUSAN DAVIS actor
JENNIFER DAVIS customer
ตอนนี้ คงจะดีไม่น้อยหากเครื่องมือเพิ่มประสิทธิภาพฐานข้อมูลเรียกใช้แบบสอบถามในลักษณะนี้แทน:
SELECT *
FROM (
SELECT first_name, last_name, 'actor' type
FROM actor
WHERE last_name = 'DAVIS'
UNION ALL
SELECT first_name, last_name, 'customer' type
FROM customer
WHERE last_name = 'DAVIS'
) people;
นั่นคือ เพื่อที่จะ ดัน เพรดิเคตลงในตารางที่ได้รับ และจากนั้นไปยังแบบสอบถามย่อยUNION ALL สองรายการ เนื่องจากท้ายที่สุดแล้ว เรามีดัชนีทั้งใน คอลัมน์ ACTOR.LAST_NAMEและ คอลัมน์ Customers.LAST_NAME ขอย้ำอีกครั้งว่าการแปลงนี้อาจขึ้นอยู่กับการประมาณการต้นทุนในฐานข้อมูลส่วนใหญ่ แต่ฉันก็ยังคิดว่ามันไม่ใช่เรื่องง่าย เนื่องจากด้วยอัลกอริธึมใดๆ ก็ตาม แทบจะจะดีกว่าเสมอที่จะลดจำนวนสิ่งอันดับที่ประมวลผลให้เร็วที่สุด หากคุณทราบกรณีที่การเปลี่ยนแปลงดังกล่าวกลายเป็นความคิดที่ไม่ดี ฉันยินดีที่จะรับฟังความคิดเห็นของคุณ! ฉันจะสนใจมาก แล้วฐานข้อมูลไหนของเราที่สามารถทำได้? (และได้โปรดเถอะ มันง่ายและสำคัญมาก ให้คำตอบเป็น: ทุกสิ่งทุกอย่าง)
ดีบี2
ตารางอนุพันธ์อย่างง่าย ใช่Explain Plan
--------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 6
2 | FETCH ACTOR | 1 of 1 (100.00%) | 6
3 | IXSCAN PK_ACTOR | 1 of 200 ( .50%) | 0
Predicate Information
3 - START (Q1.ACTOR_ID = 1)
STOP (Q1.ACTOR_ID = 1)
ตารางที่ได้รับมาพร้อมกับ UNION ใช่ด้วย:
Explain Plan
-----------------------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 20
2 | UNION | 2 of 1 | 20
3 | FETCH CUSTOMER | 1 of 1 (100.00%) | 13
4 | IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 ( .17%) | 6
5 | FETCH ACTOR | 1 of 1 (100.00%) | 6
6 | IXSCAN IDX_ACTOR_LAST_NAME | 1 of 200 ( .50%) | 0
Predicate Information
4 - START (Q1.LAST_NAME = 'DAVIS')
STOP (Q1.LAST_NAME = 'DAVIS')
6 - START (Q3.LAST_NAME = 'DAVIS')
STOP (Q3.LAST_NAME = 'DAVIS')
นอกจากนี้ ในทั้งสองกรณี ตารางที่ได้รับ (มุมมอง) ก็ถูกแยกออกจากแผนเนื่องจากไม่จำเป็นจริงๆ
MySQL
ตารางอนุพันธ์อย่างง่าย ใช่ID TABLE TYPE KEY REF EXTRA
---------------------------------------
1 actor const PRIMARY const
มีการใช้การเข้าถึงคีย์หลักตามปกติตามค่าคงที่ ตารางที่ได้รับมาด้วย UNION อ๊ะ หมายเลข
ID SELECT_TYPE TABLE TYPE KEY REF ROWS EXTRA
------------------------------------------------------------------
1 PRIMARY ref const 10
2 DERIVED actor ALL 200
3 UNION customer ALL 599
ผลลัพธ์การแปลงด้วยตนเองในแผน:
ID SELECT_TYPE TABLE TYPE KEY REF ROWS EXTRA
--------------------------------------------------------------------------
1 PRIMARY ALL 5
2 DERIVED actor ref idx_actor_last_name const 3
3 UNION customer ref idx_last_name const 1
นี่เป็นปัญหาร้ายแรงเมื่อใช้คิวรีแบบซ้อนที่ซับซ้อนใน MySQL!
ออราเคิล
ตารางที่ได้รับอย่างง่าย ใช่ มันใช้งานได้---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| ACTOR | 1 | 1 | 1 |
|* 2 | INDEX UNIQUE SCAN | PK_ACTOR | 1 | 1 | 1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ACTOR"."ACTOR_ID"=1)
และจำนวนระดับการทำรังก็ลดลง ตารางที่ได้รับจาก UNION ก็ใช้งานได้เช่นกัน:
---------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | VIEW | | 4 |
| 2 | UNION-ALL | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR | 3 |
|* 4 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 1 |
|* 6 | INDEX RANGE SCAN | IDX_CUSTOMER_LAST_NAME | 1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("LAST_NAME"='DAVIS')
6 - access("LAST_NAME"='DAVIS')
อย่างไรก็ตาม โดยไม่ลดจำนวนระดับการทำรัง Id=1 "View"แสดงว่าตารางที่ได้รับยังคงอยู่ ในกรณีนี้ไม่ใช่ปัญหาใหญ่ อาจมีค่าใช้จ่ายเพิ่มเติมเล็กน้อย
PostgreSQL
ตารางที่ได้รับอย่างง่าย ใช่ มันใช้งานได้:QUERY PLAN
----------------------------------------------------
Seq Scan on actor (cost=0.00..4.50 rows=1 width=25)
Filter: (actor_id = 1)
อย่างไรก็ตาม โปรดทราบว่าบางครั้ง PostgreSQL ไม่ได้ใช้คีย์หลักเพื่อค้นหาแถวเดียวด้วยซ้ำ แต่จะสแกนทั้งตารางแทน ในกรณีนี้ 200 แถว × 25 ไบต์ต่อแถว ("ความกว้าง") จะพอดีในหนึ่งบล็อก ดังนั้นอะไรคือประเด็นที่รบกวนการอ่านดัชนี นอกเหนือจากการสร้างการดำเนินการ I/O ที่ไม่จำเป็นเพื่อเข้าถึงตารางขนาดเล็กเช่นนี้ ตารางที่ได้รับมาด้วย UNION ใช่ มันใช้งานได้เช่นกัน:
QUERY PLAN
-----------------------------------------------------------------------------------
Append (cost=0.00..12.83 rows=4 width=45)
-> Seq Scan on actor (cost=0.00..4.50 rows=3 width=45)
Filter: ((last_name)::text = 'DAVIS'::text)
-> Index Scan using idx_last_name on customer (cost=0.28..8.29 rows=1 width=45)
Index Cond: ((last_name)::text = 'DAVIS'::text)
ขอย้ำอีกครั้งว่า ไม่ได้ใช้ดัชนีใน คอลัมน์ ACTOR.LAST_NAME แต่ ใช้ดัชนีใน คอลัมน์ Customers.LAST_NAME เนื่องจากตาราง ลูกค้ามีขนาดใหญ่กว่ามาก
เซิร์ฟเวอร์ SQL
ตารางที่ได้รับอย่างง่าย ใช่ มันใช้งานได้|--Nested Loops(Inner Join)
|--Index Seek(SEEK:([actor_id]=(1)))
|--RID Lookup(OBJECT:([actor]))
ตารางที่สืบทอดมาด้วย UNION ก็ใช้งานได้เช่นกัน
|--Concatenation
|--Compute Scalar(DEFINE:([Expr1003]='actor'))
| |--Nested Loops(Inner Join)
| |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
| |--RID Lookup(OBJECT:([actor]))
|--Compute Scalar(DEFINE:([Expr1007]='customer'))
|--Nested Loops(Inner Join)
|--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
|--RID Lookup(OBJECT:([customer]))
สรุป
ความหวังของฉันไม่เป็นจริง MySQL 8.0.2 ยังไม่รองรับการเพิ่มประสิทธิภาพแบบง่ายๆ นี้อย่างสมบูรณ์ แต่คนอื่นๆ ก็สนับสนุนนะฐานข้อมูล | พุชตารางที่ได้รับอย่างง่าย | พุชตารางที่ได้รับด้วย UNION |
---|---|---|
DB2 LUW 10.5 | ใช่ | ใช่ |
มายเอสแอล 8.0.2 | ใช่ | เลขที่ |
ออราเคิล 12.2.0.1 | ใช่ | ใช่ |
PostgreSQL 9.6 | ใช่ | ใช่ |
เซิร์ฟเวอร์ SQL 2014 | ใช่ | ใช่ |
บทสรุป
รายการที่นำเสนอนี้ยังห่างไกลจากความสมบูรณ์ มีการแปลง SQL แบบง่ายๆ อื่นๆ อีกมากมายที่ไม่ (หรือไม่ควร) ยากสำหรับฐานข้อมูลในการนำไปใช้ แม้กระทั่งก่อนที่ตัวเพิ่มประสิทธิภาพต้นทุนจะเข้ามาเกี่ยวข้องด้วยซ้ำ พวกเขากำจัด งานที่ไม่จำเป็นและพิเศษ [สำหรับฐานข้อมูล] ( ซึ่งตรงข้ามกับ งาน ที่ไม่จำเป็นและจำเป็นซึ่งฉันได้เขียนไปแล้ว ) สิ่งเหล่านี้เป็นเครื่องมือสำคัญในการ:- ข้อผิดพลาดโง่ๆ ของ [นักพัฒนา] ไม่มีผลกระทบต่อประสิทธิภาพการทำงาน ข้อผิดพลาดเป็นสิ่งที่หลีกเลี่ยงไม่ได้ และเมื่อโปรเจ็กต์เติบโตขึ้นและการสืบค้น SQL มีความซับซ้อนมากขึ้น ข้อผิดพลาดเหล่านี้ก็สามารถสะสมได้ หวังว่าจะไม่มีผลกระทบใดๆ
- มอบความสามารถในการนำบล็อกที่ซับซ้อนกลับมาใช้ใหม่ได้ เช่น มุมมองและฟังก์ชันตาราง ซึ่งสามารถฝังลงในคำสั่ง SQL ระดับพาเรนต์ แปลง หรือลบบางส่วนหรือเขียนใหม่ได้
GO TO FULL VERSION