一、前言
本文基于mysql8.0的innodb测试,建表在做对应的优化策略测试时记得加索引,由于文中太多查询例子不一一针对建立索引了,只挑几个建索引举例。
CREATE TABLE `user` (
`id`int(11) NOT NULL,
`name`varchar(20) DEFAULT NULL,
`sex`varchar(5) DEFAULT NULL,
`address`varchar(255) DEFAULT NULL,
`birthday`timestamp(6) NULL DEFAULT NULL,PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二、优化策略
下面演示均对过滤列字段建立单索引不一一建立了,联合索引我会特别建立。
1.不要在索引列上加函数或运算
--全表扫描
explain select * from user where year(birthday) < 2020
--走索引
explain select * from user where birthday < '2020-03-17'
--全表扫描
explain select * from user where id + 1 = 2
--走索引
explain select * from user where name = 'lihua'
2.隐式类型转换
以address字段举例,address为varchar类型,我们为其建立索引
--全表扫描
explain select * from user where address = 2
--走索引
explain select * from user where address = '2'
通过对比可以发现第一次没有使用索引,第二次是使用了索引idx_address的,为什么会这样呢?因为第一种发生了隐式转换,即:
explain select * from user where address = 2
--等价于
explain select * from user where CAST(address AS signed int) = 2
隐式转换在索引字段上做了函数处理,因此会全表扫描。
3.前导模糊查询不会使用索引
--全表扫描
explain select * from user where name like '%li'
--使用索引
explain select * from user where name like 'li%'
对比结果可以发现非前导模糊查询可以使用索引(%li%也不能使用索引,不做单独演示了)
4.联合索引最左匹配原则
mysql从左到右匹配,知道遇到范围查询(>、
select * from user where name = 'lihua' and sex = 'm' and birthday < '2020-03-17' and address = '1'
建立(`name`, `sex`, `birthday`, `address`)顺序的索引,address使用不到索引的,而建立(`name`, `sex`, `address`, `birthday`)顺序的索引,则都可以使用到索引。
5.查询时=可以乱序
--可以乱序
select * from user where name = 'lihua' and sex = 'm'
select * from user where sex = 'm'and name = 'lihua'
mysql查询时会将查询顺序优化成和联合索引顺序一致。
6.避免filesort排序
mysql中无法利用索引完成的排序称为“文件排序”,执行计划Extra中若出现Using filesort, 说明mysql会对数据使用一个外部的索引排序,而不是按照表内索引顺序进行读取,最左匹配原则也适用于排序,我们建立(`age`, `name`, `sex`)的联合索引,看下面具体5个例子及执行结果:
--不带过滤条件
explain select * from user order byage
--排序使用索引
explain select * from user where age = 22 order by age
--最左匹配原则,排序条件未走索引,文件排序
explain select * from user where age > 22 order by name
--当前筛选条件与排序条件使用的字段顺序与索引不一致,文件排序
explain select * from user where age = 22 order by sex,name
--排序字段方向一个升序一个降序,文件排序
explain select * from user where age = 22 order by name asc,sex desc
7.union、in、or均可命中索引
--使用索引
explainselect name from user where name = 'lihua'
union all
select name from user where name = 'limei'
--使用索引
explain select name from user where name in ('lihua','limei')--使用索引
explain select name from user where name = 'lihua' or name = 'limei'
三个关键字使用后的查询效率比较:
1)对于索引列来说,最好使用union all因为复杂的查询(包含运算等),将使or、in放弃索引而全表扫描,除非你能确定or、in会使用索引;
2)对于只有非索引字段来说,就老是用or、in,因为非索引字段本来就要全表扫描union all只会成倍数增加表扫描次数;
3)对于非索引字段及索引字段(索引字段有效)都有的情况来说,union all、or、in都可以理论上;
8.负向条件不会使用索引(不绝对,有时会走范围索引,取决于范围大小)
负向条件有!=、<>、not in、not like、not exists等,多数介绍sql优化的文章都会提到避免使用!=,因为不走索引,我们用实例验证一下,只对name建立但索引,库中数据只有三条,此时使用!=,查看执行计划:
--使用范围索引
explain select * from user where name != 'lihua'
观察执行计划,可以发现!=确确实实是走了name的范围索引的,分析原因实际应用中很可能是因为不等于的数据占比很高,走索引不如全表扫描效率高。
9.分页查询优化
mysql的分页并不是跳过offset行,而是取offset+n行,然后放弃前offset行取后面n行,当offset很大时效率就很低,利用覆盖索引,避开回表
解决方案一:书签,记录上次访问位置,下次直接从书签位置开始
select id from table limit 10000 20
--改成
select id from table where id>10000 limit 20
解决方案二:关联(或者join),根据覆盖索引查询需要的主键,再根据主键关联原表获得需要的数据
select id from table,
(select id from table limit 10000,20) tmp where table.id = tmp.id
查询所有数据
select * from table
where id> =(select id from table limit 10000, 1) limit 20
select *from table,
(select id from table limit 10000,20) tmp where table.id = tmp.id
一个关于limit的小经验:当使用limit时不使用order by ,查询id走的是索引,按索引存储位置取数据,*是查全表按表记录位置取结果,所以得出结论直接select index from table查询,不管是单索引还是组合索引都会返回索引位置的数据,如果select中包含其他非索引列就会返回顺序记录结果。
三、结语
1.本文测试均基于mysql8.0innodb,不同版本可能有所不同,实际开发中还是要具体问题具体分析多查看执行计划,一切以提升效率为前提不用过于在意条条框框,毕竟索引优化也是为了效率服务,本文主要记录mysql学习过程,如有错误请指正,一起学习一起进步。
2.本文中提到的覆盖索引,回表可以查看 https://www.cnblogs.com/ghoster/p/12509611.html
还有建立单索引还是联合索引问题,推荐看这篇博客 https://blog.csdn.net/Abysscarry/article/details/80792876