达梦数据库DM8性能诊断与优化

        下从几个方面共同学习达梦数据库DM8性能诊断与优化：查询优化基本思路、执行计划详解、索引与统计信息详解等。

一、优化的基本思路

        正常情况下，我们在遇到数据库性能问题的时候会从以下几个维度去排查锁定问题的根源，从而通过各种手段来进行优化，与达到预期或者较好的效果，一般从下面3个方面分析：

• 操作系统层面：内存使用率、cpu负载、I/O负载、磁盘读写、网络状态等。
• 实例层面：包含数据库架构的优势即缺陷、实例参数的设置。
• ​​​​​​​SQL层面：慢SQL、执行计划、索引、统计信息、表设计、这段选取等。

1、操作系统层面相关排查

1、使用top命令查看cpu使用率
​​​​​​​​​​​​​​    top主要查看总体操作系统各方面整体情况，一边快速锁定负载异常，更好去排查，top主要包含①当前系统时间(在这期间没有重启过)②用户登陆情况伞③任务(进程)，系统现在共X进程，其中处于运行中、休眠的、stoped状态的、zombie状态(僵尸)的信息。④cpu、内存、swap状态等。
2、使用iostat命令查看磁盘I/O使用情况
    iostat命令来简单查看磁盘的使用情况，可以每1秒不断刷新最新的磁盘使用情况，方便连续观察，根据硬件找好参考标准判断读写及磁盘I/O。
3、使用dstat工具查看磁盘I/O使用情况
    dstat是一个全能型的系统信息统计工具，可以替代vmstat、iostat、ifstat等。在执行dstat命令时，可以很容易的监控到系统CPU、I/0、网络等相关信息，默认每秒刷新一条信息，并且可以统计输出csv文件等。
常用的一些例子：
①查看当前内存、I/O、cpu占用最高的进程信息
    dstat --top-mem --top-io --top-cpu
②将cpu、磁盘、网络信息标准输出至csv中
    dstat -cdn --output /tmp/wh.csv
③3秒收集一次数据，输出5次后中断。
    dstat --noupdate 3 5
4、使用free命令查看内存使用情况
    使用free可知mem内存和swap交换分区的total物理内存总量、used已使用量、free内存空闲量、share可共享内存总额以及Buffers/cached磁盘缓存的大小。
5、使用nmon工具监控系统一段时间的整体情况
​​​​​​​    例如：nmon -s10 -c60 -f -m /home/
        参数解释：

• -s10 每 10 秒采集一次数据。
• -c60 采集 60 次，即为采集十分钟的数据。
• -f 生成的数据文件名中包含文件创建的时间。
• -m 生成的数据文件的存放目录。

6、使用perf top命令查看系统热点情况
        perf 是一个调查 Linux 中各种性能问题的有力工具。例如：perf top -e cpu-clock查看CPU的使，perf top -e block:block_rq_issue : 查看系统IO的请求。可以在发现系统IO异常时，可以使用该命令进行调查，就能指定到底是什么原因导致的IO异常。 block_rq_issue 是block_request_issue 就是IO请求数。其实从这些可以看出，分析和调查Linux上的各种性能问题，需要我们对Linux内核有比较多的了解，不然恐怕是无从下手的。

        最后根据结果分析：如果发现数据库主机的cpu、I/O、内存等使用率很高，往往说明数据库存在性能瓶颈。也可能是硬件本身存在问题，但这种可能性比较小，也容易排除。

2、数据库架构优化

        关于架构优化这里不详细在叙述了，之前写的一篇博客里详细介绍过了。博客链接地址：

CSDNhttps://mp.csdn.net/mp_blog/creation/editor/123199958

3、达梦数据库参数优化

​​​​​  例如有一个密集交易型数据库服务器配置如下：CPU:4路8核    内存：256G   磁盘阵列：1T。

参数名	含义	优化建议	默认值	建议值
MEMORY_POOL	公共内存池，单位为M。	高并发时应调大，避免频繁向OS申请内存	80	2048
N_MEM_POOLS	将公共内存池分片，减少并发访问冲突，单位为个。		4	4
BUFFER	数据缓冲区，单位为M。	如果数据量小于内存，则设置为数据量大小；否则设置为总内存的2/3比较合适	1000	120000
BUFFER_POOLS	BUFFER的分区数，一般配置为质数，取值范围为1~500,当MAX_BUFFER>BUFFER时，动态扩展的缓冲区不参与分区	并发较大的系统需要配置这个参数，减少数据缓冲区并发冲突，建议BUFFER=MAX_BUFFER	1	101
MAX_BUFFER	数据缓冲区扩展最大值	建议配置成=BUFFER	1000	120000
RECYCLE	用于缓冲临时表空间，单位为M	高并发或大量使用with、临时表、排序等应该调大点	64	5000
SORT_BUF_SIZE	排序缓存区，单位M	建索引时调大点，平时默认	2	32
CACHE_POOL_SIZE	用于缓存SQL、执行计划、结果集等	一般配置为1000M~4000M	10	1024
DICT_BUF_SIZE	数据字典缓存区，单位M	用于缓存数据字典，默认5M，系统中对象个数较多时适当加大	5	32
HJ_BUF_GLOBAL_SIZE	哈希连接使用的内存空间上限，单位M	高并发、hash操作多应调大	500	5000
HJ_BUF_SIZE	单个哈希连接使用的内存	有大表的hash连接应调大	50	500
HAGR_BUF_GLOBAL_SIZE	聚集操作使用的内存上限，单位M	高并发、大量的聚集操作如sum等应调大	500	5000
HAGR_BUF_SIZE	单个聚集操作使用的内存	有大表的hash分组应调大	50	500
WORKER_THREADS	工作线程的个数	建议设置为cpu核算或其两倍 1~64	4	32
ENABLE_MONITOR	数据库系统监控的级别	性能优化时设置为3，运行时设为2	2	2或者3
OLAP_FLAG	启用联机分析处理，0：不启用；1：启用；2：	联机交易系统建议设置为2，联机分析系统建议设置为1	0	2
	不启用，同时倾向于使用索引范围扫描
OPTIMIZER_MODE	优化器计划探测模式。设置为1时，采用了左深树方式进行探测，设置为0时，则采用的是卡特兰树方式进行探测	2016年以后的版本建议设置为1，采用新优化器	0	1

        以上是常见的一些参数，当然还包含一些隐藏参数等，根据具体问题对涉及的参数进行详细分析微调，参数的把握度一般是根据业务和数据量及并发场景又微调到精调。

二、SQL层面优化

        数据库的性能问题最终都要涉及到SQL优化，处理流程基本步骤：生成日志——>日志入库——>分析SQL——>优化方案。对于SQL我们一般优化的原则符合：

• 最优先处理：数量很少(5%)，但是执行频率非常高，甚至达到每秒上百次，只要一慢，系统很可能瘫痪。（并发非常高）
• 次优先处理：占大多数(80%)，如果有慢的，对系统整体稳定性影响不大，但是会造成局部的某些操作慢（并发一般）
• 最后处理：数量少(15%)，往往是很复杂的查询，可能一天就执行几次，对系统整体影响不大，但是优化难度很大。（并发很少特别慢）

1、建立索引   

        首先索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

        索引结构（索引底层的数据结构）包含：二叉树、红黑树、Hash、B-树（B+tree）。至于DM这边索引和oracle一样是btree。

建立索引的主要的一些原则有：

1. 建立唯一索引。唯一索引能够更快速地帮助我们进行数据定位；
2. 为经常需要进行查询操作的字段建立索引；
3. 对经常需要进行排序、分组以及联合操作的字段建立索引；
4. 在建立索引的时候，要考虑索引的最左匹配原则（在使用SQL语句时，如果where部分的条件不符合最左匹配原则，可能导致索引失效，或者不能完全发挥建立的索引的功效）；
5. 不要建立过多的索引。因为索引本身会占用存储空间；
6. 如果建立的单个索引查询数据很多，查询得到的数据的区分度不大，则考虑建立合适的联合索引；
7. 尽量考虑字段值长度较短的字段建立索引，如果字段值太长，会降低索引的效率。

影响CBO的因素有哪些？

1、索引的原则性约束问题。

2、数据分布问题，比如索引列包含 null，数据直方图不均衡，影响聚簇因子。

3、表的扫描方式合理性。

• 索引唯一扫描(index unique scan)
• 索引范围扫描(index range scan)
• 索引全扫描(index full scan)
• 索引快速扫描(index fast full scan)
• 索引跳跃扫描(index skip scan)

4、表的连接方式

• 嵌套循环（Nested  Loops  ）所以嵌套循环一般适合于驱动表记录集比较少（<10000）且内表有高效索引访问方式。
• 哈希连接（Hash Join ）Hash join一般用于一张小表和一张大表进行join时。在绝大多数情况下，hash join效率比其他join方式效率更高。

• 排序合并连接(Sort Merge Join (SMJ) )：通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。

• 群集连接 (CLUSTER JOIN)  很少用到。

• 笛卡尔连接 (CARTESIAN JOIN) 笛卡尔连接是指在sql语句中没有写出表连接的条件，优化器把第一个表的每一条记录和第二个表的所有纪录相连接。

5、索引分裂等。

• 9-1分裂：绝大部分数据还保留在旧有节点上，仅有非常少的一部分数据迁移到新节点上。
• 5-5分裂：旧节点和新节点上的数据比例几乎是持平的。

2、在DM数据库中，如何定位慢的 SQL？

2.1开启跟踪日志记录执行 SQL

       跟踪日志文件是一个纯文本文件，以 dmsql_实例名_日期_时间命名， 默认生成在 DM 安装目录的 log 子目录下。跟踪日志内容包含系统各会话执行的 SQL 语句、参数信息、错误信息、执行时间等。跟踪日志主要用于分析错误和分析性能问题，基于跟踪日志可以对系统运行状态进行分析。

跟踪日志配置方式

根据需要配置数据文件目录下的 sqllog.ini，如下所示：

BUF_TOTAL_SIZE = 10240 #SQLs Log Buffer Total Size(K)(1024~1024000) BUF_SIZE = 1024 #SQLs Log Buffer Size(K)(50~409600) BUF_KEEP_CNT = 6 #SQLs Log buffer keeped count(1~100) [SLOG_ALL] FILE_PATH = ../log PART_STOR = 0 SWITCH_MODE = 1 SWITCH_LIMIT = 100000 ASYNC_FLUSH = 0 FILE_NUM = 200 ITEMS = 0 SQL_TRACE_MASK = 2:3:23:24:25 MIN_EXEC_TIME = 0 USER_MODE = 0 USERS =

配置 dm.ini 中 SVR_LOG = 1 启用 sqllog.ini 配置，该参数为动态参数，可通过调用数据库函数直接修改，如下所示：

SP_SET_PARA_VALUE(1,'SVR_LOG',1);

如果对 sqllog.ini 进行了修改，可通过调用以下函数即时生效，无需重启数据库，如下所示：

SP_REFRESH_SVR_LOG_CONFIG();

2.2根据跟踪日志查找慢 SQL

当 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 打开时，显示系统最近 1000 条执行时间超过预定值的 SQL 语句。默认预定值为 1000 毫秒。可通过 SP_SET_LONG_TIME 系统函数修改，通过 SF_GET_LONG_TIME 系统函数查看当前值。

--两个参数均为动态参数，可直接调用系统函数进行修改 SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1); SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

注意：

        通过 SP_SET_PARA_VALUE 方式修改的参数值仅对当前会话以及新建会话生效，对其它已建立会话不生效。

查询方式

超过执行时间阈值的 SQL 语句记录在 V$LONG_EXEC_SQLS 系统视图中。

查询该视图获取结果，如下所示：

SELECT * FROM V$LONG_EXEC_SQLS;

3、统计信息

①创建系统包

SP_CREATE_SYSTEM_PACKAGES(1);

--更新单列统计信息

DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_TJ',null,100,false, 'FOR ALL COLUMNS SIZE AUTO'); --更新所有列

SP_COL_STAT_INIT_EX(USER,'TEST_TJ','ID',100); --更新单列。

②查看统计信息：频率直方图

 DBMS_STATS.COLUMN_STATS_SHOW(USER, 'TEST_TJ','AGE');

--1.类型：频率直方图

--2.ENDPOINT_VALUE样本值： 1

--3.ENDPOINT_HEIGHT 样本值的个数:819

SELECT COUNT(*) FROM TEST_TJ WHERE AGE=1; --819

③查看统计信息：等高直方图

DBMS_STATS.COLUMN_STATS_SHOW(USER, 'TEST_TJ','ID');

--解读统计信息

--1.类型：等高直方图

--2.ENDPOINT_VALUE样本值： 30

--3.ENDPOINT_HEIGHT小于样本值大于前一个样本值的个数:329

  SELECT COUNT(*) FROM TEST_TJ WHERE ID<30;  --329

--4.ENDPOINT_KEYGHT样本值的个数:11

  SELECT COUNT(*) FROM TEST_TJ WHERE ID=30;  --11

--5.ENDPOINT_DISTINCT小于样本值大于前一个样本值之间不同样本的个数: 30

  SELECT COUNT(DISTINCT ID) FROM TEST_TJ WHERE ID<30;  --30​​​​​​​

三、执行计划解读

1、达梦执行计划操作符介绍

达梦中执行计划涉及到的一些主要操作符有：

• CSCN ：基础全表扫描(a)，从头到尾，全部扫描
• SSCN ：二级索引扫描(b), 从头到尾，全部扫描
• SSEK ：二级索引范围扫描(b) ，通过键值精准定位到范围或者单值
• CSEK ：聚簇索引范围扫描© ，通过键值精准定位到范围或者单值
• BLKUP ：根据二级索引的ROWID 回原表中取出全部数据(b + a)

2、表扫描方式

1. NSET:收集结果集        ​​​​​​​​​​​​​​说明：用于结果集收集的操作符， 一般是查询计划的顶层节点。
2. PRJT：投影                说明：关系的“投影”(project)运算，用于选择表达式项的计算;广泛用于查询，排序，函数索引创建等。
3. SLCT：选择               说明：关系的“选择” 运算，用于查询条件的过滤。
4. AAGR：简单聚集       说明：用于没有group by的count，sum，age，max，min等聚集函数的计算。
5. FAGR：快速聚集        说明：用于没有过滤条件时从表或 索引快速获取 MAX/MIN/COUNT值，DM数据库是世界上单表不带过滤条件下取COUNT值最快的数据库。
6. HAGR：HASH分组聚集    说明：用于分组列没有索引只能走全表扫描的分组聚集，C2列没有创建索引。
7. SAGR：流分组聚集      说明：用于分组列是有序的情况下可以使用流分组聚集，C1上已经创建了索引，SAGR2性能优于HAGR2。
8. BLKUP：二次扫描       说明：先使用2级别索引定位，再根据表的主键、聚集索引、 rowid等信息定位数据行。
9. CSCN：全表扫描      说明：CSCN2是CLUSTER INDEX SCAN的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。在一个高并发的系统中应尽量避免全表扫描。
10. SSEK、CSEK、SSCN：索引扫描​​​​​​​

​​​​​​​说明：

        SSEK2是二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID等信息去扫描表；
        CSEK2是聚集索引扫描只需要扫描索引，不需要扫描表；
        SSCN是索引全扫描，不需要扫描表。

3、表链接方式

嵌套循环连接（NEST LOOP）
        两层嵌套循环结构，有驱动表和被驱动表之分。 选定一张表作为驱动表，遍历驱动表中的每一行，根据连接条件去匹配第二 张表中的行。驱动表的行数就是循环的次数，这个很大程度影响了执行效率。
        选择小表作为驱动表。统计信息尽量准确，保证优化器选对驱动表。大量的随机读。如果没有索引，随机读很致命，每次循环只能读一块，不能读多块。使用索引可以解决这个问题。
        使用场景：

• 驱动表有很好的过滤条件。
• 表连接条件能使用索引。
• 结果集比较小。

哈希连接（hash join）
        使用较小的Row source 作为Hash table和Bitmap， 而第二个row source被hashed，根据bitmap与第一个row source生成的hash table 相匹配，bitmap查找的速度极快。
hash join特点：

• 一般没索引或用不上索引时会使用该连接方式。
• 选择小的表(或row source)做hash表。
• 只适用等值连接中的情形。

由于hash连接比较消耗内存，如果系统有很多这种连接时，需调整以下3个参数:

• HJ_BUF_GLOBAL_SIZE
• HJ_BUF_SIZE
• HJ_BLK_SIZE

排序合并连接（MERGE SORT）

无驱动表之分，随机读很少。两个表都需要按照连接列排序，需要消耗大量的cpu和额外的内存。
应用场景：
        通常情况下，merge sort join需要消耗大量的cpu和内存，效率都不会太高。如果存在相关索引可以消除sort，那么CBO可能会考虑该连接方式。

4、索引常见优化手段

• 索引下推​​​​​​​
          优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

• 视图拆分​​​​​​​
  视图拆分有很多限制，如果视图查询中含有distinc、union、 group by等操作，优化器就无法进行视图拆分。

• 谓词推入​​​​​​
  是cbo一种优化的行为，但是谓词推入并不总是好的，如果谓词被推入后缩小了结果集，降低了逻辑读，那就是好的，反之就不太好，其实cbo并不是只要有谓词推入的机会就会推入，由于统计信息不准确，倾斜的列上没有直方图，都会导致cbo在计算cost的时候产生较大偏差，而对于大的业务表，数据变化频繁，也不能总收集统计信息，所以这时候我们就需要去找到合适的hint来协助cbo做出最优的选择。​​​​​​​

最后分享一下SQL优化法则

• 全值匹配我最爱，最左前缀要遵守。带头大哥不能死，中间兄弟不能断。
• 索引列上少计算，范围之后全失效。like百分写最右，覆盖索引不写星。
• 不等空值还有or，索引失效要少用。var引号不可丢，SQL高级也不难。

到此文章结束，请耐心阅读~
更多达梦技术资讯，请访问达梦技术社区：
达梦数据库 - 新一代大型通用关系型数据库 | 达梦云适配中心
https://eco.dameng.com/