2017-10-20 DBA日记，交易系统SQL编写注意事项[征询稿]

一、目的

通过提升SQL代码质量，从而提升系统稳定性与吞吐量，以满足持续增长的业务需求。

二、高质量SQL的特征

1. 运行速度快。
2. 运行时占用资源少。
3. 代码简洁，易于理解。

三、运行目标

1. 单条SQL语句运行时间少于等于500ms

四、范围

1. 使用oracle数据库的在线交易系统
2. 使用MYSQL数据库的在线交易系统参考使用.

五、面向人员

1. DBA
2. 编程人员

六、注意事项

存在即合理，SQL编写过程中，每一个运算符都有它存在的价值，只是应用场景的不同，带来的性能也各有不同，现根据过往的经验，总结如下：

1. 绑定变量

绑定变量的使用如下：
plsql:
execute immediate "select * from t1 where col1=:1" using v_col1;
java方式：
String v_id = 'xxxxx';
String v_sql = 'select name from table_a where id = ? '; //嵌入绑定变量
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //为绑定变量赋值
stmt.executeQuery();

2. 合理创建索引
   • 合理选择索引关键字
     • 值的可选值越高，重复值越少，索引的效率越高。如：唯一属性（id,card_no等），选择率在80%以上。
     • 具有二元属性的字段不适合用作索引关键字，如：性别、是否过期（有效）等等。
     • 值是由多个分类属性值组成的，也要注意数据分布，如出现严重的数据倾斜，需及时收集相关字段的直方图。
   • 单键索引还是组合索引，需看功能需求:
     • 112,推荐1条主键索引、1条唯一索引、2条组合索引的三个组合。
3. 正确使用索引
   • 在SQL中，对索引关键字进行函数运算后再进行值比较，只有函数索引才会被使用。
   • 索引关键字进行比较运算时，比较的值类型与索引关键字不匹配时，不使用索引。
   • 交易系统中禁止使用位图索引。
   • 正确使用组合索引，以一个索引(key1+key2+key3)为例。
     • key1
     • key1+key2
     • key1+key3
     • key1+key2+key3
     • key2+key3是不会使用索引的。
   • 索引关键字运行null判断运算时，不使用索引。如is null,is not null
   • like运算符
     • like '%pattern%' ,'%pattern' 不使用索引
     • like 'pattern%' 使用索引
   • 返回的记录数超过总记录数的5%不使用索引
4. in 与 exists之争
   • in内是字查询的建议返回记录不超过100条
   • in内是非子查询的建议值数不超过10个
   • exists的子查询与主查询的连接条件需命中索引。
5. minus是很消耗CPU的运算符
6. 往往令人忽视的数据命中率
   数据命中率，参与运算的数据量大于实际所需的数据量。往往是由于以下原因造成的：
   • 数据读取范围没有限定，特别是在多表关联时，在完成关联合拼后，再作过滤。例子如下：

   SELECT  count(sysreadlog0_.fd_id) AS col_0_0_  FROM  ( 
   SELECT 
       fd_read_type, 
       fd_reader_id, 
       fd_model_id, 
       fd_model_name, 
       fd_is_new_version, 
       fd_reader_client_ip, 
       fd_key, 
       fd_read_time, 
       fd_id, 
       0 AS clazz_ 
   FROM 
       sys_read_log 
   UNION 
   SELECT 
       fd_read_type, 
       fd_reader_id, 
       fd_model_id, 
       fd_model_name, 
       fd_is_new_version, 
       fd_reader_client_ip, 
       fd_key, 
       fd_read_time, 
       fd_id, 
       1 AS clazz_ 
   FROM 
       sys_readprocess_log 
       ) sysreadlog0_ 
   WHERE 
       sysreadlog0_.fd_model_id = '15xxxxxxxxxxxxxxxxxx' 
       AND sysreadlog0_.fd_model_name = 'com.xxxxxxxxxxxxxxx' 
       AND sysreadlog0_.fd_read_time <= '2017-10-13 00:59:00' ;
   改写前需要花28秒；
   按如下改写后，只需要花165毫秒，提升169倍，大大节省时间和计算资源。
   SELECT 
   count(sysreadlog0_.fd_id) AS col_0_0_ 
   FROM 
   ( 
   SELECT 
   fd_read_type, 
   fd_reader_id, 
   fd_model_id, 
   fd_model_name, 
   fd_is_new_version, 
   fd_reader_client_ip, 
   fd_key, 
   fd_read_time, 
   fd_id, 
   0 AS clazz_ 
   FROM 
   sys_read_log 
   WHERE 
   fd_model_id = '15xxxxxxxxxxxxxxxxxx' 
   AND fd_model_name = 'com.xxxxxxxxxxxxxxx' 
   AND fd_read_time <= '2017-10-13 00:59:00' 
   UNION 
   SELECT 
   fd_read_type, 
   fd_reader_id, 
   fd_model_id, 
   fd_model_name, 
   fd_is_new_version, 
   fd_reader_client_ip, 
   fd_key, 
   fd_read_time, 
   fd_id, 
   1 AS clazz_ 
   FROM 
   sys_readprocess_log 
   WHERE 
   fd_model_id = '15xxxxxxxxxxxxxxxxxx' 
   AND fd_model_name = 'com.xxxxxxxxxxxxxxx' 
   AND fd_read_time <= '2017-10-13 00:59:00' 
   ) sysreadlog0_ 
   ;

   • 数据范围有限定，但没有命中索引
7. 并行是禁止的。
   • 不能使用hints在SQL中加入并行度。
   • 不能在表及索引定义时加入并行度。
8. HINTS是禁止的。
9. 全表扫描在某种情况下并不代表就是差的。
10. 子查询尽量避免，使用left join等代替。
11. 尽量避免使用select *
12. 能够批量处理的记录，就不要一条条处理
13. 一条条记录进行DML时，避免逐行commit

七、检查SQL质量

检查SQL的执行计划来评估SQL的好坏。

7.1 SQL计划生成的方法

1. 在sqlplus中运行如下命令后，就能显示每次执行SQL的执行计划及物理读、逻辑读相关信息。

set time on
set line 300
set autotrace traceonly
11:32:25 SQL> select count(*) from aa.dealer;


Execution Plan
----------------------------------------------------------
Plan hash value: 1533960468

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |  8701   (2)| 00:01:45 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_DEALER_05 |    12M|  8701   (2)| 00:01:45 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
      31748  consistent gets
      31376  physical reads
      19360  redo size
        213  bytes sent via SQL*Net to client
        253  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

注：一般非DBA用户是没有直接查询权限的，所以需要执行如下脚本：

create role plustrace;
grant select on sys.v_$sesstat to plustrace;
grant select on sys.v_$statname to plustrace;
grant select on sys.v_$mystat to plustrace;
grant plustrace to dba with admin option;
grant plustrace to 用户A;

2. 在TOAD或plsql中查看执行计划
   在TOAD、PLSQL中都有快捷键查看sql的执行计划，这里只作简单描述。
   • TOAD: crtl+e
   • PLSQL: F5

八、SQL注释

写SQL注释有利于生产故障的快速定位，大家在代码中写SQL的时候一定要写SQL注释。

数据集里的SQL注释规范

SELECT /*[微服务中心名]-[文件名.SQL ID]*/

field1

FROM table

例子：

SELECT /*[PO]-[poApi-dataset.listPoApp]*/

代码里的SQL注释规范

SELECT /*[微服务中心名]-[类名.方法名]*/

field1

FROM table

例子：