sql优化实战之全值匹配和范围查询
⼀、前提准备
创建⼀个employees表，⼀个主键索引，⼀个联合索引，表结构如下：
-- 创建表
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '⼊职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员⼯记录表';
-- 插⼊3条数据
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
使⽤存储过程插⼊10万条数据
-- 使⽤存储过程插⼊10w条数据
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zhangsan',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();
数据准备完毕  下⾯开始分析各种场景下的sql优化。
⼆、sql优化分析
sql优化主要针对索引的优化，⽬的是尽可能的使⽤索引进⾏查询检索，那么mysql是否⾛索引查询其实是由mysql优化器决定的，针对⼀条sql语句mysql优化器会计算各种情况的成本，从⽽选择如何使⽤索引甚⾄全表扫描。这种优化结果是不确定的，也就是说，针对同⼀条sql语句，可能第⼀次查询是⾛索引的，第⼆次查询就不⾛索引了。
总的来说mysql是否能够使⽤索引我们⼤概是可以预测的，但是预测的结果并不⼀定准确。
1.全值匹配
针对联合索引需要遵循最左前缀原则以及中间不可断
-- 联合索引使⽤联合索引的三个字段都⽤到了
explain select * from employees where name = 'LiLei' and age = 22 and position='dev';
结果：使⽤了联合索引的所有字段  ⾛了索引
-- 使⽤左边的两个索引字段
explain select * from employees where name = 'LiLei' and age = 22;
结果：使⽤了联合索引的name和age字段
-- 中间断开了联合索引的age字段
explain select * from employees where name = 'LiLei'  and position='dev';
结果：使⽤了联合索引的name字段，因为中间age断了 在age不确定的情况下  position是⽆序的  所以不会⽤到索引
-- 没有使⽤最左的name字段
explain select * from employees where age = 22 and position='dev';
结果：全表扫描，没有遵循最左前缀原则，即：name字段值不确定，age是⽆序的，⽆法⾛索引
2.范围查（‘>’,‘like’,‘in’,‘or’等）
-- 联合索引的第⼀个字段就使⽤范围查询
explain select * from employees where name > 'LiLei' and age = 22 and position='dev';
结果：不会⾛联合索引
分析：mysql判断出可能需要⾛idx_name_age_position索引，但是实际key=null，没有⾛索引。按理说这个sql是可以⾛第⼀个name字段索引的，但是mysql分析过后其实没有⾛name字段的。原因可能是：name使⽤的范围查，查询的结果集较⼤，⽽且查询出来后需要回表查询，效率不⾼，还没有进⾏全表扫描快。
-- 联合索引的第⼀个字段就使⽤范围查询
explain select * from employees where name > 'zzz' and age = 22 and position='dev';
这条sql与上⼀条⼏乎⼀模⼀样，就是name的值不同，看下结果：
显⽽易见，这条查询是⾛了的索引的，虽然只是⾛了联合索引的name字段。为什么呢？⾸先看⼀下和上⾯的⼀条sql的区别， 也就是name>'LiLei' 和 name > 'zzz'的区别，我们知道字符串的排序是按照ascall码排序的，LiLei明显⽐zzz靠前，zzz⼏乎是排在最后的，所以⼤于zzz的结果集⽐⼤于LiLei的结果集⼩的多，mysql计算这个回表成本⼩于全表扫描的成本，所以就⾛了索引
-- 使⽤覆盖索引
explain select id,name,age,position from employees where name > 'LiLei' and age = 22 and position='dev';
这条sql返回的结果不是全部字段，返回的id，name，age，position，其他的都没变，看下结果：
显⽽易见，也是⾛了索引的，为什么呢？⾸先回忆⼀下mysql的索引树，针对⼆级索引，我们查询的结果在联合索引的索引树上都有，因此直接遍历⼆级索引树就可获取到结果，⽆需回表操作，因此mysql选择使⽤⼆级索引。
也就是说，某些场景我们是可以使⽤覆盖索引（即遍历索引树就可以取到返回的结果字段）来进⾏优化的。
-- 联合索引的第⼆个字段使⽤范围查询
explain select * from employees where name = 'LiLei' and age > 22 and position='dev';
在联合索引的第⼀个字段确认情况下，第⼆个字段使⽤范围查，看下结果：
使⽤了索引，在name确认情况下，age是有序的所以⾛了name和age两个字段的索引。
-- 联合索引使⽤like查询
explain select * from employees where name like '%LiL%' and age = 22 and position='dev';
explain select * from employees where name like '%LiL' and age = 22 and position='dev';
explain select * from employees where name like 'LiL%' and age = 22 and position='dev';
第⼀条sql：
第⼆条sql：
第三条sql：
结果显⽰：前两条都是全表扫描，最后⼀条⾛了联合索引。
‘%’在前⾯的都不会⾛索引，因为字符串的前⾯部分不确定，对于索引来说是没办法确定顺序的，mysql分析 如果⾛联合索
引，‘%LiLei’或者‘%LiLei%’得到的结果集可能很⼤，⽽且没法根据name按照顺序进⾏定位，查到结果还要回表，效率不⼀定有全表扫描快。
‘%’在后⾯是⾛索引的，分析应该因为字符串的前⾯⼀部分已经确定了，每次定位都可以截取索引页中的name前⼏位进⾏匹配，是可以按照顺序进⾏查询的，⽽且字符串前缀确定了结果集⽐ >'LiLei'要⼩很多，所以回表效率也⽐较⾼，因此可以⾛索引。
其实这⾥⽤到了索引下推，什么是索引下推：
对于联合索引(name,age,position)，按照最左前缀原则，sql语句 select * from employees where name like 'LiL%' and age = 22 and position='dev'；按道理说应该只能⽤到name字段，在name字段值不确认情况下，age和position字段是不⾛索引的，但是实际情况是⾛索引的。
在Mysql5.6以前，是先⾛name索引，到结果集，然后在回表，通过⽐较age和position字段确认查的记录。
在Mysql5.6以后，是⾛name索引确定name的值，之后遍历⼆级索引树，根据age和position字段在过滤⼀遍，然后拿到结果集（过滤后的结果集会少很多）再去回表，这样提⾼回表时的效率。这就是索引下推。
针对in的查询操作
-- 针对表中10万条数据使⽤in查询
explain select * from employees where name in ('LiLei','zhangsan') and age = 22 and position='dev';
-- 针对表中3条数据使⽤in查询
explain select * from employees_copy where name in ('LiLei','zhangsan') and age = 22 and position='dev';
第⼀条结果：
第⼆条结果：
结果可知：同样的in操作，对于是否使⽤索引查询是不确定的，mysql会结合数据量各⽅⾯因素分析确认是否要⾛索引的，上⾯的例⼦显然在数据量⽐较多的时候使⽤了索引，数据量⽐较少的时候没有使⽤索引。数据量⽐较少时，⾛⼆级索引后还要回表操作，不⼀定有直接全表扫描快。数据量⽐较多时，⾛⼆级索引可以过滤掉⼤部分数据，回表时效率⽐全表扫描要快，毕竟数据量多的时候全表扫描是很慢的。
与in相⽐or的操作也是类似的
-- 针对表中10万条数据使⽤or查询
explain select * from employees where name ='LiLei' or name ='zhangsan' and age = 22 and position='dev';
-- 针对表中3条数据使⽤or查询
explain select * from employees_copy where name ='LiLei' or name ='zhangsan' and age = 22 and position='dev';
结果其实和in操作是⼀样的，原理也是类似的，这⾥就不贴出结果了。
总结：对于范围查，根据不同情况mysql会⾃⼰计算选择是否⾛索引，同样的sql并不是每次都⾛索引的。
针对于'>','<','>='等查询sql，使⽤索引⼀般分下⾯⼏种情况（但也不是100%⾛，这⾥说的是⼤部分场景）：
sql语句优化方式
1.在数据量少的时候
2.联合索引的第⼆个字段以及更后⾯的字段使⽤
3.查询的范围值排在很后⾯的时候，例如>'zzz' 或者 < 'aaa'
针对like查询，⼀般是‘%’号在后⾯的时候使⽤索引，但是这样的sql也有不⾛索引的情况：
例如：表中有10w条数据，其中有90%的数据name字段值为‘zhangsan’，这个时候 like 'zha%' and age = 22 and position='dev'就不会⾛索引，甚⾄索引下推都不会使⽤。因为结果集太⼤，筛选和回表效率不⾼，还不如直接全表扫描。
针对‘in’，‘or’查询，在某些情况下也不会⾛索引，例如：表中的数据很少，in中的值很多，⾛索引范围没有全表扫描效率⾼，所以⼀般不会⾛索引。
但是表中数据量很多的话，in⼀般就会⾛索引，⽐如有⼏万条数据，使⽤in查询⼏⼗条，那么就会⾛索引。
因此：mysql是否使⽤索引查询是不确定的，哪怕同⼀条sql在不同场景下是否⾛索引也是不⼀样的，对于主键索引使⽤主键查询，⼤部分都会⾛索引查询。对于⼆级索引，如果果集⽐结较⼩，那么mysql可能会选择⾛索引，如果结果集很⼤，那么mysql可能选择全表扫描。所以针对sql优化，是需要结合业务场景优化的，抛开业务场景优化sql并不⼀定能提⾼效率。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。