SQL查询性能调优实战：从索引优化到执行计划分析
环境准备：搭建可复现的测试环境
要进行数据驱动的性能分析，一个可复现的环境是基础。本教程以 MySQL 8.0 为例，它提供了丰富的执行计划分析工具 [来源#1]。我们将用 Docker 快速启动一个数据库实例，并创建一个包含测试数据的表。
# 使用 Docker 启动 MySQL 8.0 实例
# 将容器的 3306 端口映射到宿主机的 3306 端口
# 设置 root 密码为 'password'
docker run --name mysql-perf -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 -d mysql:8.0
# 等待容器启动完成
sleep 10
# 连接到 MySQL 实例
mysql -h 127.0.0.1 -P 3306 -u root -p启动并连接 MySQL 8.0 容器
进入 MySQL 命令行后，我们创建一个测试数据库和表，并插入一些模拟数据。这个表将用于演示慢查询和索引优化。
-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS perf_test;
USE perf_test;
-- 创建一个包含百万级数据的订单表
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
INDEX idx_user_id (user_id),
INDEX idx_status (status),
INDEX idx_order_date (order_date)
) ENGINE=InnoDB;
-- 插入 100 万条模拟数据
-- 使用存储过程批量插入，提升效率
DELIMITER //
CREATE PROCEDURE generate_orders()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE user_id_val INT;
DECLARE status_val VARCHAR(20);
DECLARE order_date_val DATE;
DECLARE amount_val DECIMAL(10, 2);
WHILE i < 1000000 DO
SET user_id_val = FLOOR(1 + RAND() * 10000);
SET status_val = CASE FLOOR(1 + RAND() * 3)
WHEN 1 THEN 'pending'
WHEN 2 THEN 'completed'
ELSE 'cancelled'
END;
SET order_date_val = DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY);
SET amount_val = ROUND(RAND() * 1000, 2);
INSERT INTO orders (user_id, status, order_date, amount)
VALUES (user_id_val, status_val, order_date_val, amount_val);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
-- 执行存储过程生成数据
CALL generate_orders();
-- 清理存储过程
DROP PROCEDURE generate_orders;
-- 验证数据量
SELECT COUNT(*) FROM orders;创建测试表并插入百万级数据
执行完上述 SQL 后，你应该看到类似 `1000000` 的输出，表示数据已准备就绪。现在我们有了一个包含 100 万条记录的表，可以开始进行性能分析了。
步骤拆解：分析执行计划定位瓶颈
性能优化的第一步是理解查询是如何执行的。MySQL 提供了 `EXPLAIN` 命令来展示查询的执行计划 [来源#1]。我们先创建一个典型的慢查询，然后分析它的执行计划。
-- 这是一个典型的慢查询：在 status 和 order_date 上过滤，并按 order_date 排序
-- 由于没有合适的复合索引，这将导致全表扫描
EXPLAIN SELECT * FROM orders
WHERE status = 'completed'
AND order_date >= '2023-06-01'
ORDER BY order_date
LIMIT 10;执行一个未优化的慢查询并查看执行计划
执行上述 `EXPLAIN` 后，你会看到 `type` 列显示为 `ALL`，这表示 MySQL 正在进行全表扫描 [来源#1]。`rows` 列显示的数字会非常大，说明需要扫描大量数据行。`Extra` 列可能包含 `Using filesort`，表示 MySQL 需要额外的排序操作，这也是性能瓶颈。
- type=ALL：表示全表扫描，是最慢的访问类型。
- key=NULL：表示没有使用任何索引。
- rows=大量数字：表示需要扫描的行数，数字越大性能越差。
- Extra=Using filesort：表示需要额外的排序操作，消耗内存和CPU。
结果验证：应用索引优化并验证效果
根据执行计划的分析，我们发现查询在 `status` 和 `order_date` 字段上进行了过滤和排序，但这两个字段目前只有单列索引。为了优化这个查询，我们可以创建一个复合索引。复合索引的列顺序很重要，应遵循最左前缀原则。
-- 创建复合索引，将 status 放在前面，因为它是等值查询条件
-- order_date 放在后面，因为它用于范围查询和排序
CREATE INDEX idx_status_order_date ON orders (status, order_date);
-- 再次执行相同的查询，查看优化后的执行计划
EXPLAIN SELECT * FROM orders
WHERE status = 'completed'
AND order_date >= '2023-06-01'
ORDER BY order_date
LIMIT 10;创建复合索引并验证优化效果
创建索引后，再次执行 `EXPLAIN`，你会看到 `type` 从 `ALL` 变为 `ref` 或 `range`，`key` 显示了我们创建的索引 `idx_status_order_date`，`rows` 列的数字会显著下降。这表明索引生效，数据库只需扫描少量数据行。如果 `Extra` 列不再显示 `Using filesort`，说明排序也利用了索引，性能得到极大提升。
- type=ref/range：表示使用了索引进行查找。
- key=idx_status_order_date：确认使用了我们创建的复合索引。
- rows=较小数字：表示扫描的行数大幅减少。
- Extra 列中无 Using filesort：表示排序操作被索引覆盖，避免了额外排序。
常见错误与排查
在性能调优过程中，经常会遇到一些典型问题。以下是三个常见错误及其排查方法。
- 错误1：索引未生效。原因可能是索引列顺序不符合最左前缀原则，或者查询条件使用了函数导致索引失效。排查方法：检查 `EXPLAIN` 输出的 `key` 列是否为 NULL，并检查 `Extra` 列是否有 `Using where` 和 `Using index` 等提示。
- 错误2：执行计划显示 `type=ALL` 但 `rows` 很小。这可能是因为表数据量本身很小，全表扫描比索引查找更快。排查方法：确认表数据量，如果数据量小（如少于 1000 行），全表扫描是合理的。
- 错误3：创建索引后查询变慢。这可能是因为索引维护成本过高，或者查询条件过于复杂导致优化器选择错误。排查方法：使用 `ANALYZE TABLE` 更新表统计信息，或使用 `FORCE INDEX` 强制指定索引进行测试。
性能优化是一个持续的过程，需要结合业务场景和数据变化进行调整。定期分析执行计划，监控慢查询日志，是保持数据库高性能的关键。
本教程从环境搭建开始，通过分析执行计划定位了全表扫描的瓶颈，并通过创建复合索引解决了慢查询问题。我们验证了优化前后的执行计划差异，并讨论了三个常见错误。记住，所有优化都应基于实际数据和执行计划，避免盲目猜测 [来源#2]。
参考链接
- https://dev.mysql.com/doc/refman/8.0/en/optimization.html
- https://www.postgresql.org/docs/current/using-explain.html
