MySQL索引优化实战

索引是提升数据库查询性能的关键，本文将深入探讨MySQL索引的设计与优化策略。

1. 索引类型

B+树索引

InnoDB默认使用B+树，特点：

• 非叶子节点只存储键值
• 叶子节点存储键值和数据
• 叶子节点通过双向链表连接

优势：范围查询高效，减少IO次数

聚簇索引 vs 非聚簇索引

-- 聚簇索引（主键）
SELECT * FROM users WHERE id = 100;

-- 非聚簇索引（辅助索引）
-- 需要回表查询完整数据
SELECT * FROM users WHERE email = "test@example.com";
​

联合索引

CREATE INDEX idx_user_status ON users(status, created_at);

-- 最佳实践：最左前缀原则
-- 可以使用索引：
WHERE status = 1 AND created_at > "2024-01-01"
WHERE status = 1

-- 无法使用索引：
WHERE created_at > "2024-01-01"
​

2. 索引设计原则

选择性高的列优先

-- 选择性 = 不同值数量 / 总行数

-- 高选择性（适合建索引）
gender ENUM("M", "F")  -- 选择性低，不适合
user_id BIGINT         -- 选择性高，适合

-- 计算选择性
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
​

覆盖索引

-- 联合索引覆盖查询，避免回表
CREATE INDEX idx_user_cover ON users(status, name, email);

-- 查询只使用索引，不回表
SELECT name, email FROM users WHERE status = 1;
​

3. 索引优化案例

案例1：避免全表扫描

-- 优化前（全表扫描）
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = "2024-01-01";

-- 优化后（使用索引）
EXPLAIN SELECT * FROM orders 
WHERE created_at >= "2024-01-01 00:00:00" 
  AND created_at < "2024-01-02 00:00:00";
​

案例2：优化JOIN

-- 小表驱动大表
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.small_id;

-- 确保JOIN列有索引
CREATE INDEX idx_large_small_id ON large_table(small_id);
​

案例3：避免索引失效

-- 避免在索引列上使用函数
-- 低效
WHERE SUBSTRING(name, 1, 3) = "abc"

-- 高效
WHERE name LIKE "abc%"

-- 避免隐式转换
-- 低效
WHERE phone = 13800138000  -- phone是VARCHAR

-- 高效
WHERE phone = "13800138000"
​

4. 索引监控

-- 查看索引使用情况
SELECT 
    table_name,
    index_name,
    cardinality,
    nullable
FROM information_schema.statistics
WHERE table_schema = "your_db";

-- 分析索引效率
ANALYZE TABLE table_name;
SHOW INDEX FROM table_name;
​

5. 分区表

-- 按日期分区
CREATE TABLE orders (
    id BIGINT,
    created_at DATETIME,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- 查询自动裁剪分区
SELECT * FROM orders 
WHERE created_at >= "2024-01-01";
​

通过合理设计和优化索引，可以将查询性能提升成百上千倍。