MySQL 查询优化器 (Query Optimizer) 详解-Seo优化-合肥市网站建设公司

一、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.log

7.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版本新特性

MySQL 查询优化器 (Query Optimizer) 详解