索引 #
一、索引概述 #
1.1 索引类型 #
text
TiDB 索引类型
├── 聚簇索引
│ └── 主键索引，数据按主键组织
│
├── 二级索引
│ ├── 唯一索引
│ └── 普通索引
│
├── 复合索引
│ └── 多列索引
│
├── 表达式索引
│ └── 基于表达式的索引
│
└── 全文索引
└── 全文搜索索引
1.2 索引结构 #
text
索引存储结构
┌─────────────────────────────────────────────────────────────┐
│ │
│ 聚簇索引 (主键): │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Key: t{table_id}_r{row_id} │ │
│ │ Value: 行数据 │ │
│ │ │ │
│ │ 数据按主键顺序存储 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 二级索引: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Key: t{table_id}_i{index_id}{indexed_value}{row_id}│ │
│ │ Value: NULL (非唯一索引) 或 row_id (唯一索引) │ │
│ │ │ │
│ │ 索引数据按索引值顺序存储 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
二、聚簇索引 #
2.1 聚簇索引特点 #
sql
-- 聚簇索引 = 主键索引
-- 数据按主键组织存储
CREATE TABLE users (
id BIGINT PRIMARY KEY, -- 聚簇索引
name VARCHAR(100),
email VARCHAR(100)
);
-- 查询使用主键
SELECT * FROM users WHERE id = 1; -- 直接定位，最快
-- 主键范围查询
SELECT * FROM users WHERE id BETWEEN 1 AND 100; -- 顺序扫描
2.2 非聚簇索引表 #
sql
-- 非聚簇索引表 (使用隐式 _tidb_rowid)
CREATE TABLE users_non_clustered (
id BIGINT PRIMARY KEY NONCLUSTERED,
name VARCHAR(100),
email VARCHAR(100)
);
-- 或使用无主键表
CREATE TABLE users_no_pk (
name VARCHAR(100),
email VARCHAR(100)
); -- 使用隐式 _tidb_rowid 作为聚簇索引
-- 查看表是否使用聚簇索引
SELECT
TABLE_NAME,
TIDB_PK_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';
三、二级索引 #
3.1 创建索引 #
sql
-- 创建普通索引
CREATE INDEX idx_name ON users(name);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 创建复合索引
CREATE INDEX idx_name_email ON users(name, email);
-- 建表时创建索引
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT,
order_no VARCHAR(50),
amount DECIMAL(10, 2),
created_at TIMESTAMP,
INDEX idx_user_id (user_id),
UNIQUE INDEX idx_order_no (order_no),
INDEX idx_user_created (user_id, created_at)
);
3.2 删除索引 #
sql
-- 删除索引
DROP INDEX idx_name ON users;
-- 使用 ALTER TABLE
ALTER TABLE users DROP INDEX idx_name;
3.3 查看索引 #
sql
-- 查看表索引
SHOW INDEX FROM users;
-- 从系统表查询
SELECT
INDEX_NAME,
COLUMN_NAME,
NON_UNIQUE,
SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'users';
四、复合索引 #
4.1 最左前缀原则 #
sql
-- 复合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
-- 使用索引的查询
SELECT * FROM orders WHERE user_id = 1; -- ✓ 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid'; -- ✓ 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01'; -- ✓ 使用索引
-- 不使用索引的查询
SELECT * FROM orders WHERE status = 'paid'; -- ✗ 不使用索引
SELECT * FROM orders WHERE created_at > '2024-01-01'; -- ✗ 不使用索引
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2024-01-01'; -- ✗ 不使用索引
-- 部分使用索引
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01'; -- 只使用 user_id 部分
4.2 索引列顺序 #
text
索引列顺序建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 等值条件列在前 │
│ ├── WHERE col = 'value' 放前面 │
│ └── 范围条件列放后面 │
│ │
│ 2. 选择性高的列在前 │
│ ├── 唯一值多的列放前面 │
│ └── 区分度高，过滤效果好 │
│ │
│ 3. 排序和分组列 │
│ ├── ORDER BY 列可考虑放入索引 │
│ └── GROUP BY 列可考虑放入索引 │
│ │
│ 示例: │
│ SELECT * FROM orders │
│ WHERE user_id = 1 │
│ AND status = 'paid' │
│ ORDER BY created_at DESC; │
│ │
│ 推荐索引: (user_id, status, created_at) │
│ │
└─────────────────────────────────────────────────────────────┘
五、索引优化 #
5.1 索引选择 #
sql
-- 使用 EXPLAIN 分析索引使用
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- 强制使用索引
SELECT /*+ USE_INDEX(orders idx_user_id) */
* FROM orders WHERE user_id = 1;
-- 强制使用特定索引
SELECT /*+ FORCE_INDEX(orders idx_user_id) */
* FROM orders WHERE user_id = 1;
-- 忽略索引
SELECT /*+ IGNORE_INDEX(orders idx_user_id) */
* FROM orders WHERE user_id = 1;
5.2 覆盖索引 #
sql
-- 覆盖索引: 查询只需要索引列，不需要回表
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);
-- 使用覆盖索引
SELECT user_id, status, amount
FROM orders
WHERE user_id = 1; -- 不需要回表，更快
-- 查看执行计划
EXPLAIN SELECT user_id, status, amount
FROM orders
WHERE user_id = 1;
-- IndexReader 表示使用覆盖索引
5.3 索引下推 #
sql
-- 索引下推: 过滤条件在索引扫描时执行
CREATE INDEX idx_user_amount ON orders(user_id, amount);
-- 索引下推示例
SELECT * FROM orders
WHERE user_id = 1 AND amount > 100;
-- TiDB 会将 amount > 100 下推到索引扫描
-- 减少回表次数
六、表达式索引 #
6.1 创建表达式索引 #
sql
-- 创建表达式索引
CREATE INDEX idx_lower_email ON users((LOWER(email)));
-- 使用表达式索引
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 函数索引
CREATE INDEX idx_year_created ON orders((YEAR(created_at)));
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
6.2 注意事项 #
sql
-- 表达式索引维护成本高
-- 只有频繁使用的表达式才创建
-- 查看表达式索引
SHOW INDEX FROM users WHERE Expression IS NOT NULL;
七、全文索引 #
7.1 创建全文索引 #
sql
-- 创建全文索引
CREATE TABLE articles (
id BIGINT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_title_content (title, content)
);
-- 或在现有表上添加
CREATE FULLTEXT INDEX ft_content ON articles(content);
7.2 使用全文索引 #
sql
-- 全文搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database');
-- 自然语言模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('distributed database' IN NATURAL LANGUAGE MODE);
-- 布尔模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+distributed -mysql' IN BOOLEAN MODE);
八、索引最佳实践 #
8.1 创建索引建议 #
text
索引创建建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 适合创建索引: │
│ ├── WHERE 条件频繁使用的列 │
│ ├── JOIN 连接的列 │
│ ├── ORDER BY / GROUP BY 的列 │
│ ├── 选择性高的列 (唯一值多) │
│ └── 外键列 │
│ │
│ 不适合创建索引: │
│ ├── 选择性低的列 (如性别、状态) │
│ ├── 频繁更新的列 │
│ ├── 数据量小的表 │
│ └── 很少使用的列 │
│ │
└─────────────────────────────────────────────────────────────┘
8.2 避免索引失效 #
sql
-- 避免在索引列上使用函数
-- ✗ 不推荐
SELECT * FROM users WHERE UPPER(name) = 'ALICE';
-- ✓ 推荐
SELECT * FROM users WHERE name = 'Alice';
-- 避免隐式类型转换
-- ✗ 不推荐 (user_id 是 BIGINT)
SELECT * FROM orders WHERE user_id = '1';
-- ✓ 推荐
SELECT * FROM orders WHERE user_id = 1;
-- 避免使用 NOT LIKE
-- ✗ 不推荐
SELECT * FROM users WHERE name NOT LIKE 'A%';
-- ✓ 使用其他方式
SELECT * FROM users WHERE name >= 'B' OR name < 'A';
-- 避免 OR 导致索引失效
-- ✗ 不推荐
SELECT * FROM orders WHERE user_id = 1 OR amount > 100;
-- ✓ 使用 UNION
SELECT * FROM orders WHERE user_id = 1
UNION
SELECT * FROM orders WHERE amount > 100;
8.3 索引监控 #
sql
-- 查看索引使用统计
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
ROWS_READ,
ROWS_INSERTED
FROM INFORMATION_SCHEMA.TIDB_INDEX_USAGE;
-- 查找未使用的索引
SELECT
s.TABLE_SCHEMA,
s.TABLE_NAME,
s.INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT JOIN INFORMATION_SCHEMA.TIDB_INDEX_USAGE u
ON s.TABLE_SCHEMA = u.TABLE_SCHEMA
AND s.TABLE_NAME = u.TABLE_NAME
AND s.INDEX_NAME = u.INDEX_NAME
WHERE s.NON_UNIQUE = 1 -- 非唯一索引
AND u.ROWS_READ IS NULL; -- 未使用
九、总结 #
索引要点：
| 类型 | 说明 |
|---|---|
| 聚簇索引 | 主键索引，数据按主键组织 |
| 二级索引 | 辅助索引，需要回表 |
| 复合索引 | 多列索引，遵循最左前缀 |
| 覆盖索引 | 查询不需要回表 |
| 表达式索引 | 基于表达式的索引 |
下一步，让我们学习视图！
最后更新：2026-03-27