MySQL性能调优之 IN

转载

mb6063e31623ab7 2021-04-02 13:35:15

文章标签 MySQL 文章分类 MySQL 数据库

今天遇到的一个问题？同样的SQL在Oracle只需要不到1秒在MySQL中需要10秒，极大的影响到了效率！

首先在Oracle数据库中执行！

SELECT 
	b.NAME AS TAGNAME, 
	til.LOW, 
	til.UP, 
	b.UNIT, 
	a.CLOCK, 
	a.VAL  
FROM 
	tag_value a 
	INNER JOIN tag_Index b ON a.id = b.id 
	LEFT JOIN tag_index_limit til ON b.id = til.id  
WHERE 
	( a.id, a.clock ) IN ( 
	SELECT 
		tv.id, 
		max( tv.clock )  
	FROM 
		tag_value tv 
		LEFT JOIN tag_Index ti ON tv.id = ti.id  
	WHERE 
		ti.NAME IN ( 
			'ST1_L1R_SIN_1To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_3To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_4To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_5To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_6To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_7To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_8To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_9To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_10To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_11To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_12To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_13To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_14To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_15To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_16To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_17To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_18To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_19To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_1To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To3SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To4SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_3To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_4To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_5To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_6To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_7To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_8To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_9To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_10To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_11To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_12To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_13To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_14To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_15To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_16To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_17To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_18To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_19To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_20To1SinAbelTDea_1m_cur'  
		)  
GROUP BY 
	tv.id)

在MySQL中执行！

在SQL语句前面加EXPLAIN分析SQL

MySQL性能调优之 IN_MySQL_03

根据type可以看出b与til是最差的连接类型，key也表示实际上也没有走索引，rows也显示查询了7000多行。

那先加tag_index_limit 表的索引

MySQL性能调优之 IN_MySQL_04

在加 tag_Index 表的的索引

执行SQL结果没什么变化还是9秒多

分析SQL

MySQL性能调优之 IN_MySQL_07

可以发现b与til是走了索引（我也不知道为什么一点变化也没有，以为会快一点点的！）但是b中查询的行数还是7000多

那么只能从修改SQL了仔细观察发现where后面in有蹊跷（其实拆分了几个小时的SQL语句）

修改后代码是：

SELECT 
	b.NAME AS TAGNAME, 
	til.LOW, 
	til.UP, 
	b.UNIT, 
	a.CLOCK, 
	a.VAL  
FROM 
	tag_value a 
	INNER JOIN tag_Index b ON a.id = b.id 
	LEFT JOIN tag_index_limit til ON b.id = til.id  
WHERE 
	( a.id ) IN ( 
	SELECT 
		tv.id
	FROM 
		tag_value tv 
		LEFT JOIN tag_Index ti ON tv.id = ti.id  
	WHERE 
		ti.NAME IN ( 
			'ST1_L1R_SIN_1To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_3To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_4To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_5To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_6To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_7To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_8To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_9To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_10To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_11To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_12To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_13To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_14To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_15To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_16To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_17To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_18To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_19To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_1To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To3SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To4SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_3To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_4To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_5To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_6To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_7To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_8To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_9To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_10To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_11To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_12To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_13To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_14To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_15To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_16To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_17To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_18To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_19To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_20To1SinAbelTDea_1m_cur'  
		)  
GROUP BY 
	tv.id)
	and 
	 
	 	( a.clock ) IN ( 
	SELECT 
		
		max( tv.clock )  
	FROM 
		tag_value tv 
		LEFT JOIN tag_Index ti ON tv.id = ti.id  
	WHERE 
		ti.NAME IN ( 
			'ST1_L1R_SIN_1To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_3To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_4To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_5To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_6To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_7To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_8To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_9To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_10To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_11To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_12To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_13To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_14To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_15To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_16To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_17To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_18To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_19To1SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_1To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To3SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_2To4SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_3To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_4To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_5To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_6To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_7To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_8To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_9To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_10To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_11To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_12To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_13To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_14To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_15To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_16To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_17To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_18To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_19To2SinAbelTDea_1m_cur', 
			'ST1_L1R_SIN_20To1SinAbelTDea_1m_cur'  
		)  
)