MySQL 创建索引完整实操
这份笔记主要整理 MySQL 里最常用的 4 类索引：普通索引、唯一索引、主键索引、联合索引。
每种索引都分成两种写法：
- 建表时直接创建索引
- 表已经存在，后面再新增索引
说明：下面 SQL 默认使用 MySQL 8.x，字符集统一使用
utf8mb4，避免中文乱码。
0. 准备测试表
如果只是练习新增索引，可以先创建一张没有索引的测试表。
-- 如果 user 表已经存在，先删除，方便重复执行练习 SQLDROPTABLEIFEXISTS`user`;-- 创建用户测试表-- 这里先不加任何索引，后面用于演示“已有表新增索引”的写法CREATETABLE`user`(-- 用户 ID，普通整数字段idINT,-- 用户名，最多 50 个字符usernameVARCHAR(50),-- 手机号，国内手机号一般 11 位phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱，最多 100 个字符emailVARCHAR(100))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;1. 普通索引 INDEX
普通索引是最常见的索引，只用来加速查询，不限制字段值是否重复。
适合场景：经常用某个字段做查询条件，比如经常根据手机号查询用户。
1.1 建表时创建普通索引
-- 如果 user 表已经存在，先删除，避免建表失败DROPTABLEIFEXISTS`user`;-- 建表时直接给 phone 字段创建普通索引CREATETABLE`user`(-- 用户 IDidINT,-- 用户名usernameVARCHAR(50),-- 手机号phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100),-- 给 phone 字段建立普通索引，索引名叫 idx_phoneINDEXidx_phone(phone))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;1.2 已有表新增普通索引
语法：
CREATEINDEX索引名ON表名(字段名);示例：
-- 给 phone 字段建立普通索引-- idx_phone 是索引名，可以自己起名，一般建议用 idx_字段名CREATEINDEXidx_phoneON`user`(phone);查询示例：
-- 因为 phone 字段有索引，所以这种按手机号精确查询的 SQL 可以利用索引加速SELECT*FROM`user`WHEREphone='13900001111';2. 唯一索引 UNIQUE
唯一索引既可以加速查询，也可以限制字段值不能重复。
适合场景：手机号、邮箱、身份证号、业务唯一编号等。
注意：MySQL 的唯一索引允许多个
NULL值，因为 MySQL 认为NULL表示未知，多个未知值不算重复。
2.1 建表时创建唯一索引
-- 如果 user 表已经存在，先删除，方便重复执行DROPTABLEIFEXISTS`user`;-- 建表时给 phone 字段创建唯一索引CREATETABLE`user`(-- 用户 IDidINT,-- 用户名usernameVARCHAR(50),-- 手机号，后面会给这个字段加唯一索引phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100),-- 给 phone 字段建立唯一索引，索引名叫 uk_phone-- 加了这个索引后，phone 不能出现重复的非 NULL 值UNIQUEuk_phone(phone))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;2.2 已有表新增唯一索引
-- 给 phone 字段建立唯一索引-- 注意：如果表里已经有重复手机号，这条 SQL 会执行失败CREATEUNIQUEINDEXuk_phoneON`user`(phone);唯一索引效果示例：
-- 插入第一条手机号为 13900001111 的数据，可以成功INSERTINTO`user`(id,username,phone,age,email)VALUES(1,'张三','13900001111',20,'zhangsan@example.com');-- 再插入一条相同手机号的数据，会因为 uk_phone 唯一索引报错INSERTINTO`user`(id,username,phone,age,email)VALUES(2,'李四','13900001111',22,'lisi@example.com');3. 主键索引 PRIMARY KEY
主键索引是一种特殊的唯一索引，要求字段值必须非空并且唯一。
在 InnoDB 里，主键索引还是聚集索引，也就是说表数据会按照主键组织存储。
注意点：
- 每张表只能有一个主键
- 主键字段不能为
NULL - 主键值不能重复
- MySQL 里主键约束名固定显示为
PRIMARY
3.1 建表时指定主键
-- 如果 user 表已经存在，先删除，方便重复执行DROPTABLEIFEXISTS`user`;-- 建表时指定 id 为主键CREATETABLE`user`(-- 用户 ID，设置为 NOT NULL，后面作为主键idINTNOTNULL,-- 用户名usernameVARCHAR(50),-- 手机号phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100),-- 指定 id 字段为主键-- 主键索引名在 MySQL 中固定显示为 PRIMARYPRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;3.2 已有表添加主键
-- 给已有 user 表的 id 字段添加主键-- 注意：执行前必须保证 id 字段没有 NULL，并且没有重复值ALTERTABLE`user`ADDPRIMARYKEY(id);如果已有表的id字段可能有空值，可以先改成非空：
-- 把 id 字段改成 NOT NULL-- 只有当表里 id 没有 NULL 时，这条 SQL 才能执行成功ALTERTABLE`user`MODIFYidINTNOTNULL;-- 再给 id 字段添加主键ALTERTABLE`user`ADDPRIMARYKEY(id);4. 联合索引
联合索引也叫复合索引，就是把多个字段一起建成一个索引。
适合场景：经常按多个字段组合查询，比如经常根据username + age查询用户。
联合索引要重点记住最左匹配原则：如果索引是(username, age)，查询条件最好从username开始使用。
4.1 建表时创建联合索引
-- 如果 user 表已经存在，先删除，方便重复执行DROPTABLEIFEXISTS`user`;-- 建表时创建 username 和 age 的联合索引CREATETABLE`user`(-- 用户 IDidINT,-- 用户名usernameVARCHAR(50),-- 手机号phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100),-- 给 username 和 age 建立联合索引-- 索引顺序是 username 在前，age 在后INDEXidx_username_age(username,age))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;4.2 已有表新增联合索引
-- 给 username 和 age 字段建立联合索引-- 字段顺序很重要，这里是 username 在前，age 在后CREATEINDEXidx_username_ageON`user`(username,age);4.3 最左匹配原则示例
假设已经有联合索引：
-- 创建 username + age 联合索引CREATEINDEXidx_username_ageON`user`(username,age);可以利用索引的查询：
-- 可以走索引，因为用到了最左边的 username 字段SELECT*FROM`user`WHEREusername='张三';-- 可以走索引，因为 username 和 age 都用到了，而且没有跳过最左字段SELECT*FROM`user`WHEREusername='张三'ANDage=20;不适合这个联合索引的查询：
-- 一般不能完整利用 idx_username_age 联合索引-- 因为查询条件跳过了最左边的 username 字段，只用了 ageSELECT*FROM`user`WHEREage=20;5. 删除索引
普通索引、唯一索引、联合索引都可以用DROP INDEX删除。
语法：
DROPINDEX索引名ON表名;示例：
-- 删除普通索引 idx_phoneDROPINDEXidx_phoneON`user`;-- 删除唯一索引 uk_phoneDROPINDEXuk_phoneON`user`;-- 删除联合索引 idx_username_ageDROPINDEXidx_username_ageON`user`;主键索引的删除写法比较特殊：
-- 删除主键索引-- 注意：如果有其他表的外键依赖这个主键，可能会删除失败ALTERTABLE`user`DROPPRIMARYKEY;6. 查看表中所有索引
创建索引之后，可以用下面的 SQL 验证是否创建成功。
-- 查看 user 表里的所有索引SHOWINDEXFROM`user`;-- SHOW KEYS 和 SHOW INDEX 基本等价SHOWKEYSFROM`user`;常看字段说明：
| 字段名 | 含义 |
|---|---|
Key_name | 索引名，比如PRIMARY、idx_phone、uk_phone |
Column_name | 索引包含的字段名 |
Non_unique | 是否允许重复，0表示唯一，1表示可以重复 |
Seq_in_index | 字段在联合索引里的顺序 |
Index_type | 索引类型，InnoDB 常见是BTREE |
7. ALTER TABLE 统一新增索引写法
除了CREATE INDEX，也可以统一用ALTER TABLE给已有表添加索引。
-- 给 age 字段添加普通索引ALTERTABLE`user`ADDINDEXidx_age(age);-- 给 email 字段添加唯一索引-- 注意：如果 email 已经有重复的非 NULL 值，这条 SQL 会执行失败ALTERTABLE`user`ADDUNIQUEuk_email(email);-- 给 phone 和 age 添加联合索引-- phone 是联合索引的最左字段，查询时最好优先用 phone 作为条件ALTERTABLE`user`ADDINDEXidx_phone_age(phone,age);8. 常用命名规范
工作中建议索引名写清楚，后期排查问题会省很多事。
| 索引类型 | 推荐前缀 | 示例 |
|---|---|---|
| 普通索引 | idx_ | idx_phone |
| 唯一索引 | uk_ | uk_phone |
| 联合索引 | idx_字段1_字段2 | idx_username_age |
| 主键索引 | 固定为PRIMARY | PRIMARY |
建议：
- 索引名尽量能看出对应字段，比如
idx_phone、idx_username_age - 不要给区分度太低的字段乱建索引，比如性别
gender - 经常出现在
WHERE、JOIN ON、ORDER BY、GROUP BY里的字段更适合考虑建索引 - 索引不是越多越好，索引会占空间，也会影响新增、修改、删除数据的速度
9. 一份可直接复制练习的完整 SQL
下面这段 SQL 可以直接复制到 MySQL 里执行，用来完整练习“建表、插入数据、创建索引、查看索引、删除索引”。
-- 删除旧表，保证这份练习 SQL 可以重复执行DROPTABLEIFEXISTS`user`;-- 创建用户测试表CREATETABLE`user`(-- 用户 ID，后面会添加主键idINTNOTNULL,-- 用户名usernameVARCHAR(50),-- 手机号phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 插入测试数据INSERTINTO`user`(id,username,phone,age,email)VALUES(1,'张三','13900001111',20,'zhangsan@example.com'),(2,'李四','13900002222',22,'lisi@example.com'),(3,'王五','13900003333',20,'wangwu@example.com');-- 给 id 添加主键索引ALTERTABLE`user`ADDPRIMARYKEY(id);-- 给 phone 添加普通索引CREATEINDEXidx_phoneON`user`(phone);-- 给 email 添加唯一索引CREATEUNIQUEINDEXuk_emailON`user`(email);-- 给 username 和 age 添加联合索引CREATEINDEXidx_username_ageON`user`(username,age);-- 查看 user 表的所有索引SHOWINDEXFROM`user`;-- 使用 phone 查询，可以利用 idx_phone 索引SELECT*FROM`user`WHEREphone='13900001111';-- 使用 username + age 查询，可以利用 idx_username_age 联合索引SELECT*FROM`user`WHEREusername='张三'ANDage=20;-- 删除普通索引DROPINDEXidx_phoneON`user`;-- 删除唯一索引DROPINDEXuk_emailON`user`;-- 删除联合索引DROPINDEXidx_username_ageON`user`;-- 删除主键索引ALTERTABLE`user`DROPPRIMARYKEY;-- 再次查看索引，确认索引已经删除SHOWINDEXFROM`user`;
