记一次mysql优化过程

介绍：

有两个表 ，api_base（接口基础表）和api_agent_log（接口日志表）

api_base里保存了接口名称，有几条数据

api_agent_log里关联了api_base的id，还有接口调用时间和用时时间等（接口每调用一次，在log表里插入一条数据）有230万条数据

 

第一个案例：

目前任务需要查询：2020-02-12各接口调用次数和平均执行时间

一、刚开始是以下sql，但查询时间太长需要优化：

SELECT 

b.NAME,#接口名称
sum(IF(al.date='2020-02-12',1,0)) AS execute_sum,#接口执行总次数
avg(IF(al.date='2020-02-12',al.execute_time,0)) AS execute_avg_time #接口执行平均时间

FROM api_base b #接口基础表
LEFT JOIN api_agent_log al ON b.id=al.api_id #接口日志表
GROUP BY b.id
ORDER BY execute_avg_time desc

以上的execute_avg_time（接口执行平均时间）是错误的（2020-02-12接口总调用执行时间/log表接口总调用次数）

应该是（2020-02-12接口总调用执行时间/2020-02-12接口总调用次数）

二、后经优化（利用中间表），时间大大缩短

SELECT 

b.NAME,#接口名称
IF(al.count is not null,al.count,0) as execute_sum,#接口执行总次数
IF(al.avg is not null,al.avg,0) as execute_avg_time #接口执行平均时间

FROM api_base b 
LEFT JOIN 
(select l.api_id as api_id,count(l.api_id) as count,avg(l.execute_time) as avg from  api_agent_log l where l.date='2020-02-12' GROUP BY l.api_id) al
#中间表 （先按api_id分组查询出接口执行总次数和接口执行平均时间，用这个结果集当做中间表，再根据api_id连接查询api_base表数据）

ON b.id=al.api_id 
GROUP BY b.id
ORDER BY execute_avg_time desc

 

 

第二个案例：

 

一、以下sql，查询时间太长需要优化：

SELECT
	ab.NAME,#接口名称
	al.api_id api_id,#接口id
	al.project_id projectId,#项目id
	count( al.api_id ) executeSum #接口总执行次数
FROM
	api_agent_log al
	LEFT JOIN api_base ab ON al.api_id = ab.id 
GROUP BY
	al.api_id
ORDER BY
	executeSum DESC

 

后经过发现al.project_id列使用到了，但没有group by。所以该列值是无效的。去掉该值，或是加入group

by。

二、经优化（去掉al.project_id列）时间大大缩短

SELECT
	ab.NAME,#接口名称
	al.api_id api_id,#接口id
	count( al.api_id ) executeSum #接口总执行次数
FROM
	api_agent_log al
	LEFT JOIN api_base ab ON al.api_id = ab.id 
GROUP BY
	al.api_id
ORDER BY
	executeSum DESC