MYSQL多表联合查询

雪碧君

发布于 2023-02-15 06:50:13

3.1K00

代码可运行

文章被收录于专栏：雪碧君终将成长雪碧君终将成长

运行总次数：0

代码可运行

在一个大型的复杂应用中，我们通常会将不同模块的数据存储到各自的表中例如在APPsite框架中我们默认了4张用户表分别存储了 user_account 账户表 user_info 详情表 user_pocket 钱包表 user_group 分组表

这样我们在读写数据的时候可以做到表级别的隔离，防止一些api 或是内外部方法导致的数据泄露问题，提高安全性和事务方法的紧密度。当然也有一定的减轻单张表结构过于臃肿的作用。

这里的表拆分，要基于业务划分去做，譬如说详情、分组、钱包在用户的鉴权、登录包括收发消息等行为时都不需要，那么我们就可以将这些部分的数据转移到新的表中。保持account表的高效性。

于此对应的是我们在进行后台的丰富数据查询时就需要合并表进行查询，今天特意整理一下使用JOIN进行多表联合查询的注意点。

首先是最简单的范例

# JOIN查询 双表
SELECT * FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid

在多表查询时，我们会遇到某个表对应项目为空时的情况, 这时根据JOIN方式就会有不同的结果。其中INNER 方式就会取交集合并结果，而LEFT方式左表会完整展示，右表不满足条件的数据会被剔除为空。

看三个对比:

# JOIN 多表条件查询
# 靠后的表会是左右的反方向追加进来

# case1
SELECT * FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid 
LEFT JOIN user_group ON user_account.groupid = user_group.groupid AND user_group.level > 100
# 取出了所有用户数据、 其中用户level不大于100的 用户组信息被抹掉(NULL)

# case2
SELECT * FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid 
INNER JOIN user_pocket ON user_account.userid = user_pocket.userid AND user_pocket.point > 5000
LEFT JOIN user_group ON user_account.groupid = user_group.groupid
# 仅取出所有积分大于5000的用户数据

# case3
SELECT * FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid 
LEFT JOIN user_pocket ON user_account.userid = user_pocket.userid AND user_pocket.point > 5000
INNER JOIN user_group ON user_account.groupid = user_group.groupid AND  user_group.level > 100 
# 取出了所有用户level大于100的用户数据，且其中积分不大于5000的用户钱包信息被抹掉(NULL)

在这个部分我们可以通过INNER有效的控制最终的结果数量，譬如说在进行筛选查询时。而我们要进行范围查询，其中可能包含空值的时候，就应该用LEFT,RIGHT 而左右决定了哪一边是全表，另外一边来补齐的策略。

另外一个比较重要的是，WHERE条件和 JOIN表内条件的作用范围，同样上一个范例:

# JOIN 表内AND条件 与 WHERE条件的作用范围

# case1
SELECT * FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid 
LEFT JOIN user_group ON user_account.groupid = user_group.groupid
WHERE user_group.level > 100
# 仅取出了用户level不大于100的

# case2
SELECT * FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid 
LEFT JOIN user_pocket ON user_account.userid = user_pocket.userid 
LEFT JOIN user_group ON user_account.groupid = user_group.groupid AND user_group.level > 100
WHERE user_pocket.point > 5000
# 仅取出所有积分大于5000的用户数据,同时 用户级别不大于100的用户组信息被抹掉(NULL)

这里可以归纳一个简单的策略：

JOIN语句手拉手一键查询数据有屏蔽数据表内AND 过滤筛选WHERE最后

虽然我们可以用INNER+表内条件的方式来进行筛选，但是这里推荐的是如果要筛选就全部写在WHERE语句中，这样在查询的时候MYSQL会优化查询减少整体的运算量。

在使用JOIN查询的时候我们还会有统计行数的需求，为了减少MYSQL服务器的计算量，其实这里我们也可以做一些优化。

# JOIN 表COUNT查询效率优化

# case1
SELECT COUNT(*) FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid 
LEFT JOIN user_pocket ON user_account.userid = user_pocket.userid 
LEFT JOIN user_group ON user_account.groupid = user_group.groupid AND user_group.level > 100
WHERE user_pocket.point > 5000

在上面的case中，我们可以分析出 user_info 因为没有筛选条件、所以一定不会影响最终结果，所以这一行可以省略。 user_group因为只是一个表内筛选，也不会影响最终行数所以也可以省略。接下来是user_pocket，这一条件决定了最终结果的呈现，所以不能省略。那么可以优化成:

# case1.1
SELECT COUNT(*) FROM user_account 
LEFT JOIN user_pocket ON user_account.userid = user_pocket.userid 
WHERE user_pocket.point > 5000

这里我们会发现，如果在user_account表中也没有筛选条件的话，那其实我们仅仅需要统计user_pocket表内的行数即可( 这里存在疑问是 user_account.userid 是否可以和 user_info.userid能做到一一对应 )。我们可以继续优化成一句单表查询

# case1.2
SELECT COUNT(*) FROM user_pocket WHERE user_pocket.point > 5000

在优化COUNT的时候，另一种情况就是一张或多张表都存在有效筛选的情况。这个时候我们不可避免的还是要使用联合查询。可以优化的就是将不参与筛选的表从中移除，这样以便于优化查询效率。如:

# JOIN 表COUNT查询效率优化 多个有效筛选字段

# case 2
SELECT COUNT(*) FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid 
LEFT JOIN user_pocket ON user_account.userid = user_pocket.userid 
LEFT JOIN user_group ON user_account.groupid = user_group.groupid AND user_group.level > 100
WHERE user_info.wechatid IS NOT NULL AND user_pocket.point > 5000

# 由于 user_info , user_pocket 都有有效筛选条件 所以我们可以优化为

# case 2.1
SELECT COUNT(*) FROM user_account 
LEFT JOIN user_info ON user_account.userid = user_info.userid 
LEFT JOIN user_pocket ON user_account.userid = user_pocket.userid 
WHERE user_info.wechatid IS NOT NULL AND user_pocket.point > 5000

# 如果考虑到我们已经做到完美对应 可以将 主表进一步优化掉

# case 2.2
SELECT COUNT(*) FROM user_info
LEFT JOIN user_pocket ON user_info.userid = user_pocket.userid 
WHERE user_info.wechatid IS NOT NULL AND user_pocket.point > 5000