MySQL索引优化之--SQL处理过程

第3章 SQL处理过程

练习
3.1 为SQL3.7中所示的查询设计尽可能好的索引：

-- SQL 3.7  
SELECT LNAME, FNAME, CNO  
FROM CUST  
WHERE SEX = 'M'  
AND HEIGHT > 190  
 ORDER BY LNAME, FNAME ;

分析：
本书第4章提出了一个三星索引的概念。
如果与一个查询相关的索引行是相邻的，或者至少相距足够靠近的话，那这个索引就可以被标记上第一颗星。这最小化了必须扫描的索引片的宽度。
如果一个索引行的顺序与查询语句的需求一致，则该索引可以被标记上第二颗星。这消除了排序操作。
如果一个索引行包含查询语句中的所有列，那么该索引就可以被标记上第三颗星。这避免了访问表的操作。

在这个定义中又提及了索引行和索引片，概念如下。

索引行：
索引行与表中的行类似，就是索引中的一个特定索引值对应的索引条目。索引中的索引行都是唯一的，唯一索引和非唯一索引的区别是叶子页中一个索引行包含的指向表中记录的指针数。对于唯一索引，字段的值从表中复制到索引上，并加上一个指向表中记录的指针。对于非唯一索引，一个叶子页中索引行的实际存储方式是一个特定的索引值后带多个指向表中记录的指针。

索引片：
索引匹配列的值域范围。

查询语句的理想索引是一个三星索引。3.1的题目就可以转化成为SQL 3.7的查询语句创建理想索引的问题。
为了满足第一颗星
把等值谓词的列作为索引最开头的列————如果有多个等值谓词，索引中的字段以任意顺序都可以。对于SQL3.7来说，三星索引以SEX开头。又因为一个范围谓词是索引匹配过程中的最后一个匹配字段，所以定义索引(SEX,HEIGHT)可以让DBMS读取多个窄片索引。在这种情况下，必须扫描的索引片宽度被缩减至最窄。
为了满足第二颗星
将ORDER BY列加入到索引中。不要改变这些列的顺序，但是忽略那些在第一步中已经加入索引的列。在SQL3.7中，因为既有范围谓词又有排序，并且是不同的列，所以第一颗星和第二颗星只能满足一个。也就是说无法为SQL3.7创建一个理想索引，只能为其创建一个最佳索引。候选索引为(SEX,HEIGHT)和(SEX,LNAME,FNAME)
为了满足第三颗星
将查询语句中剩余的列加到索引中去，列在索引中添加的顺序对查询语句的性能没有影响，但是将易变的列放在最后能降低更新的成本。候选索引为(SEX,HEIGHT,LNAME,FNAME,CNO)和(SEX,LNAME,FNAME,HEIGHT,CNO)

至此，为SQL3.7设计的候选索引为(SEX,HEIGHT,LNAME,FNAME,CNO)和(SEX,LNAME,FNAME,HEIGHT,CNO)，第一个可以缩减需要扫描的索引片但需要排序，第二个可以消除排序但索引片不是最小。这两个方案最终选择哪个，还需要过滤因子和结果集大小的相关信息。这个例子中，身高高于190的过滤因子应该很小，从而得到的结果集也很小，而通常来说，对一个小的结果集排序要比扫描一个大的索引片要好，所以最佳索引为(SEX,HEIGHT,LNAME,FNAME,CNO)。

3.2 为SQL3.8中所示的查询设计尽可能好的索引：

1. SQL 3.8  
   SELECT LNAME, FNAME, CNO  
   FROM CUST  
   WHERE SEX = 'M'  
   AND (WEIGHT > 90 OR HEIGHT > 190)  
   ORDER BY LNAME, FNAME ;

分析：
在SQL 3.8的查询中，由于OR操作符的存在，无论是(SEX,WEIGHT,HEIGHT)索引还是(SEX,HEIGHT,WEIGHT)索引，DBMS都不能只读一个索引片，可行的替代方案有全索引扫描、全表扫描，以及多索引访问。

假设一个谓词的判定结果为false，而这时如果不检查其他谓词就不能确定地将一行记录排除在外，那么这类谓词被称为非布尔（non-Boolean term）谓词或非BT谓词。SQL 3.8里的WEIGHT > 90和HEIGHT > 190就是非BT谓词，而SEX = 'M'是BT谓词。非BT谓词对于优化器而言就是太困难的。

同样依据对SQL3.7的分析，最佳索引为(SEX,LNAME,FNAME,WEIGHT,HEIGHT,CNO)，虽然这个索引需要扫描的索引片很大（50%左右），但通过全索引扫描和过滤可以消除排序和回表。

参考书籍：数据库索引设计与优化   作者：（美）拉赫登迈奇，（美）利奇 著

打赏

微信扫一扫，打赏作者吧～