mysql创建索引实操

MySQL 创建索引完整实操

这份笔记主要整理 MySQL 里最常用的 4 类索引：普通索引、唯一索引、主键索引、联合索引。

每种索引都分成两种写法：

1. 建表时直接创建索引
2. 表已经存在，后面再新增索引

说明：下面 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 里，主键索引还是聚集索引，也就是说表数据会按照主键组织存储。

注意点：

1. 每张表只能有一个主键
2. 主键字段不能为NULL
3. 主键值不能重复
4. 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`;

常看字段说明：

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. 常用命名规范

工作中建议索引名写清楚，后期排查问题会省很多事。

建议：

1. 索引名尽量能看出对应字段，比如idx_phone、idx_username_age
2. 不要给区分度太低的字段乱建索引，比如性别gender
3. 经常出现在WHERE、JOIN ON、ORDER BY、GROUP BY里的字段更适合考虑建索引
4. 索引不是越多越好，索引会占空间，也会影响新增、修改、删除数据的速度

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`;