当前位置: 首页 > news >正文

三分钟了解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 这个网站随机生成了一批数据进行测验,可以看到,在没加索引的前提下,基本5万条数据后就会开始出现慢查询了(假设阈值为100ms)

数据量字段数量查询类型查询时间1000全表(ALL)约80ms50000全表(ALL)约120ms100000*全表(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
复制代码
这种场景常出现于排行榜中,因为排行榜经常需要按照 某个指标倒序 + 创建时间正序 排列。这种目前暂时无解,有解决办法的大佬望在评论区留言。
总结
总的来说,看完本文应该对慢查询有所了解了,慢查询优化是一个经久不衰的话题,场景也非常多元化,需要对索引的原理以及索引命中有一定了解,如有错漏,望大佬们在评论区留言。

相关文章:

  • C++优先队列——priority_queue,函数对象,labmda表达式,pair等
  • 记录pycharm配置Anaconda环境时没有反应的问题
  • 使用Kaggle API快速下载Kaggle数据集
  • [LeetCode][LCR187]破冰游戏——约瑟夫环
  • 支持MacOS苹果操作系统的网卡你用过吗?
  • JetBrains pycharm pro 2023 for mac Python集成开发环境
  • 【前端的讲解】
  • Redis 8种基本数据类型及常用命令和数据类型的应用场景
  • ETH网络中的账户
  • 自动驾驶消息传输机制-LCM
  • 2024数字中国创新大赛·数据要素赛道“能源大数据应用赛”正式上线!参赛指南请查收
  • [rust] 10 project, crate, mod, pub, use: 项目目录层级组织, 概念和实战
  • cesium拾取pick系列(拾取坐标和对象)
  • 音视频开发入门小知识
  • 数据挖掘-理解业务和数据(二)
  • 温振变送器为何被称为监测工频类设备故障的“利器”?
  • 【面试题】数组去重的五种方法(必会)
  • MySQL索引
  • JavaScript基础总结---重点
  • UnRaid设备共用其他UnRaid主UPS的详细设置方法
  • ESP32的MQTT AT固件烧录+STM32以ESP32的MQTT AT固件的AT指令连接EMQX下mqtt服务器实现消息订阅和发布
  • Python 多进程编程(一)Pool Manager in multiprocessing
  • 灰度变换 - 灰度切割(灰度级分层)+threshold函数
  • MyBatis 框架的思想及其第一次使用
  • 【Unity Shader】Unity中如何创建Cubemap?
  • 面试百问:项目上线后才发现bug怎么办?
  • C语言《文件版本通讯录》
  • 【无人机】基于EKF、UKF、PF、改进PF滤波算法的无人机航迹预测(Matlab代码实现)
  • 一篇文章让你搞懂Java中的静态代理和动态代理
  • ROS1云课-导航实践测评
  • React中路由的参数传递 - 路由的配置文件
  • (附源码)计算机毕业设计SSM基于web的健康饮食信息管理系统