在系统上线初期，由于数据量较少，数据库性能比较稳定，各种方式写出的SQL语句性能上差异不是很大。随着数据量的增加，特别是系统中海量数据的出现，不同SQL语句执行效率上的差异高下立判，响应速度上相差百倍千倍都有可能。而在出现问题后再进行SQL优化成本就比较大，因此，在系统设计和开发时就将SQL优化的思想贯穿始终，则是完成信息系统建设项目的重要保证和良好习惯。
一般而言，SQL语句的调优处于数据库优化最后手段。因为SQL语句的调整和业务紧密相连，需要修改应用程序中相应的代码，需要开发人员或DBA对业务场景比较熟悉，保证修改不会引起其它问题，同时需要测试人员对修改后的应用进行测试。因此，我们在进行正式的 SQL 语句优化前，需要考虑以下几个问题：
1．达梦数据库安装时的配置参数是否符合应用场景需求；
2．达梦数据库的 INI 配置文件中各项参数是否已经处于最优配置；
3．应用系统中数据库设计是否合理。
对上面3个问题仔细斟酌和实践后，数据库依然存在较大的性能问题，则进入到SQL语句优化环节。
优化规则：
选择最有效的表名顺序
解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。
以DM8自带的DMHR模式为例，employee表中的数据为856行，job表中的数据为16行。查询时，以不同的顺序做以比较。
SQL> select count(*) from dmhr.employee;
LINEID COUNT(*)
---------- --------------------
1 856
used time: 70.008(ms). Execute id is 64.
SQL> select count(*) from dmhr.job;
LINEID COUNT(*)
---------- --------------------
1 16
used time: 0.440(ms). Execute id is 65.
SQL> explain select e.employee_id, e.employee_name, j.job_title from dmhr.employee e, dmhr.job j where e.job_id=j.job_id;
1 #NSET2: [1, 856, 196]
2 #PRJT2: [1, 856, 196]; exp_num(3), is_atom(FALSE)
3 #HASH2 INNER JOIN: [1, 856, 196]; LKEY_UNIQUE KEY_NUM(1); KEY(J.JOB_ID=E.JOB_ID) KEY_NULL_EQU(0)
4 #CSCN2: [0, 16, 96]; INDEX33555535(JOB as J)
5 #CSCN2: [0, 856, 100]; INDEX33555537(EMPLOYEE as E)
used time: 88.831(ms). Execute id is 0.
SQL> explain select e.employee_id, e.employee_name, j.job_title from dmhr.employee e, dmhr.job j where e.job_id=j.job_id;
1 #NSET2: [1, 856, 196]
2 #PRJT2: [1, 856, 196]; exp_num(3), is_atom(FALSE)
3 #HASH2 INNER JOIN: [1, 856, 196]; LKEY_UNIQUE KEY_NUM(1); KEY(J.JOB_ID=E.JOB_ID) KEY_NULL_EQU(0)
4 #CSCN2: [0, 16, 96]; INDEX33555535(JOB as J)
5 #CSCN2: [0, 856, 100]; INDEX33555537(EMPLOYEE as E)
used time: 1.442(ms). Execute id is 0.
从上面的结果看，以小数据量的表做为基表，响应速度更快，相差接近80倍。
where字句中的连接顺序
多表连接时，不同的连接顺序会影响中间结果集数量的大小，这时调优的目标就是要找到一种能使中间结果保持最小的连接顺序。解析器按照从上而下解析，因此表之间的连接必须写在where条件之前，下面的两示例分别展示了表连接在where子句不同位置，响应时间上差异：
响应时间较长：
explain select e.employee_id, e.employee_name from dmhr.employee e
2
where e.salary > 9000 and e.job_id = 11
4
5 and 2 < (select count(*) from dmhr.employee where e.manager_id = e.employee_id);
1 #NSET2: [21, 2, 232]
2 #PIPE2: [21, 2, 232]
3 #PRJT2: [21, 2, 232]; exp_num(3), is_atom(FALSE)
4 #HASH2 INNER JOIN: [21, 2, 232]; KEY_NUM(1); KEY(colname=DMTEMPVIEW_16778699.AUTOID) KEY_NULL_EQU(1)
5 #SLCT2: [19, 1, 116]; colname > var1
6 #PRJT2: [19, 1, 116]; exp_num(2), is_atom(FALSE)
7 #HAGR2: [19, 1, 116]; grp_num(1), sfun_num(2); slave_empty(0) keys(DMTEMPVIEW_16778699.AUTOID)
8 #NEST LOOP LEFT JOIN2: [18, 45, 116]; join condition(DMTEMPVIEW_16778699.TMPCOL2 = DMTEMPVIEW_16778699.TMPCOL3) partition_keys_num(0) ret_null(0)
9 #HEAP TABLE SCAN: [1, 2, 116]; table_no(0),
10 #SSCN: [0, 856, 8]; INDEX33555538(EMPLOYEE)
11 #HEAP TABLE SCAN: [1, 2, 116]; table_no(0),
12 #HEAP TABLE: [0, 2, 116]; table_no(0) full(FALSE), mpp_full(0) autoid(TRUE)
13 #SLCT2: [0, 2, 116]; (E.SALARY > 9000 AND exp_cast(E.JOB_ID) = 11)
14 #CSCN2: [0, 856, 116]; INDEX33555537(EMPLOYEE as E)
used time: 18.147(ms). Execute id is 0.
响应时间较短
explain select e.employee_id, e.employee_name from dmhr.employee e
2
where 2 < (select count(*) from dmhr.employee where e.manager_id = e.employee_id)
4
5 and e.salary > 9000 and e.job_id = 11;
1 #NSET2: [21, 2, 232]
2 #PIPE2: [21, 2, 232]
3 #PRJT2: [21, 2, 232]; exp_num(3), is_atom(FALSE)
4 #HASH2 INNER JOIN: [21, 2, 232]; KEY_NUM(1); KEY(colname=DMTEMPVIEW_16778705.AUTOID) KEY_NULL_EQU(1)
5 #SLCT2: [19, 1, 116]; colname > var1
6 #PRJT2: [19, 1, 116]; exp_num(2), is_atom(FALSE)
7 #HAGR2: [19, 1, 116]; grp_num(1), sfun_num(2); slave_empty(0) keys(DMTEMPVIEW_16778705.AUTOID)
8 #NEST LOOP LEFT JOIN2: [18, 45, 116]; join condition(DMTEMPVIEW_16778705.TMPCOL0 = DMTEMPVIEW_16778705.TMPCOL1) partition_keys_num(0) ret_null(0)
9 #HEAP TABLE SCAN: [1, 2, 116]; table_no(0),
10 #SSCN: [0, 856, 8]; INDEX33555538(EMPLOYEE)
11 #HEAP TABLE SCAN: [1, 2, 116]; table_no(0),
12 #HEAP TABLE: [0, 2, 116]; table_no(0) full(FALSE), mpp_full(0) autoid(TRUE)
13 #SLCT2: [0, 2, 116]; (E.SALARY > 9000 AND exp_cast(E.JOB_ID) = 11)
14 #CSCN2: [0, 856, 116]; INDEX33555537(EMPLOYEE as E)
used time: 1.220(ms). Execute id is 0.
合理使用通配符
除非用户确实要选择表中所有列，否则 SELECT * 这种写法将让执行器背上沉重的负荷。因为每一列的数据不得不自下往上层层向上传递。不仅仅如此，如果用户查询的是列存储表，那么列存储所带来的 IO 优势将损耗殆尽。任何时候，用户都要了解表结构和业务需求，小心地选择需要的列并一一给出名称，避免直接用 SELECT *。
SQL> explain select * from dmhr.employee;
1 #NSET2: [0, 856, 280]
2 #PRJT2: [0, 856, 280]; exp_num(12), is_atom(FALSE)
3 #CSCN2: [0, 856, 280]; INDEX33555537(EMPLOYEE)
used time: 0.389(ms). Execute id is 0.
sql在执行带通配符的语句时，如果‘%’在首位，那么在字段上建立的主键或者索引将会失效！
如下%通配符的使用造成全表扫描。
SQL> explain select * from dmhr.employee where phone_num like '%55';
1 #NSET2: [0, 42, 280]
2 #PRJT2: [0, 42, 280]; exp_num(12), is_atom(FALSE)
3 #SLCT2: [0, 42, 280]; EMPLOYEE.PHONE_NUM LIKE '%55'
4 #CSCN2: [0, 856, 280]; INDEX33555537(EMPLOYEE)
used time: 0.500(ms). Execute id is 0.
困难的正则表达式是指开头和结尾都为通配符的正则表达式，如’L%’、’%L’，优化器没办法缩小它们的匹配范围，也不可能用到索引而必须使用全表扫描。因此要尽可能避免这样的正则表达式。
如果仅仅是开头为通配符，用户可以在列 a 上建立 REVERSE(a)这样一个函数索引，利用函数索引反转待匹配项从而使用函数索引进行范围扫描。
用in代替or
OR子句在实际执行中会被转换为类似于UNION的查询。如果某一个 OR 子句不能利用上索引则会使用全表扫描造成效率低下，应避免使用。
如果 OR 子句都是对同一列进行过滤，用户可以考虑使用 IN VALUE LIST 的过滤形式。
select employee_id, employee_name, identity_card from dmhr.employee e
where job_id='11' or job_id='21' or job_id='31';
select employee_id, employee_name, identity_card from dmhr.employee e
where job_id in ('11', '21', '31');
使用 COUNT()统计结果行数*
如果对单表查询 COUNT()且没有过滤条件，那么DM优化器会直接读取相关索引中存储的行数信息，加以回滚段中其他事务插入或删除元组的行数修正，迅速地给出最终结果而避免对实际数据的读取。相比之下，COUNT(列名)会对数据进行读操作，执行效率远低于COUNT()。
即使查询中含有过滤条件，由于 DM 特有的批处理方式，COUNT(*)依旧快于其他写法。
SQL> explain for select count(*) as rows_number from dmhr.employee;
LINEID PLAN_ID PLAN_NAME
---------- ----------- ---------
CREATE_TIME
----------------------------------------------------------------------------------------------------
LEVEL_ID OPERATION TAB_NAME IDX_NAME SCAN_TYPE SCAN_RANGE
----------- --------- -------- -------- --------- ----------
ROW_NUMS BYTES COST
-------------------- ----------- --------------------
CPU_COST IO_COST FILTER JOIN_COND
-------------------- -------------------- ------ ---------
ADVICE_INFO PSTART PSTOP
----------- ----------- -----------
1 4 NULL
2021-05-13 16:37:50.000000
0 NSET2 NULL NULL NULL NULL
1 0 0
0 0 NULL NULL
NULL 0 0
LINEID PLAN_ID PLAN_NAME
---------- ----------- ---------
CREATE_TIME
----------------------------------------------------------------------------------------------------
LEVEL_ID OPERATION TAB_NAME IDX_NAME SCAN_TYPE SCAN_RANGE
----------- --------- -------- -------- --------- ----------
ROW_NUMS BYTES COST
-------------------- ----------- --------------------
CPU_COST IO_COST FILTER JOIN_COND
-------------------- -------------------- ------ ---------
ADVICE_INFO PSTART PSTOP
----------- ----------- -----------
2 4 NULL
2021-05-13 16:37:50.000000
1 PRJT2 NULL NULL NULL NULL
1 0 0
0 0 NULL NULL
NULL 0 0
LINEID PLAN_ID PLAN_NAME
---------- ----------- ---------
CREATE_TIME
----------------------------------------------------------------------------------------------------
LEVEL_ID OPERATION TAB_NAME IDX_NAME SCAN_TYPE SCAN_RANGE
----------- --------- -------- -------- --------- ----------
ROW_NUMS BYTES COST
-------------------- ----------- --------------------
CPU_COST IO_COST FILTER JOIN_COND
-------------------- -------------------- ------ ---------
ADVICE_INFO PSTART PSTOP
----------- ----------- -----------
3 4 NULL
2021-05-13 16:37:50.000000
2 FAGR2 NULL NULL NULL
1 0 0
0 0 NULL NULL
NULL 0 0
used time: 3.274(ms). Execute id is 89.
SQL> explain for select count(employee_id) as rows_number from dmhr.employee;
LINEID PLAN_ID PLAN_NAME
---------- ----------- ---------
CREATE_TIME
----------------------------------------------------------------------------------------------------
LEVEL_ID OPERATION TAB_NAME IDX_NAME SCAN_TYPE SCAN_RANGE
----------- --------- -------- -------- --------- ----------
ROW_NUMS BYTES COST
-------------------- ----------- --------------------
CPU_COST IO_COST FILTER JOIN_COND
-------------------- -------------------- ------ ---------
ADVICE_INFO PSTART PSTOP
----------- ----------- -----------
1 5 NULL
2021-05-13 16:38:24.000000
0 NSET2 NULL NULL NULL NULL
1 4 0
0 0 NULL NULL
NULL 0 0
LINEID PLAN_ID PLAN_NAME
---------- ----------- ---------
CREATE_TIME
----------------------------------------------------------------------------------------------------
LEVEL_ID OPERATION TAB_NAME IDX_NAME SCAN_TYPE SCAN_RANGE
----------- --------- -------- -------- --------- ----------
ROW_NUMS BYTES COST
-------------------- ----------- --------------------
CPU_COST IO_COST FILTER JOIN_COND
-------------------- -------------------- ------ ---------
ADVICE_INFO PSTART PSTOP
----------- ----------- -----------
2 5 NULL
2021-05-13 16:38:24.000000
1 PRJT2 NULL NULL NULL NULL
1 4 0
0 0 NULL NULL
NULL 0 0
LINEID PLAN_ID PLAN_NAME
---------- ----------- ---------
CREATE_TIME
----------------------------------------------------------------------------------------------------
LEVEL_ID OPERATION TAB_NAME IDX_NAME SCAN_TYPE SCAN_RANGE
----------- --------- -------- -------- --------- ----------
ROW_NUMS BYTES COST
-------------------- ----------- --------------------
CPU_COST IO_COST FILTER JOIN_COND
-------------------- -------------------- ------ ---------
ADVICE_INFO PSTART PSTOP
----------- ----------- -----------
3 5 NULL
2021-05-13 16:38:24.000000
2 FAGR2 NULL NULL NULL
1 4 0
0 0 NULL NULL
NULL 0 0
used time: 1.432(ms). Execute id is 90.
这是因为 COUNT()无需取得行的具体值而仅仅需要行数这一信息。
需要额外说明的是，COUNT()会将 NULL 值计算在内而 COUNT(列名)是不包含 NULL值的，因此用户要结合应用场景决定是否可以使用 COUNT(*)。
868
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
