索引管理：创建、删除、维护

创建索引

基本语法

-- 单列索引
CREATE INDEX idx_email ON users(email);

-- 复合索引
CREATE INDEX idx_name ON users(last_name, first_name);

-- 指定索引类型（PostgreSQL）
CREATE INDEX idx_email_hash ON users USING HASH (email);

唯一索引

CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- 违反唯一性会报错
INSERT INTO users (email) VALUES ('alice@example.com');  -- 第二次插入失败

部分索引（PostgreSQL）

-- 只索引活跃用户
CREATE INDEX idx_active_email ON users(email) WHERE status = 'active';

-- 查询必须包含条件
SELECT * FROM users WHERE email = 'alice@example.com' AND status = 'active';  -- 使用索引

并发创建

-- PostgreSQL：不阻塞写入
CREATE INDEX CONCURRENTLY idx_email ON users(email);

-- MySQL：默认在线 DDL（8.0+）
CREATE INDEX idx_email ON users(email);

删除索引

DROP INDEX idx_email;

-- PostgreSQL：并发删除
DROP INDEX CONCURRENTLY idx_email;

-- 如果存在则删除
DROP INDEX IF EXISTS idx_email;

查看索引

PostgreSQL

-- 查看表的所有索引
\d users

-- 查询索引信息
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- 索引大小
SELECT pg_size_pretty(pg_relation_size('idx_email'));

MySQL

-- 查看索引
SHOW INDEX FROM users;

-- 查询索引信息
SELECT * FROM information_schema.statistics WHERE table_name = 'users';

索引维护

重建索引

-- PostgreSQL
REINDEX INDEX idx_email;
REINDEX TABLE users;

-- MySQL
ALTER TABLE users DROP INDEX idx_email, ADD INDEX idx_email (email);

分析统计信息

-- PostgreSQL
ANALYZE users;

-- MySQL
ANALYZE TABLE users;

监控索引使用

PostgreSQL

-- 查看索引使用情况
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- 未使用的索引

MySQL

-- 查看索引统计
SELECT * FROM sys.schema_unused_indexes;

练习

1. 创建单列和复合索引
2. 创建唯一索引并测试
3. 创建部分索引（PostgreSQL）
4. 并发创建索引
5. 查看和删除索引
6. 重建索引
7. 监控索引使用情况
8. 识别并删除未使用的索引

完成练习后，打开本章答案页核对。

下一步

下一节学习 EXPLAIN：分析查询计划，识别性能瓶颈。

回到第六章概览 · 返回SQL 学习路线