mysql关联查询如何优化

小编给大家分享一下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关联查询如何优化”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注云搜网行业资讯频道!


【AD】美国洛杉矶/香港/日本VPS推荐,回程电信CN2 GIA线路,延迟低、稳定性高、免费备份_搬瓦工