mysql索引优化策略有哪些

一、前言

　　本文基于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从左到右匹配，知道遇到范围查询（>、<、between、like）就停止匹配（这个停止匹配是指不会在接着向右匹配，当前范围查询还是可以匹配到的），举个例：

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 by age

-- 排序使用索引
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均可命中索引

-- 使用索引
explain 
select 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