MySQL数据库索引是一种特殊的数据库结构，它类似于书籍的目录，能够帮助数据库快速定位到符合条件的数据行，避免全表扫描，从而大幅提升查询效率。索引会单独存储在数据库的索引文件中，和表数据分开管理，合理的索引设计是数据库性能优化的核心环节之一。
MySQL索引的常见类型
MySQL支持多种类型的索引，不同类型的索引适用场景有所区别，常见的索引类型如下：
- 普通索引：最基本的索引类型，没有任何约束，仅用于加速查询
- 唯一索引：索引列的值必须唯一，允许存在空值，常用于保证列数据的唯一性
- 主键索引：特殊的唯一索引，不允许有空值，每个表只能有一个主键索引
- 联合索引：由多个列共同组成的索引，遵循最左前缀匹配原则
- 全文索引：用于文本内容的模糊查询，仅支持MyISAM和InnoDB引擎的CHAR、VARCHAR、TEXT类型列
索引的基本操作
创建索引
可以在创建表的时候同时创建索引，也可以给已存在的表添加索引，以下是两种场景的示例：
建表时创建索引
-- 创建用户表时添加普通索引和唯一索引
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT,
-- 创建普通索引，加速age列的查询
INDEX idx_age (age),
-- 创建唯一索引，保证email不重复
UNIQUE INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
给已存在的表添加索引
-- 给user_info表的username列添加普通索引 CREATE INDEX idx_username ON user_info(username); -- 添加联合索引，包含username和age两个列 CREATE INDEX idx_username_age ON user_info(username, age);
查看索引
可以通过SHOW INDEX语句查看表的索引信息：
-- 查看user_info表的所有索引 SHOW INDEX FROM user_info;
删除索引
如果索引不再使用，可以手动删除释放存储空间：
-- 删除idx_age索引 DROP INDEX idx_age ON user_info; -- 删除主键索引（需要先取消自增属性再操作） ALTER TABLE user_info DROP PRIMARY KEY;
索引使用注意事项
虽然索引能提升查询效率，但并不是索引越多越好，使用时需要注意以下问题：
- 联合索引需要遵循最左前缀匹配原则，比如联合索引是
(a, b, c)，那么查询条件包含a、a和b、a和b和c时才会生效，仅使用b或c不会触发索引 - 避免在索引列上使用函数或者进行运算，比如
WHERE YEAR(create_time) = 2023会导致索引失效，全表扫描 - 索引列尽量不要存储空值，空值较多时会影响索引的查询效率
- 频繁更新的列不适合创建索引，因为每次更新数据都需要同步更新索引，会增加写入的开销
- 小表（数据量很少的表）不需要创建索引，全表扫描的速度可能比走索引更快
索引性能验证示例
我们可以通过EXPLAIN语句查看查询语句的执行计划，判断索引是否生效：
-- 查看未创建索引时的查询计划 EXPLAIN SELECT * FROM user_info WHERE age = 20; -- 给age列创建索引后再次查看执行计划 CREATE INDEX idx_age ON user_info(age); EXPLAIN SELECT * FROM user_info WHERE age = 20;
执行第一个查询时，type字段会显示ALL，代表全表扫描；创建索引后再次执行，type字段会显示ref或者range，代表使用了索引查询，查询效率会有明显提升。