引言：索引的"双刃剑"特性

作为数据库性能优化的核心手段，索引就像图书馆的目录系统——用得得当能快速定位数据，但若使用不当反而会成为性能负担。笔者曾处理过一个电商平台案例：某商品表200万数据量下，看似合理的索引组合却导致查询延迟从50ms飙升到2秒，最终发现是隐式的索引失效问题。本文将结合实战经验，系统梳理MySQL索引的正确打开方式。

一、六大索引失效场景及规避方案

1. 最左前缀原则破坏

联合索引(a,b,c)使用时：sql
-- 有效用例（依次使用a、ab、abc）
SELECT * FROM table WHERE a=1 AND b=2

-- 失效场景（跳过a直接使用b）
SELECT * FROM table WHERE b=2
解决方案：像电话号码分段记忆那样严格遵循从左到右的匹配顺序，必要时调整字段顺序。

2. 隐式类型转换陷阱

当字段定义为VARCHAR但用数字查询时：sql
-- 索引失效（userid是字符串类型） SELECT * FROM users WHERE userid = 10086

-- 修正方案
SELECT * FROM users WHERE user_id = '10086'
实战建议：在Java实体类中统一使用String类型对应VARCHAR字段。

3. 函数操作导致的失效

sql
-- 全表扫描（DATEFORMAT破坏索引） SELECT * FROM orders WHERE DATEFORMAT(create_time,'%Y-%m')='2023-01'

-- 优化方案（使用范围查询）
SELECT * FROM orders
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31'
特殊技巧：MySQL 8.0+支持函数索引，可创建INDEX idx_month((DATE_FORMAT(create_time,'%Y-%m')))

4. 不当的LIKE用法

sql
-- 只有前缀匹配能走索引（%放右边）
SELECT * FROM products WHERE name LIKE '苹果%'

-- 全匹配也可以
SELECT * FROM products WHERE name LIKE '苹果手机'
业务折衷：必要时上全文索引或ES搜索引擎。

5. 索引列参与运算

sql
-- 失效写法（age列无法直接使用）
SELECT * FROM users WHERE age+1 > 20

-- 优化方案
SELECT * FROM users WHERE age > 19

6. OR连接的隐患

sql -- 部分失效（若name有索引而address无索引） SELECT * FROM customers WHERE name='张三' OR address='北京'
替代方案：改用UNION ALL拆分查询。

二、索引创建五大黄金准则

1. 选择性优先原则

通过计算字段选择性：
sql SELECT COUNT(DISTINCT gender)/COUNT(*) AS gender_selectivity, COUNT(DISTINCT mobile)/COUNT(*) AS mobile_selectivity FROM users;
阈值建议：高于0.1的字段才适合单独建索引。

2. 联合索引排列策略

遵循"高频+高选择性+等值优先"的排列顺序：
-- 正确示例（status高频等值查询，create_time范围查询） ALTER TABLE orders ADD INDEX idx_combo(status, user_id, create_time);

3. 前缀索引的妙用

对于长文本字段：sql
-- 测试不同长度的区分度
SELECT
COUNT(DISTINCT LEFT(description,10))/COUNT() AS prefix_10, COUNT(DISTINCT LEFT(description,20))/COUNT() AS prefix_20
FROM articles;

-- 创建前缀索引
ALTER TABLE articles ADD INDEX idx_desc(description(15));

4. 避免冗余索引

使用sys库检测重复索引：
sql SELECT * FROM sys.schema_redundant_indexes;
典型案例：已有(a,b)索引又单独创建(a)索引。

5. 索引数量控制

硬性指标：
- 单表索引建议不超过5个
- 单个索引字段数不超过3个
- 索引总长度不超过767字节（InnoDB限制）

三、性能监控与调优闭环

1. 慢查询分析：sql
   -- 开启慢日志
   SET GLOBAL slowquerylog = ON;
   SET GLOBAL longquerytime = 1;

-- 查看执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id=100 AND status=1;

1. 索引使用统计：
   sql -- 查看索引命中率 SELECT * FROM sys.schema_index_statistics WHERE table_schema='your_db';

2. 热力图优化法：
   定期使用pt-index-usage工具分析未使用索引。

结语：平衡的艺术

索引优化永远没有银弹，需要根据业务查询模式动态调整。某金融系统在遵循上述规范后，QPS从200提升到1500，但同时要警惕"过度索引"——每次ALTER TABLE的索引变更都会导致锁表风险。建议在开发环境使用像gh-ost这样的在线改表工具，毕竟在数据库领域，预防总是比治疗更经济。