快乐学习
前程无忧、中华英才非你莫属!

优化器不是完美的

练习

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

  • MC=1

  • 仅需访问索引

  • 无排序

  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 <= :LNAMEMAX) AND 

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

出于简化,设

LNAME >= :LNAMEPREV AND LNAME <= :LNAMEMAX 为 a

CNO > :CNOPREV 为 b

LNAME <> :LNAMEPREV 为 c

则原条件谓词转化为:

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),满足题目要求。最终的SQL改写为:

[sql]

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

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

  1. MySQL的表连接只有嵌套循环一种方式

  2. MySQL只有memory引擎支持HASH索引

  3. MySQL不支持位图索引和函数索引

  4. MySQL的分区表只有本地索引,没有全局索引

打赏
赞(0) 打赏
未经允许不得转载:同乐学堂 » 优化器不是完美的

特别的技术,给特别的你!

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!