记一次mysql调优

入职后接到任务,对整个系统进行mysql相关的调优,此博客记录整个过程,作为积累;
拿到一个任务和需求先别急着去做,首要是要去分析,不妨拿出一张白纸,写下步骤,第一步第二步...

现场现象

测试在某天反馈前台某些报表的查询很卡,对于检索也会很慢,有时导致超时,甚至时间过长导致锁表,定时分区任务执行不了,后续数据不入库的情况。

情况分析

首先确定是什么原因导致的,自己count查询得知目前表里面有千万的数据量,再通过测试的描述大概可以确定是数据量大之后的低性能sql语句导致的慢查询。大家都知道,mysql的瓶颈在于千万级别,加上sql性能不好,加上连表查询,查询慢的就更加明显的体现出来。

网上查找可行解决方案

  1. 对数据库进行分区、分表,减少单表体积;(已按天进行分区)
  2. 使用NoSQL 等辅助解决方案,如 Memcached、Redis;(采用redis进行监控墙和其他地方的缓存)
  3. 迁移到其它数据库。如es、商业的 Oracle;(客户现场换数据库不太现实)
  4. mysql参数调优;(对调优人要求较高)
  5. 基于sql调优;(代价最低,效果最好)

确定方案

首先进行sql调优输出相关文档

效果不太好的化在进行相关参数调优

不行的化再考虑改变现有部署方式,比如读写分离,主从的架构

最后考虑更换底层数据库如es数据库或者大数据的方案,比如hadoop生态

sql优化前储备

对于摘得的sql进行分析,首先你去了解一个技术的时候需要知道相关的概念,比如一些索引的知识,什么是索引及常见索引是什么,索引的工作原理,推荐可以先阅读下《高性能mysq 第三版》—(工欲善其事必先利其器)了解里面跟调优相关的,比如引擎、索引这些基本知识点,书里面和网上都有就不做搬运工了,自己查阅和百度。

高性能mysql第三版:https://pan.baidu.com/s/1e0LrUx0zXrpdgADTHFM5tA 提取码:wdi6

环境准备

一般是公司生产环境,实际中保留即可,此时为了可以复现,大致准备环境如下:
服务器内存16G
linux centos7.5
cpu 8核

单节点mysql 5.7版本

数据准备

配合测试冲数据(或者自己写程序冲,但是一般不符合实际情况)到一定的量,便于后面的查询和验证,此为前期准备。生产数据找测试要的一个大表400万条,小点的表有200万条,通过session_id,type组合索引关联,两张表都进行了按天分区的处理,索引建的较多,有十几个(总字段40左右)。

[400万数据脚本]!(https://pan.baidu.com/s/18MCxSqB6pTTbHAG0vEdLdw 提取码:kfwb
解压密码:111)

表的设计为历史遗留,无力吐槽和改变,建表和索引各种不规范,读者切勿模仿,具体的规范可以参见《阿里编程规范》。暂时只能接收这些不规范,毕竟已经上线,再去改代价太大。。。

优化准备

框架配置文件中开启打印sql,操作前台进行sql的抓取,按模块分类写入一个文本,对应写下供后续使用,整个sql较多,摘了几天。。大致分为两类,一类是检索,一类是统计,每一类又分为两类,单表查询和多表联合查询,也就是四类。列表如下:

  1. 检索 - 单表查询
  2. 检索 - 多表联合查询
  3. 统计 - 单表查询
  4. 统计 - 多表联合查询

首先对数据分布进行分析,自己写sql去统计一些字段的规律,看过高性能mysql就会知道,有时候sql走不走索引是基于代价的,如果觉得这个数据区分度不大,还不如走全表就不会走索引,索引建议大概知道数据的分布方便后面结合理论去解释。

数据特征:

select count(auditlog_id),MAX(recordtime) ,MIN(recordtime) from b$session;

MySQL 插入调优实战_优化

通过类似语句得到如下特征

MySQL 插入调优实战_优化_02



根据摘取的sql进行优化

主要通过explain命令得到每个语句的执行计划,再根据业务的需要进行调优,达到一样的结果,但是时间尽可能的少。下面挑选了一些sql进行展示,explain部分的化可以读者自行执行观察思考

1.检索 - 单表查询优化

全量查询5.180s

SELECT
        domain.`Name` domainName,
        org.`Name` orgName,
        HostName,
        serverportinfo.ServerName,
        tinstancedatabaseinfo.InstanceName instanceName,
        count,
        ClassName,
        thostinfo.HostIP 
    FROM
        (SELECT
            COUNT(0) count,
            `b$session`.DbInfoID  
        FROM
            `b$session`,
            tinstancedatabaseinfo,
            serverportinfo,
            thostinfo,
            torganization 
        WHERE
            1=1 
            AND recordtime >= '2018-06-12 00:00:00' 
            AND recordtime <= '2019-06-12 14:33:35' 
            AND tinstancedatabaseinfo.`DbInfoID` = `b$session`.`DbInfoID` 
            AND serverportinfo.`ServerPortID` = tinstancedatabaseinfo.`ServerPortID` 
            AND thostinfo.`HostID` = serverportinfo.`HostID` 
            AND torganization.`OrganizationID` = thostinfo.`OrganizationID` 
        GROUP BY
            `b$session`.DbInfoID) tabledata,
        tinstancedatabaseinfo,
        serverportinfo,
        thostinfo,
        torganization org,
        torganization domain,
        `tbasetype` 
    WHERE
        tinstancedatabaseinfo.`DbInfoID` = tabledata.`DbInfoID` 
        AND serverportinfo.`ServerPortID` = tinstancedatabaseinfo.`ServerPortID` 
        AND thostinfo.`HostID` = serverportinfo.`HostID` 
        AND org.`OrganizationID` = thostinfo.`OrganizationID` 
        AND org.ParentId = domain.OrganizationID 
        AND serverportinfo.ServerType = tbasetype.BaseTypeID 
    ORDER BY
        count DESC limit 10
-- 返回数据8条,执行时间5.180s

业务分析的化只能简化格式,对于分区的扫描无可避免,一些条件也无可避免

优化后4.895s

SELECT
	domain.`Name` domainName,
	org.`Name` orgName,
	HostName,
	serverportinfo.ServerName,
	tinstancedatabaseinfo.InstanceName instanceName,
	count,
	ClassName,
	thostinfo.HostIP
FROM
	(
		SELECT
			COUNT(0) count,
			`b$session`.DbInfoID
		FROM
			`b$session`
		WHERE
			1 = 1
		AND recordtime >= '2018-06-12 00:00:00'
		AND recordtime <= '2019-06-12 14:33:35'
		GROUP BY
			`b$session`.DbInfoID
	) tabledata,
	tinstancedatabaseinfo,
	serverportinfo,
	thostinfo,
	torganization org,
	torganization domain,
	`tbasetype`
WHERE
	tinstancedatabaseinfo.`DbInfoID` = tabledata.`DbInfoID`
AND serverportinfo.`ServerPortID` = tinstancedatabaseinfo.`ServerPortID`
AND thostinfo.`HostID` = serverportinfo.`HostID`
AND org.`OrganizationID` = thostinfo.`OrganizationID`
AND org.ParentId = domain.OrganizationID
AND serverportinfo.ServerType = tbasetype.BaseTypeID
ORDER BY
	count DESC
LIMIT 10

2.检索 - 联合查询优化

全量21.908s

SELECT
        operatetype,
        count(0) count 
    From
        b$session,
        tinstancedatabaseinfo,
        serverportinfo,
        thostinfo,
        torganization 
    Where
        b$session.DbInfoID = tinstancedatabaseinfo.DbInfoID 
        AND serverportinfo.ServerPortID = tinstancedatabaseinfo.ServerPortID 
        AND thostinfo.HostID = serverportinfo.HostID 
        AND torganization.OrganizationID = thostinfo.OrganizationID 
        AND recordtime >= '2019-05-01 00:00:00' 
        AND recordtime <= '2019-06-05 16:07:55' 
        AND operatetype in (
            'USER_LOGIN', 'USER_LOGOUT' ,'CREATE','DELETE','UPDATE','SELECT'
        ) 
    GROUP BY
        Operatetype

优化后5.61s (时间跨度更大)

-- 优化后执行时间优化后,优化后走了索引operatetype所以快
SELECT
	operatetype,
	count(0) count
FROM
	b$session
WHERE
	recordtime >= '2018-05-01 00:00:00'
AND recordtime <= '2019-06-05 16:07:55'
AND operatetype IN (
	'USER_LOGIN',
	'USER_LOGOUT',
	'CREATE',
	'DELETE',
	'UPDATE',
	'SELECT'
)
GROUP BY
	Operatetype
order by count desc
-- 执行时间5.61s

3.统计 - 单表查询优化

柱形图原sql全量12.213s

SELECT
 	username,
 	COUNT(0) count
FROM
	b$session,
	tinstancedatabaseinfo,
	serverportinfo,
	thostinfo,
	torganization
WHERE
 	b$session.DbInfoID = tinstancedatabaseinfo.DbInfoID
AND serverportinfo.ServerPortID = tinstancedatabaseinfo.ServerPortID
AND thostinfo.HostID = serverportinfo.HostID
AND torganization.OrganizationID = thostinfo.OrganizationID
AND recordtime BETWEEN '2019-05-01 00:00:00'
AND '2019-05-02 00:00:00'
GROUP BY
 	Username

优化后3.064s

SELECT
 	username,
 	COUNT(0) count
FROM
 	b$session 
GROUP BY
 	username

4.统计 - 联合查询优化

联合查询的时候发现最大的问题就是两个表都有分区,但是代码中大部分的时间条件只带了一个表的时间条件,导致另一个表扫了全表
 所以不管查询时间范围多还是少,必定有一个表会扫全表,所以在条件中加上两个表的时间条件即可。
 但是当时间范围大或者是当前时间范围数据量很大的时候还是不能很好的解决,具体解决办法见文末。

服务器审计信息全量33、27 s

SELECT
        HostName,
        HostIP,
        b.InstanceName Instance,
        e.NAME Realm,
        f.NAME Groups,
        c.ServerName Server,
        ct_username,
        ct_clientip,
        ct_application,
        ct_auditlog,
        count 
    FROM
        (SELECT
            DbInfoID id,
            count(DISTINCT username) ct_username,
            count(DISTINCT clientip) ct_clientip,
            count(DISTINCT applicationname) ct_application,
            count(CASE 
                WHEN isauditlog = 2 THEN 1 
            END) ct_auditlog,
            count(*) count 
        FROM
            b$session 
        WHERE
            1 = 1 
            AND recordtime >= '2018-05-28 00:00:00' 
            AND recordtime <= '2019-05-28 14:30:46' 
        GROUP BY
            id) s,
        tinstancedatabaseinfo b,
        serverportinfo c,
        thostinfo d,
        torganization e,
        torganization f 
    WHERE
        s.id = b.DbInfoID 
        AND b.ServerPortID = c.ServerPortID 
        AND c.HostID = d.HostID 
        AND d.OrganizationID = e.OrganizationID 
        AND e.ParentId = f.OrganizationID 
    ORDER BY
        count DESC  
-- 时长33、27 S

优化后23.48s

SELECT
 	DbInfoID id,
 	count(DISTINCT username) ct_username,
 	count(DISTINCT clientip) ct_clientip,
 	count(DISTINCT applicationname) ct_application,
 	count(CASE WHEN isauditlog = 2 THEN 1  END) ct_auditlog,
 	count(*) count 
FROM
 	b$session 
WHERE
 	1 = 1 
 	AND recordtime >= '2018-05-28 00:00:00' 
 	AND recordtime <= '2019-05-28 14:30:46' 
GROUP BY
 	id 
ORDER BY
	count
DESC  
-- 执行时间23.48s

总结

sql优化后对整个系统的慢查询起到了指导作用,知道了哪些可以优化哪些不可优化,输出的文档进行评审完善后,相关人员对项目中存在的情况进行改造,使得项目中可以优化的模块响应更快,对于那些无法优化的集中处理,比如报表类采用缓存来实现,放在Redis中,后台通过定时任务去更新,前台直接查询缓存,查询不到时再查寻mysql并把结果缓存,但是对于连表实时查询的情况还是没有很好的解决办法,毕竟触及mysql瓶颈,只有限制扫表的分区数,即数据量来控制。

后续公司客户(亿级项目)在使用中反馈了这样一种情况:现场数据有十几个亿,急于解决。。如果是你你该如何解决?留言发表建议。

后续的化抽时间完善方案,请读者持续关注