一次数据库查询优化记录

线上出现了一个问题：某界面数据量不大，但请求却非常慢，需要接近两秒才显示报表。

 一、问题定位

可以看到：总请求花了1.84s，其中99%都在Waiting（TTFB）这一步骤，就是后台请求太久了。

SELECT
	A.sn,
	time,
	IFNULL( num, 0 ) abnormalDataNum,
	'' tendencyView 
FROM
 /* 计量器具表关联所有的实时数据表，统计pstate=0 的行数，按照计量器具表的SN分组，按照数目倒序 */
	( SELECT DISTINCT sn FROM hecm_measure_device WHERE unit_id = '1001' ) A 
	LEFT JOIN (
	SELECT
		sn,
		time,
		count( 1 ) num 
	FROM
		(
		SELECT sn,time,kwhli VALUE FROM electric_real_time WHERE pstate = 0 UNION
		SELECT sn,time,weightflow VALUE FROM weight_real_time WHERE pstate = 0 UNION
		SELECT sn,time,sumflow VALUE FROM steam_real_time WHERE pstate = 0 UNION
		SELECT sn,time,heatflow VALUE FROM heat_real_time WHERE pstate = 0 
		) C 
	GROUP BY sn 
	ORDER BY time DESC ) B ON A.sn = B.sn 
ORDER BY
	num DESC

由于逻辑比较简单，后端就这一个行为：将以上SQL语句查询执行结果返回给前端，基本可以断定问题出在这条查询语句。

初步分析：业务是查看接入该系统的所有设备状态为异常的数据条数，并按照条数倒序排列。

接下来进行SQL调优：

二、SQL调优

（1）解释SQL

EXPLAIN [SQL];

 可以看到有4个Using Temporary即临时表，这是我们要优化的内容。

以及Using where尽量都改成索引，以及减少排序，理论上我们只用对最后联合查询结果排序一次

将语句粘贴进数据库，查询花了1.2s，观察执行状态：

扫描行数太多了，127条

（2）观察表结构

DESCRIBE [TABLE];

主表：

其中一个从表：

可以看到，主表的联查字段sn不是索引，从表的pstate不是索引，这是优化的空间。

（3）优化表结构和执行语句

a) 给从表添加索引

ALTER TABLE electric_real_time ADD index(pstate);
ALTER TABLE weight_real_time ADD index(pstate);
ALTER TABLE steam_real_time ADD index(pstate);
ALTER TABLE heat_real_time ADD index(pstate);

/* 删除索引*/
ALTER TABLE [TABLE_NAME] ADD index([COLUMN_NAME]);

更改表结构添加索引后，执行时间几乎没有变化，还是1s左右。

再来EXPLAIN一下：

 发现几乎查询方式几乎没有变化，唯一变了的possible_keys多了pstate。

仔细看，为什么只有heat_real_time的type是更快的ref，key是pstate？仅仅是因为它在最前面？

b)  优化SQL：将 where pstate = 0 改为 where pstate = '0'

由于pstate字段是varchar，这里用到的 where pstate = 0 ,传入数据库的是数字类型，可能会引起类型转换而导致索引失效，改成 where pstate = '0'。

再执行，速度稳定在0.5s，执行效率提升了50%

再EXPLAIN一下：

查看执行状态：

可以看到速度提升了50%的背后，是索引全部用上了，扫描行数由127W降到了17.7万。

继续优化语句，分析业务，由于这里用不到各个表计的所有异常数据值，所以去掉kwhli VALUE

继续优化语句，分析业务，由于这里用不到按照时间排序，之前是用来看最近一条异常数据的，所以去掉order by time desc，同时可以去掉一个从计量器具查询where unit_id = '1001'的虚拟表，过滤条件拼接在最后面。去掉不必要的字段可以提高查询效率。

最后的SQL：

	SELECT 
	A.sn,
	IFNULL( num, 0 ) abnormalDataNum,
	'' tendencyView 
FROM
 /* 计量器具表关联所有的实时数据表，统计pstate=0 的行数，按照计量器具表的SN分组，按照数目倒序 */
	 hecm_measure_device A 
	LEFT JOIN (
	SELECT
		sn,
		count( 1 ) num 
	FROM
		(
		SELECT sn,time FROM electric_real_time WHERE pstate = '0' UNION
		SELECT sn,time FROM weight_real_time WHERE pstate = '0' UNION
		SELECT sn,time FROM steam_real_time WHERE pstate = '0' UNION
		SELECT sn,time FROM heat_real_time WHERE pstate = '0' 
		) C 
	GROUP BY sn ) B ON A.sn = B.sn and A.unit_id = '1001'
ORDER BY
	num DESC

执行时间：0.35s，差不多已经不算一个很慢的接口了。