你还在为选 MySQL 索引头疼？这篇帮你理清所有类型与场景

你是不是也有过这样的经历？在开发业务功能时，写 SQL 语句总觉得 “能跑通就行”，可一到生产环境就傻眼 —— 同样的查询，在测试库秒出结果，到线上却要等好几秒，甚至触发接口超时告警。后来排查半天发现，原来只是没给字段选对索引；还有时候，明明给表加了索引，可执行 EXPLAIN 分析时，却看到 MySQL 根本没走索引，只能对着屏幕疑惑 “我加的索引难道说是假的？”

作为互联网软件开发人员，我们每天都要和 MySQL 打交道，而索引就像数据库的 “导航地图”，选对了能让查询效率飞起来，选错了反而会拖慢性能，甚至引发线上故障。今天就来系统梳理下 MySQL 里常见的索引类型，以及每种类型对应的使用场景，帮你下次选索引时不再 “凭感觉”。

为什么索引对开发这么重大？

在聊具体的索引类型前，我们得先明确一个基础问题：为什么我们必须重点关注索引选择？

对开发来说，写 SQL 是日常工作，但许多人容易忽略 “索引是否合理” 这个关键问题。举个最常见的场景：当业务表的数据量从测试环境的 “几千条” 涨到生产环境的 “几百万条” 时，没加索引的查询会从 “毫秒级” 变成 “秒级” 甚至 “分钟级”。列如一张 user 表，没给 user_id 加主键索引时，查询SELECT * FROM user WHERE user_id = 123会触发全表扫描，数据量越大越慢；而加了主键索引后，MySQL 能直接定位到目标数据，耗时瞬间缩短。

从原理上来说，MySQL 的索引本质是 “协助数据库高效获取数据的数据结构”，就像图书的目录 —— 我们想找某篇文章，不用逐页翻书，查目录就能快速定位页码。如果没有索引，MySQL 查询数据时只能 “逐行扫描”（全表扫描），效率极低；有了索引，就能通过索引结构快速筛选数据，大幅提升查询性能。

但这里要注意：索引不是 “越多越好”。每增加一个索引，MySQL 在执行 INSERT、UPDATE、DELETE 操作时，都要额外维护索引结构（列如更新索引树），会消耗更多的时间和资源。所以 “选对索引类型” 和 “控制索引数量” 同样重大，而搞懂每种索引的适用场景，正是做好这两点的前提。

MySQL 常见索引类型：从基础到特殊，一篇全覆盖

在 MySQL 中，索引的分类方式有许多，列如按 “数据结构” 分、按 “功能” 分、按 “物理存储” 分等。作为开发，我们不需要深究底层数据结构的实现细节，但必须清楚 “每种索引能解决什么问题”“在什么场景下该用”。下面就从开发常用的角度，梳理 6 种核心索引类型：

1. 主键索引（Primary Key Index）：表的 “唯一标识”，必加且唯一

核心特点：主键索引是表的 “主键” 对应的索引，默认是唯一且非空的，一张表只能有一个主键索引。在 InnoDB 引擎中，主键索引的叶子节点会存储整行数据（这种索引结构叫 “聚簇索引”），这也是主键查询效率最高的缘由。

适用场景：

必须用于 “唯一标识表中每行数据” 的字段，列如 user 表的 user_id、order 表的 order_id、商品表的 goods_id 等；
所有需要通过 “唯一 ID 查询单条数据” 的场景，列如根据 order_id 查询订单详情、根据 user_id 查询用户信息等；
作为外键关联的基础，列如 order 表的 user_id 关联 user 表的 user_id（主键），主键索引能提升关联查询的效率。

开发注意点：

主键字段提议用 “自增整数型”（列如 INT UNSIGNED AUTO_INCREMENT），不提议用字符串（列如 UUID）。由于自增整数能保证索引树的顺序增长，减少索引分裂的开销；而 UUID 是随机字符串，会导致索引树频繁分裂，影响性能；
不要把 “业务字段” 作为主键，列如用手机号当用户表主键 —— 如果后续业务需要修改手机号，会同时修改主键和关联表的外键，操作复杂且有风险。

2. 唯一索引（Unique Index）：保证字段 “不重复”，非主键也能用

核心特点：唯一索引的作用是 “保证索引字段的值唯一”，但允许字段为 NULL（注意：多个 NULL 值不违反唯一性约束）。和主键索引不同，一张表可以有多个唯一索引，且唯一索引的叶子节点（InnoDB 中）存储的是 “主键值”，不是整行数据（非聚簇索引）。

适用场景：

用于需要 “唯一性约束” 但又不能作为主键的字段，列如用户表的 phone（手机号）、email（邮箱）—— 这些字段需要唯一，但不能当主键（由于可能需要修改）；
用于 “唯一标识某类数据” 但允许 NULL 的场景，列如商品表的 sku_code（商品 SKU 码，唯一）、员工表的 employee_no（员工编号，唯一）；
避免重复插入数据，列如用户注册时，通过 phone 字段的唯一索引，能快速判断手机号是否已注册，比用SELECT COUNT(*) FROM user WHERE phone = '138xxxx8888'更高效。

开发注意点：

唯一索引和主键索引的 “唯一性约束” 是在数据库层面生效的，所以即使业务代码做了判重，数据库层面也提议加唯一索引，防止因代码漏洞导致重复数据；
如果字段允许为 NULL，要注意 “多个 NULL 值不违反唯一性”—— 列如给 email 加唯一索引后，插入多条 email 为 NULL 的记录是允许的，这一点和主键（非空）不同。

3. 普通索引（Normal Index）：最通用的 “查询加速器”，按需添加

核心特点：普通索引是最基础、最通用的索引，没有 “唯一性” 和 “非空” 的约束，叶子节点（InnoDB 中）存储的是主键值。它的作用就是 “加速查询”，只要某个字段常常出目前 WHERE 条件中，就可以思考加普通索引。

适用场景：

所有 “非唯一、非主键，但常常用于查询筛选” 的字段，列如订单表的 order_status（订单状态，常常查询 “待支付”“已完成” 的订单）、用户表的 user_level（用户等级，常常查询 “VIP 用户”）；
用于 “模糊查询” 的前缀匹配场景（列如LIKE '张%'），但要注意：模糊查询如果以 “%” 开头（列如LIKE '%张'），普通索引会失效，无法加速查询；
用于 “范围查询” 的场景，列如SELECT * FROM order WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31'，给 create_time 加普通索引后，能快速筛选出时间范围内的订单。

开发注意点：

普通索引不要 “滥用”—— 如果一个字段很少出目前 WHERE 条件中，加索引只会增加写操作的开销，反而影响性能；
对于 “字段值重复率很高” 的字段（列如性别字段，只有男 / 女 / 未知三个值），不提议加普通索引。由于这类字段的 “筛选效率低”，即使加了索引，MySQL 可能还是会选择全表扫描（列如查询 “性别 = 男” 的用户，占比 50%，全表扫描比走索引更快）。

4. 复合索引（Composite Index）：多字段查询的 “优化利器”

核心特点：复合索引是 “多个字段组合而成的索引”，列如给 order 表的 “user_id + create_time” 加复合索引。它的核心遵循 “最左前缀原则”—— 查询时只有用到 “索引的最左边第一个字段”，索引才会生效（列如用 “user_id” 查询能走索引，用 “create_time” 单独查询不能走索引）。

适用场景：

频繁用 “多个字段组合查询” 的场景，列如订单管理系统中，常常查询 “某用户在某个时间段内的订单”（SELECT * FROM order WHERE user_id = 123 AND create_time BETWEEN '2024-01-01' AND '2024-01-31'），这时 “user_id + create_time” 的复合索引比单独给两个字段加普通索引更高效；
用于 “排序 + 筛选” 的场景，列如查询 “某用户的订单，按创建时间倒序排列”（SELECT * FROM order WHERE user_id = 123 ORDER BY create_time DESC），复合索引 “user_id + create_time” 能同时满足 “筛选” 和 “排序”，避免 MySQL 额外做排序操作（Using filesort）；
用于 “覆盖索引” 场景（即查询的字段都在索引中，不需要回表查数据），列如查询 “某用户的订单 ID 和创建时间”（SELECT order_id, create_time FROM order WHERE user_id = 123），如果复合索引是 “user_id + create_time + order_id”，MySQL 能直接从索引中获取数据，不用回表，效率更高。

开发注意点：

复合索引的 “字段顺序很关键”—— 要把 “筛选频率高、区分度高” 的字段放在前面。列如 “user_id（筛选频率高） + create_time（筛选频率低）” 比 “create_time + user_id” 更合理；
避免创建 “过长的复合索引”—— 列如组合 3 个以上字段的索引，会增加索引的存储开销和维护成本，一般提议复合索引的字段数不超过 3 个；
不要和单个字段索引重复 —— 列如已经有 “user_id + create_time” 的复合索引，就不需要再单独给 user_id 加普通索引了，由于复合索引的最左前缀（user_id）已经能覆盖单个 user_id 的查询。

5. 前缀索引（Prefix Index）：字符串字段的 “空间优化方案”

核心特点：前缀索引是 “只对字符串字段的前 N 个字符建立索引”，列如给 user 表的 username 字段（VARCHAR (50)）建立 “前 10 个字符” 的前缀索引。它的作用是 “减少索引的存储空间”—— 由于字符串越长，索引占用的空间越大，而前缀索引只存储前 N 个字符，能大幅降低空间开销。

适用场景：

用于 “字符串字段较长” 且 “查询时只需要前缀匹配” 的场景，列如用户表的 username（长度可能达 50 字符）、文章表的 title（长度可能达 200 字符）；
用于 “字符串字段区分度主要在前 N 个字符” 的场景，列如手机号（前 7 位是运营商 + 地区，区分度足够）、邮箱（前半部分是用户名，区分度高）；
避免 “全字段索引” 的高空间开销，列如给 VARCHAR (200) 的 title 字段加全字段索引，每个索引项占用 200 字节；而加前 20 个字符的前缀索引，每个索引项只占 20 字节，空间节省 90%。

开发注意点：

前缀长度 N 的选择很关键 —— 要保证 “前 N 个字符的区分度足够高”，同时 “N 尽可能小”。可以通过SELECT COUNT(DISTINCT LEFT(username, N)) / COUNT(*) FROM user计算区分度，当区分度接近 1 时（列如 0.95 以上），N 就是合适的；
前缀索引不支持 “后缀匹配” 和 “全模糊匹配”，列如给 username 加前 10 个字符的前缀索引后，SELECT * FROM user WHERE username LIKE '%张三'（后缀匹配）和SELECT * FROM user WHERE username LIKE '%张三%'（全模糊匹配）都无法走索引；
前缀索引不支持 “覆盖索引”—— 由于索引中只存储前 N 个字符，无法直接获取完整的字符串字段值，查询时如果需要完整字段，还是要回表。

6. 全文索引（Full-Text Index）：文本内容 “关键词搜索” 的专用工具

核心特点：全文索引是专门用于 “文本字段关键词搜索” 的索引，列如在文章表的 content 字段、商品表的 description 字段上建立全文索引。它和普通索引的 “前缀匹配” 不同，能支持 “关键词分词搜索”，列如搜索 “MySQL 索引”，能匹配到包含 “MySQL” 和 “索引” 的文本内容，效率比LIKE '%MySQL索引%'高得多。

适用场景：

用于 “长文本字段的关键词搜索” 场景，列如博客系统的文章内容搜索（根据关键词找文章）、电商系统的商品描述搜索（根据关键词找商品）；
替代 “低效的模糊查询”，列如SELECT * FROM article WHERE content LIKE '%MySQL索引%'会触发全表扫描，而用全文索引的SELECT * FROM article WHERE MATCH(content) AGAINST('MySQL 索引')能快速定位结果；
需要 “分词搜索” 的场景，MySQL 的全文索引会自动对文本进行分词（列如中文需要配合 ngram 插件，英文默认支持），支持 “布尔模式”“自然语言模式” 等多种搜索方式。

开发注意点：

全文索引只支持 CHAR、VARCHAR、TEXT 类型的字段，不支持其他类型；
MySQL 的默认全文索引对中文支持有限，需要手动安装 ngram 插件（INSTALL PLUGIN ngram SONAME 'ngram.so'），并在创建索引时指定分词长度（列如FULLTEXT INDEX idx_content (content) WITH PARSER ngram）；
全文索引不适合 “短文本” 或 “关键词区分度低” 的场景，列如给 “备注” 字段（一般只有几十个字符）加全文索引，效果不如普通索引；另外，搜索 “高频词”（列如 “的”“是” 这类虚词）时，全文索引会忽略这些词（停止词），可能导致搜索结果不符合预期。