概述

在客户现场协助客户优化慢查询，有一条语句客户说在MySQL库下的时候很快，基本在毫秒级别，但是迁移到DM后变得非常慢，达到7~8百毫秒，很不正常。
SQL语句如下

   select oo.*,
          oou.*
     from OFFICIAL_ORGS     oo
left join OFFICIAL_ORG_USER oou
       on oo.ORG_ID = oou.ORG_ID
    where oou.USER_CODE = '123456'
      and oo.TENANT_ID = 'def'
      and oo.IS_DELETED = '0'
 order by oou.IS_PRIMARY desc,
          oou.DISPLAY_ORDER asc,
          oou.CREATED_TIME desc;

从客户现场的版本来看，版本是24年1月的版本。

优化过程

首先使用explain查看它的执行计划，发现它的执行计划仅使用了OFFICIAL_ORG_USER表的ORG_ID字段上的索引，以及表的聚集索引扫描，并没有使用到OFFICIAL_ORG_USER表的USER_CODE字段索引或者其他有效索引。
为了让它能使用上有效的OFFICIAL_ORG_USER表的USER_CODE字段索引，通过HINT的方式指定索引名，发现还是不起作用。
经过一番无果的分析后，突然想到统计信息是不是没有起作用？
通过select id,LAST_GATHERED from SYSSTATS;查询了一下最近一次收集统计信息的时间，发现这个表是空的，也就是说统计信息是没有收集过的。
由于是测试环境，执行统计信息的操作是允许的，所以立刻做了一次统计信息收集。
SP_DB_STAT_INIT();
执行完成后再看执行计划，已经变成了如下：

1   #NSET2: [2, 26, 789] 
2     #PRJT2: [2, 26, 789]; exp_num(14), is_atom(FALSE) 
3       #SORT3: [2, 26, 789]; key_num(3), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #SLCT2: [1, 26, 789]; (OO.TENANT_ID = 'default' AND OO.IS_DELETED = '0')
5           #HASH2 INNER JOIN: [1, 26, 789];  KEY_NUM(1); KEY(OOU.ORG_ID=OO.ORG_ID) KEY_NULL_EQU(0)
6             #SLCT2: [1, 26, 789]; OO.IS_DELETED = '0'
7               #NEST LOOP INDEX JOIN2: [1, 26, 789] 
8                 #ACTRL: [1, 26, 789];
9                   #BLKUP2: [1, 26, 305]; IDX_OFFICIAL_ORG_USER_01(OOU)
10                    #SSEK2: [1, 26, 305]; scan_type(ASC), IDX_OFFICIAL_ORG_USER_01(OFFICIAL_ORG_USER as OOU), scan_range[('12345',min),('12345',max))
11                #BLKUP2: [1, 1, 96]; INDEX11117958(OO)
12                  #SSEK2: [1, 1, 96]; scan_type(ASC), INDEX11117958(OFFICIAL_ORGS as OO), scan_range[(OOU.ORG_ID,'default'),(OOU.ORG_ID,'default')]
13            #CSCN2: [16, 80485, 484]; INDEX11117956(OFFICIAL_ORGS as OO); btr_scan(1)

从中可以看到现在已经使用到了IDX_OFFICIAL_ORG_USER_01这个索引，它是在OFFICIAL_ORG_USER表的USER_CODE字段上的索引，此时可以确定使用上了正确的索引，再执行一次这个SQL语句，执行时间从STAT信息中看到的EXECTIME已经为0（表示小于1毫秒），说明收集统计信息后是有效果的。

接下来继续从sqllog日志中使用DMLOG工具进行收集分析，发现这个SQL还是会出现慢的情况，而且基本都是在2000多毫秒级别的，这就很奇怪了，为什么在终端上单独执行都很快，但是实际执行时还是很出现慢的情况呢？

由于对慢查询优化还不是很熟悉，所以向技术大佬求助，经过他的排查后，发现执行计划中有ACTRL这样的操作符，它是在执行计划生产时会对一些复杂的子节点查询生成一个备用计划，如果在实际执行到该节点时，根据准确的代价信息来决定是否采用备用计划。备用计划是对OFFICIAL_ORGS表的聚集索引的全表扫描，这种效率会很差，可能会达到2000多毫秒的级别。
接下来使用了/*+enable_hash_join(0)*/的方式将备用计划中的HASH2 INNER JOIN的操作符禁用，可以看到执行计划变成了：

1   #NSET2: [1, 1, 1408] 
2     #PRJT2: [1, 1, 1408]; exp_num(35), is_atom(FALSE) 
3       #SORT3: [1, 1, 1408]; key_num(3), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #SLCT2: [1, 1, 1408]; OO.IS_DELETED = '0'
5           #NEST LOOP INDEX JOIN2: [1, 1, 1408] 
6             #BLKUP2: [1, 1, 558]; IDX_OFFICIAL_ORG_USER_01(OOU)
7               #SSEK2: [1, 1, 558]; scan_type(ASC), IDX_OFFICIAL_ORG_USER_01(OFFICIAL_ORG_USER as OOU), scan_range[('123456',min),('123456',max)), is_global(0)
8             #BLKUP2: [1, 1, 96]; INDEX33555636(OO)
9               #SSEK2: [1, 1, 96]; scan_type(ASC), INDEX33555636(OFFICIAL_ORGS as OO), scan_range[(OOU.ORG_ID,'def'),(OOU.ORG_ID,'def')], is_global(0)

其中没有了ACTRL操作符，并且JOIN的方法只有NEST LOOP INDEX JOIN2一个了，备用计划的HASH INNER JOIN也消失了。

技术大佬又提出了一个疑问，自适应执行计划功能一般在我们部署环境的时候都会关闭，因为它在实际生产环境使用过程中可能会出现这种无法预期的行为，这个集群为什么没有关闭？经过确认，这个环境的数据库是客户自己部署的，有些调优参数都是默认未调整的，包括自适应执行计划的开关ADAPTIVE_NPLN_FLAG还是默认的1。
将这个参数值调整为0后，需要将数据库的连接都杀掉重连，连接上才能使用新的值，为了方便操作直接重启了数据库，应用服务也做了重启。

再去查看不加HINT的语句的执行计划，发现已经和加HINT的执行计划一样了。

等待应用服务进行了简单的测试后，收集了一些慢查询的sqllog日志再使用DMLOG工具进行分析，这条SQL已经没有再出现了。

结论

经过这次慢查询的优化过程，得到了以下的经验：

1. 在做实际的慢查询分析前，需要了解环境的信息，包括有没有统计信息收集、参数配置等
2. 如果没有做统计信息收集，看实际环境是否允许执行收集操作，需要在测试环境做一遍确定没有问题再到生产环境执行
3. 一些在实际使用过程中不建议开启的优化机制需要提前了解清楚，以防浪费时间在没有方向的分析上。
4. 实在没有思考方向的话就尽快求助团队，避免一个人陷入僵局。