原创

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

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://wxy0327.blog.csdn.net/article/details/50637063
第14章 优化器不是完美的
练习
14.1 重写SQL 14.8中的游标,使得新游标的访问路径满足:

  • 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 <= :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 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的分区表只有本地索引,没有全局索引
文章最后发布于: 2016-02-05 09:57:24
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览