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

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 แบบง่ายๆ อื่นๆ อีกมากมายที่ไม่ (หรือไม่ควร) ยากสำหรับฐานข้อมูลในการนำไปใช้ แม้กระทั่งก่อนที่ตัวเพิ่มประสิทธิภาพต้นทุนจะเข้ามาเกี่ยวข้องด้วยซ้ำ พวกเขากำจัด งานที่ไม่จำเป็นและพิเศษ [สำหรับฐานข้อมูล] ( ซึ่งตรงข้ามกับ งาน ที่ไม่จำเป็นและจำเป็นซึ่งฉันได้เขียนไปแล้ว ) สิ่งเหล่านี้เป็นเครื่องมือสำคัญในการ:
  1. ข้อผิดพลาดโง่ๆ ของ [นักพัฒนา] ไม่มีผลกระทบต่อประสิทธิภาพการทำงาน ข้อผิดพลาดเป็นสิ่งที่หลีกเลี่ยงไม่ได้ และเมื่อโปรเจ็กต์เติบโตขึ้นและการสืบค้น SQL มีความซับซ้อนมากขึ้น ข้อผิดพลาดเหล่านี้ก็สามารถสะสมได้ หวังว่าจะไม่มีผลกระทบใดๆ

  2. มอบความสามารถในการนำบล็อกที่ซับซ้อนกลับมาใช้ใหม่ได้ เช่น มุมมองและฟังก์ชันตาราง ซึ่งสามารถฝังลงในคำสั่ง SQL ระดับพาเรนต์ แปลง หรือลบบางส่วนหรือเขียนใหม่ได้
ความสามารถเหล่านี้มีความสำคัญอย่างยิ่งต่อจุดที่ 2 หากไม่มีสิ่งเหล่านั้น มันจะยากมากที่จะสร้างการสืบค้น SQL 4,000 แถวที่มีประสิทธิภาพปกติโดยอิงจากไลบรารีของส่วนประกอบ SQL ที่นำมาใช้ซ้ำได้ สร้างความผิดหวังให้กับผู้ใช้ PostgreSQL และ MySQL อย่างมาก เนื่องจากฐานข้อมูลโอเพ่นซอร์สยอดนิยมทั้งสองนี้ยังคงมีแนวทางที่ยาวไกลเมื่อเทียบกับคู่แข่งทางการค้าอย่าง DB2, Oracle และ SQL Server ซึ่ง DB2 ทำงานได้ดีที่สุด โดยมี Oracle และ SQL Server คอยติดตามและ คอ. ข้างหลังนิดหน่อย.