{"id":5383,"date":"2020-07-30T00:01:45","date_gmt":"2020-07-29T16:01:45","guid":{"rendered":"http:\/\/39.97.231.10\/?p=5383"},"modified":"2020-07-30T00:01:45","modified_gmt":"2020-07-29T16:01:45","slug":"mysql-8-0-hash-join-2","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=5383","title":{"rendered":"MySQL 8.0 hash join"},"content":{"rendered":"
\nMySQL 8.0.18 \u7248\u672c\u5f15\u5165\u4e86 hash join \u529f\u80fd\uff0c\u6765\u4f18\u5316\u6ca1\u6709\u8d70\u7d22\u5f15\u7684\u7b49\u503c join \u8fde\u63a5\uff0chash join \u901a\u5e38\u6bd4 BNL(Block Nested-Loop) \u7b97\u6cd5\u6548\u7387\u66f4\u9ad8\u3002\u6bd4\u5982\u4e00\u4e2a\u5e38\u89c1\u7684\u7b49\u503c join \u67e5\u8be2\u8bed\u53e5\uff0c\u5982\u4e0b\uff1a\n<\/div>\n
\n
\n
SELECT *\n    FROM t1\n    JOIN t2\n        ON t1.c1=t2.c1;\n<\/pre>\n
\u4e00\u30018.0.20 \u4e0d\u52a0 format=tree \u4e5f\u80fd\u663e\u793a hash join<\/h5>\n
\u5728 8.0.20 \u7248\u672c\u4e4b\u524d\uff0c\u9700\u8981\u5728 explain \u8bed\u53e5\u52a0\u4e0a format=tree \u624d\u80fd\u663e\u793a\u4f7f\u7528\u4e86 hash join\uff0c\u4ece 8.0.20 \u7248\u672c\uff0cexplain \u9ed8\u8ba4\u5c31\u53ef\u4ee5\u663e\u793a\u51fa\u4f7f\u7528 hash join\uff0cexplain analyze \u540c\u6837\u4e5f\u4f1a\u663e\u793a hash join \u76f8\u5173\u4fe1\u606f\uff0c\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n
mysql> EXPLAIN\n    -> SELECT * FROM t1\n    ->     JOIN t2 ON t1.c1=t2.c1\\\\G\n*************************** 1. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: t1\n   partitions: NULL\n         type: ALL\npossible_keys: NULL\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: 1\n     filtered: 100.00\n        Extra: NULL\n*************************** 2. row ***************************\n           id: 1\n  select_type: SIMPLE\n        table: t2\n   partitions: NULL\n         type: ALL\npossible_keys: NULL\n          key: NULL\n      key_len: NULL\n          ref: NULL\n         rows: 1\n     filtered: 100.00\n        Extra: Using where; Using join buffer (hash join)\n<\/pre>\n
\u4e8c\u30018.0.20 hash join \u7b49\u503c\u6761\u4ef6\u7684\u9650\u5236\u88ab\u53bb\u9664<\/h5>\n
8.0.18 \u7248\u672c\u4f7f\u7528 hash join \u4f18\u5316\u7684\u524d\u63d0\u6761\u4ef6\u662f\u4e24\u4e2a\u8868 join \u8fde\u63a5\uff0c\u81f3\u5c11\u6709\u4e00\u4e2a\u7b49\u503c\u6761\u4ef6\uff0c\u5982\u679c\u6ca1\u6709\u7b49\u503c\u6761\u4ef6\uff0c\u5219\u4e0d\u80fd\u4f7f\u7528 hash join\uff0c\u6bd4\u5982\u4e0b\u9762\u8fd9\u4e2a\u793a\u4f8b\uff0cjoin \u8fde\u63a5\u7684\u4e24\u4e2a\u8868\uff0c\u90fd\u6709\u81f3\u5c11\u4e00\u4e2a\u7b49\u503c\u6761\u4ef6\uff1a<\/p>\n
ELECT * FROM t1\n    JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)\n    JOIN t3 ON (t2.c1 = t3.c1);\n<\/pre>\n
\u4ece 8.0.20 \u7248\u672c\u5f00\u59cb\uff0cjoin \u7684\u4e24\u4e2a\u8868\uff0c\u5982\u679c\u6ca1\u6709\u4f7f\u7528\u7b49\u503c\u6761\u4ef6\uff0c\u4e5f\u53ef\u4ee5\u505a hash join\uff0cBNL \u7684\u652f\u6301\u88ab\u79fb\u9664\uff0c\u4e4b\u524d\u4f7f\u7528 BNL \u7684\u5730\u65b9\u5c06\u7531 hash join \u4ee3\u66ff\uff0c\u5982\u4e0b\uff1a<\/p>\n
mysql> EXPLAIN FORMAT=TREE\n    -> SELECT * FROM t1\n    ->     JOIN t2 ON (t1.c1 = t2.c1)\n    ->     JOIN t3 ON (t2.c1 < t3.c1)\\\\G\n*************************** 1. row ***************************\nEXPLAIN: -> Filter: (t1.c1 < t3.c1)  (cost=1.05 rows=1)\n    -> Inner hash join (no condition)  (cost=1.05 rows=1)\n        -> Table scan on t3  (cost=0.35 rows=1)\n        -> Hash\n            -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)\n                -> Table scan on t2  (cost=0.35 rows=1)\n                -> Hash\n                    -> Table scan on t1  (cost=0.35 rows=1)\n<\/pre>\n
\u5728 8.0.20 \u53ca\u4e4b\u540e\u7684\u7248\u672c\uff0chash join \u53bb\u9664\u4e86\u7b49\u503c\u8fde\u63a5\u7684\u9650\u5236\u6761\u4ef6\uff0c\u4f7f\u5f97\u4e0b\u9762\u8fd9\u4e9b\u539f\u6765\u4e0d\u80fd\u4f7f\u7528 hash join \u4f18\u5316\u7684\u7c7b\u578b\uff0c\u5728 8.0.20 \u53ca\u4e4b\u540e\u7684\u7248\u672c\uff0c\u90fd\u53ef\u4ee5\u4f7f\u7528 hash join \u4f18\u5316\u4e86\u3002
\n1. \u7b1b\u5361\u5c14\u8fde\u63a5\u4f7f\u7528 hash join\uff1a
\n\u5bf9\u4e8e\u6ca1\u6709\u6307\u5b9a join \u6761\u4ef6\u7684\u7b1b\u5361\u5c14\u8fde\u63a5\uff0c\u540c\u6837\u53ef\u4ee5\u4f7f\u7528 hash join\uff0c\u5982\u4e0b\uff1a<\/p>\n
mysql> EXPLAIN FORMAT=TREE\n    -> SELECT *\n    ->     FROM t1\n    ->     JOIN t2\n    ->     WHERE t1.c2 > 50\\\\G\n*************************** 1. row ***************************\nEXPLAIN: -> Inner hash join  (cost=0.70 rows=1)\n    -> Table scan on t2  (cost=0.35 rows=1)\n    -> Hash\n        -> Filter: (t1.c2 > 50)  (cost=0.35 rows=1)\n            -> Table scan on t1  (cost=0.35 rows=1)\n<\/pre>\n
2. Inner non-equi-join\uff1a<\/p>\n
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\\\\G\n*************************** 1. row ***************************\nEXPLAIN: -> Filter: (t1.c1 < t2.c1)  (cost=4.70 rows=12)\n    -> Inner hash join (no condition)  (cost=4.70 rows=12)\n        -> Table scan on t2  (cost=0.08 rows=6)\n        -> Hash\n            -> Table scan on t1  (cost=0.85 rows=6)\n<\/pre>\n
3. Semijoin\uff1a<\/p>\n
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1\n    ->     WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\\\\G\n*************************** 1. row ***************************\nEXPLAIN: -> Nested loop inner join\n    -> Filter: (t1.c1 is not null)  (cost=0.85 rows=6)\n        -> Table scan on t1  (cost=0.85 rows=6)\n    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (c2=t1.c1)\n        -> Materialize with deduplication\n            -> Filter: (t2.c2 is not null)  (cost=0.85 rows=6)\n                -> Table scan on t2  (cost=0.85 rows=6)\n<\/pre>\n
4. Antijoin\uff1a<\/p>\n
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2\n    ->     WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.col1 = t2.col1)\\\\G\n*************************** 1. row ***************************\nEXPLAIN: -> Nested loop antijoin\n    -> Table scan on t2  (cost=0.85 rows=6)\n    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (c1=t2.c1)\n        -> Materialize with deduplication\n            -> Filter: (t1.c1 is not null)  (cost=0.85 rows=6)\n                -> Table scan on t1  (cost=0.85 rows=6)\n<\/pre>\n
5. Left outer join\uff1a<\/p>\n
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\\\\G\n*************************** 1. row ***************************\nEXPLAIN: -> Left hash join (t2.c1 = t1.c1)  (cost=3.99 rows=36)\n    -> Table scan on t1  (cost=0.85 rows=6)\n    -> Hash\n        -> Table scan on t2  (cost=0.14 rows=6)\n<\/pre>\n
6. Right outer join\uff08MySQL \u628a\u6240\u6709\u53f3\u8fde\u63a5\u6539\u5199\u4e3a\u5de6\u8fde\u63a5\uff09\uff1a<\/p>\n
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\\\\G\n*************************** 1. row ***************************\nEXPLAIN: -> Left hash join (t1.c1 = t2.c1)  (cost=3.99 rows=36)\n    -> Table scan on t2  (cost=0.85 rows=6)\n    -> Hash\n        -> Table scan on t1  (cost=0.14 rows=6)\n<\/pre>\n
\u4e09\u3001hash join \u53c2\u6570<\/h5>\n
8.0.18 \u7248\u672c\u5728\u53c2\u6570 optimizer_switch \u4e2d\u5f15\u5165\u9009\u9879 hash_join=on\/off\uff0c\u6765\u63a7\u5236\u662f\u5426\u4f7f\u7528 hash join \u4f18\u5316\uff0c\u540c\u6837\u4e5f\u53ef\u4ee5\u5728 SQL \u4e2d\u4f7f\u7528\u6807\u8bb0 HASH_JOIN\uff0cNO_HASH_JOIN \u5728\u4f1a\u8bdd\u7ea7\u8fbe\u5230\u540c\u6837\u7684\u76ee\u7684\u3002\u4f46\u662f\u4ece 8.0.19 \u7248\u672c\u5f00\u59cb\uff0c\u8fd9\u4e9b\u53c2\u6570\u548c\u8bbe\u7f6e\u5c06\u4e0d\u518d\u751f\u6548\uff0c\u4e4b\u524d\u4f7f\u7528 BNL \u7b97\u6cd5\u7684\u5730\u65b9\uff0c\u5168\u90e8\u7531 hash join \u4ee3\u66ff\u3002\u3002
\nhash join \u7684\u5185\u5b58\u4f7f\u7528\u901a\u8fc7\u53c2\u6570 join_buffer_size \u6765\u63a7\u5236\uff0c\u9ed8\u8ba4\u4e3a 256KB\uff0chash join \u4f7f\u7528\u7684\u5185\u5b58\u5927\u5c0f\u4e0d\u80fd\u8d85\u8fc7\u8be5\u53c2\u6570\u8bbe\u7f6e\u7684\u503c\uff0c\u5f53 hash join \u9700\u8981\u7684\u5185\u5b58\u5927\u5c0f\u8d85\u8fc7\u8be5\u503c\u65f6\uff0c\u5c06\u4f1a\u4f7f\u7528\u78c1\u76d8\u6587\u4ef6\u6765\u5b58\u50a8\uff0c\u78c1\u76d8\u6587\u4ef6\u7684\u521b\u5efa\u4f1a\u53d7\u53c2\u6570 open_files_limit \u9650\u5236\uff0c\u56e0\u6b64\u9002\u5f53\u8c03\u5927\u4e0a\u8ff0\u4e24\u4e2a\u53c2\u6570\uff0c\u5c06\u6709\u52a9\u4e8e hash join \u6267\u884c\u66f4\u9ad8\u6548\u3002
\n\u4ece 8.0.18 \u7248\u672c\u5f00\u59cb\uff0chash join \u4f7f\u7528\u7684\u5185\u5b58 buffer \u662f\u9010\u6b65\u589e\u52a0\u7684\uff0c\u56e0\u6b64\u53ef\u4ee5\u5c06 join_buffer_size \u8c03\u5927\uff0c\u5bf9\u4e8e\u5c0f\u7684\u67e5\u8be2\uff0c\u5e76\u4e0d\u4f1a\u4e00\u6b21\u5c31\u628a\u6240\u6709 join buffer \u7533\u8bf7\u5b8c\uff0c\u6240\u4ee5\u4e0d\u7528\u62c5\u5fc3\u5185\u5b58\u5360\u7528\u8fc7\u5927\u7684\u95ee\u9898\u3002\n<\/p><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
MySQL 8.0.18 \u7248\u672c\u5f15\u5165\u4e86 hash join \u529f\u80fd\uff0c\u6765\u4f18\u5316\u6ca1\u6709\u8d70\u7d22\u5f15\u7684\u7b49\u503c join \u8fde\u63a5\uff0chas …<\/p>\n
  MySQL 8.0 hash join<\/span> \u67e5\u770b\u5168\u6587 »<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90,88],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"MySQL 8.0.18 \u7248\u672c\u5f15\u5165\u4e86 hash join \u529f\u80fd\uff0c\u6765\u4f18\u5316\u6ca1\u6709\u8d70\u7d22\u5f15\u7684\u7b49\u503c join \u8fde\u63a5\uff0chas…","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/5383"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5383"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/5383\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5383"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5383"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5383"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}