【八股消消乐】慢SQL优化手段总结

发布于：2025-05-14 ⋅ 阅读:(14) ⋅ 点赞:(0)

在这里插入图片描述

😊你好，我是小航，一个正在变秃、变强的文艺倾年。
🔔本专栏《八股消消乐》旨在记录个人所背的八股文，包括Java/Go开发、Vue开发、系统架构、大模型开发、机器学习、深度学习、力扣算法等相关知识点，期待与你一同探索、学习、进步，一起卷起来叭！

目录

题目

💬技术栈：SQL

🔍简历内容：熟悉常见慢SQL优化手段，熟悉Explain、Show Profile SQL分析。

🚩面试问：如何写出高性能SQL语句？

在这里插入图片描述

💡建议暂停思考10s，你有答案了嘛？如果你有不同题解，欢迎评论区留言、打卡。

答案

慢SQL

（1）无索引、索引失效导致慢查询；
（2）锁等待：常用的存储引擎有 InnoDB 和 MyISAM，前者支持行锁和表锁，后者只支持表锁。行锁更适合高并发场景，但在使用 InnoDB 存储引擎时，我们要特别注意行锁升级为表锁的可能，在批量更新操作时，行锁就很可能会升级为表锁。
（3）不恰当的 SQL 语句，例如<SELECT *>，<SELECT COUNT(*)> ，在大数据表中使用 <LIMIT M,N> 分页查询，以及对非索引字段进行排序等等。

慢 SQL 配置项

# 查询是否开启了记录慢 SQL 的功能
Show variables like 'slow_query%';
# 查询最大的执行时间
Show variables like 'long_query_time';
# 开启慢SQL记录
set global slow_query_log='ON'; // 开启慢 SQL 日志
set global slow_query_log_file='/var/lib/mysql/test-slow.log';// 记录日志地址
set global long_query_time=1;// 最大执行时间

优化SQL

EXPLAIN 分析

通过 SQL EXPLAIN 导出相应的执行计划如下：

在这里插入图片描述

  • id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。
  • select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
  • table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。
  • partitions：访问的分区表信息。
  • type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
    • system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。如果是 B + 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。
      在这里插入图片描述
    • eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
      在这里插入图片描述
    • ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。
      在这里插入图片描述
    • range：索引范围扫描，比如，<，>，between 等操作。
      在这里插入图片描述
    • index：索引全表扫描，此时遍历整个索引树。
      在这里插入图片描述
    • ALL：表示全表扫描，需要遍历全表来找到对应的行。
      在这里插入图片描述
  • possible_keys：可能使用到的索引。
  • key：实际使用到的索引。
  • key_len：当前使用的索引的长度。
  • ref：关联 id 等信息。
  • rows：查找到记录所扫描的行数。
  • filtered：查找到所需记录占总扫描记录数的比例。
  • Extra：额外的信息。

Show Profile分析

EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况。

如果我们想要深入到 MySQL 内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以选择 Profile。

Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
 
type 参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示 CPU 的相关开销信息 
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置 (行数) 
| SWAPS：显示 swap 交换次数的相关开销信息

MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能的，可以通过 select @@have_profiling 查询是否支持该功能。

在这里插入图片描述
最新的 MySQL 版本是默认开启 Show Profile 功能的，但在之前的旧版本中是默认关闭该功能的，你可以通过 set 语句在 Session 级别开启该功能：

在这里插入图片描述

Show Profiles 只显示最近发给服务器的 SQL 语句，默认情况下是记录最近已执行的 15 条记录，我们可以重新设置 profiling_history_size 增大该存储记录，最大值为 100。

在这里插入图片描述

获取到 Query_ID 之后，再通过 Show Profile for Query ID 语句，就能够查看到对应 Query_ID 的 SQL 语句在执行过程中线程的每个状态所消耗的时间了。

在这里插入图片描述
结果分析：SQL 语句在 Sending data 状态所消耗的时间最长，这是因为在该状态下，MySQL 线程开始读取数据并返回到客户端，此时有大量磁盘 I/O 操作。

SQL优化

（1）优化分页查询；

  • 通常做法：<LIMIT M,N> + 合适的 order by。
    • 在没有任何索引条件支持的情况下，需要做大量的文件排序操作（file sort）。
    • 如果有对应的索引，通常刚开始的分页查询效率会比较理想，但越往后，分页查询的性能就越差。例如 LIMIT 10000,10 这样的查询，数据库需要查询 10010 条记录，最后返回 10 条记录。但10000 条记录被查询出来没有被使用到。
  • 优化方式：利用子查询优化分页查询。

示例：模拟一张 10 万数量级的 order 表，进行分页查询。

select * from `demo`.`order` order by order_no limit 10000, 20;

通过 EXPLAIN 分析可知：该查询使用到了索引，扫描行数为 10020 行，但所用查询时间为 0.018s：
在这里插入图片描述
在这里插入图片描述
使用子查询的方式来实现分页查询：先查询出所需要的 20 行数据中的最小 ID 值，然后通过偏移量返回所需要的 20 行数据。

select * from `demo`.`order` where id> (select id from `demo`.`order` order by order_no limit 10000, 1)  limit 20;

通过 EXPLAIN 分析可知：子查询遍历索引的范围跟上一个查询差不多，而主查询扫描了更多的行数，但执行时间却减少了，只有 0.004s。

在这里插入图片描述
在这里插入图片描述

（2）优化 SELECT COUNT(*)；

COUNT() 函数在 MyISAM 和 InnoDB 存储引擎所执行的原理是不一样：

  • 在没有任何查询条件下的 COUNT()，MyISAM 的查询速度要明显快于 InnoDB。因为 MyISAM 存储引擎记录的是整个表的行数，COUNT() 查询操作时无需遍历表计算，直接获取该值即可。
  • 当带上 where 条件语句之后，MyISAM 跟 InnoDB 就没有区别了，它们都需要扫描表来进行行数的统计。
  • 优化方式：
    • 使用近似值【某些业务场景并不需要返回一个精确的 COUNT 值】
    • 增加汇总统计【新增一个汇总统计表或者缓存字段来统计需要的 COUNT 值】。

（3）优化 SELECT *；

  • 存储引擎：
    • InnoDB：默认创建主键时会创建主键索引，而主键索引属于聚族索引，即在存储数据时，索引是基于 B + 树构成的，具体的行数据则存储在叶子节点。
    • MyISAM：默认创建的主键索引、二级索引以及 InnoDB 的二级索引都属于非聚族索引，即在存储数据时，索引是基于 B + 树构成的，而叶子节点存储的是主键值。

示例SQL：InnoDB创建

# 先查询组合索引，通过组合索引获取到主键 ID，再通过主键 ID 去主键索引中获取对应行所有列的值。
select * from order where order_no='xxx’;
# 只会查询组合索引，通过组合索引获取到对应的 order_no 和 status 的值。
select order_no, status from order where order_no='xxx;

📌 [ 笔者 ]   文艺倾年
📃 [ 更新 ]   2025.5.12
❌ [ 勘误 ]   /* 暂无 */
📜 [ 声明 ]   由于作者水平有限，本文有错误和不准确之处在所难免，
              本人也很想知道这些错误，恳望读者批评指正！

在这里插入图片描述

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布