慢sql和统计信息收集

慢SQL：

慢SQL通常指的是执行时间超过预定阈值的SQL语句。这些语句可能会影响数据库的性能，因此需要特别关注。慢SQL的记录和分析可以帮助数据库管理员识别系统瓶颈，优化查询。

统计信息：

统计信息是数据库用来优化查询的信息，比如表的行数、索引的分布情况等。数据库管理员需要定期更新这些统计信息，以保证查询优化器能够正确地选择最优的执行计划。

网站运行久了，web页面加载出现转圈和加载慢的情况，通过查看日志是慢SQL导致的，如果你对国产的达梦数据库不是很熟悉，那么怎么定位呢？

根因分析

数据库出现慢sql的原因基本上有如下几点

1. 数据库节点资源不足，负载高导致的慢

2. web节点和数据库节点网络有延迟

3. SQL语句没有索引，当网站运行数据量越来越大后出现慢sql

4. 数据库统计信息太旧了，导致SQL运行没走索引

本章主要讲达梦数据库在第4种情况下的问题处理，达梦数据库可以兼容oracle，所以运维过oracle的小伙伴，达梦就比较简单了。

找到慢SQL

查询超过执行时间阈值的 SQL 语句

可通过查询 V$LONG_EXEC_SQLS 系统视图获取结果：

SELECT * FROM V$LONG_EXEC_SQLS;

查询结果字段详细信息介绍如下表所示：

列名	说明
SESS_ID	会话 ID，会话唯一标识
SQL_ID	语句 ID，语句唯一标识
SQL_TEXT	SQL 文本
EXEC_TIME	执行时间（毫秒）
FINISH_TIME	执行结束时间
N_RUNS	执行次数
SEQNO	编号
TRX_ID	事务号

了解执行计划

数据库会按照SQL接方式、连接顺序、访问路径生成不同的执行计划，选择代价最小的一个作为最终的执行计划。

一般情况下，索引访问用于检索表的小部分数据，全表扫描用于访问表的大部分数据。从数据库中定位和检索数据的方法有：全表扫描、聚集索引扫描、二级索引扫描等。

全表扫描是指从基表中检索数据时，扫描该表中所有的数据。全表扫描方式适合检索表中大部分数据，这时比索引扫描更加有效率。

索引扫描是指通过指定语句中的索引列进行遍历来检索表中的数据。索引扫描是从基于一列或多列的索引中检索数据。索引不仅包含索引值，还包含对应表中数据的 ROWID。如果需要访问的不是索引列，这时需要通过 ROWID 或聚集索引来找到表中的数据行。

达梦数据库可通过两种方式查看执行计划。

方式一：通过 DM 数据库配套管理工具查看。

方式二：使用 explain 命令查看。

以下对两种查看方式进行介绍。

（1）管理工具查看执行计划

在 DM 配套管理工具中，选中待查看执行计划的 SQL 语句，点击工具栏中的按钮，或使用快捷键 F9，即可查看执行计划。

（2）使用 explain 命令查看执行计划

执行计划是 SQL 语句的执行方式，由查询优化器为语句设计的执行方式，交给执行器去执行。在 SQL 命令行使用 EXPLAIN 可以打印出语句的执行计划。

例如：

建表和建索引语句：

CREATE TABLE T1(C1 INT,C2 CHAR);
CREATE TABLE T2(D1 INT,D2 CHAR);
CREATE  INDEX  IDX_T1_C1  ON  T1(C1);
INSERT INTO T1 VALUES(1,'A');
INSERT INTO T1 VALUES(2,'B');
INSERT INTO T1 VALUES(3,'C');
INSERT INTO T1 VALUES(4,'D');
INSERT INTO T2 VALUES(1,'A');
INSERT INTO T2 VALUES(2,'B');
INSERT INTO T2 VALUES(5,'C');
INSERT INTO T2 VALUES(6,'D');

打印执行计划：

  EXPLAIN SELECT A.C1+1,B.D2 FROM T1 A, T2 B WHERE A.C1 = B.D1;

执行计划如下：

 1   ##NSET2: [0, 16, 9] 
 2     ##PRJT2: [0, 16, 9]; EXP_NUM(2), IS_ATOM(FALSE) 
 3       ##NEST LOOP INDEX JOIN2: [0, 16, 9] 
 4         ##CSCN2: [0, 4, 5]; INDEX33555535(B)
 5         ##SSEK2: [0, 4, 0]; SCAN_TYPE(ASC), IDX_T1_C1 (A), SCAN_RANGE[T2.D1,T2.D1]

这个执行计划看起来就像一棵树，执行过程为：控制流从上向下传递，数据流从下向上传递。其中，类似[0, 16, 9]这样的三个数字，分别表示估算的操作符代价、处理的记录行数和每行记录的字节数。同一层次中的操作符，如本例中的 CSCN2 和 SSEK2，由父节点 NEST LOOP INDEX JOIN2 控制它们的执行顺序。

该计划的大致执行流程如下：

1. CSCN2: 扫描 T2 表的聚集索引，数据传递给父节点索引连接；

2. NEST LOOP INDEX JOIN2: 当左孩子有数据返回时取右侧数据；

3. SSEK2: 利用 T2 表当前的 D1 值作为二级索引 IDX_T1_C1 定位查找的 KEY，返回结果给父节点；

4. NEST LOOP INDEX JOIN2: 如果右孩子有数据则将结果传递给父节点 PRJT2，否则继续取左孩子的下一条记录；

5. PRJT2: 进行表达式计算 C1+1, D2；

6. NSET2: 输出最后结果；

7. 重复过程 1) ~ 4)直至左侧 CSCN2 数据全部取完。

简单来说，执行计划就是一条 SQL 语句在数据库中的执行过程或访问路径的描述。SQL 语言是种功能强大且非过程性的编程语言，比如以下这条 SQL 语句：

SELECT * FROM T1, T2 WHERE T1.ID = T2.ID AND T1.ID = 6;

开发人员只关心 SQL 语句能否返回 T1 与 T2 表的关联查询结果，不需要指定该 SQL 如何执行，也就是说不关心该 SQL 是先访问 T1 表还是先访问 T2 表。对于 SQL 来说，两种访问方式就是两个执行计划，查询优化器 (CBO) 将根据代价也就是开销来选择最优的执行计划。以如下 SQL 语句执行计划为例：

SELECT * FROM SYSOBJECTS;

1   #NSET2: [0, 1282, 396]
2     #PRJT2: [0, 1282, 396]; exp_num(17), is_atom(FALSE)
3       #CSCN2: [0, 1282, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

执行计划的每行即为一个计划节点，主要包含三部分信息。

• 第一部分 NEST2、PRJT2、CSCN2 为操作符及数据库具体执行了什么操作。

• 第二部分的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数]。

• 第三部分为操作符的补充信息。

例如：第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是 0 ms，扫描的记录行数是 1282 行，输出字节数是 396 个。

各计划节点的执行顺序为：缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。缩进最深的，最先执行；缩进深度相同的，先上后下。口诀：最右最上先执行。

#CSCN2: [1, 2, 12]; INDEX33555496(TEST)
 操作符，[代价，行数，字节数] 描述

经过上面的分析，慢SQL一般从下面2个方向来优化

1、从SQL上来优化，增加索引和语句连接方式来优化

2、数据库的统计信息定期更新，让sql能否有最优的执行计划

优化方向一：关于SQL优化

关于查询语句，有以下几点特征：

• 返回数据越多，语句执行时间越长；

• 分页是一个优化重点，order by 排序大小由结果集大小决定，过大会在临时表空间排序，性能降低；

• 一些语句会隐式排序，比如 uinon group by；

• buffer 过小，数据页频繁换入换出。

关于优化 sql 语句：

• 通过各种手段减少 sql 执行过程中的 IO 代价，内存中的计算，临时表使用等；

• 表与表之间的关系，即关联条件之间的数据对应关系；

• 表数据量大小，对于 OLTP 是否满足小表驱动大表；

• sql 返回结果集多少，如果返回结果集少，sql 优化余地较大；

• 合理利用索引（组合索引）的特点，虽然维护索引也需要代价，但是对于查询来说，很多时候效果立竿见影。

对于一个表来说索引并非越多越好，过多的索引将影响该表的 DML 效率。

存在下列情况将导致无法使用索引：

• 组合索引中，条件列中没有组合索引的首列。

• 条件列带有函数或计算。

• 索引排序是按照字段值进行排序的，字段值通过函数或计算后的值索引无法获取。

• 索引过滤性能不好时。

例如对一张 10 万条记录的表进行条件查询，获取 5 万条数据，通过索引进行查找效率低于全表扫描，将放弃使用索引。

建立索引的原则：

• 建立唯一索引。唯一索引能够更快速地帮助我们进行数据定位；

• 为经常需要进行查询操作的字段建立索引；

• 对经常需要进行排序、分组以及联合操作的字段建立索引；

• 在建立索引的时候，要考虑索引的最左匹配原则（在使用 SQL 语句时，如果 where 部分的条件不符合最左匹配原则，可能导致索引失效，或者不能完全发挥建立的索引的功效）；

• 不要建立过多的索引。因为索引本身会占用存储空间；

• 如果建立的单个索引查询数据很多，查询得到的数据的区分度不大，则考虑建立合适的联合索引；

• 尽量考虑字段值长度较短的字段建立索引，如果字段值太长，会降低索引的效率

优化方向二、定期收集统计信息

统计信息主要是描述数据库中表和索引的大小数以及数据分布状况等的一类信息。比如：表的行数、块数、平均每行的大小、索引的高度、叶子节点数以及索引字段的行数等。

统计信息对于 CBO（基于代价的优化器）生成执行计划具有直接影响。例如在嵌套循环连接（链接）中需要选择小表作为驱动表，两个关联表哪个是小表完全取决于统计信息中记录的数据量信息。此外，访问一个表是否要走索引，关联查询能否采用其它关联方式等都是 CBO 基于统计信息确定的。因此，统计信息的准确是生成最优执行计划的必要前提。

生产系统中，对于频繁变动的数据表，多次执行计划可能会出现不一致的问题，这往往是由于统计信息不准导致的，索引定期收集统计信息就很有必要了。

DM 收集统计信息的方法分为手动收集和自动收集。

手动收集

--收集指定用户下所有表所有列的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集指定用户下所有索引的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('usename',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');
--或 收集单个索引统计信息：
DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');

--收集指定用户下某表统计信息：
DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集某表某列的统计信息：
STAT 100 ON table_name(column_name);

统计信息收集过程中将对数据库性能造成一定影响，避免在业务高峰期收集统计信息。

DM 数据库支持统计信息的自动收集，当全表数据量变化超过设定阈值后可自动更新统计信息。

--打开表数据量监控开关，参数值为 1时监控所有表，2时仅监控配置表
SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);

--设置 SYSDBA.T 表数据变化率超过15%时触发自动更新统计信息
DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T','STALE_PERCENT',15);

--配置自动收集统计信息触发时机
SP_CREATE_AUTO_STAT_TRIGGER(1,1,1,1,'14:36','2020/3/31',60,1);

/*
函数各参数介绍
SP_CREATE_AUTO_STAT_TRIGGER(
    TYPE                    INT,    --间隔类型，默认为天
    FREQ_INTERVAL         INT,    --间隔频率，默认 1
    FREQ_SUB_INTERVAL    INT,    --间隔频率，与 FREQ_INTERVAL 配合使用
    FREQ_MINUTE_INTERVAL INT,    --间隔分钟，默认为 1440
    STARTTIME              VARCHAR(128), --开始时间，默认为 22:00
    DURING_START_DATE    VARCHAR(128), --重复执行的起始时间，默认 1900/1/1
    MAX_RUN_DURATION    INT,    --允许的最长执行时间(秒)，默认不限制
    ENABLE                  INT     --0 关闭，1 启用  --默认为 1
);
*/

查看统计信息

--用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。
dbms_stats.table_stats_show('模式名','表名');

--用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。 返回两个结果集：一个是索引的统计信息；另一个是直方图的统计信息。
dbms_stats.index_stats_show('模式名','索引名');

–用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。
dbms_stats.COLUMN_STATS_SHOW('模式名','表名','列名');

更新统计信息

--更新已有统计信息
DBMS_STATS.UPDATE_ALL_STATS();