CodeGym /课程 /SQL SELF /用 pg_stat_statements 分析索引和过滤器的使用情况

用 `pg_stat_statements` 分析索引和过滤器的使用情况

SQL SELF

第 42 级 , 课程 3

可用

索引就像书里的书签，能帮你很快找到想要的数据。但如果我们加了一堆书签，结果没人用，甚至更糟糕的是，烂书签让我们每次都得从头翻到尾？这时候就得分析下索引到底有没有被用上。

写得不好的查询可能会直接无视索引，导致全表顺序扫描（Seq Scan），这样查询就会变慢，服务器压力也大。我们的目标就是搞清楚哪些查询没用索引，以及为啥不用。

怎么判断索引有没有被用？

我们来看两个关键问题：

我们建的索引到底用没用上？
如果用了，效果咋样？

为了解决这些问题，我们可以分析 pg_stat_statements 里的查询统计，主要关注这几个字段：

rows：查询处理的行数。
shared_blks_hit：从内存（不是磁盘）里读到的页面数。
shared_blks_read：实际从磁盘读的页面数。

查询处理的行越少，shared_blks_hit 占总页面数的比例越高，说明索引用得越好。

索引分析示例

假设我们有个学生表：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- 给 grade_level 建个索引
CREATE INDEX idx_grade_level ON students(grade_level);

现在插点数据做实验：

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    '学生 ' || generate_series(1, 100000),
    '姓氏',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

来个查询，找出特定年级的学生：

SELECT *
FROM students
WHERE grade_level = 10;

在 `pg_stat_statements` 里检查

多执行几次查询后，可以查下统计信息：

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

结果怎么解读：

rows：如果查询返回的行太多，这个索引还有用吗？可能对于低选择性的条件，索引没啥意义。
shared_blks_hit 和 shared_blks_read：如果从磁盘读的页面（shared_blks_read）很多，说明索引没起作用，或者数据不在 buffer pool 里。

索引优化

建索引只是第一步，更重要的是 PostgreSQL 真正用上它。有时候不管你怎么努力，数据库就是不用索引，非要全表顺序扫描。为啥会这样？我们来看看。

先看看为啥明明有用的索引会被忽略。然后再聊聊怎么让数据库“记得”我们有索引，并且用起来。

如果索引没被用咋办？

有时候 PostgreSQL 会无视索引，直接顺序扫描（Seq Scan），可能有这些原因：

条件选择性低。如果查询返回的行超过表的一半，顺序扫描可能更快。
数据类型或函数。如果你在查询里对索引字段用了函数，索引可能会被忽略。比如：

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- 索引没用上

这种情况可以改写查询：

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- 用上索引了

索引类型不合适。比如做全文搜索最好用 GIN 或 GiST 索引，而不是 B-TREE。
统计信息有问题。如果统计信息过时，优化器可能做出错误决策。用 ANALYZE 更新下：
```
ANALYZE students;
```

怎么优化查询

回到我们的例子。如果索引没生效，可以试试这些办法：

确保查询用的过滤条件能用上索引：别用函数、类型转换啥的。
如果过滤条件返回太多行，想想索引还有没有必要。如果这是常用查询，可以考虑改下表结构或者加物化视图。
如果因为数据量太大导致 Seq Scan，可以考虑把表分区（PARTITION BY）。

怎么验证索引效果

优化完后再执行下查询，查查统计信息：

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

对比下优化前后的指标。你应该能看到磁盘读取（shared_blks_read）减少，命中（shared_blks_hit）增加。

真实案例

索引用错了

我们有个产品表，里面有个文本字段 description：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- 做全文搜索的索引
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

如果我们查：

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

索引不会被用！因为 ILIKE 跟 GIN 不兼容。要用索引得改成这样：

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

该有索引的地方没索引

比如这个查询：

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

执行的是顺序扫描（Seq Scan）。可能是 birth_date 没有索引。建个索引：

CREATE INDEX idx_birth_date ON students(birth_date);

再更新下统计信息（ANALYZE students），这个查询就能快很多了。

上一课上一课

下一课下一课

TO VIEW ALL COMMENTS OR TO MAKE A COMMENT,
GO TO FULL VERSION

用 pg_stat_statements 分析索引和过滤器的使用情况