首页/MySQL/INDEX 索引管理

INDEX 索引管理

创建、查看和删除索引，使用 EXPLAIN 分析查询执行计划

语法

CREATE INDEX index_name ON table (column1 [, column2 ...]);
ALTER TABLE table ADD INDEX index_name (columns);
DROP INDEX index_name ON table;
EXPLAIN SELECT ...;

参数

参数说明示例级别
CREATE INDEX 创建普通索引 CREATE INDEX idx_email ON users(email); 常用
UNIQUE INDEX 创建唯一索引 CREATE UNIQUE INDEX uk_phone ON users(phone); 常用
SHOW INDEX 查看表的索引信息 SHOW INDEX FROM users; 常用
DROP INDEX 删除索引 DROP INDEX idx_email ON users; 常用
EXPLAIN 分析查询执行计划 EXPLAIN SELECT * FROM users WHERE email = 'a@b.com'; 常用
FULLTEXT 全文索引 CREATE FULLTEXT INDEX ft_title ON articles(title, content); 进阶

示例

创建复合索引

-- 遵循最左前缀原则
CREATE INDEX idx_status_created
  ON orders(status, created_at);
查询条件包含 status 或 status+created_at 时生效

EXPLAIN 分析慢查询

EXPLAIN SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 1
ORDER BY o.created_at DESC
LIMIT 20;
关注 type（最好 ref/range）、key（使用的索引）、rows（扫描行数）

查看索引使用情况

SHOW INDEX FROM orders;

-- 查看索引基数
SELECT
  INDEX_NAME, COLUMN_NAME, CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'orders';
CARDINALITY 越高说明索引区分度越好

覆盖索引优化

-- 创建覆盖索引，查询不需要回表
CREATE INDEX idx_cover_order
  ON orders(user_id, status, created_at, total_amount);

-- 这个查询只需要索引就能完成
SELECT status, created_at, total_amount
FROM orders
WHERE user_id = 1001;
EXPLAIN 中 Extra 显示 Using index 表示覆盖索引生效

常见错误

ERROR 1061 (42000): Duplicate key name 'idx_xxx' 索引名已存在，换一个名字或先删除旧索引
EXPLAIN 显示 type=ALL（全表扫描） 查询没有命中索引。检查 WHERE 条件是否有对应索引，是否使用了函数导致索引失效

技巧

相关命令

mysql-selectmysql-performancemysql-create-table