1：数据库性能常用的度量指标

（1）运行时间 run time??

（2）QPS：Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

（3）TPS：是TransactionsPerSecond的缩写，也就是事务数/秒。它是软件测试结果的测量单位。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。

?

2：数据库性能常见的依赖因素

（1）环境

a: 机器配置：

? ? ? ?磁盘、存储文件系统规划；

? ? ? ?CPU主频、核数;? ??

? ? ? ? linux OS系统内核配置：/etc/sysctl.conf; (配置生效时: sysctl -p) ；blog/20170412_04.md at master · digoal/blog · GitHub

????????网络带宽时延、网络带宽、防火墙；

? ? ? ? 资源限制：/etc/security/limits.conf

?b: HA\集群架构（同步、异步复制策略）

?c: 数据库参数（postgresql.conf 刷盘策略等, 下面会介绍一些常用的参数设置技巧）

（2）业务场景

表定义

数据量

SQL

事务隔离级别

并发度

3：数据库运行时间消耗

(1) 客户端：建连\断连， SQL发送、 结果集接受、处理

(2) 数据库服务端: 通信层(采用连接池、用户态网络、逻辑连接)， SQL优化（优化）\执行层（线程池），写日志（并发）、数据刷盘（刷页线程、shared）、结果输出

(3) 网络延迟

4： SQL执行时间分析

SQL--->查询解析（词法分析token keyword、语法分析parsetree、语义分析querytree）---> 查询优化 （查询重写）---> 优化器 （代价估算、生成执行计划 plantree） -------> 绑参 ------> 执行器 ----->存储引擎

5： 逻辑上常见的SQL执行顺序【】

【7】select 【8】distinct 【11】<select_list>

【1】from <left_table>

【3】<join_type> join <right_table>

【2】on <join_condition>

【4】where <condition>

【5】group by <group_list>

【6】having <having_condition>

【9】order by <order_list>

【10】limit <limit_offset>

6： 不同执行计划的选择 （explain的使用：PgSQL · 最佳实践 · EXPLAIN 使用浅析）

（1）如何扫描表：

????????Seq Scan ：全表顺序扫描， 一般查询没有索引的表需要全表扫描。

????????Index Scan： 索引扫描， 主要用来再where条件中存在索引列是的扫描。（建表的时候可以创建索引列）。一般由于Seq Scan。

????????Bitmap index Scan：一种索引扫描， 节点每次执行返回的是一个位图而不是一个元组，其中位图中每位代表了一个扫描到的数据块。这样做最大的好处就是把Index Scan 的随机读转换成了按照数据块的物理顺序读取，在数据量比较大的时候，这会大大提升扫描的性能。

? ? ? ? ?Bitmap heap?Scan：?作为BitmapIndex Scan 的父节点， 将BitmapIndex Scan 返回的位图转换为对应的元组。

? ? ? ? 如何选择：

? ? ? ? [1]:? 大多数情况下，Index Scan 要比 Seq Scan 快。但是如果获取的结果集占所有数据的比重很大时，这时Index Scan 因为要先扫描索引再读表数据反而不如直接全表扫描来的快。

? ? ? ? [2]:? 如果获取的结果集的占比比较小，但是元组数很多时，可能Bitmap Index Scan 的性能要比Index Scan 好。

? ? ? ? 代价估计信息：

????????cost 就是该执行节点的代价估计。它的格式是xxx..xxx，在.. 之前的是预估的启动代价，即找到符合该节点条件的第一个结果预估所需要的代价，在..之后的是预估的总代价。而父节点的启动代价包含子节点的总代价。

????????rows 代表预估的行数， 根据表的统计信息预估来；

????????width 代表预估的结果宽度，单位字节。根据表的统计信息预估来。

? ? ? ? 真实执行信息：

????????当EXPLAIN 命令中ANALYZE 选项为on时，会在代价估计信息之后输出真实执行信息，包括：

actual time 执行时间，格式为xxx..xxx，在.. 之前的是该节点实际的启动时间，即找到符合该节点条件的第一个结果实际需要的时间，在..之后的是该节点实际的执行时间rows 指的是该节点实际的返回行数loops 指的是该节点实际的重启次数。如果一个计划节点在运行过程中，它的相关参数值（如绑定变量）发生了变化，就需要重新运行这个计划节点。

（2）如何join

? ? ? ? Nested Loop

? ? ? ? Hash

? ? ? ? Merge

? (3) 如何排序

（4）如何处理子查询

（5）其他等价的执行计划

7 SQL优化器如何选择执行计划

（1）RBO 基于规则的优化器、动态适应性差

（2）CBO 基于代价的优化器、目前关系数据库的主流技术

8: 如何估算行数： eg： explain select * from test_t;

显示的执行计划中rows=200001

这个行数估算来自pg_class系统表。reltuples表示表中行的数量，该值只是被优化器使用的一个估计值。

估算值：select? reltuples from pg_class where relname='test_t';

返回reltuples = 200001

实际值：select count(*) from test_t;

返回行数为了201001。实际值和估算值不超过5%

eg: explain select * from test_t where col_val = XXX; (等值where条件场景的行数估算)

显示的执行计划中rows=300112， Filter:(cl_val = 1)

这个函数估算来自pg_class系统表和pg_stats系统表.?pg_stats记录的是每个表每个字段的统计信息。用于优化器做执行计划选择提供参考。tablebname (pg_class.relname)表示表的名字；attname(pg_attribute.attname)表示字段的名字。

where 的估算值： select * from pg_stats where tablename = 'test_t' and 他他那么= 'col_val';

9: 选择合适的索引：

PG支持非常丰富的索引方法，例如btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap (greenplum extend)，用户可以根据不同的数据类型，以及查询的场景，选择不同的索引。

(1) b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。索引与递归查询结合，还能实现快速的稀疏检索。

(2)?hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。

hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。

更多索引相关参看链接PostgreSQL 9种索引的原理和应用场景-阿里云开发者社区

10: SQL优化tips：

(1) 优先使用预编译语句执行DML，可提升安全性和性能

(2) 尽量避免长事务或大型事务，会导致垃圾膨胀或并发事务锁等待。?

(3) 避免使用 select * ,采用具体的字段。不要使用count(*)判断是否有数据、建议用limit 1

(4) 不要再索引列进行类型转换、数学运算等

(5) 合理选择union 、 union all

11: 数据库访问优化漏斗法则（从业务的全局优化）：

?(1) 减少数据访问（减少磁盘访问） ????????索引，减少全表扫描 (2) 返回更少数据（减少网络传输或磁盘访问） ????????根据实际业务需求返回所需数据 (3) 减少交互次数（减少网络传输） ?????????缓存 ????????存储过程 ????????批量查询 (4) 减少服务器CPU开销（减少CPU几内存开销） ????????批量处理 ????????在客户端处理大量负责运算 (5) 利用更多资源（增加资源） ????????硬件资源

12: PG的锁等待问题：

pg_locks? + pg_thread_wait_status + pg_stat_activity

PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁-阿里云开发者社区

13: linux 常用的数据库性能分析、问题诊断工具

（1）性能分析： top、htop、iotop、vmstat、sar 、netstat、perf、iostat、perftop

Linux系统调优常用的命令_写的不是代码的博客-CSDN博客_linux调优命令

? (2)? 问题诊断：gdb、gstack、gcore、lsof、tcpdump?

14: PG外部GUI管理工具

(1) pgadmin4

(2) DBeaver

15：性能视图 pg_stat*

postgres=# \d+ pg_stat*

postgres=# select relname from pg_class where relname like 'pg_stat%';

16： sql语句调优?

(1) 查询字段优化不要使用 SELECT * 用具体的字段列表替换 * ，不要返回用不到的字段。

????????返回了不必有的数据，就会浪费内存，加重网络的负担降低性能 。如果表大，在表扫描的期间将表锁住，禁止其他的链接访问表，后果严重！！

(2) where 子句 like调优：

????????like的关键键词前面用了“%”，会导致该Sql走全表查询，除非必要，否则不要在关键词前加%。查询耗时和字段值总长度成正比。

SELECT id FROM A WHERE name LIKE 'abc%'；（correct）

SELECT id FROM A WHERE name LIKE '%abc%'；(error)

(3)?where子句 避免对null做判断。

????????该判断将导致引擎放弃使用索引而进行全表扫描，建议针对null字段设置默认值0。

(4) where 子句避免使用!=

????????该判断将导致引擎放弃使用索引而进行全表扫描，建议将不等于 拆成 大于或者小于

(5)??where子句 避免使用 or

????????使用or的子句可以分解成多个查询，并且通过union链接多个查询。它们的速度只同是否使用索引有关，如果查询使用到联合索引，用unionAll执行的效率更高，多个or字段的字句没有用到索引，改写成union的形式，再视图与索引匹配。

? ? ? ? select id from tb1 where a > 22 or? a < 22; (error)

? ? ? ? select id form tb1 where a > 22 union all? select id from tb1 where a < 22; (correct)

(6)? 避免使用distinct 和 order by;

????????它会使查询变慢，这些动作可以改在客户端执行也可以

(7) group by 和 having的优化

????????如果能在group by的having子句之前就尽量剔除多余的行，不要用他们来做剔除行的动作。

? ? ? ? a: select 的where语句选择所有合适的行。b: group用来分组统计。c: having用于剔除多余的分组。

????????这样group by和having的开销小，查询快。对于大的数据进行分组和having十分消耗资源。如果group by的目的不包括计算，只是分组。Distinct更快

?(8) union all 比 union 快

????????UNION在进行表链接后会筛选掉重复的记录，UNION ALL不会去除重复记录。

????????UNION将会按照字段的顺序进行排序，UNION ALL只是简单的将两个结果合并后就返回

（9）一次更新多条记录比分多次更新，每次更新一条快。

? ? ? ? 批量处理更有效率。

? ? ? ? insert into tb1(id, a) values(1, 10);? ? (error)

? ? ? ? insert into tb1(id, a) values(2, 16);? ? (error)

? ? ? ? insert into tb1(id, a) values(1, 10), (2, 16);? ? ?(correct)

? ?(10) where 子句避免在条件左侧使用算法

????????? ?在where子句中的“=”左边进行函数、算数运算或其他表达式运算，系统可能无法正确的使用索引。

? ? ? ? select * from tb1?where a/2 = 100; (error)

? ? ? ? select * from tb1 where substring(a, 1, 4) = '666';?? (error)

????????

? ? ? ? select * from tb1 where a = 100 * 2;???(correct)

? ? ? ? select * from tb1 where a like '666%';???(correct)

17： postgres内核参数配置（postgesql.conf）

PostgreSQL配置优化_Kyle__Shaw的博客-CSDN博客_postgresql 优化

18：PG的查询优化

app(sql语句) --> 解析器 （生成查询树） --> 重写器 （重写后的查询树） ---> 优化器 （执行计划）--> 执行器。

查询优化包括：

a: 物理优化：单表优化、两表优化、大于两表的优化

b: 逻辑优化：? 子查询优化、等价谓词\条件表达式优化、外链接优化。

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。