Tối ưu hóa SQL thú vị không phụ thuộc vào mô hình chi phí. Phần 1

Năm tối ưu hóa đơn giản chỉ có thể được triển khai dựa trên siêu dữ liệu (tức là các ràng buộc) và chính truy vấn. Chúng tôi cung cấp cho bạn bản chuyển thể từ bài viết của Lukas Eder, được thiết kế cho những người có hiểu biết chung về cơ sở dữ liệu và SQL, cũng như một số kinh nghiệm thực tế với DBMS . Tối ưu hóa chi phí thực sự là một cách tiêu chuẩn để tối ưu hóa các truy vấn SQL trong cơ sở dữ liệu hiện đại. Đây là lý do tại sao rất khó để viết thủ công một thuật toán phức tạp bằng 3GL (ngôn ngữ lập trình thế hệ thứ ba) có hiệu suất vượt quá kế hoạch thực hiện được tính toán động do trình tối ưu hóa hiện đại tạo ra. Hôm nay chúng ta sẽ không thảo luận về tối ưu hóa chi phí, tức là tối ưu hóa dựa trên mô hình chi phí của cơ sở dữ liệu. Chúng ta sẽ xem xét các tối ưu hóa đơn giản hơn nhiều. Những thứ có thể được triển khai chỉ dựa trên siêu dữ liệu (tức là các hạn chế) và chính yêu cầu đó. Thông thường, việc triển khai cơ sở dữ liệu của chúng không phải là nhị thức Newton, vì trong trường hợp này, bất kỳ tối ưu hóa nào cũng sẽ dẫn đến kế hoạch thực hiện tốt hơn, bất kể sự hiện diện của các chỉ mục, khối lượng dữ liệu và độ lệch của phân phối dữ liệu. "Không phải nhị thức Newton" không có nghĩa là việc thực hiện tối ưu hóa dễ dàng như thế nào mà là liệu nó có nên được thực hiện hay không. Những tối ưu hóa này loại bỏ công việc bổ sung, không cần thiết [đối với cơ sở dữ liệu] ( trái ngược với công việc bắt buộc, không cần thiết mà tôi đã viết ).

Những tối ưu hóa này được sử dụng để làm gì?

Hầu hết chúng được sử dụng cho:

• sửa lỗi trong truy vấn;
• cho phép các khung nhìn được sử dụng lại mà không cần cơ sở dữ liệu thực sự thực thi logic khung nhìn.

Trong trường hợp đầu tiên, người ta có thể nói: “Vậy thì sao, hãy tiếp tục và sửa truy vấn SQL ngu ngốc này”. Nhưng hãy để người chưa bao giờ mắc lỗi ném đá tôi trước. Trường hợp thứ hai đặc biệt thú vị: nó cho chúng ta khả năng tạo các thư viện phức tạp về các khung nhìn và hàm bảng có thể được sử dụng lại trên nhiều lớp.

Cơ sở dữ liệu được sử dụng

Trong bài viết này, chúng tôi sẽ so sánh 10 cách tối ưu hóa SQL trong 5 DBMS được sử dụng rộng rãi nhất ( theo xếp hạng cơ sở dữ liệu ):

• Oracle 12.2;
• MySQL 8.0.2;
• Máy chủ SQL 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5.

Một đánh giá khác gần như lặp lại nó. Như thường lệ, trong bài viết này tôi sẽ truy vấn cơ sở dữ liệu Sakila . Dưới đây là danh sách mười loại tối ưu hóa này:

1. Sự đóng kín;
2. các vị từ không thể thực hiện được và các lệnh gọi bảng không cần thiết;
3. loại bỏ THAM GIA;
4. loại bỏ các vị từ “vô nghĩa”;
5. các phép chiếu trong truy vấn con EXISTS;
6. sáp nhập các vị ngữ;
7. có thể coi là tập trống;
8. hạn chế KIỂM TRA;
9. những kết nối phản thân không cần thiết;
10. Vị từ đẩy xuống

Hôm nay chúng ta sẽ thảo luận về pp. 1-3, ở phần thứ hai - 4 và 5, và ở phần 3 - 6-10.

1. Đóng cửa chuyển tiếp

Hãy bắt đầu với điều gì đó đơn giản hơn: đóng cửa chuyển tiếp . Đây là một khái niệm tầm thường áp dụng cho nhiều phép toán, chẳng hạn như toán tử đẳng thức. Nó có thể được phát biểu trong trường hợp này như sau: nếu A = B và B = C thì A = C.

Không khó phải không? Nhưng điều này có một số ý nghĩa thú vị đối với các trình tối ưu hóa SQL. Hãy xem một ví dụ. Hãy trích xuất tất cả các phim có 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;

Kết quả là như sau:

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

Bây giờ chúng ta hãy xem kế hoạch thực hiện truy vấn này trong trường hợp 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)

Phần về vị ngữ đặc biệt thú vị ở đây. Vị từ ACTOR_ID = 1, do đóng bắc cầu, áp dụng cho cả bảng ACTOR và bảng FILM_ACTOR. Nếu như:

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

Đối với các truy vấn phức tạp hơn, điều này tạo ra một số kết quả rất tốt. Đặc biệt, độ chính xác của ước lượng số lượng tăng lên đáng kể, vì có thể chọn các ước tính dựa trên một giá trị không đổi cụ thể của vị từ chứ không phải số lượng phim trung bình của các diễn viên, chẳng hạn như trong truy vấn sau (trả về cùng một kết quả):

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'

Kế hoạch của anh ấy:

----------------------------------------------------------------------------
| 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")

Như bạn có thể thấy, số lượng hàng trong bảng FILM_ACTOR được đánh giá quá cao, trong khi NESTED LOOP bị đánh giá thấp. Dưới đây là một vài giá trị thú vị:

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

Kết quả:

19
27.315

Đây là nơi các ước tính đến từ. Nếu cơ sở dữ liệu biết rằng chúng ta đang nói về ACTOR_ID = 1 thì nó có thể thu thập số liệu thống kê về số lượng phim của diễn viên cụ thể này . Nếu không (vì cơ chế thu thập số liệu thống kê tiêu chuẩn không tương quan FIRST_NAME/LAST_NAME với ACTOR_ID), thì chúng tôi sẽ nhận được số lượng phim trung bình cho tất cả các diễn viên . Một lỗi đơn giản, không quan trọng trong trường hợp cụ thể này, nhưng trong một truy vấn phức tạp, nó có thể lan truyền xa hơn, tích lũy và dẫn sâu hơn vào truy vấn (cấp cao hơn trong kế hoạch) dẫn đến lựa chọn THAM GIA không chính xác. Vì vậy, bất cứ khi nào bạn có thể, hãy thiết kế các phép nối và các vị từ đơn giản để tận dụng lợi thế của việc đóng chuyển tiếp. Cơ sở dữ liệu nào khác hỗ trợ tính năng này?

DB2

Đúng!

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)

Nhân tiện, nếu bạn thích những kế hoạch thực thi thú vị như thế này, hãy xem kịch bản của Markus Winand .

MySQL

Thật không may, các kế hoạch thực thi MySQL không phù hợp lắm với kiểu phân tích này. Bản thân vị ngữ bị thiếu trong thông tin đầu ra:

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

Nhưng thực tế là const được chỉ định hai lần trong cột REF cho thấy cả hai bảng đều đang tìm kiếm một giá trị không đổi. Đồng thời, kế hoạch truy vấn với FIRST_NAME/LAST_NAME trông giống như sau:

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

Và như bạn có thể thấy, REF hiện tham chiếu cột từ vị từ THAM GIA. Điểm số cardinality gần giống như trong Oracle. Vì vậy, MySQL cũng hỗ trợ việc đóng cửa chuyển tiếp.

PostgreSQL

Đúng!

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)

Máy chủ SQL

Đúng!

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

Bản tóm tắt

Tất cả cơ sở dữ liệu của chúng tôi đều hỗ trợ việc đóng cửa chuyển tiếp. Tuy nhiên, hãy đợi #6 ở phần tiếp theo của bài viết nhé. Có những trường hợp đóng chuyển tiếp phức tạp mà không phải cơ sở dữ liệu nào cũng có thể xử lý được.

2. Các vị từ bất khả thi và các lệnh gọi bảng không cần thiết

Đây là một sự tối ưu hóa hoàn toàn ngu ngốc, nhưng tại sao không? Nếu người dùng viết những vị từ không thể thực hiện được thì tại sao lại phải thực hiện chúng? Dưới đây là một số ví dụ:

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

Truy vấn đầu tiên rõ ràng sẽ không bao giờ trả về bất kỳ kết quả nào, nhưng câu lệnh tương tự cũng đúng cho truy vấn thứ hai. Xét cho cùng, mặc dù NULL IS NULL luôn là TRUE, nhưng kết quả của phép tính NULL = NULL là NULL, theo logic ba giá trị , tương đương với FALSE. Điều này khá dễ hiểu, vì vậy hãy đi thẳng vào việc tìm hiểu cơ sở dữ liệu nào thực hiện tối ưu hóa này.

DB2

Đúng!

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

Như bạn có thể thấy, quyền truy cập vào bảng ACTOR hoàn toàn bị loại trừ khỏi gói. Nó chỉ chứa hoạt động GENROW, tạo ra các hàng bằng 0. Hoàn hảo.

MySQL

Đúng!

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

Lần này, MySQL thật tốt bụng khi thông báo cho chúng tôi về mệnh đề WHERE không thể thực hiện được. Cảm ơn! Điều này làm cho việc phân tích dễ dàng hơn nhiều, đặc biệt là so với các cơ sở dữ liệu khác.

Lời tiên tri

Đúng!

---------------------------------------------------------------
| 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)

Chúng tôi thấy rằng kế hoạch vẫn đề cập đến quyền truy cập vào bảng ACTOR và số hàng dự kiến ​​​​vẫn là 200, nhưng cũng có thao tác lọc (LỌC) với Id=1, trong đó sẽ không bao giờ có TRUE. Do Oracle không thích kiểu dữ liệu SQL Boolean tiêu chuẩn , Oracle hiển thị NULL IS NOT NULL trong kế hoạch, thay vì chỉ FALSE. Ồ, nhưng nghiêm túc mà nói, hãy xem vị ngữ đó. Tôi đã có dịp gỡ lỗi các kế hoạch thực thi với các cây con 1000 dòng và giá trị chi phí cực cao, chỉ để phát hiện ra sau khi thực tế là toàn bộ cây con đã bị bộ lọc NULL IS NOT NULL "cắt bỏ". Một chút nản lòng, tôi nói với bạn.

PostgreSQL

Đúng!

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

Đã tốt hơn rồi. Không có lệnh gọi bảng ACTOR gây khó chịu và một biến vị ngữ FALSE nhỏ gọn.

Máy chủ SQL?

Đúng!

|--Constant Scan

SQL Server gọi đây là " quét liên tục ", là quá trình quét không có gì xảy ra - tương tự như DB2. Tất cả cơ sở dữ liệu của chúng tôi có thể loại trừ các vị từ không thể:

3. Loại bỏ THAM GIA

Trong phần trước, chúng ta đã quan sát thấy các truy cập bảng không cần thiết trong các truy vấn một bảng. Nhưng điều gì sẽ xảy ra nếu THAM GIA không yêu cầu một trong nhiều quyền truy cập vào bảng? Tôi đã viết về việc loại bỏ THAM GIA trong một bài đăng trước trên blog của mình . Công cụ SQL có thể xác định, dựa trên loại truy vấn và sự hiện diện của khóa chính và khóa ngoài, liệu một THAM GIA cụ thể có thực sự cần thiết trong một truy vấn nhất định hay không hoặc việc loại bỏ nó sẽ không ảnh hưởng đến ngữ nghĩa của truy vấn. Trong cả ba ví dụ tiếp theo, JOIN đều không cần thiết. Phép nối bên trong ...-với-một có thể được loại bỏ bằng cách sử dụng khóa ngoại NOT NULL. Thay vào đó:

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

Cơ sở dữ liệu có thể làm những việc sau:

SELECT first_name, last_name
FROM customer c

INNER JOIN thuộc loại "...-to-one" có thể được thay thế nếu có khóa ngoại không thể rỗng. Truy vấn trên hoạt động nếu khóa ngoại bị ràng buộc NOT NULL. Nếu không, ví dụ như trong yêu cầu này:

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

thì JOIN vẫn có thể bị loại bỏ, nhưng bạn sẽ phải thêm vị từ NOT NULL, như thế này:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

Có thể xóa OUTER JOIN thuộc loại "...-to-one" nếu có khóa duy nhất. Thay vì điều này:

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

Cơ sở dữ liệu, một lần nữa, có thể thực hiện những việc sau:

SELECT first_name, last_name
FROM customer c

... ngay cả khi không có khóa ngoại cho KHÁCH HÀNG.ADDRESS_ID. Có thể xóa kết nối bên ngoài duy nhất (DISTINCT OUTER JOIN) của loại "...-to-many". Thay vì điều này:

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

Cơ sở dữ liệu có thể làm những việc sau:

SELECT DISTINCT first_name, last_name
FROM actor a

Tất cả các ví dụ này đã được nghiên cứu chi tiết trong bài viết trước, vì vậy tôi sẽ không lặp lại mà chỉ tóm tắt mọi thứ mà các cơ sở dữ liệu khác nhau có thể loại bỏ: Thật không may, không phải cơ sở dữ liệu nào cũng có thể giải quyết được mọi loại kết nối. DB2 và SQL Server là những người dẫn đầu không thể tranh cãi ở đây! Còn tiếp