MySQL索引是数据库中用于快速查找数据的数据结构，合理操作索引可以有效提升查询效率，减少全表扫描带来的性能损耗。不同的索引类型对应不同的操作方式，同时存储引擎的差异也会影响部分操作的语法。
查看索引的操作
查看表中已有索引是最基础的操作，常用的语法有两种，分别适用于不同的使用场景。
使用SHOW INDEX语句
SHOW INDEX可以直接查看指定表的索引详情，返回结果包含索引名称、字段、唯一性等关键信息。
-- 查看test_table表的所有索引 SHOW INDEX FROM test_table; -- 查看指定数据库下的表索引，避免当前数据库不是目标库的情况 SHOW INDEX FROM test_db.test_table;
查询information_schema系统表
information_schema库的STATISTICS表存储了所有表的索引元数据，适合需要批量查询或者过滤特定条件的场景。
-- 查询test_table表的所有索引名称 SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'test_db' AND TABLE_NAME = 'test_table';
创建索引的操作
MySQL支持在创建表时同时创建索引，也可以在表创建完成后为已有表添加索引，不同场景对应不同的语法。
创建表时添加索引
在CREATE TABLE语句中可以直接定义各类索引，适合新表设计阶段确定索引结构的场景。
-- 创建表时添加主键索引、唯一索引、普通索引
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
age INT,
-- 唯一索引，确保邮箱不重复
UNIQUE INDEX idx_email (email),
-- 普通索引，提升按用户名查询的效率
INDEX idx_username (username),
-- 联合索引，适合同时按用户名和年龄查询的场景
INDEX idx_username_age (username, age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
为已有表添加索引
使用ALTER TABLE或者CREATE INDEX语句可以为已经存在的表添加索引，ALTER TABLE支持添加主键索引，CREATE INDEX更简洁适合普通索引和唯一索引的创建。
-- 使用ALTER TABLE添加普通索引 ALTER TABLE user_info ADD INDEX idx_age (age); -- 使用ALTER TABLE添加唯一索引 ALTER TABLE user_info ADD UNIQUE INDEX idx_email_new (email); -- 使用CREATE INDEX添加联合索引 CREATE INDEX idx_name_age ON user_info (username, age); -- 添加主键索引（需要字段非空且唯一） ALTER TABLE user_info ADD PRIMARY KEY (id);
删除索引的操作
删除索引需要根据索引类型选择对应的语法，主键索引的删除方式和其他索引存在差异。
删除普通索引和唯一索引
可以使用DROP INDEX语句或者ALTER TABLE语句删除普通索引和唯一索引，两种方式效果一致。
-- 使用DROP INDEX删除索引 DROP INDEX idx_age ON user_info; -- 使用ALTER TABLE删除索引 ALTER TABLE user_info DROP INDEX idx_username;
删除主键索引
主键索引的删除需要使用ALTER TABLE语句，且如果主键是自增字段，需要先取消自增属性再删除。
-- 先修改自增主键的属性 ALTER TABLE user_info MODIFY id INT; -- 删除主键索引 ALTER TABLE user_info DROP PRIMARY KEY;
索引操作注意事项
- 删除索引前建议先查看索引是否被查询使用，避免删除高频使用的索引导致查询性能下降
- 为已有大表添加索引会锁表，建议在业务低峰期操作，InnoDB引擎支持在线DDL可以减少锁表时间
- 联合索引的字段顺序会影响使用效果，最左匹配原则需要被遵守，创建时需要根据查询条件合理排序
- 唯一索引会校验数据唯一性，添加时如果表中已有重复数据，操作会失败
索引不是越多越好，过多的索引会增加数据写入和更新的开销，同时占用额外的存储空间，需要根据实际查询场景合理创建。
索引操作示例验证
可以通过以下完整流程验证上述操作的正确性，先创建表再执行索引的增删查操作。
-- 1. 创建测试表
CREATE TABLE test_order (
order_id INT,
user_id INT,
order_time DATETIME,
amount DECIMAL(10,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 2. 查看初始索引（此时只有默认的隐藏主键相关索引）
SHOW INDEX FROM test_order;
-- 3. 添加普通索引和唯一索引
CREATE INDEX idx_user_id ON test_order (user_id);
ALTER TABLE test_order ADD UNIQUE INDEX idx_order_id (order_id);
-- 4. 再次查看索引，确认新增成功
SHOW INDEX FROM test_order;
-- 5. 删除普通索引
DROP INDEX idx_user_id ON test_order;
-- 6. 最后查看索引，确认普通索引已被删除
SHOW INDEX FROM test_order;