一、索引基础：从概念到分类

索引是数据库优化的核心工具，其本质是通过构建有序数据结构加速数据检索。常见索引类型包括：

B+树索引
作为主流存储引擎InnoDB的默认索引结构，B+树具备以下特性：
- 多路平衡查找树结构，层高控制在3-4层即可支撑千万级数据
- 所有数据存储在叶子节点，非叶子节点仅存储键值指针
- 叶子节点通过双向链表连接，支持高效范围查询
```
-- 创建普通索引示例
CREATE INDEX idx_user_name ON user(name);
```
哈希索引
基于哈希表实现，提供O(1)时间复杂度的等值查询，但存在两大限制：
- 不支持范围查询（如>、BETWEEN）
- 哈希冲突会导致性能下降
```
-- 内存表支持哈希索引
CREATE TABLE mem_table (
id INT PRIMARY KEY,
name VARCHAR(50),
INDEX USING HASH (name)
) ENGINE=MEMORY;
```
全文索引
针对文本内容的语义检索，通过倒排索引实现：
- 适用于MATCH AGAINST语法
- 需使用MyISAM或InnoDB（5.6+版本）引擎
```
-- 创建全文索引示例
CREATE FULLTEXT INDEX idx_content ON articles(content);
```
空间索引（R-Tree）
用于地理空间数据查询，支持MBRContains等几何函数操作。

二、索引创建策略：从设计到优化

1. 列选择三原则

高选择性列优先：通过CARDINALITY值判断（值越大区分度越高）
```
SHOW INDEX FROM orders;
-- 观察Cardinality列，选择基数大的字段
```
复合索引字段顺序：遵循”最左前缀”原则，将高频查询条件放在左侧
避免过度索引：每个索引增加约10%写入开销，需权衡读写比例

2. 复合索引设计范式

以电商订单查询场景为例：

-- 典型查询模式
SELECT * FROM orders 
WHERE user_id = 1001 
  AND status = 'paid' 
  AND create_time > '2023-01-01';
-- 最佳索引方案
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

设计要点：

将等值查询条件（user_id、status）前置
范围查询条件（create_time）置于末尾
避免包含更新频繁的列（如total_amount）

3. 覆盖索引优化

通过索引包含查询所需全部字段，避免回表操作：

-- 原始查询（需回表）
SELECT id, name FROM user WHERE age > 30;
-- 优化方案（覆盖索引）
CREATE INDEX idx_age_name ON user(age, name);

三、索引失效场景与诊断

1. 常见失效场景

隐式类型转换：

-- 当name字段为varchar时，以下查询索引失效
SELECT * FROM user WHERE name = 123;

使用函数操作：

-- 对索引列使用函数导致失效
SELECT * FROM orders WHERE DATE(create_time) = '2023-01-01';

OR条件陷阱：

-- 当OR条件中任一列无索引时，全表扫描
SELECT * FROM user WHERE name = 'Alice' OR age = 30;

2. 诊断工具与方法

EXPLAIN分析：
```
EXPLAIN SELECT * FROM products 
WHERE category_id = 5 AND price > 100;
```
关键指标解读：
- type列：应避免出现ALL（全表扫描）
- key列：显示实际使用的索引
- rows列：预估扫描行数

慢查询日志：

# my.cnf配置示例
slow_query_log = ON
long_query_time = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

四、索引底层原理深度解析

1. B+树分裂机制

当节点数据量超过填充因子（默认50%-75%）时触发分裂：

申请新节点空间
重新分配键值范围
更新父节点指针
分裂过程会导致短暂性能波动，建议在低峰期执行批量插入操作。

2. 索引维护成本

写入放大：每个索引增加约10%写入I/O
存储开销：InnoDB索引空间约为数据量的1.5-2倍

重建策略：

-- 在线重建索引（避免锁表）
ALTER TABLE large_table ENGINE=InnoDB;

五、高级索引技术

1. 索引下推（ICP）

MySQL 5.6+特性，将WHERE条件过滤下推至存储引擎层：

-- 原始执行流程（无ICP）
1. 使用索引定位到主键
2. 回表获取完整记录
3. 应用WHERE条件过滤
-- ICP优化后流程
1. 使用索引定位到主键
2. 在索引层过滤不符合条件的记录
3. 仅对剩余记录回表

2. 自适应哈希索引（AHI）

InnoDB自动为频繁访问的索引页构建哈希索引，无需手动创建：

触发条件：连续访问模式超过100次

监控方式：

SHOW ENGINE INNODB STATUS\G
-- 观察Adaptive hash index部分

六、实战案例分析

案例1：电商系统查询优化

问题场景：订单表按用户ID分页查询缓慢

-- 原始查询（3秒+）
SELECT * FROM orders 
WHERE user_id = 1001 
ORDER BY create_time DESC 
LIMIT 10000, 20;

优化方案：

创建复合索引：

CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);

改写查询使用索引覆盖：

SELECT id, order_no, total_amount 
FROM orders 
WHERE user_id = 1001 
ORDER BY create_time DESC 
LIMIT 10000, 20;

优化后响应时间降至50ms以内。

案例2：日志系统范围查询优化

问题场景：10亿级日志表按时间范围查询超时

-- 原始查询（超时）
SELECT * FROM system_logs 
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-02';