第3章：索引底层实现

📖 章节概述

本章将深入讲解 MySQL 索引的底层实现原理，这是数据库性能优化的核心。通过本章学习，你将掌握：

  • B+Tree 索引结构和工作原理
  • 聚簇索引与二级索引的区别
  • 覆盖索引、索引下推等优化技术
  • 联合索引的最左前缀原则
  • 索引设计的最佳实践

🧱 一、索引的本质

索引（Index）就是一种用于快速定位数据的有序数据结构。 在 MySQL（尤其是 InnoDB）中，索引本质上是一个 B+Tree（多路平衡树）。

📘 一句话： 索引是为了减少磁盘 I/O 次数，通过树形结构实现高效查找。

🌳 二、B+Tree 索引结构详解

1️⃣ B+Tree 动画演示

B+Tree 的构建过程（以插入数据为例）：

初始状态：空树
┌─────────────┐
│    (空)     │
└─────────────┘

插入 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
┌─────────────────────────────────┐
│  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] │  ← 叶子节点
└─────────────────────────────────┘

当叶子节点满时，发生分裂：
┌─────────────────────────────────┐
│  [1] [2] [3] [4] [5] │ [6] [7] [8] [9] [10] │  ← 分裂
└─────────────────────────────────┘
         │
         ▼
┌─────────────┐
│     [5]     │  ← 内部节点（分裂点）
└──────┬──────┘
       │
   ┌───┴───┐
   ▼       ▼
┌─────┐ ┌─────┐
│[1-4]│ │[6-10]│  ← 叶子节点
└─────┘ └─────┘

B+Tree 的查询过程：

查询 key=7 的过程：
1. 从根节点开始：7 > 5，走右子树
2. 到达叶子节点：[6,7,8,9,10]
3. 在叶子节点中找到 key=7
4. 返回对应的数据行

查询效率：O(log n)

2️⃣ 物理结构详解

              ┌────────────┐
              │   根节点（索引页）│
              │  [5] [10]  │  ← 只存键值，不存数据
              └──────┬─────┘
                     │
       ┌────────────┼────────────┐
       ▼                            ▼
┌────────────┐            ┌────────────┐
│ 内部节点（索引页） │    ...    │ 内部节点 │
│ [2] [4]    │            │ [7] [9]    │
└──────┬─────┘            └──────┬─────┘
       │                            │
┌────────────┐            ┌────────────┐
│ 叶子节点（数据页） │←→│ 叶子节点 │←→...
│ [1][2][3][4]│            │ [7][8][9][10]│  ← 存储完整数据
└────────────┘            └────────────┘

关键特点：

  • 所有数据都在叶子节点中（聚簇索引）
  • 叶子节点通过双向链表连接 → 范围查询效率极高
  • 内部节点只存索引键 + 子节点指针，不存数据
  • 树高度平衡，查询时间复杂度为 O(log n)

2️⃣ 为什么 MySQL 使用 B+Tree？

结构优点缺点
B-Tree读写平衡每层都存数据，I/O 次数多
B+Tree所有数据在叶子层，可顺序遍历需多一次层访问
Hash等值查找快无法范围查询、无法排序、容易冲突

✅ 结论： B+Tree 最适合磁盘存储场景，因为它能显著减少磁盘随机访问次数。

🧩 三、InnoDB 的索引类型

1️⃣ 聚簇索引（Clustered Index）

  • 表的主键索引即为聚簇索引
  • 数据行按主键顺序存储在叶子节点
  • 叶子节点同时存储主键值 + 行记录
PRIMARY KEY(id)
→ 叶子节点中直接存储整行数据

📌 若没有定义主键：

  • MySQL 会自动选择第一个非空唯一索引
  • 若仍无，则创建一个隐藏的 row_id

2️⃣ 二级索引（Secondary Index）

  • 叶子节点只存储主键值而不是整行
  • 查询时需"回表"到聚簇索引获取完整数据
CREATE INDEX idx_name ON user(name);
SELECT * FROM user WHERE name='Tom';

-- 执行流程：
-- idx_name → 找到主键值(id=100)
--         → 聚簇索引（PRIMARY）回表查数据行

📌 "回表" = 通过主键二次查找数据

🔍 四、覆盖索引（Covering Index）

如果查询字段都被索引包含，就无需回表，大幅提升性能。

SELECT name, age FROM user WHERE name='Tom';

-- 若索引 idx_name_age (name, age) 存在，则：
-- • 数据可直接从索引页读取
-- • 无需访问聚簇索引

✅ 优势：

  1. 减少 I/O
  2. 减少锁竞争
  3. 利于执行计划优化

🧠 五、联合索引与最左前缀原则

联合索引示例

CREATE INDEX idx_user_name_age ON user(name, age, city);

最左前缀原则

查询条件从最左列开始匹配，若中间断裂，则后续列失效。

查询是否命中索引
WHERE name='Tom'✅ 命中第一列
WHERE name='Tom' AND age=20✅ 命中前两列
WHERE age=20❌ 不命中（跳过最左）
WHERE name LIKE 'T%'✅ 支持前缀匹配
WHERE name LIKE '%om'❌ 无法命中

⚙️ 六、索引下推优化（Index Condition Pushdown, ICP）

MySQL 5.6 引入的优化。

传统做法：

读取索引 → 回表获取整行 → Server 层再过滤

ICP 优化后：

把部分过滤条件下推到存储引擎层，减少回表次数

示例：

SELECT * FROM user WHERE name LIKE 'T%' AND age > 18;

-- 若 (name, age) 上有联合索引，则：
-- • name LIKE 'T%' 用于定位
-- • age > 18 由存储引擎层直接过滤
-- • 仅返回符合条件的主键值，减少回表

✅ 提升点： 减少 CPU + I/O

🔍 七、索引失效场景与检测

1️⃣ 常见索引失效场景

场景示例解决方案原因分析
函数操作WHERE UPPER(name) = 'TOM'改写为 WHERE name = 'tom'函数破坏了索引的有序性
隐式转换WHERE phone = 13800138000改为 WHERE phone = '13800138000'类型转换导致索引失效
前置通配符WHERE name LIKE '%tom'使用全文索引或ES无法利用索引的有序性
范围查询中断WHERE a=1 AND b>3 AND c=4调整索引顺序或使用ICP最左前缀原则
OR 条件WHERE a=1 OR b=2拆分为 UNION 查询无法同时使用两个索引
NOT 操作WHERE name != 'tom'改写为 WHERE name > 'tom' OR name < 'tom'无法利用索引范围
IN 子查询WHERE id IN (SELECT id FROM t2)使用 EXISTS 或 JOIN子查询优化问题
NULL 值判断WHERE name IS NULL避免 NULL 值或使用特殊索引NULL 值索引处理
计算表达式WHERE age + 1 = 25改写为 WHERE age = 24表达式破坏了索引
字符串截取WHERE LEFT(name, 3) = 'tom'使用前缀索引或全文索引函数破坏了索引

2️⃣ 索引失效检测工具

1. EXPLAIN 分析：

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE UPPER(name) = 'TOM';
-- 如果 type=ALL，说明索引失效

-- 详细分析
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE UPPER(name) = 'TOM';

2. 索引使用统计：

-- 查看索引使用情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mysql_learning'
ORDER BY COUNT_FETCH DESC;

-- 查找未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mysql_learning'
AND COUNT_FETCH = 0
AND COUNT_INSERT = 0
AND COUNT_UPDATE = 0
AND COUNT_DELETE = 0;

3. 慢查询分析：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 分析慢查询
-- 使用 pt-query-digest 工具
-- pt-query-digest /var/lib/mysql/slow.log

4. 索引选择性分析：

-- 计算索引选择性
SELECT 
    COLUMN_NAME,
    COUNT(DISTINCT COLUMN_NAME) / COUNT(*) AS selectivity,
    COUNT(*) AS total_rows
FROM users
GROUP BY COLUMN_NAME
ORDER BY selectivity DESC;

-- 选择性 > 0.1 的列适合建索引

3️⃣ 索引优化最佳实践

1. 索引设计原则：

  • 选择性高的列优先
  • 遵循最左前缀原则
  • 避免过多索引
  • 定期清理无用索引

2. 查询优化技巧：

  • 避免在索引列上使用函数
  • 使用覆盖索引减少回表
  • 合理使用索引下推
  • 避免 SELECT * 查询

3. 监控指标：

  • 索引命中率
  • 查询响应时间
  • 锁等待时间
  • 磁盘I/O使用率

🧩 八、B+Tree 页分裂详细过程

当页空间不足时，InnoDB 会触发页分裂：

1️⃣ 页分裂触发条件

// 页分裂判断（简化版）
bool page_needs_split(page_t* page, ulint size) {
    return (page_get_free_space(page) < size);
}
// 源码路径：storage/innobase/include/page0page.h

2️⃣ 页分裂算法

// 页分裂核心函数
dberr_t btr_page_split_and_insert() {
    // 1. 计算分裂点
    ulint split_rec = page_find_split_point(page);
    
    // 2. 创建新页
    page_t* new_page = page_create();
    
    // 3. 移动记录
    page_move_rec_list_end(page, new_page, split_rec);
    
    // 4. 插入新记录
    page_cur_insert_rec_low(page, new_rec);
    
    // 5. 更新父节点
    btr_insert_on_non_leaf_level();
}
// 源码路径：storage/innobase/btr/btr0btr.cc

3️⃣ 页分裂代价分析

分裂类型代价原因
中间分裂高需要移动大量记录
尾部插入低只需创建新页
随机插入中需要重新平衡树

4️⃣ 页分裂优化策略

-- 1. 使用自增主键（减少页分裂）
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 顺序插入
    name VARCHAR(50)
);

-- 2. 避免随机UUID主键
CREATE TABLE bad_table (
    id VARCHAR(36) PRIMARY KEY,  -- 随机插入，频繁分裂
    data TEXT
);

-- 3. 监控页分裂频率
SHOW STATUS LIKE 'Innodb_page_splits';

🔍 八、索引统计信息收集机制

InnoDB 通过统计信息帮助优化器选择最优索引：

1️⃣ Cardinality 计算

// Cardinality 估算算法
ulint btr_estimate_n_rows_in_range() {
    // 1. 采样统计
    ulint n_sample_pages = 8;
    ulint n_sample_rows = 0;
    
    // 2. 随机采样
    for (ulint i = 0; i < n_sample_pages; i++) {
        page_t* page = btr_cur_get_page();
        n_sample_rows += page_get_n_recs(page);
    }
    
    // 3. 估算总行数
    return (n_sample_rows * n_pages) / n_sample_pages;
}
// 源码路径：storage/innobase/btr/btr0cur.cc

2️⃣ 统计信息更新

-- 手动更新统计信息
ANALYZE TABLE users;

-- 查看统计信息
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SUB_PART,
    PACKED,
    NULLABLE,
    INDEX_TYPE
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'mysql_learning'
AND TABLE_NAME = 'users';

-- 配置自动更新
SET GLOBAL innodb_stats_auto_recalc = ON;
SET GLOBAL innodb_stats_persistent = ON;

3️⃣ 统计信息调优

-- 调整采样页数（提高准确性）
SET GLOBAL innodb_stats_sample_pages = 20;

-- 设置统计信息持久化
ALTER TABLE users STATS_PERSISTENT = 1;

-- 查看统计信息更新时间
SELECT 
    TABLE_NAME,
    UPDATE_TIME,
    CHECK_TIME
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mysql_learning';

🧠 九、前缀索引与全文索引

1️⃣ 前缀索引实现

-- 创建前缀索引
CREATE INDEX idx_name_prefix ON users(name(10));

-- 查看前缀索引效果
EXPLAIN SELECT * FROM users WHERE name LIKE '张%';
-- 应该显示 key_len: 43 (10*4+3)

📊 前缀长度选择

-- 计算最优前缀长度
SELECT 
    COUNT(DISTINCT LEFT(name, 1)) / COUNT(*) AS prefix_1,
    COUNT(DISTINCT LEFT(name, 2)) / COUNT(*) AS prefix_2,
    COUNT(DISTINCT LEFT(name, 3)) / COUNT(*) AS prefix_3,
    COUNT(DISTINCT LEFT(name, 4)) / COUNT(*) AS prefix_4,
    COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS prefix_5
FROM users;

2️⃣ 全文索引（FULLTEXT）

-- 创建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
) ENGINE=InnoDB;

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

-- 布尔模式搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

🔍 全文索引配置

-- 查看全文索引配置
SHOW VARIABLES LIKE 'innodb_ft%';

-- 设置最小词长度
SET GLOBAL innodb_ft_min_token_size = 2;

-- 设置停用词表
SET GLOBAL innodb_ft_enable_stopword = ON;

📊 十、索引碎片率监控与优化

1️⃣ 碎片率计算

-- 计算表碎片率
SELECT 
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Total_MB',
    ROUND((DATA_LENGTH / 1024 / 1024), 2) AS 'Data_MB',
    ROUND((INDEX_LENGTH / 1024 / 1024), 2) AS 'Index_MB',
    ROUND((DATA_FREE / 1024 / 1024), 2) AS 'Free_MB',
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS 'Frag_%'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mysql_learning'
AND DATA_FREE > 0;

2️⃣ 碎片优化方法

-- 方法1：重建表（推荐）
ALTER TABLE users ENGINE=InnoDB;

-- 方法2：优化表
OPTIMIZE TABLE users;

-- 方法3：在线重建（MySQL 5.6+）
ALTER TABLE users ALGORITHM=INPLACE, LOCK=NONE;

3️⃣ 碎片监控脚本

-- 创建碎片监控视图
CREATE VIEW table_fragmentation AS
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS fragmentation_pct,
    ROUND((DATA_FREE / 1024 / 1024), 2) AS free_space_mb
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND DATA_FREE > 0
ORDER BY fragmentation_pct DESC;

-- 查询高碎片表
SELECT * FROM table_fragmentation 
WHERE fragmentation_pct > 20;

📊 七、执行计划分析（EXPLAIN）

EXPLAIN 是性能优化必备命令。

EXPLAIN SELECT * FROM user WHERE name='Tom';
字段含义
id查询中语句的执行顺序（大的先执行）
select_type查询类型（SIMPLE、PRIMARY、SUBQUERY 等）
table表名
type访问类型（系统、const、ref、range、index、ALL）
possible_keys可用索引
key实际使用索引
rows预估扫描行数
Extra额外信息（如 Using index、Using where）

📌 重点：

  • Using index → 覆盖索引命中
  • Using where → 还需过滤
  • Using temporary / filesort → 排序或分组可优化点

🛠️ 实操演示

实操1：创建测试表和索引

-- 创建测试表
USE mysql_learning;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO users (name, age, city, email) VALUES 
('张三', 25, '北京', 'zhangsan@example.com'),
('李四', 30, '上海', 'lisi@example.com'),
('王五', 28, '广州', 'wangwu@example.com'),
('赵六', 35, '深圳', 'zhaoliu@example.com'),
('钱七', 22, '北京', 'qianqi@example.com');

实操2：创建不同类型的索引

-- 创建单列索引
CREATE INDEX idx_name ON users(name);

-- 创建联合索引
CREATE INDEX idx_age_city ON users(age, city);

-- 创建覆盖索引
CREATE INDEX idx_name_age_email ON users(name, age, email);

-- 查看表的所有索引
SHOW INDEX FROM users;

实操3：EXPLAIN 分析执行计划

-- 分析不同的查询
EXPLAIN SELECT * FROM users WHERE name = '张三';

EXPLAIN SELECT * FROM users WHERE age = 25;

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;

EXPLAIN SELECT name, age FROM users WHERE name = '张三';

-- 查看详细的执行信息
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';

实操4：覆盖索引效果验证

-- 测试回表查询
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- 应该显示 type: ref, Extra: NULL

-- 测试覆盖索引查询
EXPLAIN SELECT name, age, email FROM users WHERE name = '张三';
-- 应该显示 type: ref, Extra: Using index

-- 性能对比测试
-- 开启 profiling
SET profiling = 1;

-- 执行回表查询
SELECT * FROM users WHERE name = '张三';

-- 执行覆盖索引查询
SELECT name, age, email FROM users WHERE name = '张三';

-- 查看性能分析
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
SHOW PROFILE FOR QUERY 2;

实操5：最左前缀原则验证

-- 创建联合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 测试不同的查询条件
-- 1. 命中第一列
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 2. 命中前两列
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;

-- 3. 命中所有列
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';

-- 4. 跳过最左列（不命中索引）
EXPLAIN SELECT * FROM users WHERE age = 25;

-- 5. 中间断裂（只命中第一列）
EXPLAIN SELECT * FROM users WHERE name = '张三' AND city = '北京';

实操6：索引下推（ICP）演示

-- 创建测试索引
CREATE INDEX idx_name_age ON users(name, age);

-- 查看 ICP 是否启用
SHOW VARIABLES LIKE 'optimizer_switch';

-- 测试 ICP 效果
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age > 20;
-- 应该显示 Extra: Using index condition

-- 禁用 ICP 进行对比
SET optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age > 20;

-- 重新启用 ICP
SET optimizer_switch = 'index_condition_pushdown=on';

实操7：索引选择性分析

-- 查看索引的选择性
SELECT 
    COUNT(DISTINCT name) / COUNT(*) AS name_selectivity,
    COUNT(DISTINCT age) / COUNT(*) AS age_selectivity,
    COUNT(DISTINCT city) / COUNT(*) AS city_selectivity
FROM users;

-- 查看索引的基数
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'mysql_learning' 
AND TABLE_NAME = 'users';

实操8：索引使用情况监控

-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mysql_learning'
ORDER BY COUNT_FETCH DESC;

-- 查看未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mysql_learning'
AND COUNT_FETCH = 0
AND COUNT_INSERT = 0
AND COUNT_UPDATE = 0
AND COUNT_DELETE = 0;

🎯 实战案例

案例1：电商商品表索引设计

场景描述： 设计一个电商商品表的索引，支持按分类、价格、销量等维度查询。

表结构设计：

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    sales_count INT NOT NULL DEFAULT 0,
    status ENUM('active', 'inactive', 'deleted') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO products (name, category_id, price, stock, sales_count) VALUES 
('iPhone 15', 1, 7999.00, 100, 500),
('MacBook Pro', 1, 12999.00, 50, 200),
('Nike Air Max', 2, 899.00, 200, 1000),
('Adidas Ultraboost', 2, 1299.00, 150, 800);

索引设计策略：

-- 1. 主键索引（聚簇索引）
-- PRIMARY KEY (id) - 自动创建

-- 2. 分类查询索引
CREATE INDEX idx_category_status ON products(category_id, status);

-- 3. 价格范围查询索引
CREATE INDEX idx_price_status ON products(price, status);

-- 4. 销量排序索引
CREATE INDEX idx_sales_count ON products(sales_count DESC);

-- 5. 复合查询索引（分类+价格+状态）
CREATE INDEX idx_category_price_status ON products(category_id, price, status);

-- 6. 覆盖索引（商品列表查询）
CREATE INDEX idx_category_name_price ON products(category_id, name, price);

查询优化示例：

-- 查询某个分类下的商品（使用覆盖索引）
EXPLAIN SELECT name, price FROM products 
WHERE category_id = 1 AND status = 'active';

-- 价格范围查询
EXPLAIN SELECT * FROM products 
WHERE price BETWEEN 1000 AND 5000 AND status = 'active';

-- 销量排行榜
EXPLAIN SELECT * FROM products 
WHERE status = 'active' 
ORDER BY sales_count DESC 
LIMIT 10;

案例2：用户行为分析表索引优化

问题场景： 用户行为日志表查询慢，需要按用户ID、时间范围、行为类型查询。

原始表结构：

CREATE TABLE user_behavior_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    target_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

问题分析：

-- 问题查询
SELECT * FROM user_behavior_logs 
WHERE user_id = 123 
AND action_type = 'view' 
AND created_at >= '2024-01-01';

-- 查看执行计划
EXPLAIN SELECT * FROM user_behavior_logs 
WHERE user_id = 123 
AND action_type = 'view' 
AND created_at >= '2024-01-01';
-- 可能显示 type: ref, key: idx_user_id, Extra: Using where

优化方案：

-- 删除旧索引
DROP INDEX idx_user_id ON user_behavior_logs;
DROP INDEX idx_created_at ON user_behavior_logs;

-- 创建复合索引（按查询频率排序）
CREATE INDEX idx_user_action_time ON user_behavior_logs(user_id, action_type, created_at);

-- 验证优化效果
EXPLAIN SELECT * FROM user_behavior_logs 
WHERE user_id = 123 
AND action_type = 'view' 
AND created_at >= '2024-01-01';
-- 应该显示 type: range, key: idx_user_action_time, Extra: Using index condition

📝 练习题目

基础题

  1. 什么是 B+Tree？为什么 MySQL 选择 B+Tree 作为索引结构？
  2. 聚簇索引和二级索引的区别是什么？
  3. 什么是覆盖索引？它有什么优势？

进阶题

  1. 联合索引的最左前缀原则是什么？如何设计高效的联合索引？
  2. 什么是索引下推（ICP）？它如何提升查询性能？
  3. 如何通过 EXPLAIN 分析查询的执行计划？

实战题

  1. 设计一个订单表的索引，支持按用户、时间、状态等多维度查询
  2. 分析一个慢查询，设计合适的索引进行优化
  3. 如何监控和清理未使用的索引？

📚 扩展阅读

下一章预告： 第4章：查询优化与执行计划 - 深入理解 MySQL 查询优化器的决策过程和执行计划分析。