MYSQL多表联合查询
在一个大型的复杂应用中,我们通常会将不同模块的数据存储到各自的表中 例如在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
在最后一步优化时,我们看是将最靠左的一个有效筛选表替换为主表,同时关联的表名也进行调整。 在效率要求不高的情况下,我个人还是建议采用2.1的方式。
相关文章
- mysql longtext 查询_mysql中longtext存在大量数据时,会导致查询很慢?
- MySQL查询表行数:精确掌握表记录数(mysql查询表行数)
- 点精准计算:MySQL舍去小数点的方法(mysql去小数)
- MySQL数据库:存储图像的新方式(mysql数据库保存图片)
- 查询MySQL中三表联合查询实战(mysql三表联合)
- MySQL中的高级子查询技巧(mysql高级子查询)
- MySQL查询:查看数据表的总数(mysql查询表总数)
- MySQL连接SQL Server:实现完美同步(mysql连接sqlserver)
- Mac安装MySQL的步骤教程(mac怎么下载mysql)
- MySQL掌握表结构:基础查询语句(mysql查看表结构)
- 安装异常MySQL依赖包安装出现异常(mysql依赖包)
- 优化MySQL查询优化:提高命中率(mysql查询命中率)
- MySQL中如何正确删除联合主键(mysql删除联合主键)
- MySQL无法保存中文数据解决方法(mysql无法保存中文)
- MySQL时间转换为秒: 手把手教你!(mysql时间转换成秒)
- MySQL 中的基于 if 条件的变量管理(mysql变量if)
- MySQL 数据库切片:提高数据查询效率(mysql数据库切片)
- 极速搞定:MySQL连接数据库的最佳实践(mysql连数据库)
- MySQL中文UTF8编码详解:解析25岁以下青年趋势(mysql中文utf8)
- MySQL中使用联合主键的实践经验(mysql的联合主键)
- MySQL 中文查询:使用中文关键字进行 SQL 数据库查询的方法(mysql中文查询)
- MySQL全面解析:从基础到高级,带你深入学习MySQL数据库技术(mysql大全)
- MySQL之更新数据语句实践指引(mysql 更新数据语句)
- 数据MySQL查询整型数据的有效方法(mysql 查询int)
- MySQL中主键的作用和定义(mysql中什么是主码)
- MySQL中使用IN函数的查询限制是什么(mysql中in的限制)
- MySQL中CTES的使用方式研究(ctes mysql)
- MySQL中的AND和OR使用逻辑运算符优化查询语句(mysql中and与or)
- MySQL联合查询实现表格分组筛选(mysql两表联查后分组)
- 教你如何使用CMD远程登录MySQL(cmd远程登录mysql)
- MySQL如何实现一行表多行数据存储(mysql一行表多行)
- MySQL实现一次查询千万级数据,轻松应对海量数据处理(mysql一次千万级)
- MySQLcomh文件解析,详细了解MySQL C API(mysql_com.h)
- MySQL多表查询语句,轻松查询不同数据表的信息(mysql不同表查询语句)
- 如何在MySQL中修改用户密码(mysql下修改用户密码)