如何提高多表查询效率

最新推荐文章于 2025-10-31 21:07:37 发布

原创最新推荐文章于 2025-10-31 21:07:37 发布 · 7.8k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#SQL优化 #视图关联查询

SQL 专栏收录该内容

2 篇文章

订阅专栏

本文记录了一个系统因多表查询导致的504网关超时问题及解决过程。通过优化SQL语句、分析explain结果，发现视图关联查询影响效率。解决方案包括避免使用视图，直接引用原始表并添加索引，以及在查询中明确视图创建条件，显著提高了查询速度。

部署运行你感兴趣的模型镜像

记录背景

客户反映系统的用户统计模块报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表，以及创建视图的两张表，三张表在做关联查询，所有效率会慢很多。