join 优化的基础原则有哪些
发布时间：2021-12-23 11:59:29 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家分享的是有关join 优化的基本原则有哪些的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 1、join 优化的基本原则： a：小结果集驱动大结果集 b: 确保被驱动的表被索引 c: 不能确保驱动表被索引加大 join_buffer_s
|
这篇文章给大家分享的是有关join 优化的基本原则有哪些的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 1、join 优化的基本原则： a：小结果集驱动大结果集 b: 确保被驱动的表被索引 c: 不能确保驱动表被索引加大 join_buffer_size 的大小。 原理： mysql 的 join 算法只有一种 Nested Loop Join 算法。其最基本原理是 循环取驱动表中的每一条记录， 到匹配表中过滤，得到结果集list，再次循环list每条记录到下个匹配表中过滤，以此类推。 伪代码【2表关联】： for each recode in table_a { for each recode in table_b that table_a.column=table_b.column { combination to output; } } 解析：Nested Loop Join 嵌套循环的代价取决于，内外循环代价的乘积。即 【驱动表行数】N*M【到匹配表中查找一次代价】 innodb B+ 树索引的高度一般是3 至 4，也就是说一般情况下不管是哪个表作为匹配表，其一次查询代价是常量 T 即Join代价: N【表行数】*T【常量】所以 要用小结果集作为驱动表，另外强调一点是小结果集而不是小表，因为小 、大 是相对的，完全有可能大表通过过滤的结果 集比小表还要小的 多。所以强调小结果集。 案例：1.2 亿大表关联 ，优化前执行3个小时没有结果。。。。。。 阿拉好想唱 “等你 爱我 爱我哪怕只有一次也就足够........” select c.current_name,count(*) from ( select distinct PHONE from cis_data_qixin_score )a join TMP_A1_INFO_MOBILE_H_20151201 b on substr(a.PHONE,1,7)=b.mobile_h_code join TMP_A1_DICT_AREA_20151201 c on c.tele_code=b.prov_telecode group by c.current_name ; 说明：sql 功能是获取 每个省的 电话号码数量。cis_data_qixin_score：号码表，TMP_A1_INFO_MOBILE_H_20151201 号码H码表，TMP_A1_DICT_AREA_20151201 号码H码对应省份表 执行计划： +----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+ | 1 | PRIMARY | c | ALL | NULL | NULL | NULL | NULL | 41 | Using temporary; Using filesort | | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 124364159 | Using join buffer (Block Nested Loop) | | 1 | PRIMARY | b | ref | idx_mobile_h_code | idx_mobile_h_code | 33 | func | 1 | Using index condition; Using where | | 2 | DERIVED | cis_data_qixin_score | index | PRIMARY,idx_phone | idx_phone | 62 | NULL | 124364159 | Using index | +----+-------------+----------------------+-------+-------------------+-------------------+---------+------+-----------+---------------------------------------+ 改写后sql: select c.current_name,sum(a.cou) from ( select substr(a.PHONE,1,7) PHONE_h_code ,count(*) cou from (select distinct PHONE from cis_data_qixin_score ) a group by substr(a.PHONE,1,7) order by null )a join TMP_A1_INFO_MOBILE_H_20151201 b on a.PHONE_h_code=b.mobile_h_code join TMP_A1_DICT_AREA_20151201 c on c.tele_code=b.prov_telecode group by c.current_name ; 执行计划： +----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+ | 1 | PRIMARY | c | ALL | NULL | NULL | NULL | NULL | 41 | Using temporary; Using filesort | | 1 | PRIMARY | b | ALL | idx_mobile_h_code | NULL | NULL | NULL | 318794 | Using where; Using join buffer (Block Nested Loop) | | 1 | PRIMARY | | ref | | | 23 | cis_gather.b.mobile_h_code | 390 | Using where | | 2 | DERIVED | | ALL | NULL | NULL | NULL | NULL | 124364170 | Using temporary | | 3 | DERIVED | cis_data_qixin_score | index | PRIMARY,idx_phone | idx_phone | 62 | NULL | 124364170 | Using index | +----+-------------+----------------------+-------+-------------------+-------------+---------+----------------------------+-----------+----------------------------------------------------+ 5 rows in set (0.00 sec) 解析：通过 对号码前7位分组得到物化表【大概35w】自动创建索引 PHONE_h_code 作为匹配表 。join 代价为350000T 改写前后join 代价之比为： 124364159T /350000T = 355 哈哈 是不是有种飞起来的赶脚。 结果： 优化后的sql 4 分钟 搞定。 感谢各位的阅读！关于“join 优化的基本原则有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！ （编辑：52站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
