mysql for循环_MySQL 细致多表连接

Mysql多表连接

Mysql连接查询优化

在Mysql中多表连接分为驱动表和被驱动表,概念先举出来。驱动表加载一次,被驱动表需要加载多次。

select * from users inner join user_profiles on users.id = user_profiles.user_id

这里假设users是驱动表，users_profiles是被驱动表。假设users表中查询出了id 1~10条数据，那么user_profiles表就需要加载10次。

嵌套循环连接(Nested-Loop Join)

如上说过,驱动表加载记录有多少条,那么被驱动表就需要加载到内存多少次(如果被驱动表记录很大怎么办？)。

在内连接中驱动表和被驱动表是可以互换，它们产生的笛卡尔积都是一样的只是通过on where等等条件过滤掉了

最终产生的结果都是一样的。但是对于左外连接和右外连接的话(这个不用说吧)以驱动表为基础就算不满足on条件，where条件也会将记录查询出来

select users.age,user_profiles.age from users LEFT join user_profiles on users.age = user_profiles.age 

select users.age,user_profiles.age from users Right join user_profiles on users.age = user_profiles.age 

查看执行计划

image-20200926155711943

在explain中 Block Nested Loop 循环嵌套 当驱动表不能有效的可用索引加快访问速度的时候Mysql就会分配其一块名为 join buffer的内存块来加快查询速度(之后深入学习一下explain 目前还是皮毛)。

例子很粗糙。

回到主题如上，伪代码可以想象就是2个for循环嵌套 可以想象连接查询如果驱动表查出来的数据很多。被驱动表就得加载很多次效率非常差。

如果有3个表进行连接的话，那么前两个表中得到的结果集就是新的驱动表，然后第三个表就成为了被驱动表，重复 (3个for循环)

使用索引加快连接速度

嵌套循环连接 需要访问被驱动表多次,如果访问被驱动表都是全表扫描那么效率可以说是非常低了，那么可以使用索引来添加查询速度

举个例子 如上sql转换一下可以变成如下

如上所述驱动表加载一次到内存,被驱动表需要加载驱动表记录数的次数，如果驱动表查出了10条数据 那么被驱动表就需要加载10次。

原sql

select * from users left join user_profiles on users.age = user_profiles.age

当驱动表查询出 users.gold的value为 1的时候,那么sql其实就是这样的

select * from  user_profiles where user_profiles.age  = 1

如果user_profiles.golds这个字段有索引的话 那么就走的是索引扫描,对比一下explain

image-20200926160723172

关于扫描是Using Index这个可以下一次写explain的时候再说 简述说 就是走的是扫描整个索引树

如果给这条sql加一个条件

select * from users LEFT join user_profiles on users.age = user_profiles.age where user_profiles.answers_count = 1

如果age为1 那么简化后的sql其实就是

select * from user_profiles where age  = 1  and answers_count = 1

扩展一个小知识,当有多个where条件的时候查询器会计算执行成本选择最优的执行方案 如果选择了age那么会在回表的时候去聚簇索引中通过answers_count去筛选复合条件的语句(抛出一个问题:有谁知道什么是索引合并)

基于块的嵌套循环连接(Block Nested-Loop Join)

扫描一个表的过程其实就是先把这个表从磁盘加载到内存中,然后从内存中比较匹配条件是否满足。但是如果表中的记录很大的时候。内存里肯定是没办法完全存放的，所以在扫描表前边记录的时候后边的记录还在磁盘上，等扫描到后边记录的时候可能会内存不足，所以需要把前边的记录从内存中释放掉。采用嵌套循环连接 算法的两表连接过程中，被驱动表可是要被访问很多次。如果这个被驱动表中的数据要特别多而且又而且又不能使用索引进行访问，那么相当于要从磁盘上读好几次这个表，这个IO代价就很大了，所以想想办法:尽量减少访问被驱动表的次数

小贴士:创建了索引的字段去查询IO代价很小

当被驱动表中的数据非常多的时候，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿另外一条记录，再一次把被驱动表的记录加载到内存中一遍，重复。驱动表结果集中有 多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以可以在把被驱动表的记录加载到内存的手，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载到内存的代价。

join buffer的概念就是执行链接查询前申请的一块固定大小的内存，先把若干驱动表结果集中的记录装在这个buffer中。然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中多条驱动表记录做匹配。因为匹配过程都是在内存中完成的，所以可以显著减少被驱动表的IO代价。

最好的情况就是join buffer足够大。能容纳驱动表结果集的所有记录。这样只需要访问一次被驱动表就可以完成链接操作。

调整join buffer的大小是可以通过启动参数或者系统变量 join_biffer_size 进行配置，默认大小是262144字节 就是 256kb。最小可以设置为128字节。当然优化被驱动表最好就是使用索引。不然就是把这个参数调大

另外需要注意的是，驱动表的记录并不是所有列都会被放到 join buffer 中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢哈。