mysql关联查询怎样优化
发布时间：2022-01-12 10:47:08 所属栏目：编程 来源：互联网
导读：小编给大家分享一下mysql关联查询如何优化，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ mysql中任何关联查询都是nest loop（嵌套循环）操作，nest loop是在驱动表中取出
|
小编给大家分享一下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> explain select a.user_id,b.loan_info_id from bill b INNER 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 | a | index | PRIMARY | PRIMARY | 194 | NULL | 35970 | Using index | | 1 | SIMPLE | b | ref | in_bill_user_id | in_bill_user_id | 194 | CDM.a.user_id | 3 | NULL | +----+-------------+-------+-------+------------------------------+------------------------------+---------+---------------+-------+-------------+ 2 rows in set (0.00 sec) 内连接，mysql的优化器会根据统计信息自动选择小表user_tmp做驱动表，大家可以看到rows列值和我们刚开始统计的行数不一致，是因为统计信息和实际是有差异，所以有时候统计信息的不准确会导致执行计划不是最优的。内连接可以用STRAIGHT_JOIN按照顺序执行，即指定左表为驱动表 mysql> explain 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; +----+-------------+-------+--------+------------------------------+---------+---------+---------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+------------------------------+---------+---------+---------------+---------+-------------+ | 1 | SIMPLE | b | ALL | in_bill_user_id | NULL | NULL | NULL | 1912096 | NULL | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 194 | CDM.b.user_id | 1 | Using index | +----+-------------+-------+--------+------------------------------+---------+---------+---------------+---------+-------------+ 这个时候mysql就不会根据统计信息把右边的小表当做驱动表 删除被驱动表bill索引 mysql> explain select a.user_id,b.loan_info_id from bill b INNER 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 | 1905575 | NULL | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 194 | CDM.b.user_id | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+---------------+---------+-------------+ mysql优化器是基于成本的，bill没有了索引，那么就要扫描35970次bill全表 ，成本高于扫描1905575次user_tmp索引，所以又改变了执行计划，变成了把大表做驱动表，进而降低了查询效率 二、执行效率（关联键都有索引）： 当小表是驱动表的时候 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关联查询如何优化”这篇文章的所有内容，感谢各位的阅读！ （编辑：厦门网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
