给MySQL创建索引是优化数据库查询性能的核心操作之一，合理的索引能够大幅减少数据扫描范围，提升查询响应速度。不同业务场景下需要选择不同类型的索引，同时遵循对应的创建规则才能达到最优效果。
MySQL常见索引类型
在创建索引前需要先了解MySQL支持的主流索引类型，不同类型的索引适用场景差异较大：
- 普通索引：最基本的索引类型，没有唯一性限制，仅用于加速查询
- 唯一索引：索引列的值必须唯一，允许存在空值，常用于需要保证字段唯一性的场景
- 主键索引：特殊的唯一索引，不允许有空值，一张表只能有一个主键索引
- 全文索引：用于文本内容的模糊匹配查询，仅支持MyISAM和InnoDB引擎的CHAR、VARCHAR、TEXT类型字段
- 组合索引：同时在多个字段上创建的索引，遵循最左前缀匹配原则
创建索引的两种常用方式
方式一：创建表时同步创建索引
在定义表结构的同时可以直接添加索引，这种方式适合表结构初始设计阶段使用，下面是创建普通表时添加不同类型索引的示例：
-- 创建用户表时添加多种索引
CREATE TABLE user_info (
id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
username VARCHAR(50) NOT NULL COMMENT '用户名',
email VARCHAR(100) COMMENT '邮箱',
intro TEXT COMMENT '个人简介',
register_time DATETIME NOT NULL COMMENT '注册时间',
-- 主键索引，自动创建
PRIMARY KEY (id),
-- 普通索引，加速用户名查询
INDEX idx_username (username),
-- 唯一索引，保证邮箱唯一
UNIQUE INDEX idx_email (email),
-- 全文索引，用于简介内容搜索
FULLTEXT INDEX idx_intro (intro),
-- 组合索引，加速按注册时间和用户名查询的场景
INDEX idx_register_username (register_time, username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
方式二：已有表上新增索引
如果表已经创建完成，需要后续补充索引，可以使用ALTER TABLE或者CREATE INDEX语句，两种方式的语法如下：
使用ALTER TABLE添加索引
-- 给user_info表的register_time字段添加普通索引 ALTER TABLE user_info ADD INDEX idx_register_time (register_time); -- 给user_info表的username和email字段添加组合唯一索引 ALTER TABLE user_info ADD UNIQUE INDEX idx_username_email (username, email);
使用CREATE INDEX添加索引
-- 给user_info表的intro字段添加全文索引 CREATE FULLTEXT INDEX idx_intro_new ON user_info (intro); -- 给user_info表的id和register_time字段添加组合索引 CREATE INDEX idx_id_register ON user_info (id, register_time);
创建索引的注意事项
创建索引并非越多越好，不合理的使用反而会降低数据库写入性能，需要遵循以下原则：
- 选择区分度高的字段创建索引，比如用户表的用户名字段区分度高，比性别字段更适合建索引
- 组合索引遵循最左前缀原则，比如创建了
INDEX idx_a_b_c (a,b,c)，查询条件包含a、a和b、a和b和c时才会命中索引 - 避免在频繁更新的字段上创建索引，每次更新数据都需要同步维护索引，会增加写入开销
- 不要给过长的字段创建普通索引，可以使用前缀索引，比如对VARCHAR(255)的字段只取前20个字符创建索引：
INDEX idx_name (username(20)) - 主键索引尽量使用自增整数，避免随机字符串作为主键导致索引树频繁分裂
查看和删除索引的方法
创建完成后可以通过语句查看表的索引信息，不需要的索引也可以及时删除：
-- 查看user_info表的所有索引 SHOW INDEX FROM user_info; -- 删除user_info表的idx_username索引 DROP INDEX idx_username ON user_info; -- 使用ALTER TABLE删除idx_email索引 ALTER TABLE user_info DROP INDEX idx_email;
注意：删除索引前需要确认该索引没有被业务查询依赖，避免删除后导致查询性能大幅下降。