深入了解MySQL中的慢查询
来源:脚本之家
时间:2023-01-07 11:47:52 490浏览 收藏
本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的,希望这篇《深入了解MySQL中的慢查询》对你有很大帮助!欢迎收藏,分享给更多的需要的朋友学习~
一、什么是慢查询
什么是MySQL慢查询呢?其实就是查询的SQL语句耗费较长的时间。
具体耗费多久算慢查询呢?这其实因人而异,有些公司慢查询的阈值是100ms,有些的阈值可能是500ms,即查询的时间超过这个阈值即视为慢查询。
正常情况下,MySQL是不会自动开启慢查询的,且如果开启的话默认阈值是10秒
# slow_query_log 表示是否开启 mysql> show global variables like '%slow_query_log%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/0bd9099fc77f-slow.log | +---------------------+--------------------------------------+ # long_query_time 表示慢查询的阈值,默认10秒 show global variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+
二、慢查询的危害
既然我们这么关注慢查询,那它肯定是有一些不好的地方,常见的有这几个:
1、用户体验差。
我们访问一个东西,或者保存一个东西,都得等好久,那不得分分钟弃坑?等等,我知道体验是会差,但慢查询的阈值设置为100ms似不似太低了,我访问一个东西1-2秒应该也能接受吧。其实这个阈值并不算太低,因为这是一条SQL的阈值,而你一个接口可能要查好几次SQL,甚至调下外部接口都是很常见的。
2、占用MySQL内存,影响性能
MySQL内存本来就是有限的(大内存要加钱!),SQL为什么查询慢呢?有时候就是因为你全表扫导致查询的数据量很多,再加上各种筛选就变慢了,所以慢查询往往也会意味着内存占用的增高,内存一高,能够承载的SQL查询就变少了,性能也变差了。
3、造成DDL操作阻塞
众所周知,InnoDB引擎默认加的是行锁,但锁其实都是加在索引上的,如果筛选条件没有建立索引,会降级到表锁。而慢查询有一大部分原因都是因为没加索引导致的,所以慢查询时间过长,就会导致表锁的时间也很长,如果这时候执行DDL就会造成阻塞。
三、慢查询常见场景
既然慢查询造成的问题这么多,那一般什么场景下会出现慢查询呢?
1、没加索引/没利用好索引
在没加索引的情况,就会造成全表扫描;又或者没走到索引(或者走的不是最优索引),这两张情况都会导致扫描行数增多,从而查询时间变慢。
下面是我测试的一个例子:
# 这是我的表结构,算是一种比较常规的表 create table t_user_article ( id bigint unsigned auto_increment primary key, cid tinyint(2) default 0 not null comment 'id', title varchar(100) not null, author varchar(15) not null, content text not null, keywords varchar(255) not null, description varchar(255) not null, is_show tinyint(1) default 1 not null comment ' 1 0', is_delete tinyint(1) default 0 not null comment ' 1 0', is_top tinyint(1) default 0 not null comment ' 1 0', is_original tinyint(1) default 1 not null, click int(10) default 0 not null, created_at timestamp default CURRENT_TIMESTAMP not null, updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP ) collate = utf8mb4_unicode_ci;
在上述表结构下,我通过 [Fill Database](https://filldb.info/)
这个网站随机生成了一批数据进行测验,可以看到,在没加索引的前提下,基本5万条数据后就会开始出现慢查询了(假设阈值为100ms)
数据量 | 字段数量 | 查询类型 | 查询时间 |
---|---|---|---|
1000 | * | 全表(ALL) | 约80ms |
50000 | * | 全表(ALL) | 约120ms |
100000 | * | 全表(ALL) | 约180ms |
2、单表数据量太大
如果本身单表数据量太大,可能超千万,或者达到亿级别,可能加了索引之后,个别查询还是存在慢查询的情况,这种貌似没啥好办法,要么就看索引设置得到底对不对,要么就只能分表了。
3、Limit 深分页
深分页的意思就是从比较后面的位置开始进行分页,比如每页有10条,然后我要看第十万页的数据,这时候的分页就会比较“深”
还是上面的 t_user_article
表,你可能会遇到这样的一条深分页查询:
-- 个人测试: 106000条数据,耗时约 150ms select * from t_user_article where click > 0 order by id limit 100000, 10;
在这种情况下,即使你的 click
字段加了索引,查询速度可能还是很慢(测试后和不加差不多),因为二级索引树存的是主键ID,查到数据还需要进行回表才能决定是否丢弃,像上面的查询,回表的次数就达到了100010次,可想而知速度是非常慢的。
结合上面的分析,目前的解决思路都是先查出主键字段(id),避免回表,再根据主键查出所有字段。
第一种,延迟关联,此时SQL变为:
-- 个人测试: 106000条数据,耗时约 90ms select * from t_user_article t1, (select id from t_user_article where click > 0 order by id limit 100000, 10) t2 WHERE t1.id = t2.id;
第二种,分开查询,分开查询的意思就是分两次查,此时SQL变为:
-- 个人测试: 106000条数据,耗时约 80ms select id from t_user_article where click > 0 order by id limit 100000, 10; -- 个人测试: 106000条数据,耗时约 80ms select * from t_user_article where id in (上述查询得到的ID)
大家可能会很疑惑,为什么要分开查呢,毕竟分开查可能最终耗时比一次查询还要高!这是因为有些公司(比如我司)可能只对单条SQL的查询时长有要求,但对整体的并没有要求,这时候这种办法就能达到一个折中的效果。
另外,大家在网上可能会看到利用子查询解决的办法,比如改成这样:
select * from t_user_article where id in (select id from t_user_article where click > 0 limit 100000, 10)
但这时候执行你会发现抛出一个错误: “This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery’”,翻译过来就是子查询不支持Limit,解决办法也很简单,多嵌套一层即可:
-- 个人测试: 106000条数据,耗时约 200ms select * from t_user_article where id in (select t.id from (select id from t_user_article where click > 0 order by id limit 100000, 10) as t)
但问题是测试后发现耗时反而变长了,所以并没有列举为一种解决办法。
4、使用FileSort查询
什么是FileSort
查询呢?其实就是当你使用 order by
关键字时,如果待排序的内容不能由所使用的索引直接完成,MySQL就有可能会进行FileSort
。
当查询的数据较少,没有超过系统变量 sort_buffer_size
设定的大小,则直接在内存进行排序(快排);如果超过该变量设定的大小,则会利用文件进行排序(归并)。
FileSort出现的场景主要有以下两种:
4.1 排序字段没加索引
# click 字段此时未加索引 explain select id, click from t_user_article where click > 0 order by click limit 10; # explain 结果: type:ALL Extra:Using where; Using filesort
解决办法就是在 click 字段上加索引。
4.2 使用两个字段排序,但是排序规则不同,一个正序,一个倒序
# click 字段此时已加索引 explain select id, click from t_user_article where click > 0 order by click desc, id asc limit 10; # explain 结果: type:range Extra:Using where; Using index; Using filesort
这种场景常出现于排行榜中,因为排行榜经常需要按照 某个指标倒序 + 创建时间正序 排列。这种目前暂时无解,有解决办法的大佬望在评论区留言。
总结
总的来说,看完本文应该对慢查询有所了解了,慢查询优化是一个经久不衰的话题,场景也非常多元化,需要对索引的原理以及索引命中有一定了解。
文中关于mysql的知识介绍,希望对你的学习有所帮助!若是受益匪浅,那就动动鼠标收藏这篇《深入了解MySQL中的慢查询》文章吧,也可关注golang学习网公众号了解相关技术文章。
-
490 收藏
-
325 收藏
-
453 收藏
-
365 收藏
-
245 收藏
-
483 收藏
-
338 收藏
-
308 收藏
-
133 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 507次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习
-
- 无辜的背包
- 这篇技术贴真及时,很详细,赞 👍👍,已加入收藏夹了,关注作者大大了!希望作者大大能多写数据库相关的文章。
- 2023-03-18 14:40:00
-
- 激情的玫瑰
- 这篇技术文章太及时了,很详细,很好,收藏了,关注up主了!希望up主能多写数据库相关的文章。
- 2023-03-06 02:58:43
-
- 合适的红牛
- 太给力了,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,帮助很大,总算是懂了,感谢老哥分享文章内容!
- 2023-02-16 20:16:37
-
- 健忘的微笑
- 细节满满,已收藏,感谢老哥的这篇文章内容,我会继续支持!
- 2023-02-06 06:03:19
-
- 畅快的猎豹
- 赞 👍👍,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,看完之后很有帮助,总算是懂了,感谢师傅分享技术文章!
- 2023-02-01 22:50:50
-
- 称心的楼房
- 这篇技术文章真及时,细节满满,太给力了,收藏了,关注博主了!希望博主能多写数据库相关的文章。
- 2023-01-16 02:53:00
-
- 细心的自行车
- 很有用,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,帮助很大,总算是懂了,感谢师傅分享技术贴!
- 2023-01-09 04:06:59