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

Tối ưu hóa SQL thú vị không phụ thuộc vào mô hình chi phí. Phần 1 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 3

8. KIỂM TRA các hạn chế

Ồ, đây là thứ hay ho! Cơ sở dữ liệu Sakila của chúng tôi có ràng buộc CHECK trên cột FILM.RATING :

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

Nghiêm túc mà nói, hãy sử dụng các ràng buộc KIỂM TRA để đảm bảo tính toàn vẹn dữ liệu. Chi phí thêm chúng cực kỳ thấp - ít hơn nhiều so với các hạn chế khác, ví dụ: PRIMARY , UNIQUE hoặc FORIGN KEY , vì chúng không yêu cầu chỉ mục để hoạt động, vì vậy bạn nhận được chúng gần như "miễn phí". Nhưng có một sắc thái thú vị liên quan đến việc tối ưu hóa! Hãy xem xét các truy vấn sau:

Vị ngữ không thể

Chúng ta đã gặp phải các vị từ bất khả thi , thậm chí cả các ràng buộc NOT NULL (thực ra là một loại ràng buộc CHECK đặc biệt ), nhưng điều này thậm chí còn thú vị hơn:

SELECT *
FROM film
WHERE rating = 'N/A';

Không có phim nào như vậy và không thể có phim đó, vì ràng buộc CHECK ngăn cản việc chèn (hoặc cập nhật) phim đó. Một lần nữa, điều này sẽ chuyển thành lệnh không làm gì cả. Còn yêu cầu này thì sao?

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

Nhờ chỉ số trên, có lẽ chỉ cần quét nhanh chỉ mục và đếm tất cả các phim có rating = 'NC-17' , vì đó là xếp hạng duy nhất còn lại. Vì vậy, truy vấn nên được viết lại như thế này:

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

Điều này xảy ra bất kể chỉ mục nào, vì so sánh một cột với một giá trị sẽ nhanh hơn so với so sánh với 4. Vậy cơ sở dữ liệu nào có thể làm được điều này?

DB2

Vị từ không thể có (xếp hạng = 'N/A') Tuyệt vời!

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

Predicate Information
 2 - RESID (1 = 0)

Vị ngữ ngược (xếp hạng = 'NC-17') Không...

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

Mặc dù bước ID=3 sử dụng một chỉ mục và mặc dù số lượng chính xác, nhưng quá trình quét toàn bộ vẫn xảy ra do kế hoạch không có vị từ phạm vi mà chỉ có vị từ "SARG". Xem đánh giá của Marcus Wynand để biết chi tiết . Bạn cũng có thể chứng minh điều này bằng cách đảo vị ngữ theo cách thủ công và nhận:

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

Bây giờ chúng ta có vị từ phạm vi mong muốn.

MySQL

MySQL hỗ trợ cú pháp ràng buộc CHECK , nhưng vì lý do nào đó không thực thi chúng. Thử cái này xem sao:

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

và bạn sẽ nhận được:

A
-
0

Không có điểm nào cho MySQL (thực sự, tại sao không chỉ hỗ trợ các ràng buộc CHECK ?)

Lời tiên tri

Vị từ không thể (xếp hạng = 'N/A')

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

Một lần nữa, một bộ lọc rất lạ NULL IS NOT NULL , cắt bỏ FULL TABLE SCAN , bộ lọc này có thể dễ dàng bị loại bỏ hoàn toàn khỏi kế hoạch. Nhưng ít nhất nó hoạt động! Vị từ đảo ngược (xếp hạng = 'NC-17') Rất tiếc:

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

Vị ngữ không thể đảo ngược, việc đánh giá số lượng rất khập khiễng, ngoài ra chúng ta nhận được INDEX FAST FULL SCAN thay vì INDEX RANGE SCAN và vị từ bộ lọc thay vì vị từ truy cập . Nhưng đây là những gì chúng ta sẽ nhận được, ví dụ, bằng cách đảo ngược vị từ theo cách thủ công:

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

Rất tiếc!

PostgreSQL

Lưu ý rằng phiên bản PostgreSQL của cơ sở dữ liệu Sakila sử dụng loại ENUM thay vì các ràng buộc CHECK trên cột RATING . Thay vào đó, tôi đã sao chép bảng bằng cách sử dụng ràng buộc CHECK . Vị từ bất khả thi (xếp hạng = 'N/A') Không hoạt động:

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

Vị từ ngược (xếp hạng = 'NC-17') cũng không hoạt động:

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

Rất xin lỗi! Lưu ý: Như David Rowley đã vui lòng chỉ ra cho chúng tôi trong phần nhận xét , tính năng này có thể được bật bằng cách đặt tham số:

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

Vị ngữ ngược (xếp hạng = 'NC-17') Cũng vậy!

|--Compute Scalar
     |--Stream Aggregate
          |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

Bản tóm tắt

9. Những kết nối phản thân không cần thiết.

Khi các truy vấn của bạn trở nên phức tạp hơn, cuối cùng bạn có thể cần phải thực hiện phép nối phản ánh trên một bảng dựa trên khóa chính của nó. Tin tôi đi, đây là một cách làm rất phổ biến khi xây dựng các chế độ xem phức tạp và kết nối chúng với nhau, vì vậy việc đảm bảo cơ sở dữ liệu chú ý đến điều này là một phần quan trọng trong việc tối ưu hóa mã SQL phức tạp. Tôi sẽ không trình bày một ví dụ phức tạp, ví dụ một ví dụ đơn giản là đủ:

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Đây có thể được coi là một trường hợp đặc biệt của việc loại bỏ JOIN , vì chúng ta thực sự không cần tham gia vào A2 , chúng ta có thể làm mọi thứ mình cần chỉ với bảng A1 . Tiếp theo, việc loại bỏ INNER JOIN chỉ hoạt động bình thường nếu có FOREIGN KEY mà chúng tôi không có ở đây. Nhưng nhờ khóa chính của ACTOR_ID , chúng ta có thể chứng minh rằng trên thực tế A1 = A2 . Theo một nghĩa nào đó, đây lại là sự đóng cửa bắc cầu . Bạn có thể đi xa hơn nữa và sử dụng các cột từ cả hai bảng A1 và A2 :

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Trong trường hợp cổ điển của việc loại bỏ JOIN , sẽ không thể loại bỏ nó được nữa vì cả hai bảng đều được chiếu. Nhưng vì chúng tôi đã chứng minh rằng A1 = A2 , nên chúng có thể hoán đổi cho nhau, vì vậy chúng tôi có thể mong đợi truy vấn sẽ được chuyển đổi thành:

SELECT first_name, last_name
FROM actor;

DBMS nào có thể làm được điều này?

DB2

Chỉ chiếu bảng A1 Có:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

Chiếu bảng A1 và A2 ... cũng có:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

MySQL

Chỉ chiếu bảng A1 Không.

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

Chiếu bảng A1 và A2 ... cũng không

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

Hoàn toàn thất vọng...

Lời tiên tri

Chỉ chiếu bảng A1 Có

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

Chiếu bảng A1 và A2 Có nữa

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

Chỉ chiếu bảng A1 Không:

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

Chiếu bảng A1 và A2 Và lại không:

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

Máy chủ SQL

Chỉ chiếu bảng A1 Thật kỳ lạ là không! (Nhưng hãy nhớ rằng tôi đang sử dụng SQL Server 2014, các phiên bản mới hơn có thể đã sửa lỗi này. Tôi chắc chắn có thể sử dụng bản nâng cấp!)

|--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
     |--Index Scan(OBJECT:([a2]))
     |--Sort(ORDER BY:([a1].[actor_id] ASC))
          |--Table Scan(OBJECT:([a1]))

Việc chiếu các bảng A1 và A2 Không còn nữa, và kế hoạch thậm chí còn thay đổi theo chiều hướng tồi tệ hơn:

|--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

Bản tóm tắt

Thành thật mà nói, tôi đã mong đợi rằng việc tối ưu hóa này sẽ được thực hiện trên tất cả các cơ sở dữ liệu, nhưng thật đáng buồn là tôi đã rất nhầm lẫn. Cùng với việc loại bỏ JOIN , đây là một trong những tối ưu hóa quan trọng nhất, cho phép bạn xây dựng các truy vấn SQL khổng lồ từ các phần có thể sử dụng lại như dạng xem và hàm bảng. Thật không may, nó không được hỗ trợ ở 3 trong số 5 cơ sở dữ liệu phổ biến nhất.