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从左到右匹配，知道遇到范围查询(>、

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