MySQL调优的一些简单经验

发现问题并解决

工作中使用mysql数据库，在十万级的数据量下发下有一些查询统计的sql执行异常慢，达到7秒左右，本来以为是网络问题，后来在本地、上线后服务器环境（数据库在内网）下仍然很慢，所以排查了一下，很简单的就解决了；

sql监控发现定位问题

使用的druid数据源，自带有sql监控功能还是比较好排查的；
其他数据源如C3P0我还不知道怎么记录慢sql，是得补一下；hibernate也有慢sql的记录功能，jfinal的好像也不知道；
不过可以在数据库层面监控慢sql，比如mysql的set global long_query_time可以设置mysql日志记录执行时间大于该值的sql记录；oracle功能更强大复杂，就不展开说了；

逐条分析

SELECT
			count(*) count,
			b.organ_id organId,
			e.name organName,
			sum(if(d.mark = '01', 1, 0)) AS a01,
			sum(if(d.mark = '02', 1, 0)) AS a02,
			sum(if(d.mark = '03', 1, 0)) AS a03,
			sum(if(d.mark = '04', 1, 0)) AS a04,
			sum(if(d.mark = '05', 1, 0)) AS a05,
			sum(if(d.mark = '06', 1, 0)) AS a06,
			sum(if(d.mark = '07', 1, 0)) AS a07,
			sum(if(d.mark = '08', 1, 0)) AS a08,
			sum(if(d.mark = '09', 1, 0)) AS a09,
			sum(if(d.mark = '10', 1, 0)) AS a10,
			sum(if(d.mark = '11', 1, 0)) AS a11,
			sum(if(d.mark = '12', 1, 0)) AS a12 ,
			sum(if(d.mark = '13', 1, 0)) AS a13
		FROM
			t_app_check_asset_detail a
		LEFT JOIN t_app_check_asset b ON b.id = a.check_asset_id
		LEFT JOIN t_b_asset_info c ON c.asset_code = a.asset_id and c.approve_status = 1
		LEFT JOIN t_b_asset_type d ON d.mark = c.asset_type
		left join sys_organ e on e.id = b.organ_id
		WHERE
			b.approve_status = '3'
			and b.create_time = (select MAX(create_time) from t_app_check_asset where organ_id = b.organ_id)
		GROUP BY
			b.organ_id
		order by
			e.name

客户端工具执行分析

如图所以，sending data 占比超高，不正常，一般是连接条件未添加索引导致，根据连接条件逐个索引添加，a.check_asset_id列添加索引后，查询时间恢复到0.02s-0.02s之间，勉强可以接受；其他所有join条件加上索引后，查询时间减少幅度不明显；
另一条语句
freeing items占比较高，时间较长，主要是因为结果集数据过多导致，加上limit0，100，后查询用时0.04秒左右；可以接受；一般分页是先count，后limit查询；只要limit时间可接受，count时间可以接受就没有啥问题；
接下来查看了一下count语句，发现时间较长，仍是Sending data占比过高，再排查一次join条件的索引，发现所有的相关列索引已存在，只能从其他地方排查；
发现语句中有个AND b.create_time = ( SELECT MAX(create_time) FROM t_app_check_asset WHERE organ_id = b.organ_id )
该条件去掉后查询时间在0.6s左右，相差比较大；但是该条件业务需要，未找到更合适的替换方案；接下来尝试用EXPLAIN语句对查询进行分析

EXPLAIN语句

EXPLAIN的用法：直接在要执行的sql语句前加EXPLAIN关键字，会返回如下结果
id：SELECT识别符。这是SELECT的查询序列号。

分情况解决

经过exlpain排查以后并没有有效的解决问题，尝试逐句分析也没有什么成果，后来还是聚焦max()函数，是查询各个平台时间最晚的一条记录，关键节点语句上面已经给出，删除掉该语句后查询时间减少到0.6s左右，在create_time列加上索引后时间优化不明显，后来灵机一动，在create_time 和 orrgan_id列上加上联合索引后查询时间减少到1.4s左右

Sending data

Creating sort index

后记

觉得还是防患于未然比较好，在数据库设计阶段，就考虑以后的查询效率，根据实际业务情况设置好索引