SQL查询性能优化实战：执行计划分析与索引策略调优

小编 • • 数据库

SQL执行计划解读与EXPLAIN分析

SQL查询性能优化的第一步是理解执行计划。EXPLAIN命令展示数据库引擎执行查询的具体步骤，包括表访问方式、索引使用情况、连接顺序和估算成本。MySQL使用EXPLAIN关键字，PostgreSQL使用EXPLAIN ANALYZE获取实际执行统计。

-- MySQL执行计划分析
EXPLAIN SELECT 
    o.order_id, o.total_amount, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed' 
  AND o.created_at >= '2026-01-01'
  AND c.region = '华东'
ORDER BY o.total_amount DESC
LIMIT 20;

-- 关键字段解读
-- id: 查询序号，相同id表示同一层级的表连接
-- select_type: 查询类型（SIMPLE/PRIMARY/SUBQUERY/DERIVED）
-- table: 表名
-- type: 访问类型（性能从好到差：system > const > eq_ref > ref > range > index > ALL）
-- key: 实际使用的索引
-- key_len: 索引使用长度（判断复合索引使用了几列）
-- rows: 估算扫描行数
-- Extra: 额外信息（Using index表示覆盖索引，Using filesort表示额外排序，Using temporary表示临时表）

type字段是执行计划中最重要的指标。const和eq_ref是最优访问类型，表示通过主键或唯一索引精确匹配。ref表示通过非唯一索引匹配。range表示索引范围扫描。index表示全索引扫描。ALL表示全表扫描，性能最差，通常需要添加索引优化。

-- PostgreSQL执行计划（含实际执行统计）
EXPLAIN ANALYZE 
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed' AND o.created_at >= '2026-01-01';

-- 输出示例
-- Nested Loop (cost=0.86..5234.12 rows=15234 width=48) (actual time=0.023..45.672 rows=15200 loops=1)
--   -> Index Scan using idx_orders_status_date on orders o (cost=0.43..2103.45 rows=15234 width=24) (actual...)
--   -> Index Scan using customers_pkey on customers c (cost=0.43..0.20 rows=1 width=36) (actual...)
-- Planning Time: 0.156 ms
-- Execution Time: 45.891 ms

PostgreSQL的EXPLAIN ANALYZE展示实际执行时间和行数，比MySQL的估算值更准确。cost参数的I/O和CPU成本由配置参数控制，rows是估算值可能与实际值偏差。

索引类型选择与复合索引设计

索引是SQL性能优化的核心手段。B+Tree索引是MySQL默认索引类型，适合等值查询、范围查询和排序。Hash索引仅支持等值查询，不支持范围和排序。全文索引用于文本搜索。选择索引类型需匹配查询模式。

复合索引设计原则：最左前缀匹配（查询条件从索引最左列开始使用）、高选择性列在前（区分度高的列放在复合索引前面）、覆盖索引优先（查询的列全部包含在索引中，避免回表）。

-- 复合索引设计示例
-- 场景：订单查询按用户ID + 状态 + 创建时间过滤

-- 错误索引：单独索引无法覆盖复合查询条件
CREATE INDEX idx_customer ON orders(customer_id);        -- 仅覆盖customer_id
CREATE INDEX idx_status ON orders(status);               -- 选择性低，效果差
CREATE INDEX idx_created ON orders(created_at);          -- 范围查询不走索引

-- 正确索引：复合索引覆盖查询条件
CREATE INDEX idx_customer_status_date ON orders(
    customer_id,    -- 高选择性，等值查询
    status,         -- 中选择性，等值查询
    created_at      -- 范围查询放最后
);

-- 验证索引使用情况
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 1001 AND status = 'completed' AND created_at >= '2026-01-01';
-- type: ref, key: idx_customer_status_date, key_len: 16 (三列全用到)

-- 最左前缀匹配示例
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;
-- 使用索引 idx_customer_status_date (匹配第一列)

EXPLAIN SELECT * FROM orders WHERE status = 'completed';
-- 不使用索引 (跳过了customer_id，违反最左前缀)

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND created_at >= '2026-01-01';
-- 使用索引但key_len短 (跳过status，仅用customer_id)

覆盖索引（Covering Index）避免回表查询，性能提升显著。如果查询的列全部在索引中，数据库直接从索引返回数据，无需访问数据行：

-- 覆盖索引示例
-- 创建覆盖索引包含查询所需的所有列
CREATE INDEX idx_covering ON orders(customer_id, status, created_at, order_id, total_amount);

-- 查询：所有列都在索引中，Extra显示Using index
EXPLAIN SELECT order_id, total_amount FROM orders
WHERE customer_id = 1001 AND status = 'completed';
-- Extra: Using index (覆盖索引，无需回表)

慢查询定位与优化流程

定位慢查询的第一步是开启慢查询日志。MySQL通过slow_query_log记录执行时间超过阈值的SQL语句：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未走索引的查询

-- 分析慢查询日志
-- 使用mysqldumpslow工具聚合分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t: 按总时间排序
-- -t 10: 显示前10条

-- 输出示例
-- Count: 1523  Time=2.34s (3563s)  Lock=0.01s (15s)  Rows=12500.0 (19M)
-- SELECT * FROM orders WHERE status = 'pending' AND created_at > 'S'
-- 出现1523次，平均2.34秒，扫描12500行

-- 优化该查询
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';
-- type: ALL, rows: 850000 (全表扫描)

-- 添加复合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 优化后
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';
-- type: range, key: idx_status_created, rows: 3200 (索引范围扫描)

慢查询优化的标准流程：定位（慢查询日志） -> 分析（EXPLAIN执行计划） -> 优化（索引调整或SQL改写） -> 验证（再次EXPLAIN确认） -> 监控（持续跟踪查询性能）。

SQL改写优化技巧

部分性能问题无法仅通过添加索引解决，需要改写SQL语句。常见改写技巧：

-- 1. 避免SELECT *，只查询需要的列
-- 优化前
SELECT * FROM orders WHERE customer_id = 1001;
-- 优化后（利用覆盖索引）
SELECT order_id, total_amount, status FROM orders WHERE customer_id = 1001;

-- 2. 避免在索引列上使用函数或类型转换
-- 优化前（索引失效，全表扫描）
SELECT * FROM orders WHERE DATE(created_at) = '2026-09-10';
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 优化后（保持索引列原样）
SELECT * FROM orders WHERE created_at >= '2026-09-10' AND created_at < '2026-09-11';
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 3. 避免前置通配符LIKE查询
-- 优化前（索引失效）
SELECT * FROM products WHERE product_name LIKE '%手机%';
-- 优化后（后置通配符可走索引）
SELECT * FROM products WHERE product_name LIKE '苹果%';
-- 全文搜索替代LIKE
SELECT * FROM products WHERE MATCH(product_name) AGAINST('手机' IN BOOLEAN MODE);

-- 4. 大分页优化：避免LIMIT深翻页
-- 优化前（扫描前100万行后取20行）
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;
-- 优化后（游标分页，利用索引定位）
SELECT * FROM orders 
WHERE created_at < '2026-09-09 12:00:00' 
ORDER BY created_at DESC LIMIT 20;

-- 5. JOIN优化：小表驱动大表
-- 优化前（大表JOIN小表）
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.region = '华东';
-- 优化后（先过滤小表再JOIN）
SELECT o.* FROM orders o 
JOIN (SELECT customer_id FROM customers WHERE region = '华东') c 
ON o.customer_id = c.customer_id;

-- 6. 子查询改写为JOIN
-- 优化前
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = '华东');
-- 优化后
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.region = '华东';

-- 7. UNION ALL替代UNION
-- 优化前（UNION会去重排序）
SELECT product_name FROM products WHERE category = 'A'
UNION
SELECT product_name FROM products WHERE category = 'B';
-- 优化后（确定无重复时用UNION ALL）
SELECT product_name FROM products WHERE category = 'A'
UNION ALL
SELECT product_name FROM products WHERE category = 'B';

数据库参数调优与监控

SQL优化之外，数据库引擎参数调优对整体查询性能有显著影响。关键参数包括缓冲池大小、排序缓冲区和连接数配置：

-- InnoDB核心参数调优
-- 缓冲池大小（建议物理内存的60%-80%）
SET GLOBAL innodb_buffer_pool_size = 85899345920;  -- 80GB

-- 排序缓冲区（每个连接分配，不宜过大）
SET GLOBAL innodb_sort_buffer_size = 1048576;  -- 1MB

-- 临时表大小
SET GLOBAL tmp_table_size = 67108864;  -- 64MB
SET GLOBAL max_heap_table_size = 67108864;  -- 64MB

-- 连接数
SET GLOBAL max_connections = 500;
SET GLOBAL thread_cache_size = 100;

-- 查询缓存（MySQL 8.0已移除，MariaDB保留）
-- 生产环境建议关闭查询缓存，锁竞争严重

-- 监控关键指标
-- Innodb_buffer_pool_read_requests: 缓冲池读取请求总数
-- Innodb_buffer_pool_reads: 磁盘读取次数（命中率 = 1 - reads/read_requests）
-- Innodb_buffer_pool_pages_dirty: 脏页数量
-- Innodb_row_lock_waits: 行锁等待次数
-- Innodb_row_lock_time_avg: 平均行锁等待时间

-- 查看缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- 生产环境应保持在99%以上

持续监控方面，使用Prometheus + mysqld_exporter采集数据库指标，Grafana可视化关键性能趋势。慢查询持续入库分析，定期Review Top 10慢查询并优化。索引使用率监控识别未使用的索引（浪费空间和写入性能），定期清理冗余索引。pt-index-usage工具分析索引使用情况，pt-query-digest深入分析慢查询模式。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/sql-cha-xun-xing-neng-you-hua-shi-zhan-zhi-xing-ji-hua-fen/

赞 (0)
小编小编
0
上一篇 3小时前
下一篇 3小时前

相关推荐