1. 为什么一个小小的索引能让查询快上百倍
做后端开发这几年，我见过太多因为索引问题把数据库搞垮的案例。最典型的一次是刚接手一个电商项目，订单表两千多万行，运营同学按用户昵称查历史订单，一条SQL跑了14秒，接口超时直接报警。排查的时候发现这张表除了主键，一个二级索引都没建。
很多人对索引的理解停留在“加了索引查询会变快”这个层面，但真要问一句“为什么变快、快在哪个环节、该怎么设计”，就答不上来了。这篇文章不讲那些虚的，直接把MySQL索引从原理到实操捋一遍，重点放在“怎么给表加索引”和“加索引时最容易踩的坑”上。看完你起码能回答三个问题：一张表该建哪些索引、索引为什么能加速、加了索引之后有哪些看不见的代价。
2. 索引本质上是给数据做了一份“有序目录”
2.1 没有索引时MySQL是怎么找数据的
先想一想你查一本书里某个知识点，如果书没有目录，你得从第一页翻到最后一页，一页一页找。全表扫描就是这个思路，MySQL从表的第一行数据开始，挨个读每一行，判断是否满足WHERE条件，直到扫完整个表。
两张千万级的表做连接查询，如果都没有索引，MySQL只能对每一行都去对方表里做一次全表扫描。千万乘以千万，这个量级的计算量换成时间就是分钟级别。实际业务里根本扛不住，所以全表扫描通常只适用于小表，或者你明确知道自己要把整张表的数据都查出来。
2.2 索引结构为什么能加速
MySQL默认的索引结构是B+树，你可以把它理解成一本自带层级目录的书。树根是目录页，往下是中间层，最底层是有序的数据页链表。查值的时候，沿着树从根节点一层一层往下找，每次都能排除掉一大半数据，最终定位到目标行所在的叶子节点。
B+树的查询复杂度是O(log N)。拿一百万行数据来说，全表扫描可能要读几十万行，走B+树索引只需要大概20次以内的磁盘IO就能定位到数据。这就是“加了索引快百倍”的核心原因。
2.3 主键索引、二级索引、覆盖索引各管什么
很多刚接触MySQL的人一听到“索引类型”就头大，其实日常开发只需要分清这三类：
- 主键索引：表的主键自动生成，叶子节点直接存整行数据。按主键查，走的就是主键索引，速度最快。
- 二级索引：咱们手动创建的普通索引都算二级索引，叶子节点存“索引列的值 + 主键值”。查数据时，先找到主键值，再回表去主键索引里拿整行。
- 覆盖索引：当查询要的字段恰好全部包含在索引列里，MySQL就不需要回表了，直接拿索引里的数据返回。这是性能优化里很实用的一招。
埋个伏笔：二级索引“叶子节点存的是索引值+主键值”这件事，后面讲索引更新时的死锁问题会用到。
3. 建索引之前，先搞懂这五种索引到底怎么选
3.1 普通索引、唯一索引、联合索引、前缀索引、全文索引
建索引不是无脑CREATE INDEX，不同类型的索引有不同的适用场景。
| 索引类型 | 特点 | 适用场景 |
|---|---|---|
| 普通索引 | 只加速查询，不限制值唯一 | 大多数常规查询字段 |
| 唯一索引 | 值不允许重复，相当于约束+提速 | 手机号、身份证号、订单号等业务唯一字段 |
| 联合索引 | 多个字段一起建索引，按最左前缀原则生效 | 多条件组合查询，如“用户ID+订单状态” |
| 前缀索引 | 只对字符串的前N个字符建索引 | 长文本字段，如文章标题、URL |
| 全文索引 | 分词匹配，支持模糊搜索 | 文章内容、商品描述等大文本搜索 |
唯一索引和普通索引的区别，用一句话说：业务上要求字段值不能重复的，直接建唯一索引，既省一次查询校验，又防止脏数据。
3.2 联合索引的最左前缀原则
联合索引是最容易用错的。比如你建了一个索引(A, B, C)，MySQL能利用这个索引的查询条件是：
- 只查A
- 查A、B
- 查A、B、C
如果直接查B，或者查B、C，就走不上这个索引。这就是所谓的“最左前缀原则”。很多人建了联合索引，发现SQL没走索引，十有八九是查询条件的顺序没对齐最左前缀。
3.3 什么时候不该建索引
建索引有成本，选字段必须克制。以下情况建了索引反而是负担：
- 数据量太小的表，比如几百行，全表扫描比走索引还快，建了纯属浪费空间。
- 频繁更新的字段。索引要跟着数据一起改，更新越频繁，索引维护成本越高。
- 区分度低的字段。像性别只有“男”“女”两个值，建了索引也过滤不掉多少数据，反而增加索引树的高度，查询更快谈不上，写得更慢是真的。
我见过有团队给一张表建了12个索引，表每次写入要同时维护12棵索引树，压测时写入性能直接掉了三成。索引不是越多越好，够用就行。
4. 实操：给MySQL表加索引的完整步骤
4.1 先确认当前表结构和已有索引
动手之前，先看看这张表长什么样、已经有哪些索引。命令行连上MySQL之后执行：
SHOW CREATE TABLE orders\G;或者用：
SHOW INDEX FROM orders;前者能看到完整的建表语句和索引定义，后者能看到索引的字段、顺序、是否唯一、基数等信息。我一般两个都执行，先看全貌，再看细节。
4.2 创建索引的三种方式
方式一：建表时直接定义索引。
CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_user_status (user_id, status), UNIQUE KEY uk_order_no (order_no) );方式二：已有表追加索引。
ALTER TABLE orders ADD INDEX idx_created_at (created_at); ALTER TABLE orders ADD UNIQUE INDEX uk_order_no (order_no); ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);方式三：用CREATE INDEX直接建。
CREATE INDEX idx_created_at ON orders(created_at); CREATE UNIQUE INDEX uk_order_no ON orders(order_no);三种方式最终效果相同，区别在于：建表时定义适合新表；ALTER ADD适合老表改造；CREATE INDEX语法更直观，适合临时补索引。
4.3 联合索引字段顺序怎么排
联合索引的顺序不是拍脑袋定的，有几个经验法则：
- 区分度高的字段放前面。比如user_id的区分度比status高，idx_user_status(user_id, status)就比idx_status_user(status, user_id)好用。
- 经常用来等值查询的字段放前面，范围查询的字段放后面。等值匹配能精确定位，范围条件会中断后面的索引匹配。
- 考虑覆盖索引，把SELECT要的字段尽量塞进索引里。
举个实际例子：订单表最常见的查询是“查某个用户最近30天的订单”，那联合索引就可以设计成(user_id, created_at)。查的时候WHERE user_id = 123 AND created_at >= '2025-01-01'，先按user_id等值命中，再在created_at上范围扫描，效率很高。
4.4 大表加索引的正确姿势
给千万级大表加索引，如果直接执行ALTER TABLE，MySQL 5.6以下的版本会锁表，业务写入直接全停。即使5.6以上支持了在线DDL，默认行为对某些操作仍有限制。
生产环境我推荐用gh-ost或者pt-online-schema-change这类工具做在线变更，或者至少分几步操作：
- 先确认磁盘空间，因为在线加索引需要额外临时空间。
- 在业务低峰期执行。
- 加完索引用SHOW INDEX确认索引创建成功。
- 观察一段时间的主从延迟和慢查询日志。
小厂没有专职DBA的话，线上直接ALTER ADD INDEX也不是不能用，但一定要选凌晨低峰期，并且先备份。
5. 加完索引，怎么确认SQL真的走了索引
5.1 EXPLAIN是索引调优的第一工具
索引建好了，不代表MySQL一定会用。用EXPLAIN看执行计划：
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND created_at >= '2025-01-01'\G;重点看几个字段：
- type：const、eq_ref、ref、range算好的访问类型；ALL就是全表扫描，要警惕。
- key：实际用到的索引名。
- rows：预估扫描行数，越小越好。
- Extra：如果出现Using index，说明走了覆盖索引；如果出现Using filesort，说明排序没用上索引，可能需要优化。
5.2 明明建了索引却不走，原因通常有这么几个
遇到“索引建了但没生效”，不要急着骂MySQL，先按顺序排查：
- WHERE条件的字段做了函数运算，比如WHERE DATE(created_at) = '2025-01-01'，索引就废了，应该改成created_at >= '2025-01-01' AND created_at < '2025-01-02'。
- LIKE以%开头，比如LIKE '%abc'，索引用不上，改成前缀匹配LIKE 'abc%'。
- 隐式类型转换，比如字符串字段用数字去查，MySQL会做类型转换，导致索引失效。
- 联合索引没按最左前缀用，前面字段没带上。
- 优化器觉得全表扫描更快，这种情况通常要考虑是不是数据量太小或者统计信息不准，执行ANALYZE TABLE更新统计信息试试。
5.3 覆盖索引怎么设计最划算
覆盖索引的收益非常大。比如订单列表页只需要显示订单号、状态、创建时间，普通做法要回表拿整行数据。如果建立一个(order_no, status, created_at)的联合索引，查询就能直接从索引里拿数据，连回表都省了。
我实际做报表接口时，把五六千万行的流水表查询从2秒压到80毫秒，核心就是覆盖索引。方法就是： SELECT要哪几个字段，就把哪几个字段建进联合索引，WHERE条件字段放最前面。
6. 加索引和更新数据时，最容易踩的锁与死锁坑
6.1 二级索引更新的加锁顺序
这条是很多老手都容易忽视的坑。MySQL在更新一条记录时，如果涉及二级索引，加锁顺序通常是：先锁二级索引项，再回表锁主键记录。
这个顺序在高并发事务里有可能形成两个事务互相等待的局面。事务A更新了索引项X，正在等主键记录Y；事务B更新了索引项Y，正在等主键记录X。两边都拿着对方想要的东西，就死锁了。
6.2 为什么这个时间窗口容易形成交叉死锁
用订单表模拟一下：
- 事务1执行：UPDATE orders SET status = 1 WHERE order_no = 'A001';
- 事务2执行：UPDATE orders SET status = 2 WHERE order_no = 'A002';
看似两个事务操作不同订单，但如果order_no的二级索引页存在锁竞争，或者两个订单落在同一索引页上，锁互相等待的概率就会上升。更典型的情况是批量更新撞到同一个范围，比如事务1更新ID范围1到100，事务2更新ID范围50到150，二级索引树和主键树上的锁交叉，就可能死锁。
6.3 实际工作中怎么规避死锁
- 多条更新语句按固定顺序执行，让所有事务都以相同的加锁顺序访问资源。
- 批量更新不要一次update太多行，分批处理，每批几百行，缩短锁持有时间。
- 尽量让更新操作走覆盖索引，减少回表次数，也就减少了锁的交叉点。
- 发生死锁时，MySQL会自动回滚一个事务，应用层要做好重试机制。
- 用SHOW ENGINE INNODB STATUS查看最近一次死锁的详细信息，里面有具体的事务和锁等待链，排查时信息量很大。
我遇到过的一个真实案例：两个服务分别更新订单表和订单明细表，一个先更新主表再更新明细表，另一个先更新明细表再更新主表，结果每天凌晨定时任务高峰必现死锁。最后统一改成“主表-明细表”的固定更新顺序，问题直接消失。
7. 索引设计常见问题速查与实战心得
7.1 高频问题速查表
| 问题 | 原因 | 处理建议 |
|---|---|---|
| 查询慢但加了索引没变化 | SQL写法没走索引 | EXPLAIN检查执行计划，按5.2排查 |
| 加了索引写入变慢 | 索引过多或更新频繁 | 精简索引，控制数量在5个左右 |
| 联合索引查询失效 | 没按最左前缀 | 调整WHERE条件顺序，或调整索引字段顺序 |
| 唯一索引重复报错 | 已存在重复数据 | 先清理重复数据再建唯一索引 |
| 大表加索引锁表 | DDL期间锁表 | 用pt-osc或gh-ost在线变更 |
| 主从延迟严重 | 大事务或大批量更新 | 分批更新，避免长事务 |
7.2 我一般怎么设计一张新表的索引
新表索引设计，我的固定套路是：
- 先找出所有查询场景，列出WHERE条件、ORDER BY、GROUP BY和JOIN字段。
- 主键保证一定有，用自增ID或者有序UUID。
- 业务唯一字段直接上唯一索引。
- 高频查询场景按“等值+范围+排序”顺序建联合索引。
- SELECT的字段如果能塞进索引，就顺手做成覆盖索引。
- 索引总数控制在5到6个以内，实在要更多，先分析是不是表设计有问题。
这个流程做下来，基本能覆盖大多数业务场景。后面就算需求变化，也可以针对性加索引，不用推倒重来。
7.3 最后再分享一个小技巧
索引命名规范非常重要。我习惯用前缀区分类型：普通索引idx_字段名，唯一索引uk_字段名，联合索引idx_字段1_字段2。这样做的好处是，过半年回头看表结构，一眼就知道每个索引是干嘛的，不会出现一堆不知道能不能删的冗余索引。
另外定期用sys.schema_unused_indexes查一下有没有一直没被用到的索引，发现长期未使用的，确认没有业务依赖就直接删除。索引是拿来用的，不是拿来存着看的。冗余索引占空间、拖慢写入，删掉之后unused_indexes视图会告诉你，体感很直接。
加索引这件事，听起来简单，做起来全是细节。理解了B+树原理、拿捏了联合索引顺序、避开了死锁的坑，你就已经超过九成的开发同学了。
