基于ClickHouse分析和优化MySQL的业务运行

2017一年里，脉脉发展出了一套MySQL统计方法。借用此方法，业务方对数据库的使用效率得到长足提高，同时也解决了一些之前困扰很久的问题。详情可参考此文 《脉脉的MySQL统计工具及通过统计手段处理MySQL问题 》

在此基础上，我们又在基础架构方面和数据解析方面做了进一步的优化，并且依靠更丰富和实时的数据做了更多更深入的实践，本文将介绍最新的基础架构以及部分实践经验。

一、观察业务的问题

这部分列举一些日常工作中dba可能会好奇或者真正会遇到但又处理起来比较麻烦的问题

1、先看一个监控图

可以看到，增删改查的数量以及变化的趋势一目了然，根据对业务的基本了解，也大体可以推断出qps的起伏是由于用户在不同时间段的活跃程度不一样导致的。

但是如果再进一步思考这些问题：这一望无际的绿色（select）具体都是些什么sql？他们的数量都是多少？贡献qps最多的sql是什么？他们的数量随着时间变化的趋势又是什么样子？一张简单的监控图所能展现的信息恐怕就不够了，而如果再继续追问：在11点前后qps有较大波动，是为什么？是某个sql的qps突然飙高，还是业务整体性抽风？如果是单条sql突然飙高，他来自哪台服务器，之前的运行规律是什么？上面的这些问题或许通过咨询研发可能可以得到一些线索，但是想掌握详细的情况可能不太容易。

2、表和字段

在日常的工作中，哪些表查询量大，哪些表查询量小，我们多少会心里有数，但是哪些表已经没有查询了，恐怕不是一件很好确定的事情。

由于公司的快速发展和在业务上的不断试错，导致数据库里有很多“可能”没用了的表，之所以说“可能”，是因为真的没有谁说得清楚这个表到底有没有用。即使不考虑相关研发离职这种极端情况，也可能因为时间太久或者业务的快速迭代导致相关研发也不敢保证这个表到底还有没有用了，如果这是一张占磁盘空间比较大的表，让他这样不明不白的留在磁盘上显然不是一件让人愉快的事情。还有更糟糕的——对于一张明显比较大的表，dba终究会想起来关注一下；而对于更多的单表不那么大但是加在一起所占空间也不容小视的表来讲，他们则是彻底待在被遗忘的角落了。对于前者，我们或许可以有限的通过查询全日志或者审计日志来判断一下是否还有读写，而对于后者，要想一一搞清楚成本就比较高了。

更进一步，由于类似的原因，一张表（尤其是宽表）里的字段也会有同样的问题——哪些字段访问量大，哪些字段访问量小，哪些字段在业务上关联度更大，哪些字段已经不用了。如果能得到这些信息，将对业务上的优化有很大的帮助。

3、用户权限

这个相对简单些，如果在做权限控制的时候是以表为单位授权的话，一切都是一目了然的。而如果是对库授权甚至图省事对*.*做授权的话，事情就要麻烦多了。再设想一个场景（我相信很多dba都遇到过），如果研发新加了一台服务器，然后对dba说“我新加了一个服务器，帮我按照其他服务器的权限授权”。如果库表的业务所属划分的比较好，这自然不是一个问题，但是如果不是这样，可能会让dba和研发都比较头疼——授权工作会在dba的反复操作和研发的反复报错之间曲折前行。

二、数据的收集、解析和存储

在上一篇文章里，这一方案最终确定为：原始数据（审计日志） -> flume interceptor解析日志及sqltext ->hdfs，通过hive进行查询。后来在此基础上又添加了从hdfs转存到clickhouse的环节，最终通过clickhouse查询。

在引入了clickhouse后，查询的效率大幅度提升，绝大多数统计需求可以做到在10秒以内得到结果——相比起之前通过spark-sql的查询，速度可以说有了数量级的提升。然而这一方案仍然有很大的改进空间，主要有以下几点：

1）流程繁琐，既然数据最终会存到clickhouse，那么现有的先存到hdfs然后转存至clickhouse的模式就显得没有必要，如果可以直接存到clickhouse显然是最好的；

2）解析sql的功能仍然不够强大和细致——原先的功能只能把一个sql中涉及到的表名，条件字段，查询（更新）字段解析出来，但是不会做更多的解析。参考下面三个sql：

• select id, name, qqfrom users where id = 1 and status = 1;

• select id, name, qqfrom users where id in ( 1000个id ) and status = 1;

• select id, name, qqfrom users where id = 1 or status = 1;

在原先的方案里，这三个sql都会被解析成 {tables: ‘users’, items: ‘id,name,qq’, columns: ‘id,status’}。显而易见的，这三个sql是完全不一样的，其背后的业务含义也完全不一样（不要深究sql的合理性，只是例子），但是在统计结果里，则会被展示为相同的sql样式，这在当时对业务只需要粗粒度分析的背景下是可以接受的，而如果想更加细致的观察和分析就无能为力了。

3）对业务的观察仅限于sql层面，原始数据是mysql的审计日志，因为设计日志解析器时考虑不够全面，其中包括的信息有很多没有被解析和存储，包括执行状态（成功失败），操作类型（登录登出）等。

经过调研，决定放弃原有的flume-> hdfs -> clickhouse的方案，而更新为使用clicktail（详情请见https://github.com/Altinity/clicktail）直接写入clickhouse，这样的好处显而易见：clicktail比flume轻量化很多，易于配置和部署；直接写入clickhouse，提升了数据更新的实时性；clicktail自带对审计日志的解析功能，只需要再人为添加sql解析功能即可，开发工作量小。在原有的sql解析功能上，添加的对sql的归一化处理并计算checksum，保证了对sql样式的精细区分，并存储了sql原文的前300个字符，这可以观察sql详情，虽然对于有些比较长的sql（比如查询条件in了上千个id）不能完全展示，不过也足够看明白细节了，当然它最重要的功能并不仅仅是要让dba看到sql细节，在统计工作中sql的详情页是非常重要的。

作为补充，又把慢日志以同样的解析策略上传至clickhouse，唯一的区别是慢日志是完全保留sql原文而非前300个字符，因为慢日志的量要少的多。

在这一切确定并部署上传后，存储到clickhouse里的数据大约是这个样子：

我们可以看到用户信息，时间和sql信息。

关于sql，我们可以看到sql的原文、被归一化处理过的sql以及对应的checksum、也可以看到sql的关键信息（sql类型，库表字段信息）。有了这些，一切都是透明的了。

三、观察业务的基本方法

这部分展示如何组织和使用统计数据。

1、状态统计

状态信息是最基础也是最常要查看的信息，有助于我们定量的观察和分析业务在mysql上运行的情况

对于业务状态的统计，最直观和最全面的就是面向实例的统计 —— 某个实例在某段时间内的查询情况 —— 包括sql的样式，样例，访问什么表，（以查询为例）访问了哪些字段，条件是什么，总量是多少，来自哪个用户@ip等内容（如下图）。

这样一来，一个时间段里mysql的查询情况就被完全展现出来了，我们可以看到所有查询的sql，以及他们元信息。这可以说起到了“开全图”的效果，在此基础上，我们可以进行更细粒度的统计。可以把面向实例细化为面向表，可以看到某个表的访问情况，再进一步细化统计，可以限定它的条件字段，查询字段，用户等内容，增删改查的比例，甚至可以看到某sql的所有访问原文。

依赖上面列出的各个粗细粒度不一的状态统计信息，可以进一步的干很多事情。

2、分析业务变化情况

对两个时间段或者两个时间序列的状态统计信息进行比较，即可获取业务变化信息。上文中提到的“qps”出现较大波动的情况，就可以通过对比这两个qps高位和低位两个时间段的状态进行观察，并通过比较结果得出结论。而如果发现了比较明显的周期性变化，则可以把两个时间段扩展成两个时间段的序列，这样比较的效果会更明显。

3、变化趋势

很多个连续的时间段上的状态的累加就成了趋势，一般趋势都是针对访问量而言的。对于某个sql，比如上图中checkusm = ‘3789f41…6a9d’的sql，如果我们关注他在一天里每分钟的qps都是多少，就可以把每分钟的qps值进行累加，从而形成一个趋势数据进行观察。同样的，除了指定sql，还可以指定某个用户或者某个表观察他们的访问趋势数据——如果观察对象是整个实例，则qps的趋势数据就和监控数据一样，不过通过统计可以让数据的精度达到秒，而监控一般以分钟计。

4、访问变量的分布

如果我们把视角转移到sql本身，对于大多数sql（长度不超过300字符）我们可以根据业务的具体情况，把最重要的变量提取出来，并观察这些变量的访问分布情况以及关键的分位数据。再进一步可以把这个分布的情况或者某个变量的访问量和时间趋势结合起来，来观察业务访问的变化情况。

5、用户行为与权限观察

从用户的角度观察，可以看到某个用户在某段时间都做了哪些查询，访问了哪些表，来自于哪些ip，访问量是多少。同样的，对于某个表，也可以统计有哪些用户访问，访问量是多少。

四、实践

1、sql上下线观察

众所周知的是，由于沟通机制的问题，研发如果要新上线sql，很难做到上线前或者上线的第一时间就通知dba，久而久之则会出现很多新sql，而这些sql的访问量也通常会随着业务量的增加而增加。 通过上文提到的【 分析业务变化情况】的方法，如果把两个时间段设置为以天或者周为单位，则可以比较清楚的观察到新上线的sql，如果发现问题，则可以及时处理。

2、热数据/数据倾斜分析

对于某些高qps的sql，我们可以通过提取出sql中的关键变量，并统计其分布情况来分析sql的访问热度——这样做最直接的好处是可以判断这个sql是否适合通过缓存来减少qps并提高效率，除此以外，还可以给产品和数据分析组的工作提供一定的支持。对于慢日志，同样可以照上面的办法进行分析，不同的是，通过统计结果可以判断数据表是否存在数据倾斜，导致涉及到某个变量时sql性能不佳。

3、sql趋势数据的相关性分析

1）总趋势相关性分析：对于一个实例，我们可以通过监控数据知道它的总体qps的变化趋势，而哪些sql的趋势和总体趋势相关性最大，我们可以通过计算所有sql的qps趋势数据和整体qps趋势数据的相关系数来获得。除了可以用sql的趋势数据，也可以用更粗或者更细粒度（比如以用户或者表为观察维度）的趋势数据做分析，全面观察数据库的使用情况。

2）sql相关度分析：通常来讲，sql不会是孤立的。一个sql的执行，在其前后也会有其他sql执行，换句话说，sql是“一群一群”的。所以我们可以找到这些sql群，并由此分析业务的相关特性。或者在发现一个业务上不合理的sql的时候，可以一并观察和他同一群的其他sql，确保问题的全面解决。

3）与nginx日志的协同分析：类似的，我们还可以把sql和nginx的接口访问量进行相关性分析，这在挖掘古老的、负责人不明确的sql来源时非常有用——找到sql对应的接口，也就自然能找到对应的负责人（部门）。

4、权限回收，授权套餐化

对于权限过大的账号，回收权限是一件风险很大的事情，毕竟要确保某个账号真的没有再访问某个表或者没有再从某个ip访问数据库是比较难的，尤其是对于大周期的定时任务，观察总是很难全面。而通过存储的审计日志则可以观察很长时间段（以年计）的用户行为，确认用户要访问的库表，从而回收无关表的权限。

既然可以知道所有用户要访问的所有表，以及对应的应用服务器ip，那么我们就可以把每一个业务所属的账号权限记录下来，成为一个“套餐”，所有的权限变更操作以套餐为单位。一个典型的套餐由三部分组成，即用户名、ip列表和库表列表，当ip列表或者库表列表发生变化时，只需要对应的增加/回收套餐里的信息并在相应的数据库里做修改便可以了。这在业务扩容或者新库表上线时会带来很大的方便。

5、表或字段下线判断

随着公司的发展和开发人员的流动，很难避免的带来一些神秘的表或者神秘的字段——没人确定他们是干什么用的，还有没有在读写，能不能下线。对于这种问题，可以通过观察一个表在之前一段时间的访问情况来解决，如果既没有读也没有写，则很显然这个表没有用了。而对于只有读没有写或者只有写没有读的表，则可以以此为线索推动研发确认，做到有的放矢。

对于字段也是同理，不过因为可能会存在“select *”这种写法，所以我个人比较倾向于只观察是否有写，然后找研发做确认

五、结语和展望未来

借助clickhouse长期存储解析后的审计日志，可以让我们保留全面的，长时间段的数据，为统计分析提供足量的数据。而通过统计分析，业务方对数据库的使用已经没有秘密——我们可以深入到业务的所有细枝末节，业务方对mysql的使用从此对dba是透明的。结合性能监控，dba可以全面的掌握mysql的使用情况，不错过任何一个变化。甚至可以更清晰的了解业务状况，反向的配合和推动研发、产品部门进行业务逻辑层面的优化。再来回过头来看文章开始时提出的问题，是不是全部迎刃而解了？

接下来考虑要做的，是在现有数据的基础上，结合已有的实践经验，进一步摸索业务运行的逻辑，并对各个业务进行运行规律进行分析，从多个维度和颗粒度建立各个业务运行的模型。做到对业务运行情况的体系化观察。

作者丨纪维骁

来源丨数据库随笔（ID：MysqlPg）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱： [email protected]