怎样才能有效地减少索引失效的情况？

在 MySQL 数据库中，索引失效是导致慢 SQL 的常见原因之一。要有效减少索引失效的情况，需从索引设计、SQL 编写和数据库架构等多个层面进行优化。以下是具体的解决策略及实践方法：

一、避免在索引列上使用函数或表达式

失效场景

当查询条件对索引列使用函数或表达式时，索引将无法生效：

 

-- 索引失效（假设user_name有索引）
SELECT * FROM users WHERE UPPER(user_name) = 'ADMIN';
-- 索引失效
SELECT * FROM orders WHERE order_time + INTERVAL '1 DAY' < NOW();

 

解决方案

1. 重写查询逻辑，避免对索引列做运算：

 

-- 优化后
SELECT * FROM users WHERE user_name = 'ADMIN' OR user_name = 'admin';
-- 或创建函数索引（MySQL 8.0+支持）
CREATE INDEX idx_user_name_upper ON users(UPPER(user_name));

2. 使用表达式索引（适用于复杂计算场景）：

CREATE INDEX idx_order_time_next ON orders((order_time + INTERVAL '1 DAY'));

 

二、合理使用 LIKE 查询

失效场景

当LIKE的通配符%出现在开头时，索引无法使用：

 

-- 索引失效（假设product_name有索引）
SELECT * FROM products WHERE product_name LIKE '%phone';

 

解决方案

1. 仅使用前缀匹配：

 

-- 优化后（索引有效）
SELECT * FROM products WHERE product_name LIKE 'phone%';

 

2. 使用全文索引（适用于模糊查询场景）：

CREATE FULLTEXT INDEX idx_product_name ON products(product_name);
-- 使用MATCH AGAINST查询
SELECT * FROM products WHERE MATCH(product_name) AGAINST('phone');

3. 分区索引或倒排索引：将前缀字段单独存储，例如：

ALTER TABLE products ADD COLUMN product_name_prefix CHAR(10) GENERATED ALWAYS AS (LEFT(product_name, 10)) STORED;
CREATE INDEX idx_prefix ON products(product_name_prefix);

 

三、优化 OR 条件查询

失效场景

当OR连接的条件中存在无索引列时，整个OR条件无法使用索引：

 

-- 索引失效（假设user_id有索引，email无索引）
SELECT * FROM users WHERE user_id = 100 OR email = 'test@example.com';

 

解决方案

1. 用 UNION 拆分查询：

-- 优化后（索引有效）
(SELECT * FROM users WHERE user_id = 100)
UNION
(SELECT * FROM users WHERE email = 'test@example.com');

 

2. 为 OR 条件的所有列创建索引：

CREATE INDEX idx_user_id_email ON users(user_id, email);

3. 合并条件为复合索引（适用于频繁查询场景）：

CREATE INDEX idx_user_id_or_email ON users(user_id) INCLUDE(email);

 

四、遵循复合索引的最左前缀原则

失效场景

复合索引未按 “最左前缀” 使用时，索引部分失效：

-- 假设复合索引为(idx_a_b, a, b)
SELECT * FROM table WHERE b = 1;  -- 索引失效，未使用最左列a

解决方案

1. 查询条件包含最左列：

-- 优化后（索引有效）
SELECT * FROM table WHERE a = 1 AND b = 1;

2. 创建多列复合索引，按查询频率排序：

-- 常用查询为WHERE a AND b AND c，创建索引(a, b, c)
CREATE INDEX idx_a_b_c ON table(a, b, c);

3. 使用覆盖索引，减少回表查询：

CREATE INDEX idx_a_b_c ON table(a, b, c) INCLUDE(d);
-- 查询时a/b/c使用索引，d直接从索引获取，无需回表

 

五、避免数据类型不匹配

失效场景

查询条件与索引列的数据类型不一致，导致隐式转换：

 

-- 假设user_id为INT类型，查询时传入字符串（索引失效）
SELECT * FROM users WHERE user_id = '100';

 

解决方案

1. 确保参数类型一致：

-- 优化后（索引有效）
SELECT * FROM users WHERE user_id = 100;

2. 使用 CAST 显式转换（迫不得已时）：

SELECT * FROM users WHERE user_id = CAST('100' AS SIGNED);

 

六、处理 NULL 值查询

失效场景

MySQL 索引默认不包含 NULL 值，IS NULL查询可能无法使用索引：

 

-- 假设email有索引，但索引不包含NULL值（索引失效）
SELECT * FROM users WHERE email IS NULL;

 

解决方案

1. 为字段设置默认值，避免 NULL 值：

ALTER TABLE users MODIFY COLUMN email VARCHAR(100) DEFAULT '';

2. 创建包含 NULL 值的索引（需特殊处理）：

-- 方法1：使用函数索引
CREATE INDEX idx_email ON users(IFNULL(email, ''));
-- 方法2：分表查询（适用于大量NULL值场景）
SELECT * FROM users WHERE email IS NULL
UNION
SELECT * FROM users WHERE email = '';

 

七、避免大表 JOIN 时的索引失效

失效场景

多表 JOIN 时，连接条件未使用索引：

 

-- 假设orders表的customer_id无索引（索引失效）
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

 

解决方案

1. 为 JOIN 条件创建索引：

ALTER TABLE orders ADD INDEX idx_customer_id ON orders(customer_id);

2. 优化 JOIN 顺序，先连接小表：

 

-- 优化后（先连接小表customers）
SELECT * FROM customers c JOIN orders o ON o.customer_id = c.id;

3. 使用 EXPLAIN 分析执行计划，调整 JOIN 方式：

EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- 根据执行计划调整索引或JOIN顺序

 

八、定期维护索引

1. 重建索引，清理碎片：

ALTER TABLE table_name REBUILD INDEX index_name;

2. 分析表结构，更新统计信息：

ANALYZE TABLE table_name;

3. 删除冗余索引，减少写入开销：

-- 查看未使用的索引
SELECT * FROM information_schema.INDEX_STATISTICS WHERE rows_read = 0;

 

九、最佳实践总结

1. 设计阶段：
   • 根据高频查询场景设计索引，优先创建复合索引而非单列索引。
   • 使用EXPLAIN和SHOW PROFILE提前验证索引有效性。
2. 开发阶段：
   • 避免在索引列上做运算，保持查询条件与索引列的数据类型一致。
   • 用UNION替代低效的OR查询，拆分复杂子查询为 JOIN。
3. 运维阶段：
   • 定期监控索引使用情况（如通过pt-index-usage工具）。
   • 对大表进行分区或分表，减少单表索引维护成本。

通过以上策略，可以显著降低索引失效的概率，提升 SQL 查询效率。实际优化中需结合业务场景，灵活选择合适的方案，并通过压测验证优化效果。