之前在我的上一篇文章《系统性能优化二三事》中提到了sql的性能优化的问题。因为时间和篇幅的关系,当时并没有进行什么讨论,那现在我们就来讨论关于索引的一些事一些情~
b-tree索引的结构如上图所示。从结构上我们知道b-tree索引是一个树状的数据结构结点间相互连接,同一层的结点从左到右按value的值排序好,其实这种数据结构就叫b+tree。这个b+tree中文的意思就叫平衡树的意思。叫平衡树的原因,因为从树的根结点到任意一个叶子经过的路径长度都是相等的~。对于b+ree这种搜索树的结构来说其实树越矮越胖是越好的,因为在这些结构中查询数据的时间复杂度其实是与树的高度成正比的~,原理和我们的二叉搜索树是一样的。正因为b-tree索引因为数据是排好序的所以它支持模糊匹配,范围查询,排序,group by 等高级操作~。
如果有一个情景是让我们从user表中查询名字叫"jack"的年龄是25岁的男性,那应该是怎么去建一个b-tree索引?。
如果是我一般会建一个(name,age,sex)的b-tree复合索引,why?。因为复合索引有一个最左匹配原则,我们sql的过滤条件都是先按最左边的过滤,如果索引的前面的列不能过滤那么后面的列也是不能过滤的这个道理我相信大家都理解。而我们的用户查询中常用的一个查询就是根据名字查询,所以name要放在第一列,至于第二列为什么放的是age呢,其实是从我们上面的三个维度和三星原则出发的。因为name通常是sex是强相关的,比如在西方国家一个叫jack的人通常可以肯定是个man而不会是个miss,这个在东方国家也是适用的,你能想像一个男人起个名字叫翠花么。。。并且因为人口基数众多,同名的人也多。所以name,sex放在前面两列是起不了多大的过滤效果。这就不符合我们的"减少服务器需要扫描的数据量"和三星标准的第一标准,但是age是可以的,并且如果是按年排序的话,age第二列就已经排好序了,不经意间我们就创建了一个二星索引,所以把age第二行!。sex第三列则是用于滤精确结果。如果查询只要求返回name,age,sex三列那我们都不需要去查表里面的数据,直接返回索引里面的数据就可以了,这就是一个perfect的三星索引~。
下面列举一些常用建索引的最佳实践:
1、一般建索引时优先创建复合索引而不是多个单列索引,比如常用的几个过滤条件是A,B,C。那就应该创建一个(A,B,C)同时包含三列的复合索引因为这样的过滤效果最好,而不是分别建A,B,C三个单独索引。因为mysql在一个查询语句中只能选择生效一个索引~。如果创建单列索引的话,比如生效了(A)索引,B,C列的过滤就只能是读取表中的记录之后再进行过滤,如果是(A,B,C)复合索引的话就直接在索引就可以过滤好记录,大大的减少了服务器的读取数量~。
2、复合索引一般是常用的过滤项放在靠前的位置,两个强相关的列不要放在紧挨在一起,这个上面的jack例子就有说明。
3、一般来说优先选择数字的列和字段较小的列建索引,选择数字的列是因为cpu天生就支持数字的比较,运算复杂度看作是O(1),如果是varchar(n)的话就要一个个字符