第四章：索引优化¶

索引类型¶

主键索引¶

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

唯一索引¶

CREATE UNIQUE INDEX idx_email ON users(email);

普通索引¶

CREATE INDEX idx_name ON users(name);

组合索引¶

CREATE INDEX idx_name_age ON users(name, age);

全文索引¶

CREATE FULLTEXT INDEX idx_content ON articles(content);

索引操作¶

创建索引¶

-- 创建表时
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    INDEX idx_name (name)
);

-- 表已存在时
CREATE INDEX idx_name ON users(name);
ALTER TABLE users ADD INDEX idx_name (name);

删除索引¶

DROP INDEX idx_name ON users;
ALTER TABLE users DROP INDEX idx_name;

查看索引¶

SHOW INDEX FROM users;

索引设计原则¶

1. 选择合适的列¶

-- ✅ 高选择性列适合建索引
CREATE INDEX idx_email ON users(email);

-- ❌ 低选择性列不适合
-- CREATE INDEX idx_gender ON users(gender);

2. 最左前缀原则¶

-- 组合索引 (name, age, city)
CREATE INDEX idx_composite ON users(name, age, city);

-- ✅ 有效查询
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';

-- ❌ 无效查询
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE city = '北京';

3. 覆盖索引¶

-- 索引包含查询所需的所有字段
CREATE INDEX idx_covering ON users(name, age);

-- 只需要访问索引
SELECT name, age FROM users WHERE name = '张三';

4. 避免索引失效¶

-- ❌ 索引失效情况

-- 使用函数
SELECT * FROM users WHERE UPPER(name) = '张三';

-- 隐式转换
SELECT * FROM users WHERE name = 123;

-- 使用 OR
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- 使用 != 或 <>
SELECT * FROM users WHERE name != '张三';

-- 使用 LIKE 左模糊
SELECT * FROM users WHERE name LIKE '%三';

EXPLAIN 分析¶

EXPLAIN SELECT * FROM users WHERE name = '张三';

重要字段¶

字段	说明
type	访问类型（ALL, index, range, ref, const）
key	使用的索引
rows	扫描行数
Extra	额外信息

type 优先级¶

system > const > eq_ref > ref > range > index > ALL

索引优化案例¶

案例1：优化分页¶

-- ❌ 低效
SELECT * FROM users LIMIT 10000, 10;

-- ✅ 高效
SELECT * FROM users u
INNER JOIN (SELECT id FROM users LIMIT 10000, 10) t
ON u.id = t.id;

案例2：优化排序¶

-- 添加排序索引
CREATE INDEX idx_age_name ON users(age, name);

-- 使用索引排序
SELECT * FROM users ORDER BY age, name LIMIT 100;

案例3：优化 COUNT¶

-- ❌ 低效
SELECT COUNT(*) FROM users;

-- ✅ 高效（如果只需要估算）
SHOW TABLE STATUS LIKE 'users';

小结¶

本章学习了：

✅ 索引类型
✅ 索引操作
✅ 索引设计原则
✅ 索引失效情况
✅ EXPLAIN 分析
✅ 优化案例

下一章¶

第五章：SQL 调优 - 学习 SQL 性能调优。