第 11 章索引基础与创建

站长2026-10-23 14:00:00

内容摘要

索引是数据库性能优化的第一课，也是性价比最高的优化手段——一张大表没索引时查一条数据可能要扫全表几秒钟，建好索引后毫秒返回。本篇从"为什么需要索引"出发，讲清楚 InnoDB 默认的 B+Tree 索引结构，以及唯一索引、复合索引、全文索引的创建与查看方式，并引入覆盖索引这个关键概念。学完后你能为业务表合理设计索引。本示例基于 MySQL 8.0 语法。

📖 本篇简介：索引是数据库性能优化的第一课，也是性价比最高的优化手段——一张大表没索引时查一条数据可能要扫全表几秒钟，建好索引后毫秒返回。本篇从"为什么需要索引"出发，讲清楚 InnoDB 默认的 B+Tree 索引结构，以及唯一索引、复合索引、全文索引的创建与查看方式，并引入覆盖索引这个关键概念。学完后你能为业务表合理设计索引。本示例基于 MySQL 8.0 语法。

1 为什么需要索引

假设订单表有 1000 万行，现在要查 WHERE order_no = '202610070001'。没有索引时，数据库只能从第一行开始一行行比对，直到找到目标——这叫全表扫描（Full Table Scan），数据量一大就慢。

有了索引，数据库相当于给"订单号"建了一本目录，像查字典一样先翻目录定位到页码，再直接跳到那一行读取，速度从"逐页翻"变成"直接跳"。

| 对比 | 无索引 | 有索引 |

|---|---|---|

| 查询方式 | 全表扫描，逐行比对 | B+Tree 定位，log 级 |

| 1000 万行按主键查 | 秒级甚至更久 | 毫秒级 |

| 写入成本 | 快（只写数据） | 略慢（还要维护索引） |

| 存储空间 | 小 | 额外占用 |

索引不是银弹：它加速查询，但拖慢写入（每次 INSERT/UPDATE/DELETE 都要同步维护索引树）、额外占磁盘。所以"该给哪些字段建索引"是个权衡问题，不是越多越好。

1.1 哪些字段适合建索引

经验法则——出现在这些位置的列优先考虑：

| 出现位置 | 例子 | 原因 |

|---|---|---|

| WHERE 过滤 | WHERE user_id=1 | 直接定位行 |

| JOIN 关联键 | ON a.uid=b.id | 被驱动表快速匹配 |

| ORDER BY / GROUP BY | ORDER BY create_time | 索引有序，免排序 |

| 唯一性业务约束 | 手机号、订单号 | 唯一索引兼顾约束 |

反过来，这些字段不建议单独建索引：

表很小（几百行），全表扫描比走索引还快；
字段区分度极低（性别、状态只有两三个值）；
频繁批量更新、极少查询的日志字段；
大文本字段直接建索引代价高，应改用全文索引。

2 索引的底层原理B+Tree

MySQL InnoDB 默认用 B+Tree（平衡多叉搜索树） 做索引结构。理解它的几个关键特征，后面所有索引规则都能推导出来：

1. 非叶子节点只存键值和指针，不存数据：所以一个节点能放很多键，树很"矮胖"，通常 3~4 层就能索引千万级数据，查一次最多 3~4 次磁盘 IO。

2. 叶子节点存完整数据（聚簇索引）或主键值（二级索引）：叶子节点之间用双向链表相连，方便范围查询和排序。

3. 数据按索引键有序排列：这就是为什么 =、>、BETWEEN、ORDER BY 能用上索引——数据本来就是排好的。

2.1 聚簇索引与二级索引

InnoDB 里：

聚簇索引（主键索引）：叶子节点直接存整行数据。一张表只有一个，就是主键。
二级索引（普通索引）：叶子节点存的是"索引列值 + 主键值"。通过二级索引查到主键后，再回聚簇索引查整行，这一步叫回表（回表查询）。

聚簇索引（主键 id）：
  叶子 → [id=1, 整行数据] ↔ [id=2, 整行数据] ↔ ...

二级索引（name）：
  叶子 → [name='张三', id=1] ↔ [name='李四', id=2] ↔ ...
  查到 id=1 后，再去聚簇索引取回整行（回表）

2.1.1 一次查询是怎么沿着 B+Tree 走的

以查 id=5000 为例，B+Tree 大约 3 层：

第 1 层（根节点）：[ ... | 1000 | 3000 | 5000 | 7000 | ... ]
                      5000 落在 (3000,5000] → 走对应指针
第 2 层（中间节点）：[ ... | 4000 | 5000 | 6000 | ... ]
                      5000 落在该区间 → 走对应指针
第 3 层（叶子节点）：[ id=5000, 整行数据 ]

不管表里有 100 万还是 1000 万行，路径长度几乎不变（树高只随数据量对数增长）。这就是索引"稳快"的本质。

2.2 用 sqlite3 实跑看索引效果

虽然本机没有 MySQL，但 sqlite3 也用 B+Tree 类结构，用 EXPLAIN QUERY PLAN 能直观看到"全表扫描"和"走索引"的差别：

# sqlite3 实跑：对比建索引前后的查询计划（Python 3.14 + sqlite3 3.53）
import sqlite3, random
con = sqlite3.connect(':memory:')
cur = con.cursor()
cur.execute('CREATE TABLE t(id INTEGER PRIMARY KEY, user_id INT, name TEXT)')
rows = [(i, random.randint(1, 1000), 'u%d' % i) for i in range(1, 2001)]
cur.executemany('INSERT INTO t VALUES(?,?,?)', rows)
print('--- before index ---')
for r in cur.execute('EXPLAIN QUERY PLAN SELECT * FROM t WHERE user_id=500'):
    print(r)
cur.execute('CREATE INDEX idx_uid ON t(user_id)')   -- 给 user_id 建索引
print('--- after index ---')
for r in cur.execute('EXPLAIN QUERY PLAN SELECT * FROM t WHERE user_id=500'):
    print(r)
con.close()

实跑输出：

--- before index ---
(2, 0, 216, 'SCAN t')
--- after index ---
(3, 0, 61, 'SEARCH t USING INDEX idx_uid (user_id=?)')

建索引前是 SCAN t（全表扫描 2000 行），建索引后变成 SEARCH t USING INDEX idx_uid（直接走索引定位）。MySQL 的 EXPLAIN 里对应的就是 type=ALL（全表扫描）和 ref/range（走索引），后面 EXPLAIN 专题会细讲。

2.3 InnoDB 还用到哪些索引结构

B+Tree 是默认，但 MySQL 还有其他索引结构，了解即可：

| 索引结构 | 存储引擎 | 特点 |

|---|---|---|

| B+Tree | InnoDB/MyISAM | 默认，支持等值、范围、排序 |

| HASH | Memory/InnoDB自适应 | 等值查询极快，但不支持范围，InnoDB 自动管理 |

| R-Tree | MyISAM | 空间索引，存地理坐标，用得少 |

| FULLTEXT | InnoDB/MyISAM | 大文本倒排索引 |

日常开发你几乎只和 B+Tree 打交道，记住它为什么快（矮胖树、叶子链表、有序）就够了。

3 创建查看与删除索引

3.1 创建索引

-- 给单列建普通索引（MySQL 8.0）
CREATE INDEX idx_order_no ON orders(order_no);

-- 建表时顺带定义索引
CREATE TABLE user_info (
  id        BIGINT PRIMARY KEY,
  phone     VARCHAR(20),
  name      VARCHAR(50),
  INDEX idx_phone (phone)        -- 表内定义普通索引
);

3.2 查看索引

-- 查看表上的所有索引（MySQL 8.0）
SHOW INDEX FROM orders;

-- 也可以查 information_schema
SELECT index_name, column_name, seq_in_index, non_unique
FROM information_schema.STATISTICS
WHERE table_name = 'orders';

SHOW INDEX 输出里几个关键列：

| 列 | 含义 |

|---|---|

| Key_name | 索引名，PRIMARY 是主键 |

| Column_name | 索引包含的列 |

| Seq_in_index | 该列在索引中的顺序（1 开头） |

| Non_unique | 0 表示唯一索引，1 表示非唯一 |

3.3 删除索引

-- 删除索引（MySQL 8.0）
DROP INDEX idx_order_no ON orders;

-- 建表时定义的索引也可以这样删
ALTER TABLE orders DROP INDEX idx_order_no;

注意：主键索引不能这样删，主键由 PRIMARY KEY 管理。删除大表索引时，MySQL 8.0 支持在线 DDL，不会长时间锁表，但仍建议低峰操作。

3.4 主键的选择也影响性能

InnoDB 表的数据本身就是按主键组织的（聚簇索引），所以主键怎么选很关键：

| 主键选择 | 评价 |

|---|---|

| 自增 BIGINT | 推荐。顺序插入，索引页不会频繁分裂 |

| UUID 字符串 | 不推荐。无序插入导致页分裂、碎片多、占空间 |

| 业务字段做主键 | 谨慎。业务字段可能改、可能长，一旦变更代价大 |

经验：主键用 BIGINT AUTO_INCREMENT，业务唯一约束交给唯一索引，两不耽误。

3.5 给已有大表加索引要注意什么

线上大表加索引不能想当然直接执行，特别是上千万行的表：

-- 在线加索引（MySQL 8.0）（MySQL 8.0 支持 Instant/Online DDL）
ALTER TABLE orders ADD INDEX idx_user_time(user_id, create_time), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE 表示不复制整表、LOCK=NONE 表示不阻塞读写。但即便如此，加索引仍会消耗 IO 和 CPU，建议在业务低峰执行，并先用 EXPLAIN 确认确实需要这个索引，别加了又删、删了又加。

4 唯一索引复合索引全文索引

4.1 唯一索引

要求索引列的值不能重复（NULL 除外）。它既加速查询，又充当数据约束：

-- 唯一索引（MySQL 8.0）
CREATE UNIQUE INDEX uk_phone ON user_info(phone);

往 phone 里插入两个相同号码会直接报错。业务上"手机号唯一、身份证唯一、订单号唯一"都该用唯一索引兜底，光靠应用层判断不够安全（并发下两个请求可能同时通过校验）。

4.2 复合索引

把多个列放进同一个索引，是最能体现功底的设计。最关键的规则是最左前缀匹配：

-- 复合索引 (a, b, c)（MySQL 8.0）
CREATE INDEX idx_abc ON t(a, b, c);

这个索引能加速：

| 查询条件 | 能否用上 idx_abc |

|---|---|

| WHERE a=1 | 能 |

| WHERE a=1 AND b=2 | 能 |

| WHERE a=1 AND b=2 AND c=3 | 能 |

| WHERE b=2（没有 a） | 不能 |

| WHERE a=1 AND c=3 | 只能用到 a 这一段 |

规则一句话：从索引最左列开始，连续命中才能用，中间断了就用不上后面的列。所以建复合索引时，把"最常用在 WHERE 里的列"放最左边。

4.3 全文索引

针对大段文本内容的模糊搜索，普通 LIKE '%关键词%' 用不上索引。MySQL 8.0 的 InnoDB 支持全文索引：

-- 全文索引（MySQL 8.0）
CREATE FULLTEXT INDEX ft_content ON article(content);

-- 用 MATCH AGAINST 查询
SELECT title, content
FROM article
WHERE MATCH(content) AGAINST('数据库 索引' IN NATURAL LANGUAGE MODE);

注意全文索引适合文章、商品描述这类大文本；普通短字段的 LIKE 'abc%'（前缀匹配）用普通 B+Tree 索引即可，只有 LIKE '%abc%' 才需要考虑全文索引（但生产中文检索一般用 Elasticsearch 等专门引擎）。

4.4 索引命名规范

团队协作时索引名要见名知意，推荐约定：

| 类型 | 前缀 | 例子 |

|---|---|---|

| 普通索引 | idx_ | idx_user_time |

| 唯一索引 | uk_ | uk_phone |

| 全文索引 | ft_ | ft_content |

命名里带上表名缩写和列名，半年后回头看 SHOW INDEX 才知道每个索引是干嘛的。

4.5 最左前缀的进阶：范围列之后失效

复合索引 (a, b, c) 里，如果 a 用了范围查询（>、<、BETWEEN），那么 b、c 就用不上了：

| 查询 | 用到的索引列 |

|---|---|

| WHERE a=1 AND b=2 AND c=3 | a, b, c 全用 |

| WHERE a=1 AND b>2 AND c=3 | a, b 用，c 断在范围后面 |

| WHERE a>1 AND b=2 AND c=3 | 只用 a |

所以建复合索引时，把等值查询列放前面、范围列放最后，能让更多列被利用。这也是为什么"按用户查最近订单"的索引是 (user_id, create_time)——user_id 等值在前，create_time 范围/排序在后。

5 覆盖索引的概念

回表是有成本的：二级索引查到主键后，还要再去聚簇索引取一次整行。如果一个查询需要的列，全部都在二级索引里能拿到，就不用回表了——这就是覆盖索引（Covering Index）。

-- 表 orders 有二级索引 idx_user_status(user_id, status)（MySQL 8.0）
SELECT user_id, status FROM orders WHERE user_id = 1001;

这条 SQL 要的 user_id 和 status 都在索引 idx_user_status 里，MySQL 直接从索引树返回结果，不用回表。EXPLAIN 里会显示 Extra: Using index，这就是覆盖索引生效的标志。

| 概念 | 含义 |

|---|---|

| 回表 | 二级索引查到主键后再查聚簇索引取整行 |

| 覆盖索引 | 查询列都在索引里，免回表 |

| Using index | EXPLAIN 中表示覆盖索引生效 |

设计索引时，把高频查询用到的列"塞进"复合索引，既能当过滤条件又能覆盖返回列，是索引优化的常用套路（第 18 篇会专题深入）。

5.1 一个完整的覆盖索引设计例子

表 orders(user_id, status, amount, create_time)，高频查询是：

-- 业务高频查询（MySQL 8.0）
SELECT user_id, amount, create_time
FROM orders
WHERE user_id = 1001 AND status = 2;

如果只建 idx_user(user_id)，查到主键后还要回表取 amount、create_time。改成复合索引：

-- 覆盖索引：过滤列 + 返回列都收进去（MySQL 8.0）
CREATE INDEX idx_user_status ON orders(user_id, status, amount, create_time);

这样 WHERE 的 user_id, status 和 SELECT 的 amount, create_time 全在索引里，直接覆盖。代价是索引变宽、写入更慢——所以只对"高频且慢"的查询才值得这么做，不要给每条 SQL 都设计一个覆盖索引。

5.2 覆盖索引的边界

覆盖索引不是万能的：

SELECT * 几乎不可能覆盖，因为要的列太多；
索引里没存的列（比如大文本、JSON）不在覆盖范围内；
虽然免回表，但如果索引本身要扫描的行数巨大，收益也有限。

判断一个查询是否值得做覆盖索引，标准是：它是否高频、是否真的因为回表变慢。拿不准就先用 EXPLAIN 看 Extra 列，下一篇之后会专门讲 EXPLAIN。

6 索引速查表

| 操作 | 语句 |

|---|---|

| 建普通索引 | CREATE INDEX idx_xx ON 表(列); |

| 建唯一索引 | CREATE UNIQUE INDEX uk_xx ON 表(列); |

| 建复合索引 | CREATE INDEX idx_ab ON 表(a,b); |

| 建全文索引 | CREATE FULLTEXT INDEX ft_xx ON 表(列); |

| 查看索引 | SHOW INDEX FROM 表; |

| 删除索引 | DROP INDEX idx_xx ON 表; |

| 主键 | 自动就是聚簇索引，无需手动 CREATE INDEX |

| 覆盖索引标志 | EXPLAIN 中 Extra: Using index |

7 常见坑与注意事项

1. 索引越多越好：错。每个索引都拖慢写入、占磁盘，一张表索引建议控制在 5 个以内，优先给高频 WHERE/JOIN/ORDER BY 列建。

2. 在索引列上套函数导致失效：WHERE DATE(create_time)='2026-10-07' 会让索引失效，改成范围查询。

3. 违反最左前缀：复合索引 (a,b,c) 却只按 b 查询，索引用不上。建索引前想清楚查询模式。

4. 前导模糊匹配失效：LIKE '%abc' 用不上 B+Tree 索引，LIKE 'abc%' 可以。

5. 选择性低的列单独建索引意义不大：比如"性别"只有两个值，单独建索引过滤性差，优化器可能直接放弃走索引。

6. 忽略覆盖索引：高频查询可以把返回列也纳入复合索引，避免回表，收益往往比想象大。

7. 索引列类型与查询值不匹配：phone 是 VARCHAR，查询却写 WHERE phone=13800000000（数字），隐式类型转换会让索引失效。查询值和列类型保持一致。

8. 对索引列做计算：WHERE age+1 = 20 同样让索引失效，改成 WHERE age = 19。

9. 建完索引从不复查：业务变了、查询模式变了，旧索引可能成了"僵尸索引"，定期用慢查询日志和 sys 库复查哪些索引从没被用到，及时删掉。

10. 盲目给所有列建索引：一张表建七八个索引，写入性能塌方。先从最慢、最频繁的 2~3 个查询入手，用 EXPLAIN 验证收益后再加。

8 练习题

练习 1：为订单表设计索引

表 orders(id, order_no, user_id, status, create_time)，高频查询是"按用户查他最近的订单，按时间倒序"。该建什么索引？

-- 复合索引：user_id 等值 + create_time 排序（MySQL 8.0）
CREATE INDEX idx_user_time ON orders(user_id, create_time);

</details>

练习 2：唯一索引兜底

用户表 user_info(id, phone)，要求手机号不能重复，怎么建？

-- 手机号唯一（MySQL 8.0）
CREATE UNIQUE INDEX uk_phone ON user_info(phone);

</details>

练习 3：判断索引用法

已有索引 idx_abc(a,b,c)，下列哪些查询能用到它？

1. WHERE a=1

2. WHERE b=2

3. WHERE a=1 AND c=3

4. WHERE a=1 AND b=2 AND c=3

1、4 能完整用到；3 只能用到 a；2 用不到（违反最左前缀）。

</details>

练习 4：覆盖索引

接上题练习 1，若查询是 SELECT user_id, create_time FROM orders WHERE user_id=1001，这个索引是否构成覆盖索引？

是。user_id 和 create_time 都在索引 idx_user_time 里，无需回表，EXPLAIN 会显示 Using index。

</details>

练习 5：找出失效写法

下列两条查询都建了 idx_time(create_time)，哪条用不上索引，怎么改？

1. WHERE DATE(create_time) = '2026-10-07'

2. WHERE create_time >= '2026-10-07' AND create_time < '2026-10-08'

第 1 条对索引列套了函数，索引用不上；第 2 条用范围查询能命中索引。把第 1 条改成第 2 条的写法即可。

</details>

练习 6：复合索引列顺序

表 order_item(order_id, product_id, qty)，高频查询是"按订单 ID 查它买了哪些商品"，以及"按商品 ID 统计它出现在哪些订单"。该怎么设计索引？

两条查询分别用不同列做条件，建两个单列索引即可：

-- 两个方向的索引（MySQL 8.0）
CREATE INDEX idx_order ON order_item(order_id);
CREATE INDEX idx_product ON order_item(product_id);

不要建 (order_id, product_id) 复合索引去同时满足——它对"只按 product_id 查"依然违反最左前缀，帮不上忙。

</details>

本章小结

索引的核心就三句话：B+Tree 让查询变快、最左前缀决定复合索引怎么用、覆盖索引省去回表。记住"索引是为查询服务的、建多了反而拖慢写入"这个权衡，设计索引前先看业务里真实的 WHERE/JOIN/ORDER BY 长什么样。

一个简单的检验标准：你建的每个索引，都应该能说清"它是为哪条高频慢查询服务的"；说不清的索引，大概率是多余的。下一篇讲视图与存储过程——把 SQL 逻辑封装起来的两种方式。

下一篇预告：第 12 章视图与存储过程

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 11 章 索引基础与创建

目录

1 为什么需要索引

1.1 哪些字段适合建索引

2 索引的底层原理B+Tree

2.1 聚簇索引与二级索引

2.1.1 一次查询是怎么沿着 B+Tree 走的

2.2 用 sqlite3 实跑看索引效果

2.3 InnoDB 还用到哪些索引结构

3 创建查看与删除索引

3.1 创建索引

3.2 查看索引

3.3 删除索引

3.4 主键的选择也影响性能

3.5 给已有大表加索引要注意什么

4 唯一索引复合索引全文索引

4.1 唯一索引

4.2 复合索引

4.3 全文索引

4.4 索引命名规范

4.5 最左前缀的进阶：范围列之后失效

5 覆盖索引的概念

5.1 一个完整的覆盖索引设计例子

5.2 覆盖索引的边界

6 索引速查表

7 常见坑与注意事项

8 练习题

练习 1：为订单表设计索引

练习 2：唯一索引兜底

练习 3：判断索引用法

练习 4：覆盖索引

练习 5：找出失效写法

练习 6：复合索引列顺序

本章小结

第 11 章索引基础与创建