本篇文章主要想要找到最理想的索引
数据库索引 和 数据库索引(二)
之前提到的知识可能还会在本文提到,可以结合服用,效果更佳。
涉及到的名词有:索引片、过滤因子、三星索引
索引片就是 SQL 查询语句在执行中需要扫描的一个索引片段。
WHERE 字句中的每个条件成为一个谓词
过滤因子 描述了谓词的选择性,即表中满足谓词条件的记录行数所占的比例。
多个匹配谓词做合成的组合谓词的过滤因子(由多个谓词的过滤因子乘积得到)越小越好,可以减少索引结果集的行数,增加查询效率。最差的过滤因子代表了索引查询响应的最长时间,我们一般使用最差的过滤因子来衡量组合谓词的可行性。
过滤因子(FF) = 结果集的数量 / 表行的数量平均过滤因子= 1 / 不同列值的数量
宽索引 即包含的索引列数大于2,也是之前提到的联合索引(复合索引)
窄索引 比如包含索引列数为 1 或 2。索引列数为1的时候就是单索引,为2的时候就是联合索引或者复合索引。
第一个索引列定义一个索引片,如果WHERE字句中有第二个列,而这个列也在索引上,从而使得这两个列能够一同定义一个更窄的索引片,这两个列叫做匹配列。定义的索引片叫做窄索引。
如果索引片越宽,那么需要顺序扫描的索引页就越多;如果索引片越窄,就会减少索引访问的开销。
SQL中的谓词主要有 LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS
三星索引
三星索引是对于一个查询语句可能最好的索引。通过三星索引,一次查询通常只需一次随机读(从磁盘读取到缓冲池)及一次窄索引的扫描。
- 索引中包含 WHERE 子句中所用到的所有列,查询相关的索引行是相邻的,标记上第一颗星。最小化碎片
最小化了必须扫描的索引片的宽度,索引过滤可以确保回表访问只发生在所有查询条件都满足的时候。
- 索引行的顺序与查询语句一致,标记上第二颗星。避免排序
这排除了排序操作。 ORDER BY 的列放入索引中,不改变顺序。这些列放在等值谓词前后都可以,但一定要放在范围谓词之前。
- 索引行包含了查询语句中的所有列,标记上第三颗星。避免回表查询
这样列值都在索引中,不需要访问表,同步读也不会出现问题。也就是覆盖索引,避免回表。
带来的问题:
- 采用三星索引会让索引片变宽,这样每个页能够存储的索引数据就会变少,从而增加了页加载的数量。从另一个角度来看,如果数据量很大,比如有 1000 万行数据,过多索引所需要的磁盘空间可能会成为一个问题,对缓冲池所需空间的压力也会增加。
- 增加了索引维护的成本。如果我们为所有的查询语句都设计理想的三星索引,就会让数据表中的索引个数过多,这样索引维护的成本也会增加。举个例子,当我们添加一条记录的时候,就需要在每一个索引上都添加相应的行(存储对应的主键值),假设添加一行记录的时间成本是 10ms(磁盘随机读取一个页的时间),那么如果我们创建了 10 个索引,添加一条记录的时间就可能变成 0.1s,如果是添加 10 条记录呢?就会花费近 1s 的时间。从索引维护的成本来看消耗还是很高的。当然对于数据库来说,数据的更新不一定马上回写到磁盘上,但即使不及时将脏页进行回写,也会造成缓冲池中的空间占用过多,脏页过多的情况。
你能看到针对一条 SQL 查询来说,三星索引是个理想的方式,但实际运行起来我们要考虑更多维护的成本,在索引效率和索引维护之间进行权衡。