多表连接查询如何优化?

在读完<<MySQL是怎样运行的>>这本书以后我就迫切的希望能发表一篇关于多表连接查询优化的文章,今天正好在这里可以大谈阔斧的将我所学的知识无私奉献给大家。

      多表连接查询是我们经常在工作中遇到的问题,解决这个问题,我想先从底层的MySQL数据库对于多表连接查询的处理说起,首先对于这种简单的查询:
SELECT * FROM  s1, s2 WHERE s1.work_order_no = s2.work_order_no

两表查询通过work_order_no消除笛卡尔积,MySQL会根据根节点定位到第一条记录,然后从B+树的叶子节点开始从左到右开始全表扫描,当扫描到一条记录就去s2表看该值是否存在于s2表,如果不存在就丢弃,继续下一条数据。判断是否存在于s2表的方法是看该列是否在s2表中能用到索引,如果不能那就全表扫描,也就是s1表(这里称之为驱动表)扫描一次,s2表(这里称之为被驱动表)扫描n次,那么两表查询查询的成本就是扫描s1表一次的时间✖️s2表扫描的时间,如果s2表不能通过索引来获得合理的扫描区间,那就需要用到全表扫描,我们这里做一个假设,假如驱动表(s1)表的扇出值(也就是查出来符合条件的值)是1000,而被驱动表(s2表)的全表扫描的时间是100毫秒,那么多表连接查询的时间成本就是1000✖️0.1=100秒(理论上是,当然后面还会讲到连接缓冲区进行优化),我们通常听到别人讲到的小表驱动大表的方法就是因为大表查询可以用索引进行优化(如果用到了索引理想状态下可以最少通过三次查询就能找到该值,时间消耗极低),降低每次查询被驱动表的时间,而扇出值一般是无法进行更改的,所以可以降低总的查询时长。

下面我给大家讲一下什么是连接缓冲区 join buffer,这是一个系统变量,我们可以手动设置其大小,我们知道一次磁盘IO的速度是非常慢的,如果在内存中进行是非常快的,MySQL设计大叔正是利用这个特性, 在多表连接查询时,会提前将需要用到的页面加载到内存中,当然如果join buff的大小可以存放所有需要连接查询的数据会全部存放进来,不可以的话也会一部分在磁盘中存放,所以当我们的系统内存很大的时候可以适当提高该系统变量可以提升查询的效率。

     讲了这么多还没有将如何优化,优化的思路肯定是建索引,提高join buffer连接缓冲区的大小,当然我们也可以通过手动编程的方式实现,就是将我们需要查询的两张表数据都查询出来将其中一张表(数据多的) 转成Map,因为大家知道Map底层是通过hash找值得,也就相当于建立了hash索引,然后我们就可以提升查询的效率。

我写了一份伪代码,可以供大家进行参考!