查詢優(yōu)化

• 避免全表掃描：盡量使用索引來避免全表掃描，例如在WHERE子句中使用索引列進(jìn)行過濾。
• 優(yōu)化子查詢：子查詢可能會(huì)導(dǎo)致性能問題，可以考慮使用JOIN來替代子查詢。
• 減少不必要的列：在查詢時(shí)只選擇需要的列，避免使用SELECT *。

配置參數(shù)調(diào)整

• 調(diào)整內(nèi)存分配：根據(jù)服務(wù)器的硬件資源和業(yè)務(wù)需求，調(diào)整innodb_buffer_pool_size、key_buffer_size等參數(shù)，以提高緩存命中率。
• 調(diào)整日志參數(shù)：根據(jù)業(yè)務(wù)需求調(diào)整log_bin、innodb_log_file_size等參數(shù)，以平衡數(shù)據(jù)安全性和性能。

硬件優(yōu)化

• 使用高速存儲(chǔ)設(shè)備：如 SSD 可以顯著提高磁盤 I/O 性能。
• 增加內(nèi)存：足夠的內(nèi)存可以減少磁盤 I/O，提高查詢性能。

MySQL的執(zhí)行原理

1.2.MySQL的查詢重寫規(guī)則

對(duì)于一些執(zhí)行起來十分耗費(fèi)性能的語句，MySQL還是依據(jù)一些規(guī)則，竭盡全力的把這個(gè)很糟糕的語句轉(zhuǎn)換成某種可以比較高效執(zhí)行的形式，這個(gè)過程也可以被稱作查詢重寫。

1.2.1.條件化簡(jiǎn)

我們編寫的查詢語句的搜索條件本質(zhì)上是一個(gè)表達(dá)式，這些表達(dá)式可能比較繁雜，或者不能高效的執(zhí)行，MySQL的查詢優(yōu)化器會(huì)為我們簡(jiǎn)化這些表達(dá)式。

1.2.1.1.移除不必要的括號(hào)

有時(shí)候表達(dá)式里有許多無用的括號(hào)，比如這樣：

((a = 5 AND b =c) OR ((a > c) AND (c < 5)))

看著就很煩，優(yōu)化器會(huì)把那些用不到的括號(hào)給干掉，就是這樣：

(a = 5 and b =c) OR (a > c AND c < 5)

1.2.1.2.常量傳遞（constant_propagation）

有時(shí)候某個(gè)表達(dá)式是某個(gè)列和某個(gè)常量做等值匹配，比如這樣：

a = 5

當(dāng)這個(gè)表達(dá)式和其他涉及列a的表達(dá)式使用AND連接起來時(shí)，可以將其他表達(dá)式中的a的值替換為5，比如這樣：

a = 5 AND b >a

就可以被轉(zhuǎn)換為：

a = 5 AND b >5

等值傳遞（equality_propagation）

有時(shí)候多個(gè)列之間存在等值匹配的關(guān)系，比如這樣：

a = b and b = c and c = 5

這個(gè)表達(dá)式可以被簡(jiǎn)化為：

a = 5 and b = 5 and c = 5

1.2.1.3.移除沒用的條件（trivial_condition_removal）

對(duì)于一些明顯永遠(yuǎn)為TRUE或者FALSE的表達(dá)式，優(yōu)化器會(huì)移除掉它們，比如這個(gè)表達(dá)式：

(a < 1 and b= b) OR (a = 6 OR 5 != 5)

很明顯，b = b這個(gè)表達(dá)式永遠(yuǎn)為TRUE，5 != 5這個(gè)表達(dá)式永遠(yuǎn)為FALSE，所以簡(jiǎn)化后的表達(dá)式就是這樣的：

(a < 1 and TRUE) OR (a = 6 OR FALSE)

可以繼續(xù)被簡(jiǎn)化為

a < 1 OR a =6

1.2.1.4.表達(dá)式計(jì)算

在查詢開始執(zhí)行之前，如果表達(dá)式中只包含常量的話，它的值會(huì)被先計(jì)算出來，比如這個(gè)：

a = 5 + 1

因?yàn)? + 1這個(gè)表達(dá)式只包含常量，所以就會(huì)被化簡(jiǎn)成：

a = 6

但是這里需要注意的是，如果某個(gè)列并不是以單獨(dú)的形式作為表達(dá)式的操作數(shù)時(shí)，比如出現(xiàn)在函數(shù)中，出現(xiàn)在某個(gè)更復(fù)雜表達(dá)式中，就像這樣：

ABS(a) > 5

或者：

-a < -8

優(yōu)化器是不會(huì)嘗試對(duì)這些表達(dá)式進(jìn)行化簡(jiǎn)的。我們前邊說過只有搜索條件中索引列和常數(shù)使用某些運(yùn)算符連接起來才可能使用到索引，所以如果可以的話，最好讓索引列以單獨(dú)的形式出現(xiàn)在表達(dá)式中。

1.2.1.5.常量表檢測(cè)

MySQL覺得下邊這種查詢運(yùn)行的特別快：

使用主鍵等值匹配或者唯一二級(jí)索引列等值匹配作為搜索條件來查詢某個(gè)表。

MySQL覺得這兩種查詢花費(fèi)的時(shí)間特別少，少到可以忽略，所以也把通過這兩種方式查詢的表稱之為常量表（英文名：constant tables）。優(yōu)化器在分析一個(gè)查詢語句時(shí)，先首先執(zhí)行常量表查詢，然后把查詢中涉及到該表的條件全部替換成常數(shù)，最后再分析其余表的查詢成本，比方說這個(gè)查詢語句：

SELECT
	*
FROM
	table1
INNER JOIN table2 ON table1.column1 = table2.column2
WHERE
	table1.primary_key = 1;

很明顯，這個(gè)查詢可以使用主鍵和常量值的等值匹配來查詢table1表，也就是在這個(gè)查詢中table1表相當(dāng)于常量表，在分析對(duì)table2表的查詢成本之前，就會(huì)執(zhí)行對(duì)table1表的查詢，并把查詢中涉及table1表的條件都替換掉，也就是上邊的語句會(huì)被轉(zhuǎn)換成這樣：

SELECT
	table1表記錄的各個(gè)字段的常量值,
	table2.*
FROM
	table1
INNER JOIN table2 ON table1表column1列的常量值 = table2.column2;

1.2.2.外連接消除

我們前邊說過，內(nèi)連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表的位置可以相互轉(zhuǎn)換，而左（外）連接和右（外）連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表是固定的。這就導(dǎo)致內(nèi)連接可能通過優(yōu)化表的連接順序來降低整體的查詢成本，而外連接卻無法優(yōu)化表的連接順序。

我們之前說過，外連接和內(nèi)連接的本質(zhì)區(qū)別就是：對(duì)于外連接的驅(qū)動(dòng)表的記錄來說，如果無法在被驅(qū)動(dòng)表中找到匹配ON子句中的過濾條件的記錄，那么該記錄仍然會(huì)被加入到結(jié)果集中，對(duì)應(yīng)的被驅(qū)動(dòng)表記錄的各個(gè)字段使用NULL值填充；而內(nèi)連接的驅(qū)動(dòng)表的記錄如果無法在被驅(qū)動(dòng)表中找到匹配ON子句中的過濾條件的記錄，那么該記錄會(huì)被舍棄。查詢效果就是這樣：

SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2;

SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2;

對(duì)于上邊例子中的（左）外連接來說，由于驅(qū)動(dòng)表e1中m1=1, n1='a’的記錄無法在被驅(qū)動(dòng)表e2中找到符合ON子句條件e1.m1 = e2.m2的記錄，所以就直接把這條記錄加入到結(jié)果集，對(duì)應(yīng)的e2表的m2和n2列的值都設(shè)置為NULL。

因?yàn)榉彩遣环蟇HERE子句中條件的記錄都不會(huì)參與連接。只要我們?cè)谒阉鳁l件中指定關(guān)于被驅(qū)動(dòng)表相關(guān)列的值不為NULL，那么外連接中在被驅(qū)動(dòng)表中找不到符合ON子句條件的驅(qū)動(dòng)表記錄也就被排除出最后的結(jié)果集了，也就是說：在這種情況下：外連接和內(nèi)連接也就沒有什么區(qū)別了！

另外再說下這個(gè)查詢：

SELECT* FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.n2 IS NOT NULL

由于指定了被驅(qū)動(dòng)表e2的n2列不允許為NULL，所以上邊的e1和e2表的左（外）連接查詢和內(nèi)連接查詢是一樣的。當(dāng)然，我們也可以不用顯式的指定被驅(qū)動(dòng)表的某個(gè)列IS NOT NULL，只要隱含的有這個(gè)意思就行了，比方說這樣：

SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2

在這個(gè)例子中，我們?cè)赪HERE子句中指定了被驅(qū)動(dòng)表e2的m2列等于2，也就相當(dāng)于間接的指定了m2列不為NULL值，所以上邊的這個(gè)左（外）連接查詢其實(shí)和下邊這個(gè)內(nèi)連接查詢是等價(jià)的：

SELECT* FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2

我們把這種在外連接查詢中，指定的WHERE子句中包含被驅(qū)動(dòng)表中的列不為NULL值的條件稱之為空值拒絕（英文名：reject-NULL）。在被驅(qū)動(dòng)表的WHERE子句符合空值拒絕的條件后，外連接和內(nèi)連接可以相互轉(zhuǎn)換。這種轉(zhuǎn)換帶來的好處就是查詢優(yōu)化器可以通過評(píng)估表的不同連接順序的成本，選出成本最低的那種連接順序來執(zhí)行查詢。

1.2.3.子查詢優(yōu)化

1.2.3.1.子查詢語法

在一個(gè)查詢語句A里的某個(gè)位置也可以有另一個(gè)查詢語句B，這個(gè)出現(xiàn)在A語句的某個(gè)位置中的查詢B就被稱為子查詢，A也被稱之為外層查詢。子查詢可以在一個(gè)外層查詢的各種位置出現(xiàn)，比如：

1）SELECT子句中

也就是我們平時(shí)說的查詢列表中，比如這樣：

SELECT(SELECT m1 FROM e1 LIMIT 1);

其中的(SELECT m1 FROM e1LIMIT 1)就是子查詢。

2）FROM子句中

SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2) AS t;

這個(gè)例子中的子查詢是：(SELECT m2+1 AS m, n2 AS n FROM e2 WHERE m2 > 2)

這里可以把子查詢的查詢結(jié)果當(dāng)作是一個(gè)表，子查詢后邊的AS t表明這個(gè)子查詢的結(jié)果就相當(dāng)于一個(gè)名稱為t的表，這個(gè)名叫t的表的列就是子查詢結(jié)果中的列，比如例子中表t就有兩個(gè)列：m列和n列。

這個(gè)放在FROM子句中的子查詢本質(zhì)上相當(dāng)于一個(gè)表，但又和我們平常使用的表有點(diǎn)兒不一樣，MySQL把這種由子查詢結(jié)果集組成的表稱之為 派生表 。

3）WHERE或ON子句中

把子查詢放在外層查詢的WHERE子句或者ON子句中可能是我們最常用的一種使用子查詢的方式了，比如這樣：

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2)

這個(gè)查詢表明我們想要將(SELECT m2FROM e2)這個(gè)子查詢的結(jié)果作為外層查詢的IN語句參數(shù)，整個(gè)查詢語句的意思就是我們想找e1表中的某些記錄，這些記錄的m1列的值能在e2表的m2列找到匹配的值。

4）ORDER BY子句、GROUP BY子句中

雖然語法支持，但沒啥意義。

還有一些其他的子查詢，這里不一一列舉

1.2.3.2.子查詢?cè)贛ySQL中是怎么執(zhí)行的

想象中子查詢的執(zhí)行方式是這樣的：

如果該子查詢是不相關(guān)子查詢，比如下邊這個(gè)查詢：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2);

先單獨(dú)執(zhí)行(SELECTorder_note FROM s2)這個(gè)子查詢。

然后在將上一步子查詢得到的結(jié)果當(dāng)作外層查詢的參數(shù)再執(zhí)行外層查詢

最后根據(jù)子查詢的查詢結(jié)果來檢測(cè)外層查詢WHERE子句的條件是否成立，如果成立，就把外層查詢的那條記錄加入到結(jié)果集，否則就丟棄。

但真的是這樣嗎？其實(shí)MySQL用了一系列的辦法來優(yōu)化子查詢的執(zhí)行，大部分情況下這些優(yōu)化措施其實(shí)挺有效的，下邊我們來看看各種不同類型的子查詢具體是怎么執(zhí)行的。

不同的子查詢

1）按返回的結(jié)果集區(qū)分子查詢

因?yàn)樽硬樵儽旧硪菜闶且粋€(gè)查詢，所以可以按照它們返回的不同結(jié)果集類型而把這些子查詢分為不同的類型：

1.1)標(biāo)量子查詢

那些只返回一個(gè)單一值的子查詢稱之為標(biāo)量子查詢，比如這樣：

SELECT (SELECT m1 FROM e1 LIMIT 1);

或者這樣：

SELECT * FROM e1 WHERE m1 = (SELECT MIN(m2) FROM e2);SELECT * FROM e1 WHERE m1 < (SELECT MIN(m2) FROM e2);

這兩個(gè)查詢語句中的子查詢都返回一個(gè)單一的值，也就是一個(gè)標(biāo)量。這些標(biāo)量子查詢可以作為一個(gè)單一值或者表達(dá)式的一部分出現(xiàn)在查詢語句的各個(gè)地方。

1.2)行子查詢

顧名思義，就是返回一條記錄的子查詢，不過這條記錄需要包含多個(gè)列（只包含一個(gè)列就成了標(biāo)量子查詢了）。比如這樣：

SELECT * FROM e1 WHERE (m1, n1) = (SELECT m2, n2 FROM e2 LIMIT 1);

其中的(SELECT m2, n2 FROM e2 LIMIT 1)就是一個(gè)行子查詢，整條語句的含義就是要從e1表中找一些記錄，這些記錄的m1和n1列分別等于子查詢結(jié)果中的m2和n2列。

1.3)列子查詢

列子查詢自然就是查詢出一個(gè)列的數(shù)據(jù)，不過這個(gè)列的數(shù)據(jù)需要包含多條記錄（只包含一條記錄就成了標(biāo)量子查詢了）。比如這樣：

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);

其中的(SELECT m2 FROM e2)就是一個(gè)列子查詢，表明查詢出e2表的m2列的值作為外層查詢IN語句的參數(shù)。

1.4)表子查詢

顧名思義，就是子查詢的結(jié)果既包含很多條記錄，又包含很多個(gè)列，比如這樣：

SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

其中的(SELECT m2, n2 FROM e2)就是一個(gè)表子查詢，

這里需要和行子查詢對(duì)比一下，行子查詢中我們用了LIMIT 1來保證子查詢的結(jié)果只有一條記錄，表子查詢中不需要這個(gè)限制。

2)按與外層查詢關(guān)系來區(qū)分子查詢

2.1)不相關(guān)子查詢

如果子查詢可以單獨(dú)運(yùn)行出結(jié)果，而不依賴于外層查詢的值，我們就可以把這個(gè)子查詢稱之為不相關(guān)子查詢。我們前邊介紹的那些子查詢?nèi)慷伎梢钥醋鞑幌嚓P(guān)子查詢。

2.2)相關(guān)子查詢

如果子查詢的執(zhí)行需要依賴于外層查詢的值，我們就可以把這個(gè)子查詢稱之為相關(guān)子查詢。比如：

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE n1 = n2);

例子中的子查詢是(SELECT m2 FROM e2 WHERE n1 = n2)，

可是這個(gè)查詢中有一個(gè)搜索條件是n1 = n2，別忘了n1是表e1的列，也就是外層查詢的列，也就是說子查詢的執(zhí)行需要依賴于外層查詢的值，所以這個(gè)子查詢就是一個(gè)相關(guān)子查詢。

3) [NOT] IN/ANY/SOME/ALL子查詢

對(duì)于列子查詢和表子查詢來說，它們的結(jié)果集中包含很多條記錄，這些記錄相當(dāng)于是一個(gè)集合，所以就不能單純的和另外一個(gè)操作數(shù)使用操作符來組成布爾表達(dá)式了，MySQL通過下面的語法來支持某個(gè)操作數(shù)和一個(gè)集合組成一個(gè)布爾表達(dá)式：

3.1)IN或者NOT IN

具體的語法形式如下：

操作數(shù) [NOT] IN (子查詢)

這個(gè)布爾表達(dá)式的意思是用來判斷某個(gè)操作數(shù)在不在由子查詢結(jié)果集組成的集合中，比如下邊的查詢的意思是找出e1表中的某些記錄，這些記錄存在于子查詢的結(jié)果集中：

SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

3.2) ANY/SOME（ANY和SOME是同義詞）

具體的語法形式如下：

操作數(shù) 比較符 ANY/SOME(子查詢)

這個(gè)布爾表達(dá)式的意思是只要子查詢結(jié)果集中存在某個(gè)值和給定的操作數(shù)做比較操作，比較結(jié)果為TRUE，那么整個(gè)表達(dá)式的結(jié)果就為TRUE，否則整個(gè)表達(dá)式的結(jié)果就為FALSE。比方說下邊這個(gè)查詢：

SELECT * FROM e1 WHERE m1 > ANY(SELECT m2 FROM e2);

這個(gè)查詢的意思就是對(duì)于e1表的某條記錄的m1列的值來說

如果子查詢(SELECTm2 FROM e2)的結(jié)果集中存在一個(gè)小于m1列的值，那么整個(gè)布爾表達(dá)式的值就是TRUE，

否則為FALSE，也就是說只要m1列的值大于子查詢結(jié)果集中最小的值，整個(gè)表達(dá)式的結(jié)果就是TRUE，所以上邊的查詢本質(zhì)上等價(jià)于這個(gè)查詢：

SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2);

另外，=ANY相當(dāng)于判斷子查詢結(jié)果集中是否存在某個(gè)值和給定的操作數(shù)相等，它的含義和IN是相同的。

3.3)ALL具體的語法形式如下：

操作數(shù) 比較操作 ALL(子查詢)

這個(gè)布爾表達(dá)式的意思是子查詢結(jié)果集中所有的值和給定的操作數(shù)做比較操作比較結(jié)果為TRUE，那么整個(gè)表達(dá)式的結(jié)果就為TRUE，否則整個(gè)表達(dá)式的結(jié)果就為FALSE。比方說下邊這個(gè)查詢：

SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2);

這個(gè)查詢的意思就是對(duì)于e1表的某條記錄的m1列的值來說

如果子查詢(SELECT m2 FROM e2)的結(jié)果集中的所有值都小于m1列的值，那么整個(gè)布爾表達(dá)式的值就是TRUE，否則為FALSE，也就是說只要m1列的值大于子查詢結(jié)果集中最大的值，整個(gè)表達(dá)式的結(jié)果就是TRUE，所以上邊的查詢本質(zhì)上等價(jià)于這個(gè)查詢：

SELECT * FROM e1 WHERE m1 > (SELECT MAX(m2) FROM e2);

3.4)EXISTS子查詢

有的時(shí)候我們僅僅需要判斷子查詢的結(jié)果集中是否有記錄，而不在乎它的記錄具體是個(gè)啥，可以使用把EXISTS或者NOT EXISTS放在子查詢語句前邊，就像這樣：

SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);

對(duì)于子查詢(SELECT 1 FROM e2)來說，我們并不關(guān)心這個(gè)子查詢最后到底查詢出的結(jié)果是什么，所以查詢列表里填*、某個(gè)列名，或者其他啥東西都無所謂，我們真正關(guān)心的是子查詢的結(jié)果集中是否存在記錄。也就是說只要(SELECT 1 FROM e2)這個(gè)查詢中有記錄，那么整個(gè)EXISTS表達(dá)式的結(jié)果就為TRUE。

1.2.3.3.MySQL對(duì)IN子查詢的優(yōu)化

1）標(biāo)量子查詢、行子查詢的執(zhí)行方式

對(duì)于不相關(guān)標(biāo)量子查詢或者行子查詢來說，它們的執(zhí)行方式很簡(jiǎn)單，比方說下邊這個(gè)查詢語句：

SELECT * FROM s1 WHERE order_note = (SELECT order_note FROM s2 WHERE key3 = 'a' LIMIT 1);

它的執(zhí)行方式和我們前面想象的一樣：

先單獨(dú)執(zhí)行(SELECT order_note FROM s2 WHERE key3 = ‘a’ LIMIT 1)這個(gè)子查詢。

然后在將上一步子查詢得到的結(jié)果當(dāng)作外層查詢的參數(shù)，

再執(zhí)行外層查詢SELECT * FROM s1 WHERE order_note= …。

也就是說，對(duì)于包含不相關(guān)的標(biāo)量子查詢或者行子查詢的查詢語句來說，MySQL會(huì)分別獨(dú)立的執(zhí)行外層查詢和子查詢，就當(dāng)作兩個(gè)單表查詢就好了。

對(duì)于相關(guān)的標(biāo)量子查詢或者行子查詢來說，比如下邊這個(gè)查詢：

SELECT * FROM s1 WHEREorder_note = (SELECT order_note FROM s2 WHERE s1.order_no= s2.order_no LIMIT 1);

事情也和我們前面想象的一樣，它的執(zhí)行方式就是這樣的：

先從外層查詢中獲取一條記錄，本例中也就是先從s1表中獲取一條記錄。

然后從上一步驟中獲取的那條記錄中找出子查詢中涉及到的值，本例中就是從s1表中獲取的那條記錄中找出s1.order_no列的值，然后執(zhí)行子查詢。

最后根據(jù)子查詢的查詢結(jié)果來檢測(cè)外層查詢WHERE子句的條件是否成立，如果成立，就把外層查詢的那條記錄加入到結(jié)果集，否則就丟棄。

再次執(zhí)行第一步，獲取第二條外層查詢中的記錄，依次類推。

也就是說對(duì)于兩種使用標(biāo)量子查詢以及行子查詢的場(chǎng)景中，MySQL優(yōu)化器的執(zhí)行方式并沒有什么新鮮的。

2)物化表

對(duì)于不相關(guān)的IN子查詢，比如這樣：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = 'a');

我們最開始的感覺就是這種不相關(guān)的IN子查詢和不相關(guān)的標(biāo)量子查詢或者行子查詢是一樣一樣的，都是把外層查詢和子查詢當(dāng)作兩個(gè)獨(dú)立的單表查詢來對(duì)待。但是MySQL為了優(yōu)化IN子查詢下了很大力氣，所以整個(gè)執(zhí)行過程并不像我們想象的那么簡(jiǎn)單。

對(duì)于不相關(guān)的IN子查詢來說，如果子查詢的結(jié)果集中的記錄條數(shù)很少，那么把子查詢和外層查詢分別看成兩個(gè)單獨(dú)的單表查詢效率很高，但是如果單獨(dú)執(zhí)行子查詢后的結(jié)果集太多的話，就會(huì)導(dǎo)致這些問題：

1、結(jié)果集太多，可能內(nèi)存中都放不下。

2、對(duì)于外層查詢來說，如果子查詢的結(jié)果集太多，那就意味著IN子句中的參數(shù)特別多，這就導(dǎo)致：無法有效的使用索引，只能對(duì)外層查詢進(jìn)行全表掃描。

在對(duì)外層查詢執(zhí)行全表掃描時(shí)，由于IN子句中的參數(shù)太多，這會(huì)導(dǎo)致檢測(cè)一條記錄是否符合和IN子句中的參數(shù)匹配花費(fèi)的時(shí)間太長(zhǎng)。

比如說IN子句中的參數(shù)只有兩個(gè)：

SELECT * FROM tbl_name WHERE column IN (a, b);

這樣相當(dāng)于需要對(duì)tbl_name表中的每條記錄判斷一下它的column列是否符合column = a OR column = b。

在IN子句中的參數(shù)比較少時(shí)這并不是什么問題，如果IN子句中的參數(shù)比較多時(shí)，比如這樣：

SELECT * FROM tbl_name WHERE column IN (a, b, c ..., ...);

那么這樣每條記錄需要判斷一下它的column列是否符合column =a OR column = b OR column = c OR …，這樣性能耗費(fèi)可就多了。

MySQL的改進(jìn)是不直接將不相關(guān)子查詢的結(jié)果集當(dāng)作外層查詢的參數(shù)，而是將該結(jié)果集寫入一個(gè)臨時(shí)表里。寫入臨時(shí)表的過程是這樣的：

1、該臨時(shí)表的列就是子查詢結(jié)果集中的列。

2、寫入臨時(shí)表的記錄會(huì)被去重，臨時(shí)表也是個(gè)表，只要為表中記錄的所有列建立主鍵或者唯一索引。

一般情況下子查詢結(jié)果集不會(huì)大的離譜，所以會(huì)為它建立基于內(nèi)存的使用Memory存儲(chǔ)引擎的臨時(shí)表，而且會(huì)為該表建立哈希索引。

如果子查詢的結(jié)果集非常大，超過了系統(tǒng)變量tmp_table_size或者max_heap_table_size，臨時(shí)表會(huì)轉(zhuǎn)而使用基于磁盤的存儲(chǔ)引擎來保存結(jié)果集中的記錄，索引類型也對(duì)應(yīng)轉(zhuǎn)變?yōu)锽+樹索引。

MySQL把這個(gè)將子查詢結(jié)果集中的記錄保存到臨時(shí)表的過程稱之為 物化 （英文名：Materialize）。為了方便起見，我們就把那個(gè)存儲(chǔ)子查詢結(jié)果集的臨時(shí)表稱之為物化表。正因?yàn)槲锘碇械挠涗浂冀⒘怂饕ɑ趦?nèi)存的物化表有哈希索引，基于磁盤的有B+樹索引），通過索引執(zhí)行IN語句判斷某個(gè)操作數(shù)在不在子查詢結(jié)果集中變得非?？?，從而提升了子查詢語句的性能。

3)物化表轉(zhuǎn)連接

事情到這就完了？我們還得重新審視一下最開始的那個(gè)查詢語句：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = 'a');

當(dāng)我們把子查詢進(jìn)行物化之后，假設(shè)子查詢物化表的名稱為materialized_table，該物化表存儲(chǔ)的子查詢結(jié)果集的列為m_val，那么這個(gè)查詢

就相當(dāng)于表s1和子查詢物化表materialized_table進(jìn)行內(nèi)連接：

SELECT s1.* FROM s1 INNER JOIN materialized_table ON order_note = m_val;

轉(zhuǎn)化成內(nèi)連接之后就有意思了，查詢優(yōu)化器可以評(píng)估不同連接順序需要的成本是多少，選取成本最低的那種查詢方式執(zhí)行查詢。

我們分析一下上述查詢中使用外層查詢的表s1和物化表materialized_table進(jìn)行內(nèi)連接的成本都是由哪幾部分組成的：

1、如果使用s1表作為驅(qū)動(dòng)表的話，總查詢成本由下邊幾個(gè)部分組成：

物化子查詢時(shí)需要的成本掃描s1表時(shí)的成本

s1表中的記錄數(shù)量 × 通過m_val = xxx對(duì)materialized_table表進(jìn)行單表訪問的成本（我們前邊說過物化表中的記錄是不重復(fù)的，并且為物化表中的列建立了索引，所以這個(gè)步驟顯然是非?？斓模?。

2、如果使用materialized_table表作為驅(qū)動(dòng)表的話，總查詢成本由下邊幾個(gè)部分組成：

物化子查詢時(shí)需要的成本掃描物化表時(shí)的成本

物化表中的記錄數(shù)量 × 通過order_note= xxx對(duì)s1表進(jìn)行單表訪問的成本（如果order_note列上建立了索引，這個(gè)步驟還是非常快的）。

MySQL查詢優(yōu)化器會(huì)通過運(yùn)算來選擇上述成本更低的方案來執(zhí)行查詢。

到此這篇關(guān)于MySQL的執(zhí)行原理之 MySQL的查詢重寫規(guī)則詳解的文章就介紹到這了,更多相關(guān)mysql查詢重寫規(guī)則內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！