连表查询和子查询在实际中的使用问题和分析

朴灿烈づ我的快乐病毒、 2021-09-18 11:38 330阅读 0赞

## 前言： ##

1.对于mysql，不推荐使用子查询和join是因为本身join的效率就是硬伤，一旦数据量很大效率就很难保证，强烈推荐分别根据索引单表取数据，然后在程序里面做join，merge数据。

2.子查询就更别用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

3.如果是JOIN的话，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理。

4.数据库是最底层的，瓶颈往往是数据库。建议数据库只是作为数据store的工具，而不要添加业务上去。

## 一、放弃连表查询，在应用层关联的优势： ##

**1.让缓存的效率更高**。许多应用程序可以方便地缓存单表查询对应的结果对象。如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。  
将查询分解后，执行单个查询可以减少锁的竞争。  
**2.在应用层做关联**，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。  
**3..查询本身效率也可能会有所提升**。查询id集的时候，使用IN（）代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。  
**4.可以减少冗余记录的查询**。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消艳。  
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

##    二、放弃连表查询，在应用层关联的使用场景： ##

1.当应用能够方便地缓存单个查询的结果的时候  
2.当可以将数据分布到不同的MySQL服务器上的时候  
3.当能够使用IN（）的方式代替关联查询的时候  
4.并发场景多，DB查询频繁，需要分库分表

##    三、不推荐使用join进行连表查询的原因：  ##

1.DB承担的业务压力大，能减少负担就减少。当表处于百万级别后，join导致性能下降；

2.分布式的分库分表。这种时候是不建议跨库join的。目前mysql的分布式中间件，跨库join表现不良。

3.修改表的schema，单表查询的修改比较容易，join写的sql语句要修改，不容易发现，成本比较大，当系统比较大时，不好维护。

## 四、不使用join的解决方法：  ##

在业务层，单表查询出数据后，作为条件给下一个单表查询。也就是子查询。 会担心子查询出来的结果集太多。mysql对in的数量没有限制，但是mysql限制整条sql语句的大小。通过调整参数max\_allowed\_packet ，可以修改一条sql的最大值。建议在业务上做好处理，限制一次查询出来的结果集是能接受的。

## 五、再来说说join查询的好处： ##

1.做分页查询：

关联查询的好处时候可以做分页，可以用副表的字段做查询条件，在查询的时候，将副表匹配到的字段作为结果集，用主表去in它，但是问题来了，如果匹配到的数据量太大就不行了，也会导致返回的分页记录跟实际的不一样，解决的方法可以交给前端，一次性查询，让前端分批显示就可以了，这种解决方案的前提是数据量不太，因为sql本身长度有限。

## 总结： ##

1.数据库是作为数据存储的地方,能用程序解决的问题,不应由数据库去完成，比如对数据的加工（使用一些函数）会导致索引失效，对数据的加工处理在业务层完成  
  
2.在数据量极大的项目里，连表查询会消耗大量数据库资源,想比之下,程序运算消耗的是服务器资源,而现在应用的瓶颈还在数据库上,服务器可以通过增量分压来扩容,数据库虽然也可以,但是多个之间的数据同步是个很难处理的问题,由此促进了缓存技术的发展,否则也不会出现缓存为王这种认知了

3.在数据量不大的项目里，使用连表查询要比子查询嵌套效率要高一些，连表查询的关键点是索引的运用，索引的合理性可以大大增强连表的效率，但是相对的数据过多也是对重组索引的一种负担，应尽量选择整型变量来加快索引的重组

4.连表查询给前台发送数据时用分页或者懒加载的方式也能减小数据库负担，  
  
5.虽然php程序相比其他程序运算慢点,但也不是很离谱,相比两个大表联合查询的等待时间,分两次查询再由php组合的速度可能会更快.  
  
6.就实际工作中,很多大公司都严禁使用连表查询,我相信这么做是有依据,而且连表查询可以通过优化转化子查询的方式去避免.