久久r热视频,国产午夜精品一区二区三区视频,亚洲精品自拍偷拍,欧美日韩精品二区

您的位置:首頁(yè)技術(shù)文章
文章詳情頁(yè)

詳解MySQL 聯(lián)合查詢優(yōu)化機(jī)制

瀏覽:4日期:2023-10-03 12:56:25
MySQL 聯(lián)合查詢執(zhí)行策略。

以一個(gè) UNION 查詢?yōu)槔琈ySQL 執(zhí)行 UNION 查詢時(shí),會(huì)把他們當(dāng)做一系列的單個(gè)查詢語(yǔ)句,然后把對(duì)應(yīng)的結(jié)果放入到臨時(shí)表中,最終再讀出來返回。在 MySQL中,每個(gè)獨(dú)立的查詢都是一個(gè)聯(lián)合查詢,從臨時(shí)表讀取返回結(jié)果也一樣。

這種情形下,MySQL 的聯(lián)合查詢執(zhí)行很簡(jiǎn)單——它將這里的聯(lián)合查詢當(dāng)做是嵌套循環(huán)的聯(lián)合查詢。這意味著 MySQL 會(huì)運(yùn)行一個(gè)循環(huán)去從數(shù)據(jù)表讀取數(shù)據(jù)行,然而在運(yùn)行一個(gè)嵌套循環(huán)從下一個(gè)表讀取匹配的數(shù)據(jù)行。這個(gè)過程一直持續(xù),直到找到聯(lián)合查詢中的所有匹配的數(shù)據(jù)行。然后再根據(jù) SELECT 語(yǔ)句中需要的列去構(gòu)建返回結(jié)果。如下面的查詢語(yǔ)句所示:

SELECT tb1.col1, tb2.col2FROM tb1 INNER JOIN tb2 USING(col3)WHERE tb1.col1 IN(5,6);

實(shí)際轉(zhuǎn)換為 MySQL可能執(zhí)行的偽代碼是下面這樣的:

outer_iter = iterator over tb1 where col1 IN(5,6);outer_row = outer_iter.next;while outer_rowinner_iter = iterator over tb2 where col3 = outer_row.col3;inner_row = inner_iter.next while inner_row output [outer_row.col1, inner_row.col2];inner_row = inner_iter.next;end outer_row = outer.iter.next;end

轉(zhuǎn)換為偽代碼后如下所示

outer_iter = iterator over tb1 where col1 IN(5,6);outer_row = outer_iter.next;while outer_rowinner_iter = iterator over tb2 where col3 = outer_row.col3;inner_row = inner_iter.next if inner_rowwhile inner_row output [outer_row.col1, inner_row.col2]; inner_row = inner_iter.next;end else output [outer_row.col1, NULL];end outer_row = outer.iter.next;end

另一個(gè)方式可視化展現(xiàn)查詢計(jì)劃的方式是使用泳道圖的形式。下面的圖展示了 內(nèi)連接查詢的泳道圖。

詳解MySQL 聯(lián)合查詢優(yōu)化機(jī)制

MySQL 執(zhí)行的各類查詢基本上都是相同的方式。例如,在 FROM 條件里需要先執(zhí)行的子查詢時(shí),也是先將結(jié)果放入臨時(shí)表,然后再把臨時(shí)表當(dāng)作普通表后聯(lián)合來處理。MySQL 執(zhí)行聯(lián)合查詢時(shí)也是使用臨時(shí)表,然后將右連接查詢重寫為等價(jià)的左連接。簡(jiǎn)而言之,當(dāng)前版本的 MySQL 會(huì)盡可能把各類查詢轉(zhuǎn)成這種方式處理(最新版本 MySQL5.6以后引入了更多的復(fù)雜的處理方式)。

當(dāng)然,并不是所有合法的 SQL 查詢語(yǔ)句都可以這么做,有些查詢這么做的效果可能很差。

執(zhí)行計(jì)劃

MySQL不像其他很多數(shù)據(jù)庫(kù)產(chǎn)品,它不會(huì)將查詢語(yǔ)句產(chǎn)生字節(jié)碼去執(zhí)行查詢計(jì)劃。實(shí)際上,查詢執(zhí)行計(jì)劃是一棵指令樹,查詢執(zhí)行引擎根據(jù)這棵樹產(chǎn)生查詢結(jié)果。最終的查詢計(jì)劃包含了足夠多的信息去重構(gòu)最初的查詢。如果在查詢語(yǔ)句上執(zhí)行EXPLAIN EXTENDED(MySQL 8以后不需要加 EXTENDED),然后再執(zhí)行SHOW WARNINGS,就可以看到重構(gòu)后的查詢。

詳解MySQL 聯(lián)合查詢優(yōu)化機(jī)制

對(duì)于多表查詢?cè)诟拍钌峡梢杂脴浯怼@纾粋€(gè)4張表的查詢可能長(zhǎng)得像下面的樹一樣。這在計(jì)算機(jī)里稱為平衡樹,

然而這不是 MySQL 執(zhí)行查詢的方式。如前所述,MySQL 總是從一張數(shù)據(jù)表開始,然后再?gòu)南乱粡埍韺ふ移ヅ涞臄?shù)據(jù)行。因此,MySQL 的查詢計(jì)劃看起來像下面的左深連接樹。

詳解MySQL 聯(lián)合查詢優(yōu)化機(jī)制

聯(lián)合查詢優(yōu)化器

MySQL 的查詢優(yōu)化器中最重要的部分是聯(lián)合查詢優(yōu)化器,由它來決定多表查詢執(zhí)行過程的最優(yōu)順序。通常可以通過多種聯(lián)合查詢的次序獲取相同的結(jié)果。聯(lián)合查詢優(yōu)化器試圖估計(jì)這些方案的代價(jià),然后選擇最低代價(jià)的方案去執(zhí)行。

下面是一個(gè)查詢相同結(jié)果,但不同次序的聯(lián)合查詢示例。

SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_nameFROM sakila.filmINNER JOIN sakila.film_actor USING(film_id)INNER JOIN sakila.actor USING(actor_id);

這里面可能會(huì)有一些不同的查詢方式。比如,MySQL 可以從 film 表開始,使用 film_actor 的film_id 索引去查找對(duì)應(yīng)的 actor_di 值,然后再?gòu)?actor 表使用主鍵找到對(duì)應(yīng)的 actor 數(shù)據(jù)行。而 Oracle 用戶可能會(huì)表述為:“film 表是 film_actor 的驅(qū)動(dòng)表,而 film_actor 是 actor 表的驅(qū)動(dòng)表”。而使用 Explain 解析的結(jié)果如下:

******** 1.row ********id: 1select_type: SIMPLEtable: actortype: ALLpossible_keys: PRIMARYkey: NULLkey_len: NULLref: NULLrows: 200Extra:******** 2.row ********id: 1select_type: SIMPLEtable: film_actortype: refpossible_keys: PRIMARY, idx_fk_film_idkey: PRIMARYkey_len: 2ref: sakila.film.film_idrows: 1Extra: USING index******** 3.row ********id: 1select_type: SIMPLEtable: filmtype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 2ref: sakila.film_actor.film_idrows: 1Extra:

這個(gè)執(zhí)行計(jì)劃與我們猜想的有很大不同。MySQL 首先從 actor 表開始,然后次序是反向的。這是否真的更有效?我們可以在 EXPLAIN 上加上 STRAIGHT_JOIN 來避免優(yōu)化:

EXPLAIN SELECT STRAIGHT_JOIN film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_nameFROM sakila.filmINNER JOIN sakila.film_actor USING(film_id)INNER JOIN sakila.actor USING(actor_id);

******** 1.row ********id: 1select_type: SIMPLEtable: filmtype: ALLpossible_keys: PRIMARYkey: NULLkey_len: NULLref: NULLrows: 951Extra:******** 2.row ********id: 1select_type: SIMPLEtable: film_actortype: refpossible_keys: PRIMARY, idx_fk_film_idkey: idx_fk_film_idkey_len: 2ref: sakila.film.film_idrows: 1Extra: USING index******** 3.row ********id: 1select_type: SIMPLEtable: actortype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 2ref: sakila.film_actor.actor_idrows: 1Extra:

這解釋了為什么MySQL 為什么需要反序執(zhí)行查詢,這會(huì)使得檢查的數(shù)據(jù)行更少。

先查詢 film 表會(huì)需要對(duì) film_actor 和 actor 進(jìn)行951次查詢(最外層循環(huán)) 如果將 actor表前置,則只需要對(duì)其他表進(jìn)行200次查詢。

從這個(gè)例子可以看出,MySQL 的聯(lián)合查詢優(yōu)化器可以通過調(diào)整查詢表次序降低查詢代價(jià)。重新排序后的聯(lián)合查詢通常是很有效的優(yōu)化,通常是幾倍性能的提高。如果沒有性能提高的話,也可以使用 STRAIGHT_JOIN 來避免重排序,而使用我們自己認(rèn)為最好的查詢方式。這種情況實(shí)際遇到的會(huì)很少,大部分情況下,聯(lián)合查詢優(yōu)化器都會(huì)比人做得更出色。

聯(lián)合查詢優(yōu)化器視圖以最低完成代價(jià)構(gòu)建一個(gè)查詢執(zhí)行樹。如果有可能,它會(huì)從全部的單表計(jì)劃開始,檢查所有可能的子樹組合。不幸的是,一個(gè) N 張表的聯(lián)合查詢會(huì)有 N 個(gè)階乘的組合次序數(shù)量。這被稱之為所有可能的查詢計(jì)劃的搜索空間,這個(gè)數(shù)量增長(zhǎng)非常快。一個(gè)10張表的聯(lián)合索引會(huì)有3628800個(gè)不同的方式!一旦搜索空間增長(zhǎng)到過大,會(huì)導(dǎo)致查詢的優(yōu)化十分久,這時(shí)候服務(wù)端會(huì)停止做全量分析,替代以類似貪婪算法的方式完成優(yōu)化。這個(gè)數(shù)量通過 optimizer_search_depth 系統(tǒng)變量控制,可以自己修改該參數(shù)。

標(biāo)簽: MySQL 數(shù)據(jù)庫(kù)
相關(guān)文章:
主站蜘蛛池模板: 贵港市| 蓝山县| 确山县| 永昌县| 双峰县| 治多县| 庄河市| 中阳县| 宝山区| 泰来县| 三河市| 沙田区| 扶风县| 东港市| 石屏县| 商城县| 昌邑市| 海伦市| 肃宁县| 修武县| 汾西县| 修文县| 松原市| 新津县| 方山县| 东阳市| 瑞丽市| 曲阳县| 呼和浩特市| 陇南市| 盘锦市| 达拉特旗| 米林县| 桂林市| 札达县| 乌什县| 会宁县| 拜城县| 新泰市| 青冈县| 冕宁县|