Mysql索引详解

索引概念

索引（index）是帮助Mysql高效获取数据的数据结构，索引的本质就是数据结构，索引可以简单理解为 “排好序的快速查找的数据结构”，对于数据库的表而言，索引其实就是它的“目录"。

为什么要使用索引

索引的出现提高了数据查询的效率，降低数据库的io成本，通过索引队列数据进行排序，降低了数据排序的成本，降低了cpu的消耗。在使用索引的时候，尽量使用覆盖索引（下文介绍），只访问索引的查询，并减少select* 的使用（下文介绍）。

索引提高查询效率的原因：
Mysql中索引数据结构是B+Tree，B+Tree是由二叉树、平衡二叉树、BTree逐渐演化过来的。
B+Tree的结构如下：

当我想要查找主键为10的这条数据（比如：select name from table where id = 10，上图假设为主键索引的 B+Tree），会先在磁盘块1进行一次IO操作，然后找到磁盘块2进行一次IO操作，最后找到磁盘块5，再次进行一次IO操作，最后查询出数据。普通索引都是和主键相关联的（下文介绍）。如果没有索引，那就需要一条一条数据的查找，进行频繁的IO操作。

索引分类

（索引的分类有很多层次，这里的分割点是主键还是非主键）
B+Tree索引可以分为两大类，主键索引和非主键索引（普通索引），并且每一个索引都会对应一棵B+Tree树。

主键索引

主键索引又叫做聚集索引（聚簇索引），每张表中有且仅有一个主键（该主键可以由表中的一个字段或者多个字段组成），该主键就会自动成为表中的一个索引。如果不主动创建主键，那么InnoDB会选择一个不包含有null值的唯一索引作为主键，如果连唯一索引都没有，InnoDB就会为该表默认生成一个6字节的rowid作为主键。主键索引的叶子节点存放的是整行数据。

主键索引的三个条件：主键值必须唯一；不能包含null值；要保证该值是自增属性。
为什么要主键值要自增，见上图，主键索引叶子节点的值是从小到大排好序的，如果主键本身就是自增的，那插入时效率会大大提升。

创建主键索引的语句：

alter table table_name add primary key(column);

查看某张表的索引：

非主键索引

非主键索引中又存在很多其他索引类型。

单值索引

单值索引是表中的一列为一个索引，一个表可以存在多个单值索引。
非主键索引的叶子节点放的是主键（聚集索引）的值，也就是说如果想要通过非主键索引来查内容，最终还是依赖于主键索引。

如下图，ID为主键字段，k为普通字段，二者都是索引。如果查询语句为 select * from T where ID=500，则肯定会搜索第一棵索引树。如果查询语句为 select from T where k =5,则会查询第二棵 K索引树，得到ID的值为500，然后在去ID索引树重新查询一遍，这个过程叫做回表操作。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

唯一索引

唯一索引表示该列中不能有重复的值，但是可以有一个null值，它和主键索引的关系是：一个表中只可以有一个主键索引，但是可以存在多个唯一索引。
创建唯一索引的语法：

alter table table_name add unique(column);

因为唯一索引定义了唯一性，当查找到第一个满足条件的记录后，就会停止继续检索。而普通索引找到记录后，还会查找下一个记录，直到碰到第一个不满足条件的记录。

复合索引

顾名思义，一个复合索引（联合索引）可以包含多个字段。在使用复合索引时，必须满足最左前缀法则，一般都是把选择性高的列放在前面。一条查询语句可以使用复合索引的一部分字段，但是必须是从最左面的第一个字段开始使用，不能没有第一个字段，也不能跳过字段。
创建一个复合索引的语法,表 t 中的c1 、c2、c3 字段：

create index idx_c1_c2_c3 on t(c1,c2，c3);

正确的使用该复合索引的几种方法如下：

select * from t where c1="某值";
select * from t where c1="某值" and c2 ="某值" and c3="某值";
select * from t where c1="某值" and c2 in(某值，某值) and c3 = "某值";
select * from t order by c1,c2,c3;
select * from t where c1="某值" order by c2;

反之，使用不到索引的情况：

select * from t where c2="某值";
select * from t where c3="某值";
select * from t where c1="某值" and c3="某值";
select * from  t where c1="某值" or c2="某值"; //使用or连接索引也会失效

覆盖索引

Mysql只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后再取回表查询数据，这就减少了大量的IO操作，查询速度也非常快。
上面“单值索引”的模块中，提到了回表操作，并且非主键索引都是依赖于主键索引的，所以当通过非主键索引字段来查询主键，就可以不用再回表查询，这就是索引的覆盖。
如下 id和name 为索引：

select id from table where name = "张三";

因为name的B+Tree中叶子节点放的是id的值，所以可以直接从叶子节点中取到id，而不用再通过id去回表重新查一次了。

索引下推

而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
如下联合索引（name，age）：

 select * from tuser where name like '张%' and age=10 and ismale=1;

在Mysql 5.6之前没有索引下推的时候，执行过程图如下：

绿色箭头表示回表操作，执行过程是先找到所有“张”开头的name字段，然后根据对应的ID再去查询满足条件的内容。
下面是Mysql 5.6之后出现索引下推的执行过程图：

因为name 和age是复合索引，所以可以直接查到符合 name 和age条件的ID，然后再根据ID去查找对应的数据，这样可以减少回表的操作。

索引失效

上面简单描述了复合索引失效的情况，接下里详细说一下各类索引失效的场景。

1、使用join连接时

当使用join连接进行多表联查时，假设是左连接，左连接是左面的表都有，右面的表选择性的，所以会从右面的表开始搜索，当进行左连接的时候，要把索引加在右边的表中，如果加在左边，则索引会失效。

2、复合索引失效

见上复合索引中描述

3、单值索引失效

1、使用操作函数

select * from tb_user where left(name,1) ="唐"

上面代码的意思是查询name 前1个字段为“唐”的数据，这样写name字段所在的索引会失效；不能在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效并且转向为全表扫描。

2、使用范围查询

Select * from table where name ="zhangsan" and age>29 and sex = "nan"

如上代码中 name，age，sex为复合索引，则从age开始（不包括age，age会用到一部分），右面字段所在的索引都会失效。所以，当复合索引遵循最左前缀法则时，如果使用了范围查询，则该列后面的字段索引都会失效。单值索引更是如此，使用范围就会失效。

3、使用like等符号查询

select * from tuser where name like '%三' 

如上sql就会失效，使用like进行模糊查询的时候，要将“%”放在最后面（最右面），放在前面的话索引会失效。使用其它符号如 ！= 或者<> 的时候也会无法使用索引会导致全表扫描。

4、字符串失去单引号
字符串类型和varchar类型一定不要失去单引号。否则索引会失效。

4、使用order by 索引失效

orderby也会用到索引，索引功能包括两部分，查找和排序，orderby只是用到了排序，没有用到查找。
使用orderby的话，如果不想让索引失效，一定要从第一个索引开始使用，查找和排序一定都要按照索引的顺序来。
假设c1到c5为复合索引。

Select * from test1 where c1=1 and c5=5 order by c3，c2

如上的sql索引会失效，因为会产生 filesort文件，因为排序的时候是先有的c3，然后才有的c2，没有按照索引的顺序。

Select * from test1 where c1=1 and c2=2 and c5=5 order by c3，c2

上面的sql就不会索引失效，因为在order by 之前就已经存在 c1 和c2 了。

适合创建索引的情况

1、频繁作为查询条件的字段适合创建索引。
2、当前表与其他表关联的字段，作为外键。
3、高并发的情况下倾向创建组合索引。
4、表中数量过多时，当表中有300w条数据时，如果不建立索引，则mysql性能开始下降。
5、查看索引的选择比例，越接近1，索引的效率越高，索引的选择性=基数/总行数。
举例说明：假如有一张表格，总共有一万行的记录，其中有一个性别列sex，这个列的包含选项就两个：男/女。那么，这个时候，这一列创建索引的话，索引的选择性为万分之二，这时候，在性别这一列创建索引是没有啥意义的。假设个极端情况，列内的数据都是女，那么索引的选择性为万分之一，其效率还不如直接进行全表扫描。如果是主键索引的话，那么选择性为1，索引价值比较大。可以直接根据索引定位到数据。

不适合出创建索引的情况

1、表中的数据过少
2、经常进行增删改的表，
3、大数据类型字段不要创建索引，因为每次更新不但会更新记录，还会更新索引（索引也是一张表）。
4、尽量避免使用null，在mysql中，含有空值的列很难进行查询优化，它们会使得索引、索引的统计信息及比较运算更加复杂。可以使用空字符串代替空值。