企业官网建设流程全解析

MySQL加索引这件事，写过SQL的人基本都绕不开。你打开命令行，第一反应是用CREATE INDEX，还是ALTER TABLE ADD INDEX？有人在建表时顺手把索引写在字段定义里，有人因为加了唯一约束、主键约束，被数据库隐式建了索引却不自知。今天我把MySQL添加索引的5种主流方式完整拆开讲清楚，每条语法、适用场景、以及真正踩过的坑都会摆出来。文章面向开发、运维，也适合刚接触索引优化、被慢查询折磨的新手，读完之后你至少能回答两个问题：现在这个场景该用哪种方式，以及为什么不用另一种。

1. 为什么写这篇：五种添加索引方式的设计思路

1.1 索引的本质，一句话讲透

索引就是数据库维护的“目录树”。MySQL底层最常见的B+Tree，把索引列的值按序排列，每个叶子节点指向一行数据。没有索引时，查询走全表扫描，相当于一本几百页的书从第一页翻到最后一页；有索引时，直接按照目录跳到对应章节，速度自然不一样。

但索引不是白拿的。每次插入、更新、删除，除了改数据行，还要同步维护这棵“目录树”，所以索引建多了，写性能会下降，存储空间也会膨胀。这就是为什么加索引要考虑“值不值”，而不是有多少列就建多少索引。

1.2 五种方式速览与选型对比

我按自己日常的使用频率，把MySQL添加索引的方式归结为五种：

实际生产中，前四种是“直接加索引”，第五种更多是“被动生成索引”，很多人在表结构上看不到索引语句，却看到索引列表里多出东西，原因就在这里。

2. 方式一：使用CREATE INDEX命令

2.1 语法与参数逐个说明

CREATE INDEX是MySQL专门用来创建索引的独立语句，基本语法：

CREATE INDEX index_name ON table_name (column_name);

支持加排序和前缀长度：

CREATE INDEX idx_name ON table_name (column_name(20) DESC);

几个关键参数：

• index_name：索引名字，建议一眼能看出用途，比如idx_user_email、idx_order_create_time。
• table_name：要加索引的表。
• column_name：需要索引的列，可以写多个列组成联合索引。
• (20)：只对列的前20个字符建索引，适合字符串列很长的情况，能明显减小索引体积，但查询条件也得是前缀匹配才能生效。
• DESC：MySQL 8.0起支持降序索引，以往的版本语法上允许，实际还是升序存储。

2.2 实操示例

假设有张用户表，经常按邮箱查用户：

CREATE TABLE user_profile ( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(100), nickname VARCHAR(50) ); CREATE INDEX idx_user_email ON user_profile (email);

执行后可以用下面命令确认索引是否生效：

SHOW INDEX FROM user_profile;

结果里会多出一行，Key_name是idx_user_email，Column_name是email。

2.3 一个容易被忽略的限制

CREATE INDEX语句没办法创建主键索引。主键索引本质是一个非空的唯一索引，表现形式是PRIMARY，语法上MySQL不允许用CREATE PRIMARY INDEX这种写法。如果你要给一张现有表加主键，必须用ALTER TABLE语句修改表结构，或者重建表。

另外，CREATE INDEX也不能直接修改已有索引的列顺序。想调整联合索引的字段顺序，正确做法是先DROP INDEX，再重新CREATE INDEX。

注意：建索引前务必检查表里是否已经有重复或职责重叠的索引。我见过一个订单表，有idx_order_no和idx_order_no_status两个索引，前者其实可以被后者完全覆盖，多建一个白白消耗写性能。

3. 方式二：使用ALTER TABLE ADD INDEX

3.1 为什么说这是日常用得最多的方式

ALTER TABLE的本质是修改表结构，ADD INDEX只是它众多子句中的一种。它和CREATE INDEX都能加普通索引，但ALTER TABLE更灵活，可以在一条语句里同时加索引、删索引、改字段，还能用来加主键索引。

基本语法：

ALTER TABLE table_name ADD INDEX index_name (column_name);

加主键索引：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

加唯一索引（后面细讲）：

ALTER TABLE table_name ADD UNIQUE KEY index_name (column_name);

3.2 实操示例

回到用户表的例子，如果表已经存在，邮箱那列没建索引：

ALTER TABLE user_profile ADD INDEX idx_user_email (email);

执行后，SHOW INDEX能看到新的索引。日常线上变更时我更多用这种方式，因为可以放在一个表结构变更脚本里，和其他字段改动一起提交，方便走审批流。

3.3 与CREATE INDEX的差异对比

在网上经常看到有人问“CREATE INDEX和ALTER TABLE ADD INDEX到底有什么区别”，实际执行计划层面没有差别，它们生成的索引结构完全一样。我总结出三个真正的差异：

如果你的目标就是“给这一列加个索引”，两条命令选哪个都行。但如果后续还要调整字段类型、加唯一约束、加主键，用ALTER TABLE更顺手。

提示：执行ALTER TABLE ADD INDEX时，MySQL会对表加元数据锁，锁的持续时间取决于表大小和索引构建方式。MySQL 8.0默认使用在线DDL，可以在一定程度上允许并发DML，但并非完全无锁，大表变更还是建议低峰期操作，下面第7部分会展开讲。

4. 方式三：在CREATE TABLE建表语句中直接定义索引

4.1 内联索引定义的语法

MySQL允许在CREATE TABLE语句里，字段定义结束后，用专门子句直接定义索引。基本格式：

CREATE TABLE table_name ( col1 INT, col2 VARCHAR(50), INDEX index_name (col1, col2) );

也可以用KEY关键字，KEY是INDEX的别名，两者完全等价。除了普通索引，建表时还能直接声明主键和唯一约束：

CREATE TABLE table_name ( col1 INT NOT NULL, col2 VARCHAR(50), col3 VARCHAR(100), PRIMARY KEY (col1), UNIQUE KEY uk_col2 (col2), INDEX idx_col3 (col3) );

4.2 实操示例

这里给一个稍完整的表结构设计场景：

CREATE TABLE user_order ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_order_status (user_id, status), KEY idx_create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这个建表语句里一次性规划了三类索引：主键id、唯一索引uk_order_no、联合索引idx_user_order_status，外加单列索引idx_create_time。字段类型、字符集这些细节先不谈，单看索引结构，已经把查询路径提前梳理好了。

4.3 为什么新表一定要在建表时规划好索引

我见过太多表上线时一个索引都没有，等到慢查询报警，才急匆匆补索引。补索引本身不难，难的是业务数据量已经上来，加索引需要更长的时间和更大的资源开销，而且大表ALTER操作稍有不慎就会拖慢线上请求。

建表阶段规划索引的好处有三点：

• 一次成型，避免后续反复修改表结构。
• 可以站在查询角度设计联合索引，避免“每个查询单独建一个索引”这种浪费。
• 新表没有数据，建索引几乎没有成本。

但也要提醒一句：哪怕建表时规划好了，也不意味着以后不用调整。业务查询模式会变，索引设计本来就是一个持续修正的过程。新表建索引相当于“把地基打好”，不等于“一劳永逸”。

注意：CREATE TABLE里定义索引时，索引列顺序很关键。多列索引(a,b)和(b,a)并不是同一个索引，能服务的最左前缀查询也不一样。设计联合索引时，应该把等值查询频率最高的列放前面，范围查询列放后面，这是很多索引优化文章反复强调的一点，但真正落到建表语句里时，还是经常有人写反。

5. 方式四：使用CREATE UNIQUE INDEX创建唯一索引

5.1 唯一索引是什么

唯一索引就是在普通索引的基础上加了“列值不能重复”的约束。插入或更新时，如果新值与已有值冲突，MySQL会直接报错Duplicate entry。

唯一索引同时承担了查提速和约束数据两种职责。业务上凡是要求“唯一”的字段，基本都适合加唯一索引，比如：用户邮箱、订单号、身份证号、支付流水号。

语法：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

也可以在ALTER TABLE里加：

ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);

5.2 实操示例

拿订单表举例：

CREATE UNIQUE INDEX uk_order_no ON user_order (order_no);

如果表中已经有重复的order_no，这条语句会执行失败。这个“失败”其实是保护机制，而不是麻烦。正因为有这层约束，后续任何时候新插入重复订单号，数据库都会拦截，业务层再也不需要额外写“先查一下是否存在”的代码。

5.3 唯一索引、普通索引、主键索引三者对比

很多人把这三者混在一起，其实它们边界清晰：

唯一的区别点在于NULL值处理。唯一索引允许列有多个NULL，因为MySQL认为“NULL != NULL”，所以NULL之间不冲突。如果业务要求该列连NULL都不能出现，还得配合NOT NULL约束一起使用。

提示：给已有大表加唯一索引前，务必先查重。我遇到过同事在生产环境执行ALTER TABLE ADD UNIQUE，执行到一半才发现几十万条历史重复数据，语句直接报错回滚。虽然数据没坏，但变更窗口被白白占用，生产环境差点出问题。正确姿势是先跑SELECT problematic_column, COUNT(*) FROM table GROUP BY problematic_column HAVING COUNT(*) > 1;，确认数据干净后再变更。

6. 方式五：通过约束隐式创建索引

6.1 隐式创建的三个来源

第五种方式不需要你写任何专门的CREATE INDEX语句，MySQL会在某些约束出现时自动创建索引。

• 主键约束（PRIMARY KEY）：InnoDB表每行数据本身按主键聚簇存放，主键字段一定会生成聚簇索引。
• 唯一约束（UNIQUE KEY）：为了保证唯一性，数据库必须为它建唯一索引。
• 外键约束（FOREIGN KEY）：外键列如果没有索引，InnoDB会自动创建普通索引，否则会影响关联查询和删除时的约束检查性能。

这条来自日常建表时很容易踩的坑：有人写了外键但没写索引，看表结构时发现外键列自动多了一个索引，还以为是“灵异事件”。其实不是，是InnoDB的外键机制强制要求关联列不能无索引，这是底层设计决定的。

6.2 实操示例

看这个表结构：

CREATE TABLE order_item ( id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, product_name VARCHAR(100), PRIMARY KEY (id), UNIQUE KEY uk_order_item (order_id, product_name) ) ENGINE=InnoDB;

执行完SHOW INDEX FROM order_item，你会看到两行：一行是主键PRIMARY，另一行是唯一索引uk_order_item。这两者都不是用CREATE INDEX加出来的，而是由约束隐式生成。

如果再加一个外键：

CREATE TABLE order_payment ( id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, amount DECIMAL(10,2), PRIMARY KEY (id), CONSTRAINT fk_order_pay FOREIGN KEY (order_id) REFERENCES order_item(id) ) ENGINE=InnoDB;

order_id虽然没有显式建索引，但因为有外键，SHOW INDEX里也会出现一个名为fk_order_pay的索引，这就是隐式创建的。

6.3 进阶：一条ALTER TABLE同时加多个索引

严格来说，这不算第五种独立的“方式”，而是ALTER TABLE ADD INDEX的扩展用法，我习惯把它和隐式创建一起归纳到“进阶操作”里。

一条语语句可以同时加多个索引，省去反复执行多条变更语句的时间：

ALTER TABLE user_order ADD INDEX idx_user_id (user_id), ADD INDEX idx_create_time (create_time), ADD UNIQUE INDEX uk_pay_no (pay_no);

MySQL 8.0里这种写法会按顺序逐个创建索引，执行完成后一次生效。相比写三条ALTER TABLE，这种方式更利于线上变更脚本的幂等控制，也好回滚（整个alter块失败时不会留下部分索引）。

不过要留意：多个索引在一个ALTER语句里，执行时间约等于逐个创建累加，DML锁的持有时间也会相应拉长。如果其中一个索引创建耗时过长，整个变更的窗口会非常久，所以大表上不建议一次塞太多索引。

7. 常见问题与排查技巧实录

7.1 重复索引排查

很多库里常见这种组合：

ALTER TABLE t ADD INDEX idx_a (a); ALTER TABLE t ADD INDEX idx_a_b (a, b);

idx_a其实就是一个冗余索引，因为idx_a_b的最左前缀已经覆盖了单列a的查询场景。排查时可以跑下面这条SQL：

SELECT s1.TABLE_NAME, s1.INDEX_NAME, s1.COLUMN_NAME, s2.INDEX_NAME AS duplicate_index FROM information_schema.STATISTICS s1 JOIN information_schema.STATISTICS s2 ON s1.TABLE_NAME = s2.TABLE_NAME AND s1.COLUMN_NAME = s2.COLUMN_NAME AND s1.INDEX_NAME <> s2.INDEX_NAME AND s1.SEQ_IN_INDEX = 1 AND s2.SEQ_IN_INDEX = 1 ORDER BY s1.TABLE_NAME, s1.INDEX_NAME;

查出疑似重复索引后，再用SHOW INDEX确认一下实际列顺序，判断哪个索引可以被删。

7.2 加索引后查询还是慢的典型场景

最常见的三个原因：

• 使用函数或计算表达式包裹索引列，比如WHERE DATE(create_time) = '2025-01-01'，索引会失效，因为B+Tree存的是原始列值，不是函数结果。
• 模糊查询以通配符开头，比如WHERE nickname LIKE '%明月%'，无法利用索引前缀匹配。
• 列的顺序和索引定义不一致，比如联合索引(user_id, status)，但查询条件只写WHERE status = 1，这条查询最多只能部分使用索引，甚至直接全表扫描。

遇到慢查询，先用EXPLAIN看执行计划：

EXPLAIN SELECT * FROM user_order WHERE status = 1;

看type字段，如果出现ALL说明是全表扫描；再看key字段，如果为NULL说明当前查询没有可用索引。先定位原因，再决定要不要加索引，这个顺序千万别错。

7.3 线上大表加索引卡住怎么办

线上表数据量超过千万级时，直接执行ALTER TABLE ADD INDEX，默认行为可能造成长时间锁表或大量IO占用。

MySQL 8.0对在线DDL的支持比较成熟，多数加索引操作可以做到允许并发读写，但前提是使用默认的ALGORITHM=INPLACE策略。底层逻辑是：InnoDB先开辟一块新增的空间构建索引结构，同时记录增量变更，最后再切换。整个过程锁粒度很小，但构建索引期间会产生额外磁盘空间和IO压力。

实操建议有三条：

• 低峰期执行，不要选业务峰值时段。
• 预先评估表大小和磁盘剩余空间，必要时留出表体积1.5倍以上的空间。
• 可以先加索引后再删除，不要让变更长期占用锁资源。

如果实在担心，可以用pt-osc这类在线变更工具来做，通过临时表同步数据方式完成索引增加，对线上业务干扰更小。选择前先在自己环境做压测，各方案都有取舍。

7.4 几个实用排查SQL组合

检查一张表所有索引：

SHOW INDEX FROM user_order;

查看某条查询的真实执行计划：

EXPLAIN SELECT order_no, status FROM user_order WHERE user_id = 123 AND create_time > '2025-01-01';

查看一条查询是否用到某个索引：

EXPLAIN FORMAT=TREE SELECT * FROM user_order WHERE order_no = 'XYZ123';

MySQL 8.0的EXPLAIN FORMAT=TREE输出更直观，能看出索引扫描范围、回表次数等细节。做索引优化时，我基本都靠这三个命令定位问题。

注意：加索引本身不会让所有查询变快。查询如果返回超过表中20%左右的数据行，优化器可能认为全表扫描更划算，直接放弃索引。所以不要用“这列经常出现在WHERE里”作为唯一建索引标准，还要看这列的区分度。性别这种区分度极低的列，建索引往往白搭。

8. 最后说点我的个人体会

加索引这件事，表面上是“选择一个SQL语句”，实际上考验的是你对表结构、查询模式、数据分布的理解。我见过太多人一遇到慢查询就加索引，加了没效果就开始怀疑MySQL，最后查出来是SQL写法有问题，索引本来就没有失效的必要条件。做索引优化要养成的习惯很简单：加索引前先跑EXPLAIN，加完后回头看执行计划有没有变化，过一段时间再看看这台数据库的写性能有没有被拖累。

再分享一个小技巧：表结构变更清单里的索引命名，尽量统一风格，普通索引用idx_开头，唯一索引用uk_开头，主键直接用PRIMARY。时间久了之后，只看索引名就能推断索引的作用，排查问题和交接的时候会省很多事。希望大家下次加索引时不再纠结用哪条语句，而是能想清楚“为什么是这条语句”。