一、SQL 优化的基本认知框架
一条 SQL 语句在 MySQL 中的生命周期包含词法解析、语法解析、语义解析、逻辑优化、物理优化和执行返回六个阶段。DBA 的优化工作主要聚焦于逻辑优化与部分物理优化环节，核心目标是让 SQL 在处理过程中扫描更少的记录与字段，减少 IO 和计算资源消耗。
逻辑优化本质是基于数据库内置规则的预处理，例如：
- 用表关联替代子查询以避免临时表开销
- 将分组聚合条件上推至更早处理阶段
- 在特定场景用内连接替换外连接减少空值处理
- 视图合并到基表查询以释放优化器的选择空间
物理优化则是数据库根据实时状态生成执行计划的过程，影响因素包括：
- 表统计信息与列分布情况
- 索引数量及选择性优劣
- 当前系统负载与硬件资源占用
- 事务隔离级别等会话参数
二、表结构层面的深度诊断
（一）存储引擎标准化
首先需确认所有表是否统一使用InnoDB 引擎，这在 MySQL 5.7 及更早版本中尤为重要。从 5.5 版本开始，MySQL 的存储层优化（如
MVCC、锁机制、Buffer Pool 等）均针对 InnoDB 设计。对于非 InnoDB 表，可通过以下语句转换：
ALTER TABLE table_name ENGINE=InnoDB;
同时需检查临时表参数设置，例如：
tmp_table_size与max_heap_table_size是否匹配业务峰值- 是否存在大结果集导致临时表从 MEMORY 转为磁盘存储的情况
（二）字段类型合理性校验
重点关注：
- 数值型字段是否使用了过大的类型（如用 BIGINT 存储小整数）
- 字符串字段是否合理设置了 CHAR/VARCHAR 类型及长度
- 时间类型是否根据精度需求选择 DATETIME/TIMESTAMP
- 是否存在 TEXT/BLOB 类型字段导致的行格式异常
（三）表规模与统计信息检查
通过以下语句获取表信息：
SELECT table_name, table_rows, avg_row_length
FROM information_schema.tables
WHERE table_schema = 'your_schema';
若单表记录数超过 500 万或平均行长度过大，需考虑分表或分区。同时检查统计信息是否更新：
SHOW TABLE STATUS LIKE 'table_name';
若
Update_time与当前时间差异较大，需执行ANALYZE TABLE table_name;更新统计信息。（四）索引设计评估
索引分析可分为四个层级：
- 无索引表：甚至缺失主键，需立即添加主键并根据查询模式创建索引
- 仅有主键 / 唯一索引：需根据高频查询条件添加二级索引
- 索引结构合理：检查索引选择性（
SELECT COUNT(DISTINCT col)/COUNT(*) FROM table） - 索引选择性差：如枚举字段创建索引，需考虑复合索引或重构字段
三、视图优化的核心策略
（一）视图算法选择
通过
SHOW CREATE VIEW view_name查看视图算法：- MERGE 算法：将视图定义合并到外层查询，优化器可重新规划表关联顺序
- TEMPTABLE 算法：生成临时表存储视图结果，适合复杂子查询
对于大结果集视图，可通过修改算法测试性能：
CREATE OR REPLACE VIEW view_name
AS SELECT ... WITH [MERGE | TEMPTABLE];
（二）视图逻辑重构
当视图包含复杂处理逻辑时：
- 将部分计算移至应用层，避免数据库 CPU 资源浪费
- 拆解多层嵌套视图为基表关联，释放优化器的连接顺序选择空间
- 对于非必要视图，直接合并到上层 SQL 查询中
四、表关联优化的关键要点
（一）关联键完整性校验
检查关联键是否满足：
- 主表关联字段是否为主键或唯一索引
- 从表关联字段是否添加外键约束
- 关联字段的字符集、排序规则是否一致
对于编码不一致的情况，需在 SQL 中显式转换：
SELECT * FROM table1 t1
JOIN table2 t2 ON CONVERT(t1.col USING utf8mb4) = CONVERT(t2.col USING utf8mb4);
（二）关联条件强化
当关联键为非唯一字段时，需添加额外过滤条件：
-- 优化前
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- 优化后
SELECT * FROM orders o JOIN customers c
ON o.customer_id = c.id AND o.order_date > '2023-01-01';
五、SQL 语句的重写优化
（一）复杂查询简化策略
1. 子查询优化
将嵌套子查询转换为表关联：
-- 子查询方式
SELECT * FROM products p
WHERE p.id IN (SELECT product_id FROM orders WHERE status = 'completed');
-- 关联方式
SELECT p.* FROM products p
JOIN orders o ON p.id = o.product_id AND o.status = 'completed';
2. 聚合操作优化
将多层聚合简化为单层：
-- 优化前
SELECT AVG(price) FROM (SELECT MAX(price) FROM products GROUP BY category) sub;
-- 优化后
SELECT AVG(price) FROM products
GROUP BY category ORDER BY price DESC LIMIT 1;
（二）表连接优化
对于多表连接（超过 5 张表），可采用：
- 分批次查询，通过应用层合并结果
- 建立中间表存储常用连接结果
- 使用 STRAIGHT_JOIN 指定表连接顺序
六、执行计划分析与优化迭代
（一）执行计划关键指标
通过
EXPLAIN [EXTENDED] SELECT ...分析：- key：实际使用的索引，NULL 表示全表扫描
- rows：预估扫描行数，与实际行数的偏差率
- Extra：包含 Using Index（覆盖索引）、Using Where（条件过滤）等关键信息
（二）索引失效场景处理
当索引未被使用时：
- 检查 WHERE 条件是否包含函数操作（如
YEAR(date_col) = 2023） - 确认是否存在数据倾斜导致统计信息偏差
- 检查是否因 OR 条件导致索引合并失效
- 考虑添加覆盖索引（包含所有查询字段的索引）
（三）大表查询优化
对于走索引仍性能不佳的场景：
- 实施分区表（如按日期分区）：
ALTER TABLE large_table PARTITION BY RANGE(TO_DAYS(create_time)) (PARTITION p2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),-- 更多分区... ); - 采用分表策略（如按 ID 哈希分表）
- 业务层面添加查询时间范围限制
七、优化实践的最佳实践
-
建立优化流程规范：
- 所有 SQL 变更前必须经过 EXPLAIN 分析
- 大表操作需在测试环境预演
- 保留原始 SQL 与优化后 SQL 的性能对比记录
-
索引管理策略：
- 定期清理未使用的索引（通过
SHOW PROCESSLIST和pt-index-usage） - 新增索引前评估对写入性能的影响
- 核心表索引数量控制在 5 个以内
- 定期清理未使用的索引（通过
-
性能基线与监控：
- 建立 SQL 性能基线（如响应时间、QPS 阈值）
- 使用 Percona Toolkit 或 Prometheus 监控 SQL 执行变化
- 定期生成 SQL 优化报告（TOP 慢查询、索引命中率等）