一、为什么需要了解索引选择性
想象你走进一个超大的图书馆，里面有上百万本书。如果没有图书分类系统，你要找一本特定的书可能需要一整天。数据库里的索引就像这个分类系统，而选择性就是衡量这个分类系统有多好用的指标。
选择性高的索引，就像精确到具体书架编号的分类标签，能帮你快速定位。选择性低的索引，则像是只告诉你"在文学区"这样宽泛的提示，帮助有限。
在PostgreSQL中，我们可以通过pg_stats这个系统视图来量化评估索引的选择性，从而判断这个索引是否值得创建。
二、认识pg_stats视图
pg_stats是PostgreSQL自带的一个统计信息视图，它记录了表中列的数值分布情况。这些信息不是实时更新的，而是通过ANALYZE命令收集的。
这个视图包含几个关键字段：
- tablename：表名
- attname：列名
- n_distinct：该列不同值的数量
- most_common_vals：最常见的值列表
- most_common_freqs：这些最常见值出现的频率
举个例子，我们创建一个简单的用户表并插入一些数据：
-- PostgreSQL示例
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
age INT,
is_active BOOLEAN DEFAULT true
);
-- 插入测试数据
INSERT INTO users (username, age, is_active)
SELECT
'user_' || i,
(random() * 70 + 10)::INT,
CASE WHEN random() > 0.2 THEN true ELSE false END
FROM generate_series(1, 10000) AS i;
-- 收集统计信息
ANALYZE users;
三、计算索引选择性的方法
3.1 基本选择性计算
选择性计算公式很简单： 选择性 = 不同值的数量 / 总行数
我们可以通过pg_stats获取这些数据：
-- PostgreSQL示例
SELECT
attname AS column_name,
n_distinct,
(n_distinct / reltuples) AS selectivity
FROM pg_stats, pg_class
WHERE
pg_stats.tablename = 'users' AND
pg_class.relname = 'users';
3.2 处理特殊值
有些列的值分布不均匀，比如我们的is_active字段。让我们看看它的统计信息：
-- PostgreSQL示例
SELECT
attname,
most_common_vals,
most_common_freqs
FROM pg_stats
WHERE
tablename = 'users' AND
attname = 'is_active';
结果可能显示80%的值是true，20%是false。这种情况下，在is_active上建索引对查找false值有帮助，但对true值帮助不大。
3.3 多列索引的选择性
对于多列索引，选择性计算需要考虑列的组合情况。例如：
-- PostgreSQL示例
-- 创建一个多列索引
CREATE INDEX idx_users_age_active ON users(age, is_active);
-- 需要手动收集扩展统计信息
CREATE STATISTICS users_age_active_stats (dependencies) ON age, is_active FROM users;
ANALYZE users;
-- 查看组合选择性
SELECT
stxname,
stxkeys,
stxdependencies
FROM pg_statistic_ext
WHERE stxrelid = 'users'::regclass;
四、实际应用案例分析
4.1 案例一：高选择性列
username列通常具有很高的选择性，因为用户名一般都是唯一的：
-- PostgreSQL示例
-- 查看username的统计信息
SELECT
attname,
n_distinct,
(n_distinct / reltuples) AS selectivity
FROM pg_stats, pg_class
WHERE
pg_stats.tablename = 'users' AND
pg_class.relname = 'users' AND
attname = 'username';
如果结果显示选择性接近1，说明这个列非常适合建索引。
4.2 案例二：低选择性列
像性别这样的列选择性通常很低：
-- PostgreSQL示例
-- 添加性别列并更新数据
ALTER TABLE users ADD COLUMN gender CHAR(1);
UPDATE users SET gender = CASE WHEN random() > 0.5 THEN 'M' ELSE 'F' END;
ANALYZE users;
-- 查看性别列选择性
SELECT
attname,
n_distinct,
(n_distinct / reltuples) AS selectivity
FROM pg_stats, pg_class
WHERE
pg_stats.tablename = 'users' AND
pg_class.relname = 'users' AND
attname = 'gender';
如果选择性低于0.1，可能不值得单独建索引，除非经常与其他列一起查询。
五、索引选择性的实际应用建议
- 选择性大于0.9的列几乎总是值得建索引
- 选择性在0.1-0.9之间的列，需要结合查询频率考虑
- 选择性低于0.1的列通常不适合单独建索引
- 对于多列查询，考虑创建复合索引而不是多个单列索引
- 定期运行ANALYZE更新统计信息，特别是在大量数据变更后
六、性能优化实战
让我们看一个完整的优化示例：
-- PostgreSQL示例
-- 创建一个订单表
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
amount DECIMAL(10,2),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT now()
);
-- 插入50万条测试数据
INSERT INTO orders (user_id, amount, status, created_at)
SELECT
(random() * 9999 + 1)::INT,
(random() * 1000)::DECIMAL(10,2),
CASE (random() * 4)::INT
WHEN 0 THEN 'pending'
WHEN 1 THEN 'processing'
WHEN 2 THEN 'shipped'
ELSE 'completed'
END,
now() - (random() * 365)::INT * INTERVAL '1 day'
FROM generate_series(1, 500000) AS i;
-- 收集统计信息
ANALYZE orders;
-- 检查各列的选择性
SELECT
attname,
n_distinct,
(n_distinct / reltuples) AS selectivity
FROM pg_stats, pg_class
WHERE
pg_stats.tablename = 'orders' AND
pg_class.relname = 'orders';
-- 根据查询模式创建合适的索引
-- 高频查询：查找特定用户的状态为pending的订单
-- 先检查user_id和status的选择性
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending';
根据选择性分析，我们可能会决定创建一个(user_id, status)的复合索引。
七、常见问题解答
Q: 统计信息多久会自动更新一次？ A: PostgreSQL有自动ANALYZE进程，但大量数据加载后最好手动运行一次。
Q: 为什么我的索引选择性很高但查询还是慢？ A: 可能因为查询没有使用索引，检查EXPLAIN输出确认索引是否被使用。
Q: 如何强制使用某个索引？ A: 可以使用SET enable_seqscan = off临时禁用全表扫描，但不建议在生产环境长期使用。
八、总结
通过pg_stats视图计算索引选择性，我们可以科学地评估索引的价值，避免盲目创建无效索引。记住以下几点：
- 选择性是衡量索引价值的重要指标
- 使用pg_stats获取统计信息
- 考虑查询模式而不仅是单个列的选择性
- 复合索引的选择性计算更复杂但往往更有效
- 定期更新统计信息保持计算的准确性
合理运用索引选择性分析，可以显著提升数据库查询性能，同时避免不必要的索引维护开销。
评论