一、 索引分类概述
1.1 按底层结构
- B + 树索引：最通用，支持等值、范围、排序；InnoDB、Oracle、TiDB 主力索引。
- 哈希索引：等值快（O (1)），不支持范围 / 排序；MySQL Memory、Oracle 哈希簇等。
- R 树（空间索引）：地理坐标、范围查询。
- 倒排索引（全文）：文本分词、关键词检索。
1.2 按存储与数据关系
- 聚簇索引（Clustered）：索引和数据行 “捆在一起”，叶子节点存整行；一张表只能一个（InnoDB 主键、TiDB 主键）。
- 非聚簇索引（Secondary）：索引和数据分离，叶子只存主键 / ROWID；可多个。
1.3 按约束 / 用途
- 主键索引（PRIMARY KEY）：非空 + 唯一，特殊的聚簇 / 唯一索引。
- 唯一索引（UNIQUE）：列值唯一，允许 NULL。
- 普通索引（NORMAL）：无约束，只加速查询。
- 复合索引（联合）：多列组合，遵循最左前缀。
- 函数索引：对表达式结果建索引（如 UPPER (name)）。
- 前缀索引：只对字符串前 N 个字符建索引。
1.4 按分布式 / 分区特性
- 本地索引（Local）：分区表内，索引分区 = 表分区。
- 全局索引（Global）：跨所有分区，独立分区。
二、TiDB 的索引类型
2.1 聚簇索引（主键索引）
聚簇索引 = 索引和「整行数据」存放在一起。数据行被 “聚集、捆绑” 在索引结构里，所以叫聚簇索引。
- 表的完整数据行，直接存在聚簇索引的叶子节点里
- 整个表的数据，本身就是一棵 B+ 树（聚簇索引树）
- 一张表有且只有一个聚簇索引
TiDB 中每张表都有一行数据按主键组织存储，这就是聚簇索引：
CREATE TABLE users (
id BIGINT PRIMARY KEY, -- 聚簇索引
name VARCHAR(64),
email VARCHAR(128)
);
聚簇索引的特点：
- 数据就是索引，索引就是数据
- 按主键查询只需要一次 KV 查找
- 主键查询效率最高
主键查询路径:
SELECT * FROM users WHERE id = 100
TiDB Server
│
v
构造 Key: t{table_id}_r100
│
v
发送到对应 TiKV Region
│
v
RocksDB 查找 Key → 直接得到行数据
2.2 二级索引
二级索引是独立于主键的索引结构：
CREATE INDEX idx_email ON users(email);
二级索引的存储：
Key: t{table_id}_i{index_id}_{email_value}_{row_id}
Value: (空，row_id 已在 Key 中)
通过二级索引查询的过程：
SELECT * FROM users WHERE email = 'alice@example.com'
步骤 1: 查找二级索引
Key: t56_i1_"alice@example.com"
→ 得到 row_id = 100
步骤 2: 回表查找主键
Key: t56_r100
→ 得到完整行数据: [id=100, name="Alice", email="alice@example.com"]
回表是二级索引查询不可避免的额外开销。如果查询的列都在索引中，就不需要回表，这叫做覆盖索引。
-- 覆盖索引示例
SELECT id, email FROM users WHERE email = 'alice@example.com';
-- 只需要查二级索引，不需要回表
2.3 联合索引
CREATE INDEX idx_name_age ON users(name, age);
联合索引遵循最左前缀原则：
-- 能使用联合索引
SELECT * FROM users WHERE name = 'Alice'; -- ✓ 使用最左列
SELECT * FROM users WHERE name = 'Alice' AND age = 28; -- ✓ 使用全部列
-- 不能使用联合索引
SELECT * FROM users WHERE age = 28; -- ✗ 缺少最左列
SELECT * FROM users WHERE name LIKE '%ice' AND age = 28; -- ✗ name 前缀通配符导致索引失效
可以通过 EXPLAIN 验证：
EXPLAIN SELECT * FROM users WHERE age = 28;
-- 如果看到 TableFullScan，说明没走索引
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
-- 如果看到 IndexRangeScan，说明走了索引
2.4 唯一索引
CREATE UNIQUE INDEX uk_email ON users(email);
唯一索引与普通二级索引在结构上完全一致，只是额外在写入时检查唯一性约束。
2.5 前缀索引
当列值很长时（如 URL、长文本），可以对前缀建立索引：
CREATE INDEX idx_url_prefix ON pages(url(64));
-- 只对 URL 的前 64 个字符建索引
前缀索引可以减小索引大小，但可能导致查询效率下降（需要回表后再精确匹配）。
三、执行计划解读
3.1 EXPLAIN 基础
EXPLAIN 是理解 SQL 执行过程最重要的工具：
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
+-------------------------------+---------+-----------+----------------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+----------------------+--------------------------------+
| IndexReader_6 | 1.00 | root | | index:IndexRangeScan_5 |
| └─IndexRangeScan_5 | 1.00 | cop[tikv] | table:users, index:idx_name | range:["Alice","Alice"] |
+-------------------------------+---------+-----------+----------------------+--------------------------------+
各列含义：
| 列 | 含义 |
|---|---|
| id | 算子名称 |
| estRows | 优化器估算的输出行数 |
| task | 执行位置：root（TiDB）或 cop[tikv]（下推到 TiKV） |
| access object | 访问的表/索引 |
| operator info | 算子的详细信息 |
3.2 常见算子
数据访问算子
| 算子 | 含义 | 触发条件 |
|---|---|---|
| TableFullScan | 全表扫描 | 无索引或优化器认为全表更快 |
| TableRangeScan | 按主键范围扫描 | WHERE id BETWEEN … |
| TableRowIDScan | 按 Row ID 扫描 | 回表时使用 |
| IndexFullScan | 全索引扫描 | 索引列无过滤条件 |
| IndexRangeScan | 按索引范围扫描 | WHERE indexed_col = … |
| Point_Get | 单行获取 | WHERE primary_key = const |
| Batch_Point_Get | 批量单行获取 | WHERE primary_key IN (…) |
Point_Get 是效率最高的查询：
EXPLAIN SELECT * FROM users WHERE id = 100;
-- id: Point_Get, 直接从 TiKV 获取一行，无需构建执行计划树
EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);
-- id: Batch_Point_Get, 批量获取指定主键的行
过滤算子
| 算子 | 含义 |
|---|---|
| Selection | WHERE 条件过滤 |
| HashAgg | 哈希聚合（GROUP BY） |
| StreamAgg | 流式聚合（有序数据的 GROUP BY） |
| Sort | 排序（ORDER BY） |
| TopN | Top N 查询（ORDER BY … LIMIT） |
| Limit | 行数限制 |
Join 算子
| 算子 | 含义 | 适用场景 |
|---|---|---|
| HashJoin | 哈希 Join | 通用，两表都无合适索引 |
| IndexJoin | 索引嵌套 Join | 内表有索引，外表数据量小 |
| IndexHashJoin | 索引哈希 Join | 与 IndexJoin 类似 |
| MergeJoin | 归并 Join | 两边都有序（如有索引） |
| Apply | 子查询展开 | 相关子查询 |
3.3 执行计划示例分析
EXPLAIN
SELECT
u.name,
COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.age > 25
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;
+----------------------------------+----------+-----------+--------------------------+-----------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+--------------------------+-----------------------------------------------+
| TopN_15 | 10.00 | root | | Column#6:desc, offset:0, limit:10 |
| └─HashAgg_24 | 10.00 | root | | group by:users.name, count(orders.id) |
| └─TopN_26 | 100.00 | root | | Column#6:desc, offset:0, limit:10 |
| └─HashAgg_28 | 100.00 | root | | group by:users.name, count(orders.id) |
| └─Projection_30 | 8000.00 | root | | users.name, orders.id |
| └─HashJoin_32 | 8000.00 | root | | left outer join, inner:IndexReader_36 |
| ├─Selection_34 | 3333.33 | root | | gt(users.age, 25) |
| │ └─TableReader_35 | 10000.00 | root | | data:TableFullScan_33 |
| │ └─TableFullScan_33 | 10000.00 | cop[tikv] | table:u | keep order:false |
| └─IndexReader_36 | 10000.00 | root | | index:IndexFullScan_35 |
| └─IndexFullScan_35 | 10000.00 | cop[tikv] | table:o, idx:idx_user_id | keep order:false |
+----------------------------------+----------+-----------+--------------------------+-----------------------------------------------+
执行计划解读（从下往上）：
1. TableFullScan_33: 全表扫描 users 表
2. Selection_34: 过滤 age > 25
3. IndexFullScan_35: 全索引扫描 orders.idx_user_id
4. HashJoin_32: 将两表结果 Join
5. HashAgg_28/24: 按 name 聚合，计算 COUNT
6. TopN_15/26: 排序取前 10
优化空间分析：
- users 表走了
TableFullScan，如果在age上建索引，可能更高效 - 但由于
age > 25可能匹配大量行，优化器可能认为全表扫描更快
四、SQL 优化方法
4.1 索引优化
规则 1：为高频查询列建索引
-- 慢查询
SELECT * FROM orders WHERE user_id = 12345;
-- 如果 user_id 没有索引，会全表扫描
-- 优化
CREATE INDEX idx_user_id ON orders(user_id);
-- 现在走 IndexRangeScan
规则 2：联合索引的列顺序很重要
-- 场景: 经常按 status 和 created_at 查询
SELECT * FROM orders WHERE status = 1 AND created_at > '2024-01-01';
-- 差的设计（区分度低的列在前）
CREATE INDEX idx_status_date ON orders(status, created_at);
-- status 可能只有几个值（0,1,2），过滤效果差
-- 好的设计（区分度高的列在前，或按查询模式）
CREATE INDEX idx_date_status ON orders(created_at, status);
-- created_at 范围更精确，过滤效果更好
规则 3：避免过多的索引
每个索引都会：
- 占用额外存储空间
- 降低写入性能（每次 INSERT/UPDATE 都要更新索引）
- 增加优化器选择执行计划的复杂度
-- 查看所有索引信息（供分析参考）
-- 注意：TiDB 不在 information_schema.statistics 中记录索引使用频率，
-- 需通过 EXPLAIN 分析执行计划或使用 TiDB Dashboard 观察实际查询模式
SELECT
table_schema,
table_name,
index_name,
seq_in_index,
column_name
FROM information_schema.statistics
WHERE table_schema = 'your_database'
ORDER BY table_name, index_name;
4.2 查询重写
优化 1：避免 SELECT *
-- 差
SELECT * FROM users WHERE id = 1;
-- 回表获取所有列
-- 好
SELECT id, name, email FROM users WHERE id = 1;
-- 如果这些列都在索引中，可以覆盖索引，不回表
优化 2：避免在索引列上使用函数
-- 差（不能使用索引，在索引列上使用函数会导致全表扫描）
-- 假设 users 表有 created_at 列
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 好（可以使用范围扫描索引）
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
优化 3：避免隐式类型转换
-- 假设 phone 是 VARCHAR 类型
-- 差（数字与字符串比较，触发类型转换，索引失效）
SELECT * FROM users WHERE phone = 13800138000;
-- 好（类型一致，使用索引）
SELECT * FROM users WHERE phone = '13800138000';
优化 4：IN 列表不要太大
-- 差（IN 列表过大会影响优化器判断）
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 10000);
-- 好（分批处理或使用临时表）
-- 方案 A: 分批
SELECT * FROM users WHERE id IN (1, 2, ..., 1000); -- 第 1 批
SELECT * FROM users WHERE id IN (1001, 1002, ..., 2000); -- 第 2 批
-- 方案 B: 临时表 + JOIN
CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1), (2), ..., (10000);
SELECT u.* FROM users u JOIN tmp_ids t ON u.id = t.id;
4.3 JOIN 优化
规则 1：小表驱动大表
-- 假设 users 有 1000 行，orders 有 100 万行
-- 差的方式（如果优化器选择错误）
-- 假设 users 表有 status 列
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 1;
-- 好的方式（让优化器先过滤小表）
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;
-- TiDB 的优化器通常会自动 reorder Join，
-- 但可以通过 Hint 指导：
SELECT /*+ TIDB_INLJ(u) */ *
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;
-- TIDB_INLJ 强制使用 IndexJoin
规则 2：确保 Join 列有索引
-- orders.user_id 必须有索引
SHOW INDEX FROM orders WHERE Column_name = 'user_id';
-- 如果没有，创建
CREATE INDEX idx_user_id ON orders(user_id);
4.4 分页优化
传统分页在数据量大时效率很低：
-- 深分页慢：需要扫描前 1000000 行，然后丢弃
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;
优化方法一：延迟关联
-- 先通过索引获取 ID，再回表
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 1000000
) tmp ON o.id = tmp.id;
优化方法二：基于上次查询的最大 ID 翻页
-- 第 1 页
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 记住最后一条的 id，假设为 12345
-- 第 2 页
SELECT * FROM orders WHERE id > 12345 ORDER BY id LIMIT 20;
-- 不需要 OFFSET，直接从上次的位置开始
这种方法适合"无限滚动"场景，性能远优于 OFFSET。
五、Optimizer Hints
当优化器的选择不理想时，可以通过 Hint 指导：
5.1 索引 Hint
-- 强制使用特定索引
SELECT /*+ USE_INDEX(users, idx_name_age) */ *
FROM users WHERE name = 'Alice';
-- 忽略特定索引
SELECT /*+ IGNORE_INDEX(users, idx_name_age) */ *
FROM users WHERE name = 'Alice';
-- 强制全表扫描
SELECT /*+ USE_INDEX_MERGE(users, PRIMARY, idx_name_age) */ *
FROM users WHERE id = 1 OR name = 'Alice';
5.2 Join Hint
-- 强制 HashJoin
SELECT /*+ HASH_JOIN(u, o) */ *
FROM users u JOIN orders o ON u.id = o.user_id;
-- 强制 IndexJoin
SELECT /*+ INL_JOIN(u, o) */ *
FROM users u JOIN orders o ON u.id = o.user_id;
-- 强制 MergeJoin
SELECT /*+ MERGE_JOIN(u, o) */ *
FROM users u JOIN orders o ON u.id = o.user_id;
5.3 执行计划缓存
-- 绑定执行计划（类似 Oracle 的 SQL Plan Baseline）
CREATE BINDING FOR
SELECT * FROM users WHERE name = 'Alice'
USING
SELECT /*+ USE_INDEX(users, idx_name) */ * FROM users WHERE name = 'Alice';
-- 查看绑定
SHOW BINDINGS;
-- 删除绑定
DROP BINDING FOR SELECT * FROM users WHERE name = 'Alice';
六、统计信息与优化器
6.1 统计信息的作用
优化器依赖统计信息来选择最优执行计划：
-- 手动收集统计信息
ANALYZE TABLE users;
-- 收集特定索引的统计信息
ANALYZE TABLE users INDEX idx_name_age;
-- 查看统计信息
SHOW STATS_META WHERE table_name = 'users';
SHOW STATS_HISTOGRAMS WHERE table_name = 'users';
6.2 统计信息不准确的后果
-- 假设 users 表刚导入了大量数据，但统计信息未更新
-- 优化器可能认为表很小，选择了全表扫描
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
-- 实际表很大，全表扫描很慢
-- 解决: 更新统计信息
ANALYZE TABLE users;
6.3 自动分析
TiDB 默认会自动分析：
-- 查看自动分析配置
SHOW VARIABLES LIKE 'tidb_auto_analyze%';
-- 关闭自动分析（不推荐）
SET GLOBAL tidb_enable_auto_analyze = OFF;
-- 调整自动分析的时间窗口
SET GLOBAL tidb_auto_analyze_start_time = '02:00 +0800';
SET GLOBAL tidb_auto_analyze_end_time = '06:00 +0800';
七、慢查询分析实战
7.1 开启慢查询日志
-- 设置慢查询阈值（单位：纳秒）
-- TiDB 中 tidb_slow_log_threshold 的单位是纳秒
-- 300ms = 300000000 纳秒
SET GLOBAL tidb_slow_log_threshold = 300000000;
-- 查看慢查询
SELECT * FROM information_schema.slow_query
WHERE Time > NOW() - INTERVAL 1 HOUR
ORDER BY Query_time DESC
LIMIT 10;
7.2 分析慢查询
-- 查看具体的慢查询
SELECT
Query_time,
Query,
Digest,
Mem_max,
Stats
FROM information_schema.slow_query
ORDER BY Query_time DESC
LIMIT 10;
7.3 常见慢查询原因及对策
| 原因 | 特征 | 对策 |
|---|---|---|
| 全表扫描 | TableFullScan | 加索引 |
| 索引选择不当 | 用了低效索引 | ANALYZE TABLE / Hint |
| Join 方式不对 | HashJoin 大表 | INL_JOIN Hint |
| 深分页 | 大 OFFSET | 延迟关联 / 游标分页 |
| 数据分布倾斜 | 某 Region 特别慢 | 热点分析 / Split Region |
| 锁等待 | 悲观事务锁等待长 | 优化事务范围 / 改用乐观 |
八、小结
SQL 优化的核心思路：
- 理解执行计划：知道 SQL 实际是怎么执行的
- 合理利用索引：为高频查询列建索引，避免过多索引
- 查询重写：避免 SELECT *、函数调用、隐式转换
- 收集统计信息：确保优化器有准确的数据分布信息
- 必要时使用 Hint：引导优化器选择更好的执行计划