慢查询与MySQL语句优化

慢查询是指执行时间超过预设阈值（通常为1秒）的SQL语句，其危害体现在三个方面：

• 资源占用：长时间运行的查询会持续占用CPU、内存和I/O资源，导致数据库整体吞吐量下降。
• 连接阻塞：在事务型应用中，慢查询可能长时间持有锁资源，引发其他事务的等待超时。
• 用户体验：前端应用因等待数据库响应而出现卡顿，直接影响业务转化率。

开启慢查询日志

通过slow_query_log参数可开启慢查询日志，配合long_query_time参数设置阈值。建议生产环境将阈值设为0.5秒，开发环境设为0.1秒以便更早发现问题。

查看慢查询配置

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

临时开启慢查询日志

-- 以下写法等价，都是在 MySQL 命令行中执行
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log = ON;


SET GLOBAL long_query_time = 1;  -- 超过1秒记录（生产建议0.5-1秒）
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

这种开启方式适合临时调试、排查问题。立即生效，无需重启，但MySQL 重启后恢复为配置文件中的值。

永久开启慢查询日志（my.cnf）

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

注意：在配置文件中 1/ON/TRUE 都表示开启，但推荐用 1（最通用）。

这种开启方式适合长期配置。修改后不会立即生效，需要重启 MySQL，但每次 MySQL 启动都会读取，永久生效。

slow_query_log_file 配置

slow_query_log_file 路径必须满足的条件：

1. MySQL 进程必须有写入权限

# MySQL 通常以 mysql 用户运行                                                                                                                                           
# 查看 MySQL 运行用户
ps aux | grep mysqld                                                                                                                                                    
# 输出类似: mysql  12345 ... /usr/sbin/mysqld                   
                                                                                                                                                                          
# 目录必须让 mysql 用户可写                                     
sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

常见错误：设置到 /root/slow.log，mysql 用户没有权限写入。

2. 目录必须已存在

MySQL 不会自动创建目录，只会自动创建文件。

# ❌ 错误：如果 /data/logs/ 目录不存在，MySQL 启动失败
slow_query_log_file = /data/logs/slow.log

# ✅ 正确：先确保目录存在
sudo mkdir -p /data/logs
sudo chown mysql:mysql /data/logs

3. 路径必须是绝对路径

# ✅ 推荐：绝对路径
slow_query_log_file = /var/log/mysql/slow.log                                                                                                                           
                                                                  
# ⚠️  相对路径：会相对于 datadir 目录
slow_query_log_file = slow.log
# 实际位置 = datadir/slow.log（如 /var/lib/mysql/slow.log）

设置失败排查

-- 尝试设置
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 如果报错，检查：

# 1. 目录是否存在
ls -ld /var/log/mysql/

# 2. 权限是否正确
ls -la /var/log/mysql/
# 应该是 mysql:mysql 所有

分析慢查询日志

慢查询日志通常位于你指定的位置（如上述配置中的/var/log/mysql/slow.log）。你可以使用以下几种方法来分析这些日志：

方法1：使用mysqldumpslow工具

MySQL提供了一个名为mysqldumpslow的工具，用于分析和汇总慢查询日志。可以通过以下命令使用它：

mysqldumpslow /var/log/mysql/mysql-slow.log

这个工具会显示最慢的查询及其出现次数。你可以通过添加参数来定制输出，例如：

mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log  # 显示出现次数最多的前10个查询

方法2：手动分析日志文件

也可以直接查看慢查询日志文件，找到执行时间最长的查询。通常，这些查询会以# Time: <timestamp>开头的行开始。例如：

# Time: 2023-04-01T10:00:01.234567Z
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 2.345678  Lock_time: 0.123456 Rows_sent: 1  Rows_examined: 1000
SET timestamp=1585712401;
SELECT * FROM large_table WHERE some_column = 'some_value';

优化慢查询

一旦识别出了性能瓶颈的查询，可以尝试以下几种方法来优化它们：

• ‌添加索引‌：对查询中频繁用于WHERE子句的列添加索引。
• ‌优化查询逻辑‌：减少SELECT子句中的字段数量，只选择必要的字段。
• ‌使用EXPLAIN分析‌：使用EXPLAIN关键字来查看MySQL如何执行你的查询，找出是否有更优的执行计划。
• ‌缓存结果‌：对于不经常变化且需要频繁访问的数据，考虑使用缓存技术。
• ‌分区表‌：如果表非常大，考虑使用分区来提高查询效率。

1. 索引优化策略

覆盖索引：让查询只需通过索引就能获取所需数据

-- 原查询（需要回表取 name 字段）
SELECT name FROM orders WHERE user_id = 1 AND status = 2;
-- 优化：创建包含 name 的覆盖索引
CREATE INDEX idx_user_status_name ON orders(user_id, status, name);

索引合并优化：对多列条件使用索引合并

-- 创建单列索引
ALTER TABLE products ADD INDEX idx_category (category_id);
ALTER TABLE products ADD INDEX idx_brand (brand_id);
-- 优化器可能使用index_merge策略
SELECT * FROM products WHERE category_id = 5 OR brand_id = 10;

索引列顺序优化：把等值查询的列放前面，范围查询的列放后面

-- 查询：WHERE status = 1 AND created_at > '2024-01-01'
CREATE INDEX idx_status_time ON orders(status, created_at);  -- ✅
CREATE INDEX idx_time_status ON orders(created_at, status);  -- ❌ status走不到索引

2. 执行计划分析

使用EXPLAIN命令获取执行计划，重点关注：

• type列：访问类型（const > eq_ref > ref > range > index > ALL）
• key列：实际使用的索引
• rows列：预估需要检查的行数
• Extra列：额外信息（Using filesort, Using temporary）

3. SQL改写技巧

避免SELECT *：明确指定所需字段

-- ❌ 取出所有列，增加网络传输量，可能破坏覆盖索引
SELECT * FROM students WHERE class = '高三1班';
-- ✅ 只取需要的列
SELECT id, name, score FROM students WHERE class = '高三1班';

分页优化（深分页问题）

-- ❌ 深分页：LIMIT 100000, 10 需要扫描前 100010 行
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- ✅ 延迟关联：先通过索引找ID，再JOIN取数据
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) t ON o.id = t.id;
-- ✅ 游标分页（上次查询的最后一个ID）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

JOIN 驱动表选择：小表驱动大表（MySQL 优化器通常会自动选择），被驱动表的关联字段必须有索引！

SELECT s.name, c.name
FROM students s   -- 小表（驱动表）
JOIN scores sc ON s.id = sc.student_id;  -- sc.student_id 要有索引

4. 数据库参数调优

关键参数配置建议：

• innodb_buffer_pool_size：设为物理内存的50-70%
• query_cache_size：MySQL 8.0已移除，5.7及之前版本建议设为64M-256M
• tmp_table_size/max_heap_table_size：控制内存表大小，防止转为磁盘表
• sort_buffer_size：排序操作缓冲区，默认256K-2M