mysql-索引(2)索引失效情况以及优化细节

索引的使用

本文主要是做笔记用的,所以有些凌乱，不喜勿喷。

一、索引匹配类型

tabA 表的组合索引 a,b,c
a varchar(24) 非空,
b int(11) 非空 ,
c varchar(20) 非空

1 全值匹配

explain select * from tabA where c='cat' and a='tom' and b=1 ;  

下面sql执行计划的索引长度 key_len=140 ，
计算方法：
设计表的时候会有一段字符编码，一般设置为utf-8,占3个字节，gbk 占2个字节，拉丁 占1个字节
(24+20)*3（utf-8 是每个长度由3个字节来存储） +intlength(4)+0（标志位，允许为空的额外长度计算值）+4（varchar类型最终要加2个字节，2个varchar4个字节）=140

2 匹配最左前缀

explain select * from tabA where  a='tom' and b=1 ;

下面sql执行计划的索引长度 key_len=78 ，
计算方法：
设计表的时候会有一段字符编码，一般设置为utf-8,占3个字节，gbk 占2个字节，拉丁 占1个字节
24*3（utf-8 是每个长度由3个字节来存储） +int(4)+2（varchar类型最终要加2个字节 ）=78

3 匹配列前缀

1）模糊查询 like ,失效情况 like后面直接加通配符

4 匹配范围查询

1)普通索引或者组合索引第一列用的范围查询，效率也很高，【因为会用到索引，innodb 的b+tree底层叶子会指向相邻叶子地址，是个双向链表，且叶子节点存储有序。】

explain select * from tabA where a>'tom'

2）组合索引用到了范围查询
这里索引长度key_len=78，说明索引只用到了 a,b ; c没有用到，说明组合索引用范围查询时，范围查询列后面的索引列会失效。

explain select * from tabA where a='tom' and b>1  and c='cat'

3）只用索引列做查询

explain select a,b,c from tabA where a='tom' and b=1  and c='cat'

执行计划extra = using index 表示索引覆盖，无需回表，效率高。

二、or生效情况和失效情况

1 、or生效情况
tabB表，只有 a,b,c 三个字段,均为int 类型且允许为null ，且为联合索引 a,b,c

explain select * from tabB where a=1 or b=3  ;

适用了联合索引，且key_len=15 ,= 4(int 长度为4)*3(3个int)+3（3个允许为空，每个空占一个字符）。表示索引用到了 a,b,c

explain select * from tabB where a=1  ;  -- 执行计划里 key_len=5

使用and

explain select * from tabB where a=1 and c=4 ;  -- 执行计划里 key_len=5;

explain select * from tabB where a=1 and b=4 ;  -- 执行计划里 key_len=10;

2 or失效情况
tabC表，4个字段 a,b,c,d，且联合索引为 a,b,c
a,b,c均为int类型，d为varchar(10) 类型，均允许为空。

-- 索引失效，执行计划里，没有使用索引。
explain select * from tabC where a=1 or b=3  ;
-- 索引失效 。
explain select * from tabC where a=1 or a=3  ;
explain select * from tabC where a in(1,3)  ;

如果不查询普通列，则用or也不会使索引失效，且key_len=15

explain select a,b,c from tabC where a=1 or b=3  ;  

小结：列全部是组合索引，即便使用or，索引也会生效。
表里除了组合索引的列，还含有其他列，
全表扫描的时候，
组合索引 a用in和or都会失效，
a=1 and b>2 and c=1 b后面的c会失效，因为用到了范围查询

索引失效情况：
1 order by a asc, b desc
2 like ‘%ms’ 通配符在前面的，大多数情况会导致索引失效，但不绝对
3 组合索引 a,b,c a=1 and b>2 and c=1， c会失效，因为b用到了范围查询
4 索引列上有函数
5 不满足最左前缀
6 使用不等号
7 隐士类型转换。比如varchar类型，结果写成数字，数据库会自动做类型转换，这个索引字段的索引功能就会失效

三、索引覆盖

1 、tabD ,a主键,b 索引,组合索引(c,d)

EXTRA using index
explain select c,d from tabD\G

2、tabE a主键,b 索引，c 普通列。

EXTRA using index
explain select a,b from tabE where b='123'\G

EXTRA：null
explain select a,c from tabE where b='123'\G

优化细节

1 索引列不要参与计算，

比如 a*3=15 这种，可以改为a=15/3 ,或者拿到业务层计算，不要在数据库层面去计算。

2 当需要很长的字符列作为索引的时候，选择创建前缀索引。

A.计算完整列的选择性，使前缀的选择性接近完整列的选择性。
select count(distinct cola)/count(*) from tabF;//得到一个浮点型小数a

B 计算不同前缀长度的选择性
select 
count(distinct left(cola,3))/count(*) as c3,
count(distinct left(cola,4))/count(*) as c4,
count(distinct left(cola,5))/count(*) as c5,
count(distinct left(cola,6))/count(*) as c6,
count(distinct left(cola,7))/count(*) as c7 from tabF;
会有接近a的值，

C.如果数据分布不均匀，只看数据选择性是不够的;
还要看数据重复次数：
select count(*) as cnt,left(city,4) as c4 from tabF group by c4 order by cnt desc limit 5;

D.综上，比如找到了一个合适的索引前缀的长度，创建即可
alter table tabF add key(cola(6));

a. 索引的选择性越高，查询效率越高，唯一索引的选择性是1，是最好的索引，性能也是最好的。
b. 索引的选择性：率重后的索引值和总记录数的比值
c. 对于很长的varchar类型的列，或者blob,text的列，必须使用前缀索引，mysql不允许索引这些列的完整长度。
d:前缀索引缺点：不能group by，order by ，和索引覆盖扫描

3 使用索引来做排序

索引存储的时候就维护数据按顺序存储。

-- 执行计划key=rental_date   extra:using index conditions
explain select rental_id ,staff_id from rental where rental_date='2005-05-31' order by  inventory_id,customer_id\G

--去掉invetory_id 的时候，key:rental_date ，extra： using index conditions，using filesort【表示用到文件排序】
explain select rental_id ,staff_id from rental where rental_date='2005-05-31' order by  customer_id\G

-- 去掉where条件，索引会失效，加上下面这个where条件，索引生效
explain select rental_id ,staff_id from rental where rental_date='2005-05-31' order by  rental_date,inventory_id,customer_id\G

--- 执行计划key=null   extra:using where ;using filesort  联合索引范围查询，索引失效
explain select rental_id ,staff_id from rental where rental_date>'2005-05-31' order by  rental_date,inventory_id \G

-- 排序一升一降 ，索引失效
explain select rental_id ,staff_id from rental where rental_date='2005-05-31' order by  inventory_id desc ,customer_id asc \G

如果是单列索引，通常情况下，or会使用索引；
如果是组合索引：
a 全部列均为组合索引，会用对应的索引，
b如果部分列是组合索引，那么不会使用索引。
具体是否生效，还是要看实际情况来分析。

4 隐士类型转换也会让索引失效

比如varchar类型，没加单引号，那么执行的时候，会导致该索引列失效。

5 数据更新频繁或者区分度不高的列，不建议创建索引。

基数，率重/总数 超过80% ,可以创建索引。

6 表连接的时候最好不要超过3张表

7 尽量使用limit

limit起到一个限制输出的作用，分页查询只是他的一个附带功能。但是涉及到limit 1000000,5 这种偏移量比较大的，可以替换为使用 in 或者 between and ，或者子查询 或者先投影查询再内联查。

8 单表索引有的书建议控制在6~7个以内

索引过多也会给数据库造成压力，影响性能。

9 单表字段组合索引尽量不要超过5个字段