之前在我的上一篇文章《系统性能优化二三事》中提到了sql的性能优化的问题。因为时间和篇幅的关系，当时并没有进行什么讨论，那现在我们就来讨论关于索引的一些事一些情~

    认识索引

    索引也叫做"键(key)"，是存储引擎用于快速找到记录的一种数据结构。 索引分为两种：b-tree索引、hash索引。这两种索引的实现上的区别是b-tree索引是建立一种叫b+tree的数据结构上面，而hash索引则是用一个hash函数建立一种类似hashmap的数据结构。因为底层结构的关系我们的b-tree索引支持、范围查询、排序、模糊匹配等功能,而hash索引只支持全值匹配查询。所以我们日常工作中用到的索引基本都是b-tree索引，并且后面的讨论的索引指的就是b-tree索引~。hash索引虽然只支持全值匹配，但是只要hash函数设计的好，key分布的均匀，基本上每次查询的时间复杂度都可以看作是O(1)，这性能也是杠杠的~。另外索引不一定是建了就能起效果，索引建的不好的一般的结果是没有走索引也就是索引没起效这种结果就是扫全表，但是有些时候你可能会发现走了某些索引的查询可能会比扫全表更慢。。。这个后面会有具体的例子进行量化分析~。所以我们知道建索引这么简单的一个操作也是个技术活~。

 

      确定建索引的目标

     建索引容易，但是建好索引不易。所以建索引之前我们先要了解一下索引是如何工作来帮助我们的sql实现快速查询，如果不了解其原理的话那我们就很难决定把表中的哪些列放到我们的索引和如何决定这些列的顺序~。

    其实总的来说 索引帮助我们的sql实现快速查是在体现在三个方面：

       1、减少服务器需要扫描的数据量。

       2、避免排序和临时表

       3、将随机I/O变成顺序I/O

   所以当我们建一个索引之后就可以围绕这三个维度来判断索引的好坏~。另外对于索引的评级是有一个三星标准的。

   1、如果与一个查询相关的索引行是相邻的，或者至少足够靠近的话，那这个索引就可以被标记上第一颗星。这最小化了必须扫描的索引片的宽度。这句话看字面可能起来有绕，其实简单一点描述就是指如果数据库需要扫描的索引行是最少的那就获得了第一颗星~

  2、如果索引行的顺序与查询语句的需求一致，则索引可以被标记上第二颗星。

  3、如果索引行包含查询语句中的所有列——那么索引就可以被标记上第三颗星。这避免了访问表的操作，仅访问索引就可以了。

    我们整理一下：

    数据库需要扫描的索引行是最少 -> 减少服务器需要扫描的数据量

     索引行的顺序与查询语句的需求一致 -> 避免排序和临时表，将随机I/O变成顺序I/O 

    索引行包含查询语句中的所有列 -> 减少服务器需要扫描的数据量,将随机I/O变成顺序I/O 

    所以你知道三星标准和三个维度其实说的是一回事儿~。 知道建索引的目标之后，那下面我们来学习一下如何建索引。

 

    如何建索引

    这里讨论建的索引指的是b-tree索引并且我们指定数据库是mysql，存储引擎是innodb。

    

    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)的话就要一个个字符