mysql的索引是帮助数据库高效获取数据的数据结构，合理的索引可以大幅提升查询速度，而创建和删除索引是数据库日常维护中非常常见的操作，掌握这两种操作是使用mysql的基础技能。
mysql创建索引的方法
mysql支持多种创建索引的方式，不同的方式适用于不同的场景，下面逐一介绍。
1. 创建表时直接创建索引
在创建数据表的同时，可以直接定义索引，这种方式适合表结构已经确定，且明确需要某些索引的场景。语法格式如下：
-- 创建表时添加普通索引
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
age INT,
-- 普通索引
INDEX idx_username (username),
-- 唯一索引
UNIQUE INDEX idx_email (email)
);
-- 创建表时添加复合索引
CREATE TABLE order_info (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
order_time DATETIME,
-- 复合索引，包含user_id和order_time两个字段
INDEX idx_user_time (user_id, order_time)
);
2. 使用CREATE INDEX语句创建索引
如果表已经存在，需要新增索引，可以使用CREATE INDEX语句，这种方式不会影响表已有的数据，适合给线上运行的表添加索引。
-- 给已有表添加普通索引 CREATE INDEX idx_age ON user_info(age); -- 给已有表添加唯一索引 CREATE UNIQUE INDEX idx_username_unique ON user_info(username); -- 给已有表添加复合索引 CREATE INDEX idx_user_order ON order_info(user_id, order_time);
需要注意，CREATE INDEX语句不能用来创建主键索引，主键索引需要在建表时或者通过ALTER TABLE语句添加。
3. 使用ALTER TABLE语句创建索引
ALTER TABLE语句也可以用来添加索引，功能比CREATE INDEX更全面，支持添加主键索引、普通索引、唯一索引等所有类型。
-- 添加普通索引 ALTER TABLE user_info ADD INDEX idx_age (age); -- 添加唯一索引 ALTER TABLE user_info ADD UNIQUE INDEX idx_email_unique (email); -- 添加主键索引，注意主键索引的字段必须非空且唯一 ALTER TABLE user_info ADD PRIMARY KEY (id);
mysql删除索引的方法
当索引不再需要，或者索引影响了写入性能时，就需要删除索引，mysql删除索引也有两种常用方式。
1. 使用DROP INDEX语句删除索引
DROP INDEX是删除索引最直观的方式，语法简单，适合删除普通索引和唯一索引。
-- 删除普通索引 DROP INDEX idx_age ON user_info; -- 删除唯一索引 DROP INDEX idx_email_unique ON user_info;
2. 使用ALTER TABLE语句删除索引
ALTER TABLE语句同样支持删除索引，还可以用来删除主键索引，这是DROP INDEX做不到的。
-- 删除普通索引 ALTER TABLE user_info DROP INDEX idx_username; -- 删除唯一索引 ALTER TABLE user_info DROP INDEX idx_email; -- 删除主键索引，注意删除主键索引前需要确认没有其他表关联该主键 ALTER TABLE user_info DROP PRIMARY KEY;
创建和删除索引的注意事项
- 索引并不是越多越好，过多的索引会占用额外的存储空间，还会降低插入、更新、删除数据的速度，因为每次数据变更都需要同步更新索引。
- 创建索引时，尽量选择区分度高的字段，比如用户表的用户名字段比性别字段更适合做索引，因为性别只有两个值，区分度低，索引效果差。
- 删除索引前，最好先确认该索引没有被查询语句使用，避免删除后导致查询性能下降，可以通过mysql的慢查询日志或者EXPLAIN语句分析索引使用情况。
- 给大表添加索引时，尽量选择业务低峰期操作，避免锁表影响线上业务，mysql 5.6及以上版本支持在线添加索引，能减少锁表时间。
索引操作相关查询语句
在操作索引前后，我们可能需要查看表的索引信息，下面是常用的查询语句：
-- 查看表的所有索引信息 SHOW INDEX FROM user_info; -- 查看表的创建语句，包含索引定义 SHOW CREATE TABLE user_info;
通过以上方法，就可以完成mysql中索引的创建和删除操作，根据实际需求选择合适的操作方式，才能让索引更好地服务于数据库性能优化。