数据库性能优化十大技巧从索引失效到查询提速

聊到数据库性能优化，“索引”几乎是每个开发者都绕不开的核心话题。很多人都知道索引能加速查询，但被问到“怎么建索引才能让查询最快”时，答案往往停留在“给WHERE后面的列加索引”这个层面。实际上，索引优化的门道远比这要深。今天，我们就来系统梳理一下工作中最高频、最实用的十个索引优化技巧。

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

一个常见的误区是，给所有出现在WHERE子句里的列都加上索引。结果呢？有时加了索引，查询反而更慢了。比如给“性别”这种列建索引，查询男性用户时，数据库依然需要扫描海量数据，索引几乎没起作用。

什么是选择性？

这里的关键在于“选择性”。它的计算公式是：COUNT(DISTINCT col) / COUNT(*)。这个比值越接近1，列的唯一性越高，选择性就越好。高选择性的列能像精准的筛子一样，快速过滤掉大量无关数据。

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

像性别（通常只有两三种值）就属于低选择性列，建索引价值不大。而用户ID、订单号这类近乎唯一的列，才是索引的理想选择。

底层原理

从InnoDB的B+Tree索引结构来看就很好理解了。索引树的每个叶子节点存储一个键值和对应的行指针。当某个键值（比如“男”）对应成千上万行数据时，即便通过索引树快速定位到了这个键，后续需要回表读取的数据量依然巨大，其代价可能远超直接全表扫描。这时，优化器往往会放弃使用索引。

正确做法

核心原则是：优先在高选择性列上建立索引。如果业务查询中必须用到低选择性字段（例如订单状态status），一个更聪明的做法是把它放在联合索引的末尾，作为二次筛选的条件。

-- 不推荐：单独为低选择性列建索引
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)在物理上是一棵先按a排序、a相同再按b排序、b相同再按c排序的B+Tree。因此，查询条件必须从最左边的列开始匹配，才能有效利用这棵索引树。

正确与错误示例

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

-- 正确：匹配索引前两列
SELECT * FROM t WHERE a = 1 AND b = 2;

-- 正确：仅匹配最左列
SELECT * FROM t WHERE a = 1;

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

-- 注意：范围查询后的列无法走索引
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3; -- c列不会使用索引查找

优化建议

设计联合索引时，尽量把等值查询的列放在左边，范围查询的列放在右边。确保你的高频查询条件能够覆盖索引的最左前缀。

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

什么是回表？

InnoDB中，二级索引的叶子节点只存储主键值。通过二级索引找到主键后，还需要回到主键索引（聚簇索引）中去查找完整的行数据，这个过程就是“回表”。回表操作是随机IO，性能开销很大。

覆盖索引

如果索引中已经包含了查询语句所需要的全部字段，MySQL就可以直接从索引中取得数据，无需回表，这就是“覆盖索引”。它能将查询性能提升数倍甚至数十倍。

-- 低效：仅idx_id_card索引，查询name和age需要回表
SELECT name, age FROM user WHERE id_card = 'xxx';

-- 高效：创建覆盖索引
ALTER TABLE user ADD INDEX idx_id_card_name_age (id_card, name, age);
SELECT name, age FROM user WHERE id_card = 'xxx'; -- 无需回表，直接返回

当然，覆盖索引会增加索引体积和写入维护成本，因此适用于那些查询字段固定且访问极其频繁的场景。

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

在索引列上进行计算或函数调用，是导致索引失效的经典原因之一。

-- 错误：对索引列使用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';

其他常见错误

-- 错误：隐式类型转换（phone字段是VARCHAR类型）
SELECT * FROM user WHERE phone = 13800000000;
-- 正确：保持类型一致
SELECT * FROM user WHERE phone = '13800000000';

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

其底层原理在于，B+Tree索引是按照列的原始值排序的。任何对列值的加工都会破坏这种有序性，使得数据库无法利用索引进行快速的范围定位。

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

什么是索引下推（ICP）？

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

开启ICP后，存储引擎会在索引遍历过程中，直接利用索引中包含的age列进行过滤，只对同时满足name LIKE '张%'和age = 25的记录进行回表。这显著减少了不必要的回表操作。

开启与验证

ICP默认是开启的，可以通过以下命令查看和设置：

SHOW VARIABLES LIKE 'optimizer_switch';
SET optimizer_switch='index_condition_pushdown=on';

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

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

当WHERE条件中使用OR连接不同条件时，尤其是这些条件分别涉及不同的索引列，优化器可能无法高效地使用索引，转而选择全表扫描。

-- 可能低效
SELECT * FROM user WHERE status = 1 OR status = 2;

优化方案

对于同一个字段的多个等值条件，优先使用IN查询：

SELECT * FROM user WHERE status IN (1, 2);

如果IN的效果不佳，或者OR连接的是不同字段，可以考虑使用UNION ALL（确保结果无重复）来强制走多个索引再合并结果：

SELECT * FROM user WHERE status = 1
UNION ALL
SELECT * FROM user WHERE status = 2;

其原理在于，优化器对IN查询有专门的优化处理，而UNION ALL则明确地将复杂查询拆分成多个可以独立使用索引的简单查询。

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

对于像VARCHAR(255)这样的长文本字段，建立完整长度的索引会占用大量磁盘空间，并影响插入、更新速度。这时可以考虑前缀索引。

前缀索引

只对字段的前N个字符建立索引。例如，邮箱的前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;

选择一个选择性接近完整列选择性（total_sel）的最小长度即可。需要注意的是，前缀索引无法用于ORDER BY、GROUP BY操作，也不能实现覆盖索引。

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

索引不是建得越多越好。无用的索引不仅浪费存储空间，还会降低DML（增删改）语句的性能。常见的冗余索引包括：

• 已有INDEX(a, b)，又单独建了INDEX(a)，后者就是冗余的。
• INDEX(a, b)和INDEX(b, a)顺序不同，功能有重叠，需根据查询模式评估保留哪一个。

查找未使用和冗余的索引

在开启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的索引可以考虑删除。此外，MySQL的sys库提供了更直观的冗余索引视图：

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;

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

优化器依靠表的统计信息（如数据分布、基数）来决定使用哪个索引。如果统计信息过时，优化器就可能做出错误的选择，例如本该走索引却走了全表扫描。

手动更新

-- 更新统计信息
ANALYZE TABLE orders;
-- 重建表并整理碎片（适用于大量删除操作后）
OPTIMIZE TABLE orders;

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

总结

说到底，索引是一把双刃剑。用得好，它是提升查询性能的“屠龙刀”；用不好，反而会成为拖慢系统、浪费资源的“自残剑”。

最关键的实践原则就两条：第一，在创建索引前，养成用EXPLAIN分析执行计划的习惯；第二，建立索引后，定期通过监控工具清理无效和冗余的索引。做到这些，你的数据库性能表现一定会更加稳健。