MySQL索引优化实战：让慢查询飞起来的10个技巧

引言

作为一名后端开发者，你大概率遇到过这样的场景：上线前测试一切正常，上线后随着数据量增长，某些SQL查询从几毫秒飙升到几十秒，数据库CPU打满，应用响应超时。打开慢查询日志一看，原来是一条没有走索引的全表扫描在作祟。

索引是MySQL性能优化的第一道防线，也是最容易见效的手段。但很多开发者对索引的理解还停留在"加个索引就行了"的层面，结果往往是索引加了却没生效，或者索引过多导致写入性能下降。本文将从原理到实战，系统讲解MySQL索引优化的10个核心技巧。

一、先搞懂B+树索引的工作原理

MySQL的InnoDB引擎使用B+树作为索引的底层数据结构。理解B+树是进行索引优化的基础。

B+树的三个关键特性：

• 多路平衡：一个节点可以存储多个键值，树的高度通常只有3-4层，意味着一次查询最多需要3-4次磁盘IO
• 数据只在叶子节点：非叶子节点只存索引键，使得每个节点能容纳更多键，进一步降低树高
• 叶子节点有序链表：所有叶子节点通过双向链表连接，支持高效的范围查询和排序

以一个INT类型的主键索引为例，假设每个节点大小为16KB，一个BIGINT占8字节，指针占6字节，那么一个节点可以存储约1170个键。3层B+树可以支撑约 1170 × 1170 × 16 ≈ 2100 万行数据，也就是说对2000万行的表进行主键查询，也只需要3次磁盘IO。

二、EXPLAIN是你的好朋友

在优化任何SQL之前，先用EXPLAIN查看执行计划。重点关注以下字段：

EXPLAIN SELECT * FROM orders WHERE user_id = 10000 AND status = 1;

关键字段解读：

• type：访问类型，从好到差依次为：system > const > eq_ref > ref > range > index > ALL。至少应达到range级别，出现ALL说明全表扫描，必须优化
• key：实际使用的索引。如果为NULL，说明没有走索引
• rows：预估扫描的行数，越少越好
• Extra：额外信息，"Using index"表示覆盖索引，"Using filesort"表示需要额外排序，"Using temporary"表示使用了临时表

三、覆盖索引——查询性能的终极加速器

如果一个索引包含了查询所需的所有字段，InnoDB可以直接从索引中返回结果而无需回表，这就是覆盖索引。回表里多了一步根据主键去聚簇索引查找数据的过程，会产生额外的随机IO。

示例：

-- 假设表结构
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    title VARCHAR(200),
    category_id INT,
    created_at DATETIME,
    INDEX idx_cat_time (category_id, created_at)
);

-- 使用覆盖索引：只需查索引即可返回结果
EXPLAIN SELECT category_id, created_at FROM articles WHERE category_id = 5;
-- Extra: Using index (ok)

-- 需要回表：SELECT的字段不在索引中
EXPLAIN SELECT id, title FROM articles WHERE category_id = 5;
-- 需要回表查找title字段

实战技巧：对于高频查询但更新较少的表，可以将被查询字段加入联合索引形成覆盖索引。即使索引稍大一点，换来查询不需要回表，收益也非常可观。

四、最左前缀原则——联合索引的灵魂

联合索引(a, b, c)相当于先按a排序，a相同再按b排序，b相同再按c排序。这意味着：

• 查询条件包含a ✓ ——可以使用索引
• 查询条件包含a和b ✓ ——可以使用索引
• 查询条件包含a、b和c ✓ ——完全使用索引
• 查询条件只有b ✗ ——无法使用索引（因为b在a无序的情况下也是无序的）
• 查询条件只有a和c △ ——只能使用a的部分，c无法利用索引

设计联合索引的黄金法则：

1. 将等值查询的字段放在前面
2. 将范围查询的字段放在后面（范围查询后的字段无法使用索引排序）
3. 考虑WHERE条件、ORDER BY、GROUP BY的综合需求

五、索引下推（ICP）——减少回表次数的利器

MySQL 5.6引入了索引下推（Index Condition Pushdown），在存储引擎层就对索引中的字段进行过滤，而不是将所有符合前缀条件的记录都回表。

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    age INT,
    INDEX idx_name (last_name, first_name, age)
);

-- 查询姓张且年龄大于25的用户
SELECT * FROM users WHERE last_name = '张' AND age > 25;

没有ICP时，存储引擎找到所有last_name='张'的记录后都要回表，然后在Server层过滤age>25。有了ICP后，存储引擎在索引层面就会判断age>25，只对符合条件的记录回表，大幅提升性能。

可通过 SET optimizer_switch = 'index_condition_pushdown=on'; 开启（默认已开启）。

六、Order By优化——避免Filesort

如果ORDER BY的字段能利用索引的自然排序，就能避免额外的filesort操作。

INDEX idx_a_b_c (a, b, c)

-- ✅ 利用索引排序，无需filesort
SELECT * FROM t WHERE a = 1 ORDER BY b;
SELECT * FROM t WHERE a = 1 ORDER BY b, c;
SELECT * FROM t WHERE a = 1 AND b = 2 ORDER BY c;

-- ❌ 无法利用索引排序
SELECT * FROM t ORDER BY b;           -- 缺少a的等值条件
SELECT * FROM t WHERE a = 1 ORDER BY c;  -- 跳过了b
SELECT * FROM t WHERE a > 1 ORDER BY b;  -- a是范围查询
SELECT * FROM t WHERE a = 1 ORDER BY b DESC, c ASC;  -- 排序方向不一致

七、隐式类型转换——索引失效的隐形杀手

这是生产环境中最常见也最容易被忽视的索引失效场景。

-- 假设phone是VARCHAR类型，有索引 idx_phone
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- ❌ 索引失效！MySQL将 VARCHAR 与 INT 比较时，会隐式将 phone 转换为数字
-- 相当于：WHERE CAST(phone AS UNSIGNED) = 13800138000

-- ✅ 正确写法：使用字符串类型
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-- ✅ 索引生效

其他隐式转换场景：

• 字符集不一致：utf8列与utf8mb4值比较，utf8列会被转换
• 日期类型：DATE列与DATETIME值比较
• 使用函数：WHERE YEAR(create_time) = 2024 ——函数导致索引失效

日期查询优化：

-- ❌ 索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- ✅ 改为范围查询
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

八、LIKE查询优化——前缀模糊搜索的陷阱

INDEX idx_username (username)

-- ✅ 可以使用索引（前缀匹配）
SELECT * FROM users WHERE username LIKE 'zhang%';

-- ❌ 无法使用索引（左模糊，破坏了有序性）
SELECT * FROM users WHERE username LIKE '%zhang%';

-- ❌ 无法使用索引（右模糊也不好）
SELECT * FROM users WHERE username LIKE '%zhang';

解决方案：

• 对于左右模糊搜索，使用反向索引：存储反转字符串，LIKE 'gnahz%' 查反转后等于原字符串以'zhang'结尾
• 使用全文索引（FULLTEXT）：MySQL 5.6+ InnoDB支持全文索引
• 引入Elasticsearch/Sphinx等专业搜索引擎
• 使用MySQL 8.0的倒排索引功能

九、索引选择性——判断索引是否有用的指标

索引选择性 = 不重复的索引值数量 / 总记录数。选择性越高（越接近1），索引越有价值。

SELECT 
    COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity,
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity,
    COUNT(DISTINCT phone) / COUNT(*) AS phone_selectivity
FROM users;

-- gender选择性通常很低（约0.5），因为只有男/女/未知三个值
-- email选择性理论上接近1，每个值都是唯一的
-- phone选择性也很高

判断标准：

• 选择性 > 0.1：适合建索引
• 选择性 < 0.01：建索引效果不佳（除非用于覆盖索引）
• 唯一索引：选择性 = 1，最佳

对于低选择性的字段（如gender），可以考虑复合索引的方式提升整体选择性，例如 (gender, city_id) 的组合选择性要比单独的gender高得多。

十、索引维护与健康检查

索引不是建了就完事，需要定期维护。

1. 查找冗余索引：

-- pt-duplicate-key-checker 工具
pt-duplicate-key-checker h=localhost,u=root,p=password,D=mydb

-- 示例：idx_a_b (a, b) 和 idx_a (a) 是冗余的，因为(a,b)已经包含(a)的功能

2. 查找未使用的索引：

-- MySQL 5.7+ 通过 sys.schema_unused_indexes 视图
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = 'mydb';

-- 这些索引占空间、影响写入性能，应该考虑删除

3. 索引碎片整理：

-- 查看表碎片
SELECT table_name, data_free 
FROM information_schema.tables 
WHERE table_schema = 'mydb' AND data_free > 0;

-- 整理碎片
OPTIMIZE TABLE orders;
-- 或者
ALTER TABLE orders ENGINE=InnoDB;

总结

MySQL索引优化可以总结为一个口诀：覆盖最左要记牢，类型匹配不能少，Explain常挂嘴边，定期维护不能忘。

记住以下核心原则：

1. 永远先看EXPLAIN执行计划，不要凭感觉优化
2. 联合索引遵循最左前缀原则，合理设计字段顺序
3. 警惕隐式类型转换导致的索引失效
4. 用减少回表次数的思维设计索引
5. 不是索引越多越好，监控并保持适量

在实际工作中，建议开启慢查询日志（long_query_time设为0.5秒），定期分析慢查询，结合EXPLAIN逐一优化。每优化一条慢查询，都可能为数据库减负不少。