一站式排查优化慢SQL

引言

在我们后端日常开发测试中，常常会遇到一个SQL语句查询过慢而导致的整体接口响应速度变慢，用户体验不好。这时候如果确认接口变慢是因为慢SQL的原因，我们就需要对慢SQL进行排查优化。

识别慢SQL😵

一个项目的SQL语句往往庞大而冗杂，那我们想要知道是哪几个SQL导致我们对应的接口变慢，该如何排查呢？

一般常用以下方法进行排查

慢日志查询

开启数据库的慢查询日志功能，记录执行时间超过指定阈值的 SQL 语句。这是最常用的方法，可以帮助你快速找到需要优化的 SQL。

• MySQL: 设置 long_query_time 参数，并开启 slow_query_log。
• PostgreSQL: 设置 log_min_duration_statement 参数。
• SQL Server: 使用 SQL Server Profiler 或 Extended Events 监控执行时间长的查询

以MySQL为例，如何进行慢日志查询呢？

查看当前配置

首先，你需要查看当前的 long_query_time 和 slow_query_log 的配置。

SHOW VARIABLES LIKE 'long_query_time'; 
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';

• long_query_time：慢查询的阈值，单位为秒。默认值通常是 10 秒。
• slow_query_log：是否开启慢查询日志。ON 表示开启，OFF 表示关闭。
• slow_query_log_file：慢查询日志文件的路径

修改配置

你可以通过以下两种方式修改配置：

• 修改 MySQL 配置文件 (my.cnf 或 my.ini)：

  1. 找到 MySQL 的配置文件。在 Linux 系统中，通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf。在 Windows 系统中，通常位于 MySQL 安装目录下的 my.ini 文件。
  2. 使用文本编辑器打开配置文件，找到 [mysqld] 部分（如果没有，则添加该部分）。
  3. 在 [mysqld] 部分添加或修改以下配置：

  [mysqld]
  long_query_time = 1
  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/mysql-slow.log
  log_output = FILE

  • long_query_time：设置为你希望的慢查询阈值，例如 1 秒。

  • slow_query_log：设置为 1，表示开启慢查询日志。

  • slow_query_log_file：设置慢查询日志文件的路径。请确保 MySQL 用户对该目录有写入权限。

  • log_output：设置日志输出方式为文件。

  4. 保存配置文件，并重启 MySQL 服务，使配置生效。

• 使用 SET GLOBAL 命令动态修改：

  1. 使用具有 SUPER 权限的 MySQL 用户登录。
  2. 执行以下 SQL 语句：

  SET GLOBAL long_query_time = 1;
  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

  • 这些设置会立即生效，但 MySQL 服务重启后会失效。如果希望永久生效，仍然需要修改配置文件。

配置生效

• 如果修改了配置文件，需要重启 MySQL 服务：

  sudo systemctl restart mysql  # Linux (systemd)
  sudo service mysql restart    # Linux (SysVinit)
  net stop mysql                # Windows
  net start mysql               # Windows

• 如果使用了 SET GLOBAL 命令，不需要重启 MySQL 服务，配置会立即生效。

验证配置

重新执行以下 SQL 语句，确认配置是否生效：

SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';

分析慢查询日志

• 慢查询日志会记录执行时间超过 long_query_time 的 SQL 语句。

• 你可以使用文本编辑器打开慢查询日志文件，查看慢 SQL 语句。

• 也可以使用 mysqldumpslow 工具分析慢查询日志，找出执行频率最高的慢 SQL 语句。

  mysqldumpslow -s t -a /var/log/mysql/mysql-slow.log

  • -s t：按照查询时间排序。
  • -a：不将数字和字符串抽象化。

注意事项

• long_query_time 的值应该根据你的应用场景进行调整。如果你的应用对响应时间要求很高，可以将 long_query_time 设置为更小的值。
• 开启慢查询日志会增加 MySQL 的 I/O 负担，因此不建议在生产环境中长时间开启。可以在需要排查问题时临时开启，问题解决后及时关闭

分析慢SQL😯

当我们通过慢SQL日志了解到了哪几个SQL语句是慢SQL之后，就需要对其进行分析。以MySQL为例

获取执行计划：

使用 EXPLAIN 命令，查看 SQL 语句的执行计划。执行计划可以告诉你数据库是如何执行 SQL 语句的，包括使用了哪些索引、表连接方式等。

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-01-31';

分析执行计划：

获取执行计划之后，我们需要通过几个核心参数了解具体SQL语句执行情况。

• possible_keys: 该SQL语句可能会用到的索引

• key: 该SQL语句实际上用到的索引

• type: 执行该SQL语句的扫描方式

其中type我们尤其需要注意，扫描方式效率从低到高依次为ALL，index，range，ref，eq_ref,const

这个参数最好在range及以上，效率越高越好

优化慢SQL🤩

• 索引优化：

  • 添加索引： 为经常用于查询的字段添加索引。
  • 复合索引： 考虑使用复合索引，可以提高多字段查询的性能。
  • 覆盖索引： 尽量使用覆盖索引，避免回表查询。
  • 删除无用索引： 删除不再使用的索引，减少索引维护的开销。
  • 防止索引失效： 正确使用索引，防止索引失效

• SQL 语句优化：

  • 避免使用 SELECT *： 只查询需要的字段，减少 I/O 开销。
  • 避免在 WHERE 子句中使用函数： 函数会导致索引失效。
  • 使用 JOIN 代替子查询： JOIN 通常比子查询更有效率。
  • 优化 LIKE 查询： 避免使用 LIKE '%keyword%'，可以使用全文索引或倒排索引。
  • 使用批量操作代替单条操作： 批量插入、更新、删除操作可以减少网络开销。
  • 避免在循环中执行 SQL 语句： 将循环中的 SQL 语句合并成一条 SQL 语句。

• 数据库配置优化：

  • 调整缓冲区大小： 调整数据库的缓冲区大小，可以提高数据访问速度。
  • 调整连接池大小： 调整数据库连接池的大小，可以提高并发处理能力。
  • 优化操作系统参数： 优化操作系统的 TCP 参数、I/O 参数等，可以提高数据库的整体性能。

示例😍

给出一个用复合索引来优化慢SQL的例子：

假设你发现以下 SQL 语句执行很慢

SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-01-31';

1. 获取执行计划： 使用 EXPLAIN 命令查看执行计划，发现没有使用索引。
2. 分析执行计划： 发现全表扫描了 orders 表。
3. 优化： 为 customer_id 和 order_date 字段添加复合索引：

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

验证： 再次执行 SQL 语句，并查看执行计划，发现使用了索引，执行速度明显提高

总结❤️

这是后端面试常问的问题，建议各位结合自己的项目进行回答，面试官问你的优化方法也可以有更多的思路。如果你看了这篇文章有收获可以点赞+关注+收藏🤩，这是对笔者更新的最大鼓励！如果你有更多方案或者文章中有错漏之处，请在评论区提出帮助笔者勘误，祝你拿到更好的offer！