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 

查看执行计划

在 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

select * from  user_profiles where user_profiles.age  = 1

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

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

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

如果给这条 sql 加一个条件

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

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

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

如果 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 中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢哈。

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

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

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

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

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

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

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