索引设计与最佳实践 | 清晨的一缕阳光

引言

索引设计是数据库性能优化的核心环节。一个好的索引设计可以将查询速度提升数十倍甚至上百倍，而糟糕的索引设计不仅浪费存储空间，还会拖慢写入性能。

本章将深入讲解：

索引设计的核心原则
覆盖索引与索引下推优化
前缀索引与函数索引的应用
索引设计实战案例

核心概念

索引设计的目标

索引设计的核心目标是在查询性能和写入性能之间找到最佳平衡点：

graph LR
    subgraph 查询性能
        A[减少 IO 次数]
        B[避免全表扫描]
        C[加速排序分组]
    end
    
    subgraph 写入性能
        D[减少索引维护]
        E[降低锁竞争]
        F[减少存储空间]
    end
    
    A & B & C --> G[最佳平衡点]
    D & E & F --> G
    
    style G fill:#90EE90

索引的代价

每个索引都有成本，设计前需要权衡：

成本类型	说明	影响程度
存储空间	每个索引占用磁盘空间	⭐⭐⭐
写入性能	INSERT/UPDATE/DELETE 需维护索引	⭐⭐⭐⭐
优化器开销	查询优化器需选择最优索引	⭐⭐
内存占用	索引缓存占用 buffer pool	⭐⭐⭐

经验法则：

单表索引数建议不超过 5 个
联合索引列数建议不超过 3-5 列
优先为高频查询设计索引

索引设计原则

1. 选择高选择性列

选择性（Selectivity） = 不同值的数量 / 总行数

选择性越高，索引效果越好：

-- 表结构
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    phone VARCHAR(11) UNIQUE,      -- 选择性 ≈ 1（最优）
    name VARCHAR(50),               -- 选择性中等
    gender TINYINT,                 -- 选择性低（只有 2 个值）
    status TINYINT DEFAULT 1        -- 选择性低（大部分为 1）
);

-- ✅ 适合建索引
CREATE INDEX idx_phone ON users(phone);

-- ⚠️ 谨慎建索引（区分度低）
CREATE INDEX idx_gender ON users(gender);

-- ❌ 不建议建索引
CREATE INDEX idx_status ON users(status);

检查选择性：

-- 查看列的选择性
SELECT 
    COUNT(DISTINCT phone) / COUNT(*) AS phone_selectivity,
    COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity
FROM users;

-- 结果示例：
-- phone_selectivity: 1.0000  (100% 不同)
-- gender_selectivity: 0.0002  (仅 2 个值)

2. 利用最左前缀原则设计联合索引

联合索引遵循最左前缀原则，设计时需考虑查询模式：

-- 常见查询场景
SELECT * FROM orders WHERE user_id = 100;                      -- 场景 1
SELECT * FROM orders WHERE user_id = 100 AND status = 1;       -- 场景 2
SELECT * FROM orders WHERE user_id = 100 AND create_time > '2024-01-01';  -- 场景 3

错误设计：

-- ❌ 索引列顺序不合理
CREATE INDEX idx_status_user ON orders(status, user_id);
-- 场景 1 无法使用该索引（跳过了 status 列）

正确设计：

-- ✅ 将高频查询列放在前面
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- 场景 1、2、3 都可以使用该索引

设计技巧：

等值查询列优先于范围查询列
高频查询列优先于低频查询列
选择性高的列优先于选择性低的列

3. 优先使用覆盖索引

覆盖索引是指查询所需的所有列都在索引中，无需回表：

-- 表结构
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    category_id INT,
    price DECIMAL(10,2),
    stock INT,
    INDEX idx_category_price (category_id, price)
);

-- ❌ 需要回表（查询了索引外的列）
SELECT id, name, price FROM products WHERE category_id = 10;

-- ✅ 覆盖索引（所有查询列都在索引中）
SELECT category_id, price FROM products WHERE category_id = 10;

-- ✅ 覆盖索引（包含主键）
SELECT id, category_id, price FROM products WHERE category_id = 10;

验证覆盖索引：

EXPLAIN SELECT id, category_id, price FROM products WHERE category_id = 10;
-- Extra 列显示：Using index（表示使用了覆盖索引）

4. 避免索引失效场景

常见导致索引失效的操作：

-- 1. 在索引列上使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2024;  -- ❌ 索引失效
SELECT * FROM users WHERE create_time >= '2024-01-01';  -- ✅ 索引有效

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- ❌ phone 是字符串，数字会触发转换
SELECT * FROM users WHERE phone = '13800138000';  -- ✅ 索引有效

-- 3. LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%张%';  -- ❌ 索引失效
SELECT * FROM users WHERE name LIKE '张%';   -- ✅ 索引有效（最左匹配）

-- 4. 使用 OR 连接非索引列
SELECT * FROM orders WHERE user_id = 100 OR status = 1;  -- ❌ status 无索引，全表扫描
SELECT * FROM orders WHERE user_id = 100 OR order_id = 200;  -- ✅ 两个列都有索引

-- 5. 不等于操作
SELECT * FROM users WHERE age != 18;  -- ⚠️ 可能索引失效（取决于数据分布）
SELECT * FROM users WHERE age > 18;   -- ✅ 索引有效

高级索引优化技术

索引下推（Index Condition Pushdown）

索引下推是 MySQL 5.6 引入的优化技术，将过滤条件下推到存储引擎层：

-- 表结构
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_name_city (name, city)
);

-- 查询
SELECT * FROM users WHERE name LIKE '张%' AND city = '北京';

无索引下推（MySQL 5.6 之前）：

通过索引定位 name LIKE '张%' 的记录
回表查询完整行数据
在 Server 层过滤 city = '北京'

有索引下推（MySQL 5.6+）：

通过索引定位 name LIKE '张%' 的记录
在索引层直接过滤 city = '北京'（无需回表）
只回表符合条件的记录

验证索引下推：

EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND city = '北京';
-- Extra 列显示：Using index condition

前缀索引

对于长字符串列，可以只索引前 N 个字符：

-- 原列：VARCHAR(200)
CREATE INDEX idx_email ON users(email);  -- 索引较大

-- 前缀索引：只索引前 20 个字符
CREATE INDEX idx_email_prefix ON users(email(20));  -- 索引更小

选择合适的前缀长度：

-- 计算不同前缀长度的选择性
SELECT 
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS prefix_20,
    COUNT(DISTINCT LEFT(email, 30)) / COUNT(*) AS prefix_30,
    COUNT(DISTINCT email) / COUNT(*) AS full_column
FROM users;

-- 结果示例：
-- prefix_10: 0.85  (85% 选择性)
-- prefix_20: 0.92  (92% 选择性)
-- prefix_30: 0.95  (95% 选择性)
-- full_column: 0.98 (98% 选择性)

-- 选择 prefix_20，在索引大小和选择性之间取得平衡
CREATE INDEX idx_email_prefix ON users(email(20));

注意事项：

前缀索引不支持覆盖索引（需要回表获取完整列值）
前缀索引不能用于 ORDER BY

函数索引（Generated Column + 索引）

MySQL 5.7+ 支持通过生成列实现函数索引：

-- 场景：需要按年份查询
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    create_time DATETIME,
    -- 定义生成列
    create_year INT AS (YEAR(create_time)) STORED,
    -- 为生成列创建索引
    INDEX idx_create_year (create_year)
);

-- 查询（使用函数索引）
SELECT * FROM orders WHERE create_year = 2024;  -- ✅ 索引有效

-- 对比：直接对函数查询
SELECT * FROM orders WHERE YEAR(create_time) = 2024;  -- ❌ 索引失效

覆盖索引优化排序

利用索引的有序性避免 filesort：

-- 表结构
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    category_id INT,
    create_time DATETIME,
    INDEX idx_category_time (category_id, create_time)
);

-- ✅ 利用索引排序（无需 filesort）
SELECT * FROM articles 
WHERE category_id = 10 
ORDER BY create_time DESC 
LIMIT 10;

-- ❌ 无法利用索引排序
SELECT * FROM articles 
WHERE category_id = 10 
ORDER BY create_time DESC, id ASC  -- 排序方向不一致
LIMIT 10;

索引设计实战

电商订单表索引设计

业务场景：

按用户 ID 查询订单列表（高频）
按订单状态筛选（中频）
按时间范围查询（中频）
后台管理按订单号查询（低频）

表结构：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,      -- 订单号
    user_id BIGINT NOT NULL,             -- 用户 ID
    status TINYINT NOT NULL DEFAULT 1,   -- 订单状态
    amount DECIMAL(10,2) NOT NULL,       -- 订单金额
    create_time DATETIME NOT NULL,       -- 创建时间
    pay_time DATETIME,                   -- 支付时间
    INDEX idx_user_id (user_id),         -- 用户查询
    UNIQUE INDEX idx_order_no (order_no) -- 订单号唯一索引
);

常见查询及索引优化：

-- 查询 1：用户订单列表（按时间倒序）
SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC 
LIMIT 20;
-- ✅ 现有索引 idx_user_id 可用，但需要 filesort
-- 优化：创建联合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);

-- 查询 2：用户待支付订单
SELECT * FROM orders 
WHERE user_id = 100 AND status = 1;
-- ✅ 优化索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 查询 3：用户订单统计
SELECT status, COUNT(*), SUM(amount) 
FROM orders 
WHERE user_id = 100 
GROUP BY status;
-- ✅ 覆盖索引优化
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

-- 查询 4：按时间范围查询订单
SELECT * FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01';
-- ⚠️ 需要单独的时间索引
CREATE INDEX idx_create_time ON orders(create_time);

最终索引方案：

-- 主键索引
PRIMARY KEY (id)

-- 唯一索引
UNIQUE INDEX idx_order_no (order_no)

-- 联合索引（核心查询）
INDEX idx_user_time_status (user_id, create_time DESC, status)

-- 单列索引（辅助查询）
INDEX idx_create_time (create_time)

用户表索引设计

业务场景：

手机号登录（高频）
按用户名搜索（中频）
按城市筛选用户（低频）

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    phone VARCHAR(11) NOT NULL,
    username VARCHAR(50),
    email VARCHAR(100),
    city VARCHAR(50),
    status TINYINT DEFAULT 1,
    create_time DATETIME
);

-- 索引设计
UNIQUE INDEX idx_phone (phone);              -- 手机号唯一，登录用
INDEX idx_username (username);               -- 用户名搜索
INDEX idx_city_status (city, status);        -- 城市筛选（联合状态）

注意事项

1. 避免过度索引

-- ❌ 错误示例：为每个列都创建索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_status ON users(status);
-- 问题：写入性能严重下降，优化器选择困难

-- ✅ 正确做法：根据实际查询设计
-- 分析慢查询日志，只为高频查询创建索引

2. 定期清理无用索引

-- 查看索引使用情况
SELECT 
    table_schema,
    table_name,
    index_name,
    rows_selected,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE table_schema = 'your_database'
ORDER BY rows_selected DESC;

-- 删除长期未使用的索引
ALTER TABLE users DROP INDEX idx_unused;

3. 大表索引变更策略

对于大表（>100 万行），添加索引需谨慎：

-- ❌ 直接添加索引（可能锁表）
ALTER TABLE large_table ADD INDEX idx_new (column);

-- ✅ 低风险方案
-- 方案 1：使用 pt-online-schema-change（Percona 工具）
pt-online-schema-change --add-index "idx_new(column)" D=database,t=large_table

-- 方案 2：MySQL 8.0+ 使用 INPLACE 算法
ALTER TABLE large_table ADD INDEX idx_new (column), ALGORITHM=INPLACE, LOCK=NONE;

-- 方案 3：业务低峰期执行
-- 在凌晨等低峰时段执行索引变更

4. 监控索引健康度

-- 查看索引碎片率
SELECT 
    table_name,
    index_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    ROUND(data_free / 1024 / 1024, 2) AS free_mb,
    ROUND(data_free / (data_length + index_length) * 100, 2) AS fragmentation_pct
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY fragmentation_pct DESC;

-- 优化高碎片表（谨慎使用）
OPTIMIZE TABLE large_table;

总结

核心要点

选择性原则：优先为高选择性列创建索引
最左前缀：联合索引设计需考虑查询顺序
覆盖索引：尽可能让查询命中覆盖索引
避免失效：注意函数、类型转换等导致索引失效的场景
平衡取舍：在查询性能和写入性能之间找到平衡点

索引设计检查清单

是否为高频查询列创建了索引
联合索引的列顺序是否合理
是否存在可以优化的覆盖索引
是否有长期未使用的冗余索引
索引是否会导致写入性能严重下降
是否考虑了索引的存储空间成本

下一步

掌握索引设计后，下一章我们将学习：

索引失效的具体场景分析
如何使用 EXPLAIN 分析执行计划
慢查询日志的配置与分析方法

参考资料

MySQL 官方文档 - 索引优化
《高性能 MySQL》第 5 章：创建高性能的索引
MySQL Index Condition Pushdown
Percona pt-online-schema-change