一篇文章彻底搞懂日常开发MySql索引最佳实践（基础篇）

---

---

前言

这一篇主要讲日常开发常见的SQL优化
看这篇文章需要了解explain调优工具每列是干嘛的，不了解的看我上一篇文章Explain调优工具

---

一、准备工作：用到的表、数据

drop table if exists employees;
create table employees
(
    id        int                                 not null
        primary key,
    name      varchar(24)                         not null,
    age       int                                 null,
    position  varchar(20)                         null,
    hire_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP
);

create index idx_name_age_position 
    on employees (name, age, position);

INSERT INTO mysql.employees (id, name, age, position, hire_time) VALUES (1, 'LiLei', 22, 'manager', '2022-09-24 17:30:05');
INSERT INTO mysql.employees (id, name, age, position, hire_time) VALUES (2, 'HanMeimei', 23, 'dev', '2022-09-24 17:30:05');
INSERT INTO mysql.employees (id, name, age, position, hire_time) VALUES (3, 'Lucy', 23, 'dev', '2022-09-24 17:30:05');

---

二、SQL优化建议

2.1、全值匹配

explain select * from employees where name = 'Lilei'

explain select * from employees where name = 'Lilei' and age =22;

explain select * from employees where name = 'Lilei' and age =22 and position='manager';

---

2.2、最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;

EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';

EXPLAIN SELECT * FROM employees WHERE position = 'manager';

可以看到第二个和第三条SQL 是SQL没生效的。

---

2.3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换）

在索引上做操作，会导致索引失效而转向全表扫描。

explain select * from employees where name='LiLei';

正常走索引查询是ref。

explain select * from employees where left(name,3)='LiLei';

这里是对name做了left操作，可以看见成了ALL（全表扫描）。

#添加时间索引
alter table employees add index idx_hire_time(hire_time);
#使用date函数
EXPLAIN select * from employees where date(hire_time) ='2018‐09‐30';

下面这个案例可以看见，possible_keys是值为索引，但是key为null，这是因为mysql认为全秒扫描更快，数据量大的时候也有可能走索引。

explain select * from employees where hire_time >='2022-09-24' and hire_time<'2022-09-25';

还原最初索引状态

alter table employees drop index idx_hire_time;

---

2.4、尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

用了覆盖索引，就不用进行回表，性能更快。

explain select name, age, position from employees where name='LiLei' and age =23 and position='manager';

select *场景,可以看见Extra列为空。

explain select * from employees where name='LiLei' and age =23 and position='manager';

---

2.5、mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描，< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

explain select * from employees where name !='LiLei'

---

2.6、is null,is not null 一般情况下也无法使用索引

一般索引列，都不建议有null的情况，最差情况也要给个默认值

explain select * from employees where name is null

---

2.7、like以通配符开头（‘$abc…’）mysql索引失效会变成全表扫描操作

explain select * from employees where name like '%LEI'

一般like,通配符放后面。

explain select * from employees where name like 'LEI%'

问题：解决like’%字符串%'索引不被使用的方法？

使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei';

---

2.8、字符串不加单引号索引失效

带引号

explain SELECT * FROM employees WHERE name='1000';

不带引号、全表扫描

explain SELECT * FROM employees WHERE name=1000;

---

2.9、少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

explain select name, age, position from employees where name='LiLei' or name ='ljc'

---

2.10、范围查询优化

给年龄添加单值索引

#添加索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;

explain select * from employees where age >=1 and age <=2000;

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引

优化方法：可以将大的范围拆分成多个小范围

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;

PS：但是也有概率不走索引；

还原最初索引状态

ALTER TABLE `employees` DROP INDEX `idx_age`;

三、索引使用总结

like KK%相当于=常量
%KK和%KK% 相当于范围

---

四、覆盖索引

比如一个表就只有2个字段 id和name,id是主键索引，name是普通索引，普通索引里是包含索引字段和主键ID的.
当执行select * from 表时，他会扫描name这个普通索引，去普通索引里去找，然后发现*里面要的数据这里都包含了，就直接返回了，
如果表里还有其他字段，这时候二级索引并没有完整的结果集，他还会回表到聚集索引里面去找，这时候mysql觉得可能全表扫描更快一点；

---

五、mysql内部优化原则

凡事通过查找结果集，在所有索引都有，会优先查找二级索引，因为二级索引小，所以查找就快，因为主键索引是一个聚集索引，有所有信息，肯定比二级索引大.
既然要的数据二级索引里已经有了，所以这时候会选择二级索引；
mysql内部会有一些成本计算，比如发现全表扫描可能比用索引还快一点；

---

六、索引下推（Index Condition Pushdown，ICP）

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

上面的SQL-----------------like LiLei%其实就是用到了索引下推优化

6.1、什么是索引下推了？

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，上面这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

---

6.2、在MySQL5.6之前的版本

这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

---

6.3、MySQL 5.6引入了索引下推优化

可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

---

6.4、为什么范围查找Mysql没有用索引下推优化？

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 likeLilei% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。