索引 #
一、索引概述 #
1.1 索引类型 #
text
CockroachDB 索引类型
├── 主键索引
│ └── 每个表必须有一个主键
│
├── 二级索引
│ ├── 唯一索引
│ └── 普通索引
│
├── 复合索引
│ └── 多列索引
│
├── 表达式索引
│ └── 基于表达式的索引
│
├── 部分索引
│ └── 带条件的索引
│
└── 倒排索引
└── JSONB和数组索引
1.2 索引结构 #
text
索引存储结构
┌─────────────────────────────────────────────────────────────┐
│ │
│ 主键索引: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Key: /Table/<table_id>/<primary_key> │ │
│ │ Value: 行数据 │ │
│ │ │ │
│ │ 数据按主键顺序存储 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 二级索引: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Key: /Table/<table_id>/<index_id>/<indexed_value>/<primary_key>│
│ │ Value: NULL (非唯一索引) 或 primary_key (唯一索引) │ │
│ │ │ │
│ │ 索引数据按索引值顺序存储 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
二、主键索引 #
2.1 主键设计 #
sql
-- 单列主键
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100),
email VARCHAR(100)
);
-- 复合主键
CREATE TABLE order_items (
order_id UUID,
item_id INT,
product_name VARCHAR(100),
quantity INT,
PRIMARY KEY (order_id, item_id)
);
-- 自增主键 (不推荐，可能导致热点)
CREATE TABLE users_seq (
id BIGINT PRIMARY KEY DEFAULT unique_rowid(),
name VARCHAR(100)
);
-- 推荐使用UUID
CREATE TABLE users_uuid (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100)
);
2.2 主键选择建议 #
text
主键选择建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 推荐: │
│ ├── UUID: 分散写入，避免热点 │
│ ├── 复合主键: 符合业务逻辑 │
│ └── 有序UUID: 结合顺序性和分散性 │
│ │
│ 不推荐: │
│ ├── 自增ID: 导致写入热点 │
│ ├── 时间戳: 时间相近的数据集中 │
│ └── 无主键: 使用隐式rowid │
│ │
│ 注意事项: │
│ ├── 主键不可修改 │
│ ├── 主键应尽量短 │
│ └── 主键应避免频繁更新 │
│ │
└─────────────────────────────────────────────────────────────┘
三、二级索引 #
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 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID,
order_no VARCHAR(50),
amount DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT NOW(),
INDEX idx_user_id (user_id),
UNIQUE INDEX idx_order_no (order_no),
INDEX idx_user_status (user_id, status)
);
3.2 删除索引 #
sql
-- 删除索引
DROP INDEX idx_name;
-- 使用表名删除
DROP INDEX users@idx_name;
-- 使用 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';
-- 查看索引大小
SELECT
table_name,
index_name,
pg_size_pretty(pg_relation_size(index_name::regclass)) AS size
FROM pg_indexes
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 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
-- 创建表达式索引
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((EXTRACT(YEAR FROM created_at)));
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;
-- 计算列索引
CREATE INDEX idx_full_name ON users((first_name || ' ' || last_name));
SELECT * FROM users WHERE first_name || ' ' || last_name = 'John Doe';
5.2 部分索引 #
sql
-- 创建部分索引
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
-- 使用部分索引
SELECT * FROM users WHERE email = 'test@example.com' AND status = 'active';
-- 部分索引示例
CREATE INDEX idx_pending_orders ON orders(user_id)
WHERE status = 'pending';
CREATE UNIQUE INDEX idx_unique_email ON users(email)
WHERE deleted_at IS NULL;
5.3 倒排索引 #
sql
-- JSONB索引
CREATE INDEX idx_data_gin ON users USING GIN (data);
-- JSONB路径索引
CREATE INDEX idx_data_name ON users((data->>'name'));
-- 数组索引
CREATE TABLE tags (
id UUID PRIMARY KEY,
tags STRING[]
);
CREATE INDEX idx_tags_gin ON tags USING GIN (tags);
-- 查询
SELECT * FROM tags WHERE tags @> ARRAY['tag1'];
六、索引优化 #
6.1 覆盖索引 #
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 表示使用覆盖索引
6.2 索引选择 #
sql
-- 使用 EXPLAIN 分析索引使用
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- 强制使用索引
SELECT * FROM orders@idx_user_id WHERE user_id = 1;
-- 使用提示
SELECT /*+ Index(orders idx_user_id) */ *
FROM orders
WHERE user_id = 1;
-- 忽略索引
SELECT /*+ NoIndex(orders) */ *
FROM orders
WHERE user_id = 1;
6.3 索引下推 #
sql
-- 索引下推: 过滤条件在索引扫描时执行
CREATE INDEX idx_user_amount ON orders(user_id, amount);
-- 索引下推示例
SELECT * FROM orders
WHERE user_id = 1 AND amount > 100;
-- CockroachDB 会将 amount > 100 下推到索引扫描
-- 减少回表次数
七、索引最佳实践 #
7.1 创建索引建议 #
text
索引创建建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 适合创建索引: │
│ ├── WHERE 条件频繁使用的列 │
│ ├── JOIN 连接的列 │
│ ├── ORDER BY / GROUP BY 的列 │
│ ├── 选择性高的列 (唯一值多) │
│ └── 外键列 │
│ │
│ 不适合创建索引: │
│ ├── 选择性低的列 (如性别、状态) │
│ ├── 频繁更新的列 │
│ ├── 数据量小的表 │
│ └── 很少使用的列 │
│ │
│ 注意事项: │
│ ├── 索引数量不宜过多 │
│ ├── 索引会增加写入开销 │
│ └── 定期检查未使用的索引 │
│ │
└─────────────────────────────────────────────────────────────┘
7.2 避免索引失效 #
sql
-- 避免在索引列上使用函数
-- ✗ 不推荐
SELECT * FROM users WHERE UPPER(name) = 'ALICE';
-- ✓ 推荐
SELECT * FROM users WHERE name = 'Alice';
-- 避免隐式类型转换
-- ✗ 不推荐 (user_id 是 UUID)
SELECT * FROM orders WHERE user_id = '1';
-- ✓ 推荐
SELECT * FROM orders WHERE user_id = 'uuid-string';
-- 避免 OR 导致索引失效
-- ✗ 不推荐
SELECT * FROM orders WHERE user_id = 'uuid-1' OR amount > 100;
-- ✓ 使用 UNION
SELECT * FROM orders WHERE user_id = 'uuid-1'
UNION
SELECT * FROM orders WHERE amount > 100;
-- 避免 NOT LIKE
-- ✗ 不推荐
SELECT * FROM users WHERE name NOT LIKE 'A%';
-- ✓ 使用其他方式
SELECT * FROM users WHERE name >= 'B' OR name < 'A';
7.3 索引监控 #
sql
-- 查看索引使用统计
SELECT
table_schema,
table_name,
index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- 查找未使用的索引
SELECT
schemaname,
tablename,
indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%pkey%';
-- 查看索引大小
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
八、总结 #
索引要点：
| 类型 | 说明 |
|---|---|
| 主键索引 | 每表必须，数据按主键组织 |
| 二级索引 | 辅助索引，需要回表 |
| 复合索引 | 多列索引，遵循最左前缀 |
| 覆盖索引 | 查询不需要回表 |
| 表达式索引 | 基于表达式的索引 |
| 部分索引 | 带条件的索引 |
下一步，让我们学习视图！
最后更新：2026-03-27