编辑推荐

网易数帆开源API网关与容器云项目，让云原生生产落地“多快好

网易汪源：统一负载与多云环境的“开放姿态”，才是云原生

网易数帆如何用 Kubernetes“原语”搞定云原生中间件

快手打新挤爆券商系统，网易数帆推出券商稳定性保障方案

探索智慧校园新模式，网易有数在教育行业的实践分享

金融行业大数据治理之路——数据模型篇

小优化，大收益 —— 记一次MySQL数据库优化实践

勿忘初心2018-10-24 11:31

此文已由作者施勇授权网易云社区发布。

欢迎访问网易云社区，了解更多网易技术产品运营经验。

问题背景

2014 年 10 月份，我们协助维护的一个产品 DDB 数据库 [ DDB: 网易杭州研究院开发的分布式数据库软件，利用多达几十上百个 MySQL 数据库实例来扩展整个数据库的容量和性能，使用简便如同单个 MySQL 数据库。 ] 计划在最近停服维护的时候做数据库扩容。 DBA 和 SA 汇报的情况是 MySQL 数据库服务器的 CPU 占用过高，晚上 19 点到凌晨 1 点多的业务高峰期间需要占用 32 个超线程核心的 24 个，都是 user cpu ，磁盘 IO 占用很少，单个 MySQL 实例高峰期间 QPS 为 3000 左右。 DBA 排查过 DDB 底层 MySQL 数据库节点的超过 1 秒的慢查询，没有发现能够造成 CPU 占用过高的查询；也利用 tcprstat[ tcprstat: Percona 公司开发的一个开源的 TCP 响应延迟统计工具。 ] 分析了数据库的响应延迟 rt ，平均不到 2 毫秒（如图 1 ）， SQL 性能都很好，应该不太可能优化了，只有扩容才能提升数据库容量来支持业务发展带来的更高请求。

图1 tcprstat统计结果

疑问

在得知此产品DDB因MySQL数据库占用CPU过高需要扩容之后，我就觉得很可疑，是否有优化措施来降低数据库的CPU占用呢？

MySQL数据库CPU占用过高的原因，无非有以下几种：

高并发频繁创建连接、关闭连接等。
频繁执行CPU消耗型的函数，比如md5()、sha()、encode()等。
内存扫描过多的记录：

没有合适的索引的SQL被频繁执行
频繁读取缓存中的整表数据
频繁执行count/group by/order by等，索引区分度不好
确实是正常请求导致，并发量和QPS都非常高，热点数据量小且都被缓存

第1种情况，频繁创建关闭连接，可能造成服务器CPU突升和突降，很少有产品会开发出连续频繁创建关闭连接的代码，与我们观察到的服务器CPU平稳上升的形态不符合，得到排除。另外根据我对此产品的了解，CPU消耗型的函数是不会频繁使用的，那么最可能就是因数据库内存扫描过多记录导致服务器的CPU过高。又观察到高峰期间单节点的QPS只有3000/s左右，若是正常业务请求并且数据库SQL都很高效全部缓存读取记录的话，单节点能达到上万的QPS，那么肯定是SQL或索引有问题。

排查与调优

MySQL数据库内存记录扫描过多记录导致服务器CPU过高，那么这类SQL语句的执行速度肯定不会快，由此想到最便捷的一个方式就是利用MySQL数据库的慢查询日志。

首先我们查看了MySQL数据库原有的慢查询日志，原先配置慢查询条件是超过1秒会记录日志。虽然发现原有日志中有扫描记录多（rows_examined）且执行时间超过1秒的SQL，但这类SQL的查询频繁度不是很高，是定期统计用途的，的确存在索引优化的可能，却不是cpu持续这么高的根源。

原有的慢查询日志没有线索，接下去怎么做呢？这里教大家一个简单的数据库调优方法：降低慢查询时间（long_query_time）[ long_query_time: MySQL数据库慢查询配置参数，单位是秒。MySQL数据库可以将执行时间超过此配置的所有SQL记录下来，以供分析。从MySQL-5.1.21版本开始，此参数支持最小统计粒度可以为毫秒。]，逐步找出问题SQL，实施SQL优化排除掉此类SQL；不断循环，直到将所有SQL的执行时间都降低到满意的程度为止。

在这次排查中，因为原先的long_query_time=1的设置没有产生频繁的扫描记录多的SQL语句，所以我们先尝试将慢查询时间设置为500毫秒（set global long_query_time=0.5），再次观察慢查询日志，没有发现。

# User@Host# : xxx[xx_online] @ [10.120.200.80] 

# Thread_id: 2729564 Schema: xx Last_errno: 0 Killed: 0 
# Query_time: 0.015428 Lock_time: 0.000051 Rows_sent: 0 Rows_examined: 5041 Rows_affected: 0 Rows_read: 5041 

# Bytes_sent: 77 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_t

able_sizes: 0 

# InnoDB_trx_id: 101A2CB97F 

SET timestamp=1413856254; 

SELECT ACCOUNTID FROM ROLE WHERE (ROLE 

= 600) AND (CHANNELID = 1944016);

继续降低到100毫秒，还是没有发现问题SQL。继续降低long_query_time为10毫秒后，发现有很多如下慢日志：

观察到这类SQL每次扫描都有5000多记录，但返回记录数基本是0行，有少量是返回1行，并且在业务非高峰期间也执行得比较频繁。再看ROLE表的定义：

CREATE TABLE `ROLE` (  

`ACCOUNTID` int(11) NOT NULL COMMENT 'xxx',  

`CHANNELID` int(11) NOT NULL COMMENT 'xxx',  

`ROLE` int(11) DEFAULT NULL COMMENT 'xxx',  

PRIMARY KEY (`ACCOUNTID`,`CHANNELID`),  

KEY `IDX_CHANNELID` (`CHANNELID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='xxx' ;

虽然有CHANNELID上的单键索引，但每次查询扫描的记录数相比返回记录数过多，存在索引优化的可能，即单键索引修改为(CHANNELID, ROLE)的组合索引肯定能够降低查询扫描的记录数。由于此表的数据总量不大，并且早上业务不处于高峰期间，我们直接修改索引后，数据库的CPU占用马上降低了，并且10毫秒的慢日志里也没有再出现这类语句，说明我们的优化措施是有效的。整个排查和优化过程耗时10分钟左右。