本页将讨论
  • 什么是三星索引?
  • 为什么有时候无法同时获得第一、二颗星?

什么是三星索引?

实例:
select A,B,C,D from user where A="xx" and B = "xx" order by C;

比如A的选择性为 0.01%
B的选择性为 0.1%
最佳的索引是建复合索引 (A, B, C, D) ,这是一个三星索引。


第一颗星:
过滤尽可能多的行,这意味着把选择性高的索引放在前面A、B。
减少索引片的大小以减少需要扫描的数据行

第二颗星:
也就是说,当经过了A,B的筛选之后,筛选出来的行本身就是已C排序的。
避免排序,减少磁盘 IO 和内存的使用;

第三颗星:
通过宽索引实现索引覆盖。
避免每一个索引对应的数据行都需要进行一次随机 IO 从聚集索引中读取剩余的数据;

如果某个列经常更新,最好放在复合索引中后面的位置,以减小维护索引的代价。


为什么有时候无法同时获得第一、二颗星?


在实际场景中,问题往往没有这么简单,我们虽然可以总能够通过宽索引避免大量的随机访问,但是在一些复杂的查询中我们无法同时获得第一颗星和第二颗星。

SELECT id, name, age FROM users
WHERE age BETWEEN 18 AND 21
AND city = "Beijing"
ORDER BY name;

在上述查询中,我们总可以通过增加索引中的列以获得第三颗星,但是如果我们想要获得第一颗星就需要最小化索引片的大小,这时索引的前缀必须为 (city, age),在这时再想获得第三颗星就不可能了,哪怕在 age 的后面添加索引列 name,也会因为 name 在范围索引列 age 后面必须进行一次排序操作,最终得到的索引就是 (city, age, name, id):


如果我们需要在内存中避免排序的话,就需要交换 age 和 name 的位置了,在这时就可以得到索引 (city, name, age, id),当一个 SQL 查询中同时拥有范围谓词和 ORDER BY 时,无论如何我们都是没有办法获得一个三星索引的,我们能够做的就是在这两者之间做出选择,是牺牲第一颗星还是第二颗星。


实际上大多数时候,我们更偏爱第一颗星。我们希望减少需要扫描的数据行。






成为你想看到的世界变革力量

创建者:万乐荣
最后更新时间 : 2018年9月4日 18:21

评论