实用性极强的MySQL查询优化策略 _MySQL

前言
在程序上线运行一段时间后，一旦数据量上去了，或多或少会感觉到系统出现延迟、卡顿等现象，出现这种问题，就需要程序员或架构师进行系统调优工作了。
其中，大量的实践经验表明，调优的手段尽管有很多，但涉及到SQL调优的内容仍然是非常重要的一环，本文将结合实例，总结一些工作中可能涉及到的SQL优化策略。
一、查询优化
可以说，对于大多数系统来说，读多写少一定是常态，这就表示涉及到查询的SQL是非常高频的操作。
前置准备：给一张测试表添加10万条数据。
使用下面的存储过程给单表造一批数据，将表换成自己的就好了。
create procedure addMyData()
begin
declare num int;
set num =1;
while num <= 100000 do
insert into XXX_table values(
replace(uuid(),'-',''),concat('测试',num),concat('cs',num),'123456'
);
set num =num +1;
end while;
end ;
然后调用该存储过程：
call addMyData();
本篇准备了3张表，分别为学生（student）表，班级（class）表，账户(account)表，各自有50万， 1万和10万条数据用于测试。

文章插图

文章插图
二、分页查询优化
分页查询是开发中经常会遇到的，有一种情况是，当分页的数量非常大的时候，查询的时候往往非常耗时，比如查询student表，使用下面的sql查询，耗时达到0.2秒。

文章插图
实践经验告诉我们，越往后，分页查询效率越低，这就是分页查询的问题所在，因为，当在进行分页查询时，如果执行 limit 400000,10 ，此时需要 MySQL 排序前4000 10 记录，仅仅返回400000 - 4 00010 的记录，其他记录丢弃，查询排序的代价非常大。
优化思路：
一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
1.在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容
SELECT * FROM student t1,(SELECT id FROM student ORDER BY id LIMIT 400000,10) t2 WHERE t1.id =t2.id;
执行上面的sql，可以看到响应时间有一定的提升。

文章插图
2.对于主键自增的表，可以把Limit 查询转换成某个位置的查询
select * from student where id > 400000 limit 10;
执行上面的sql ，可以看到响应时间有一定的提升。
三、关联查询优化
在实际的业务开发过程中，关联查询可以说随处可见，关联查询的优化核心思路是，最好为关联查询的字段添加索引，这是关键。具体到不同的场景，还需要具体分析，这个跟mysql的引擎在执行优化策略的方案选择时有一定关系。
1.左连接或右连接
下面是一个使用left join 的查询，可以预想到这条sql查询的结果集非常大。
select t.* from student t left join class cs on t.classId = cs.id;
为了检查下sql的执行效率，使用explAIn做一下分析，可以看到，第一张表即left join左边的表student走了全表扫描，而class表走了主键索引，尽管结果集较大，还是走了索引。

文章插图
针对这种场景的查询，思路如下：

让查询的字段尽量包含在主键索引或者覆盖索引中；
查询的时候尽量使用分页查询。

文章插图
关于左连接（右连接）的explain结果补充说明：

左连接左边的表一般为驱动表，右边的表为被驱动表；
尽可能让数据集小的表作为驱动表，减少mysql内部循环的次数；
两表关联时， explain结果展示中，第一栏一般为驱动表。

2.关联查询关联的字段建立索引
看下面的这条sql ，其关联字段非表的主键，而是普通的字段。
explain select u.* from tenant t left join `user` u on u.account = t.tenant_name where t.removed is null and u.removed is null;