MySQL索引

本文所说的索引都是针对Innodb而言的，对MyIsam引擎而言是不适用或者不完全适用的

1. 索引都会提到的BTree和B+Tree

但凡提到索引，都会提到这两种树，因为本文的重点是索引的一些使用上的问题，所以对其数据结构并不做深入讨论，只需要知道BTree和B+Tree最大的差别在于B+Tree只有叶子节点会存在Data域。

2. 为什么使用主键索引会更快

想要解释这个问题，就必须要先了解聚集索引和非聚集索引其存储内容的差别，聚集索引一般情况下又可以称之为主键索引，其索引树的叶子节点中的Data域会引用具体数据，而非聚集索引其叶子节点的Data域中存的只是主键值，所以通过主键查询数据时我们只需要查找一遍聚集索引树就能得到相应数据，但不通过主键查询数据时，我们会在非聚集索引树中找到相应主键后再从主键索引树中查找相应数据。所以通过主键查询数据会比使用其他索引更快。

3. 索引使用的一些注意事项

# 以这个索引为例 create index test_index on person(name, age, sex);

3.1 最左前缀原则

最左前缀原则指查询索引从索引的最左列开始，并且不会跳过中间的列。例如：

# 该sql是可以命中该索引的，能命中索引列的前两列 select * from person where name='lisi' and age=13 ; # sql1 # 该sql是无法命中索引的，因为不符合最左前缀原则 select * from person where age=13; # sal2 # 该sql是能命中索引的，但只能命中第一列, 因为索引列sex前面还有一个age select * from person where name='lisi' and sex=1; # sql3 # 下面这条看起来貌似不符合最左匹配原则，但是也能命中的 select * from person where age=13 and name='lisi'; # sql4 # 原因是因为MySQL是有执行优化器的，它会调整sql的顺序以最大程度的命中索引，所以其和sql1是没有差别的

所以，索引建立的时候第一列应该选择最容易被命中的，使得查询语句能够命中索引

3.2 不要在索引列上做操作

# 该条sql用不到索引 select * from person where substring(name, 1)='lisi';

3.3 范围条件会导致其右边的列失效

# 该条sql只能命中索引的前两列 select * from person where name='lisi' and age>13 and sex=1;

这里说的右边的列并不是sql语句中的右边的列，因为sql语句会被优化器优化重排，这里说的右边的列是指索引列的右边。

3.4 尽量使用覆盖索引

覆盖索引的意思是查询的列都在一个索引上，这样子他们无需再去查询聚集索引树去拿具体的信息，只需要遍历非聚集索引树的相关节点就可以拿到需要的信息。

# 该条sql使用覆盖索引 select age from person; # 该条sql也能使用索引，只是where条件的内容需要mysql的server层过滤, 前提是where条件和查询列在一个索引中 select age from person where sex=1;

3.5 null判断会导致索引列失效

# 该条sql只能用到name索引列 select * from person where name='a' and age is not null

3.6 like字段以通配符开头会导致索引列失效

# 该条sql只能用到name索引列 select * from person where name='a' and age like '%1';

3.7 or不同字段会导致索引失效

# 该条sql会使用全表扫描 select * from person where name='a' or age=21; # 该条sql会使用索引，因为等同于in select * from person where name='a' or name='b';