Mysql的索引知识点说起来感觉是一个大杂烩，但是总体的原则是根据使用情况来建立和使用索引。

根据使用情况来建立索引，经常用到的比如：

• 经常查询的列要建立索引
• 多表关联时，要保证关联字段上要有索引。

索引的存储：B+树

本文探讨的范围是索引中的普通索引，当对一个列创建索引也就是B+树之后，索引会包含该列的键值及键值对应行所在的 rowid。

null值存储

索引列的null值存在于B+树的最左边。目前常用版本的mysql，索引里面含有null是没有问题的。

普通索引特性（列的桶存储主键集合）

在建立索引时，有一个原则：区分度不大的字段上不宜建立索引。这有点像是基数排序和桶排序的思想，通过索引查找到的每个桶里面的主键就是目标范围。

Mysql使用B+树来组织这颗索引树，如下所示是一个5阶索引分裂的示意图：

如果通过索引找到的数据范围还是很大，回表或比较的开销还是很大，就要考虑是否还要走索引了（后面章节有具体的分析）。

更新索引树

通常来说，更新十分频繁的索引上不宜建立索引，如果索引的值发生变更，不仅主键索引指向的行的数据要修改，对应的B+树索引也会发生较多的数据移动来维持索引树的有序性和平衡性。

怎样命中索引

在查询的时候，即使查询条件命中了索引也不一定走索引树，我们可以使用expalin命令进行分析。

基本原则

命中索引是一个搜索B+树的过程，而SQL语法支持的查询语义特别多，像等值查询和范围查询都可以很方便的在B+树上进行查询。具体如下：

• 等值查询也就是说：列=值刚好命中索引列，则可使用这个索引。
• in、between，union, or 等语义查询也可以命中索引
• 范围查询可以命中索引
  • 这也是有别于hashmap的树结构的特性，树结构天然支持范围查询

分析什么时候走索引

基本原则是，少于某个百分比的数据回表才走索引，比如说少于30%的数据回表才走索引，具体是使用索引还是不使用普通索引，使用哪个索引会根据CPU、磁盘等的开销选择一个时间最优的计划。

通过索引中记录的 rowid 访问表中的数据就叫回表。回表次数太多会严重影响 SQL 性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。 EXPLAIN命令结果中的Using Index意味着不会回表，通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据

前面讲到了索引的区分度，在计算区分度的时候，要考虑计数和基数。

• 计数：表总行数
• 基数：某个列唯一值的个数 很明显，区分度=基数/计数，列的基数越大区分度越高。

如果多个列都命中的条件下，mysql也会选择一个区分度更大的索引去查询或者说开销更小的查询计划。

其他规则

等值查询和范围查询可以命中索引，但是他们的

• 负向条件查询不能命中索引，比如!= 、not in和 <>
• server执行函数计算不会命中索引，比如date(c)='xxx',c列上的索引将失去作用 注意这些目前来说还没有通融的余地，这可能是实现的问题，虽然有些函数可能可以优化，但是为了大局着想还是不便于优化的。

除此之外，还有两类索引值得特别注意，他们就是字符串索引和复合索引。

字符串索引

我们知道可以使用字符串索引，但是在使用字符串索引的时候经常会出现下面这个问题：

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

767bytes的长度限制是为什么呢？

Mysql索引的每个节点是一个Page,每个page 16K，如果不限制的话会导致索引的深度太大，一次查询的过程过于占用内存空间，反而使得使用索引的效率也不高。

而按照767bytes的限制，至少可以分裂出20阶树，4层的B+树可以存储大约16万数据量，5层可以存储320万数据量，从查询效率来看也是一个很好的折衷。

like

like查询是字符串查询可使用的SQL语法。语法如：

like '%xxx%'

mysql可支持like在前缀命中的情况下使用索引。

为什么能支持呢？ 因为前缀命中在B+树里面刚好也是一个连续的范围。 这个范围可表示为这样的一个前闭后开区间：

[第一个命中前缀的字符串,第一个不命中前缀的字符串)

假设like要查询的前缀为abc,有下图这样的一个索引树结构：

在这个图中，先查到第一个满足前缀要求的字符串abc,然后沿着abc依次往后查询，直到第一个不满足前缀要求的字符串位置。所以，这里["abc","abd")之间的范围是被命中的范围，就是我们要查询的数据。

复合索引

复合索引是含有两个以上列组成的索引，列在索引中的顺序会影响索引的命中。

• 列在索引中的顺序按照从左到右的顺序，左边的列被命中后，索引中更右边的列才有被命中的可能。
• 复合索引如果利用的好，sql语句利用覆盖索引进行查询，可以进一步避免回表。这不仅对于普通的行查询有用，也可以用于group by和order等复杂sql查询语义中。

下面分析一个问题， 问题： 有一个复合索引a,b,c,有一个查询a=1,b>1,c=1,能用到这个索引吗？

分析：

假设这个复合索引a,b,c的数据目前是这样的，那么对于查询a=1,b>1刚好命中是如下图红色部分的范围。

因为还有c=1的条件，于是能命中的索引数据只有1,3,1这一个。

所以我认为问题的答案是能用到这个索引，而且三个条件a=1,b>1,c=1都可以发挥作用。

如果这个问题改一下： 问题： 有一个复合索引a,b,c,有一个查询b>1,c=1,能用到这个索引吗？

答案：不能

总结

本文本文探讨的范围是索引中的普通索引，分析了Mysql索引的一些问题，主要是关于怎么建立索引、索引的存储结构以及思考关于怎么命中索引的问题。

在细节上，是否了解到了这些？

• 经常使用和联查的列要加索引
• B+树特性支持范围查询
• 字符串索引有长度限制
• 字符串like前缀可使用索引
• 复合查询也是前缀先匹配原则
• 联合索引的覆盖索引可避免回表