我要评论一、数据库故障的关键点
引起数据库故障的因素有操作系统层面、存储层面，还有断电断网的基础环境层面（以下称为外部因素），以及应用程序操作数据库和人为操作数据库这两个层面（以下称内部因素）。这些故障中外部因素发生的概率较小，可能几年都未发生过一起。许多 dba 从入职到离职都可能没有遇到过外部因素导致的故障，但是内部因素导致的故障可能每天都在产生。内部因素中占据主导的是应用程序导致的故障，可以说数据库故障的主要元凶就是应用程序的 sql 写的不够好。
sql 是由开发人员编写的，但是责任不完全是开发人员的。
- sql 的成因：sql 是为了实现特定的需求而编写的，那么需求的合理性是第一位的。一般来说，在合理的需求下即使有问题的 sql 也是可以挽救的。但是如果需求不合理，那么就为 sql 问题埋下了隐患。
- sql 的设计：这里的设计主要是数据库对象的设计。即使是合理的需求，椰果在数据库设计层面没有把控的缓解或者保证，那么很多优化就会大打折扣。
- sql 的实现：这部分是开发人员所涉及的工作。但是这已经是流程的末端，这个时候改善的手段依然有，但是属于挽救措施。
在笔者多年的工作中，数据库故障主要来源于三个方面：不合理的需求、不合理的设计和不合理的实现。而这些如果从管理和流程上明确规定由经验丰富的 dba 介入，那么对数据库故障的源头是有很大的控制作用的。
上述摘自薛晓刚老师的 《dba 实战手记》 3.2 节。
二、慢 sql 的常见成因
在分析 sql 语句时，sql 运行缓慢绝对是最主要的问题，没有之一。慢 sql 是数据库性能瓶颈的主要表现，其核心成因可归纳为以下几类：
- 索引问题：缺失索引导致全表扫描、索引失效（如函数操作索引列、隐式类型转换）、索引设计不合理（单值索引 vs 复合索引选择错误）。
- 查询写法缺陷：select * 全字段查询、复杂子查询嵌套、无过滤条件的大范围扫描、低效 join 操作。
- 数据量与结构：表数据量过大未分区、字段类型设计不合理（如用 varchar 存储数字）、大字段（text/blob）频繁查询。
- 执行计划异常：优化器误判（统计信息过时）、join 顺序错误、临时表与文件排序滥用。
下面通过实例表和纯 sql 生成的数据，结合执行计划工具详解优化方法。
三、实验表结构及数据
1. 表结构（电商场景）
-- 用户表 create table users ( user_id int auto_increment primary key, username varchar(50) not null, email varchar(100) unique, age int, register_time datetime, index idx_age (age), index idx_register_time (register_time) ) engine=innodb; -- 商品表 create table products ( product_id int auto_increment primary key, product_name varchar(100) not null, price decimal(10,2), category_id int, stock int, index idx_category (category_id), index idx_name_price (product_name, price) ) engine=innodb; -- 订单表 create table orders ( order_id int auto_increment primary key, user_id int not null, product_id int not null, order_time datetime, amount decimal(10,2), status tinyint, -- 1:待支付 2:已支付 3:已取消 index idx_user_time (user_id, order_time), index idx_product_id (product_id) ) engine=innodb;
2. 生成测试数据（存储过程）
生成用户数据（10 万条）
delimiter //
create procedure prod_generate_users()
begin
declare i int default 1;
declare batch_size int default 1000; -- 每批处理1000条记录
declare total int default 100000; -- 总记录数
while i <= total do
start transaction;
-- 插入当前批次的记录
while i <= total and i <= (batch_size * floor((i-1)/batch_size) + batch_size) do
insert into users (username, email, age, register_time)
values (
concat('user_', i),
concat('user_', i, '@example.com'),
floor(rand() * 40) + 18,
date_sub(now(), interval floor(rand() * 3650) day)
);
set i = i + 1;
end while;
commit;
end while;
end //
delimiter ;
-- 执行存储过程
call prod_generate_users();
生成商品数据 1 万条）
delimiter //
create procedure prod_generate_products()
begin
declare i int default 1;
declare batch_size int default 1000; -- 每批处理1000条记录
declare total int default 10000; -- 总记录数
while i <= total do
start transaction;
-- 插入当前批次的记录
while i <= total and i <= (batch_size * floor((i-1)/batch_size) + batch_size) do
insert into products (product_name, price, category_id, stock)
values (
concat('product_', i),
round(rand() * 999 + 1, 2), -- 1-1000元
floor(rand() * 20) + 1, -- 1-20类分类
floor(rand() * 1000) + 10 -- 10-1009库存
);
set i = i + 1;
end while;
commit;
end while;
end //
delimiter ;
call prod_generate_products();
生成订单数据（100 万条）
delimiter //
create procedure prod_generate_orders()
begin
declare i int default 1;
declare batch_size int default 500; -- 每批处理500条记录（订单数据量大，批次更小）
declare total int default 1000000; -- 总记录数
declare max_user int;
declare max_product int;
declare rand_product_id int;
declare product_price decimal(10,2);
-- 获取用户和商品的最大id
select max(user_id) into max_user from users;
select max(product_id) into max_product from products;
while i <= total do
start transaction;
-- 插入当前批次的记录
while i <= total and i <= (batch_size * floor((i-1)/batch_size) + batch_size) do
-- 优化：预先计算随机商品id和价格，避免子查询
set rand_product_id = floor(rand() * max_product) + 1;
select price into product_price from products where product_id = rand_product_id limit 1;
insert into orders (user_id, product_id, order_time, amount, status)
values (
floor(rand() * max_user) + 1, -- 随机用户
rand_product_id, -- 随机商品
date_sub(now(), interval floor(rand() * 365) day), -- 近1年订单
product_price * (floor(rand() * 5) + 1), -- 1-5件数量
floor(rand() * 3) + 1 -- 随机状态
);
set i = i + 1;
end while;
commit;
end while;
end //
delimiter ;
call prod_generate_orders();
查看数据
select count(1) from users union all select count(1) from products union all select count(1) from orders; +----------+ | count(1) | +----------+ | 100000 | | 10000 | | 1000000 | +----------+
四、执行计划详解：从分析到优化完整指南
三类工具的选择指南
| 工具 | 核心价值 | 适用场景 |
|---|---|---|
| explain | 快速预判执行计划（预估） | 日常开发、索引设计验证、排查明显低效操作 |
| optimizer_trace | 深入优化器决策过程（分析“为什么这么做”） | 复杂查询的索引选择问题、join 顺序优化 |
| explain analyze | 量化实际执行性能（精确耗时、行数） | 性能瓶颈量化、优化效果对比、分页/排序分析 |
通过这三类工具的组合使用，可从“预判”到“分析”再到“量化”，全面掌握 mysql 查询的执行逻辑，精准定位并解决性能问题。
1. 基础分析工具：explain——预判查询执行逻辑
explain是 mysql 中最常用的执行计划分析工具，无需实际执行查询，即可返回优化器对查询的执行方案（如索引选择、扫描方式等），帮助提前发现性能隐患。
核心语法与使用场景
-- 对任意select查询执行分析 explain select 列名 from 表名 where 条件; -- 支持复杂查询（join、子查询等） explain select u.username, o.order_id from users u join orders o on u.user_id = o.user_id where u.age > 30; +----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+ | 1 | simple | u | null | all | primary,idx_age | null | null | null | 99864 | 50.00 | using where | | 1 | simple | o | null | ref | idx_user_time_amount | idx_user_time_amount | 4 | testdb.u.user_id | 9 | 100.00 | using index | +----+-------------+-------+------------+------+----------------------+----------------------+---------+------------------+-------+----------+-------------+ 2 rows in set, 1 warning (0.14 sec)
适用场景：
- 快速判断查询是否使用索引、是否存在全表扫描；
- 分析 join 语句的表连接顺序和连接方式；
- 定位
using filesort（文件排序）、using temporary（临时表）等低效操作。
字段深度解读
在 mysql 的 explain 执行计划中，除了 type、key、rows、extra 这几个核心字段外，其他字段也承载着查询执行逻辑的关键信息。以下是 explain 所有字段的完整含义：
| 字段名 | 核心含义 | 补充说明 |
|---|---|---|
| id | 查询中每个操作的唯一标识（多表/子查询时用于区分执行顺序）。 | - 若 id 相同：表示操作在同一层级，按表的顺序（从左到右）执行（如 join 时的驱动表和被驱动表）。 - 若 id 不同：id 越大优先级越高，先执行（如子查询会嵌套在主查询内部，id 更大）。 |
| select_type | 查询的类型（区分简单查询、子查询、联合查询等）。 | 常见值： - simple：简单查询（无子查询、join 等复杂结构）。 - primary：主查询（包含子查询时，最外层的查询）。 - subquery：子查询（select 中的子查询，不依赖外部结果）。 - derived：衍生表（from 中的子查询，会生成临时表）。 - union：union 语句中第二个及以后的查询。 - union result：union 结果集的合并操作。 |
| table | 当前操作涉及的表名（或临时表别名，如 derived2 表示衍生表）。 | 若为 null：可能是 union result（合并结果集时无具体表），或子查询的中间结果。 |
| partitions | 查询匹配的分区（仅对分区表有效）。 | 非分区表显示 null；分区表会显示匹配的分区名称（如 p2023 表示命中 p2023 分区）。 |
| type | 访问类型（索引使用效率等级，最关键的性能指标）。 | 详见前文“type 字段优先级与解读”，从优到差反映索引利用效率（如 const > ref > all）。 |
| possible_keys | 优化器认为可能使用的索引（候选索引列表）。 | 该字段仅表示“可能有效”的索引，不代表实际使用；若为 null，说明没有可用索引。 |
| key | 实际使用的索引（null 表示未使用任何索引）。 | 若 possible_keys 有值但 key 为 null，可能是索引选择性差（如字段值重复率高）或优化器判断全表扫描更快。 |
| key_len | 实际使用的索引长度（字节数）。 | 用于判断复合索引的使用情况： - 若 key_len 等于复合索引总长度，说明整个索引被使用； - 若较短，说明仅使用了复合索引的前缀部分（需检查是否因类型不匹配导致索引截断，如字符串未指定长度）。 |
| ref | 表示哪些字段或常量被用来匹配索引列。 | - 若为常量（如 const）：表示用固定值匹配索引（如 where id=1）。 - 若为表名.字段（如 u.user_id）：表示用其他表的字段关联当前表的索引（如 join 时的关联条件）。 |
| rows | 优化器预估的扫描行数（基于表统计信息估算）。 | 数值越小越好，反映查询的“工作量”；若远大于实际数据量，可能是统计信息过时，需执行 analyze table 表名 更新。 |
| filtered | 经过过滤条件后，剩余记录占预估扫描行数的比例（百分比）。 | 如 filtered=50 表示扫描的 rows 中，有 50% 满足过滤条件；值越高，说明过滤效率越好（减少后续处理的数据量）。 |
| extra | 额外的执行细节（补充说明索引使用、排序、临时表等特殊行为）。 | 包含大量关键信息，如 using filesort（文件排序）、using temporary（临时表）等，是优化的核心线索（详见前文）。 |
type字段优先级与解读（从优到差）
| type值 | 含义 | 性能影响 |
|---|---|---|
| system | 表中只有 1 行数据（如系统表），无需扫描 | 理想状态，仅特殊场景出现。 |
| const | 通过主键/唯一索引匹配 1 行数据（如where id=1） | 高效，索引精确匹配，推荐。 |
| eq_ref | 多表 join 时，被驱动表通过主键/唯一索引匹配，每行只返回 1 条数据 | 高效，适合关联查询（如orders.user_id关联users.user_id主键）。 |
| ref | 非唯一索引匹配，可能返回多行（如where age=30，age为普通索引） | 较好，索引部分匹配，需关注返回行数。 |
| range | 索引范围扫描（如between、in、>等） | 中等，比全表扫描高效，适合范围查询（需确保索引覆盖条件）。 |
| index | 扫描整个索引树（未命中索引过滤条件，仅用索引排序/覆盖） | 低效，相当于“索引全表扫描”（如select id from users，id为主键但无过滤）。 |
| all | 全表扫描（未使用任何索引） | 极差，大表中会导致查询超时，必须优化。 |
extra字段关键值解读
| extra值 | 含义 | 优化方向 |
|---|---|---|
| using where | 使用where条件过滤，但未使用索引（全表扫描后过滤） | 为过滤字段创建索引。 |
| using index | 索引覆盖扫描（查询字段均在索引中，无需回表查数据） | 理想状态，说明索引设计合理（如select user_id from orders使用user_id索引）。 |
| using where; using index | 既用索引过滤，又用索引覆盖 | 最优状态，索引同时满足过滤和查询需求。 |
| using filesort | 无法通过索引排序，需在内存/磁盘中排序（大结果集极慢） | 优化排序字段，创建“过滤+排序”复合索引（如where status=1 order by time需(status, time)索引）。 |
| using temporary | 需要创建临时表存储中间结果（如group by非索引字段） | 避免在大表上使用group by非索引字段，或创建包含分组字段的复合索引。 |
| using join buffer | 多表 join 未使用索引，通过连接缓冲区匹配 | 为 join 条件字段创建索引（如on u.user_id = o.user_id，需o.user_id索引）。 |
案例：从explain结果到优化
需求：查询年龄 30-40 岁的用户用户名和邮箱。
原始查询：
explain select username, email from users where age between 30 and 40;
执行计划结果（问题版）：
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | simple | users | null | all | idx_age | null | null | null | 99776 | 47.03 | using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
问题分析：
type=all：全表扫描，未使用索引；possible_keys=idx_age但key=null：索引存在但未被使用（可能因统计信息过时或索引选择性差）。
优化步骤：
- 确认索引是否有效：
show index from users where key_name='idx_age';（若不存在则创建）； - 更新表统计信息：
analyze table users;（让优化器获取准确数据分布）； - 优化后预期结果：
type=range，key=idx_age，extra=using where; using index（若username和email不在索引中，至少实现range扫描）。
2. 深入优化工具：optimizer_trace——揭秘优化器决策过程
explain只能展示执行计划的“结果”，而optimizer_trace可以展示优化器生成计划的“过程”（如索引选择的权衡、成本计算、join 顺序决策等），适合分析复杂查询的深层性能问题。
核心作用与适用场景
- 分析“明明有索引却不用”的原因（优化器认为全表扫描成本更低？）；
- 对比不同索引的成本差异，指导索引设计；
- 解读 join 语句中表连接顺序的决策逻辑（为什么 a 表驱动 b 表而不是相反？）。
使用步骤与注意事项
-- 默认是关闭的
show global variables like 'optimizer_trace';
+-----------------+--------------------------+
| variable_name | value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
-- 开启跟踪（修改当前会话）
set optimizer_trace = "enabled=on";
-- 仅影响当前会话（看global全局还是off的）
show session variables like 'optimizer_trace';
+-----------------+-------------------------+
| variable_name | value |
+-----------------+-------------------------+
| optimizer_trace | enabled=on,one_line=off |
+-----------------+-------------------------+
-- 执行目标查询
select * from orders where user_id = 100 and order_time > '2024-01-01';
-- 查看跟踪结果
select * from information_schema.optimizer_trace\g
*************************** 1. row ***************************
query: select * from orders where user_id = 100 and order_time > '2024-01-01'
trace: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `orders`.`order_id` as `order_id`,`orders`.`user_id` as `user_id`,`orders`.`product_id` as `product_id`,`orders`.`order_time` as `order_time`,`orders`.`amount` as `amount`,`orders`.`status` as `status` from `orders` where ((`orders`.`user_id` = 100) and (`orders`.`order_time` > '2024-01-01'))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "where",
"original_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > '2024-01-01'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`orders`.`order_time` > '2024-01-01') and multiple equal(100, `orders`.`user_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`orders`.`order_time` > '2024-01-01') and multiple equal(100, `orders`.`user_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`orders`.`order_time` > timestamp'2024-01-01 00:00:00') and multiple equal(100, `orders`.`user_id`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`orders`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`orders`",
"field": "user_id",
"equals": "100",
"null_rejecting": true
}
]
},
{
"rows_estimation": [
{
"table": "`orders`",
"range_analysis": {
"table_scan": {
"rows": 925560,
"cost": 93207.1
},
"potential_range_indexes": [
{
"index": "primary",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_user_time",
"usable": true,
"key_parts": [
"user_id",
"order_time",
"order_id"
]
},
{
"index": "idx_product_id",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_user_time",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_user_time",
"ranges": [
"user_id = 100 and '2024-01-01 00:00:00' < order_time"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 10,
"cost": 3.76,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_user_time",
"rows": 10,
"ranges": [
"user_id = 100 and '2024-01-01 00:00:00' < order_time"
]
},
"rows_for_plan": 10,
"cost_for_plan": 3.76,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`orders`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_user_time",
"chosen": false,
"cause": "range_uses_more_keyparts"
},
{
"rows_to_scan": 10,
"access_type": "range",
"range_details": {
"used_index": "idx_user_time"
},
"resulting_rows": 10,
"cost": 4.76,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 10,
"cost_for_plan": 4.76,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > timestamp'2024-01-01 00:00:00'))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`orders`",
"attached": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > timestamp'2024-01-01 00:00:00'))"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "`orders`",
"original_table_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > timestamp'2024-01-01 00:00:00'))",
"final_table_condition ": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > timestamp'2024-01-01 00:00:00'))"
}
]
},
{
"refine_plan": [
{
"table": "`orders`",
"pushed_index_condition": "((`orders`.`user_id` = 100) and (`orders`.`order_time` > timestamp'2024-01-01 00:00:00'))",
"table_condition_attached": null
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
missing_bytes_beyond_max_mem_size: 0
insufficient_privileges: 0
-- 关闭跟踪（避免性能消耗）
set optimizer_trace = "enabled=off";
注意事项：
- 仅在分析复杂查询时使用，开启后会增加 cpu 和内存消耗；
- 结果中
missing_bytes_beyond_max_mem_size>0表示内容被截断，需调大optimizer_trace_max_mem_size（默认 1mb）； - 需
process权限才能查看information_schema.optimizer_trace。
关键结果解读
从 json 结果中重点关注以下部分：
rows_estimation：优化器对各表行数的估算（若与实际偏差大，需更新统计信息）；potential_range_indexes：优化器考虑的所有候选索引（包括未被选中的）；analyzing_range_alternatives：各索引的成本对比（cost字段，值越小越优）；chosen_range_access_summary：最终选择的索引及原因（如cost=3.76的索引被选中）。
示例解读：
若potential_range_indexes中包含idx_user_time，但chosen_range_access_summary未选中，需查看cost是否高于全表扫描（可能因索引选择性差，优化器认为全表扫描更快），此时需优化索引（如增加区分度更高的前缀字段）。
3. 精准量化工具：explain analyze（mysql 8.0+）——实测执行性能
explain analyze是 mysql 8.0 引入的增强功能，会实际执行查询，并返回精确的执行时间、扫描行数等 metrics，适合量化性能瓶颈（如排序耗时、扫描行数与预期的偏差）。
核心优势与使用场景
- 对比
explain：explain返回“预估”值，explain analyze返回“实际”值（如actual time、actual rows）； - 适合分析分页查询（
limit offset, size）、排序、join 等操作的真实耗时； - 量化索引优化效果（如优化前后的执行时间对比）。
警告：
- 对大表执行
explain analyze会消耗实际资源（如全表扫描 1000 万行），生产环境谨慎使用； - 非
select语句（如update、delete）禁用，避免误操作数据。 - 会实际执行语句，因此非 dql 语句谨慎执行！！！
案例：分析分页查询性能
需求：分析“查询状态为 2 的订单，按时间排序后取第 10001-10020 条”的性能瓶颈。
查询：
explain analyze select * from orders where status = 2 order by order_time limit 10000, 20\g
未优化结果：
explain: -> limit/offset: 20/10000 row(s) (cost=93205 rows=20) (actual time=744..744 rows=20 loops=1)
-> sort: orders.order_time, limit input to 10020 row(s) per chunk (cost=93205 rows=925560) (actual time=742..743 rows=10020 loops=1)
-> filter: (orders.`status` = 2) (cost=93205 rows=925560) (actual time=3.26..591 rows=333807 loops=1)
-> table scan on orders (cost=93205 rows=925560) (actual time=3.25..505 rows=1e+6 loops=1)
关键信息解读：
总执行时间：744ms（根节点的actual time）
各阶段实际耗时：
- 表扫描（
table scan on orders）：(505ms - 3.25ms)*1 ≈ 502ms - 过滤（
filter）：(591ms - 3.26ms)*1 ≈ 588ms（包含表扫描时间） - 排序（
sort）：(743ms - 3.26ms)*1 ≈ 740ms（包含表扫描和过滤时间）
| 字段 | 含义 |
|---|---|
| cost | 优化器预估的执行成本（数值越小越好，基于 cpu 消耗、io 操作等计算） |
| rows（预估） | 优化器预估的需要处理的行数（反映查询的“工作量”，数值越小效率越高） |
| actual time | 实际执行时间（格式为 开始时间..结束时间，单位为毫秒） |
| actual rows | 实际处理的行数（反映真实数据量，与预估 rows 差异过大需关注） |
| loops | 操作执行的循环次数（非join或带子查询的sql通常为 1） |
优化方案：创建覆盖“过滤+排序”的复合索引：
create index idx_status_time on orders(status, order_time);
优化后结果：
explain: -> limit/offset: 20/10000 row(s) (cost=48225 rows=20) (actual time=48.4..48.4 rows=20 loops=1)
-> index lookup on orders using idx_status_time (status=2) (cost=48225 rows=462780) (actual time=25.3..47.9 rows=10020 loops=1)
优化效果：
- 总时间从 744ms 降至 48ms（提升 93%）；
- 消除全表扫描和文件排序，直接通过索引定位数据（
index lookup）。
五、案例 - 索引问题与表结构
1. 索引失效 - 函数操作索引列
案例 sql：
explain select count(1) from users where year(register_time) = 2025;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
| 1 | simple | users | null | index | null | idx_register_time | 6 | null | 99776 | 100.00 | using where; using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
explain select count(1) from users where register_time >= '2025-01-01' and register_time < date_add('2025-01-01', interval 1 year);
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
| 1 | simple | users | null | range | idx_register_time | idx_register_time | 6 | null | 5300 | 100.00 | using where; using index |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
执行计划问题：对索引列register_time使用了函数，导致索引失效。
优化手段：改为范围查询，不要对索引列使用函数。
2. 索引失效 - 隐式类型转换
案例 sql：
explain select count(1) from products where product_name=12345; +----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+ | 1 | simple | products | null | index | idx_name_price | idx_name_price | 408 | null | 9642 | 10.00 | using where; using index | +----+-------------+----------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+ 1 row in set, 3 warnings (0.00 sec) explain select count(1) from products where product_name='12345'; +----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | simple | products | null | ref | idx_name_price | idx_name_price | 402 | const | 1 | 100.00 | using index | +----+-------------+----------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
执行计划问题：product_name是 varchar 类型，但查询条件使用了数字类型，发生隐式类型转换导致索引失效。参考：select count(1) from products where cast(product_name as signed)=12345;
优化手段：查询条件类型与索引列字段一致。
可能有小伙伴会问，明明查出来是 0 行，怎么执行计划 rows=1 呢，实际在扫索引时 mysql 也已经知道了没有匹配的结果，理论返回 0 行就行，但 mysql 代码里写死了这种情况返回 1，他们也没有解释那就这样吧。
3. 索引设计不合理 - 单值索引 vs 复合索引选择错误
案例 sql：
-- 删掉原本的 idx_category 方便验证该案例 alter table products drop index idx_category,add index idx_price_category(price,category_id); explain select * from products where category_id = 5 and price > 900; +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+ | 1 | simple | products | null | range | idx_price_category | idx_price_category | 6 | null | 1003 | 10.00 | using index condition | +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) -- 优化索引顺序 alter table products drop index idx_price_category,add index idx_category_price(category_id,price); explain select * from products where category_id = 5 and price > 900; +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+ | 1 | simple | products | null | range | idx_category_price | idx_category_price | 11 | null | 45 | 100.00 | using index condition | +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) -- 改回去 alter table products drop index idx_category_price,add index idx_category(category_id);
执行计划问题：虽然有复合索引idx_price_category(price, category_id)，但范围查询price > 900导致索引截断。
优化手段：调整索引顺序为(category_id, price)
六、案例 - 执行计划异常与查询写法缺陷
1. 优化器误判（统计信息过时，以 products 表为例）
案例 sql：
-- 查询特定分类的商品，products表有idx_category索引 explain select * from products where category_id = 15; analyze table products; -- 更新统计信息后，执行计划会选择idx_category索引
执行计划问题：优化器误判（因统计信息过时），认为全表扫描比走idx_category快（实际category_id=15的数据量很小）。
统计信息过时的原因:
- 数据量剧变：批量插入 / 删除 / 更新超过表总量的 10%。
- 分布剧变：字段值的重复度、基数发生显著变化（如从低重复到高重复）。
- 配置限制：关闭自动更新、采样精度不足。
- 结构变更：新增索引、修改字段类型后未同步更新统计信息。
统计信息过时会直接导致优化器误判执行计划（如全表扫描 vs 索引扫描、join 顺序错误），因此需在上述场景中定期执行 analyze table 或开启自动更新（非极致写入场景）。
优化手段：更新表统计信息，帮助优化器正确判断。
2. select * 全字段查询
案例 sql：
-- 查询用户的订单记录，使用select * 导致读取不必要字段 select * from users u join orders o on u.user_id = o.user_id where u.user_id = 100; select u.username, o.order_id, o.order_time, o.amount from users u join orders o on u.user_id = o.user_id where u.user_id = 100;
问题：users表的email、register_time和orders表的status等非必要字段被读取，增加 io 和内存开销（尤其orders表数据量达 100 万条）。多余字段占用大量 io 带宽，拖慢查询。
优化手段：只查询业务需要的字段
3. 复杂子查询嵌套
案例 sql：
-- 统计2024 年每个用户的订单总金额（包含所有用户，即使无订单也显示 0）
explain analyze select
u.user_id,
u.username,
-- 子查询：统计该用户2024年的订单总金额（无订单则返回null，用ifnull转为0）
ifnull((select sum(o.amount)
from orders o
where o.user_id = u.user_id
and o.order_time >= '2024-01-01'
and o.order_time < '2025-01-01'), 0) as total_2024_amount
from users u \g;
*************************** 1. row ***************************
explain: -> table scan on u (cost=10098 rows=99776) (actual time=0.147..44.7 rows=100000 loops=1)
-> select #2 (subquery in projection; dependent)
-> aggregate: sum(o.amount) (cost=3.26 rows=1) (actual time=0.0155..0.0155 rows=1 loops=100000)
-> index lookup on o using idx_user_time (user_id=u.user_id), with index condition: ((o.order_time >= timestamp'2024-01-01 00:00:00') and (o.order_time < timestamp'2025-01-01 00:00:00')) (cost=2.36 rows=9.02) (actual time=0.0133..0.0143 rows=4.74 loops=100000)
1 row in set, 1 warning (1.87 sec)
-- 覆盖索引：包含所有查询需要的字段
alter table orders drop index idx_user_time,add index idx_user_time_amount(user_id, order_time, amount);
explain analyze select
u.user_id,
u.username,
coalesce(s.total_amount, 0) as total_2024_amount
from users u
left join (
select
user_id,
sum(amount) as total_amount
from orders
where order_time >= '2024-01-01'
and order_time < '2025-01-01'
group by user_id -- 预聚合订单数据
) s on u.user_id = s.user_id \g;
*************************** 1. row ***************************
explain: -> nested loop left join (cost=1.01e+9 rows=10e+9) (actual time=706..892 rows=100000 loops=1)
-> table scan on u (cost=10098 rows=99776) (actual time=0.175..39.2 rows=100000 loops=1)
-> index lookup on s using <auto_key0> (user_id=u.user_id) (cost=113560..113562 rows=10) (actual time=0.00809..0.00831 rows=0.988 loops=100000)
-> materialize (cost=113559..113559 rows=100725) (actual time=706..706 rows=98795 loops=1)
-> group aggregate: sum(orders.amount) (cost=103487 rows=100725) (actual time=1.24..571 rows=98795 loops=1)
-> filter: ((orders.order_time >= timestamp'2024-01-01 00:00:00') and (orders.order_time < timestamp'2025-01-01 00:00:00')) (cost=93205 rows=102819) (actual time=1.23..487 rows=473886 loops=1)
-> covering index scan on orders using idx_user_time_amount (cost=93205 rows=925560) (actual time=1.23..340 rows=1e+6 loops=1)
1 row in set (0.92 sec)
执行计划问题：10 万次子查询重复执行 sum()，累积开销大。
优化手段：
- 用覆盖索引避免回表 io：子查询和主查询均通过 idx_user_time_amount 获取所需字段（user_id、order_time、amount），无需回表。
- 减少中间结果集大小（如预聚合）：将 sum(amount)的计算放在子查询中，避免主查询处理大量中间结果。
七、案例 - 分页查询
传统分页问题（limit 大偏移量）
案例 sql：
-- 查询第100001-100020条订单（偏移量10万）
explain analyze
select * from orders
order by order_id -- 按主键排序（默认也可能按此排序）
limit 100000, 20 \g;
*************************** 1. row ***************************
explain: -> limit/offset: 20/100000 row(s) (cost=1074 rows=20) (actual time=50.3..50.3 rows=20 loops=1)
-> index scan on orders using primary (cost=1074 rows=100020) (actual time=2.84..46.1 rows=100020 loops=1)
1 row in set (0.05 sec)
核心问题：
limit 100000, 20需要扫描前 100020 行数据，然后丢弃前 100000 行，仅返回 20 行，99.98%的扫描是无效的。- 即使有排序，大偏移量仍会导致全表扫描+排序，io 和 cpu 开销极高。
分页优化核心思路
| 优化方案 | 核心思路 | 适用场景 | 性能提升幅度 |
|---|---|---|---|
| 主键偏移量分页 | 用where id > 偏移值替代limit 偏移量 | 按连续主键排序，有上一页 id | 50-100 倍 |
| 条件+边界值分页 | 用where 条件 and 字段 > 上一页值 | 按非主键排序，有过滤条件 | 30-50 倍 |
| 延迟关联+小范围 limit | 先查 id 再回表，减少大偏移量数据量 | 必须用大偏移量，无边界值 | 2-3 倍 |
优化方案 1：主键偏移量分页（利用连续主键）
通过已知的最后一条记录的主键（如第 100000 行的order_id）作为条件，直接定位到起始位置，避免扫描偏移量内的所有行。
优化后 sql：
-- 假设第100000行的order_id为100000（可从上次查询获取）
explain analyze
select * from orders
where order_id > 100000 -- 直接定位到偏移量位置
order by order_id
limit 20 \g; -- 仅取20行
*************************** 1. row ***************************
explain: -> limit: 20 row(s) (cost=99909 rows=20) (actual time=2.08..2.09 rows=20 loops=1)
-> filter: (orders.order_id > 100000) (cost=99909 rows=498729) (actual time=2.08..2.08 rows=20 loops=1)
-> index range scan on orders using primary over (100000 < order_id) (cost=99909 rows=498729) (actual time=2.07..2.08 rows=20 loops=1)
1 row in set (0.01 sec)
写法优势：
- 无需扫描偏移量内数据：通过
where order_id > 100000直接定位到起始点，扫描行数从 100020 降至 20 行。 - 利用现有主键索引：
primary key (order_id)天然存在，无需额外索引，通过范围查询（range）快速定位。
适用场景：
- 分页按连续自增主键排序（如
order_id）。 - 前端分页可记录上一页最后一条记录的
order_id（如“下一页”按钮传递该值）。
优化方案 2：基于条件过滤的分段分页（非主键排序）
当分页需要按非主键字段排序（如order_time），且有固定过滤条件（如status=2，已支付订单），传统limit大偏移量同样低效。
传统写法问题：需扫描前 1020 条符合status=2的记录，丢弃前 10000 条，无效扫描多。
-- 按订单时间排序，查询第1001-1020条已支付订单（偏移量1000）
explain analyze
select * from orders
where status = 2
order by order_time
limit 10000, 20 \g;
*************************** 1. row ***************************
explain: -> limit/offset: 20/10000 row(s) (cost=52012 rows=20) (actual time=23.9..23.9 rows=20 loops=1)
-> index lookup on orders using idx_status_time (status=2) (cost=52012 rows=498729) (actual time=11.6..23.6 rows=10020 loops=1)
1 row in set (0.02 sec)
优化写法（利用上一页边界值）：
-- 假设上一页最后一条记录的order_time为'2025-05-01 10:00:00'，order_id为50000
explain analyze
select * from orders
where status = 2
and order_time >= '2025-05-01 10:00:00' -- 用上一页时间作为起点
and not (order_time = '2025-05-01 10:00:00' and order_id <= 50000) -- 排除同时间的前序记录
order by order_time, order_id -- 时间+id联合排序，避免重复/遗漏
limit 20 \g;
*************************** 1. row ***************************
explain: -> limit: 20 row(s) (cost=58539 rows=20) (actual time=12.3..12.3 rows=20 loops=1)
-> index range scan on orders using idx_status_time over (status = 2 and '2025-05-01 10:00:00' <= order_time), with index condition: ((orders.`status` = 2) and (orders.order_time >= timestamp'2025-05-01 10:00:00') and ((orders.order_time <> timestamp'2025-05-01 10:00:00') or (orders.order_id > 50000))) (cost=58539 rows=130086) (actual time=12.3..12.3 rows=20 loops=1)
1 row in set (0.02 sec)
写法优势：
- 通过条件过滤替代偏移量：利用上一页最后一条记录的
order_time和order_id作为边界，直接定位到下一页起始位置，避免扫描前 10000 条记录。 - 联合排序去重：
order by order_time, order_id确保排序唯一，避免同时间订单重复或遗漏。 - 复用现有索引：
idx_user_time (user_id, order_time)虽以user_id开头，但order_time作为第二列可辅助范围查询（配合status=2过滤）。
适用场景：
- 按非主键字段排序（如
order_time）。 - 有固定过滤条件（如
status=2），可通过条件+边界值快速定位。 - 前端需记录上一页最后一条记录的
order_time和order_id（如传递给“下一页”接口）。
优化方案 3：延迟关联+小范围 limit（无边界值时）
当无法获取上一页边界值（如“跳转至第 500 页”），且必须使用大偏移量，可通过“先查 id，再回表”减少无效数据传输。
优化写法：
-- 子查询先获取目标页的order_id，再关联回表
explain analyze
select o.*
from orders o
join (
-- 子查询仅查id，数据量小，排序/偏移高效
select order_id
from orders
where status = 2
order by order_time
limit 10000, 20 -- 大偏移量仅处理id，而非全字段
) tmp on o.order_id = tmp.order_id \g;
*************************** 1. row ***************************
explain: -> nested loop inner join (cost=52567 rows=20) (actual time=7.07..7.15 rows=20 loops=1)
-> table scan on tmp (cost=50058..50060 rows=20) (actual time=7.04..7.04 rows=20 loops=1)
-> materialize (cost=50058..50058 rows=20) (actual time=7.04..7.04 rows=20 loops=1)
-> limit/offset: 20/10000 row(s) (cost=50056 rows=20) (actual time=7.01..7.01 rows=20 loops=1)
-> covering index lookup on orders using idx_status_time (status=2) (cost=50056 rows=498729) (actual time=2.36..6.37 rows=10020 loops=1)
-> single-row index lookup on o using primary (order_id=tmp.order_id) (cost=0.25 rows=1) (actual time=0.00447..0.00453 rows=1 loops=20)
1 row in set (0.01 sec)
写法优势：
- 减少排序/偏移的数据量：子查询仅处理
order_id（4 字节），比全字段（*包含多个字段，约 50 字节）更轻量，排序和偏移效率更高。 - 回表数据量小：仅对 20 条
order_id回表查询全字段，避免 10000 条无效记录的全字段传输。
适用场景：
- 必须使用大偏移量（如“跳转至第 n 页”）。
- 表字段较多（
*包含大量数据），通过先查 id 减少中间数据传输。
以上就是mysql中慢sql原因与优化方法详解的详细内容，更多关于mysql慢sql的资料请关注代码网其它相关文章！
发表评论