MySQL索引设计避坑指南：这些错误别再犯了

同事写了个SQL，生产环境跑了8秒，被DBA追着骂。

一看执行计划，全表扫描，100万行数据一行行扫。

“不是加了索引吗？”
“加了，但没用上。”

索引这东西，加得不对比不加还糟糕。整理一下常见的索引坑。

一、索引失效的常见场景

1.1 对索引列做函数运算

-- 索引失效SELECT*FROMordersWHEREYEAR(create_time)=2024;-- 索引生效SELECT*FROMordersWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01';

对索引列用函数，优化器没法用索引。

同理：

-- 失效SELECT*FROMusersWHEREUPPER(username)='ADMIN';-- 如果经常这样查，建函数索引（MySQL 8.0+）CREATEINDEXidx_username_upperONusers((UPPER(username)));

1.2 隐式类型转换

-- phone是varchar类型-- 失效：传入数字，会发生隐式转换SELECT*FROMusersWHEREphone=13812345678;-- 生效：传入字符串SELECT*FROMusersWHEREphone='13812345678';

类型不匹配，MySQL会做隐式转换，相当于对列做了函数操作。

1.3 前导模糊查询

-- 失效SELECT*FROMproductsWHEREnameLIKE'%手机%';-- 生效（前缀匹配）SELECT*FROMproductsWHEREnameLIKE'手机%';

%放前面，没法用B+树的有序性。

解决方案：

• 用全文索引
• 用Elasticsearch

1.4 OR条件

-- 假设只有name有索引，age没有-- 失效SELECT*FROMusersWHEREname='张三'ORage=25;-- 解决方案1：给age也加索引-- 解决方案2：改成UNIONSELECT*FROMusersWHEREname='张三'UNIONSELECT*FROMusersWHEREage=25;

OR条件会导致索引失效，除非OR两边的列都有索引。

1.5 不等于条件

-- 可能失效（取决于数据分布）SELECT*FROMordersWHEREstatus!='completed';-- 如果status只有几个值，考虑改成SELECT*FROMordersWHEREstatusIN('pending','processing','failed');

!=和NOT IN通常无法利用索引，或者即使用了也是全索引扫描。

二、联合索引的坑

2.1 最左前缀原则

假设有索引：idx_abc (a, b, c)

-- 生效SELECT*FROMtWHEREa=1;SELECT*FROMtWHEREa=1ANDb=2;SELECT*FROMtWHEREa=1ANDb=2ANDc=3;SELECT*FROMtWHEREa=1ANDc=3;-- 只用到a-- 失效SELECT*FROMtWHEREb=2;SELECT*FROMtWHEREc=3;SELECT*FROMtWHEREb=2ANDc=3;

联合索引必须从最左列开始使用，中间不能跳过。

2.2 范围查询后的列失效

-- 索引：idx_abc (a, b, c)-- c用不到索引SELECT*FROMtWHEREa=1ANDb>10ANDc=3;-- 都能用到SELECT*FROMtWHEREa=1ANDb=10ANDc>3;

范围查询（>, <, BETWEEN, LIKE）会终止后续列的索引使用。

设计索引时，把等值查询的列放前面，范围查询的列放后面。

2.3 索引列顺序

-- 查询1：高频SELECT*FROMordersWHEREuser_id=1ANDstatus='pending';-- 查询2：低频SELECT*FROMordersWHEREstatus='pending';-- 正确设计：user_id放前面CREATEINDEXidx_user_statusONorders(user_id,status);-- 如果反过来，查询1能用，但查询1效率差（要扫描很多user_id）

高频查询的条件列放前面，区分度高的列放前面。

三、覆盖索引

3.1 什么是覆盖索引

-- 索引：idx_user_id_name (user_id, name)-- 覆盖索引：查询的列都在索引里，不用回表SELECTuser_id,nameFROMusersWHEREuser_id=1;-- 非覆盖：需要回表取phoneSELECTuser_id,name,phoneFROMusersWHEREuser_id=1;

覆盖索引避免回表，性能更好。

3.2 利用覆盖索引优化COUNT

-- 慢：需要扫描主键索引SELECTCOUNT(*)FROMusers;-- 快：选择最小的二级索引SELECTCOUNT(*)FROMusersFORCEINDEX(idx_status);

MySQL会自动选择最小的索引来COUNT，但有时选错了需要手动指定。

四、索引设计原则

4.1 选择性高的列优先

选择性 = 不重复的值 / 总行数

-- 查看列的选择性SELECTCOUNT(DISTINCTstatus)/COUNT(*)ASstatus_selectivity,COUNT(DISTINCTuser_id)/COUNT(*)ASuser_id_selectivityFROMorders;-- 假设结果-- status_selectivity: 0.0001（5个状态/10万行）-- user_id_selectivity: 0.8（8万用户/10万行）

user_id选择性高，更适合建索引。

status选择性低，单独建索引意义不大。

4.2 短索引优先

-- 对于很长的字符串，可以只索引前缀CREATEINDEXidx_titleONarticles(title(20));-- 确定前缀长度：保证足够的选择性SELECTCOUNT(DISTINCTLEFT(title,10))/COUNT(*)ASsel_10,COUNT(DISTINCTLEFT(title,20))/COUNT(*)ASsel_20,COUNT(DISTINCTtitle)/COUNT(*)ASsel_fullFROMarticles;

前缀索引更短，同样空间能存更多数据，效率更高。

4.3 避免冗余索引

-- 冗余：idx_a已经被idx_ab覆盖CREATEINDEXidx_aONt(a);CREATEINDEXidx_abONt(a,b);-- 不冗余：idx_ba的顺序不同CREATEINDEXidx_abONt(a,b);CREATEINDEXidx_baONt(b,a);

定期检查冗余索引：

-- MySQL 8.0+SELECT*FROMsys.schema_redundant_indexes;

4.4 避免过度索引

索引不是越多越好：

• 占用磁盘空间
• 插入/更新/删除都要维护索引
• 优化器选择困难

一般一个表不超过5-6个索引。

五、EXPLAIN看执行计划

5.1 关键字段

EXPLAINSELECT*FROMordersWHEREuser_id=1;

5.2 常见type解释

-- ALL：全表扫描，最差EXPLAINSELECT*FROMusersWHEREage=25;-- age没索引-- index：全索引扫描EXPLAINSELECTidFROMusers;-- range：范围扫描EXPLAINSELECT*FROMusersWHEREid>100;-- ref：非唯一索引等值查询EXPLAINSELECT*FROMordersWHEREuser_id=1;-- eq_ref：唯一索引等值查询EXPLAINSELECT*FROMusersWHEREid=1;-- const：主键/唯一索引等值，最多一行EXPLAINSELECT*FROMusersWHEREid=1;

5.3 Extra信息

-- Using index：覆盖索引，好-- Using where：用了WHERE过滤，正常-- Using temporary：用了临时表，需要优化-- Using filesort：用了文件排序，需要优化

看到Using temporary或Using filesort，基本都要优化。

六、真实案例

案例1：订单查询优化

原SQL（执行8秒）：

SELECT*FROMordersWHEREuser_id=12345ANDstatus='pending'ANDcreate_time>'2024-01-01'ORDERBYcreate_timeDESCLIMIT20;

EXPLAIN显示：

• type: ALL
• rows: 1000000
• Extra: Using where; Using filesort

问题：没用到索引，全表扫描+文件排序。

优化：

-- 建立联合索引CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);

优化后EXPLAIN：

• type: range
• rows: 234
• Extra: Using index condition

执行时间：8ms

案例2：分页查询优化

原SQL：

SELECT*FROMlogsORDERBYidDESCLIMIT100000,20;

问题：深分页，要扫描10万行再丢弃。

优化方案1：记录上次ID

-- 前端传上一页最小IDSELECT*FROMlogsWHEREid<12345678ORDERBYidDESCLIMIT20;

优化方案2：延迟关联

SELECTl.*FROMlogs lINNERJOIN(SELECTidFROMlogsORDERBYidDESCLIMIT100000,20)AStONl.id=t.id;

子查询只查ID（覆盖索引），再关联取全量数据。

七、运维小技巧

大表加索引会锁表，生产环境要用pt-online-schema-change或gh-ost：

# pt-online-schema-changept-online-schema-change\--alter"ADD INDEX idx_user_id (user_id)"\--execute\D=mydb,t=orders,h=localhost

我们有几个数据库在不同机房，之前同步DDL操作很麻烦。现在用星空组网把几个节点连起来，统一用Ansible批量执行就方便多了。

总结

索引设计核心原则：

索引失效常见原因：

索引优化这块经验欢迎交流~