肏逼福利影城,国产嫩草影院久久久久,日本xxxx在线,夹夹夹一区二区,精品无码在线观看,丁香久月,开心激情五月天网,手机AV网站在线观看

Java技術(shù)棧

www.javastack.cn

關(guān)注閱讀更多優(yōu)質(zhì)文章

MySQL 開(kāi)發(fā)組于 2019 年 10 月 14 日正式發(fā)布了 MySQL 8.0.18 GA 版本，帶來(lái)了一些新特性和增強(qiáng)功能。其中最引人注目的莫過(guò)于多表連接查詢(xún)支持 hash join 方式了。

我們先來(lái)看看官方的描述：

https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

MySQL 實(shí)現(xiàn)了用于內(nèi)連接查詢(xún)的 hash join 方式。例如，從 MySQL 8.0.18 開(kāi)始以下查詢(xún)可以使用 hash join 進(jìn)行連接查詢(xún)：

SELECT?*?
????FROM?t1?
????JOIN?t2?
????????ON?t1.c1=t2.c1;

Hash join 不需要索引的支持。大多數(shù)情況下，hash join 比之前的 Block Nested-Loop 算法在沒(méi)有索引時(shí)的等值連接更加高效。

使用以下語(yǔ)句創(chuàng)建三張測(cè)試表：

CREATE?TABLE?t1?(c1?INT,?c2?INT);
CREATE?TABLE?t2?(c1?INT,?c2?INT);
CREATE?TABLE?t3?(c1?INT,?c2?INT);

使用EXPLAIN FORMAT=TREE命令可以看到執(zhí)行計(jì)劃中的 hash join，例如：

mysql>?EXPLAIN?FORMAT=TREE
????->?SELECT?*?
????->?????FROM?t1?
????->?????JOIN?t2?
????->?????????ON?t1.c1=t2.c1\G
***************************?1.?row?***************************
EXPLAIN:?->?Inner?hash?join?(t2.c1?=?t1.c1)??(cost=0.70?rows=1)
????->?Table?scan?on?t2??(cost=0.35?rows=1)
????->?Hash
????????->?Table?scan?on?t1??(cost=0.35?rows=1)

必須使用 EXPLAIN 命令的 FORMAT=TREE 選項(xiàng)才能看到節(jié)點(diǎn)中的 hash join。另外，EXPLAIN ANALYZE命令也可以顯示 hash join 的使用信息。這也是該版本新增的一個(gè)功能。

多個(gè)表之間使用等值連接的的查詢(xún)也會(huì)進(jìn)行這種優(yōu)化。例如以下查詢(xún)：

SELECT?*?
????FROM?t1
????JOIN?t2?
????????ON?(t1.c1?=?t2.c1?AND?t1.c2?????JOIN?t3?
????????ON?(t2.c1?=?t3.c1);

在以上示例中，任何其他非等值連接的條件將會(huì)在連接操作之后作為過(guò)濾器使用。圖解 5 種 Join 連接及實(shí)戰(zhàn)案例，這篇有必要看下。

可以通過(guò)EXPLAIN FORMAT=TREE命令的輸出進(jìn)行查看：

mysql>?EXPLAIN?FORMAT=TREE
????->?SELECT?*?
????->?????FROM?t1
????->?????JOIN?t2?
????->?????????ON?(t1.c1?=?t2.c1?AND?t1.c2?????->?????JOIN?t3?
????->?????????ON?(t2.c1?=?t3.c1)\G
***************************?1.?row?***************************
EXPLAIN:?->?Inner?hash?join?(t3.c1?=?t1.c1)??(cost=1.05?rows=1)
????->?Table?scan?on?t3??(cost=0.35?rows=1)
????->?Hash
????????->?Filter:?(t1.c2?cost=0.70?rows=1)
????????????->?Inner?hash?join?(t2.c1?=?t1.c1)??(cost=0.70?rows=1)
????????????????->?Table?scan?on?t2??(cost=0.35?rows=1)
????????????????->?Hash
????????????????????->?Table?scan?on?t1??(cost=0.35?rows=1)

從以上輸出同樣可以看出，包含多個(gè)等值連接條件的查詢(xún)也可以（會(huì)）使用多個(gè) hash join 連接。關(guān)注公眾號(hào)Java技術(shù)棧獲取更多MySQL教程。

但是，如果任何連接語(yǔ)句（ON）中沒(méi)有使用等值連接條件，將不會(huì)采用 hash join 連接方式。

例如：

mysql>?EXPLAIN?FORMAT=TREE
????->?????SELECT?*?
????->?????????FROM?t1
????->?????????JOIN?t2?
????->?????????????ON?(t1.c1?=?t2.c1)
????->?????????JOIN?t3?
????->?????????????ON?(t2.c1?***************************?1.?row?***************************
EXPLAIN:?<not?executable?by?iterator?executor>

此時(shí)，將會(huì)采用性能更慢的 block nested loop 連接算法。這與 MySQL 8.0.18 之前版本中沒(méi)有索引時(shí)的情況一樣：

mysql>?EXPLAIN
????->?????SELECT?*?
????->?????????FROM?t1
????->?????????JOIN?t2?
????->?????????????ON?(t1.c1?=?t2.c1)
????->?????????JOIN?t3?
????->?????????????ON?(t2.c1?***************************?1.?row?***************************
???????????id:?1
??select_type:?SIMPLE
????????table:?t1
???partitions:?NULL
?????????type:?ALL
possible_keys:?NULL
??????????key:?NULL
??????key_len:?NULL
??????????ref:?NULL
?????????rows:?1
?????filtered:?100.00
????????Extra:?NULL
***************************?2.?row?***************************
???????????id:?1
??select_type:?SIMPLE
????????table:?t2
???partitions:?NULL
?????????type:?ALL
possible_keys:?NULL
??????????key:?NULL
??????key_len:?NULL
??????????ref:?NULL
?????????rows:?1
?????filtered:?100.00
????????Extra:?Using?where;?Using?join?buffer?(Block?Nested?Loop)
***************************?3.?row?***************************
???????????id:?1
??select_type:?SIMPLE
????????table:?t3
???partitions:?NULL
?????????type:?ALL
possible_keys:?NULL
??????????key:?NULL
??????key_len:?NULL
??????????ref:?NULL
?????????rows:?1
?????filtered:?100.00
????????Extra:?Using?where;?Using?join?buffer?(Block?Nested?Loop)

Hash join 連接同樣適用于不指定查詢(xún)條件時(shí)的笛卡爾積（Cartesian product），例如：

mysql>?EXPLAIN?FORMAT=TREE
????->?SELECT?*
????->?????FROM?t1
????->?????JOIN?t2
????->?????WHERE?t1.c2?>?50\G
***************************?1.?row?***************************
EXPLAIN:?->?Inner?hash?join??(cost=0.70?rows=1)
????->?Table?scan?on?t2??(cost=0.35?rows=1)
????->?Hash
????????->?Filter:?(t1.c2?>?50)??(cost=0.35?rows=1)
????????????->?Table?scan?on?t1??(cost=0.35?rows=1)

默認(rèn)配置時(shí)，MySQL 所有可能的情況下都會(huì)使用 hash join。同時(shí)提供了兩種控制是否使用 hash join 的方法：

在全局或者會(huì)話(huà)級(jí)別設(shè)置服務(wù)器系統(tǒng)變量 optimizer_switch 中的 hash_join=on 或者 hash_join=off 選項(xiàng)。默認(rèn)為 hash_join=on。
在語(yǔ)句級(jí)別為特定的連接指定優(yōu)化器提示 HASH_JOIN 或者 NO_HASH_JOIN。

可以通過(guò)系統(tǒng)變量 join_buffer_size 控制 hash join 允許使用的內(nèi)存數(shù)量；hash join 不會(huì)使用超過(guò)該變量設(shè)置的內(nèi)存數(shù)量。如果 hash join 所需的內(nèi)存超過(guò)該閾值，MySQL 將會(huì)在磁盤(pán)中執(zhí)行操作。

需要注意的是，如果 hash join 無(wú)法在內(nèi)存中完成，并且打開(kāi)的文件數(shù)量超過(guò)系統(tǒng)變量 open_files_limit 的值，連接操作可能會(huì)失敗。為了解決這個(gè)問(wèn)題，可以使用以下方法之一：

增加 join_buffer_size 的值，確保 hash join 可以在內(nèi)存中完成。
增加 open_files_limit 的值。

接下來(lái)我們比較一下 hash join 和 block nested loop 的性能，首先分別為 t1、t2 和 t3 生成 1000000 條記錄：

set?join_buffer_size=2097152000;

SET?@@cte_max_recursion_depth?=?99999999;

INSERT?INTO?t1
--?INSERT?INTO?t2
--?INSERT?INTO?t3
WITH?RECURSIVE?t?AS?(
??SELECT?1?AS?c1,?1?AS?c2
??UNION?ALL
??SELECT?t.c1?+?1,?t.c1?*?2
????FROM?t
???WHERE?t.c1?1000000
)
SELECT?*
??FROM?t;

沒(méi)有索引情況下的 hash join：

mysql>?EXPLAIN?ANALYZE
????->?SELECT?COUNT(*)
????->???FROM?t1
????->???JOIN?t2?
????->?????ON?(t1.c1?=?t2.c1)
????->???JOIN?t3?
????->?????ON?(t2.c1?=?t3.c1)\G
***************************?1.?row?***************************
EXPLAIN:?->?Aggregate:?count(0)??(actual?time=22993.098..22993.099?rows=1?loops=1)
????->?Inner?hash?join?(t3.c1?=?t1.c1)??(cost=9952535443663536.00?rows=9952435908880402)?(actual?time=14489.176..21737.032?rows=1000000?loops=1)
????????->?Table?scan?on?t3??(cost=0.00?rows=998412)?(actual?time=0.103..3973.892?rows=1000000?loops=1)
????????->?Hash
????????????->?Inner?hash?join?(t2.c1?=?t1.c1)??(cost=99682753413.67?rows=99682653660)?(actual?time=5663.592..12236.984?rows=1000000?loops=1)
????????????????->?Table?scan?on?t2??(cost=0.01?rows=998412)?(actual?time=0.067..3364.105?rows=1000000?loops=1)
????????????????->?Hash
????????????????????->?Table?scan?on?t1??(cost=100539.40?rows=998412)?(actual?time=0.133..3395.799?rows=1000000?loops=1)

1?row?in?set?(23.22?sec)

mysql>?SELECT?COUNT(*)
????->???FROM?t1
????->???JOIN?t2?
????->?????ON?(t1.c1?=?t2.c1)
????->???JOIN?t3?
????->?????ON?(t2.c1?=?t3.c1);
+----------+
|?COUNT(*)?|
+----------+
|??1000000?|
+----------+
1?row?in?set?(12.98?sec)

實(shí)際運(yùn)行花費(fèi)了 12.98 秒。這個(gè)時(shí)候如果使用 block nested loop：

mysql>?EXPLAIN?FORMAT=TREE
????->?SELECT?/*+??NO_HASH_JOIN(t1,?t2,?t3)?*/?COUNT(*)
????->???FROM?t1
????->???JOIN?t2?
????->?????ON?(t1.c1?=?t2.c1)
????->???JOIN?t3?
????->?????ON?(t2.c1?=?t3.c1)\G
***************************?1.?row?***************************
EXPLAIN:?<not?executable?by?iterator?executor>

1?row?in?set?(0.00?sec)

SELECT?/*+??NO_HASH_JOIN(t1,?t2,?t3)?*/?COUNT(*)
??FROM?t1
??JOIN?t2?
????ON?(t1.c1?=?t2.c1)
??JOIN?t3?
????ON?(t2.c1?=?t3.c1);

EXPLAIN 顯示無(wú)法使用 hash join。查詢(xún)跑了幾十分鐘也沒(méi)有出結(jié)果，其中一個(gè) CPU 使用率到了 100%；因?yàn)橐恢痹趫?zhí)行嵌套循環(huán)（1000000 的 3 次方）。

再看有索引時(shí)的 block nested loop 方法，增加索引：

mysql>?CREATE?index?idx1?ON?t1(c1);
Query?OK,?0?rows?affected?(7.39?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?CREATE?index?idx2?ON?t2(c1);
Query?OK,?0?rows?affected?(6.77?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?CREATE?index?idx3?ON?t3(c1);
Query?OK,?0?rows?affected?(7.23?sec)
Records:?0??Duplicates:?0??Warnings:?0

查看執(zhí)行計(jì)劃并運(yùn)行相同的查詢(xún)語(yǔ)句：

mysql>?EXPLAIN?ANALYZE
????->?SELECT?COUNT(*)
????->???FROM?t1
????->???JOIN?t2?
????->?????ON?(t1.c1?=?t2.c1)
????->???JOIN?t3?
????->?????ON?(t2.c1?=?t3.c1)\G
***************************?1.?row?***************************
EXPLAIN:?->?Aggregate:?count(0)??(actual?time=47684.034..47684.035?rows=1?loops=1)
????->?Nested?loop?inner?join??(cost=2295573.22?rows=998412)?(actual?time=0.116..46363.599?rows=1000000?loops=1)
????????->?Nested?loop?inner?join??(cost=1198056.31?rows=998412)?(actual?time=0.087..25788.696?rows=1000000?loops=1)
????????????->?Filter:?(t1.c1?is?not?null)??(cost=100539.40?rows=998412)?(actual?time=0.050..5557.847?rows=1000000?loops=1)
????????????????->?Index?scan?on?t1?using?idx1??(cost=100539.40?rows=998412)?(actual?time=0.043..3253.769?rows=1000000?loops=1)
????????????->?Index?lookup?on?t2?using?idx2?(c1=t1.c1)??(cost=1.00?rows=1)?(actual?time=0.012..0.015?rows=1?loops=1000000)
????????->?Index?lookup?on?t3?using?idx3?(c1=t1.c1)??(cost=1.00?rows=1)?(actual?time=0.012..0.015?rows=1?loops=1000000)

1?row?in?set?(47.68?sec)

mysql>?SELECT?COUNT(*)
????->???FROM?t1
????->???JOIN?t2?
????->?????ON?(t1.c1?=?t2.c1)
????->???JOIN?t3?
????->?????ON?(t2.c1?=?t3.c1);
+----------+
|?COUNT(*)?|
+----------+
|??1000000?|
+----------+
1?row?in?set?(19.56?sec)