要设计好索引，首先要了解SQL语句在数据库服务器中的处理流程。本文介绍了数据库索引设计和优化中对索引优化非常重要的几个概念。谓词谓词是条件表达式。SQL语句的where子句由一个或多个谓词组成。WHERESEX='M'AND(WHIGHT>90ORHEIGHT>190)上面的WHERE子句有三个简单的谓词：SEX='M'WRIGHT>90HEIGHT>190也可以认为是两个组合谓词：WEIGHT>90ORHEIGHT>190SEX='M'AND(WEIGHT>90ORHEIGHT>190)优化器和访问路径关系数据库的一个主要优点是用户不需要访问关系数据。它的访问路径由DBMS的一个组件，即优化器决定。优化器是SQL处理的核心。这里我们以mysql为例，展示一个简单的mysql服务器逻辑结构。我们可以在图中看到优化器的位置。在真正执行SQL语句之前，优化器必须首先确定如何访问数据。例如，mysql会解析查询并创建一个解析树，然后对其进行各种优化，包括决定选择合适的索引和决定表的读取顺序。谓词表达式是索引设计的主要出发点。如果一个索引能够满足SELECT查询语句的所有谓词表达式，那么优化器很可能建立了一条高效的访问路径。如果索引切片和匹配列以B+树的形式组织，如果有谓词表达式WHEREA>100ANDA<110，那么最终查询到的叶子节点范围如下图所示：图中的一侧是indexed一个窄段，我们称这个段为索引段。该段将被顺序扫描。上面索引行的值在100到110之间，对应的表行会通过同步读取的方式从表中（可能在缓冲池中）读取。因此，访问路径的代价很大程度上取决于索引切片的厚度，即谓词表达式确定的取值范围。索引切片越厚，需要扫描的索引页越多，需要处理的索引记录也越多，但最大的开销还是来自于增加的表同步读操作，以及每张表所需的I/O页面读取操作可能需要10毫秒。相应地，较窄的索引切片会减少对表的同步读取。索引过滤器和过滤列并不是所有的索引列都可以定义索引切片的大小。有时，一个列可能同时存在于WHERE子句和索引中，但该列不能参与索引切片的定义，例如。表上有一个联合索引（A，B，C，D），sql语句如下：WHEREA=:AANDB>:BANDC=:C我们需要判断WHERE子句中的谓词是否可以判断大小索引切片：首先，我们看在WHERE子句中，这一列是否至少有一个足够简单的谓词与之对应？如果是，则此列是匹配列。如果不是，那么这个列和它后面的索引列都是不匹配的列。如果谓词是范围谓词，则其余索引列是非匹配列。对于最后一个匹配列之后的索引列，如果有一个足够简单的谓词与之对应，那么该列就是过滤列。根据这个方法，我们可以判断A列出现在一个等价谓词中，这是一个足够简单的谓词，所以A是一个匹配列，B列是一个范围谓词，也是一个匹配列。B后面的列C不能定义索引切片（它不能使索引切片变窄），但它仍然可以参与索引切片的过滤过程。也就是说，我们通过A列和B列来定义索引切片的大小，C列不能，但是在访问表之前，我们仍然可以通过C列过滤记录，这样可以减少不必要的表访问。C列是过滤列，与A列和B列一样重要。总结：上面的WHERE子句有两个匹配列，A列和B列，定义了要扫描的索引切片。除此之外，还有一个列C作为过滤列。所以只有当一行满足所有三个谓词时，表中的数据才会被访问。如果B列的谓词表达式是等价谓词，那么三列都可以作为匹配列。如果取消A列的谓词表达式，那么索引片段就是整个索引的大小，B列和C列都只能用于过滤。过滤因子过滤因子描述的谓词的选择性，即表中满足谓词条件的记录行数所占的比例，主要取决于列值的分布。过滤因子的计算公式为：结果集数/表行数。例如，我们的一个用户表有一个SEX字段。当添加女性用户时，SEX='F'的过滤因子会变大。如果男性占表的70%，则SEX='M'的过滤因子为70%，SEX='F'的过滤因子为30%，SEX列的最坏情况过滤因子为70%，平均过滤系数50%。如果男女比例是1比1，那么SEX列最坏情况下的过滤因子和平均过滤因子都是50%。当我们评估一个索引是否合适时，最坏情况过滤因子比平均过滤因子更重要，因为最坏情况与最坏输入相关，即在输入条件下，基于特定索引的查询将消耗时间最长。复合谓词的过滤因子那么我们如何计算三组合谓词表达式的过滤因子呢？如果构成谓词的列不相关，则复合谓词的过滤因子可以从单个谓词的过滤因子导出。不相关是指两个谓词的取值互不影响。比如我们有一个user表，有两个字段“province”和“city”，那么这是两个相关的谓词，因为city的值一定是他所在省份的城市。CITY和BD（生日）是不相关的谓词。例如，组合谓词CITY=:CITYANDBD=:BD的过滤因子等于谓词CITY=:CITY和谓词BD=:BD的过滤因子的乘积。如果CITY列有2000个不同的值，BD列有2700个不同的值，那么复合谓词的过滤因子是：1/2000*1/2700。那么列组合[CITY,BD]总共有5,400,000个不同的值。对于相关列，数值会比这个小很多。我们在设计索引结构时，需要将SQL语句中的组合谓词作为一个整体来考虑，来评估过滤因子。过滤因素对索引设计的影响显然，需要扫描的索引分片的大小对访问路径的性能有着至关重要的影响。过滤因子越小，过滤出的索引片越小，意味着访问表的次数越少。假设该表有一个用于SQL语句的联合索引（MAKE、MODEL、YEAR）：SELECTPRICE、COLOR、DEALERNOFROMCARWHEREMAKE=:MAKEANDMODEL=:MODELORDERBYPRICEMAKE和MODEL是匹配列。如果复合谓词的过滤因子是0.1%，那么需要访问的索引分片大小将是整个索引的0.1%。对于下面的sql语句，这个索引不是很好：SELECTPRICE,COLOR,DEALERNOFROMAUTOWHEREMAKE=:MAKEANDYEAR=:YEAR由于联合索引的最左匹配原则，匹配的列只有MAKE。过滤系数为1%，索引切片比较大。SQL语句：SELECTLNAME,FNAME,CNOFROMCUSTWHERESEX='M'AND(WEIGHT>90ORHEIGHT>190)ORDERBYLNAME,FNAME这条SQL语句搜索的是身材高大，有一定要求的男性。此时匹配的谓词只有一个SEX，过滤因子一般为50%，如果表有100万行，那么索引切片就有50万行，这是一个相当厚的索引切片。练习思考为以下两个SQL语句设计最佳索引SELECTLNAME,FNAME,CNOFROMCUSTWHERESEX='M'ANDHEIGHT>190ORDERBYLNAME,FNAMESELECTLNAME,FNAME,CNOFROMCUSTWHERESEX='M'AND(WHIGHT>90ORHEIGHT>190)ORDERBYLNAME,FNAME
