数据库索引设计与优化读书笔记--《四》为SELECT语句创建理想的索引

磁盘及CPU时间的基础假设

I/O时间：

随机读	10ms（4KB或8KB的页）
顺序读	40MB/s

顺序扫描的CPU时间：6XZX

检查一行记录	5us
FETCH	100us

三星索引

星级是如何给定的

如果与一个查询相关的索引行是相邻的，或者至少相距足够靠近的话，那这个索引就可以被标记上第一颗星。这最小化了必须扫描的索引片的宽度。
如果索引行的顺序与查询 语句的需求一致，则索引可以被标记上第二颗星。这排除了排序操作。
如果索引行包含的查询语句中的所有列，那么索引就可以被标记上第三颗星。这避免了访问表的操作：仅访问索引就可以了。
对于这三颗星，第三颗通常是最重要的。将一个列排除在索引之外可能会导致许多速度较慢的磁盘随机读。我们把一个至少包含第三颗星的索引成为对应查询语句的宽索引。

宽索引：宽索引是指一个至少满足第三颗星的索引。该索引包含了SELECT语句所涉及的所有列，因而能够使得查询只需访问索引而无需访问表。

如何构造一个三星索引

为了满足第一颗星

首先取出所有等值谓词的列（WHERE COL=…）。把这些列作为索引最开头的列——以任意顺序都可以。这样，必须扫描的索引片的宽度将被缩减至最窄。

为了满足第二颗星

将ORDER BY列加入到索引中。不要改变这些列的顺序，但是忽略哪些在第一步中已经加入索引的列。由于将ORDER BY语句中的列现在加入了索引，所以结果集中的记录无需排序就已经是以正确的顺序排列了。

为了满足第三颗星

将查询语句中剩余的列加入到索引中去（SELECT LNAME, CITY…），列在索引中添加的顺序对查询语句的性能没有影响，但是将易变的列放过在最后能够降低更新的成本，现在，索引已经包含了满足无需回表的访问路径所需的所有列。

我们理想的索引有几颗星呢？首先它必须得有第三颗星。有时候，第一颗星和第二颗星我们只能二选一。比如存在范围谓词时，我们（也许）不得不牺牲第二颗星来满足一个更窄的索引片（第一颗星）：

• 避免排序——拥有第二颗星
• 拥有可能的最窄索引片，不仅将需要处理的索引行数降至最低，而且将后续处理量，特别是表中数据行的同步读，减少到最小——拥有第一颗星。

（一般情况下第一颗星比第二颗重要，但不总是这样）。

根据星级创建最佳索引的算法

候选A:（去掉第二颗星）

1. 取出对于优化器来说不过分复杂的等值谓词列。将这些列作为索引的前导列——以任意顺序皆可。
2. 将选择性最好的范围谓词作为索引的下一个列，如果存在的话，最好的选择性是指对于最差的输入值有最低的过滤因子。只考虑对于优化器来说不过分复杂的范围谓词即可。
3. 以正确的顺序添加ORDER BY列（如果ORDER 列有DESC的话，加上DESC）。忽略在第1布或第2步中已经添加的列。
4. 以任意顺序将SELECT语句中的其余的列添加至索引中（但是需要以不易变的列开始）。

候选B:（去掉第一颗星）
如果候选A引起了所给查询语句的一次排序操作，那么还可以设计候选B，对于候选B来说第二颗星比第一颗星更重要。

1. 取出对于优化器来说不过分复杂的等值谓词列。将这些列作为索引的前导列——以任意顺序皆可。
2. 以正确的顺序添加ORDER BY列（如果ORDER BY列中有DESC的话，加上DESC）。忽略在第一步中已经添加的列。

由于现在的硬件条件下排序速度很快，所以如果一个程序取出结果集的所有行，那么候选A可能和候选B一样快，甚至比候选B更快。然而，如果一个程序只需获取能够填充满一个屏幕的数据量，那么候选B可能比候选A快很多。如果访问路径中没有排序的话，数据库管理系统只要一次一次地读取数据行就能对结果集进行物化。如果结果集很大的话，为了产生第一屏的数据，候选A需要排序可能会花费非常长的时间。

（我的理解是：在LIMIT和OFFSET这类SQL中尤为明显，从百万行级的结果集中每次按顺序取20行，那么如果没有第二颗星，每次事务都需要排序的话，那么效率是极差的）。

设计索引的要求

机械性的为每一个查询设计最佳索引也是不明智的，因为索引的维护可能会使得一些程序速度太慢或者使磁盘负载超负荷。实际情况中更常见的情况是，只对那些由于不合适的索引而导致速度太慢的查询语句进行索引设计。