一、前言

二、基础知识回顾

三、索引设计优化

1.遵循最左匹配原则，合理设计联合索引顺序

2.利用覆盖索引避免回表查询

3.针对字符串列使用前缀索引

4.合理使用复合索引替代多个单列索引

5.使用前缀索引优化模糊查询的左匹配

四、索引使用优化

1.避免在WHERE子句中对字段进行函数运算

2.避免隐式类型转换导致索引失效

3.小心使用NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等否定操作符

4.合理使用LIMIT优化分页查询

5.避免使用SELECT *，只查询需要的列

6.使用EXPLAIN分析查询执行计划

五、特殊场景索引优化

1.使用索引排序优化ORDER BY操作

2.在大表上创建索引的最佳实践

3.使用虚拟列为计算结果创建索引

六、索引维护优化

1.定期优化和重建索引

2.控制单表上的索引数量

3.使用降序索引优化排序

4.使用部分索引优化高选择性数据

七、索引监控与进阶技巧

1.利用索引统计信息进行调优

2.使用索引提示(Index Hints)解决优化器选择问题

八、结语

一、前言

MySQL索引优化是提升数据库性能的关键手段，一个合理的索引设计和使用策略，往往能将查询速度提升几十倍甚至上百倍。

然而，索引优化并不简单，既需要扎实的理论基础，也需要丰富的实战经验。

本文总结了21个MySQL索引优化的实战技巧，从索引选择、设计到维护、监控的全生命周期，帮助你解决日常开发中的索引性能问题。

二、基础知识回顾

在具体介绍前，让我们先简单回顾索引的基础知识：

MySQL常用的索引类型包括：主键索引、唯一索引、普通索引、联合/组合/复合索引、覆盖索引、全文索引等。

其中最常用的B+树索引，具有以下特点：

• 非叶子节点只存储键值信息

• 所有叶子节点包含了完整的数据记录

• 叶子节点通过指针连接，方便范围查询

• 所有节点按键值大小排序

理解这些基础对于后续优化至关重要。接下来，让我们进入正题。

三、索引设计优化

1.遵循最左匹配原则，合理设计联合索引顺序

联合索引的顺序直接影响其使用效率。MySQL会从左到右依次使用索引列，如果中间某列没有使用，则后面的列也无法使用索引。

错误示例：

-- 创建索引(name, age, city)
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- 以下查询无法充分利用索引
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';  -- name列缺失，只能全表扫描
SELECT * FROM user WHERE name = 'Tom' AND city = 'Beijing';  -- 中间age列缺失，city无法使用索引

优化方法：

1. 将选择性高的列放在前面（选择性 = 不重复值 / 总记录数）

2. 将常用于条件查询的列放在前面

3. 考虑范围查询的列放在最后

-- 假设选择性：city < name < age
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- 充分利用索引的查询
SELECT * FROM user WHERE name = 'Tom' AND age = 25;
SELECT * FROM user WHERE name = 'Tom' AND age = 25 AND city = 'Beijing';

(注：最左前缀匹配原则的核心要点：

1.必须包含最左列：查询条件中必须包含组合索引定义中的最左边第一列 (name)，否则索引对该查询无效。

2.连续前缀匹配：索引可以被用于匹配查询条件中索引列的连续前缀 ((name), (name,age), (name, age, city)）。

3.不能跳过中间列： 如果查询条件没有包含索引定义中某个连续前缀的中间列（例如，只有 name和 city，跳过了 age），那么只有跳过的列之前的列（name）能用于索引查找，跳过的列之后的列（city）不能用于查找，只能作为扫描后的过滤条件。

-- 创建组合索引索引
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- 充分利用索引的查询
SELECT * FROM user WHERE name = 'Tom';
SELECT * FROM user WHERE name = 'Tom' AND age = 25 ;
SELECT * FROM user WHERE name = 'Tom' AND age = 25 AND city = 'Beijing';
-- 跳过了索引中定义的age列，只有name是利用了索引，city这里只是作为查询条件
SELECT * FROM user WHERE name = 'Tom' AND city = 'Beijing';

4.范围查询会中断后续列:如果查询条件中对某一列使用了范围查询 (>, <, BETWEEN, LIKE 'prefix%')，那么索引中位于这个范围查询列之后的列无法再用于索引查找，只能作为扫描后的过滤条件。

-- 创建组合索引索引
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- name列使用范围查询，导致age和city列不走这个组合索引（这里name列是走索引的）
SELECT * FROM user WHERE name like 'Tom%' AND age = 25 AND city = 'Beijing';

)

2.利用覆盖索引避免回表查询

回表操作是指通过索引找到对应的行记录指针，再通过指针去查询完整记录的过程。

如果查询只需要返回索引包含的列，则可以避免回表，这称为覆盖索引。

优化前：

-- 创建普通索引
CREATE INDEX idx_user_name ON user(name);-- 需要回表查询
SELECT id, name, age, city FROM user WHERE name = 'Tom';

优化后：

-- 创建包含所需字段的索引
CREATE INDEX idx_user_name_age_city ON user(name, age, city);-- 使用覆盖索引，无需回表
SELECT name, age, city FROM user WHERE name = 'Tom';

3.针对字符串列使用前缀索引

对于CHAR和VARCHAR类型的列，如果整列长度较大，可以只索引开头的部分字符，这样可以大幅减少索引占用空间，提高索引效率。
优化方法：

-- 假设product_desc是较长的产品描述文本
CREATE INDEX idx_product_desc ON product(product_desc(50));

如何确定前缀长度？可以通过计算选择性来确定：

-- 计算不同前缀长度的选择性
SELECT COUNT(DISTINCT LEFT(product_desc, 10)) / COUNT(*) AS sel_10,COUNT(DISTINCT LEFT(product_desc, 20)) / COUNT(*) AS sel_20,COUNT(DISTINCT LEFT(product_desc, 30)) / COUNT(*) AS sel_30,COUNT(DISTINCT LEFT(product_desc, 40)) / COUNT(*) AS sel_40,COUNT(DISTINCT LEFT(product_desc, 50)) / COUNT(*) AS sel_50,COUNT(DISTINCT product_desc) / COUNT(*) AS sel_full
FROM product;

创建索引时，选择一个接近完整列选择性的前缀长度即可。
注意事项： 使用前缀索引后，无法使用该索引做ORDER BY或GROUP BY，也无法使用覆盖索引。

4.合理使用复合索引替代多个单列索引

多个单列索引在多条件查询时，MySQL只会选择一个索引。而复合索引可以同时满足多个条件的查询需求。

优化前：

-- 单独创建两个索引
CREATE INDEX idx_user_age ON user(age);
CREATE INDEX idx_user_city ON user(city);-- MySQL通常只会选择一个索引
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';

优化后：

-- 创建一个复合索引
CREATE INDEX idx_user_age_city ON user(age, city);-- 可以同时使用age和city条件
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';

5.使用前缀索引优化模糊查询的左匹配

LIKE语句使用通配符前缀（如'%abc'）会导致索引失效。但对于右匹配模式（如'abc%'），索引仍然有效。

可以使用索引的查询：

-- 可以使用索引
SELECT * FROM products WHERE product_name LIKE 'iphone%';

无法使用索引的查询：

-- 无法使用索引
SELECT * FROM products WHERE product_name LIKE '%iphone%';

优化方法：

对于需要搜索包含某个关键词的记录，可以考虑全文索引或搜索引擎。对于简单场景，也可以通过字段冗余解决：

-- 添加一个反转字段
ALTER TABLE products ADD product_name_reversed VARCHAR(255);-- 创建反转字段的索引
CREATE INDEX idx_product_name_rev ON products(product_name_reversed);-- 搜索以'phone'结尾的产品
SELECT * FROM products 
WHERE product_name_reversed LIKE CONCAT(REVERSE('phone'), '%');

或者如果你的 MySQL 版本 >= 8.0也可以使用函数索引；

-- 创建字段的函数索引
CREATE INDEX idx_product_name_fun ON products(REVERSE(product_name));-- 搜索以'phone'结尾的产品（这里直接用REVERSE函数反转字段）
SELECT * FROM products 
WHERE REVERSE(product_name) LIKE CONCAT(REVERSE('phone'), '%');

或者使用全文索引（关键字FULLTEXT）：

-- 1. 在需要搜索的列上创建全文索引 (以 products 表的 description 列为例)
ALTER TABLE products ADD FULLTEXT INDEX ft_idx_description (description);-- 2. 基础搜索：查找包含 'phone' 或 'camera' 的记录 (自然语言模式)
SELECT *, MATCH(description) AGAINST('phone camera') AS relevance
FROM products
WHERE MATCH(description) AGAINST('phone camera')
ORDER BY relevance DESC;-- 3. 布尔搜索：查找必须包含 'fast' 且包含 'charger' 但不包含 'wireless' 的记录
SELECT *
FROM products
WHERE MATCH(description) AGAINST('+fast +charger -wireless' IN BOOLEAN MODE);-- 4. 短语搜索：查找精确包含 'long battery life' 短语的记录
SELECT *
FROM products
WHERE MATCH(description) AGAINST('"long battery life"' IN BOOLEAN MODE);

强烈建议： 除非是微不足道的中文搜索需求，否则不要依赖 MySQL 全文索引解决核心中文搜索问题。投入专业搜索引擎 (如 ES/MeiliSearch + 中文分词) 的回报远高于在 MySQL 内勉强凑合。

（搜索引擎这里不做赘述，有想了解的同学请关注我后续的文章。）

四、索引使用优化

1.避免在WHERE子句中对字段进行函数运算

在字段上使用函数会导致索引失效，应该把运算转移到值上。

错误用法：

-- 索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

优化方法：

-- 可以使用索引
SELECT * FROM orders 
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

2.避免隐式类型转换导致索引失效

MySQL在进行查询时，如果字段类型与条件值类型不匹配，会进行隐式类型转换，可能导致索引失效。

错误用法：

-- user_id是varchar类型，但使用了整数条件
CREATE INDEX idx_user_id ON users(user_id);
SELECT * FROM users WHERE user_id = 12345;  -- 索引可能失效

优化方法：

-- 确保条件值类型与字段类型一致
SELECT * FROM users WHERE user_id = '12345';  -- 使用字符串类型

3.小心使用NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等否定操作符

否定条件通常会导致索引失效，因为数据库需要检查所有不满足条件的记录。
优化方法：

尽量用肯定表达式替代否定表达式：

-- 优化前：无法充分利用索引
SELECT * FROM products WHERE category_id != 5;-- 优化后：可以使用索引
SELECT * FROM products WHERE category_id < 5 OR category_id > 5;

4.合理使用LIMIT优化分页查询

大偏移量的LIMIT分页查询效率较低，因为MySQL需要检索前N条记录然后丢弃。

优化前：

-- 性能较差的分页查询
SELECT * FROM products ORDER BY id LIMIT 100000, 10;

优化方法1 - 使用索引覆盖扫描：

-- 先获取ID，再关联查询完整数据
SELECT p.* FROM products p
JOIN (SELECT id FROM products ORDER BY id LIMIT 100000, 10
) tmp ON p.id = tmp.id;

优化方法2 - 使用上次查询的最大ID（不适合前端跳页）：

-- 假设已知上一页的最大ID是100233
SELECT * FROM products WHERE id > 100233 ORDER BY id LIMIT 10;

5.避免使用SELECT *，只查询需要的列

使用SELECT *会返回所有列，可能破坏覆盖索引的效果，并增加网络和内存开销。

优化前：

-- 可能导致不必要的开销
SELECT * FROM users WHERE name = 'Tom';

优化后：

-- 只返回需要的列，可能利用覆盖索引
SELECT id, name, email FROM users WHERE name = 'Tom';

6.使用EXPLAIN分析查询执行计划

在优化前，先使用EXPLAIN分析SQL语句的执行计划，了解索引使用情况。

EXPLAIN SELECT * FROM users WHERE name = 'Tom' AND age > 20;

重点关注以下字段：

• type: 从好到差依次是：system > const > eq_ref > ref > range > index > ALL（性能排序）

• key: 实际使用的索引

• rows: 预计需要扫描的行数

• Extra: 额外信息，如"Using index"表示使用了覆盖索引

五、特殊场景索引优化

1.使用索引排序优化ORDER BY操作

如果ORDER BY的列与WHERE使用的列不一致，排序无法使用索引，会导致文件排序（如果满足`name='Tom'`的行数很多，那么排序可能会很慢，因为排序操作可能需要在内存或者磁盘上完成（即所谓的文件排序，filesort）。

优化前：

-- WHERE和ORDER BY使用不同的列，可能导致文件排序
CREATE INDEX idx_user_name ON users(name);
SELECT * FROM users WHERE name = 'Tom' ORDER BY age;

优化后：

-- 创建联合索引同时包含WHERE和ORDER BY的列
CREATE INDEX idx_user_name_age ON users(name, age);
SELECT * FROM users WHERE name = 'Tom' ORDER BY age;

注意事项： ORDER BY的多个字段需要与索引顺序一致，且排序方向需一致(全ASC或全DESC)。

2.在大表上创建索引的最佳实践

在大表上直接创建索引可能会导致长时间锁表。可以使用以下方法优化：

方法1 - 使用低峰期操作：

-- 在低峰期执行索引创建
CREATE INDEX idx_order_status ON orders(status);

方法2 - 使用在线DDL(MySQL 8.0+首选方案)：

-- 使用ALGORITHM和LOCK选项
CREATE INDEX idx_order_status ON orders(status)
ALGORITHM=INPLACE, LOCK=NONE;

技术原理：非阻塞索引构建

ALGORITHM=INPLACE
增量构建：仅扫描原表一次，中间数据存于临时文件（非新建整表）
空间复用：无需额外占用等表大小的磁盘空间（对比ALGORITHM=COPY）
LOCK=NONE
零锁表：允许并发的SELECT和DML（INSERT/UPDATE/DELETE）
底层通过行版本控制（如InnoDB的MVCC）实现读写分离

方法3 - 使用pt-online-schema-change工具：

pt-online-schema-change --alter "ADD INDEX idx_order_status (status)" \
--host=localhost --user=root --ask-pass --database=mydb --table=orders \
--execute

3.使用虚拟列为计算结果创建索引

对于经常需要计算后过滤的场景，可以使用虚拟列并在其上创建索引（关键字VIRTUAL）。

-- 添加虚拟列存储计算结果
ALTER TABLE products 
ADD total_value DECIMAL(10,2) AS (price * quantity) VIRTUAL;-- 在虚拟列上创建索引
CREATE INDEX idx_total_value ON products(total_value);-- 使用计算列进行查询
SELECT * FROM products WHERE total_value > 10000;

或者使用MySQL 8.0+的函数索引：

-- 添加虚拟列存储计算结果
CREATE INDEX tatal_value ON products((price * quantity))-- 使用计算列进行查询
SELECT * FROM products WHERE (price * quantity) > 10000;

虚拟列索引和函数索引是同一索引优化机制（预计算表达式+建索引）的两种语法糖：

虚拟列索引: 通过创建"计算列"提供 显式接口
函数索引: 允许直接索引表达式 无需显式列

选择谁 ≈ 选择语法风格，而非性能差异，因为两者在相同条件下性能命中索引时查询性能一致。

六、索引维护优化

1.定期优化和重建索引

随着数据变化，索引可能变得碎片化，影响性能。定期优化表和重建索引可以改善性能。

-- 分析表
ANALYZE TABLE orders;-- 优化表
OPTIMIZE TABLE orders;-- 或者重建索引
ALTER TABLE orders DROP INDEX idx_status, ADD INDEX idx_status(status);

总结：

日常用 ANALYZE 维持优化器准确性
定期监控碎片率，>30% 时用 OPTIMIZE
关键索引性能下降时单独重建索引
永远在业务低峰期操作！

注：下面提供一个碎片率的计算方式（参考）：

-- 检查碎片率
SELECT TABLE_NAME,ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS "Size(MB)",ROUND(DATA_FREE / 1024 / 1024, 2) AS "Free(MB)",ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS "Frag %"
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

2.控制单表上的索引数量

索引数量过多会影响写性能，建议每个表的索引数量控制在5个以内。

优化方法：

1. 删除重复和未使用的索引

2. 合并功能类似的索引

2. 合并功能类似的索引-- 查找未使用的索引
SELECT * FROM schema_unused_indexes;  -- Performance Schema-- 查找重复的索引
SELECT * FROM sys.schema_redundant_indexes;  -- Sys Schema

3.使用降序索引优化排序

MySQL 8.0+支持降序索引，可以优化混合排序方向的查询。

-- 创建混合排序方向的索引(MySQL 8.0+)
CREATE INDEX idx_user_age_score ON users(age ASC, score DESC);-- 可以高效执行的查询
SELECT * FROM users ORDER BY age ASC, score DESC;

4.使用部分索引优化高选择性数据

MySQL 8.0+支持在WHERE条件满足时才为行创建索引记录，减少索引大小。

-- 只为活跃用户创建索引(MySQL 8.0+)
CREATE INDEX idx_active_users ON users(name, email) 
WHERE status = 'active';

七、索引监控与进阶技巧

1.利用索引统计信息进行调优

MySQL维护了索引统计信息，可以帮助优化器选择合适的索引。有时统计信息不准确会导致次优的执行计划。

-- 查看表的统计信息
SHOW TABLE STATUS LIKE 'users';-- 查看索引的基数
SHOW INDEX FROM users;-- 刷新统计信息
ANALYZE TABLE users;

2.使用索引提示(Index Hints)解决优化器选择问题

有时MySQL优化器的选择不是最优的，可以使用索引提示强制使用特定索引。

-- 强制使用特定索引
SELECT * FROM users FORCE INDEX(idx_name_age) 
WHERE name = 'Tom' AND age > 20;-- 忽略特定索引
SELECT * FROM users IGNORE INDEX(idx_status) 
WHERE status = 'active' AND age > 20;

建议： 索引提示应该是最后的手段，通常先尝试优化表结构和索引设计。