数据库（二）：索引与查询规划——数据库如何找到你的数据

1

SELECT * FROM users WHERE email = 'alice@example.com';

1
2
3
4
5
6
7

Root Node: [charlie@... | mike@... | zara@...]
                |              |           |
          Child < charlie  charlie-mike  mike-zara   > zara
                |
    [alice@... | bob@...]  <-- leaf node
         |
    Pointer to heap page 4721, offset 23

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 单列索引
CREATE INDEX idx_users_email ON users (email);

-- 唯一索引（同时强制唯一性约束）
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- 查看现有索引（PostgreSQL）
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- 查看现有索引（MySQL）
SHOW INDEX FROM users;

1
2
3
4

-- 范围查询：查找最近 7 天的订单
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at;

1
2

-- PostgreSQL：显式创建哈希索引
CREATE INDEX idx_users_email_hash ON users USING hash (email);

1

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 此查询高效利用复合索引
SELECT * FROM orders
WHERE user_id = 42 AND status = 'completed';

-- 此查询可利用索引（最左前缀）
SELECT * FROM orders
WHERE user_id = 42;

-- 此查询**无法**利用该复合索引
-- 需单独为 (status) 创建索引
SELECT * FROM orders
WHERE status = 'pending';

1
2
3
4
5
6
7
8
9

-- 创建覆盖索引
CREATE INDEX idx_orders_covering ON orders (user_id, status)
INCLUDE (created_at, order_id);

-- 此查询可完全由索引满足
-- 无需堆获取 → “仅索引扫描”
SELECT order_id, status, created_at
FROM orders
WHERE user_id = 42 AND status = 'completed';

1
2
3
4
5
6
7

-- MySQL 覆盖索引
CREATE INDEX idx_orders_covering ON orders (user_id, status, created_at, order_id);

-- 检查是否启用仅索引扫描（MySQL）
EXPLAIN SELECT order_id, status, created_at
FROM orders WHERE user_id = 42 AND status = 'completed';
-- 查看 Extra 列是否显示 "Using index"

1
2
3
4
5
6
7
8

EXPLAIN ANALYZE
SELECT u.full_name, COUNT(*) AS order_count
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.full_name
ORDER BY order_count DESC
LIMIT 10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

 Limit  (cost=1845.23..1845.26 rows=10 width=40) (actual time=12.456..12.461 rows=10 loops=1)
   ->  Sort  (cost=1845.23..1857.45 rows=4889 width=40) (actual time=12.454..12.458 rows=10 loops=1)
         Sort Key: (count(*)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate  (cost=1723.56..1772.45 rows=4889 width=40) (actual time=11.234..11.890 rows=4889 loops=1)
               Group Key: u.full_name
               Batches: 1  Memory Usage: 913kB
               ->  Hash Join  (cost=234.67..1601.23 rows=24450 width=32) (actual time=2.345..8.901 rows=24450 loops=1)
                     Hash Cond: (o.user_id = u.user_id)
                     ->  Seq Scan on orders o  (cost=0.00..1156.00 rows=24450 width=4) (actual time=0.012..4.567 rows=24450 loops=1)
                           Filter: ((status)::text = 'completed'::text)
                           Rows Removed by Filter: 25550
                     ->  Hash  (cost=159.00..159.00 rows=10000 width=36) (actual time=2.123..2.123 rows=10000 loops=1)
                           Buckets: 16384  Batches: 1  Memory Usage: 641kB
                           ->  Seq Scan on users u  (cost=0.00..159.00 rows=10000 width=36) (actual time=0.008..0.987 rows=10000 loops=1)
 Planning Time: 0.234 ms
 Execution Time: 12.567 ms

1
2
3
4
5
6
7
8

EXPLAIN
SELECT u.full_name, COUNT(*) AS order_count
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.full_name
ORDER BY order_count DESC
LIMIT 10;

1
2
3
4
5
6

+----+-------------+-------+------+------------------+---------+---------+------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys    | key     | key_len | ref              | rows  | Extra                                        |
+----+-------------+-------+------+------------------+---------+---------+------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | o     | ref  | idx_order_status | idx_... | 82      | const            | 24450 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | u     | ref  | PRIMARY          | PRIMARY | 4       | mydb.o.user_id   |     1 | NULL                                         |
+----+-------------+-------+------+------------------+---------+---------+------------------+-------+----------------------------------------------+

1
2
3
4

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > '2023-01-01';

1
2
3
4
5

 Seq Scan on orders  (cost=0.00..2456.00 rows=245 width=48) (actual time=0.034..18.567 rows=234 loops=1)
   Filter: (((status)::text = 'pending'::text) AND (created_at > '2023-01-01'))
   Rows Removed by Filter: 49766
 Planning Time: 0.089 ms
 Execution Time: 18.623 ms

1

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

1
2
3
4

 Index Scan using idx_orders_status_created on orders  (cost=0.29..12.45 rows=245 width=48) (actual time=0.023..0.189 rows=234 loops=1)
   Index Cond: (((status)::text = 'pending'::text) AND (created_at > '2023-01-01'))
 Planning Time: 0.102 ms
 Execution Time: 0.234 ms

1
2
3
4
5
6
7
8
9

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

1
2
3
4
5
6
7

-- 仅索引未删除的用户（若 99% 用户未被删除）
CREATE INDEX idx_users_active ON users (email)
WHERE is_deleted = FALSE;

-- 仅索引待处理订单（若大部分订单已完成）
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

1
2
3
4
5
6
7

-- 此查询可使用 idx_orders_pending
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2023-12-01';

-- 此查询**不会**使用 idx_orders_pending
SELECT * FROM orders
WHERE status = 'completed' AND created_at > '2023-12-01';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 为大小写不敏感查找建立小写 email 索引
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 查询必须使用相同表达式
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- 为时间戳提取年份建立索引
CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM created_at));

-- 为 JSONB 字段建立索引（PostgreSQL）
CREATE INDEX idx_users_metadata_country
ON users ((metadata->>'country'));

1
2
3
4
5
6

-- 此查询**无法**使用 email 列上的普通索引
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- 数据库看到的是 LOWER(email)，而非 email —— 二者语义不同

-- 此查询**可以**使用 email 列上的普通索引
SELECT * FROM users WHERE email = 'alice@example.com';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- GIN 索引用于全文检索
CREATE INDEX idx_products_search ON products
USING gin (to_tsvector('english', name || ' ' || description));

-- 全文检索查询
SELECT name, ts_rank(to_tsvector('english', name || ' ' || description),
                     plainto_tsquery('english', 'wireless keyboard')) AS rank
FROM products
WHERE to_tsvector('english', name || ' ' || description)
      @@ plainto_tsquery('english', 'wireless keyboard')
ORDER BY rank DESC;

-- GIN 索引用于 JSONB 包含查询
CREATE INDEX idx_users_metadata ON users USING gin (metadata jsonb_path_ops);

SELECT * FROM users WHERE metadata @> '{"country": "US"}';

-- GiST 索引用于几何/区间数据
CREATE INDEX idx_events_timerange ON events USING gist (time_range);

1
2
3

块范围 1 (第 0-127 页):   min_date = 2024-01-01, max_date = 2024-01-15
块范围 2 (第 128-255 页): min_date = 2024-01-15, max_date = 2024-01-31
块范围 3 (第 256-383 页): min_date = 2024-02-01, max_date = 2024-02-14

1
2
3
4
5
6
7

-- 创建 BRIN 索引（订单大致按时间顺序插入）
CREATE INDEX idx_orders_created_brin ON orders
USING BRIN (created_at) WITH (pages_per_range = 64);

-- 对比大小
SELECT pg_size_pretty(pg_relation_size('idx_orders_created_brin')) AS brin_size;
-- 结果: 48 kB（相比等效 B-tree 在 1 亿行上需要 2.1 GB）

1
2
3

SELECT correlation FROM pg_stats
WHERE tablename = 'orders' AND attname = 'created_at';
-- 结果: 0.98 → 非常适合 BRIN。低于 0.5 → 不要用

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE EXTENSION bloom;

-- Bloom 索引：多列等值查询的高效方案
CREATE INDEX idx_logs_bloom ON logs USING bloom (
    user_id, session_id, action, status_code
) WITH (length=80, col1=2, col2=2, col3=4, col4=2);

-- 这单个索引加速任意列组合：
SELECT * FROM logs WHERE user_id = 42 AND status_code = 500;
SELECT * FROM logs WHERE action = 'login' AND session_id = 'abc';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE EXTENSION pgstattuple;

SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

-- 详细膨胀估算
SELECT * FROM pgstatindex('idx_orders_user_id');
-- avg_leaf_density < 50% → 严重膨胀

1
2
3
4
5

-- 重建膨胀的索引（短暂锁表）
REINDEX INDEX idx_orders_user_id;

-- CONCURRENTLY：不锁表重建（PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

1
2
3
4
5
6
7
8
9

-- 查找自上次统计重置以来未被扫描的索引
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS scans_since_reset
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND pg_relation_size(indexrelid) > 1048576  -- > 1MB
ORDER BY pg_relation_size(indexrelid) DESC;

1
2
3
4
5
6
7
8

1. 对慢查询运行 EXPLAIN ANALYZE
2. 寻找带有高 "Rows Removed by Filter" 的 Seq Scan
3. 识别 WHERE/JOIN/ORDER BY 中缺失索引的列
4. 检查这些列的基数
5. 创建最具选择性的复合索引
6. 再次运行 EXPLAIN ANALYZE 验证改进效果
7. 监控 pg_stat_user_indexes 以确认实际使用率
8. 30 天后删除未使用的索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 步骤 1：慢查询
EXPLAIN ANALYZE
SELECT p.name, SUM(oi.quantity) AS total_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.created_at >= '2023-11-01'
  AND o.status = 'completed'
GROUP BY p.name
ORDER BY total_sold DESC
LIMIT 10;
-- Execution Time: 234.567 ms

-- 步骤 2–4：orders 表出现 Seq Scan，过滤掉 80% 的行
-- 需要在 (status, created_at) 上建索引

-- 步骤 5：创建索引
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- 步骤 6：验证
EXPLAIN ANALYZE
-- ... 同一查询 ...
-- Execution Time: 3.456 ms  （68 倍提速）