MySQL 索引优化策略

索引优化是提升查询性能的核心手段，正确的索引设计可大幅减少查询时间。

最左匹配原则

-- 组合索引 (a, b, c)
CREATE INDEX idx_abc ON users(a, b, c);

-- 匹配规则：从左连续匹配

-- ✓ 使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b = 2 ORDER BY c

-- ✗ 不使用索引（缺少a）
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

-- ✗ 范围查询后的字段不匹配
WHERE a = 1 AND b > 2 AND c = 3  -- c不匹配
-- 范围条件（>、<、LIKE、BETWEEN）后索引失效

覆盖索引

-- 覆盖索引：查询字段全部在索引中
CREATE INDEX idx_name_age ON users(name, age);

-- ✓ 覆盖索引，无需回表
SELECT name, age FROM users WHERE name = '张三';

-- ✗ 需回表查询
SELECT * FROM users WHERE name = '张三';

-- 使用EXPLAIN验证
EXPLAIN SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index（覆盖索引）

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- Extra: 无Using index

索引选择性

-- 选择性 = DISTINCT值 / 总行数
-- 越接近1，索引效果越好

-- 计算选择性
SELECT COUNT(DISTINCT name) / COUNT(*) FROM users;

-- 高选择性字段适合建索引
-- name选择性 = 0.9，适合建索引
-- gender选择性 = 0.5，不适合建索引

-- 组合索引字段顺序
-- 高选择性字段放在前面
CREATE INDEX idx_status_name ON users(status, name);  -- 不推荐
CREATE INDEX idx_name_status ON users(name, status);  -- 推荐

避免索引失效

-- 1. 不在索引列上做运算
-- ✗ 索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2024;
-- ✓ 使用范围查询
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- 2. 避免隐式类型转换
-- email是VARCHAR类型
-- ✗ 索引失效（字符串转数字）
SELECT * FROM users WHERE email = 123;
-- ✓ 正确类型
SELECT * FROM users WHERE email = '123';

-- 3. 避免使用函数
-- ✗ 索引失效
SELECT * FROM users WHERE LOWER(name) = 'zhangsan';
-- ✓ 正确方式
SELECT * FROM users WHERE name = 'zhangsan';

-- 4. 避免前缀模糊查询
-- ✗ 索引失效
SELECT * FROM users WHERE name LIKE '%张三';
-- ✓ 索引有效
SELECT * FROM users WHERE name LIKE '张三%';

-- 5. 避免 OR 条件（部分情况）
-- ✗ 索引失效
SELECT * FROM users WHERE name = '张三' OR age = 20;
-- ✓ 使用 UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 20;

-- 6. 避免 NULL 条件
-- ✗ 索引失效
SELECT * FROM users WHERE name IS NULL;
-- 建议字段设置NOT NULL

索引下推（ICP）

-- 索引下推：将过滤条件下推到索引层
-- MySQL 5.6+ 特性

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

-- 查询
SELECT * FROM users WHERE name LIKE '张%' AND age = 20;

-- 无ICP：存储引擎返回name匹配行，Server层过滤age
-- 有ICP：存储引擎直接过滤name和age，减少回表

-- 查看是否使用ICP
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age = 20;
-- Extra: Using index condition

索引维护

-- 分析表，更新索引统计信息
ANALYZE TABLE users;

-- 重建索引（解决索引碎片）
ALTER TABLE users ENGINE = InnoDB;

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

-- 删除无用索引
ALTER TABLE users DROP INDEX idx_unused;

索引设计建议

-- 1. 单表索引数量控制在5个以内
-- 2. 组合索引字段控制在3个以内
-- 3. 区分度高的字段优先建索引
-- 4. 频繁更新的字段少建索引
-- 5. 小表（<1000行）不建索引
-- 6. 定期清理无用索引

索引设计需根据实际查询场景，不是越多越好。

要点总结

• 组合索引遵循最左匹配原则
• 覆盖索引可避免回表，提升性能
• 索引选择性高才有效果
• 避免在索引列上运算、函数、类型转换
• 定期维护索引，清理无用索引