Việc tối ưu hóa này không hoàn toàn phù hợp ở đây, vì không thể nói rằng nó hoàn toàn không dựa trên mô hình chi phí. Nhưng vì tôi không thể nghĩ ra một lý do nào khiến trình tối ưu hóa không nên đẩy các biến vị ngữ vào các bảng dẫn xuất nên tôi sẽ liệt kê nó ở đây, cùng với phần còn lại của các tối ưu hóa không tốn phí. Hãy xem xét yêu cầu:
SELECT *
FROM (
SELECT *FROM actor
) a
WHERE a.actor_id =1;
Bảng dẫn xuất trong truy vấn này không có ý nghĩa gì và cần được loại bỏ bằng cách giảm số lượng cấp độ lồng truy vấn. Nhưng bây giờ hãy bỏ qua điều đó. Bạn có thể mong đợi cơ sở dữ liệu thực hiện truy vấn sau thay vì truy vấn trên:
SELECT *
FROM (
SELECT *
FROM actor
WHERE actor_id =1) a;
Và sau đó, một lần nữa, có thể loại bỏ yêu cầu bên ngoài. Một ví dụ phức tạp hơn được lấy bằng cách sử dụng 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';
Kết quả của truy vấn này:
FIRST_NAME LAST_NAME TYPE
----------------------------
JENNIFER DAVIS actor
SUSAN DAVIS actor
SUSAN DAVIS actor
JENNIFER DAVIS customer
Bây giờ, sẽ thật tuyệt nếu trình tối ưu hóa cơ sở dữ liệu chạy một truy vấn như thế này:
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;
Nghĩa là, để nó đẩy vị từ vào bảng dẫn xuất, và từ đó thành hai truy vấn con UNION ALL , vì xét cho cùng, chúng ta có chỉ mục trên cả cột ACTOR.LAST_NAME và cột KHÁCH HÀNG.LAST_NAME . Một lần nữa, việc chuyển đổi này có thể dựa trên ước tính chi phí trong hầu hết các cơ sở dữ liệu, nhưng tôi vẫn nghĩ đó là điều hiển nhiên vì với bất kỳ thuật toán nào, việc giảm số lượng bộ dữ liệu được xử lý càng sớm càng tốt hầu như luôn luôn tốt hơn. Nếu bạn biết trường hợp mà việc chuyển đổi như vậy hóa ra lại là một ý tưởng tồi, tôi sẽ rất vui khi nghe ý kiến của bạn! Tôi sẽ rất quan tâm. Vậy cơ sở dữ liệu nào của chúng tôi có thể làm được điều này? (Và làm ơn, nó quá đơn giản và quá quan trọng, hãy để câu trả lời là: tất cả mọi thứ)
DB2
Bảng dẫn xuất đơn giản Có
ExplainPlan--------------------------------------------------
ID |Operation|Rows|Cost1| RETURN ||62| FETCH ACTOR |1 of 1(100.00%)|63| IXSCAN PK_ACTOR |1 of 200(.50%)|0PredicateInformation3- START (Q1.ACTOR_ID =1)
STOP (Q1.ACTOR_ID =1)
Bảng dẫn xuất với UNION Cũng có:
ExplainPlan-----------------------------------------------------------------
ID |Operation|Rows|Cost1| RETURN ||202| UNION |2 of 1|203| FETCH CUSTOMER |1 of 1(100.00%)|134| IXSCAN IDX_CUSTOMER_LAST_NAME |1 of 599(.17%)|65| FETCH ACTOR |1 of 1(100.00%)|66| IXSCAN IDX_ACTOR_LAST_NAME |1 of 200(.50%)|0PredicateInformation4- START (Q1.LAST_NAME ='DAVIS')
STOP (Q1.LAST_NAME ='DAVIS')6- START (Q3.LAST_NAME ='DAVIS')
STOP (Q3.LAST_NAME ='DAVIS')
Ngoài ra, trong cả hai trường hợp, bảng dẫn xuất (dạng xem) đều bị loại khỏi kế hoạch vì nó không thực sự cần thiết.
MySQL
Bảng dẫn xuất đơn giản Có
ID TABLE TYPE KEY REF EXTRA
---------------------------------------1 actor const PRIMARY const
Quyền truy cập khóa chính thông thường theo giá trị không đổi được sử dụng. Bảng dẫn xuất có UNION Rất tiếc, không.
ID SELECT_TYPE TABLE TYPE KEY REF ROWS EXTRA
------------------------------------------------------------------1 PRIMARY ref const102 DERIVED actor ALL 2003 UNION customer ALL 599
Kết quả chuyển đổi thủ công trong phương án:
ID SELECT_TYPE TABLE TYPE KEY REF ROWS EXTRA
--------------------------------------------------------------------------1 PRIMARY ALL 52 DERIVED actor ref idx_actor_last_name const33 UNION customer ref idx_last_name const1
Đây là một vấn đề nghiêm trọng khi sử dụng các truy vấn lồng nhau phức tạp trong MySQL!
Lời tiên tri
Bảng dẫn xuất đơn giản Có, nó hoạt động.
---------------------------------------------------------------------------|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|---------------------------------------------------------------------------PredicateInformation(identified by operation id):---------------------------------------------------2-access("ACTOR"."ACTOR_ID"=1)
Và số lượng cấp độ làm tổ đã giảm xuống. Bảng dẫn xuất với UNION cũng hoạt động:
---------------------------------------------------------------------------------|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|---------------------------------------------------------------------------------PredicateInformation(identified by operation id):---------------------------------------------------4-access("LAST_NAME"='DAVIS')6-access("LAST_NAME"='DAVIS')
Tuy nhiên, không làm giảm số lượng cấp độ lồng nhau. Id=1 "View" cho thấy bảng dẫn xuất vẫn còn đó. Trong trường hợp này, đó không phải là vấn đề lớn, có lẽ chỉ là một khoản chi phí bổ sung nhỏ.
PostgreSQL
Bảng dẫn xuất đơn giản Có, nó hoạt động:
QUERY PLAN
----------------------------------------------------SeqScan on actor (cost=0.00..4.50 rows=1 width=25)Filter:(actor_id =1)
Tuy nhiên, lưu ý rằng PostgreSQL đôi khi thậm chí không sử dụng khóa chính để tra cứu một hàng mà thay vào đó quét toàn bộ bảng. Trong trường hợp này, 200 hàng × 25 byte mỗi hàng ("chiều rộng") vừa với một khối, vậy việc đọc chỉ mục có ích gì ngoài việc tạo ra các hoạt động I/O không cần thiết để truy cập vào một bảng nhỏ như vậy? Bảng dẫn xuất bằng UNION Có, nó cũng hoạt động:
QUERY PLAN
-----------------------------------------------------------------------------------Append(cost=0.00..12.83 rows=4 width=45)->SeqScan on actor (cost=0.00..4.50 rows=3 width=45)Filter:((last_name)::text='DAVIS'::text)->IndexScan using idx_last_name on customer (cost=0.28..8.29 rows=1 width=45)IndexCond:((last_name)::text='DAVIS'::text)
Một lần nữa, chỉ mục trên cột ACTOR.LAST_NAME không được sử dụng nhưng chỉ mục trên cột KHÁCH HÀNG.LAST_NAME được sử dụng vì bảng KHÁCH HÀNG lớn hơn nhiều.
Hy vọng của tôi đã không thành hiện thực. MySQL 8.0.2 chưa hỗ trợ đầy đủ tính năng tối ưu hóa đơn giản này. Tuy nhiên, mọi người khác đều ủng hộ.
Cơ sở dữ liệu
Đẩy một bảng dẫn xuất đơn giản
Đẩy bảng dẫn xuất bằng UNION
DB2 LUW 10.5
Đúng
Đúng
MySQL 8.0.2
Đúng
KHÔNG
Oracle 12.2.0.1
Đúng
Đúng
PostgreSQL 9.6
Đúng
Đúng
Máy chủ SQL 2014
Đúng
Đúng
Phần kết luận
Danh sách được trình bày ở đây vẫn chưa đầy đủ. Có nhiều phép biến đổi SQL đơn giản khác mà cơ sở dữ liệu không (hoặc không nên) khó thực hiện, ngay cả trước khi sử dụng trình tối ưu hóa chi phí. Chúng loại bỏ những công việc bổ sung , không cần thiết [cho cơ sở dữ liệu] ( trái ngược với những công việc bắt buộc , không cần thiết mà tôi đã viết ). Đây là những công cụ quan trọng để:
Những lỗi [nhà phát triển] ngu ngốc không ảnh hưởng đến hiệu suất. Lỗi là không thể tránh khỏi và khi dự án phát triển và các truy vấn SQL trở nên phức tạp hơn, những lỗi này có thể tích lũy, hy vọng là không có bất kỳ ảnh hưởng nào.
Cung cấp khả năng sử dụng lại các khối phức tạp, chẳng hạn như dạng xem và hàm bảng, có thể được nhúng trong các truy vấn SQL gốc, được chuyển đổi hoặc bị xóa hoặc viết lại một phần.
Những khả năng này rất quan trọng đối với điểm 2. Nếu không có chúng, sẽ rất khó để tạo các truy vấn SQL 4000 hàng với hiệu suất bình thường dựa trên thư viện các thành phần SQL có thể tái sử dụng. Điều gây thất vọng cho người dùng PostgreSQL và MySQL là hai cơ sở dữ liệu nguồn mở phổ biến này vẫn còn một chặng đường dài để so sánh với các đối thủ thương mại DB2, Oracle và SQL Server, trong đó DB2 đã hoạt động tốt nhất, với Oracle và SQL Server ở mức gần như ngang bằng. cổ, lùi lại một chút.
GO TO FULL VERSION