MySQL索引策略

索引是数据库性能优化的核心技术之一。合理的索引策略可以显著提升查询性能，而不当的索引设计则可能导致性能下降。本文档将详细介绍MySQL索引的设计原则、策略和最佳实践。

索引基础概念

索引类型

B-Tree索引：MySQL中最常用的索引类型，适用于等值查询、范围查询和排序操作。

sql

-- B-Tree索引示例
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);

哈希索引：仅适用于等值查询，不支持范围查询和排序，Memory存储引擎支持。

sql

-- Memory表中的哈希索引
CREATE TABLE temp_data (
    id INT PRIMARY KEY,
    key_value VARCHAR(50),
    INDEX USING HASH (key_value)
) ENGINE=MEMORY;

全文索引：用于文本搜索，支持自然语言和布尔模式搜索。

sql

-- 全文索引示例
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_title_content (title, content)
);

-- 全文搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL optimization' IN NATURAL LANGUAGE MODE);

空间索引：用于地理空间数据类型的索引。

sql

-- 空间索引示例
CREATE TABLE locations (
    id INT PRIMARY KEY,
    coordinate POINT NOT NULL,
    SPATIAL INDEX(coord_idx) (coordinate)
);

索引数据结构

MySQL的InnoDB存储引擎使用B+树结构：

叶子节点：存储实际数据或数据指针
非叶子节点：存储索引键值和指针
双向链表：连接叶子节点，便于范围查询

索引设计原则

选择性原则

选择性 = 不重复的值的数量 / 总行数，选择性越高，索引效果越好。

sql

-- 计算列的选择性
SELECT 
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity
FROM users;

-- 高选择性列适合创建索引
CREATE INDEX idx_user_email ON users(email); -- 高选择性，好索引

-- 低选择性列通常不适合单独创建索引
-- CREATE INDEX idx_user_gender ON users(gender); -- 低选择性，效果差

最左前缀原则

对于复合索引，查询条件必须包含索引的最左列才能有效使用索引。

sql

-- 复合索引定义
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- 有效使用索引的查询
SELECT * FROM orders WHERE user_id = 1;  -- 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';  -- 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending' AND created_at > '2023-01-01';  -- 使用索引

-- 无法有效使用索引的查询
SELECT * FROM orders WHERE status = 'pending';  -- 跳过最左列，无法使用索引
SELECT * FROM orders WHERE created_at > '2023-01-01';  -- 跳过最左列，无法使用索引

覆盖索引原则

当索引包含了查询所需的所有列时，称为覆盖索引，可以避免回表操作。

sql

-- 创建覆盖索引
CREATE INDEX idx_order_summary ON orders(user_id, status, total_amount);

-- 这个查询只需要索引，无需回表
SELECT user_id, status, total_amount 
FROM orders 
WHERE user_id = 123 AND status = 'completed';

-- 检查是否使用了覆盖索引
EXPLAIN SELECT user_id, status, total_amount 
FROM orders 
WHERE user_id = 123 AND status = 'completed';
-- 如果Extra列显示"Using index"，说明使用了覆盖索引

索引策略

单列索引策略

主键索引：自动创建，唯一且不能为空

sql

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自动创建主键索引
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

唯一索引：确保列值唯一性

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL,
    
    UNIQUE KEY uk_email (email),      -- 唯一索引
    UNIQUE KEY uk_username (username) -- 唯一索引
);

普通索引：提高查询性能

sql

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),       -- 普通索引
    INDEX idx_status (status),         -- 普通索引
    INDEX idx_created_at (created_at)  -- 普通索引
);

复合索引策略

查询模式驱动的索引设计：

sql

-- 假设最常见的查询模式是：
-- 1. WHERE user_id = ? AND status = ?
-- 2. WHERE user_id = ? AND status = ? AND created_at > ?
-- 3. WHERE user_id = ? ORDER BY created_at

-- 最优的复合索引设计
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 这个索引可以支持上述所有查询模式

排序和分组的索引设计：

sql

-- 需要按用户ID分组统计订单
SELECT user_id, COUNT(*), AVG(total_amount) 
FROM orders 
WHERE status = 'completed' 
GROUP BY user_id;

-- 最优索引
CREATE INDEX idx_status_user ON orders(status, user_id);

-- 需要按时间范围查询并排序
SELECT * FROM orders 
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31' 
ORDER BY total_amount DESC;

-- 最优索引
CREATE INDEX idx_date_amount ON orders(created_at, total_amount);

前缀索引策略

对于长字符串列，可以创建前缀索引以节省空间：

sql

-- 为长字符串创建前缀索引
CREATE INDEX idx_title_prefix ON articles(title(50)); -- 只索引前50个字符

-- 计算合适的前缀长度
SELECT 
    CHAR_LENGTH(title) AS len,
    COUNT(*) AS cnt,
    COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS sel10,
    COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS sel20,
    COUNT(DISTINCT LEFT(title, 30)) / COUNT(*) AS sel30,
    COUNT(DISTINCT LEFT(title, 50)) / COUNT(*) AS sel50
FROM articles 
GROUP BY CHAR_LENGTH(title) 
ORDER BY cnt DESC 
LIMIT 10;

特殊场景索引策略

高并发写入场景

sql

-- 减少索引数量以提高写入性能
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_level VARCHAR(10),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 只创建必要的索引
    INDEX idx_created_at (created_at)
    -- 避免创建过多索引影响插入性能
);

大数据量分页查询

sql

-- 传统分页在大数据量时性能差
-- SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 使用游标分页
CREATE INDEX idx_cursor_pagination ON orders(created_at, id);

-- 分页查询优化
SELECT * FROM orders 
WHERE (created_at, id) > (?, ?) 
ORDER BY created_at, id 
LIMIT 10;

多值查询场景

sql

-- 使用JSON类型和函数索引（MySQL 8.0+）
CREATE TABLE user_tags (
    id INT PRIMARY KEY,
    tags JSON,
    INDEX idx_tags ((CAST(tags AS CHAR(255) ARRAY)))
);

-- 或使用关联表
CREATE TABLE user_interests (
    user_id INT,
    interest VARCHAR(50),
    PRIMARY KEY (user_id, interest)
);

索引优化技巧

索引列顺序优化

sql

-- 根据查询频率和选择性排列列顺序
-- 高频率 + 高选择性 → 最左边
CREATE INDEX idx_status_user_date ON orders(status, user_id, created_at);

-- status: 高频率查询条件
-- user_id: 高选择性
-- created_at: 排序需求

索引合并策略

MySQL可以合并多个单列索引：

sql

-- MySQL可以合并以下索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);

-- 等效于复合查询
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

函数索引（MySQL 8.0+）

sql

-- 为函数结果创建索引
CREATE INDEX idx_lower_email ON users((LOWER(email)));

-- 查询时可以使用索引
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

索引维护策略

定期分析表统计信息

sql

-- 更新表统计信息以帮助优化器选择最优执行计划
ANALYZE TABLE users, orders, products;

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'users';

监控索引使用情况

sql

-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_READ DESC;

-- 查找未使用的索引
SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME
FROM information_schema.TABLES t
JOIN information_schema.STATISTICS s ON t.TABLE_NAME = s.TABLE_NAME 
    AND t.TABLE_SCHEMA = s.TABLE_SCHEMA
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage iu 
    ON s.INDEX_NAME = iu.INDEX_NAME 
    AND s.TABLE_NAME = iu.OBJECT_NAME 
    AND s.TABLE_SCHEMA = iu.OBJECT_SCHEMA
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')
    AND s.INDEX_NAME != 'PRIMARY'
    AND iu.COUNT_READ = 0 OR iu.COUNT_READ IS NULL;

索引重建

sql

-- 重建表以优化索引（会锁表）
ALTER TABLE orders ENGINE=InnoDB;

-- 或者使用在线DDL（MySQL 5.6+）
ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

索引性能测试

使用EXPLAIN分析

sql

-- 分析查询执行计划
EXPLAIN FORMAT=JSON
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id
HAVING order_count > 5;

性能对比测试

sql

-- 测试不同索引策略的性能
-- 测试前
SELECT SQL_NO_CACHE COUNT(*) FROM orders WHERE status = 'pending';

-- 添加索引后
CREATE INDEX idx_status ON orders(status);
SELECT SQL_NO_CACHE COUNT(*) FROM orders WHERE status = 'pending';

索引反模式

过度索引

sql

-- 不好的做法：为每个列都创建索引
CREATE TABLE bad_example (
    id INT PRIMARY KEY,
    col1 VARCHAR(50),
    col2 VARCHAR(50),
    col3 VARCHAR(50),
    col4 VARCHAR(50),
    -- 为每个列都创建索引会严重影响INSERT/UPDATE/DELETE性能
    INDEX idx_col1 (col1),
    INDEX idx_col2 (col2),
    INDEX idx_col3 (col3),
    INDEX idx_col4 (col4)
);

索引列上使用函数

sql

-- 不好的做法：在索引列上使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 无法使用索引

-- 好的做法：改写查询条件
SELECT * FROM users 
WHERE created_at >= '2023-01-01' 
AND created_at < '2024-01-01'; -- 可以使用索引

索引设计最佳实践

1. 基于查询模式设计

sql

-- 分析实际查询模式
-- 1. 频繁的查询条件
-- 2. JOIN条件
-- 3. ORDER BY子句
-- 4. GROUP BY子句

-- 基于以下查询模式设计索引
SELECT u.username, o.total 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'active' 
AND o.created_at > '2023-01-01' 
ORDER BY o.created_at DESC;

2. 平衡读写性能

sql

-- 读写比高的表：可以创建更多索引
-- 读写比低的表：减少索引数量

-- 评估索引成本
SELECT 
    t.TABLE_NAME,
    i.INDEX_NAME,
    i.NON_UNIQUE,
    i.COLUMN_NAME,
    s.INDEX_LENGTH,
    t.DATA_LENGTH,
    t.TABLE_ROWS
FROM information_schema.TABLES t
JOIN information_schema.STATISTICS i ON t.TABLE_NAME = i.TABLE_NAME
JOIN information_schema.INNODB_SYS_INDEXES s ON i.INDEX_NAME = s.NAME
WHERE t.TABLE_SCHEMA = 'your_database';

3. 定期审查和优化

定期分析查询日志，识别慢查询
检查索引使用统计，删除未使用的索引
根据业务变化调整索引策略
监控数据库性能指标

总结

索引策略的设计需要综合考虑查询模式、数据分布、业务需求和性能要求。良好的索引策略应该：

基于实际查询模式设计
平衡读写性能需求
定期监控和优化
避免过度索引
考虑未来的扩展需求

通过遵循这些原则和实践，可以建立高效、可维护的索引策略，显著提升数据库性能。

MySQL索引策略 #

索引基础概念 #

索引类型 #

索引数据结构 #

索引设计原则 #

选择性原则 #

最左前缀原则 #

覆盖索引原则 #

索引策略 #

单列索引策略 #

复合索引策略 #

前缀索引策略 #

特殊场景索引策略 #

高并发写入场景 #

大数据量分页查询 #

多值查询场景 #

索引优化技巧 #

索引列顺序优化 #

索引合并策略 #

函数索引（MySQL 8.0+） #

索引维护策略 #

定期分析表统计信息 #

监控索引使用情况 #

索引重建 #

索引性能测试 #

使用EXPLAIN分析 #

性能对比测试 #

索引反模式 #

过度索引 #

索引列上使用函数 #

索引设计最佳实践 #

1. 基于查询模式设计 #

2. 平衡读写性能 #

3. 定期审查和优化 #

总结 #