《数据库索引设计优化》读书笔记（八）

第14章 优化器不是完美的

练习

14.1 重写 SQL 14.8 中的游标，使得新游标的访问路径满足：

• MC=1（MC 是指 Matching Column，MC=1 即一个匹配列）
• 仅需访问索引
• 无排序

-- SQL 14.8
DECLARE CURSOR141 CURSOR FOR
SELECT LNAME, FNAME, CNO
  FROM CUST
 WHERE (LNAME = :LNAMEPREV AND CNO > :CNOPREV)
    OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX)
 ORDER BY LNAME, CNO
WE WANT 20 ROWS PLEASE

要求：不能去除 ORDER BY。

提示：WHERE 语句可以包含操作符 NOT，不过 NOT 将使该谓词对于优化器而言太过困难（无匹配列）。

分析：

        查询谓词只有两个字段 LNAME、CNO，并且这两个字段都有范围条件。要满足“MC=1”，候选索引为（LNAME）、（CNO）、（LNAME,CNO）、（CNO,LNAME）。要满足“仅需访问索引”，则前两个单列候选索引被排除。后两个复合索引还要加入 FNAME 字段，使之成为宽索引。候选索引变为（LNAME,CNO,FNAME）或（CNO,LNAME,FNAME）。最后要满足“无排序”，则索引只能是（LNAME,CNO,FNAME）。索引确定后需要改写 SQL 14.8，使得查询能够走（LNAME,CNO,FNAME）索引。

        原查询语句里的 OR 是非 BT 谓词，所以要把外层的 OR 去掉。根据逻辑运算的等价性做如下转换（前提是 LNAMEPREV <= :LNAMEMAX）：

(LNAME = :LNAMEPREV AND CNO > :CNOPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX)
=>
((LNAME = :LNAMEPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX)) AND 
((CNO > :CNOPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX))
=>
(LNAME = :LNAMEPREV OR LNAME > :LNAMEPREV) AND (LNAME = :LNAMEPREV OR LNAME <= :LNAMEMAX) AND 
((CNO > :CNOPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX))
=>
(LNAME >= :LNAMEPREV AND LNAME = :LNAMEPREV OR LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX) AND 
((CNO > :CNOPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX))
=>
(LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX) AND 
((CNO > :CNOPREV) OR (LNAME > :LNAMEPREV AND LNAME <= :LNAMEMAX))
=>
(LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX) AND 
((CNO > :CNOPREV) OR (LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX AND LNAME <> :LNAMEPREV))

设：

a 代表条件 LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX
b 代表条件 CNO > :CNOPREV
c 代表条件 LNAME <> :LNAMEPREV

则原条件谓词转化为：

a and (b or (a and c)) => a and (a or b) and (b or c) => a and (b or c)

再把 a、b、c 还原回去，则条件原来的条件谓词可以转化为：

LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX AND (CNO > :CNOPREV OR LNAME <> :LNAMEPREV)

这个等价的谓词条件去掉了外层的 OR，可以以 LNAME 作为匹配列，CNO 和 LNAME 作为过滤列访问索引（LNAME,CNO,FNAME），满足题目要求。但是，CNO > :CNOPREV OR LNAME <> :LNAMEPREV 可能导致优化器难以选择最优索引（OR 条件常导致全表扫描或索引合并）。

继续转换：

LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX AND (CNO > :CNOPREV OR LNAME <> :LNAMEPREV)
=>
LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX AND 
(
  (LNAME = :LNAMEPREV AND CNO > :CNOPREV) 
  OR 
  (LNAME > :LNAMEPREV)
)

转换后的形式更清晰，便于优化器识别索引使用：

• LNAME = :LNAMEPREV AND CNO > :CNOPREV 可以高效利用（LNAME, CNO）复合索引的范围扫描。
• LNAME > :LNAMEPREV 直接利用复合索引的左前缀。
   

最终的 SQL 改写为：

DECLARE CURSOR141 CURSOR FOR
SELECT LNAME, FNAME, CNO
  FROM CUST
 WHERE LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX
   AND (
  (LNAME = :LNAMEPREV AND CNO > :CNOPREV) 
  OR 
  (LNAME > :LNAMEPREV)
  )
 ORDER BY LNAME, CNO
WE WANT 20 ROWS PLEASE

确保：

• 严格 MC=1
• 100% 索引覆盖
• 零排序开销

14.2 列出你正在使用的优化器具有的最常见的缺点。

1. MySQL 的表连接只有嵌套循环一种方式
2. MySQL 只有 memory 引擎支持 HASH 索引
3. MySQL 不支持位图索引和函数索引
4. MySQL 的分区表只有本地索引，没有全局索引