索引优化原则

索引设计需要平衡查询效率与维护成本，遵循以下核心原则。

核心设计原则

1. 选择合适列建索引

-- ✅ 高选择性列适合建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- ❌ 低选择性列不适合
-- 如性别、状态等只有几个值的列
CREATE INDEX idx_gender ON users(gender);  -- 不推荐

-- ✅ 组合使用提高选择性
CREATE INDEX idx_gender_age ON users(gender, age);

2. 联合索引遵循最左前缀

-- 联合索引 (a, b, c)
CREATE INDEX idx_a_b_c ON t1(a, b, c);

-- ✅ 可以使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- ❌ 无法使用索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

3. 覆盖索引优先

-- ✅ 覆盖索引，无需回表
CREATE INDEX idx_user_status ON orders(user_id, status);
SELECT user_id, status FROM orders WHERE user_id = 1;

-- ❌ 需回表查询其他字段
SELECT * FROM orders WHERE user_id = 1;

4. 控制索引数量

-- 单表索引数量建议 ≤ 5
-- 过多索引影响写入性能

-- 查看表索引
SHOW INDEX FROM orders;

索引列选择原则

避免索引失效场景

禁止在索引列上运算

-- ❌ 索引失效
SELECT * FROM orders WHERE id + 1 = 100;

-- ✅ 索引有效
SELECT * FROM orders WHERE id = 99;

避免类型隐式转换

-- ❌ 字符列用数字查询，索引失效
SELECT * FROM users WHERE phone = 13800000000;

-- ✅ 类型一致
SELECT * FROM users WHERE phone = '13800000000';

避免使用函数

-- ❌ 索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-05-15';

-- ✅ 索引有效
SELECT * FROM orders WHERE created_at >= '2026-05-15' AND created_at < '2026-05-16';

避免LIKE左模糊

-- ❌ 索引失效
SELECT * FROM users WHERE name LIKE '%张';

-- ✅ 索引有效
SELECT * FROM users WHERE name LIKE '张%';

索引长度优化

-- 前缀索引，减少索引大小
CREATE INDEX idx_name ON users(name(10));

-- 但前缀索引无法用于ORDER BY和覆盖索引
SELECT * FROM users WHERE name = '张三丰';  -- 需回表验证

-- 查看索引选择性
SELECT COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) FROM users;
-- 建议：选择性 > 0.3

索引维护

删除无效索引

-- 查看索引使用情况
SELECT
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL AND COUNT_READ = 0;

-- 删除不使用的索引
ALTER TABLE orders DROP INDEX idx_unused;

定期重建索引

-- 索引碎片整理
ALTER TABLE orders ENGINE=InnoDB;
-- 或使用 pt-online-schema-change

主键选择原则

-- ✅ 自增主键，减少页分裂
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ...
);

-- ❌ 随机主键，导致大量页分裂
CREATE TABLE orders (
    uuid VARCHAR(36) PRIMARY KEY,  -- 不推荐
    ...
);

-- ⚠️ 业务主键需评估写入量
CREATE TABLE orders (
    order_no VARCHAR(20) PRIMARY KEY,  -- 有序时可接受
    ...
);

要点总结

• 高选择性列适合建索引，低选择性列避免
• 联合索引遵循最左前缀原则
• 优先设计覆盖索引减少回表
• 单表索引数量控制在合理范围
• 避免索引列运算、函数、隐式转换
• 自增主键减少页分裂提升写入性能