mysql关联查询如何优化

120次阅读

共计 4323 个字符，预计需要花费 11 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 mysql 关联查询如何优化，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

mysql 中任何关联查询都是 nest loop（嵌套循环）操作，nest loop 是在驱动表中取出一条数据，然后从被驱动表中逐行比较，把符合规则的放入结果集中，然后再取下一行，依次循环，驱动表每返回一行，被驱动表就要扫描一次。
针对 nest loop 关联机制需要从下面几个方面着手优化：
1、减少 nest loop 循环次数，使用小结果集做驱动表，驱动大结果集。
2、被驱动表每次循环都要被扫描，所以要求关联键上一定要有索引，而且选择性要好。
3、如果第二条无法满足，可以通过调 join_buffer_size 来设置 join buffer 的大小，不过还是建议添加索引而不是纯粹的加大 join_buffer_size

接下来通过下面的实验来了解 mysql 的 nest loop
实验环境：Percona server5.6.27 大表 bill、小表 user，表上均有索引
mysql select count(*) from bill;
+———-+
| count(*) |
+———-+
| 1966789 |
+———-+

mysql select count(*) from user_tmp;
+———-+
| count(*) |
+———-+
| 36317 |
+———-+
一、执行计划：
mysql explain select a.user_id,b.loan_info_id from bill b left JOIN user_tmp a on a.user_id=b.user_id;
+—-+————-+——-+——–+—————+———+———+—————+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———+———+—————+———+————-+
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1912096 | NULL |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 194 | CDM.b.user_id | 1 | Using index |
+—-+————-+——-+——–+—————+———+———+—————+———+————-+

左连接左表不管有多大总是驱动表，右表总是被驱动表

内连接，mysql 的优化器会根据统计信息自动选择小表 user_tmp 做驱动表，大家可以看到 rows 列值和我们刚开始统计的行数不一致，是因为统计信息和实际是有差异，所以有时候统计信息的不准确会导致执行计划不是最优的。内连接可以用 STRAIGHT_JOIN 按照顺序执行，即指定左表为驱动表

二、执行效率（关联键都有索引）：
当小表是驱动表的时候
mysql select a.user_id,b.loan_info_id from bill b INNER JOIN user_tmp a on a.user_id=b.user_id;
这里结果集有几万条，省略
耗时：0.202s
使用 STRAIGHT_JOIN 强制大表是驱动表的时候
mysql select STRAIGHT_JOIN a.user_id,b.loan_info_id from bill b INNER JOIN user_tmp a on a.user_id=b.user_id
耗时：5.260s
由于两张表的相差几十倍，两种执行计划的效率也是显而易见的
注：如果大表的关联键索引选择性比较差（如重复数据多等），每次循环扫太多了，不如让大表做驱动表，上述实验是在大表的索引选择性好的情况下得出的结果

以上是“mysql 关联查询如何优化”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！

正文完