如何提高多表查询效率

记录背景

客户反映系统的用户统计模块报504网关超时错误，在用本地服务器运行项目，发现确实如此。
原因在于每个项目查询需要2-3秒，几十项目的查询时间就超过一分钟，而nginx设置的超时时间为60秒，所以出现了客户提到的现象。解决方法有两个，一是提高nginx的超时时间，二是优化sql的查询效率，于是选择了后者。

优化的大致思路

加索引，优化sql语句

当你发现做了上述两件事后并没有改善

可以通过explain来辅助找问题
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了

效率低的sql语句

SELECT
	*
FROM
	wy_member a , wy_room_view b 
WHERE
	(
		a.room_id IS NOT NULL
		AND a.room_id != ''
	)
AND a.appid = 'wx64*********'
AND a.`status` = 1
AND a.member_type = '1'
AND b.WYID = '43'
and a.room_id = b.roomId

查询用时2.479s

explain 结果

table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL，type显示的是访问类型，是较为重要的一个指标
一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

分析结果

从图中可以看出，连接的类型最差，索引没有用到，最大的问题是出现了三行结果，但我查询的只是两张表，为何出现三行结果。仔细看两张表，发现B表原来是一张视图，该视图是由两张表组合而成的，所以explain结果为3行。

如何解决1

根据实际业务，其实B表关联查询的字段无需用到视图，直接用视图中的一张表就能满足，于是取消视图，j将wy_room_view视图换为实际的表wy_room

SELECT
	*
FROM
	wy_member a , wy_room b 
WHERE
	(
		a.room_id IS NOT NULL
		AND a.room_id != ''
	)
AND a.appid = 'wx64ae084d5286fd23'
AND a.`status` = 1
AND a.member_type = '1'
AND b.WYID = '43'
and a.room_id = b.roomId

查询用时 0.353s
效率提高了将近10倍

explain结果

视图是无法创建索引的，换为表后，可以再根据查询内容添加索引，进一步优化，根据type发现本次查询满足优化条件

如何解决2

如果说我的需求非要用到视图，那么可以先查看创建视图的条件，

在查询的sql语句中将两个字段的条件加上，虽然explain的结果没有发生改变，但是效率确实也提高也将近10倍。我的猜想：如果查询条件中没有满足创建视图的条件，实际的查询是a表，以及创建视图的两张表，三张表在做关联查询，所有效率会慢很多。