 详解)
一、MySQL优化器概述
1.1 什么是查询优化器
查询优化器(Query Optimizer)是MySQL的核心组件,负责将SQL语句转换为最优的执行计划。
工作流程:
SQL语句 → 解析器(Parser) → 优化器(Optimizer) → 执行器(Executor) → 存储引擎优化器的主要职责:
- 选择最优的索引
- 确定表的连接顺序
- 选择合适的连接算法
- 优化子查询
- 简化和重写查询语句
1.2 优化器类型
MySQL主要有两种优化器:
基于规则的优化器(RBO - Rule-Based Optimizer)
- 基于预定义的规则进行优化
- 较为简单,但不够灵活
基于成本的优化器(CBO - Cost-Based Optimizer)⭐
- MySQL主要使用这种
- 通过计算各种执行计划的成本,选择成本最低的
- 依赖统计信息
二、优化器的工作原理
2.1 成本模型
MySQL优化器通过成本模型评估不同执行计划的代价。
成本计算因素:
总成本=I/O成本+CPU成本-- I/O成本: 从磁盘读取数据的成本-- CPU成本: 处理数据(比较、排序)的成本成本常量(MySQL 5.7+):
-- 查看成本常量SELECT*FROMmysql.server_cost;SELECT*FROMmysql.engine_cost;-- 主要成本参数:-- disk_temptable_create_cost: 创建临时表成本(默认20.0)-- disk_temptable_row_cost: 临时表行读取成本(默认0.5)-- key_compare_cost: 键比较成本(默认0.05)-- memory_temptable_create_cost: 内存临时表创建成本(默认1.0)-- memory_temptable_row_cost: 内存临时表行成本(默认0.1)-- row_evaluate_cost: 行评估成本(默认0.1)2.2 统计信息
优化器依赖表和索引的统计信息做决策。
-- 查看表统计信息SHOWTABLESTATUSLIKE'table_name'\G-- 查看索引统计信息SHOWINDEXFROMtable_name;-- 关键统计指标:-- Cardinality: 索引中唯一值的数量(区分度)-- Rows: 表中的行数-- Data_length: 数据文件大小-- Index_length: 索引文件大小-- 更新统计信息ANALYZETABLEtable_name;统计信息采样:
-- InnoDB统计信息采样设置SHOWVARIABLESLIKE'innodb_stats%';-- innodb_stats_persistent: 持久化统计信息(ON/OFF)-- innodb_stats_auto_recalc: 自动重新计算统计信息-- innodb_stats_sample_pages: 采样页数(默认8)三、优化器的优化策略
3.1 条件简化和优化
常量传播:
-- 原始SQLSELECT*FROMtWHEREa=5ANDb=a;-- 优化后SELECT*FROMtWHEREa=5ANDb=5;恒等式消除:
-- 原始SQLSELECT*FROMtWHEREa>3ANDa>5;-- 优化后SELECT*FROMtWHEREa>5;范围合并:
-- 原始SQLSELECT*FROMtWHERE(a>1ANDa<5)OR(a>3ANDa<7);-- 优化后SELECT*FROMtWHEREa>1ANDa<7;3.2 索引选择
优化器通过以下步骤选择索引:
1. 找出所有可能的索引:
EXPLAINSELECT*FROMuserWHEREage=25ANDname='张三';-- possible_keys 显示所有可能使用的索引2. 计算每个索引的成本:
-- 成本计算公式(简化版):成本=(扫描的数据页数 × I/O成本)+(处理的记录数 × CPU成本)3. 选择成本最低的索引
示例分析:
-- 表结构CREATETABLEuser(idINTPRIMARYKEY,ageINT,nameVARCHAR(50),cityVARCHAR(50),INDEXidx_age(age),INDEXidx_name(name),INDEXidx_age_name(age,name));-- 查询1: 优化器会选择 idx_age_name(覆盖索引)EXPLAINSELECTage,nameFROMuserWHEREage=25;-- 查询2: 如果需要所有字段,可能选择 idx_age(需要回表)EXPLAINSELECT*FROMuserWHEREage=25;-- 使用 optimizer_trace 查看详细过程SEToptimizer_trace='enabled=on';SELECT*FROMuserWHEREage=25;SELECT*FROMinformation_schema.OPTIMIZER_TRACE\GSEToptimizer_trace='enabled=off';3.3 JOIN优化
连接顺序优化:
-- 三表连接SELECT*FROMt1JOINt2ONt1.id=t2.t1_idJOINt3ONt2.id=t3.t2_idWHEREt1.status=1;-- 优化器会评估6种连接顺序(3! = 6):-- t1 → t2 → t3-- t1 → t3 → t2-- t2 → t1 → t3-- t2 → t3 → t1-- t3 → t1 → t2-- t3 → t2 → t1连接算法选择:
- 嵌套循环连接(Nested-Loop Join)
-- 简单嵌套循环(Simple Nested-Loop)for each rowint1:for each rowint2:ifrowmatchesjoincondition: outputrow-- 时间复杂度: O(n * m)- 索引嵌套循环(Index Nested-Loop Join)
-- 使用索引加速内表查询for each rowint1:useindextofind matchingrowsint2 outputmatchedrows-- 时间复杂度: O(n * log m)- 块嵌套循环(Block Nested-Loop Join)
-- 使用join buffer缓存外表数据-- MySQL 8.0+ 使用Hash Join替代-- 查看join buffer大小SHOWVARIABLESLIKE'join_buffer_size';- Hash Join(MySQL 8.0.18+)
-- 构建哈希表,性能更好-- 适用于等值连接EXPLAINFORMAT=TREESELECT*FROMt1JOINt2ONt1.id=t2.id;-- 可以看到 "Hash Join" 字样JOIN优化建议:
-- ✅ 小表驱动大表SELECT*FROMsmall_table t1JOINlarge_table t2ONt1.id=t2.small_id;-- ✅ 确保JOIN字段有索引ALTERTABLEt2ADDINDEXidx_small_id(small_id);-- ✅ 使用STRAIGHT_JOIN强制连接顺序(谨慎使用)SELECT*FROMt1 STRAIGHT_JOIN t2ONt1.id=t2.t1_id;3.4 子查询优化
子查询转换策略:
1. 子查询物化(Subquery Materialization):
-- 原始SQLSELECT*FROMt1WHEREidIN(SELECTt1_idFROMt2WHEREstatus=1);-- 优化过程:-- 1. 先执行子查询,结果存入临时表-- 2. 临时表加索引-- 3. 用临时表进行JOIN-- EXPLAIN 中看到 "MATERIALIZED"2. 子查询转JOIN:
-- 原始SQL(相关子查询)SELECT*FROMt1WHEREEXISTS(SELECT1FROMt2WHEREt2.t1_id=t1.id);-- 优化后(Semi-Join)SELECTt1.*FROMt1 SEMIJOINt2ONt2.t1_id=t1.id;3. 子查询展开:
-- 原始SQLSELECT*FROMt1WHERE(SELECTCOUNT(*)FROMt2WHEREt2.t1_id=t1.id)>5;-- 优化后SELECTt1.*FROMt1JOIN(SELECTt1_id,COUNT(*)ascntFROMt2GROUPBYt1_idHAVINGcnt>5)t2ONt1.id=t2.t1_id;控制子查询优化:
-- 查看子查询优化策略SHOWVARIABLESLIKE'optimizer_switch';-- 关键参数:-- materialization: 子查询物化-- semijoin: 半连接优化-- subquery_materialization_cost_based: 基于成本选择3.5 ORDER BY 和 GROUP BY 优化
索引排序 vs 文件排序:
-- ✅ 使用索引排序(Index Scan)-- 假设有索引 idx_age(age)EXPLAINSELECT*FROMuserORDERBYage;-- Extra: Using index-- ❌ 使用文件排序(Filesort)EXPLAINSELECT*FROMuserORDERBYname;-- Extra: Using filesort-- Filesort过程:-- 1. 根据WHERE条件读取数据-- 2. 将需要排序的字段放入sort buffer-- 3. 如果数据量大于sort_buffer_size,使用磁盘临时文件-- 4. 进行排序(快速排序)GROUP BY优化:
-- ✅ 松散索引扫描(Loose Index Scan)-- 假设索引 idx_age_city(age, city)EXPLAINSELECTage,COUNT(*)FROMuserGROUPBYage;-- Extra: Using index for group-by-- ✅ 紧凑索引扫描(Tight Index Scan)EXPLAINSELECTage,city,COUNT(*)FROMuserWHEREage>20GROUPBYage,city;-- Extra: Using where; Using index-- ❌ 临时表分组EXPLAINSELECTcity,COUNT(*)FROMuserGROUPBYcity;-- Extra: Using temporary; Using filesort优化配置:
-- 查看排序缓冲区大小SHOWVARIABLESLIKE'sort_buffer_size';-- 默认256KBSHOWVARIABLESLIKE'max_length_for_sort_data';-- 默认1024-- 临时表相关SHOWVARIABLESLIKE'tmp_table_size';-- 内存临时表大小SHOWVARIABLESLIKE'max_heap_table_size';-- 堆表最大值四、优化器提示(Hint)
4.1 索引提示
-- 强制使用某个索引SELECT*FROMuserFORCEINDEX(idx_age)WHEREage=25;-- 建议使用某个索引(优化器可能忽略)SELECT*FROMuserUSEINDEX(idx_age)WHEREage=25;-- 忽略某个索引SELECT*FROMuserIGNOREINDEX(idx_age)WHEREage=25;-- MySQL 8.0+ 新语法SELECT/*+ INDEX(user idx_age) */*FROMuserWHEREage=25;4.2 JOIN提示
-- 强制连接顺序SELECT*FROMt1 STRAIGHT_JOIN t2ONt1.id=t2.t1_id;-- MySQL 8.0+ JOIN提示SELECT/*+ JOIN_ORDER(t1, t2, t3) */*FROMt1,t2,t3WHEREt1.id=t2.t1_idANDt2.id=t3.t2_id;-- 指定JOIN算法SELECT/*+ BNL(t1, t2) */*-- Block Nested-LoopFROMt1JOINt2ONt1.id=t2.id;SELECT/*+ HASH_JOIN(t1, t2) */*-- Hash Join(8.0.18+)FROMt1JOINt2ONt1.id=t2.id;4.3 其他提示
-- 子查询物化SELECT/*+ SUBQUERY(MATERIALIZATION) */*FROMt1WHEREidIN(SELECTt1_idFROMt2);-- 指定临时表使用内存SELECT/*+ SET_VAR(internal_tmp_mem_storage_engine=TempTable) */age,COUNT(*)FROMuserGROUPBYage;-- 限制执行时间(8.0+)SELECT/*+ MAX_EXECUTION_TIME(1000) */*FROMuser;-- 1秒超时-- 查看所有可用提示SELECT/*+ QB_NAME(qb1) */*FROMt1;五、优化器跟踪
5.1 使用optimizer_trace
-- 开启优化器跟踪SEToptimizer_trace='enabled=on';-- 执行查询SELECT*FROMuserWHEREage=25ANDname='张三';-- 查看优化过程SELECT*FROMinformation_schema.OPTIMIZER_TRACE\G-- 关闭跟踪SEToptimizer_trace='enabled=off';trace信息解读:
{"steps":[{"join_preparation":{"select_id":1,"steps":[{"expanded_query":"/* 展开后的查询 */"}]}},{"join_optimization":{"select_id":1,"steps":[{"condition_processing":{/* 条件优化过程 */}},{"table_dependencies":[/* 表依赖关系 */]},{"rows_estimation":[/* 行数估算 */{"table":"user","range_analysis":{"potential_range_indexes":[/* 可能使用的索引 */],"analyzing_range_alternatives":{/* 分析每个索引的成本 */"range_scan_alternatives":[{"index":"idx_age","ranges":["25 <= age <= 25"],"rows":100,"cost":121}]},"chosen_range_access_summary":{/* 选择的索引 */"range_access_plan":{"type":"range_scan","index":"idx_age","rows":100,"cost":121}}}}]},{"considered_execution_plans":[/* 考虑的执行计划 */{"plan_prefix":[],"table":"user","best_access_path":{/* 最佳访问路径 */}}]},{"attaching_conditions_to_tables":{/* 附加条件到表 */}}]}},{"join_execution":{/* 执行阶段 */}}]}5.2 使用EXPLAIN详细分析
-- 传统EXPLAINEXPLAINSELECT*FROMuserWHEREage=25;-- 格式化输出(MySQL 8.0+)EXPLAINFORMAT=TREESELECT*FROMuserWHEREage=25;EXPLAINFORMAT=JSONSELECT*FROMuserWHEREage=25;-- 查看实际执行统计(MySQL 8.0.18+)EXPLAINANALYZESELECT*FROMuserWHEREage=25;EXPLAIN关键字段详解:
| 字段 | 说明 | 重要值 |
|---|---|---|
| id | 查询序列号 | 数字越大越先执行 |
| select_type | 查询类型 | SIMPLE, PRIMARY, SUBQUERY, DERIVED |
| table | 表名 | 实际表名或别名 |
| partitions | 分区 | 匹配的分区 |
| type | 访问类型 | system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能的索引 | 候选索引列表 |
| key | 实际索引 | 实际使用的索引 |
| key_len | 索引长度 | 使用的索引字节数 |
| ref | 引用 | 与索引比较的列 |
| rows | 扫描行数 | 预估扫描的行数 |
| filtered | 过滤百分比 | 满足条件的行百分比 |
| Extra | 额外信息 | Using index, Using where, Using filesort等 |
type类型详解:
-- system: 表只有一行(系统表)-- const: 通过主键或唯一索引查询,最多返回一行EXPLAINSELECT*FROMuserWHEREid=1;-- eq_ref: 唯一索引扫描,用于JOINEXPLAINSELECT*FROMt1JOINt2ONt1.id=t2.id;-- ref: 非唯一索引扫描EXPLAINSELECT*FROMuserWHEREage=25;-- range: 范围扫描EXPLAINSELECT*FROMuserWHEREageBETWEEN20AND30;-- index: 全索引扫描EXPLAINSELECTidFROMuser;-- ALL: 全表扫描(最差)EXPLAINSELECT*FROMuserWHEREname='张三';-- name无索引六、优化器常见问题
6.1 优化器选错索引
原因:
- 统计信息不准确
- 成本估算偏差
- 数据分布不均匀
解决方案:
-- 1. 更新统计信息ANALYZETABLEuser;-- 2. 使用索引提示SELECT*FROMuserFORCEINDEX(idx_age)WHEREage=25;-- 3. 调整优化器参数SEToptimizer_search_depth=5;-- 控制JOIN搜索深度SEToptimizer_prune_level=1;-- 启用优化器剪枝-- 4. 修改索引或查询-- 例如:添加更合适的组合索引6.2 JOIN顺序不优
-- 查看JOIN顺序EXPLAINFORMAT=TREESELECT*FROMlarge_table t1JOINsmall_table t2ONt1.id=t2.large_id;-- 如果顺序不对,使用STRAIGHT_JOINSELECT*FROMsmall_table t2 STRAIGHT_JOIN large_table t1ONt1.id=t2.large_id;6.3 子查询性能差
-- ❌ 相关子查询(每行都执行一次)SELECT*FROMt1WHERE(SELECTCOUNT(*)FROMt2WHEREt2.t1_id=t1.id)>5;-- ✅ 改写为JOINSELECTt1.*FROMt1JOIN(SELECTt1_idFROMt2GROUPBYt1_idHAVINGCOUNT(*)>5)t2ONt1.id=t2.t1_id;-- ✅ 或使用EXISTSSELECT*FROMt1WHEREEXISTS(SELECT1FROMt2WHEREt2.t1_id=t1.idGROUPBYt1_idHAVINGCOUNT(*)>5);七、优化器最佳实践
7.1 定期维护
-- 1. 定期更新统计信息ANALYZETABLEuser;-- 2. 优化表(重建索引,回收空间)OPTIMIZETABLEuser;-- 3. 检查表CHECKTABLEuser;-- 4. 修复表REPAIRTABLEuser;7.2 监控慢查询
-- 开启慢查询日志SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=1;-- 1秒SETGLOBALlog_queries_not_using_indexes=ON;-- 查看慢查询日志位置SHOWVARIABLESLIKE'slow_query_log_file';-- 分析慢查询日志(使用mysqldumpslow工具)-- mysqldumpslow -s t -t 10 /path/to/slow.log7.3 使用性能监控
-- Performance SchemaSELECT*FROMperformance_schema.events_statements_summary_by_digestORDERBYSUM_TIMER_WAITDESCLIMIT10;-- 查看索引使用情况SELECT*FROMsys.schema_unused_indexes;SELECT*FROMsys.schema_redundant_indexes;7.4 版本升级建议
- MySQL 5.7: 引入成本模型,优化器改进
- MySQL 8.0: Hash Join, CTE, Window Function, Invisible Index
- MySQL 8.0.18+: EXPLAIN ANALYZE(实际执行统计)
- MySQL 8.0.20+: Hash Join默认启用
-- 查看MySQL版本SELECTVERSION();-- 查看优化器特性SHOWVARIABLESLIKE'optimizer_switch';八、总结
MySQL优化器是一个复杂的系统,理解其工作原理有助于:
- 编写更高效的SQL
- 设计合理的索引
- 排查性能问题
- 合理使用优化器提示
核心要点:
- 优化器基于成本模型选择执行计划
- 依赖准确的统计信息
- 需要定期维护(ANALYZE TABLE)
- 使用EXPLAIN分析执行计划
- 谨慎使用优化器提示
- 关注MySQL版本新特性
