慢SQL优化方法与思路总结

慢SQL优化方法与思路总结

慢SQL优化方法与思路总结

定位慢的SQL（数据库层排查）

慢查询知识

一般日志（General Log）：记录所有查询，用于调试
-- 查看一般日志相关配置
SHOW VARIABLES LIKE 'general_log%';
SHOW VARIABLES LIKE 'log_output';
-- 开启一般日志
SET GLOBAL general_log = 'ON'; -- OFF 关闭
-- 设置日志文件路径（如果需要）
SET GLOBAL general_log_file = 'D:\\file\\SlowLogMySQL\\mysql-general.log';
慢查询日志（Slow Query Log）：只记录执行时间超过阈值的查询

开启并分析数据库慢查询日志：

确认慢查询日志是否开启：

-- MySQL
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'log_output';

如果没开，立即开启（需重启或动态设置）：

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 设置慢查询阈值，例如2秒
-- SET SESSION long_query_time = 2;  -- 当前会话也设置 不设置这个的话需要重新连接才生效
SET GLOBAL slow_query_log_file = '/path/to/your/slow-query.log';
-- 绝对路劲
SET GLOBAL slow_query_log_file = 'D:\\file\\SlowLogMySQL\\slow-query.log';
SET GLOBAL log_output = 'FILE';

-- 有需要的话，可以关闭一般日志（避免干扰），默认好像就是关的
SET GLOBAL general_log = 'OFF';
-- 查看关闭状态
SHOW VARIABLES LIKE 'general_log';

分析慢日志： 直接打开日志文件查看，或者使用 mysqldumpslow 或 pt-query-digest (Percona Toolkit) 工具分析慢日志文件。

# 列出平均耗时最长的前10条SQL
mysqldumpslow -s at -t 10 /path/to/slow.log
# 使用pt-query-digest进行更专业的分析
pt-query-digest /path/to/slow.log

从分析结果中，你会得到：
- 具体的慢SQL语句。
- 执行次数、平均/最大/总耗时。直接打开日志文件查看不到，得用工具分析得到。
- 锁等待时间、扫描行数等信息。直接打开日志文件查看不到，得用工具分析得到。

实时监控数据库进程：

在功能变慢时，实时连接到数据库，查看当前正在执行的SQL。
```
SHOW FULL PROCESSLIST;
```
这个命令会列出所有数据库连接及其正在执行的命令或SQL。寻找 Time 列数值大、State 列为 Sending data, Copying to tmp table, Sorting result 等状态的连接，其 Info 列就是正在执行的SQL。

使用数据库监控工具：

很多数据库管理工具（如 phpMyAdmin, MySQL Workbench, Navicat）都带有“服务器状态监控”或“进程列表”功能，可以图形化地查看慢查询和运行状态。
云服务商（如 AWS RDS,阿里云RDS）的控制台也提供了非常完善的性能洞察和慢SQL统计功能。

慢查询搭建

创建超大表（500万条数据）

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS extreme_slow_demo;
USE extreme_slow_demo;

-- 创建没有索引的大表
CREATE TABLE massive_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    description TEXT
) ENGINE=InnoDB;

分批插入

-- 创建存储过程分批插入
DELIMITER $$
CREATE PROCEDURE InsertMassiveData()
BEGIN
    DECLARE batch_count INT DEFAULT 0;
    DECLARE total_batches INT DEFAULT 50;  -- 50批，每批10万 = 500万
    
    WHILE batch_count < total_batches DO
        INSERT INTO massive_orders (user_id, product_id, amount, status, created_at, description)
        SELECT 
            FLOOR(1 + RAND() * 100000),
            FLOOR(1 + RAND() * 5000),
            ROUND(RAND() * 1000, 2),
            FLOOR(RAND() * 5),
            DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY),
            REPEAT('Text data for slow query testing ', 30)
        FROM 
            (SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
            (SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
            (SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
            (SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t4,
            (SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t5,
            (SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t6,
            (SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t7,
            (SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t8;
        
        SET batch_count = batch_count + 1;
        SELECT CONCAT('Inserted batch: ', batch_count) as progress;
    END WHILE;
END$$
DELIMITER ;

-- 执行存储过程
CALL InsertMassiveData();

-- 删除存储过程
DROP PROCEDURE InsertTestData;

验证数据

-- 查看总数据量
SELECT COUNT(*) as total_records FROM massive_orders;

-- 查看数据样本
SELECT * FROM massive_orders LIMIT 10;

执行会导致慢查询的SQL

-- 执行一个肯定很慢的查询来测试
SELECT SLEEP(3);  -- 这个查询应该被记录

-- 或者用您的表测试
SELECT COUNT(*) FROM massive_orders WHERE description LIKE '%test%';

-- 查询1：全表扫描 + 复杂条件 + 排序（保证慢）
SELECT * FROM massive_orders 
WHERE user_id BETWEEN 1000 AND 2000 
  AND status = 1 
  AND amount > 500 
  AND created_at BETWEEN '2020-01-01' AND '2023-12-31'
  AND description LIKE '%slow%'
ORDER BY amount DESC, created_at ASC
LIMIT 1000;

-- 查询2：多字段分组 + 复杂计算
SELECT 
    user_id,
    product_id, 
    status,
    COUNT(*) as total_orders,
    AVG(amount) as avg_amount,
    SUM(amount) as total_amount,
    MIN(created_at) as first_order,
    MAX(created_at) as last_order
FROM massive_orders 
WHERE amount BETWEEN 100 AND 800
  AND created_at > '2021-01-01'
GROUP BY user_id, product_id, status
HAVING total_orders > 5 AND total_amount > 3000
ORDER BY total_amount DESC
LIMIT 500;

-- 查询3：多表JOIN（如果需要更慢）
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(100),
    vip_level TINYINT
);

慢sql日志分析

一个慢sql日志案例

# Time: 251126 20:51:40
# User@Host: root[root] @ localhost [127.0.0.1]  Id:   207
# Query_time: 61.885823  Lock_time: 0.000000 Rows_sent: 3125000  Rows_examined: 3125000
SET timestamp=1764161500;
SELECT * FROM massive_orders;

日志关键信息分析

字段	值	含义与分析
Id	207	数据库连接的进程ID
Query_time	`61.885823`	查询耗时约62秒 - 这是一个非常长的时间！
Rows_sent	`3,125,000`	返回了312.5万行数据
Rows_examined	`3,125,000`	扫描了312.5万行数据
SQL语句	`SELECT * FROM massive_orders;`	对`massive_orders`表的全量查询
`SET timestamp=1764161500;`	2025-11-26 20:51:40	这是查询开始执行时的时间戳。
`# Time: 251126 20:51:40`	2025-11-26 20:51:40	查询开始时间或者日志记录时间

慢SQL分析

上面找到慢SQL后，现在进行分析

使用 EXPLAIN 分析SQL执行计划：

这是优化SQL的必备技能。在慢SQL前面加上 EXPLAIN 关键字，查看数据库是如何执行这条SQL的。

EXPLAIN SELECT * FROM your_slow_sql;

重点关注：
- type列： 访问类型，至少应该是 range 级别，最好能达到 ref, eq_ref 或 const。避免出现 ALL（全表扫描）。
- key列： 实际使用的索引。如果为NULL，说明没用到索引。
- rows列： 预估需要扫描的行数。这个值应该尽可能小。
- Extra列： 额外信息。注意是否出现 Using filesort（需要额外排序）或 Using temporary（需要创建临时表），这些都是性能杀手。

1. `type` 列（访问类型）

核心概念：这列描述了 MySQL 如何查找表中的行。它揭示了查询效率的级别，从最优到最差排序。

从最优到最差，常见的类型有：

system & const：
- 含义：对查询的某部分进行优化，并将其转换为一个常量。system 是 const 的特例（表只有一行，如系统表）。
- 场景：通过主键（PRIMARY KEY）或唯一索引（UNIQUE INDEX）进行等值查询时。
- 例子：SELECT * FROM users WHERE id = 1;（id 是主键）
- 评价：最佳性能，最多只返回一行。
eq_ref：
- 含义：在表连接时，使用主键或唯一索引的所有部分被连接使用。
- 场景：多表连接查询，驱动表的每一行，都只能匹配被驱动表的一行。通常出现在 = 操作符的 JOIN 条件中。
- 例子：SELECT * FROM users JOIN orders ON users.id = orders.user_id;（orders.user_id 是外键且是唯一索引）
- 评价：非常高效的 JOIN 类型。
ref：
- 含义：使用非唯一性索引或者唯一性索引的前缀部分进行等值查询。
- 场景：使用普通索引（INDEX）或联合索引的左前缀进行 = 或 <=> 操作。
- 例子：SELECT * FROM users WHERE email = 'foo@bar.com';（email 字段有普通索引）
- 评价：很好，可能会返回多行，但索引查找效率很高。
range：
- 含义：使用索引检索给定范围的行。
- 场景：在索引列上使用 BETWEEN、>、<、>=、<=、IN()、LIKE 'prefix%'（前缀匹配）等操作符。
- 例子：SELECT * FROM users WHERE created_at > '2023-01-01';（created_at 有索引）
- 评价：可以接受，它扫描的是一个索引范围，而不是全表。这是你提到的“至少应该达到”的水平。
index：
- 含义：全索引扫描。遍历整个索引树来查找数据，与全表扫描类似，但因为它只扫描索引（通常比数据本身小），所以比 ALL 稍快。
- 场景：查询的列都包含在某个索引中（覆盖索引），或者需要按索引的顺序进行排序。
- 例子：SELECT id FROM users;（id 是主键，这个查询可以直接从索引中获取所有 id，无需读数据行）
- 评价：通常不理想，但如果数据量巨大，它比 ALL 好。
ALL：
- 含义：全表扫描。MySQL 会读取整张表的每一行来找到匹配的行。
- 场景：没有索引可用于查询条件。
- 例子：SELECT * FROM users WHERE name = 'foo';（name 字段没有索引）
- 评价：性能杀手，必须避免，尤其是在大表上。

2. `key` 列（实际使用的索引）

核心概念：显示 MySQL 实际决定使用的索引。如果优化器选择了复合索引，这里会显示索引的名字。

优化重点：
- 如果 key 为 NULL，这是一个强烈的警告信号，说明没有使用索引。你需要考虑为 WHERE、JOIN、ORDER BY 或 GROUP BY 子句中的列创建索引。
- 有时，key 列显示的不是你期望的索引。这可能是因为：
  - 存在多个可选索引，MySQL 优化器选择了一个它认为成本最低的（可能不正确）。
  - 你的查询写法导致索引失效（例如对索引列使用了函数 WHERE YEAR(create_time) = 2023）。
- 你可以使用 FORCE INDEX 或调整查询语句来引导优化器使用正确的索引。

3. `rows` 列（预估扫描行数）

核心概念：MySQL 优化器预估为了找到所需的行，需要读取多少行数据。注意，这是一个预估值，并非精确值。

优化重点：
- 这个值应该尽可能小。一个良好的查询可能只扫描几十或几百行。
- 如果这个值非常大（例如，数万、数百万），即使 type 不是 ALL，也意味着查询效率低下。它表明虽然用了索引，但索引的选择性不高，或者查询需要访问大量索引项。
- 将此值与表的总行数对比。如果 rows 接近总行数，通常意味着全表扫描或近似全表扫描。

4. `Extra` 列（额外信息）

核心概念：包含 MySQL 解决查询的详细信息，很多是性能优化的关键线索。

需要警惕的“坏”信息：

Using filesort：
- 含义：MySQL 必须执行一个额外的排序操作，而无法直接利用索引顺序。这个“文件”排序可能在内存或磁盘上进行，效率较低。
- 原因：ORDER BY 的列没有合适的索引，或者索引顺序与 ORDER BY 顺序不一致。
- 优化：为 ORDER BY 和 GROUP BY 的列创建索引。
Using temporary：
- 含义：MySQL 需要创建一张临时表来存储中间结果，通常发生在处理 GROUP BY、DISTINCT 或复杂的 ORDER BY 时。
- 原因：查询过于复杂，无法直接在一遍扫描中完成。
- 优化：尤其是当临时表很大，需要被写入磁盘时，性能会急剧下降。需要通过创建合适的索引或重写查询来避免。
Using where：
- 含义：MySQL 服务器在从存储引擎拿到行之后，还需要使用 WHERE 条件进行过滤。
- 评价：这不一定总是坏的。如果 type 是 index 或 range，Using where 是正常的。但如果 type 是 ALL 并且有 Using where，说明它在进行全表扫描后逐行过滤，性能极差。

值得高兴的“好”信息：

Using index：
- 含义：出现了覆盖索引。这意味着查询所需的所有列都包含在索引中，因此 MySQL 只需读取索引，而无需回表读取数据行。这非常高效。
- 例子：有一个索引 INDEX (name, age)，查询 SELECT name, age FROM users WHERE name = 'foo';

总结与排查流程

先看 type：判断访问方法是否高效。目标是 range 及以上，杜绝 ALL。
再看 key：确认是否用上了索引。如果是 NULL，优先解决。
然后看 rows：评估扫描数据量。数值过大则需要优化。
最后看 Extra：发现隐藏问题。重点解决 Using filesort 和 Using temporary，并争取实现 Using index。

通过系统性地分析这四列，你可以精准地定位 SQL 的性能瓶颈，并采取相应的优化措施（主要是创建或调整索引）。