PostgreSQL之如何进行SQL优化?

文章目录

    • 一、明确主题
    • 二、目标
    • 三、如何进行SQL优化?
      • 3.1 pg_stat_activity系统内置活动视图
        • 视图主要字段
          • wait_event_type
          • state
      • 3.2 pg_stat_statement SQL执行统计视图
        • 3.2.1 pg_stat_statements 视图详细说明
          • 中文版(简版)
        • 3.2.2 常见案例 Top SQL
      • 3.3 大表的索引使用情况
        • 3.3.1 pg_stat_user_tables视图
        • 3.3.2 pg_stat_user_indexes视图
    • 四、如何分析慢查询?查看执行计划!
      • 4.1 EXPLAIN 介绍
      • 4.2 EXPLAIN 内容说明
        • 4.2- EXPLAIN可视化
      • 4.3 EXPLAIN一些常见运算操作
      • 4.4 查询规划器的依据--统计数据
        • 4.4.1 统计数据
        • 4.4.2 pg_stats 视图
      • 4.5 用好工具
    • 五、如何使用索引?
      • 5.0 必看!!!重要!!!**:boom:**:boom::boom:
      • 5.1 快问快答
      • 5.2 探索:如何创建索引?
        • 5.2.1 索引的名字
        • 5.2.2 索引的字段
        • 5.2.3 索引的方法(类型)
        • 5.2.4 索引的运算符类别
        • 5.2.5 索引的排序顺序
      • 5.3 探索:什么时候走索引?
      • 5.4 全都是套路!:rocket::bulb:
        • 5.4.1 重点关注大表
        • 5.4.2 分析大表
        • 5.4.3 日常重建索引**:recycle:**


一、明确主题

引语

  • 如何使用索引?(what 什么是索引?why 为什么需要索引?how 如何创建索引?when 什么时候走索引?)

二、目标

原因:大部分SQL问题都能通过加索引来解决,我希望这个套路能够成为大家后续在进行SQL优化中思考的一个方向。

养成一种自信:索引能够解决的问题,就不是问题。

三、如何进行SQL优化?

上面有提到,虽然DDL,DML,DQL,DCL我们都需要了解,但是select仍然是主旋律

那么问题来了:如何写出高性能的SQL?还有,我怎么知道我写的SQL是不是高效的?

最简单的一个标准:在达到业务目的的情况下,select语句用时最少,那么就是最高效的。

如何发现问题?

  1. 🚑紧急,重大事故(逆推法,从现象找原因):生产环境慢查询导致系统卡顿(数据库服务器CPU使用率飙升,负载飙升),前端表现为大批用户反馈数据请求超时。
  2. ➖居安思危:日常排查慢查询 pg_stat_statement 。
  3. ✅前瞻性:查看索引使用情况,为必要的表字段添加索引。(大胆猜测,小心求证)

相关问题:

  1. 怎么判断是否需要优化(怎么查看SQL是否有问题)?阻塞、锁?(pg_stat_activity系统内置活动视图)
  2. 如何统计慢查询?(pg_stat_statement 插件)
  3. 大表vs慢查询?
  4. 如何分析慢查询?(explain)

3.1 pg_stat_activity系统内置活动视图

pg_stat_activity视图将为每一个服务器进程显示一行,显示与该进程的当前活动相关的信息。

理论上能捕抓到每个实时的SQL,运行结束可能就会消失,也就是说实际上有些运行时间短的SQL可能会抓不到(人为错觉)。

该视图可以实时查询当前在执行的SQL,包括状态,比如是否阻塞、等待锁等。

视图主要字段

wait_event_type

后端正在等待的事件类型,如果不存在则为 NULL。可能的值有:

  • LWLock:后端正在等待一个轻量级锁。每一个这样的锁保护着共享内存中的一个特殊数据结构。wait_event将含有一个标识该轻量级锁目的的名称(一些锁具有特定的名称,其他是一组具有类似目的的锁中的一部分)。
  • Lock:后端正在等待一个重量级锁。重量级锁,也称为锁管理器锁或者简单锁,主要保护 SQL 可见的对象,例如表。不过,它们也被用于确保特定内部操作的互斥,例如关系扩展。wait_event将标识等待的锁的类型。
  • BufferPin:服务器进程正在等待访问一个数据缓冲区,而此时没有其他进程正在检查该缓冲区。如果另一个进程持有一个最终从要访问的缓冲区中读取数据的打开的游标,缓冲区 pin 等待可能会被拖延。
  • Activity:服务器进程处于闲置状态。这被用于在其主处理循环中等待活动的系统进程。wait_event将标识特定的等待点。
  • Extension:服务器进程正在一个扩展模块中等待活动。这一个分类被用于要跟踪自定义等待点的模块。
  • Client:服务器进程正在一个套接字上等待来自用户应用的某种活动,并且该服务器预期某种与其内部处理无关的事情发生。wait_event将标识特定的等待点。
  • IPC:服务器进程正在等待来自服务器中另一个进程的某种活动。wait_event将标识特定的等待点。
  • Timeout:服务器进程正在等待一次超时发生。wait_event将标识特定的等待点。
  • IO:服务器进程正在等待一次IO完成。wait_event将标识特定的等待点。
state

这个后端的当前总体状态。可能的值是:

  • active:后端正在执行一个查询。
  • idle:后端正在等待一个新的客户端命令。
  • idle in transaction:后端在一个事务中,但是当前没有正在执行一个查询。
  • idle in transaction (aborted):这个状态与idle in transaction相似,不过在该事务中的一个语句导致了一个错误。
  • fastpath function call:后端正在执行一个 fast-path 函数。
  • disabled:如果在这个后端中track_activities被禁用,则报告这个状态。

常用SQL

select * from pg_stat_activity;-- 查找非空闲的SQL
select * from pg_stat_activity where state <>'idle';
-- 按照开始时间进行排序
select *,now()-query_start from pg_stat_activity where state <>'idle' ORDER BY query_start asc ;-- kill SQL,pg_terminate_backend(PID);
SELECT pg_terminate_backend(15322);

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

阻塞问题:如果是非异常的阻塞,可以直接kill掉,使用pg_terminate_backend。

注意:不是说SQL执行时间长或者是在等待锁就是有问题的,需要看具体的SQL,有些DDL就是需要加锁的,因此其他相关的操作会等待锁是正常的。不正常距离:有很多终端列表查询的SQL,且运行时间都比较长。

http://postgres.cn/docs/12/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

3.2 pg_stat_statement SQL执行统计视图

需要安装插件。

类似活动视图,将SQL参数化,统计执行时间,buffer读取等。

pg_stat_statements 视图包含了一些重要信息,例如:

  • SQL 的调用次数总耗时,最快执行时间,最慢执行时间,平均执行时间,执行时间的方差(看出抖动),总共扫描、返回或处理了多少行。
  • shared buffer 的使用情况:命中、未命中、产生脏块、驱逐脏块。
  • local buffer 的使用情况:命中、未命中、产生脏块、驱逐脏块。
  • temp buffer 的使用情况:读了多少脏块、驱逐脏块。
  • 数据块的读写时间。

3.2.1 pg_stat_statements 视图详细说明

参数名称类型参考说明
useridoidpg_authid.oidOID of user who executed the statement.
dbidoidpg_database.oidOID of database in which the statement was executed.
queryidbigintInternal hash code, computed from the statement’s parse tree.
querytextText of a representative statement.
callsbigintNumber of times executed.
total_timedouble precisionTotal time spent in the statement, in milliseconds.
min_timedouble precisionMinimum time spent in the statement, in milliseconds.
max_timedouble precisionMaximum time spent in the statement, in milliseconds.
mean_timedouble precisionMean time spent in the statement, in milliseconds.
stddev_timedouble precisionPopulation standard deviation of time spent in the statement, in milliseconds.
rowsbigintTotal number of rows retrieved or affected by the statement.
shared_blks_hitbigintTotal number of shared block cache hits by the statement.
shared_blks_readbigintTotal number of shared blocks read by the statement.
shared_blks_dirtiedbigintTotal number of shared blocks dirtied by the statement.
shared_blks_writtenbigintTotal number of shared blocks written by the statement.
local_blks_hitbigintTotal number of local block cache hits by the statement.
local_blks_readbigintTotal number of local blocks read by the statement.
local_blks_dirtiedbigintTotal number of local blocks dirtied by the statement.
local_blks_writtenbigintTotal number of local blocks written by the statement.
temp_blks_readbigintTotal number of temp blocks read by the statement.
temp_blks_writtenbigintTotal number of temp blocks written by the statement.
blk_read_timedouble precisionTotal time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero).
blk_write_timedouble precisionTotal time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero).
中文版(简版)
参数名称类型参考说明
useridoidpg_authid.oid
dbidoidpg_database.oid数据库id
queryidbigint
querytext查询语句
callsbigint执行次数
total_timedouble precision总耗时(毫秒)
min_timedouble precision最短耗时
max_timedouble precision最长耗时
mean_timedouble precision平均耗时

3.2.2 常见案例 Top SQL

查找最耗费资源的 SQL(Top SQL) (alibabacloud.com)

--最耗IO SQL
--单次调用最耗IO SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;--总最耗IO SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;--最耗时 SQL
--单次调用最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5; --总最耗时 SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5; --响应时间抖动最严重 SQL
select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5; --最耗共享内存 SQL
select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;--最耗临时空间 SQL
select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;

注意:因为参数化的问题,这里的SQL的是不可直接执行的,我们需要根据SQL的一些信息,在IDE中找到对应的逻辑,打日志,抓到对应的SQL,进行分析。

(本次分享会)重点:咱们目标足够小,因此很多东西暂时用不着,可以后续再深入研究,本次不做讨论。我们需要重点关注:调用次数总耗时平均执行时间,这3个指标,SQL执行时间长,那说明有优化的空间。(其实,其他如读写缓存的大小、临时空间的大小等最终多多少少都会体现在用时这里,因此关注用时也就基本上够了。)

3.3 大表的索引使用情况

索引主要用于查询逻辑,一般用于大表。反过来,我们需要关注索引的使用情况。

日常维护,观察大表(所占存储空间大、记录多、索引所占空间大的表)和索引的情况,考虑是否需要为关键字段加索引、删掉未曾使用过的索引。

统计收集器 (postgres.cn)

3.3.1 pg_stat_user_tables视图

pg_stat_all_tables视图将包含 当前数据库中每个表的一行(包括TOAST表),显示访问特定表的统计信息。 pg_stat_user_tablespg_stat_sys_tables视图 包含相同的信息,但是过滤只分别显示用户和系统表。

pgAdmin里面的表的分析数据大部分就是这里来的。

在这里插入图片描述

类型描述
relidoid表的OID
schemanamename此表的模式名
relnamename表名
seq_scanbigint此表发起的顺序扫描数
seq_tup_readbigint顺序扫描抓取的活跃行数
idx_scanbigint此表发起的索引扫描数
idx_tup_fetchbigint索引扫描抓取的活跃行数
n_tup_insbigint插入行数
n_tup_updbigint更新行数
n_tup_delbigint删除行数
n_tup_hot_updbigintHOT更新行数(比如没有更新所需的单独索引)
n_live_tupbigint估计活跃行数
n_dead_tupbigint估计死行数
last_vacuumtimestamp with time zone最后一次此表是手动清理的(不计算VACUUM FULL
last_autovacuumtimestamp with time zone上次被autovacuum守护进程清理的表
last_analyzetimestamp with time zone上次手动分析这个表
last_autoanalyzetimestamp with time zone上次被autovacuum守护进程分析的表
vacuum_countbigint这个表被手动清理的次数(不计算VACUUM FULL
autovacuum_countbigint这个表被autovacuum清理的次数
analyze_countbigint这个表被手动分析的次数
autoanalyze_countbigint这个表被autovacuum守护进程分析的次数

(本次分享)需要注意的数据:seq_scan,idx_scan,n_live_tup,n_dead_tup

如何判断表的大小?

-- 问下pg怎么求大小?!
select * from pg_proc  where proname ~ 'size';
-- 按照页的数量进行排序
select * from pg_class where relnamespace = 2200 ORDER BY relpages desc;
-- 按照实体的大小进行排序
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc;
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relkind = 'r' and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc;
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relkind = 'i' and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc;
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relkind = 'm' and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc;

关注表使用情况:

-- seq_scan,idx_scan,n_live_tup,n_dead_tup
select * from pg_stat_user_tables limit 10;
select * from pg_stat_user_tables ORDER BY seq_scan desc;
select * from pg_stat_user_tables ORDER BY idx_scan desc;
select * from pg_stat_user_tables ORDER BY n_live_tup desc;
select * from pg_stat_user_tables ORDER BY n_dead_tup desc;
-- 死亡元组多的表可以考虑清一波
select 'VACUUM VERBOSE ANALYZE '|| relname || ';',* from pg_stat_user_tables ORDER BY n_dead_tup desc;-- 排查:大表的全表扫描比索引扫描用得多的情况(考虑建索引)
select * from pg_stat_user_tables where seq_scan > idx_scan ORDER BY seq_scan desc;
select * from pg_stat_user_tables where n_live_tup > 10000 and seq_scan > idx_scan ORDER BY seq_scan desc;

3.3.2 pg_stat_user_indexes视图

pg_stat_all_indexes视图将包含当前数据库中的每个索引行,显示访问特定索引的统计。 pg_stat_user_indexespg_stat_sys_indexes视图包含相同的信息, 但是过滤只是分别显示用户和系统索引。

类型描述
relidoid这个索引的表的OID
indexrelidoid索引的OID
schemanamename索引中模式名
relnamename索引的表名
indexrelnamename索引名
idx_scanbigint索引上开始的索引扫描数
idx_tup_readbigint通过索引上扫描返回的索引项数
idx_tup_fetchbigint通过使用索引的简单索引扫描抓取的活表行数

查看某个表索引使用情况:

-- 查看某个表索引使用情况
select * from pg_stat_user_indexes limit 10;
select * from pg_stat_user_indexes where relname = 'test' ORDER BY idx_scan asc;

套路:根据 pg_stat_user_tables 视图找到需要关注的表,然后根据 pg_stat_user_indexes 视图查看该表的索引使用情况。

-- 建议一个个分析,当然也可以合起来
select * from pg_stat_user_indexes where relname in (select relname from pg_class where relnamespace = 2200 and relkind = 'r' and relname !~'bak|copy' ORDER BY pg_total_relation_size(oid) desc limit 10) ORDER BY relname,idx_scan asc;

🤔 使用次数为0(或者次数少)的索引是不是考虑删掉?!

四、如何分析慢查询?查看执行计划!

4.1 EXPLAIN 介绍

EXPLAIN – 显示一个语句的执行规划。

大纲:

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement这里的 option可以是下列之一:ANALYZE [ boolean ]VERBOSE [ boolean ]COSTS [ boolean ]BUFFERS [ boolean ]TIMING [ boolean ]FORMAT { TEXT | XML | JSON | YAML }

EXPLAIN (postgres.cn)

使用EXPLAIN (postgres.cn)

简版:

EXPLAIN
select kks.id from test_table1 kks left join test_table2 kksp on kks.id = kksp.storeid where kksp.id = 210399;

详细版:

EXPLAIN (ANALYZE,VERBOSE,BUFFERS)
select kks.id from test_table1 kks left join test_table2 kksp on kks.id = kksp.storeid where kksp.id = 210399;

4.2 EXPLAIN 内容说明

案例:

EXPLAIN 
SELECTkks.ID,kksp.representativeid,po.oname
FROMtest_table1 kksLEFT JOIN test_table2 kksp ON kks.ID = kksp.storeid LEFT JOIN test_table3 po ON po.ostructid = kksp.representativeid 
WHEREkks.ID = 210399;

输出:

Nested Loop Left Join  (cost=1.12..49.84 rows=3 width=27)Join Filter: (kks.id = kksp.storeid)->  Index Only Scan using test_table1_pkey on test_table1 kks  (cost=0.42..8.44 rows=1 width=8)Index Cond: (id = 210399)->  Nested Loop Left Join  (cost=0.70..41.36 rows=3 width=27)->  Index Scan using ix_test_table2_storeid on test_table2 kksp  (cost=0.42..16.46 rows=3 width=16)Index Cond: (storeid = 210399)->  Index Scan using ix_test_table3_orgstructid on test_table3 po  (cost=0.28..8.30 rows=1 width=19)Index Cond: (orgstructid = kksp.representativeid)

说明:

  1. 阅读顺序:从下到上(从左到右),(按照剪头->)我们可以看到是有层次的,我们应该从最底层(即最里面的)开始看。比如上面的例子,我们应该从第6(67)行开始看,然后是8(89)、5(56789)、3、1。

  2. 每行的大致规律:操作描述+(估计成本/代价/开销)+条件。

    • 描述通常是说明这一步进行进行什么操作,比如第6行:Index Scan using ix_test_table2_storeid on test_table2 kksp,说明 在test_table2使用的是索引扫描,用到的索引是 ix_test_table2_storeid 。

    • 括号内容:cost 进行这个操作需要花费的估计启动成本和总成本(成本:和CPU相关的一个值,和时间相关,可以简单的理解成值越大,用时越久),rows 估计行数,width 估计的每行的宽度(字节)。(cost=0.42…16.46 rows=3 width=16) 扫描这个索引的启动成本为0.42,即获取第一行记录的成本为0.42,总成本为16.46,估计行数为3,每行宽度为16.

    • 条件:操作相关的条件,比如第7行的 Index Cond: (storeid = 210399) ,使用这个索引的条件是 storeid = 210399。

  3. 详细的还会有每个步骤的Output、Buffers读写情况。如下面的第2行:Output: kks.id, kksp.representativeid, po.oname 是最后的输出,第4行:Buffers: shared hit=19 是指命中了19个数据块(页)。

详细的:

EXPLAIN (ANALYZE,VERBOSE,BUFFERS)
SELECTkks.ID,kksp.representativeid,po.oname
FROMtest_table1 kksLEFT JOIN test_table2 kksp ON kks.ID = kksp.storeid LEFT JOIN test_table3 po ON po.ostructid = kksp.representativeid 
WHEREkks.ID = 210399;

输出

Nested Loop Left Join  (cost=1.12..45.84 rows=3 width=27) (actual time=1.730..1.791 rows=2 loops=1)Output: kks.id, kksp.representativeid, po.onameJoin Filter: (kks.id = kksp.storeid)Buffers: shared hit=19->  Index Only Scan using test_table1_pkey on public.test_table1 kks  (cost=0.42..4.44 rows=1 width=8) (actual time=1.605..1.606 rows=1 loops=1)Output: kks.idIndex Cond: (kks.id = 210399)Heap Fetches: 1Buffers: shared hit=8->  Nested Loop Left Join  (cost=0.70..41.36 rows=3 width=27) (actual time=0.121..0.180 rows=2 loops=1)Output: kksp.representativeid, kksp.storeid, po.onameInner Unique: trueBuffers: shared hit=11->  Index Scan using ix_test_table2_storeid on public.test_table2 kksp  (cost=0.42..16.46 rows=3 width=16) (actual time=0.076..0.094 rows=2 loops=1)Output: kksp.platcreatetime, kksp.platupdatetime, kksp.platcreateop, kksp.platupdateop, kksp.platstatus, kksp.storeid, kksp.id, kksp.representativeid, kksp.isdefault, kksp.tn_timeIndex Cond: (kksp.storeid = 210399)Buffers: shared hit=5->  Index Scan using ix_test_table3_orgstructid on public.test_table3 po  (cost=0.28..8.30 rows=1 width=19) (actual time=0.039..0.039 rows=1 loops=2)Output: po.ostructid, po.parentorgstructid, po.oname, po.status, po.ostructeffecttime, po.ostructexpiretime, po.ostructtypeid, po.userinfoid, po.positionid, po.parentpositionid, po.oid, po.otypeid, po.ostructdescr, po.codepath, po.createtime, po.createop, po.updatetime, po.updateop, po.fullname, po.seq, po.platcreatetime, po.platupdatetime, po.platcreateop, po.platupdateop, po.platstatus, po.level, po.parentmemberidIndex Cond: (po.ostructid = kksp.representativeid)Buffers: shared hit=6
Planning time: 0.649 ms
Execution time: 1.850 ms

参考:【转载】PostgreSQL执行计划_秦时明月之君临天下的博客-CSDN博客_pgsql 执行计划

4.2- EXPLAIN可视化

https://explain.depesz.com/
在这里插入图片描述

例如上面这个例子,可视化之后是这样的:

https://explain.depesz.com/s/hANb#html

explain内容可视化:
在这里插入图片描述
汇总分析:
在这里插入图片描述

4.3 EXPLAIN一些常见运算操作

运算类型操作说明是否有启动时间
Seq Scan顺序扫描表无启动时间
Index Scan索引扫描无启动时间
Index Only Scan索引扫描无启动时间
Bitmap Index Scan索引扫描有启动时间
Bitmap Heap Scan索引扫描有启动时间
Subquery Scan子查询无启动时间
Tid Scan行号检索无启动时间
Function Scan函数扫描无启动时间
Nested Loop Join嵌套连接无启动时间
Merge Join合并连接有启动时间
Hash Join哈希连接有启动时间
Sort排序(order by)有启动时间
Hash哈希运算有启动时间
Result函数扫描,和具体的表无关无启动时间
Uniquedistinct/union有启动时间
Limitlimit/offset有启动时间
Aggregatecount, sum,avg等聚集函数有启动时间
Groupgroup by有启动时间
Appendunion操作无启动时间
Materialize子查询有启动时间
SetOpintersect/except有启动时间

4.4 查询规划器的依据–统计数据

4.4.1 统计数据

查询规划器基于表的统计数据对查询进行优化,自动选择它认为最佳的策略。

因此需要注意:

  1. 如果统计信息不准确,可能会导致策略和实际相差很远。因而分析前,建议先更新统计数据。ANALYZE VERBOSE tablename;,统计信息在 pg_statistic 这个表中。
  2. 因为分析表时,使用的是抽样调查的方法,因此该统计信息天然就是不准确的。但是及时更新还是必要的,以保证统计数据不至于太落后。

这个表的可读性较差,我们可以看对应的视图:pg_stats。

pg_statistic 数据示例图:

在这里插入图片描述

pg_stats 数据示例图:

在这里插入图片描述

文档:目录pg_statistic存储有关数据库内容的统计数据。其中的项由ANALYZE创建,查询规划器会使用这些数据来进行查询规划。注意所有的统计数据天然就是近似的,即使它刚刚被更新。

http://postgres.cn/docs/12/catalog-pg-statistic.html

相关SQL

-- 4.4 查询规划器的依据--统计数据
select * from pg_class where relname ~ 'pg_stat' limit 100;
select * from pg_class where relname = 'test_table' limit 100;--1289400
select * from pg_class where relname ~ 'test_table' limit 10000;--ANALYZE VERBOSE test_table;
select * from pg_statistic limit 100;
select * from pg_statistic where starelid = 1289400 limit 100;select * from pg_stats limit 100;
select * from pg_stats where tablename = 'test_table' limit 100;

4.4.2 pg_stats 视图

视图pg_stats提供对存储在pg_statistic目录中信息的访问。

名称类型引用描述
schemanamenamepg_namespace.nspname包含表的模式名
tablenamenamepg_class.relname表名
attnamenamepg_attribute.attname被此行描述的列名
inheritedbool如果为真,表示此行包括继承子列,不仅仅是指定表中的值
null_fracreal列项中为空的比例
avg_widthinteger列项的平均字节宽度
n_distinctreal如果大于零,表示列中可区分值的估计个数。如果小于零,是可区分值个数除以行数的负值(当ANALYZE认为可区分值的数量会随着表增长而增加时采用负值的形式,而如果认为列具有固定数量的可选值时采用正值的形式)。例如,-1表示一个唯一列,即其中可区分值的个数等于行数。
most_common_valsanyarray列中最常用值的一个列表(如果没有任何一个值看起来比其他值更常用,此列为空)
most_common_freqsreal[]最常用值的频率列表,即每一个常用值的出现次数除以总行数(如果most_common_vals为空,则此列为空)
histogram_boundsanyarray将列值划分成大小接近的组的值列表。如果存在most_common_vals,其中的值会被直方图计算所忽略(如果列类型没有一个<操作符或者most_common_vals等于整个值集合,则此列为空)
correlationreal物理行顺序和列值逻辑顺序之间的统计关联。其范围从-1到+1。当值接近-1或+1时,在列上的一个索引扫描被认为比值接近0时的代价更低,因为这种情况减少了对磁盘的随机访问(如果列数据类型不具有一个<操作符,则此列为空)
most_common_elemsanyarray在列值中,最经常出现的非空元素列表(对标度类型为空)
most_common_elem_freqsreal[]最常用元素值的频度列表,即含有至少一个给定值实例的行的分数。在每个元素的频度之后有二至三个附加值,它们是每个元素频度的最小和最大值,以及可选的空元素的频度(如果most_common_elems为空,则此列为空)
elem_count_histogramreal[]在列值中可区分非空元素值计数的一个直方图,后面跟随可区分非空元素的平均数(对于标度类型为空)

4.5 用好工具

初期:能力不够,工具来补。

后期:经验。

1.Navicat 公司推荐。

2.pgAdmin pg官方自带的工具,能看到一些pg的特性。比如infomation_schema等。

能用图示显示explain过程。

在这里插入图片描述
在这里插入图片描述

3.DBeaver DBeaver Community | Free Universal Database Tool 最近比较流行的数据库工具,有社区版、企业版,付费的功能好像比较多一点(查看执行计划,能提示哪个节点比较慢),网上可以找到绿色版。

能显示出explain比较慢的节点(颜色标注)。

在这里插入图片描述

五、如何使用索引?

慢查询已经问题出来了,那些表的字段需要加索引也应该清楚了,那么就该考虑为字段加索引了。

5.0 必看!!!重要!!!💥💥💥

测试最好使用测试环境。

谨慎在生产环境进行索引相关操作(创建、删除、重建等),特别是业务高峰期,防止搞出事来。

如果真要在高峰期为生产数据库创建索引,请带上 CONCURRENTLY 参数,并发非阻塞创建索引。例如:

CREATE INDEX CONCURRENTLY idx_test_table4_dynamicid ON public.test_table4 USING btree (dynamicid);

5.1 快问快答

如何使用索引?(what 什么是索引?why 为什么需要索引?how 如何创建索引?when 什么时候走索引?)

  1. 什么是索引: 索引是数据库中用于提高查询效率的技术,类似于目录,一个索引是存储的表中一个特定列的值数据结构(最常见的是B-Tree)。

  2. 为什么需要索引:加快查询速度。

  3. 如何创建索引: 加create index index_name on table_name using USING btree (column_name);

  4. 什么时候走索引:比较大的表、条件有一定筛选行。

5.2 探索:如何创建索引?

Navicat创建一个索引可以设置的项,打勾的是我认为我们需要注意的。

在这里插入图片描述

5.2.1 索引的名字

命名无特别要求,只需要满足命名规则即可,然后这个名字不常用,但是建议是见文知意。比如 idx_test_table_address,或者是pg_class_oid_index(官方的)。

5.2.2 索引的字段

  1. 单列索引:最普通的。
  2. 部分索引:只把符合条件的值放到索引中 USING btree (userid) WHERE (status= 1)
  3. 多列索引(组合索引):把常用的几个关联的字段,同时组合成索引。 USING btree (userid, reportmonth);
  4. 唯一索引 CREATE UNIQUE INDEX ……

5.2.3 索引的方法(类型)

PostgreSQL提供了多种索引类型: B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下, CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

  1. btree 满足日常大部分场景,如果不知道用啥就它了!
  2. hash 等值操作。

5.2.4 索引的运算符类别

一个索引定义可以为索引中的每一列都指定一个操作符类

11.10. 操作符类和操作符族 (postgres.cn)


问题引入:索引列不能进行复杂计算、函数操作等,不然会导致索引失效(右置操作,把需要操作的数据放在右边,把索引列放在左边,然后进行比较)。比如createtime有索引,where to_char(createtime,‘yyyy-mm’) = ‘2022-01’ 为什么不走索引?

索引和列一样,都有自己的操作符,以简单的单列索引为例,默认会创建一个对应类型的索引列,如下表。每个操作符类有支持的操作类型,如果实际操作数类型不符,那么就不会走索引。

即创建时间其实为:USING btree (createtime “pg_catalog”.“timestamp_ops”)

类型操作符类别
int4int4_ops
int8int8_ops
timestamptimestamp_ops
varchartext_ops
…………
…………
-- 索引的运算符类别
explain (analyze,verbose,buffers) 
select createtime,id 
from test_table1 
where to_char(createtime,'yyyy-mm-dd') > '2022-04-17' 
-- where createtime > '2022-04-17 00:00:00' 
ORDER BY createtime desc;

所有已定义的操作符族和每一个族中包含的所有操作符:

-- 所有已定义的操作符族和每一个族中包含的所有操作符
SELECT am.amname AS index_method,opf.opfname AS opfamily_name,amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid
ORDER BY index_method, opfamily_name, opfamily_operator;
--index_method	opfamily_name	opfamily_operator
--btree	datetime_ops	<(timestamp with time zone,timestamp without time zone)
--btree	datetime_ops	<=(timestamp with time zone,timestamp without time zone)
……

5.2.5 索引的排序顺序

和普通列一样,索引也有顺序,默认升序(asc)。可以根据特点的用途选择合适的排序规则。

比如为拜访步骤web端查询,默认总是按照创建时间逆序进行展示,USING btree (createtime desc)。

5.3 探索:什么时候走索引?

课后作业:网上复制的一段内容,真实性不知。

【转发】PostgreSQL查询不走索引的情况_秦时明月之君临天下的博客-CSDN博客

查询不走索引的情况:

1、条件字段选择性弱,查出的结果集较大,不走索引;
2、where条件等号两边字段类型不同,不走索引;
3、索引字段 is null 不走索引;
4、对于count(*)当索引字段有not null约束时走索引,否则不走索引;
5、like 后面的字符当首位为通配符时不走索引;
6、使用不等于操作符如:<>、!= 等不走索引;
7、索引字段前加了函数或参加了运算不走索引;
8、部分索引,但查询条件包括不属于部分索引的数据。

如果where条件都没有以上所述,那么考虑优化器分析的统计信息陈旧,需要更新这个表的统计信息或者重建索引。

5.4 全都是套路!🚀💡

总结性质。

针对表进行分析

5.4.1 重点关注大表

-- 找到表的总大小排名前20的表(考虑优化)
select pg_size_pretty(pg_total_relation_size(oid)),* from pg_class where relnamespace = 2200 and relkind = 'r' ORDER BY pg_total_relation_size(oid) desc limit 20;-- 顺序扫描比索引扫描多的大表(考虑优化)
SELECT * from pg_stat_user_tables where n_live_tup > 10000 and seq_scan > idx_scan ORDER BY seq_scan desc;-- 索引比基本数据大的表(需要关注,可能有问题)
select pg_size_pretty(pg_relation_size(oid)) as table_size,pg_size_pretty(pg_indexes_size(oid)) as indexes_size,* from pg_class where relnamespace = 2200 and relkind = 'r' and pg_indexes_size(oid) > pg_relation_size(oid) ORDER BY pg_relation_size(oid) desc limit 20;

5.4.2 分析大表

分析大表,考虑是否需要加索引。

----分析问题--start---test_table4----2022年4月25日12:42:22
select * from test_table4 ORDER BY updatetime desc limit 10;
select count(1) from test_table4; -- 554826
SELECT * from pg_stat_user_tables where relname = 'test_table4';
SELECT * from pg_indexes where tablename = 'test_table4'; -- 默认无索引
SELECT * from pg_stat_user_indexes where relname = 'test_table4';
-- 查看表大小(包含索引) 166 MB
select pg_size_pretty(pg_total_relation_size('test_table4'));
-- 查看表(不包含索引)或者是索引的大小 166 MB
select pg_size_pretty(pg_relation_size('test_table4'));
select pg_size_pretty(pg_relation_size('idx_test_table4_triggerid')); -- 12 MB
select pg_size_pretty(pg_relation_size('idx_test_table4_jobinfoid'));
select pg_size_pretty(pg_relation_size('idx_test_table4_dynamicid'));
-- 考虑加索引
select * from pg_proc where prosrc like '%test_table4%';
select * from pg_stat_statements where query like '%test_table4%';
select exestatus,count(exestatus) from test_table4 GROUP BY exestatus;CREATE INDEX CONCURRENTLY idx_test_table4_triggerid ON public.test_table4 USING btree (triggerid);
CREATE INDEX CONCURRENTLY idx_test_table4_jobinfoid ON public.test_table4 USING btree (jobinfoid);
CREATE INDEX CONCURRENTLY idx_test_table4_dynamicid ON public.test_table4 USING btree (dynamicid);
-- 测试
select * from test_table4 where triggerid = 1517153934752485376 ORDER BY exestarttime desc limit 100;
select * from test_table4 where jobinfoid = 1380471623139856384  ORDER BY exestarttime desc limit 100;
select * from test_table4 where dynamicid = 'a0fe4f782fcb4cc59381642ac026fd1d'  ORDER BY exestarttime desc limit 100;----分析问题--end----------------------加索引

5.4.3 日常重建索引**♻️**

因为MVCC的机制,某些特殊情况下索引也可能会导致膨胀,因此官方建议日常重建索引,特别是针对一些更新频繁的表。

也可以先删,后建。

-- 创建语句
CREATE INDEX CONCURRENTLY idx_test_table4_triggerid ON public.test_table4 USING btree (triggerid);
-- 重建语句
REINDEX INDEX  idx_test_table4_triggerid ;
-- 并发重建
REINDEX INDEX  CONCURRENTLY idx_test_table4_triggerid ;
-- 重建表的所有索引
REINDEX TABLE  test_table4;
-- 并发重建表的所有索引
REINDEX TABLE  CONCURRENTLY test_table4;

注:CONCURRENTLY 好像是pg12之后才有的,具体版本未知。


end……

全剧终🎉🎉🎉。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/9639.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

以防作弊,ChatGPT 遭教育部「拉黑」:师生禁用!

来源&#xff1a;FUTURE远见 选编&#xff1a;FUTURE | 远见 闵青云 CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 文 上个月&#xff0c;弗曼大学哲学助理教授 Darren Hick 逮到一个用 ChatGPT 写论文的作弊者后&#xff0c;就感慨&#xff1a;「ChatGPT 太先进了&am…

ChatGPT国产化:ChatYuan元语对话大模型升级

国产自研功能对话大模型元语 ChatYuan 于 2022 年 12 月发布测试版本后&#xff0c;引起社会各界人士的广泛讨论&#xff0c;并且收到了用户的大量反馈和宝贵建议。元语智能团队已于近日对元语 ChatYuan 进行了模型效果优化和版本功能升级&#xff0c;现已开放内测。 &#xff…

体验联网版 ChatGPT:优点和缺点同样明显,还藏着无限可能

ChatGPT 有点像古希腊的阿喀琉斯&#xff1a;它很强大&#xff0c;却有个致命的弱点——无法联网&#xff0c;这注定了它只能是一个停留在 2021 年的超人。 但很快&#xff0c;我们将等到一个「鱼和熊掌兼得」的时刻。 通过插件集的 Web browsing 功能&#xff0c;ChatGPT 就…

教你写接口需求文档

作者&#xff1a;果果 转载已取得作者授权 一、什么是接口 百科上对接口的定义&#xff1a;API&#xff08;Application Programming Interface,应用程序编程接口&#xff09;是一些预先定义的函数&#xff0c;目的是提供应用程序与开发人员基于某软件或硬件得以访问一组例程的…

什么是反向代理,开放的反向代理软件使用方法

什么是反向代理呢&#xff1f;其实&#xff0c;反向代理也就是通常所说的WEB服务器加速&#xff0c;它是一种通过在繁忙的WEB服务器和Internet之间增加一个高速的WEB缓冲服务器&#xff08;即&#xff1a;WEB反向代理服务器&#xff09;来降低实际的WEB服务器的负载。 Web服务器…

反向正向代理

https://www.zhihu.com/question/36412304

宝塔反代教程

近期有网友问宝塔如何设置反向代理&#xff0c;小编这里介绍一种简单的操作方法&#xff0c;就是使用宝塔官方软件面板自带的反向代理功能来实现。首先您要先安装宝塔面板&#xff0c;当Nginx或LNMP环境配置完成后&#xff0c;便可开始设置反向代理了&#xff0c;下面来看下操作…

[转发]图示说明正向代理和反向代理

关于代理 说到代理&#xff0c;首先我们要明确一个概念&#xff0c;所谓代理就是一个代表、一个渠道&#xff1b; 此时就设计到两个角色&#xff0c;一个是被代理角色&#xff0c;一个是目标角色&#xff0c;被代理角色通过这个代理访问目标角色完成一些任务的过程称为代理操…

Linux宝塔面板反向代理设置教程

此方法最简单快捷&#xff0c;没有复杂步骤&#xff0c;不容易出错&#xff0c;即最简单&#xff0c;零代码、零部署的方法。 实现准备 一台服务器安装宝塔面板OpenAI官方的API_KEYChatGPT网站系统源码 ChatGPT网站系统源码&#xff0c;大家可以看看另一篇文章介绍&#xff…

网关V.S反向代理

简介 网关主要服务于微服务/API&#xff0c;偏向研发人员反向代理主要面向传统静态web应用&#xff0c;偏向运维而未来趋势是DevOps网关和反向代理再次融合 发展趋势 WEB1.0/2.0时代&#xff0c;使用前置反向代理&#xff0c;由运维负责 nginx&#xff0c;进行反向代理和负载…

反向代理

先看这张图 反向代理&#xff08;Reverse Proxy&#xff09;方式是指以代理服务器来接受internet上的连接请求&#xff0c;然后将请求转发给内部网络上的服务器&#xff0c;并将从服务器上得到的结果返回给internet上请求连接的客户端&#xff0c;此时代理服务器对外就表现为一…

图解正向代理和反向代理

套用古龙武侠小说套路来说&#xff0c;代理服务技术是一门很古老的技术&#xff0c;是在互联网早期出现就使用的技术。一般实现代理技术的方式就是在服务器上安装代理服务软件&#xff0c;让其成为一个代理服务器&#xff0c;从而实现代理技术。常用的代理技术分为正向代理、反…

代理,正向代理和反向代理

1. 代理 代理也被叫做网络代理&#xff0c;是一种比较特殊的网络服务&#xff0c;允许一个终端&#xff08;通常指客户端&#xff09;通过这个服务与另一个终端&#xff08;通常指服务器端&#xff09;进行非直接的连接。 例如&#xff1a;一些网关、路由器等网络设备都具备网…

反向代理,正向代理,网关

反向代理&#xff0c;正向代理&#xff0c;网关 正向代理与反向代理&#xff0c;正向与反向是如何区分的&#xff1f;反向代理在系统架构中的应用场景&#xff1a; 正向代理与反向代理&#xff0c;正向与反向是如何区分的&#xff1f; 正向与反向的目标是客户端&#xff0c;是…

2、正向代理和反向代理(正向代理(客户端),反向代理(服务器))

正向代理和反向代理&#xff08;正向代理&#xff08;客户端&#xff09;&#xff0c;反向代理&#xff08;服务器&#xff09;&#xff09; 1、正向代理&#xff1a;&#xff08;代理客户端&#xff09; 说个例子&#xff08;访问外网&#xff09;&#xff1a;我们的客户端先…

正向代理 / 反向代理

1. 正向代理 正向代理是一个位于客户端和原始服务器(origin server)之间的服务器,为了从原始服务器取得内容,客户端向代理发送一个请求并指定目标(原始服务器),然后代理向原始服务器转交请求并将获得的内容返回给客户端。客户端必须设置正向代理服务器,当然前提是要知道正…

chatgpt赋能python:Python中的JWT解码(Decode)

Python中的JWT解码&#xff08;Decode&#xff09; 什么是JWT&#xff08;JSON Web Token&#xff09;&#xff1f; JSON Web Token&#xff08;JWT&#xff09;是目前最流行的 Web 身份验证方案之一。JWT 是基于 JSON 的开放标准&#xff0c;它可以让客户端和服务器之间传递…

对接支付宝php版easysdk接口分享

在网上没有找到支付宝easysdk接口的对接详细说明&#xff0c;自己一步一步的摸索&#xff0c;其中使用chatgpt的帮助作用不少。 根据官方给的文档 需要执行composer require alipaysdk/easysdk:^2.0安装sdk。如果你的项目已经安装过其它扩展&#xff0c;可以先把vendor文件夹备…

记录第一次利用chatgpt 实现RSA加密解密编程

1&#xff0c;下载Cursor软件 2&#xff0c;左侧文件栏目新建一个first.java 3&#xff0c;在编辑区输入“//使用RSA公私钥实现JWT令牌的签名和验签”&#xff0c;并且连续输入两个回车&#xff08;相当于调用CtrlK&#xff09; AI会自动生成代码&#xff0c;点击“Accept al…

ChatGPT | Word文档如何更好地提取表格内容给ChatGPT

本文来自http://blog.csdn.net/hellogv/ &#xff0c;引用必须注明出处&#xff01; Word文档如何更好地提取表格内容给ChatGPT做知识库&#xff0c;这属于文本预处理工作。 本文只讲思路、测试结果&#xff0c;技术实现用Python和Java都能完成&#xff0c;下一篇文章再贴源码…