十个高效技巧助你快速掌握索引优化方法

聊到数据库性能优化，“索引”这个话题永远绕不开。大家都知道索引能加速查询，但具体怎么建、怎么用才能让查询效率最大化，很多人可能只停留在“给WHERE条件加索引”的层面。其实，索引优化是一门精细活儿，用好了是性能利器，用不好反而会成为负担。今天，我们就来系统梳理一下工作中最高频、最实用的十个索引优化技巧。

技巧一：选择高选择性的列作为索引

一个常见的误区是，给所有出现在WHERE子句里的字段都建上索引。比如给“性别”列建索引，想查询所有男性用户，结果发现速度并没提升，甚至更慢了。这背后的关键，在于列的“选择性”。

什么是选择性？

选择性 = COUNT(DISTINCT col) / COUNT(*)。这个比值越接近1，说明该列的唯一值越多，选择性越高。高选择性的列（比如用户ID、订单号）能快速过滤掉大量无关数据，索引价值巨大。而低选择性的列（比如性别、状态码），即便走了索引，每个键值背后也可能对应海量数据，导致大量回表操作，其代价有时甚至超过全表扫描，优化器就会明智地放弃使用索引。

-- 查看字段选择性示例
SELECT
    COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity
FROM users;

正确做法

优先在高选择性列上建立索引。如果业务查询必须用到低选择性字段（例如按状态筛选），一个更优的策略是将其放在联合索引的末尾，作为二次过滤条件。

-- 不推荐：单独为低选择性字段建索引
ALTER TABLE orders ADD INDEX idx_status (status);

-- 推荐：将低选择性字段置于联合索引末尾
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

技巧二：联合索引遵循最左前缀法则

建了联合索引 INDEX(a, b, c)，查询时却写了 WHERE b = 1 AND c = 2，发现索引失效，这就是没吃透“最左前缀原则”。

最左前缀原则详解

联合索引 (a, b, c) 在物理上是一棵B+树，其数据是先按a排序，a相同再按b排序，b相同再按c排序。因此，查询条件必须从索引的最左列开始，才能利用这棵树的排序结构进行高效查找。跳过最左列，索引就失去了作用。

查询示例分析

-- 正确：完全匹配所有列，索引效果最佳
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;

-- 正确：匹配最左前列a和b，c虽未用但已过滤大量数据
SELECT * FROM t WHERE a = 1 AND b = 2;

-- 正确：仅匹配最左列a，依然可以使用索引
SELECT * FROM t WHERE a = 1;

-- 错误：跳过了最左列a，索引失效
SELECT * FROM t WHERE b = 2 AND c = 3;

-- 注意：a列使用范围查询后，其后的b、c列无法再使用索引进行精确匹配
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3; -- 仅a和b的范围部分走索引

因此，设计联合索引时，应将等值查询的列放在左边，范围查询的列放在右边，并尽量让查询条件覆盖索引的最左前缀。

技巧三：尽量使用覆盖索引，避免回表

“回表”是影响索引查询性能的主要瓶颈之一。InnoDB的二级索引叶子节点存储的是主键值，通过二级索引找到主键后，还需要回到主键索引（聚簇索引）去查找完整的行数据，这个额外的随机IO操作非常耗时。

什么是覆盖索引？

如果一个索引包含了查询语句所需要的全部字段，那么MySQL就可以直接从索引中取得数据，无需回表，这就是“覆盖索引”。它的效率提升往往是数量级的。

-- 低效查询：仅凭id_card索引找到主键后，需回表获取name和age
SELECT name, age FROM user WHERE id_card = 'xxx';

-- 高效方案：创建覆盖索引 (id_card, name, age)
ALTER TABLE user ADD INDEX idx_id_card_name_age (id_card, name, age);
-- 再次执行上述查询，所需数据全在索引中，无需回表

覆盖索引虽好，但会增大索引体积，增加写入时的维护成本。它最适合用于那些查询字段固定且访问频率极高的场景。

技巧四：避免在索引列上使用函数或表达式

在索引列上进行计算或函数操作，会让索引立刻失效。因为B+树是按照列的原始值排序的，对值进行任何加工都会破坏这种有序性。

-- 错误：对索引列使用DATE函数，导致索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2026-01-01';

-- 正确：改为范围查询，可利用索引
SELECT * FROM orders
WHERE create_time >= '2026-01-01 00:00:00'
  AND create_time < '2026-01-02 00:00:00';

其他常见陷阱

-- 陷阱1：隐式类型转换（假设phone字段为VARCHAR类型）
SELECT * FROM user WHERE phone = 13800000000; -- 错误，数字与字符串比较
SELECT * FROM user WHERE phone = '13800000000'; -- 正确

-- 陷阱2：在索引列上进行运算
SELECT * FROM product WHERE price * 0.8 > 100; -- 错误
SELECT * FROM product WHERE price > 100 / 0.8; -- 正确，将运算移至右侧

核心原则是：保持索引列在查询条件中的“纯洁性”，让比较操作直接作用于列本身。

技巧五：利用索引下推减少回表

索引下推（Index Condition Pushdown, ICP）是MySQL 5.6引入的一项重要优化。在没有ICP时，对于联合索引 (name, age) 的查询 WHERE name LIKE '张%' AND age = 25，存储引擎会先通过索引找到所有姓“张”的主键，然后回表取出完整记录，再由Server层过滤age=25的条件。

开启ICP后，存储引擎可以在索引遍历过程中，直接利用索引中包含的列（本例中的age）进行过滤，只对同时满足 name LIKE '张%' 和 age=25 的记录进行回表，从而大幅减少不必要的IO。

-- 查看ICP是否开启
SHOW VARIABLES LIKE 'optimizer_switch';
-- 开启ICP（通常默认已开启）
SET optimizer_switch='index_condition_pushdown=on';

ICP特别适用于联合索引中，前导列使用范围查询（如LIKE）而后续列有等值过滤的场景。

技巧六：避免使用OR，改用UNION或IN

当WHERE条件中使用OR连接不同字段时，很可能导致索引失效，优化器转而选择全表扫描。

-- 可能导致索引失效
SELECT * FROM user WHERE status = 1 OR status = 2;

优化方案通常有两种：

1. 使用IN：WHERE status IN (1, 2)。优化器对IN的处理更友好，通常能有效利用索引。
2. 使用UNION：将OR拆分成多个独立的查询，利用各自的索引，再合并结果。

-- 使用UNION优化
SELECT * FROM user WHERE status = 1
UNION ALL
SELECT * FROM user WHERE status = 2;

注意，UNION 会去重，如果确定结果集无重复，使用 UNION ALL 性能更佳，因为它省去了去重步骤。

技巧七：使用前缀索引节省空间

对于像邮箱、地址这类长字符串字段，建立完整索引会占用大量磁盘空间，并影响写入速度。此时，可以考虑前缀索引，即只对字段的前N个字符建立索引。

-- 为email字段创建前缀索引，只索引前10个字符
ALTER TABLE user ADD INDEX idx_email_prefix (email(10));

关键是如何确定合适的前缀长度？目标是找到选择性接近完整列的最小长度。

-- 计算不同前缀长度的选择性
SELECT
    COUNT(DISTINCT LEFT(email, 5))/COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(email, 10))/COUNT(*) AS sel10,
    COUNT(DISTINCT email)/COUNT(*) AS total_sel
FROM user;

需要警惕的是，前缀索引无法用于 ORDER BY 和 GROUP BY 操作，也无法实现覆盖索引（因为索引中不包含完整字段值）。

技巧八：定期监控并删除未使用的索引

无效或冗余的索引不仅浪费存储空间，还会降低DML（增删改）语句的性能。常见的冗余情况包括：

• 已有联合索引 (a,b)，又单独创建了索引 (a)，后者通常是冗余的。
• 索引 (a,b) 和 (b,a) 同时存在，虽然顺序不同适用场景不同，但需根据实际查询模式评估是否都需要。

如何查找？

-- 1. 查找可能未使用的索引（需开启performance_schema）
SELECT *
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
  AND index_name IS NOT NULL
ORDER BY count_read, count_write;
-- 长期 count_read 为0的索引可考虑删除

-- 2. 使用sys库查找冗余索引（MySQL 5.7+/MariaDB）
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'your_db';

技巧九：深分页查询优化（延迟关联）

深分页查询 LIMIT 100000, 10 性能极差，因为它需要先扫描并排序100010行，然后丢弃前100000行，只返回最后10行。

一个高效的优化方法是“延迟关联”：先利用覆盖索引快速定位到需要的主键，再通过主键关联回原表获取完整数据。

-- 原始慢查询
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 优化后的延迟关联写法
SELECT *
FROM orders t1
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) t2 ON t1.id = t2.id;

对于数据只增不减的场景（如按时间或自增ID排序），“游标分页”是更优解：记录上一页最后一条记录的ID，下次查询时直接定位。

SELECT * FROM orders WHERE id > last_id ORDER BY id LIMIT 10;

技巧十：定期分析表，更新统计信息

MySQL优化器依赖表的统计信息（如数据分布、基数）来选择它认为最优的执行计划。如果统计信息过时，优化器就可能做出错误判断，例如选择了全表扫描而非更快的索引。

在以下情况后，建议手动更新统计信息：

-- 更新表的统计信息
ANALYZE TABLE orders;

-- 重建表并整理碎片（适用于大量删除操作后）
OPTIMIZE TABLE orders;

虽然MySQL 8.0默认开启了统计信息的持久化和自动更新，但在进行大规模数据变更后，手动执行一次 ANALYZE TABLE 仍是稳妥的做法。

总结

说到底，索引是一把双刃剑。设计得当，它是提升查询性能的“屠龙刀”；盲目滥用，它就会变成拖慢系统、浪费资源的“自残剑”。最稳妥的策略是：创建索引前，务必用 EXPLAIN 分析执行计划；索引创建后，则要借助监控系统定期清理无效和冗余的索引。保持索引的简洁与高效，是数据库性能稳定的重要基石。