PostgreSQL 如何对索引进行分析和处理

AustinDatabases 2023-06-25

195

开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群（共890人左右 1 + 2 + 3）新人会进入3群。

最近有人私信问POSTGRESQL 怎么比MYSQL的索引大，这个问题升级上我个人觉得从这几点考虑

1 一个数据库本身的索引提供什么样的功能，是否有为加速查询附加的信息。

2 POSTGRESQL 数据库中的数据索引的结构是否与你在其他的数据库中使用的索引的结构不同

3 POSTGRESQL 的索引还负担了MVCC多版本控制查询中所需要的信息，所以本身的设计也让他比其他的数据库的索引要更大

4 索引的字段中如果包含TOAST 字段，是不会引起索引的包含TOAST的内容的，因为索引是通过指针的方式指到TOAST的字段位置

那么具体怎么分析索引的问题，我们可以通过以下的语句来进行一些简单的问题

1 如何一个SQL将索引中的核心信息一网打尽，实际上很多的同学问，怎么能知道索引中的字段组成，这你PG与MYSQL不同，可以单纯的通过系统表来获得这些信息，而是通过很多不同的函数来完成相关的工作，如我们要获得索引的字段组成，同时发现PG中表的一些特殊索引建立的方式，就必须用下面的这个语句。

SELECT
    t.schemaname,
    t.tablename,
    c.reltuples::bigint                            AS num_rows,
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
    psai.indexrelname                              AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique",
    psai.idx_scan                                  AS number_of_scans,
    psai.idx_tup_read                              AS tuples_read,
    psai.idx_tup_fetch                             AS tuples_fetched,
   pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)   as create_index
FROM
    pg_tables t
    LEFT JOIN pg_class c ON t.tablename = c.relname
    LEFT JOIN pg_index i ON c.oid = i.indrelid
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
    t.schemaname NOT IN ('pg_catalog', 'information_schema') and t.tablename = 'sys_log'
ORDER BY 1, 2;

如果这个语句不好记忆的话，可以采用VIEW的方式来提高这样的语句的使用效率如下面我们通过VIEW 来把这个SQL进行一个封装

CREATE OR REPLACE VIEW index_check AS
SELECT
    t.schemaname,
    t.tablename,
    c.reltuples::bigint                            AS num_rows,
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
    psai.indexrelname                              AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique",
    psai.idx_scan                                  AS number_of_scans,
    psai.idx_tup_read                              AS tuples_read,
    psai.idx_tup_fetch                             AS tuples_fetched,
   pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)   as create_index
FROM
    pg_tables t
    LEFT JOIN pg_class c ON t.tablename = c.relname
    LEFT JOIN pg_index i ON c.oid = i.indrelid
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
    t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

select * from index_check where tablename = 'sys_log';

通过这样的方式可以更快速的对于系统中的表进行索引的分析和辨认，对于索引的日常维护对于系统也是非常重要的，尤其是POSTGRESQL在一些项目中，有一些查询并不是一直存在，业务逻辑下线后，这个语句就不会再执行了，但是对于当时为这个语句建立的索引还在系统中存在，及时发现这些索引，并将其确认清理也是一个系统优化中的必须得工作。

SELECT relname AS index_name,
idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

这里提出几个需要考虑的地方

上面的语句可以发现索引是否持续的被使用，我们可以建立一个索引的历史分析表，每天将表中的内容插入到历史表中，然后定期分析，通过历史分析表中,同一个索引，不同时间的增量来判断这个索引是否还在被使用，专业也是一个发现无用索引的方案。

1 你的系统数据库运行到当前时间的长度，因为系统的表中的数据会伴随你系统的重启而清零，所以你得程序设计的逻辑中必须考虑这点

2 历史数据的合并与累加，因为历史表不能无限的进行增加，这与你截取系统表数据进行记录的频度有关，所以你需要考虑后期的数据合并的问题

3 判定提醒的阈值

当然对于POSTGRESQL 的索引的碎片我们也是要进行监控和管理的，索引的碎片太多，造成查询的效率降低，我们是要进行持续的定期的检查和重建相关的索引，这你分析的部分就需要自己写程序来进行后期的处理了。

CREATE EXTENSION pgstattuple; 在使用下面的函数前请确认是否在PG

的数据库中存在pgstattuple extension

SELECT * FROM pgstatindex('idx_sys_log_moduleid');

对于索引的性能优化还需要考虑

重复的索引导致的系统的资源的浪费的问题.

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
       (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
       (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
FROM (
    SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                                         coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key
    FROM pg_index) sub
GROUP BY key HAVING count(*)>1
ORDER BY sum(pg_relation_size(idx)) DESC;

以上是基本的POSTGRESQL 针对索引优化的基本姿势。

索引 postgresql mysql索引

文章转载自AustinDatabases，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL 如何对索引进行分析和处理

评论