一、索引选择性是什么？
想象一下你在图书馆找一本书。如果图书馆把所有书按照书名首字母排序（比如A开头的放一起，B开头的放另一区），你找《PostgreSQL指南》时，直接去"P"区翻找，效率肯定比从第一排书架开始挨个找快得多。数据库索引也是类似的原理，而"选择性"就是衡量这个"字母分区"到底有多靠谱的指标。
选择性（Selectivity）定义为：索引中不同值的数量与表中总行数的比值。公式很简单：
-- PostgreSQL示例：计算某列的选择性
SELECT
COUNT(DISTINCT user_id) * 1.0 / COUNT(*) AS selectivity
FROM users;
如果结果是0.01，意味着平均每100行才有1个不同的user_id，这种低选择性列建索引效果通常不好；如果是0.8，则说明该列值分布分散，索引效果会很好。
二、如何计算选择性？实战演示
场景1：基础字段选择性分析
假设我们有一个电商订单表，想评估product_id和order_status哪个更适合建索引：
-- 创建示例表（PostgreSQL语法）
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
product_id INT,
order_status VARCHAR(20), -- 可能值：'pending','paid','shipped','delivered'
created_at TIMESTAMP
);
-- 插入10万条测试数据（假设product_id有5000种，status有4种）
INSERT INTO orders (product_id, order_status, created_at)
SELECT
(random() * 5000)::INT,
(CASE WHEN random() < 0.25 THEN 'pending'
WHEN random() < 0.5 THEN 'paid'
WHEN random() < 0.75 THEN 'shipped'
ELSE 'delivered' END),
NOW() - (random() * 365 || ' days')::INTERVAL
FROM generate_series(1, 100000);
-- 计算选择性
SELECT
COUNT(DISTINCT product_id) * 1.0 / COUNT(*) AS product_selectivity,
COUNT(DISTINCT order_status) * 1.0 / COUNT(*) AS status_selectivity
FROM orders;
执行结果可能类似：
product_selectivity | status_selectivity
--------------------+-------------------
0.05 | 0.00004
显然product_id的选择性（0.05）远高于order_status（0.00004），前者更适合建索引。
场景2：复合索引选择性计算
当需要评估多列组合的选择性时：
-- 添加customer_id列并更新数据
ALTER TABLE orders ADD COLUMN customer_id INT;
UPDATE orders SET customer_id = (random() * 10000)::INT;
-- 计算复合选择性
SELECT
COUNT(DISTINCT (product_id, customer_id)) * 1.0 / COUNT(*) AS combined_selectivity
FROM orders;
如果结果是0.04，说明(product_id, customer_id)组合比单列选择性更高，这种复合索引对查询如WHERE product_id=123 AND customer_id=456会非常有效。
三、高级评估技巧
1. 使用pg_stats系统表
PostgreSQL自带统计信息收集器，通过pg_stats可以查看预计算的统计信息：
-- 查看列的统计信息
SELECT
attname AS column_name,
n_distinct,
most_common_vals,
most_common_freqs
FROM pg_stats
WHERE tablename = 'orders';
输出示例：
column_name | n_distinct | most_common_vals | most_common_freqs
------------+------------+-------------------------------------+---------------------
order_status | 4 | {pending,paid,shipped,delivered} | {0.25,0.25,0.25,0.25}
n_distinct显示不同值的数量（负数表示占总行数的比例），结合most_common_vals可以判断数据分布是否均匀。
2. 索引使用情况监控
即使选择性高，也要确认索引是否被实际使用：
-- 启用跟踪（需要超级用户权限）
CREATE EXTENSION pg_stat_statements;
-- 查看索引使用情况
SELECT
indexrelname AS index_name,
idx_scan AS scans
FROM pg_stat_user_indexes
WHERE relname = 'orders';
如果scans值长期为0，说明该索引可能是个摆设。
四、优化实战与避坑指南
优化案例：改善慢查询
假设我们有一个执行缓慢的查询：
-- 原始慢查询
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_status = 'shipped'
AND created_at > NOW() - INTERVAL '30 days';
通过EXPLAIN发现进行了全表扫描。虽然order_status选择性低，但结合时间条件后数据量大幅减少：
-- 计算组合条件的选择性
SELECT
COUNT(*) * 1.0 / (SELECT COUNT(*) FROM orders) AS selectivity
FROM orders
WHERE order_status = 'shipped'
AND created_at > NOW() - INTERVAL '30 days';
如果结果是0.01（即筛选出1%数据），可以创建复合索引：
CREATE INDEX idx_orders_status_created ON orders(order_status, created_at);
常见误区
- 过度索引：每个索引都会增加写操作开销。一个修改频繁的表有10个索引，每次INSERT都要更新10个索引树。
- 盲目相信高选择性：如UUID选择性极高，但对范围查询无效，不如时间戳+ID的复合索引实用。
- 忽略统计信息更新：大量数据变更后记得运行
ANALYZE table_name更新统计信息。
五、总结
索引选择性就像数据库的"导航精准度"——高选择性意味着导航能直接带你到准确位置，低选择性则像告诉你"书在某个200平米的大房间里"。通过本文的实战方法，你可以：
- 用简单SQL量化索引价值
- 通过系统表获取关键统计信息
- 避免常见的索引设计陷阱
- 制定符合业务场景的索引策略
记住，没有绝对完美的索引方案。一个优秀的DBA应该像老练的侦探，通过查询模式、数据分布和系统指标，不断调整索引这把"双刃剑"。
评论