数据库索引(三)

本篇文章主要想要找到最理想的索引
数据库索引 和 数据库索引(二)
之前提到的知识可能还会在本文提到，可以结合服用，效果更佳。

---

涉及到的名词有：索引片、过滤因子、三星索引

索引片就是 SQL 查询语句在执行中需要扫描的一个索引片段。

WHERE 字句中的每个条件成为一个谓词

过滤因子 描述了谓词的选择性，即表中满足谓词条件的记录行数所占的比例。
多个匹配谓词做合成的组合谓词的过滤因子(由多个谓词的过滤因子乘积得到)越小越好，可以减少索引结果集的行数，增加查询效率。最差的过滤因子代表了索引查询响应的最长时间，我们一般使用最差的过滤因子来衡量组合谓词的可行性。
过滤因子(FF) = 结果集的数量 / 表行的数量平均过滤因子= 1 / 不同列值的数量

宽索引 即包含的索引列数大于2，也是之前提到的联合索引(复合索引)
窄索引 比如包含索引列数为 1 或 2。索引列数为1的时候就是单索引，为2的时候就是联合索引或者复合索引。

第一个索引列定义一个索引片，如果WHERE字句中有第二个列，而这个列也在索引上，从而使得这两个列能够一同定义一个更窄的索引片，这两个列叫做匹配列。定义的索引片叫做窄索引。

如果索引片越宽，那么需要顺序扫描的索引页就越多；如果索引片越窄，就会减少索引访问的开销。

SQL中的谓词主要有 LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS

三星索引

三星索引是对于一个查询语句可能最好的索引。通过三星索引，一次查询通常只需一次随机读(从磁盘读取到缓冲池)及一次窄索引的扫描。

• 索引中包含 WHERE 子句中所用到的所有列，查询相关的索引行是相邻的，标记上第一颗星。最小化碎片

最小化了必须扫描的索引片的宽度,索引过滤可以确保回表访问只发生在所有查询条件都满足的时候。

• 索引行的顺序与查询语句一致，标记上第二颗星。避免排序

这排除了排序操作。 ORDER BY 的列放入索引中,不改变顺序。这些列放在等值谓词前后都可以，但一定要放在范围谓词之前。

• 索引行包含了查询语句中的所有列，标记上第三颗星。避免回表查询

这样列值都在索引中，不需要访问表，同步读也不会出现问题。也就是覆盖索引，避免回表。

带来的问题：

1. 采用三星索引会让索引片变宽，这样每个页能够存储的索引数据就会变少，从而增加了页加载的数量。从另一个角度来看，如果数据量很大，比如有 1000 万行数据，过多索引所需要的磁盘空间可能会成为一个问题，对缓冲池所需空间的压力也会增加。
2. 增加了索引维护的成本。如果我们为所有的查询语句都设计理想的三星索引，就会让数据表中的索引个数过多，这样索引维护的成本也会增加。举个例子，当我们添加一条记录的时候，就需要在每一个索引上都添加相应的行（存储对应的主键值），假设添加一行记录的时间成本是 10ms（磁盘随机读取一个页的时间），那么如果我们创建了 10 个索引，添加一条记录的时间就可能变成 0.1s，如果是添加 10 条记录呢？就会花费近 1s 的时间。从索引维护的成本来看消耗还是很高的。当然对于数据库来说，数据的更新不一定马上回写到磁盘上，但即使不及时将脏页进行回写，也会造成缓冲池中的空间占用过多，脏页过多的情况。

---

你能看到针对一条 SQL 查询来说，三星索引是个理想的方式，但实际运行起来我们要考虑更多维护的成本，在索引效率和索引维护之间进行权衡。