MySql常用查询优化策略有哪些

共计 4973 个字符，预计需要花费 13 分钟才能阅读完成。

本篇内容介绍了“MySql 常用查询优化策略有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

查询优化

可以说，对于大多数系统来说，读多写少一定是常态，这就表示涉及到查询的 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 万条数据用于测试；

1、分页查询优化

分页查询是开发中经常会遇到的，有一种情况是，当分页的数量非常大的时候，查询的时候往往非常耗时，比如查询 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，可以看到响应时间有一定的提升；

2、关联查询优化

在实际的业务开发过程中，关联查询可以说随处可见，关联查询的优化核心思路是，最好为关联查询的字段添加索引，这是关键，具体到不同的场景，还需要具体分析，这个跟 mysql 的引擎在执行优化策略的方案选择时有一定关系；

2.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.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;

登录后复制

通过 explain 分析可以发现，左边的表走了全表扫描，可以考虑给左边的表的 tenant_name 和 user 表的 account 各自创建索引；

create index idx_name on tenant(tenant_name);

create index idx_account on `user`(account);

再次使用 explain 分析结果如下

可以看到第二行 type 变为 ref，rows 的数量优化比较明显。这是由左连接特性决定的，LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点, 一定需要建立索引。

2.3 内连接关联的字段建立索引

我们知道，左连接和右连接查询的数据分别是完全包含左表数据，完全包含右表数据，而内连接（inner join 或 join）则是取交集（共有的部分），在这种情况下，驱动表的选择是由 mysql 优化器自动选择的；

在上面的基础上，首先移除两张表的索引

ALTER TABLE `user` DROP INDEX idx_account;
ALTER TABLE `tenant` DROP INDEX idx_name;

使用 explain 语句进行分析

然后给 user 表的 account 字段添加索引，再次执行 explain 我们发现，user 表竟然被当作是被驱动表了；

此时，如果我们给 tenant 表的 tenant_name 加索引，并移除 user 表的 account 索引，得出的结果竟然都没有走索引，再次说明，使用内连接的情况下，查询优化器将会根据自己的判断进行选择；

3、子查询优化

子查询在日常编写业务的 SQL 时也是使用非常频繁的做法，不是说子查询不能用，而是当数据量超出一定的范围之后，子查询的性能下降是很明显的，关于这一点，本人在日常工作中深有体会；

比如下面这条 sql，由于 student 表数据量较大，执行起来耗时非常长，可以看到耗费了将近 3 秒；

select st.* from student st where st.classId in (select id from class where id   100);

登录后复制

通过执行 explain 进行分析得知，内层查询 id 100 的子查询尽管用上了主键索引，但是由于结果集太大，带入到外层查询，即作为 in 的条件时，查询优化器还是走了全表扫描；

针对上面的情况，可以考虑下面的优化方式

select st.id from student st join class cl on st.classId = cl.id where cl.id 100;

子查询性能低效的原因

子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后，再撤销这些临时表。这样会消耗过多的 CPU 和 IO 资源, 产生大量的慢查询；

子查询结果集存储的临时表，不论是内存临时表还是磁盘临时表都不能走索引，所以查询性能会受到一定的影响；

对于返回结果集比较大的子查询，其对查询性能的影响也就越大；

使用 mysql 查询时，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好，尽量不要使用 NOT IN 或者 NOT EXISTS，用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代；

一个真实的案例

在下面的这段 sql 中，优化前使用的是子查询，在一次生产问题的性能分析中，发现某个 tenant_id 下的数据达到了 35 万多，这样直接导致某个列表页面的接口查询耗时达到了 5 秒左右；

找到了问题的根源后，尝试使用上面的优化思路进行解决即可，优化后的 sql 大概如下，

4、排序（order by）优化

在 mysql，排序主要有两种方式

Using filesort : 通过表索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort
buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序；

Using index : 通过有序的索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高；

对于以上两种排序方式，Using index 的性能高，而 Using filesort 的性能低，我们在优化排序操作时，尽量要优化为 Using index

4.1 使用 age 字段进行排序

由于 age 字段未加索引，查询结果按照 age 排序的时候发现使用了 filesort，排序性能较低；

给 age 字段添加索引，再次使用 order by 时就走了索引；

4.2 使用多字段进行排序

通常在实际业务中，参与排序的字段往往不只一个，这时候，就可以对参与排序的多个字段创建联合索引；

如下根据 stuno 和 age 排序

给 stuno 和 age 添加联合索引

create index idx_stuno_age on `student`(stuno,age);

再次分析时结果如下，此时排序走了索引

关于多字段排序时的注意事项

1）排序时，需要满足最左前缀法则, 否则也会出现 filesort；

在上面我们创建的联合索引顺序是 stuno 和 age，即 stuno 在前面，而 age 在后，如果查询的时候调换排序顺序会怎样呢？通过分析结果发现，走了 filesort；

2）排序时，排序的类型保持一致

在保持字段排序顺序不变时，默认情况下，如果都按照升序或者降序时，order by 可以使用 index，如果一个是升序，另一个是降序会如何呢？分析发现，这种情况下也会走 filesort；

5、分组（group by）优化

group by 的优化策略和 order by 的优化策略非常像，主要列举如下几个要点：

group by 即使没有过滤条件用到索引，也可以直接使用索引；

group by 先排序再分组，遵照索引建的最佳左前缀法则；

当无法使用索引列时，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置；

where 效率高于 having，能写在 where 限定的条件就不要写在 having 中了；

减少使用 order by，能不排序就不排序，或将排序放到程序去做。Order by、groupby、distinct 这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的；

如果 sql 包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢；

5.1 给 group by 的字段添加索引

如果字段未加索引，分析结果如下，这种结果性能显然很低效

给 stuno 添加索引之后

给 stuno 和 age 添加联合索引

如果不遵循最佳左前缀，group by 性能将会比较低效

遵循最佳左前缀的情况如下

6、count 优化

count() 是一个聚合函数，对于返回的结果集，一行行判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值；

用法：count（*）、count（主键）、count（字段）、count（数字）

如下列举了 count 的几种写法的详细说明

用法说明 count（主键）InnoDB 会遍历整张表，把每一行的主键 id 值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加(主键不可能为 null)；count(*)InnoDB 不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加;count（字段）没有 not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null，计数累加，有 not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加；count（数字）InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加；

经验值总结

按照效率排序来看，count(字段) count(主键 id) count(1) ≈ count(*)，所以尽量使用 count(*)

“MySql 常用查询优化策略有哪些”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！