一、SQL调优概述

在数据库的使用过程中，我们常常会提到数据库性能调优。数据库性能调优涉及很多方面，如：操作系统参数、数据库配置参数、SQL调优等等，下面主要介绍达梦数据库的SQL调优。提到SQL调优，很多人会想到通过创建索引来提高数据库的查询效率。索引在一定程度的确能加速我们的查询检索，但是在实际的生产实践中创建过多冗余、选择性差的索引往往会适得其反。我们应该了解索引的底层实现，掌握如何分析SQL的执行计划，再结合实际的业务逻辑去创建合适的索引。

1、索引概述

目前市面上的数据库种类很多，但是底层存储的结构主要有索引组织表（IOT表）、堆表还有LSM TREE日志树结构等几种。达梦数据库默认使用的是索引组织表（可通过参数LIST_TABLE控制；默认0表示IOT表，1表示堆表），创建主键默认不会转换成聚集索引，相比普通索引多了非空唯一的约束（可通过参数PK_WITH_CLUSTER控制主键是否转换成聚集索引；默认0表示不开启转换，1表示开启转换）。如果没有显式的指定聚集索引，达梦会采用隐藏列rowid来创建对应的聚集索引，下面根据不同的维度对索引作了简单的划分：

1）按照数据存储顺序：
聚集索引：表中数据的物理存储顺序与索引键的逻辑顺序是一致的；聚集索引的叶子节点存储着数据；
非聚集索引：又称辅助索引、二级索引。表中数据的物理存储顺序和索引键的逻辑顺序没有关联，非聚集索引的叶子节点存储着对应的列索引以及聚集索引的指针（便于回表）；
2）按索引的数量：
单列索引：单个列组成的索引；
联合索引：又称组合索引，多个列组成的索引；
3）按数据离散性：
稠密索引：每个数据记录都有对应的索引项与之对应；非聚集索引必须是稠密索引，非聚集索引本身无法保证数据的有序性，所以需要每条数据都有索引项与之对应；
稀疏索引：每个数据段记录都有对应的索引项与之对应；稀疏索引必须是聚集索引，因为稀疏索引必须要保证顺序，如果不保证顺序，则有些数据无法寻址；
4）其他索引：
覆盖索引：组合索引中包含需要的查询列、条件列，不需要再回表从而减少io消耗；
前缀索引：对表中列的前几个字符创建索引；
函数索引：对表中列进行函数计算后的结果创建索引

2、索引创建规范

1）尽量减少回表（可根据实际业务决定是否使用覆盖索引）；
2）索引的选择性要好（索引选择性不好时，走索引的开销比全表扫描更大）
3）尽可能防止重新排序（排序的列前几个字段需要走索引，且不能有范围查询）
4）减少冗余索引，将筛选性好的等值条件字段作为组合索引的前导列；对于执行次数很少的SQL语句，需要业务综合考虑是否有必要单独创建索引（每多维护一个索引，性能都会有一些损耗）

3、SQL语句开发规范

1）避免隐式转换，如果查询列的字段类型和传值类型不一致，会导致索引失效。
2）避免对查询字段使用表达式，会导致索引失效，如果一定要使用表达式尽量放在谓词右侧的值上。
3）避免select * 查询所有列，会造成不必要的IO开销和解析成本。
4）减少OR子句的使用，可能会走不到合适的索引导致全表扫描。
5）当不需要去重时，尽量使用UNION ALL，UNION需要创建HASH表来做去重，比较损耗性能。

4、SQL执行计划分析

SQL的执行在数据库层面会经过词法语法解析、查询转换再经由查询优化器生成对应的执行计划。查询优化器会结合SQL语句中涉及到的表的统计信息、直方图、HINT等信息以及代价模型生成最优的的执行计划，如果统计信息和实际的数据差异很大，执行计划可能不是最优，所以针对数据变化比较大的表需要定期去收集对应的统计信息。我们可以通过explain + SQL语句去查看达梦数据库中SQL的执行计划，一般需要关注表的联结方式(NL JOIN、HASH JOIN、MERGE JOIN)、联结顺序（驱动表的选择）以及访问路径(全表扫描还是索引扫描)等。在SQL调优过程中，我们也可以通过HINT来看SQL执行计划是否符合预期，观察不同执行计划的COST代价等等。
通常情况NL JOIN适用于小表驱动大表，大表有合适的索引，可用在非等值条件下；HASH JOIN适用于小表驱动大表，大表没有合适的索引，只能用在等值条件下；MERGE JOIN适用于两张大小相当的表做关联，关联列有序，只能用在等值条件下；

说明：
该章节概述借鉴了少量网上的资料以及达梦数据库技术手册，并进行了归纳总结。

二、SQL调实践

上面提到SQL调优的一些思路，比如创建合适的索引、遵循SQL开发规范等，下面会通过简单的测试用例了解达梦数据库的执行计划、常用的执行计划操作符的含义。

1、OR条件

1.创建表
create table emp(id int,depno int,name varchar2(10));

2.模拟数据
insert into emp
select level,
mod(level,1000), 
case mod(level,5) 
when 0 then '张一'
when 1 then '李一'
when 2 then '王一'
when 3 then '赵一'
when 4 then '钱一'
else null
end from dual  
connect by level <=150000;

3.测试用例
create index idx_id on emp(id);
create index idx_depno on emp(depno);

#case1 or条件是对于同一个字段
select * from emp where id <= 1 or id >= 149000;

#case2 or条件是对于不同字段
select * from emp where id < 1000 or depno = 1;

#等价改写
select * from emp where id < 1000
union all
select * from emp where depno = 1 and lnnvl(id < 1000);

#上面这种改写方法需要注意 lnnvl(id<1000) 和id>=1000的区别
#当条件为id is null and depno = 1 符合 id < 1000 or depno = 1。
#如果改写成 id < 1000 && depno = 1 and id > 1000显然会把id is null的情况忽略。
#因为null 除了使用 is null返回true外，和null，任何值比较都是返回unknow。
#还有一种union的改写方法，但是需要通过HASH去重，所以开销更多，不如union all
select * from emp where id < 1000
union
select * from emp where depno =1;

不创建索引时，使用的CSCN2操作符，表示扫描聚集索引（相当于全表扫描）

给id列添加索引，两个子条件使用了SSEK操作符（分别走了二级索引扫描），然后再使用UNION FOR OR2对两个2子条件返回的结果进行UNION聚合

当or子句有个条件没有合适的索引时，会将二级索引扫描、聚集索引（全表扫描）扫描的结果进行UNION聚合

or子句的2个条件都有索引后，整体的COST代价下降

通过UNION ALL等价改写（需要注意NULL的情况，通过LNNVL过滤）

通过UNION改写，可以看到在UNION ALL之后做了DISTINCT去重操作，达梦做去重通过HASH表，相对会有些损耗

2、子查询改写

标量子查询由于表的联结顺序确定了，可能导致无法走到更合适的执行计划，在某些情况下可以将标量子查询改写成外连接的方式：

create table empl(id int,depno int,name varchar(20));
insert into empl values(1,1,'张三'),(2,3,'老六'),(3,2,'王老五'),(4,7,'王武'),(5,null,'dj');

create table dep(depno int,lv int,sal int);
insert into dep values(1,0,1000),(2,1,250),(3,5,2000),(3,6,888),(4,0,200),(5,6,13333),(6,2,2222);

select e.*,ifnull((select d.sal from dep d where d.depno = e.depno and d.lv > 3 order by
d.sal asc limit 1),666) ev
from empl e;

#等价改写
select e.*,ifnull((d2.sal),666) dv from empl e
left join (select d.depno,min(d.sal) sal from dep d where d.lv > 3 group by d.depno) d2
on e.depno = d2.depno;

实际场景中，一般子查询中dep.depno = e.depno时,应该通过业务模型保证一个depno对应的sal值都是相同或者只返回一条记录。如果有多个不同值，可能随着数据库的变化导致多次查询的结果不一致

3、in、exists

in关联子查询的逻辑是先执行内部的in子句，再将结果集去驱动外层的查询，往往走不到合适的索引，常用的优化思路是将in子查询改写成exists子查询，外层驱动子查询，当子查询中的关联表有合适的索引时就可以提高查询效率。

create table a(id int,val int);
create table b(id int,val int);
insert into a values(1,1),(2,2),(3,3),(null,4);
insert into b values(1,1),(2,2),(null,3);

select * from a where id in (select id from b);
select * from a where exists (select 1 from b where a.id = b.id);

注：in、exists可以等价替换；但是not in、not exists不能等价替换，两者关联列都非空的情况下是相同的，在关联列有null值时不同。如果被驱动表关联列含有null值，not in的结果集恒为空。而not exists会返回驱动表id列关联不到的行（含null），具体逻辑如下：
1）in 相当于 id = xx1 or id = xx2... ；not in 相当于 id != xx1 and id ! = xx2...
2）exists和not exists每条记录都单独判定条件是否满足。
3）in 和 exists 当关联列有null值，都会过滤掉（无论驱动表还是被驱动表null值关联都为unkown）
4）加了not关键字后，exists的匹配逻辑还是和之前一样，但是not in的逻辑就变了，只要子表关联列
有null值，条件就会变成 id != xx1 and id != null [and ...],条件恒不成立
5）加了not关键字，即使子表关联列非空，驱动表关联列有null，两者逻辑也不相同；前者是用null + 表达式 ；后者是表达式取反。null not in ()恒不成立会被过滤掉，而not exists (null = xx1)恒成立不会过滤null。

https://eco.dameng.com