索引概念
索引(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中,含有空值的列很难进行查询优化,它们会使得索引、索引的统计信息及比较运算更加复杂。可以使用空字符串代替空值。