MySQL查询优化器(QueryOptimizer)的使用小结

一、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 * FROM mysql.server_cost; SELECT * FROM mysql.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 统计信息

优化器依赖表和索引的统计信息做决策。

-- 查看表统计信息 SHOW TABLE STATUS LIKE 'table_name'\G -- 查看索引统计信息 SHOW INDEX FROM table_name; -- 关键统计指标: -- Cardinality: 索引中唯一值的数量(区分度) -- Rows: 表中的行数 -- Data_length: 数据文件大小 -- Index_length: 索引文件大小 -- 更新统计信息 ANALYZE TABLE table_name;

统计信息采样:

-- InnoDB统计信息采样设置 SHOW VARIABLES LIKE 'innodb_stats%'; -- innodb_stats_persistent: 持久化统计信息(ON/OFF) -- innodb_stats_auto_recalc: 自动重新计算统计信息 -- innodb_stats_sample_pages: 采样页数(默认8)

三、优化器的优化策略

3.1 条件简化和优化

常量传播:

-- 原始SQL SELECT * FROM t WHERE a = 5 AND b = a; -- 优化后 SELECT * FROM t WHERE a = 5 AND b = 5;

恒等式消除:

-- 原始SQL SELECT * FROM t WHERE a > 3 AND a > 5; -- 优化后 SELECT * FROM t WHERE a > 5;

范围合并:

-- 原始SQL SELECT * FROM t WHERE (a > 1 AND a < 5) OR (a > 3 AND a < 7); -- 优化后 SELECT * FROM t WHERE a > 1 AND a < 7;

3.2 索引选择

优化器通过以下步骤选择索引:

1. 找出所有可能的索引:

EXPLAIN SELECT * FROM user WHERE age = 25 AND name = '张三'; -- possible_keys 显示所有可能使用的索引

2. 计算每个索引的成本:

-- 成本计算公式(简化版):
成本 = (扫描的数据页数 × I/O成本) + (处理的记录数 × CPU成本)

3. 选择成本最低的索引

示例分析:

-- 表结构 CREATE TABLE user ( id INT PRIMARY KEY, age INT, name VARCHAR(50), city VARCHAR(50), INDEX idx_age(age), INDEX idx_name(name), INDEX idx_age_name(age, name) ); -- 查询1: 优化器会选择 idx_age_name(覆盖索引) EXPLAIN SELECT age, name FROM user WHERE age = 25; -- 查询2: 如果需要所有字段,可能选择 idx_age(需要回表) EXPLAIN SELECT * FROM user WHERE age = 25; -- 使用 optimizer_trace 查看详细过程 SET optimizer_trace='enabled=on'; SELECT * FROM user WHERE age = 25; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace='enabled=off';

3.3 JOIN优化

连接顺序优化:

-- 三表连接 SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id JOIN t3 ON t2.id = t3.t2_id WHERE t1.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 row in t1: for each row in t2: if row matches join condition: output row -- 时间复杂度: O(n * m)

索引嵌套循环(Index Nested-Loop Join)

-- 使用索引加速内表查询 for each row in t1: use index to find matching rows in t2 output matched rows -- 时间复杂度: O(n * log m)

块嵌套循环(Block Nested-Loop Join)

-- 使用join buffer缓存外表数据 -- MySQL 8.0+ 使用Hash Join替代 -- 查看join buffer大小 SHOW VARIABLES LIKE 'join_buffer_size';

Hash Join(MySQL 8.0.18+)

-- 构建哈希表,性能更好 -- 适用于等值连接 EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id; -- 可以看到 "Hash Join" 字样

JOIN优化建议:

-- ✅ 小表驱动大表 SELECT * FROM small_table t1 JOIN large_table t2 ON t1.id = t2.small_id; -- ✅ 确保JOIN字段有索引 ALTER TABLE t2 ADD INDEX idx_small_id(small_id); -- ✅ 使用STRAIGHT_JOIN强制连接顺序(谨慎使用) SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.id = t2.t1_id;

3.4 子查询优化

子查询转换策略:

1. 子查询物化(Subquery Materialization):

-- 原始SQL SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2 WHERE status = 1); -- 优化过程: -- 1. 先执行子查询,结果存入临时表 -- 2. 临时表加索引 -- 3. 用临时表进行JOIN -- EXPLAIN 中看到 "MATERIALIZED"

2. 子查询转JOIN:

-- 原始SQL(相关子查询) SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.t1_id = t1.id); -- 优化后(Semi-Join) SELECT t1.* FROM t1 SEMI JOIN t2 ON t2.t1_id = t1.id;

3. 子查询展开:

-- 原始SQL SELECT * FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.t1_id = t1.id) > 5; -- 优化后 SELECT t1.* FROM t1 JOIN ( SELECT t1_id, COUNT(*) as cnt FROM t2 GROUP BY t1_id HAVING cnt > 5 ) t2 ON t1.id = t2.t1_id;

控制子查询优化:

-- 查看子查询优化策略 SHOW VARIABLES LIKE 'optimizer_switch'; -- 关键参数: -- materialization: 子查询物化 -- semijoin: 半连接优化 -- subquery_materialization_cost_based: 基于成本选择

3.5 ORDER BY 和 GROUP BY 优化

索引排序 vs 文件排序:

-- ✅ 使用索引排序(Index Scan) -- 假设有索引 idx_age(age) EXPLAIN SELECT * FROM user ORDER BY age; -- Extra: Using index -- ❌ 使用文件排序(Filesort) EXPLAIN SELECT * FROM user ORDER BY name; -- Extra: Using filesort -- Filesort过程: -- 1. 根据WHERE条件读取数据 -- 2. 将需要排序的字段放入sort buffer -- 3. 如果数据量大于sort_buffer_size,使用磁盘临时文件 -- 4. 进行排序(快速排序)

GROUP BY优化:

-- ✅ 松散索引扫描(Loose Index Scan) -- 假设索引 idx_age_city(age, city) EXPLAIN SELECT age, COUNT(*) FROM user GROUP BY age; -- Extra: Using index for group-by -- ✅ 紧凑索引扫描(Tight Index Scan) EXPLAIN SELECT age, city, COUNT(*) FROM user WHERE age > 20 GROUP BY age, city; -- Extra: Using where; Using index -- ❌ 临时表分组 EXPLAIN SELECT city, COUNT(*) FROM user GROUP BY city; -- Extra: Using temporary; Using filesort

优化配置:

-- 查看排序缓冲区大小 SHOW VARIABLES LIKE 'sort_buffer_size'; -- 默认256KB SHOW VARIABLES LIKE 'max_length_for_sort_data'; -- 默认1024 -- 临时表相关 SHOW VARIABLES LIKE 'tmp_table_size'; -- 内存临时表大小 SHOW VARIABLES LIKE 'max_heap_table_size'; -- 堆表最大值

四、优化器提示(Hint)

4.1 索引提示

-- 强制使用某个索引 SELECT * FROM user FORCE INDEX(idx_age) WHERE age = 25; -- 建议使用某个索引(优化器可能忽略) SELECT * FROM user USE INDEX(idx_age) WHERE age = 25; -- 忽略某个索引 SELECT * FROM user IGNORE INDEX(idx_age) WHERE age = 25; -- MySQL 8.0+ 新语法 SELECT /*+ INDEX(user idx_age) */ * FROM user WHERE age = 25;

4.2 JOIN提示

-- 强制连接顺序 SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.id = t2.t1_id; -- MySQL 8.0+ JOIN提示 SELECT /*+ JOIN_ORDER(t1, t2, t3) */ * FROM t1, t2, t3 WHERE t1.id = t2.t1_id AND t2.id = t3.t2_id; -- 指定JOIN算法 SELECT /*+ BNL(t1, t2) */ * -- Block Nested-Loop FROM t1 JOIN t2 ON t1.id = t2.id; SELECT /*+ HASH_JOIN(t1, t2) */ * -- Hash Join(8.0.18+) FROM t1 JOIN t2 ON t1.id = t2.id;

4.3 其他提示

-- 子查询物化 SELECT /*+ SUBQUERY(MATERIALIZATION) */ * FROM t1 WHERE id IN (SELECT t1_id FROM t2); -- 指定临时表使用内存 SELECT /*+ SET_VAR(internal_tmp_mem_storage_engine=TempTable) */ age, COUNT(*) FROM user GROUP BY age; -- 限制执行时间(8.0+) SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM user; -- 1秒超时 -- 查看所有可用提示 SELECT /*+ QB_NAME(qb1) */ * FROM t1;

五、优化器跟踪

5.1 使用optimizer_trace

-- 开启优化器跟踪 SET optimizer_trace='enabled=on'; -- 执行查询 SELECT * FROM user WHERE age = 25 AND name = '张三'; -- 查看优化过程 SELECT * FROM information_schema.OPTIMIZER_TRACE\G -- 关闭跟踪 SET optimizer_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详细分析

-- 传统EXPLAIN EXPLAIN SELECT * FROM user WHERE age = 25; -- 格式化输出(MySQL 8.0+) EXPLAIN FORMAT=TREE SELECT * FROM user WHERE age = 25; EXPLAIN FORMAT=JSON SELECT * FROM user WHERE age = 25; -- 查看实际执行统计(MySQL 8.0.18+) EXPLAIN ANALYZE SELECT * FROM user WHERE age = 25;

EXPLAIN关键字段详解:

type类型详解:

-- system: 表只有一行(系统表) -- const: 通过主键或唯一索引查询,最多返回一行 EXPLAIN SELECT * FROM user WHERE id = 1; -- eq_ref: 唯一索引扫描,用于JOIN EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.id = t2.id; -- ref: 非唯一索引扫描 EXPLAIN SELECT * FROM user WHERE age = 25; -- range: 范围扫描 EXPLAIN SELECT * FROM user WHERE age BETWEEN 20 AND 30; -- index: 全索引扫描 EXPLAIN SELECT id FROM user; -- ALL: 全表扫描(最差) EXPLAIN SELECT * FROM user WHERE name = '张三'; -- name无索引

六、优化器常见问题

6.1 优化器选错索引

原因:

解决方案:

-- 1. 更新统计信息 ANALYZE TABLE user; -- 2. 使用索引提示 SELECT * FROM user FORCE INDEX(idx_age) WHERE age = 25; -- 3. 调整优化器参数 SET optimizer_search_depth = 5; -- 控制JOIN搜索深度 SET optimizer_prune_level = 1; -- 启用优化器剪枝 -- 4. 修改索引或查询 -- 例如:添加更合适的组合索引

6.2 JOIN顺序不优

-- 查看JOIN顺序 EXPLAIN FORMAT=TREE SELECT * FROM large_table t1 JOIN small_table t2 ON t1.id = t2.large_id; -- 如果顺序不对,使用STRAIGHT_JOIN SELECT * FROM small_table t2 STRAIGHT_JOIN large_table t1 ON t1.id = t2.large_id;

6.3 子查询性能差

-- ❌ 相关子查询(每行都执行一次) SELECT * FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.t1_id = t1.id) > 5; -- ✅ 改写为JOIN SELECT t1.* FROM t1 JOIN ( SELECT t1_id FROM t2 GROUP BY t1_id HAVING COUNT(*) > 5 ) t2 ON t1.id = t2.t1_id; -- ✅ 或使用EXISTS SELECT * FROM t1 WHERE EXISTS ( SELECT 1 FROM t2 WHERE t2.t1_id = t1.id GROUP BY t1_id HAVING COUNT(*) > 5 );

七、优化器最佳实践

7.1 定期维护

-- 1. 定期更新统计信息 ANALYZE TABLE user; -- 2. 优化表(重建索引,回收空间) OPTIMIZE TABLE user; -- 3. 检查表 CHECK TABLE user; -- 4. 修复表 REPAIR TABLE user;

7.2 监控慢查询

-- 开启慢查询日志 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 1秒 SET GLOBAL log_queries_not_using_indexes = ON; -- 查看慢查询日志位置 SHOW VARIABLES LIKE 'slow_query_log_file'; -- 分析慢查询日志(使用mysqldumpslow工具) -- mysqldumpslow -s t -t 10 /path/to/slow.log

7.3 使用性能监控

-- Performance Schema SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10; -- 查看索引使用情况 SELECT * FROM sys.schema_unused_indexes; SELECT * FROM sys.schema_redundant_indexes;

7.4 版本升级建议

-- 查看MySQL版本 SELECT VERSION(); -- 查看优化器特性 SHOW VARIABLES LIKE 'optimizer_switch';

八、总结

MySQL优化器是一个复杂的系统,理解其工作原理有助于:

编写更高效的SQL设计合理的索引排查性能问题合理使用优化器提示

核心要点:

到此这篇关于MySQL 查询优化器 (Query Optimizer) 的使用小结的文章就介绍到这了,