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

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

4. Loại bỏ các vị từ “vô nghĩa”

Vô nghĩa không kém là những vị từ (gần như) luôn đúng. Như bạn có thể tưởng tượng, nếu bạn đang hỏi:

SELECT * FROM actor WHERE 1 = 1;

...khi đó cơ sở dữ liệu sẽ không thực sự thực thi nó mà sẽ bỏ qua nó. Tôi đã từng trả lời một câu hỏi về vấn đề này trên Stack Overflow và đó là lý do tại sao tôi quyết định viết bài này. Tôi sẽ để việc kiểm tra điều này như một bài tập cho người đọc, nhưng điều gì sẽ xảy ra nếu vị ngữ bớt "vô nghĩa" hơn một chút? Ví dụ:

SELECT * FROM film WHERE release_year = release_year;

Bạn có thực sự cần so sánh giá trị với chính nó cho mỗi hàng không? Không, không có giá trị nào mà vị từ này sẽ là FALSE , phải không? Nhưng chúng ta vẫn cần kiểm tra nó. Mặc dù vị từ không thể bằng FALSE , nhưng nó cũng có thể bằng NULL ở mọi nơi , một lần nữa do logic ba giá trị. Cột RELEASE_YEAR là null và nếu bất kỳ hàng nào có RELEASE_YEAR IS NULL thì NULL = NULL sẽ dẫn đến NULL và hàng đó phải được loại bỏ. Vì vậy, yêu cầu trở thành như sau:

SELECT * FROM film WHERE release_year IS NOT NULL;

Cơ sở dữ liệu nào thực hiện việc này?

DB2

Đúng!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL

Thật đáng tiếc, nhưng một lần nữa, MySQL không ánh xạ các biến vị ngữ vào các kế hoạch thực hiện, vì vậy việc tìm hiểu xem MySQL có triển khai tối ưu hóa cụ thể này hay không là một điều hơi khó khăn. Bạn có thể thực hiện đánh giá hiệu suất và xem liệu có bất kỳ so sánh quy mô lớn nào đang được thực hiện hay không. Hoặc bạn có thể thêm một chỉ mục:

CREATE INDEX i_release_year ON film (release_year);

Và nhận lại kế hoạch cho các yêu cầu sau:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

Nếu tối ưu hóa hoạt động thì kế hoạch của cả hai truy vấn sẽ gần giống nhau. Nhưng trong trường hợp này thì không phải vậy:

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

Như bạn có thể thấy, hai truy vấn của chúng ta khác nhau đáng kể về giá trị của cột POSSIBLE_KEYS và FILTERED . Vì vậy, tôi có thể đoán một cách hợp lý rằng MySQL không tối ưu hóa điều này.

Lời tiên tri

Đúng!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

Tiếc là không có!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

Các kế hoạch và chi phí khác nhau. Cụ thể, hãy nhìn vào việc đánh giá về số lượng, điều này hoàn toàn không tốt, trong khi vị ngữ này:

SELECT * FROM film WHERE release_year IS NOT NULL;

cho kết quả tốt hơn nhiều:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

Rất tiếc!

Máy chủ SQL

Thật kỳ lạ, SQL Server dường như cũng không làm được điều này:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

Tuy nhiên, dựa trên hình thức bên ngoài của kế hoạch, đánh giá về số lượng cũng như chi phí là chính xác. Nhưng theo kinh nghiệm của tôi với SQL Server, tôi sẽ nói rằng trong trường hợp này, không có tối ưu hóa nào xảy ra, vì SQL Server sẽ hiển thị vị từ được thực thi thực sự trong kế hoạch (để biết lý do tại sao, hãy xem các ví dụ ràng buộc CHECK bên dưới). Còn các vị từ "vô nghĩa" trên cột NOT NULL thì sao ? Việc chuyển đổi ở trên chỉ cần thiết vì RELEASE_YEAR có thể không được xác định. Điều gì xảy ra nếu bạn chạy cùng một truy vấn vô nghĩa trên cột FILM_ID chẳng hạn ?

SELECT * FROM film WHERE film_id = film_id

Bây giờ nó có tương ứng với không có vị ngữ nào cả không? Hoặc ít nhất đó là cách nó phải như vậy. Nhưng nó là?

DB2

Đúng!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

Không có vị từ nào được áp dụng cả và chúng tôi chọn tất cả các phim.

MySQL

Đúng! (Một lần nữa, đoán có giáo dục)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

Lưu ý rằng cột EXTRA hiện trống, như thể chúng ta không có mệnh đề WHERE nào cả!

Lời tiên tri

Đúng!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

Một lần nữa, không có vị ngữ nào được áp dụng.

PostgreSQL

Ôi, không nữa!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

Bộ lọc được áp dụng và điểm số lượng số vẫn là 5. Rất tiếc!

Máy chủ SQL

Và ở đây một lần nữa không!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

Bản tóm tắt

Nó có vẻ giống như một sự tối ưu hóa đơn giản, nhưng nó không được sử dụng trong tất cả các DBMS; đặc biệt, thật kỳ lạ, nó không được sử dụng trong SQL Server!

5. Phép chiếu trong truy vấn con EXISTS

Thật thú vị, tôi luôn được hỏi về chúng trong lớp học nâng cao của mình, nơi tôi bảo vệ quan điểm rằng CHỌN * thường không dẫn đến bất kỳ điều tốt đẹp nào. Câu hỏi đặt ra là: có thể sử dụng SELECT * trong truy vấn con EXISTS không ? Ví dụ: nếu chúng ta cần tìm diễn viên đóng phim...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Và câu trả lời là có. Có thể. Dấu hoa thị không ảnh hưởng đến yêu cầu. Làm thế nào bạn có thể chắc chắn về điều này? Hãy xem xét truy vấn sau:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

Tất cả các cơ sở dữ liệu này đều báo cáo lỗi chia cho số 0. Lưu ý một sự thật thú vị: trong MySQL, khi chia cho 0, chúng ta nhận được NULL thay vì lỗi, vì vậy chúng ta phải làm một việc khác không được phép. Bây giờ, điều gì sẽ xảy ra nếu chúng ta thực hiện các truy vấn sau thay vì thực hiện các truy vấn trên?

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

Bây giờ không có cơ sở dữ liệu nào trả về lỗi. Tất cả đều trả về TRUE hoặc 1 . Điều này có nghĩa là không có cơ sở dữ liệu nào của chúng tôi thực sự đánh giá phép chiếu (tức là mệnh đề SELECT ) của truy vấn con EXISTS . Ví dụ: SQL Server hiển thị kế hoạch sau:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

Như bạn có thể thấy, biểu thức CASE đã được chuyển đổi thành hằng số và truy vấn phụ đã bị loại bỏ. Các cơ sở dữ liệu khác lưu trữ truy vấn con trong kế hoạch và không đề cập bất kỳ điều gì về phép chiếu, vì vậy, hãy xem xét lại kế hoạch truy vấn ban đầu trong Oracle:

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Kế hoạch truy vấn ở trên trông như thế này:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

Chúng tôi quan sát thông tin về phép chiếu tại Id=3 . Trên thực tế, chúng tôi thậm chí không truy cập vào bảng FILM_ACTOR vì chúng tôi không cần. Vị từ EXISTS có thể được thực hiện bằng cách sử dụng chỉ mục khóa ngoại trên một cột ACTOR_ID - tất cả những gì cần thiết cho truy vấn này - ngay cả khi chúng tôi đã viết SELECT * .

Bản tóm tắt

May mắn thay, tất cả cơ sở dữ liệu của chúng tôi đều xóa phép chiếu khỏi truy vấn con EXISTS : Hãy theo dõi Phần 3 , nơi chúng ta sẽ thảo luận về các cách tối ưu hóa SQL thú vị khác.