不依赖于成本模型的酷 SQL 优化。第 1 部分 不依赖于成本模型的 Cool SQL 优化。第 2 部分 不依赖于成本模型的酷 SQL 优化。第三部分
8.检查限制
哦,这是很酷的东西!我们的Sakila数据库对FILM.RATING列有CHECK约束:CREATE TABLE film (
..
RATING varchar(10) DEFAULT 'G',
..
CONSTRAINT check_special_rating
CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
..
);
认真地说,使用CHECK约束来确保数据完整性。添加它们的成本非常低 - 远低于其他限制,例如PRIMARY、UNIQUE或FOREIGN KEY,因为它们不需要索引即可工作,因此您几乎“免费”获得它们。但有一个与优化相关的有趣的细微差别!考虑以下查询:
不可能谓词
我们已经遇到过不可能的谓词,甚至NOT NULL约束(这实际上是一种特殊的CHECK约束),但这个更酷:SELECT *
FROM film
WHERE rating = 'N/A';
不存在这样的电影,也不可能存在,因为CHECK约束阻止其插入(或更新)。同样,这应该翻译为不执行任何操作的命令。这个请求怎么办?
CREATE INDEX idx_film_rating ON film (rating);
SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');
由于上面的索引,只需快速扫描索引并计算所有评级= 'NC-17' 的电影就足够了,因为这是唯一剩下的评级。所以查询应该像这样重写:
SELECT count(*)
FROM film
WHERE rating = 'NC-17';
无论索引如何,情况都应该如此,因为将一列与一个值进行比较比与 4 个值进行比较要快。那么,哪些数据库可以做到这一点呢?
数据库2
不可能的谓词(评级 = 'N/A') 酷!Explain Plan
-----------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 0
2 | TBSCAN GENROW | 0 of 0 | 0
Predicate Information
2 - RESID (1 = 0)
反向谓词(评级=“NC-17”) 不...
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'))
尽管 ID=3 步骤使用索引,并且基数正确,但仍会发生完整扫描,因为该计划没有范围谓词,只有“SARG”谓词。有关详细信息,请参阅Marcus Wynand 的评论。您还可以通过手动反转谓词并获取来演示这一点:
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')
现在我们有了所需的范围谓词。
MySQL
MySQL 支持CHECK约束语法,但由于某种原因不强制执行它们。尝试这个:CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;
你会得到:
A
-
0
MySQL 零分(真的,为什么不只支持CHECK约束?)
甲骨文
不可能的谓词(评级=“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')
同样,一个非常奇怪的过滤器NULL IS NOT NULL,切断了FULL TABLE SCAN,它可以很容易地从计划中完全删除。但至少它有效! 反向谓词(评级 = 'NC-17') 哎呀:
----------------------------------------------------------------------------
| 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'))
谓词不能反转,基数评估非常蹩脚,此外我们得到INDEX FAST FULL SCAN而不是INDEX RANGE SCAN,并且得到过滤谓词而不是访问谓词。但这是我们应该得到的,例如,通过手动反转谓词:
------------------------------------------------------------------------
| 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')
真糟糕!
PostgreSQL
请注意,Sakila数据库的PostgreSQL版本在RATING列上使用ENUM类型而不是CHECK约束。我使用CHECK约束来复制该表。 不可能的谓词(评级 = 'N/A') 不起作用:QUERY PLAN
------------------------------------------------------
Seq Scan on film2 (cost=0.00..67.50 rows=1 width=385)
Filter: ((rating)::text = 'N/A'::text)
反向谓词(评级 = 'NC-17') 也不起作用:
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[]))
很抱歉! 注意:正如David Rowley 在评论中向我们指出的那样,可以通过设置参数来启用此功能:
SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan
反向谓词(评级 = 'NC-17') 也是!
|--Compute Scalar
|--Stream Aggregate
|--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))
概括
数据库 | 不可能谓词 | 反向谓词 |
---|---|---|
DB2 逻辑单元 10.5 | 是的 | 不 |
MySQL 8.0.2 | 不支持 | 不支持 |
甲骨文12.2.0.1 | 是的 | 不 |
PostgreSQL 9.6 | 不 | 不 |
9. 不必要的反射性联系。
随着您的查询变得更加复杂,您很可能最终需要根据表的主键对表执行反射联接。相信我,在构建复杂视图并将它们相互连接时,这是一种非常常见的做法,因此确保数据库关注这一点是优化复杂 SQL 代码的关键部分。我不会演示一个复杂的例子,一个简单的例子就足够了,例如:SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;
这可以看作是JOIN 消除的特殊情况,因为我们实际上不需要连接到A2,我们可以只用表A1来完成我们需要的一切。接下来,INNER JOIN 消除仅在存在 FOREIGN KEY 时才能正常工作,而我们这里没有。但是由于ACTOR_ID的主键,我们可以证明实际上A1 = A2。从某种意义上说,这又是传递闭包。您可以更进一步,使用表A1和A2中的列:
SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;
在JOIN消除 的经典情况下,将不再可能消除它,因为两个表都已投影。但由于我们已经证明A1 = A2,那么它们是可以互换的,因此我们可以期望查询转换为:
SELECT first_name, last_name
FROM actor;
什么 DBMS 可以做到这一点?
数据库2
仅表 A1 的投影 是:Explain Plan
------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 20
2 | TBSCAN ACTOR | 200 of 200 (100.00%) | 20
表 A1 和 A2 的投影 ...也可以:
Explain Plan
------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 20
2 | TBSCAN ACTOR | 200 of 200 (100.00%) | 20
MySQL
仅 表 A1 的投影ID TABLE REF EXTRA
-----------------------------------
1 a1
1 a2 a1.actor_id Using index
表 A1 和 A2 的投影 ...也没有
ID TABLE REF EXTRA
-----------------------------------
1 a1
1 a2 a1.actor_id
彻底失望了……
甲骨文
仅表 A1 的投影 是--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS FULL| ACTOR | 200 |
--------------------------------------------
表 A1 和 A2 的投影 再次是
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS FULL| ACTOR | 200 |
--------------------------------------------
PostgreSQL
仅表 A1 的投影 否: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)
表 A1 和 A2 的投影 又否:
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)
SQL服务器
仅表 A1 的投影 奇怪的是,没有!(但请记住,我使用的是 SQL Server 2014,较新的版本可能已修复此问题。我绝对可以使用升级!)|--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]))
表 A1 和 A2 的投影 不再是这样,计划甚至变得更糟:
|--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]))
概括
坦率地说,我预计这种优化会在所有数据库上进行,但遗憾的是我大错特错了。除了消除 JOIN之外,这是最重要的优化之一,它允许您从视图和表函数等可重用部分构建大量 SQL 查询。不幸的是,5 个最常见的数据库中有 3 个不支持它。数据库 | 删除反射连接,单表投影 | 自反连接消除,全投影 |
---|---|---|
DB2 逻辑单元 10.5 | 是的 | 是的 |
MySQL 8.0.2 | 不 | 不 |
甲骨文12.2.0.1 | 是的 | 是的 |
PostgreSQL 9.6 | 不 | 不 |
SQL Server 2014 | 不 | 不 |
GO TO FULL VERSION