概述

在业务数据库诊断过程中，如果数据库能记录执行过的每个SQL的性能信息，那对应用诊断性能异常问题会很有帮助。传统商业数据库在这方面做了一些探索。

如ORACLE的AWR或ASH视图里记录的SQL都是参数化的SQL，所以不能准确关联到有问题的业务SQL上。ORACLE的诊断思路是如果SQL性能不好，就执行计划上找原因，相应的解决方案就是调整索引、收集统计信息或者用大纲(OUTLINE)修改或固定执行计划。ORACLE 11G增加了 v$sql_monitor视图可以保存特定条件或者全部的正在运行的SQL以及最近运行的SQL的执行性能信息。

MySQL没有这样的SQL历史视图，有慢日志(Slow Log)可以收集执行时间超过指定阈值(long_query_time)的SQL记录在慢日志里。MySQL的思路是只关注执行慢的SQL。不过这个记录的性能数据信息并不是很多。此外记录的SQL也只是业务SQL的很小部分。此外MySQL有查询日志(Query Log)，默认是关闭的。如果打开，MySQL的性能会下降50%以上，所以生产环境基本不敢用。

在生产中，有时会碰到这种情形就是并不复杂的业务SQL突然变得很慢，其执行计划也并没有走错。单独跑这个SQL又很快。这个就让人很困惑。要分析这个问题需要认识到两点：

业务SQL在数据库的响应时间 等于SQL排队等被调度过程中的等待时间加上SQL的执行时间。当SQL请求非常高的时候，SQL工作线程非常繁忙时会引起排队。

我们从客户端监控到的SQL响应时间多是平均响应时间，只是一个时间段内全部SQL执行时间的统计值。对于具体的每个SQL，其响应时间可能在这个均值之下或者之上。或者说全部SQL的响应时间实际呈现一个类似正太分布的。

所以，如果数据库内核能记录每个SQL详细的执行信息，就能观察到上面两点。如总的时间、等待IO时间、锁等待时间和服务时间、逻辑读、物理读信息等，甚至更多。有了这些基础信息后，数据库性能诊断可以自动化，不再单纯依赖DBA的精力和能力。

阿里数据库内核的SQL全量功能

AliSQL的SQL全量日志

AliSQL是阿里巴巴数据库内核团队曾经维护的一个开源MySQL的分支，针对MySQL内核做了很多加强和优化。其中一个独特的功能就是SQL全量日志。

AliSQL的内核会在SQL执行结束时拿到执行性能信息后就答复客户端。然后异步的将SQL文本和执行性能信息以字符流形式写入一个SQL日志缓冲区。然后一个单独的日志线程循环读取该SQL日志缓冲区内容并写入到磁盘上一个管道文件。管道文件每个MySQL实例一个(实例监听端口不一样)，格式为：/u01/my$port/run/mysql.fifo。这个管道文件很特殊，如果写满了需要有其他客户端连接到这个管道并消费(读取)数据。否则，内核会停止输出(写入)SQL全量性能信息。这个设计很巧妙，不用担心SQL全量日志会占用空间(管道文件的大小很小)。它需要有客户端不停的读取这个管道文件。这个客户端就是MySQL的运维平台的监控客户端。

SQL全量输出的信息格式如下：

#time:1464861208504797
#user@host:root[root] @ [127.0.0.1]
#db:chuck
#table_name:tt
select * from tt;
#query_time:0.000232
#lock_time:0.000000
#rows_sent:0
#rows_examined:0
#rows_affected:0
#innodb_pages_read:0
#innodb_pages_io_read:0
#id:342

SQL全量输出通过参数log_sql_info控制。默认是false，开启就设置为true。开启后要先保证监控客户端在读取管道文件。通过命令show Sql_log_info_status 可以查看SQL全量日志输出状态。即使在天猫双11大促期间，这个功能也是开启的，对性能的影响在2%以内，完全可以接受。

OceanBase的SQL全量日志

OceanBase是阿里巴巴和蚂蚁金服完全自主研发的通用的分布式关系型数据库，其在SQL执行和性能诊断方面的逻辑大量参考了ORACLE的设计思路。所以在OceanBase里也有执行计划、硬解析和软解析，以及类似AWR设计的性能视图等。同时OceanBase还有自己的创新就是提供了一个SQL审计的功能。

OceanBase的视图(v$sql_audit)会以类似队列形式缓存当前集群内运行的所有SQL的执行性能信息，并且包括那些执行报错的SQL(报错原因很多如内部执行超时、锁等待超时、违反约束等各种原因)。这部份数据全部在内存里，内存大小由参数控制(sql_audit_memory_limit)，默认是3G。超出这个大小的SQL审计信息就遵循先入先出原则，并不会保存在磁盘文件里。当然有人可能会说如果OceanBase集群挂了这个数据不就丢失了吗？实际上由于OceanBase独特的高可用特性，不是那么容易挂掉的。此外，OceanBase运维平台(OCP)也会部署监控客户端定时拉取这个性能数据用作后面分析。
OceanBase的SQL审计功能由参数(enable_sql_audit)控制，可以针对每个节点设置是否开启。默认值是开启的(true)。同样即使在蚂蚁双11大促期间，这个参数也可以不关闭，由此可见开启这个对性能并没有什么影响。

OceanBase的SQL审计视图(gv$sql_audit)包含了SQL文本以及执行的详细信息，如执行节点、总耗时、等待时间、服务时间、逻辑读、影响行数、等待事件及其参数和其他信息，内容非常丰富。如下图

下面是查询某个节点上某个租户的某个用户的的最近的 100条SQL执行信息

SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */ substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.query_sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024,2) req_mem_mb, is_executor_rpc, is_inner_sql
FROM gv$sql_audit s
WHERE  s.tenant_id=1001 and user_name='testuser'  and svr_ip in ('11.166.84.78')
ORDER BY request_time DESC
LIMIT 100;

SQL全量日志的用处

数据库诊断引擎 CloudDBA

在电商有上万的AliSQL实例，上千的数据库菜鸟开发，上百个高流量敏感的核心业务。然后支持业务的DBA团队只有十几个人，其中还不乏兼职的运维平台开发人员。数据库的性能问题不能再依赖DBA去一一分析，更别提提前发现预防性能问题。当AliSQL的运维平台收集了所有实例7*24小时的SQL全量执行信息后，接入到一个数据库智能诊断平台中，就能依据一定的规则和机器学习算法去识别性能异常并告警给开发。同时平台自动读出该SQL的详细性能数据、执行计划甚至给出优化建议(如调整索引等)给到开发。极大的降低了研发同学做数据库诊断的技术门槛。在电商，这个产品叫CloudDBA，在阿里云数据库上也有相应的云产品，思路是一样的。

下面是CloudDBA曾经某个版本的一个功能示意图。能看到全部SQL的QPS和RT,以及每个SQL在不同区间的分布状况。 

进一步点击SQL前面的id值，显示该SQL的不同时间段的执行时间RT分布。

蓝色的是链接，还可以进一步做性能下钻分析。

CloudDBA的价值在于降低了业务研发做数据库诊断的技术门槛，同时解放了业务DBA的人力，专心去做更难的事情。不过也不能过度夸大其作用。作为一个大规模的性能诊断平台，无论是基于规则(DBA的经验)还是基于机器学习(计算机的经验)，都跟统计学类似，都可能存在”不命中“或者”误判“的情形。

OceanBase数据库云平台 OCP

同样，OCP采集了各个OceanBase集群的SQL审计信息后也提供页面可以查看TOP SQL信息，并提供SQL性能分析下钻功能。

点击 蓝色序号链接，可以查看SQL明细

这些还只是的简单的展示。在SQL审计日志信息上OCP也可以继续发展自动诊断，定位问题SQL，并给出优化建议。

总结

阿里数据库内核产品能输出全量SQL执行性能数据，数据库的PaaS平台在这些数据基础上可以开发自动化数据库性能诊断产品。这个可以极大的降低业务研发做数据库诊断的技术门槛，同时也解放DBA人力。

SQL全量日志同样可以做为审计的依据，它可以证明某笔记录是某个SQL修改的，以及某个SQL发生过。

OceanBase在运维和性能诊断上提供了丰富的特性非常适合第三方开发衍生产品。

参考

AliSQL SQL全量日志作者博客：https://www.cnblogs.com/cchust/

阿里在数据库智能优化路上，做了哪些探索与实践？

推荐阅读