文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

啥?我写的一条SQL让公司网站瘫痪了...

2024-12-14 04:57

关注

[[348260]]
图片来自 Pexels

其实远远不止,我经历过几次线上事故,有一次就是由一条 SQL 慢查询导致的。

[[348261]]

那次是一条 SQL 查询耗时达到 2-3 秒「没有命中索引,导致全表扫描」,由于是高频查询,并发一起来很快就把 DB 线程池打满了,导致大量查询请求堆积,DB 服务器 CPU 长时间 100%+,大量请求 timeout...

最终系统崩溃,老板登场!可见,团队如果对慢查询不引起足够的重视,风险是很大的。

经过那次事故我们老板就说了:谁的代码再出现类似事故,开发和部门领导一起走人,吓得一大堆领导心发慌,赶紧招了两位 DBA 同事🙂🙂🙂。

[[348262]]

慢查询,顾名思义,执行很慢的查询。有多慢?超过 long_query_time 参数设定的时间阈值(默认 10s),就被认为是慢的,是需要优化的。慢查询被记录在慢查询日志里。

慢查询日志默认是不开启的,如果你需要优化 SQL 语句,就可以开启这个功能,它可以让你很容易地知道哪些语句是需要优化的(想想一个 SQL 要 10s 就可怕)。好了,下面我们就一起来看看怎么处理慢查询。

慢查询配置

开启慢查询

MySQL 支持通过以下方式开启慢查询:

方式一:通过命令开启慢查询

步骤 1:查询 slow_query_log 查看是否已开启慢查询日志:

  1. show variables like '%slow_query_log%'
  1. mysql> show variables like '%slow_query_log%'
  2. +---------------------+-----------------------------------+ 
  3. | Variable_name       | Value                             | 
  4. +---------------------+-----------------------------------+ 
  5. | slow_query_log      | OFF                               | 
  6. | slow_query_log_file | /var/lib/mysql/localhost-slow.log | 
  7. +---------------------+-----------------------------------+ 
  8. rows in set (0.01 sec) 

步骤 2:开启慢查询命令:

  1. set global slow_query_log='ON'

步骤 3:指定记录慢查询日志 SQL 执行时间得阈值(long_query_time 单位:秒,默认 10 秒)。

如下我设置成了 1 秒,执行时间超过 1 秒的 SQL 将记录到慢查询日志中:

  1. set global long_query_time=1; 

步骤 4:查询 “慢查询日志文件存放位置”。

  1. show variables like '%slow_query_log_file%'
  1. mysql> show variables like '%slow_query_log_file%'
  2. +---------------------+-----------------------------------+ 
  3. | Variable_name       | Value                             | 
  4. +---------------------+-----------------------------------+ 
  5. | slow_query_log_file | /var/lib/mysql/localhost-slow.log | 
  6. +---------------------+-----------------------------------+ 
  7. 1 row in set (0.01 sec) 

slow_query_log_file 指定慢查询日志的存储路径及文件(默认和数据文件放一起)。

步骤 5:核对慢查询开启状态,需要退出当前 MySQL 终端,重新登录即可刷新。

配置了慢查询后,它会记录以下符合条件的 SQL:

方式二:通过配置 my.cnf(Windows 是 my.ini)系统文件开启(版本:MySQL 5.5 及以上)。

在 my.cnf 文件的 [mysqld] 下增加如下配置开启慢查询,如下图:

  1. # 开启慢查询功能 
  2. slow_query_log=ON 
  3. # 指定记录慢查询日志SQL执行时间得阈值 
  4. long_query_time=1 
  5. # 选填,默认数据文件路径 
  6. # slow_query_log_file=/var/lib/mysql/localhost-slow.log 

 

重启数据库后即持久化开启慢查询,查询验证如下:

  1. mysql> show variables like '%_query_%'
  2. +------------------------------+-----------------------------------+ 
  3. | Variable_name                | Value                             | 
  4. +------------------------------+-----------------------------------+ 
  5. | have_query_cache             | YES                               | 
  6. | long_query_time              | 1.000000                          | 
  7. | slow_query_log               | ON                                | 
  8. | slow_query_log_file          | /var/lib/mysql/localhost-slow.log | 
  9. +------------------------------+-----------------------------------+ 
  10. rows in set (0.01 sec) 

慢查询日志介绍

 

如上图,是执行时间超过 1 秒的 SQL 语句(测试):

Explain 分析慢查询 SQL

分析 MySQL 慢查询日志,利用 Explain 关键字可以模拟优化器执行 SQL 查询语句,来分析 SQL 慢查询语句。

下面我们的测试表是一张 137w 数据的 app 信息表,我们来举例分析一下。

SQL 示例如下:

  1. -- 1.185s 
  2. SELECT * from vio_basic_domain_info where app_name like '%翻译%' ; 

这是一条普通的模糊查询语句,查询耗时:1.185s,查到了 148 条数据。

我们用 Explain 分析结果如下表,根据表信息可知:该 SQL 没有用到字段 app_name 上的索引,查询类型是全表扫描,扫描行数 137w。

  1. mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%翻译%' ; 
  2. +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 
  3. | id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       | 
  4. +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 
  5. |  1 | SIMPLE      | vio_basic_domain_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1377809 |    11.11 | Using where | 
  6. +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 
  7. 1 row in set, 1 warning (0.00 sec) 

当这条 SQL 使用到索引时,SQL 如下:查询耗时:0.156s,查到 141 条数据:

  1. -- 0.156s 
  2. SELECT * from vio_basic_domain_info where app_name like '翻译%' ; 

Explain 分析结果如下表;根据表信息可知:该 SQL 用到了 idx_app_name 索引,查询类型是索引范围查询,扫描行数 141 行。

由于查询的列不全在索引中(select *),因此回表了一次,取了其他列的数据。

  1. mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '翻译%' ; 
  2. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 
  3. | id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 | 
  4. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 
  5. |  1 | SIMPLE      | vio_basic_domain_info | NULL       | range | idx_app_name  | idx_app_name | 515     | NULL |  141 |   100.00 | Using index condition | 
  6. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 
  7. 1 row in set, 1 warning (0.00 sec) 

当这条 SQL 使用到覆盖索引时,SQL 如下:查询耗时:0.091s,查到 141 条数据。

  1. -- 0.091s 
  2. SELECT app_name from vio_basic_domain_info where app_name like '翻译%' ; 

Explain 分析结果如下表;根据表信息可知:和上面的 SQL 一样使用到了索引,由于查询列就包含在索引列中,又省去了 0.06s 的回表时间。

  1. mysql> EXPLAIN SELECT app_name from vio_basic_domain_info where app_name like '翻译%' ; 
  2. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
  3. | id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    | 
  4. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
  5. |  1 | SIMPLE      | vio_basic_domain_info | NULL       | range | idx_app_name  | idx_app_name | 515     | NULL |  141 |   100.00 | Using where; Using index | 
  6. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
  7. 1 row in set, 1 warning (0.00 sec) 

那么是如何通过 EXPLAIN 解析结果分析 SQL 的呢?各列属性又代表着什么?一起往下看。

各列属性的简介

各列属性的简介如下:

以上标星的几类是我们优化慢查询时常用到的。

慢查询分析常用到的属性

①type

对表访问方式,表示 MySQL 在表中找到所需行的方式,又称“访问类型”。

存在的类型有:ALL、index、range、ref、eq_ref、const、system、NULL(从左到右,性能从低到高)。

介绍三个咱们天天见到的:

②key

key 列显示了 SQL 实际使用索引,通常是 possible_keys 列中的索引之一,MySQL 优化器一般会通过计算扫描行数来选择更适合的索引,如果没有选择索引,则返回 NULL。

当然,MySQL 优化器存在选择索引错误的情况,可以通过修改 SQL 强制MySQL“使用或忽视某个索引”:

强制使用一个索引:FORCE INDEX (index_name)、USE INDEX (index_name)。

强制忽略一个索引:IGNORE INDEX (index_name)。

③rows

rows 是 MySQL 估计为了找到所需的行而要读取(扫描)的行数,可能不精确。

④Extra

这一列显示一些额外信息,很重要。

Using index:查询的列被索引覆盖,并且 where 筛选条件是索引的是前导列,Extra 中为 Using index。意味着通过索引查找就能直接找到符合条件的数据,无须回表。

注:前导列一般指联合索引中的第一列或“前几列”,以及单列索引的情况;这里为了方便理解我统称为前导列。

Using where:说明 MySQL 服务器将在存储引擎检索行后再进行过滤;即没有用到索引,回表查询。

可能的原因:

Using temporary:这意味着 MySQL 在对查询结果排序时会使用一个临时表。

Using filesort:说明 MySQL 会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。

Using index condition:查询的列不全在索引中,where 条件中是一个前导列的范围。

Using where;Using index:查询的列被索引覆盖,并且 where 筛选条件是索引列之一,但不是索引的前导列或出现了其他影响直接使用索引的情况(如存在范围筛选条件等),Extra 中为 Using where;Using index,意味着无法直接通过索引查找来查询到符合条件的数据,影响并不大。

一些慢查询优化经验分享

优化 LIMIT 分页

在系统中需要分页的操作通常会使用 limit 加上偏移量的方法实现,同时加上合适的 order by 子句。

如果有对应的索引,通常效率会不错,否则 MySQL 需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候,例如可能是 limit 1000000,10 这样的查询。

这是 MySQL 需要查询 1000000 条然后只返回最后 10 条,前面的 1000000 条记录都将被舍弃,这样的代价很高,会造成慢查询。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描,而不是查询所有的列。

然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

对于下面的查询:

  1. -- 执行耗时:1.379s 
  2. SELECT * from vio_basic_domain_info LIMIT 1000000,10; 

Explain 分析结果:

  1. mysql> EXPLAIN SELECT * from vio_basic_domain_info LIMIT 1000000,10; 
  2. +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ 
  3. | id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra | 
  4. +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ 
  5. |  1 | SIMPLE      | vio_basic_domain_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1377809 |   100.00 | NULL  | 
  6. +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ 
  7. 1 row in set, 1 warning (0.00 sec) 

该语句存在的最大问题在于 limit M,N 中偏移量 M 太大,导致每次查询都要先从整个表中找到满足条件的前 M 条记录,之后舍弃这 M 条记录并从第 M+1 条记录开始再依次找到 N 条满足条件的记录。

如果表非常大,且筛选字段没有合适的索引,且 M 特别大那么这样的代价是非常高的。

那么如果我们下一次的查询能从前一次查询结束后标记的位置开始查找,找到满足条件的 10 条记录,并记下下一次查询应该开始的位置,以便于下一次查询能直接从该位置开始。

这样就不必每次查询都先从整个表中先找到满足条件的前 M 条记录,舍弃掉,再从 M+1 开始再找到 10 条满足条件的记录了。

处理分页慢查询的方式一般有以下几种:

思路一:构造覆盖索引

通过修改 SQL,使用上覆盖索引,比如我需要只查询表中的 app_name、createTime 等少量字段,那么我秩序在 app_name、createTime 字段设置联合索引,即可实现覆盖索引,无需全表扫描。

适用于查询列较少的场景,查询列数过多的不推荐,耗时:0.390s。

  1. mysql> EXPLAIN SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10; 
  2. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ 
  3. | id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra       | 
  4. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ 
  5. |  1 | SIMPLE      | vio_basic_domain_info | NULL       | index | NULL          | idx_app_name | 515     | NULL | 1377809 |   100.00 | Using index | 
  6. +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ 
  7. 1 row in set, 1 warning (0.00 sec) 

思路二:优化 offset

无法用上覆盖索引,那么重点是想办法快速过滤掉前 100w 条数据。我们可以利用自增主键有序的条件,先查询出第 1000001 条数据的 id 值,再往后查 10 行。

适用于主键 id 自增的场景,耗时:0.471s。

  1. SELECT * from vio_basic_domain_info where  
  2.   id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10; 

原理:先基于索引查询出第 1000001 条数据对应的主键 id 的值,然后直接通过该 id 的值直接查询该 id 后面的 10 条数据。

下方 EXPLAIN 分析结果中大家可以看到这条 SQL 的两步执行流程:

  1. mysql> EXPLAIN SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10; 
  2. +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  3. | id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       | 
  4. +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  5. |  1 | PRIMARY     | vio_basic_domain_info | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |      10 |   100.00 | Using where | 
  6. |  2 | SUBQUERY    | vio_basic_domain_info | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | Using index | 
  7. +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 
  8. rows in set, 1 warning (0.40 sec) 

方法三:“延迟关联”

耗时:0.439s,延迟关联适用于数量级较大的表。

SQL 如下:

  1. SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id); 

这里我们利用到了覆盖索引+延迟关联查询,相当于先只查询 id 列,利用覆盖索引快速查到该页的 10 条数据 id,然后再把返回的 10 条 id 拿到表中通过主键索引二次查询。(表数据增速快的情况对该方法影响较小)

  1. mysql> EXPLAIN SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id); 
  2. +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ 
  3. | id | select_type | table                 | partitions | type   | possible_keys | key     | key_len | ref      | rows    | filtered | Extra       | 
  4. +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ 
  5. |  1 | PRIMARY     |             | NULL       | ALL    | NULL          | NULL    | NULL    | NULL     | 1000010 |   100.00 | NULL        | 
  6. |  1 | PRIMARY     | vio_basic_domain_info | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | myNew.id |       1 |   100.00 | NULL        | 
  7. |  2 | DERIVED     | vio_basic_domain_info | NULL       | index  | NULL          | PRIMARY | 8       | NULL     | 1000010 |   100.00 | Using index | 
  8. +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ 
  9. rows in set, 1 warning (0.00 sec) 

排查索引没起作用的情况

①模糊查询尽量避免用通配符'%'开头,会导致数据库引擎放弃索引进行全表扫描

如下:

  1. SELECT * FROM t WHERE username LIKE '%陈%' 

优化方式:尽量在字段后面使用模糊查询。如下:

  1. SELECT * FROM t WHERE username LIKE '陈%' 

如果需求是要在前面使用模糊查询:

②尽量避免使用 not in,会导致引擎走全表扫描。建议用 not exists 代替

如下:

  1. -- 不走索引 
  2. SELECT * FROM t WHERE name not IN ('提莫','队长'); 
  3. -- 走索引 
  4. select * from t as t1 where not exists (select * from t as t2 where name IN ('提莫','队长'and t1.id = t2.id); 

③尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描

如下:

  1. SELECT * FROM t WHERE id = 1 OR id = 3 

优化方式:可以用 union 代替 or。如下:

  1. SELECT * FROM t WHERE id = 1 
  2.    UNION 
  3. SELECT * FROM t WHERE id = 3 

④尽量避免进行 null 值的判断,会导致数据库引擎放弃索引进行全表扫描

如下:

  1. SELECT * FROM t WHERE score IS NULL 

优化方式:可以给字段添加默认值 0,对 0 值进行判断。如下:

  1. SELECT * FROM t WHERE score = 0 

⑤尽量避免在 where 条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描

可以将表达式、函数操作移动到等号右侧。如下:

  1. -- 全表扫描 
  2. SELECT * FROM T WHERE score/10 = 9 
  3. -- 走索引 
  4. SELECT * FROM T WHERE score = 10*9 

⑥当数据量大时,避免使用 where 1=1 的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描

如下:

  1. SELECT username, age, sex FROM T WHERE 1=1 

优化方式:用代码拼装 SQL 时进行判断,没 where 条件就去掉 where,有 where 条件就加 and。

⑦查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。

如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。

⑧where 条件仅包含复合索引非前导列

如:复合(联合)索引包含 key_part1,key_part2,key_part3 三列,但 SQL 语句没有包含索引前置列"key_part1",按照 MySQL 联合索引的最左匹配原则,不会走联合索引。

  1. -- 不走索引 
  2. select col1 from table where key_part2=1 and key_part3=2 
  3. -- 走索引 
  4. select col1 from table where key_part1 =1 and key_part2=1 and key_part3=2 

⑨隐式类型转换造成不使用索引

如下 SQL 语句由于索引对列类型为 varchar,但给定的值为数值,涉及隐式类型转换,造成不能正确走索引。

  1. select col1 from table where col_varchar=123;  

结语

好了,通过这篇文章,希望你 Get 到了一些分析 MySQL 慢查询的方法和心得,如果你觉得这篇文章不错,记得分享给朋友或同事,让大家少踩点坑。

作者:陈哈哈

简介:MySQL 社区的非著名贡献者,善于白嫖知识;陪伴 MySQL 五年,致力于高性能 SQL、事务锁优化方面的研究;长路漫漫,希望通过自己的分享让大家少踩一些坑。我是陈哈哈,一个爱笑的程序员。

编辑:陶家龙

征稿:有投稿、寻求报道意向技术人请联络 editor@51cto.com

 【51CTO原创稿件,合作站点转载请注明原文作者和出处为51CTO.com】

来源:51CTO技术栈内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯