一、明确主题
引语
- 如何使用索引？（what 什么是索引？why 为什么需要索引？how 如何创建索引？when 什么时候走索引？）
二、目标
原因：大部分SQL问题都能通过加索引来解决，我希望这个套路能够成为大家后续在进行SQL优化中思考的一个方向。
养成一种自信：索引能够解决的问题，就不是问题。
三、如何进行SQL优化？
上面有提到，虽然DDL,DML,DQL,DCL我们都需要了解，但是select仍然是主旋律。
那么问题来了：如何写出高性能的SQL？还有，我怎么知道我写的SQL是不是高效的？
最简单的一个标准：在达到业务目的的情况下，select语句用时最少，那么就是最高效的。
如何发现问题？
- 🚑紧急，重大事故（逆推法，从现象找原因）：生产环境慢查询导致系统卡顿（数据库服务器CPU使用率飙升，负载飙升），前端表现为大批用户反馈数据请求超时。
- ➖居安思危：日常排查慢查询 pg_stat_statement 。
- ✅前瞻性：查看索引使用情况，为必要的表字段添加索引。（大胆猜测，小心求证）
相关问题：
- 怎么判断是否需要优化（怎么查看SQL是否有问题）？阻塞、锁？（pg_stat_activity系统内置活动视图）
- 如何统计慢查询？（pg_stat_statement 插件）
- 大表vs慢查询？
- 如何分析慢查询？（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 视图详细说明
| 参数名称 | 类型 | 参考 | 说明 |
|---|---|---|---|
| userid | oid | pg_authid.oid | OID of user who executed the statement. |
| dbid | oid | pg_database.oid | OID of database in which the statement was executed. |
| queryid | bigint | 无 | Internal hash code, computed from the statement’s parse tree. |
| query | text | 无 | Text of a representative statement. |
| calls | bigint | 无 | Number of times executed. |
| total_time | double precision | 无 | Total time spent in the statement, in milliseconds. |
| min_time | double precision | 无 | Minimum time spent in the statement, in milliseconds. |
| max_time | double precision | 无 | Maximum time spent in the statement, in milliseconds. |
| mean_time | double precision | 无 | Mean time spent in the statement, in milliseconds. |
| stddev_time | double precision | 无 | Population standard deviation of time spent in the statement, in milliseconds. |
| rows | bigint | 无 | Total number of rows retrieved or affected by the statement. |
| shared_blks_hit | bigint | 无 | Total number of shared block cache hits by the statement. |
| shared_blks_read | bigint | 无 | Total number of shared blocks read by the statement. |
| shared_blks_dirtied | bigint | 无 | Total number of shared blocks dirtied by the statement. |
| shared_blks_written | bigint | 无 | Total number of shared blocks written by the statement. |
| local_blks_hit | bigint | 无 | Total number of local block cache hits by the statement. |
| local_blks_read | bigint | 无 | Total number of local blocks read by the statement. |
| local_blks_dirtied | bigint | 无 | Total number of local blocks dirtied by the statement. |
| local_blks_written | bigint | 无 | Total number of local blocks written by the statement. |
| temp_blks_read | bigint | 无 | Total number of temp blocks read by the statement. |
| temp_blks_written | bigint | 无 | Total number of temp blocks written by the statement. |
| blk_read_time | double precision | 无 | Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). |
| blk_write_time | double precision | 无 | Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). |
中文版(简版)
| 参数名称 | 类型 | 参考 | 说明 |
|---|---|---|---|
| userid | oid | pg_authid.oid | |
| dbid | oid | pg_database.oid | 数据库id |
| queryid | bigint | 无 | |
| query | text | 无 | 查询语句 |
| calls | bigint | 无 | 执行次数 |
| total_time | double precision | 无 | 总耗时（毫秒） |
| min_time | double precision | 无 | 最短耗时 |
| max_time | double precision | 无 | 最长耗时 |
| mean_time | double 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 大表的索引使用情况
索引主要用于查询逻辑，一般用于大表。反过来，我们需要关注索引的使用情况。
日常维护，观察大表（所占存储空间大、记录多、索引所占空间大的表）和索引的情况，考虑是否需要为关键字段加索引、删掉未曾使用过的索引。
3.3.1 pg_stat_user_tables视图
pg_stat_all_tables视图将包含 当前数据库中每个表的一行（包括TOAST表），显示访问特定表的统计信息。 pg_stat_user_tables和 pg_stat_sys_tables视图 包含相同的信息，但是过滤只分别显示用户和系统表。
pgAdmin里面的表的分析数据大部分就是这里来的。
| 列 | 类型 | 描述 |
|---|---|---|
relid | oid | 表的OID |
schemaname | name | 此表的模式名 |
relname | name | 表名 |
seq_scan | bigint | 此表发起的顺序扫描数 |
seq_tup_read | bigint | 顺序扫描抓取的活跃行数 |
idx_scan | bigint | 此表发起的索引扫描数 |
idx_tup_fetch | bigint | 索引扫描抓取的活跃行数 |
n_tup_ins | bigint | 插入行数 |
n_tup_upd | bigint | 更新行数 |
n_tup_del | bigint | 删除行数 |
n_tup_hot_upd | bigint | HOT更新行数（比如没有更新所需的单独索引） |
n_live_tup | bigint | 估计活跃行数 |
n_dead_tup | bigint | 估计死行数 |
last_vacuum | timestamp with time zone | 最后一次此表是手动清理的（不计算VACUUM FULL） |
last_autovacuum | timestamp with time zone | 上次被autovacuum守护进程清理的表 |
last_analyze | timestamp with time zone | 上次手动分析这个表 |
last_autoanalyze | timestamp with time zone | 上次被autovacuum守护进程分析的表 |
vacuum_count | bigint | 这个表被手动清理的次数（不计算VACUUM FULL） |
autovacuum_count | bigint | 这个表被autovacuum清理的次数 |
analyze_count | bigint | 这个表被手动分析的次数 |
autoanalyze_count | bigint | 这个表被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_indexes和 pg_stat_sys_indexes视图包含相同的信息， 但是过滤只是分别显示用户和系统索引。
| 列 | 类型 | 描述 |
|---|---|---|
relid | oid | 这个索引的表的OID |
indexrelid | oid | 索引的OID |
schemaname | name | 索引中模式名 |
relname | name | 索引的表名 |
indexrelname | name | 索引名 |
idx_scan | bigint | 索引上开始的索引扫描数 |
idx_tup_read | bigint | 通过索引上扫描返回的索引项数 |
idx_tup_fetch | bigint | 通过使用索引的简单索引扫描抓取的活表行数 |
查看某个表索引使用情况：
-- 查看某个表索引使用情况
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
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
SELECT
kks.ID,
kksp.representativeid,
po.oname
FROM
test_table1 kks
LEFT JOIN test_table2 kksp ON kks.ID = kksp.storeid
LEFT JOIN test_table3 po ON po.ostructid = kksp.representativeid
WHERE
kks.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)
说明：
-
阅读顺序：从下到上（从左到右），（按照剪头->）我们可以看到是有层次的，我们应该从最底层（即最里面的）开始看。比如上面的例子，我们应该从第6(67)行开始看，然后是8(89)、5(56789)、3、1。
-
每行的大致规律：操作描述+（估计成本/代价/开销）+条件。
-
描述通常是说明这一步进行进行什么操作，比如第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。
-
-
详细的还会有每个步骤的Output、Buffers读写情况。如下面的第2行：Output: kks.id, kksp.representativeid, po.oname 是最后的输出，第4行：Buffers: shared hit=19 是指命中了19个数据块(页)。
详细的：
EXPLAIN (ANALYZE,VERBOSE,BUFFERS)
SELECT
kks.ID,
kksp.representativeid,
po.oname
FROM
test_table1 kks
LEFT JOIN test_table2 kksp ON kks.ID = kksp.storeid
LEFT JOIN test_table3 po ON po.ostructid = kksp.representativeid
WHERE
kks.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.oname
Join 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.id
Index Cond: (kks.id = 210399)
Heap Fetches: 1
Buffers: 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.oname
Inner Unique: true
Buffers: 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_time
Index 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.parentmemberid
Index 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/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 | 函数扫描，和具体的表无关 | 无启动时间 |
| Unique | distinct/union | 有启动时间 |
| Limit | limit/offset | 有启动时间 |
| Aggregate | count, sum,avg等聚集函数 | 有启动时间 |
| Group | group by | 有启动时间 |
| Append | union操作 | 无启动时间 |
| Materialize | 子查询 | 有启动时间 |
| SetOp | intersect/except | 有启动时间 |
4.4 查询规划器的依据–统计数据
4.4.1 统计数据
查询规划器基于表的统计数据对查询进行优化，自动选择它认为最佳的策略。
因此需要注意：
- 如果统计信息不准确，可能会导致策略和实际相差很远。因而分析前，建议先更新统计数据。
ANALYZE VERBOSE tablename;，统计信息在 pg_statistic 这个表中。 - 因为分析表时，使用的是抽样调查的方法，因此该统计信息天然就是不准确的。但是及时更新还是必要的，以保证统计数据不至于太落后。
这个表的可读性较差，我们可以看对应的视图：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目录中信息的访问。
| 名称 | 类型 | 引用 | 描述 |
|---|---|---|---|
schemaname | name | pg_namespace.nspname | 包含表的模式名 |
tablename | name | pg_class.relname | 表名 |
attname | name | pg_attribute.attname | 被此行描述的列名 |
inherited | bool | 如果为真，表示此行包括继承子列，不仅仅是指定表中的值 | |
null_frac | real | 列项中为空的比例 | |
avg_width | integer | 列项的平均字节宽度 | |
n_distinct | real | 如果大于零，表示列中可区分值的估计个数。如果小于零，是可区分值个数除以行数的负值（当ANALYZE认为可区分值的数量会随着表增长而增加时采用负值的形式，而如果认为列具有固定数量的可选值时采用正值的形式）。例如，-1表示一个唯一列，即其中可区分值的个数等于行数。 | |
most_common_vals | anyarray | 列中最常用值的一个列表（如果没有任何一个值看起来比其他值更常用，此列为空） | |
most_common_freqs | real[] | 最常用值的频率列表，即每一个常用值的出现次数除以总行数（如果most_common_vals为空，则此列为空） | |
histogram_bounds | anyarray | 将列值划分成大小接近的组的值列表。如果存在most_common_vals，其中的值会被直方图计算所忽略（如果列类型没有一个<操作符或者most_common_vals等于整个值集合，则此列为空） | |
correlation | real | 物理行顺序和列值逻辑顺序之间的统计关联。其范围从-1到+1。当值接近-1或+1时，在列上的一个索引扫描被认为比值接近0时的代价更低，因为这种情况减少了对磁盘的随机访问（如果列数据类型不具有一个<操作符，则此列为空） | |
most_common_elems | anyarray | 在列值中，最经常出现的非空元素列表（对标度类型为空） | |
most_common_elem_freqs | real[] | 最常用元素值的频度列表，即含有至少一个给定值实例的行的分数。在每个元素的频度之后有二至三个附加值，它们是每个元素频度的最小和最大值，以及可选的空元素的频度（如果most_common_elems为空，则此列为空） | |
elem_count_histogram | real[] | 在列值中可区分非空元素值计数的一个直方图，后面跟随可区分非空元素的平均数（对于标度类型为空） |
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 什么时候走索引？）
-
什么是索引： 索引是数据库中用于提高查询效率的技术，类似于目录，一个索引是存储的表中一个特定列的值数据结构（最常见的是B-Tree）。
-
为什么需要索引：加快查询速度。
-
如何创建索引： 加
create index index_name on table_name using USING btree (column_name); -
什么时候走索引：比较大的表、条件有一定筛选行。
5.2 探索：如何创建索引？
Navicat创建一个索引可以设置的项，打勾的是我认为我们需要注意的。
5.2.1 索引的名字
命名无特别要求，只需要满足命名规则即可，然后这个名字不常用，但是建议是见文知意。比如 idx_test_table_address，或者是pg_class_oid_index（官方的）。
5.2.2 索引的字段
- 单列索引：最普通的。
- 部分索引：只把符合条件的值放到索引中 USING btree (userid) WHERE (status= 1)
- 多列索引（组合索引）：把常用的几个关联的字段，同时组合成索引。 USING btree (userid, reportmonth);
- 唯一索引
CREATE UNIQUE INDEX ……
5.2.3 索引的方法(类型)
PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。
- btree 满足日常大部分场景，如果不知道用啥就它了！
- hash 等值操作。
5.2.4 索引的运算符类别
一个索引定义可以为索引中的每一列都指定一个操作符类。
11.10. 操作符类和操作符族 (postgres.cn)
问题引入：索引列不能进行复杂计算、函数操作等，不然会导致索引失效（右置操作，把需要操作的数据放在右边，把索引列放在左边，然后进行比较）。比如createtime有索引，where to_char(createtime,‘yyyy-mm’) = ‘2022-01’ 为什么不走索引？
索引和列一样，都有自己的操作符，以简单的单列索引为例，默认会创建一个对应类型的索引列，如下表。每个操作符类有支持的操作类型，如果实际操作数类型不符，那么就不会走索引。
即创建时间其实为：USING btree (createtime “pg_catalog”.“timestamp_ops”)
| 类型 | 操作符类别 |
|---|---|
| int4 | int4_ops |
| int8 | int8_ops |
| timestamp | timestamp_ops |
| varchar | text_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……
全剧终🎉🎉🎉。
2323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
