数据库索引优化指南

数据库索引全面指南

一、索引基础概念

1. 索引定义

• 类比解释：类似书籍目录，通过页码快速定位内容
• 技术定义：存储在磁盘上的特殊数据结构，作为加速数据库查询的数据指针集合
• 核心价值：显著减少磁盘I/O操作，避免全表扫描

2. 索引优缺点对比

二、索引数据结构

1. B+树（默认结构）

特点：

• 数据仅存储在叶子节点（非叶子节点只保存索引键）
• 叶子节点形成双向链表（支持高效范围查询）
• 典型树高3-4层（千万级数据仅需3次I/O）

2. 哈希索引

• 仅Memory引擎支持
• 采用键值对存储（Key=哈希值, Value=行指针）
• 适用场景：精确等值查询（=），不支持范围查询

三、MySQL索引分类

1. 功能分类

2. 存储结构分类

四、索引管理操作

1. 创建索引

-- 建表时创建
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(50) UNIQUE,
    name VARCHAR(50),                   
    INDEX idx_name (name),
    FULLTEXT INDEX ft_bio (bio)
);

-- 已有表添加
ALTER TABLE orders ADD INDEX idx_amount (order_amount);
CREATE INDEX idx_date ON logs (create_time);

2. 查看索引

SHOW INDEX FROM users;

输出解析：

• Non_unique: 0表示唯一索引，1表示非唯一
• Seq_in_index: 列在索引中的顺序
• Cardinality: 基数估算（值越大索引效果越好）
• Index_type: 索引类型（BTREE/HASH/FULLTEXT）

3. 删除索引

ALTER TABLE users DROP INDEX idx_name;
DROP INDEX ft_bio ON users;

五、索引优化策略

1. 最左前缀原则

复合索引 (col1, col2, col3) 的有效查询：

WHERE col1 = 'A'                    -- √ 使用索引
WHERE col1 = 'A' AND col2 = 'B'     -- √ 使用索引
WHERE col2 = 'B'                    -- ✘ 不触发索引
WHERE col1 LIKE 'A%'                -- √ 前缀匹配

2. 索引失效场景

3. 覆盖索引

-- 创建复合索引
CREATE INDEX idx_cover ON products (category, price);

-- 覆盖索引查询
SELECT category, price FROM products 
WHERE category = 'Electronics';  -- 无需回表

六、执行计划分析

EXPLAIN SELECT * FROM users WHERE age > 25;

七、高级技巧

1. 索引下推（ICP）

-- 索引: (last_name, first_name)
SELECT * FROM employees 
WHERE last_name = 'Smith' 
AND first_name LIKE 'J%';

• 无ICP：先查所有Smith，再回表过滤
• 有ICP：直接在索引中过滤名以J开头

2. 前缀索引

-- 计算合适长度
SELECT 
  COUNT(DISTINCT LEFT(address, 10))/COUNT(*) AS sel10,
  COUNT(DISTINCT LEFT(address, 15))/COUNT(*) AS sel15
FROM users;

-- 创建索引
CREATE INDEX idx_address ON users (address(15));

八、设计最佳实践

选列原则：

• 高频出现在WHERE/JOIN/GROUP BY/ORDER BY的列
• 高区分度（Cardinality大）的列优先
• 避免为NULL值过多的列建索引

复合索引策略：

• 等值查询列在前，范围查询列在后
• 常用字段排序规则一致
• 单表索引数建议不超过5个

维护建议：

-- 定期优化
ALTER TABLE orders ENGINE=InnoDB; 
ANALYZE TABLE users;  -- 更新统计信息

九、实战案例

原始慢查询：

SELECT product_id, COUNT(*) 
FROM orders
WHERE status = 'completed'
  AND create_time BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_id
ORDER BY COUNT(*) DESC
LIMIT 10;

优化方案：

1. 创建复合索引：

CREATE INDEX idx_order_stats ON orders 
(status, create_time, product_id);

1. 二次优化（覆盖索引）：

SELECT product_id, COUNT(*) 
FROM orders USE INDEX (idx_order_stats)
WHERE status = 'completed'
  AND create_time BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_id;

十、常见问题

Q1：索引列顺序的重要性？ A：复合索引遵循最左前缀匹配原则，顺序决定索引可用性。

Q2：所有表都需要主键吗？ A：InnoDB表强烈建议：

• 避免使用隐藏的6字节ROWID
• 提升查询效率，方便数据管理

Q3：何时删除索引？ A：当出现：

• 长期未使用的索引（>6个月）
• 小表（<1000行）全表扫描更快
• 严重影响写入性能时

优化路线图

1. 定位瓶颈：分析慢查询日志和EXPLAIN结果
2. 设计索引：应用最左前缀、覆盖索引和高区分度原则
3. 避免陷阱：防止对索引列进行计算或函数转换
4. 持续监控：定期检查索引使用效率
5. 性能平衡：权衡读写操作需求

黄金法则：精准设计的适量索引才能最大化性能提升。