告别慢查询：从青铜到王者的MySQL索引优化终极指南在软件开发的世界里，数据库性能是衡量系统水平的关键指标，而索引优化则

在软件开发的世界里，数据库性能是衡量系统水平的关键指标，而索引优化则是这顶王冠上最耀眼的宝石。无数的系统瓶颈，最终都指向了那几条令人头疼的慢SQL。今天，我们将通过一份详尽的实战笔记，带你从索引的基础原理一路深入，覆盖设计原则、实战案例、高级优化技巧，让你彻底掌握MySQL索引优化的精髓，向慢查询说再见！

Part 1: 索引为何失效？从一个“反常”的例子说起

我们先从一个经典的联合索引场景开始，看看索引在什么情况下会“不听话”。

数据准备

我们创建一个员工表

employees，并为其创建一个name, age, position的联合索引。

SQL

CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB;
-- 插入约10万条数据用于测试

场景一：联合索引的范围查询“陷阱”

我们执行一个查询，

WHERE条件的第一个字段name使用了范围查询 >。

SQL

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

结果令人惊讶，执行计划显示

type 为 ALL（全表扫描），索引失效了！。这是因为MySQL优化器认为，当联合索引的第一个字段就进行范围查找时，可能会返回大量数据，回表查询的成本可能比直接全表扫描还要高，于是它“自作主张”放弃了索引。

场景二：覆盖索引的奇效

如果我们只查询索引中包含的列，情况就不同了。

SQL

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

这次，索引被用上了！执行计划的

Extra字段显示Using index。这就是

覆盖索引的魔力：当查询所需的所有数据都能直接从索引中获取时，MySQL就无需回表查询，大大提升了效率。

场景三：索引下推 (ICP) - MySQL的智能优化

再看一个

LIKE查询的例子。

SQL

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

在MySQL 5.6之前，这个查询只会利用索引找到

name字段匹配的行，然后逐一回表，再用age和position条件去过滤。但从5.6开始，引入了**索引下推（Index Condition Pushdown）**优化。它允许在索引遍历过程中，就使用索引中包含的

age和position字段进行过滤，从而有效减少回表次数，提升性能。

Part 2: 深入内核 —— MySQL如何选择索引？

MySQL优化器是基于成本（Cost-Based）来决定是否使用索引的。它会估算“全表扫描”和“走索引”两种方案的成本，选择成本更低的那一个。

我们可以使用

optimizer_trace工具来窥探MySQL的决策过程。

开启trace:

set session optimizer_trace="enabled=on",end_markers_in_json=on;
执行你的SQL语句。
查询trace信息:

SELECT * FROM information_schema.OPTIMIZER_TRACE;

在trace的JSON结果中，你可以清晰地看到MySQL对不同访问路径（如全表扫描

table_scan 和索引扫描 range_scan_alternatives）的成本估算，以及它最终的选择。例如，对于

WHERE name > 'a'，MySQL计算出走索引的成本（cost: 6074.2）高于全表扫描的成本（cost: 2054.7），因此最终选择了全表扫描。

Part 3: 运筹帷幄 —— 高效索引设计的六大原则

设计出高效的索引，需要遵循以下基本原则。

1. 代码先行，索引后上
：业务代码开发完成后，根据实际的SQL查询需求来创建索引，避免盲目和冗余。
1. 联合索引尽量覆盖条件
：设计联合索引以包含WHERE、ORDER BY、GROUP BY的字段，并遵循最左前缀原则。
1. 拒绝低基数
：不要在基数很小的字段（如性别）上建立索引，因为它们无法有效过滤数据。
1. 拥抱前缀索引
：对于长字符串字段，可以使用前缀索引来节省空间，但要注意它对排序和分组的限制。
1. 冲突时优先WHERE
：当WHERE和ORDER BY的索引需求冲突时，优先满足WHERE，以求最快速度筛选出少量数据。
1. 紧盯慢查询
：持续关注慢查询日志，针对性地进行优化。

Part 4: 沙盘推演 —— 社交APP索引设计实战

我们以一个社交APP的用户搜索场景为例，体验索引的逐步演进过程。

需求是筛选地区、性别、年龄、爱好等，并可能按受欢迎程度排序。

初步索引：用户常按地区和性别筛选，建立(province, city, sex)联合索引。
加入年龄范围：查询变为... AND age >= 20 AND age <= 25。由于sex没有在查询条件中，无法利用age索引。此时可以通过改写SQL ... AND sex in ('male', 'female') AND age >= 20 AND age <= 25来解决，并将索引扩展为(province, city, sex, age)。
处理多值字段：对于“爱好”这种字段，可以类似sex一样处理，将索引扩展为(province, city, sex, hobby, age) 。
处理时间范围：当需要筛选“最近一周登录”的用户时，直接将latest_login_time加入索引末尾是无效的。更优的方案是增加一个冗余字段

is_login_in_latest_7_days（值为0或1），并将其加入索引(province,city,sex,hobby,is_login_in_latest_7_days,age)，将范围查询转换为等值查询。
应对不同查询模式：对于WHERE sex = 'female' ORDER BY score这样的查询，上述索引无法满足。这时需要再设计一个辅助索引，如

(sex, score)，来专门应对这种情况。

核心思想是用一两个复杂的多字段联合索引扛下80%的查询，再用少数辅助索引解决剩余的非典型查询。

Part 5: 高级战术 —— 常见SQL的深度优化

1. 排序 (`Order by`) 与分组 (`Group by`)

ORDER BY的性能关键在于执行计划Extra列是否为Using index。如果是Using filesort，则意味着需要进行额外的文件排序，性能较低。

避免文件排序（filesort）的规则：
- 严格遵循联合索引的最左前缀原则。
  
  where name='xx' order by position会跳过age，导致filesort。
- ORDER BY子句中所有字段的排序方向（ASC/DESC）必须一致。order by age asc, position desc也会导致filesort。
- WHERE条件中如果出现范围查询或IN子句，其后的ORDER BY也可能导致filesort。
filesort的两种模式：
- 单路排序：一次性读取所有需要的字段到sort_buffer中排序，然后返回结果。
- 双路排序：先读取排序字段和主键ID到sort_buffer排序，排序后再根据ID回表查询其他字段。
- MySQL根据
  
  max_length_for_sort_data参数（默认1024字节）和查询字段总长度来决定使用哪种模式。

2. 分页查询 (`LIMIT`)

深度分页查询

select * from employees limit 100000, 10 效率极低，因为它需要读取100010条记录再丢弃前面的100000条。

优化方案一：利用主键

如果按主键排序且主键连续，可改写为

select * from employees where id > 100000 limit 10，效率极高。
优化方案二：覆盖索引 + JOIN

对于非主键字段的排序，先用覆盖索引查出目标分页的主键ID，再

INNER JOIN原表获取完整数据。

SQL
```
select * from employees e 
inner join (select id from employees order by name limit 90000,5) ed 
on e.id = ed.id;
```
优化后的执行计划显示子查询走了索引，避免了对大量数据的filesort。

3. 关联查询 (`JOIN`)

NLJ (Nested-Loop Join) 算法：当被驱动表的关联字段有索引时使用 47474747。它遍历驱动表，然后根据关联值通过索引去被驱动表查找，性能较高 48484848。
BNL (Block Nested-Loop Join) 算法：当被驱动表的关联字段无索引时使用。它会将驱动表的数据读入

join_buffer，然后全表扫描被驱动表，将每一行与join_buffer中的数据进行对比。

JOIN优化核心：

为关联字段加索引，让MySQL尽量使用NLJ算法。
遵循

小表驱动大表原则。这里的“小表”指的是经过WHERE条件过滤后，结果集较小的表。可以使用

straight_join来强制指定驱动表。

4. `IN` vs `EXISTS`

两者优化的核心同样是“小表驱动大表”原则。

IN: select * from A where id in (select id from B)。适合B表（子查询）是小表的场景。
EXISTS: select * from A where exists (select 1 from B where B.id = A.id)。适合A表（主查询）是小表的场景 56565656。

**5. `COUNT(*)` 查询**

count(*)、count(1)、count(id)、count(字段)的性能大致关系为：count(*) ≈ count(1) > count(主键 id) > count(有索引字段) > count(无索引字段) 。MySQL对

count(*)有专门优化，它不取值，按行累加，效率很高。
InnoDB引擎需要实时计算总行数，而MyISAM引擎会直接存储总行数，所以对于不带

WHERE的count(*)，MyISAM速度极快。
优化方案：对于InnoDB，如果只需要一个估算值，可以使用 show table status。对于精确值，可以考虑将计数值维护在Redis或专门的数据库计数表中 61616161。

Part 6: 夯实地基 —— MySQL数据类型选择

选择正确的数据类型对性能至关重要。

数值类型：如果不需要负数，请使用UNSIGNED，可以让存储范围扩大一倍。用

TINYINT代替ENUM。用

DECIMAL存储精确的小数，如价格。
日期时间：建议使用TIMESTAMP或DATETIME来存储时间。

TIMESTAMP与时区相关，占用4字节，有2038年的上限问题 67676767。

DATETIME与时区无关，占用8字节，没有时间上限问题 68686868。
字符串类型：长度基本固定的用CHAR，长度可变大的用VARCHAR 。尽量避免使用

BLOB和TEXT，如果必须使用，可考虑将其拆分到单独的表中。

结语

MySQL索引优化是一场永无止境的探索。它不仅需要你理解B+树、优化器、执行计划等底层原理，更需要你在大量的实战中去总结、去思考。希望这篇由你笔记整理而来的博文能成为你的“屠龙之技”，在未来的开发道路上，助你斩杀一切慢查询！

告别慢查询：从青铜到王者的MySQL索引优化终极指南

Part 1: 索引为何失效？从一个“反常”的例子说起

Part 2: 深入内核 —— MySQL如何选择索引？

Part 3: 运筹帷幄 —— 高效索引设计的六大原则

Part 4: 沙盘推演 —— 社交APP索引设计实战

Part 5: 高级战术 —— 常见SQL的深度优化

1. 排序 (Order by) 与分组 (Group by)

2. 分页查询 (LIMIT)

3. 关联查询 (JOIN)

4. IN vs EXISTS

5. COUNT(*) 查询