具體細(xì)節(jié) 請(qǐng)去掘金購(gòu)買(mǎi)《MySQL 是怎樣運(yùn)行的：從根兒上理解 MySQL》

mysql會(huì)優(yōu)化我們的查詢條件進(jìn)行優(yōu)化赁遗，稱之為查詢重寫(xiě)

where子句必須放在group子句之前峭梳；而having子句必須在group子句之后

where子句只可以處理數(shù)據(jù)表中的數(shù)據(jù),having只能處理在group by子句中出現(xiàn)的字段样屠、select的列的字段或聚合函數(shù)處理過(guò)的列、外部查詢中的字段

mysql會(huì)化簡(jiǎn)我們的sql語(yǔ)句

• 1.移除不必要的括號(hào)
• 2.常量傳遞：a = 5 AND b > a--->a = 5 AND b > 5,用OR的時(shí)候這個(gè)條件就不能替換了
• 3.等值傳遞：a = b and b = c and c = 5 --->a = 5 and b = 5 and c = 5
• 4.移除沒(méi)用的條件:對(duì)于一些明顯永遠(yuǎn)為T(mén)RUE或者FALSE的表達(dá)式萝风，優(yōu)化器會(huì)移除掉它們
• 5.表達(dá)式計(jì)算:a = 5 + 1-->a=6,函數(shù)是不會(huì)優(yōu)化的。
• 6.HAVING子句和WHERE子句的合并：如果查詢語(yǔ)句中沒(méi)有出現(xiàn)諸如SUM、MAX等等的聚集函數(shù)以及GROUP BY子句顷扩，優(yōu)化器就把HAVING子句和WHERE子句合并起來(lái)。
• 7.常量表檢測(cè):查詢的表中一條記錄沒(méi)有慰毅，或者只有一條記錄(不適合innodb)和使用主鍵等值匹配或者唯一二級(jí)索引列等值匹配（常量）作為搜索條件來(lái)查詢某個(gè)表
  優(yōu)化器在分析查詢語(yǔ)句時(shí)候隘截，先執(zhí)行常量表查詢，然后把查詢中涉及到該表的條件全部替換成常數(shù)，最后再分析其余表的查詢成本
• 8.外連接消除--（通過(guò)外連接加一個(gè)where語(yǔ)句限制鏈接的記錄不為null婶芭，可以把外連接消除东臀，進(jìn)而優(yōu)化器根據(jù)查詢成本選擇合適的驅(qū)動(dòng)表）內(nèi)連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表的位置可以相互轉(zhuǎn)換，而左外和右外的驅(qū)動(dòng)表和被驅(qū)動(dòng)表是固定的
• 9.外連接和內(nèi)連接的本質(zhì)區(qū)別就是：對(duì)于外連接的驅(qū)動(dòng)表的記錄來(lái)說(shuō)犀农，如果無(wú)法在被驅(qū)動(dòng)表中找到匹配ON子句中的過(guò)濾條件的記錄惰赋，那么該記錄仍然會(huì)被加入到結(jié)果集中，對(duì)應(yīng)的被驅(qū)動(dòng)表記錄的各個(gè)字段使用NULL值填充呵哨；
  而內(nèi)連接的驅(qū)動(dòng)表的記錄如果無(wú)法在被驅(qū)動(dòng)表中找到匹配ON子句中的過(guò)濾條件的記錄赁濒，那么該記錄會(huì)被舍棄
• 10.子查詢優(yōu)化

子查詢的二三事

• 1.這種由子查詢結(jié)果集組成的表稱之為派生表。

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

• 1.標(biāo)量子查詢:只返回一個(gè)單一值的子查詢
• 2.行子查詢:就是返回一條記錄的子查詢孟害，不過(guò)這條記錄需要包含多個(gè)列（只包含一個(gè)列就成了標(biāo)量子查詢了）
• 3.列子查詢:列子查詢自然就是查詢出一個(gè)列的數(shù)據(jù)嘍拒炎，不過(guò)這個(gè)列的數(shù)據(jù)需要包含多條記錄（只包含一條記錄就成了標(biāo)量子查詢了）。
• 4.表子查詢:子查詢的結(jié)果既包含很多條記錄挨务，又包含很多個(gè)列

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

• 1.不相關(guān)子查詢:子查詢可以單獨(dú)運(yùn)行出結(jié)果击你，而不依賴于外層查詢的值
• 2.相關(guān)子查詢:查詢的執(zhí)行需要依賴于外層查詢的值

子查詢?cè)诓紶柋磉_(dá)式中的使用

• 1.操作數(shù) comparison_operator (子查詢):操作數(shù)可以是某個(gè)列名，或者是一個(gè)常量谎柄，或者是一個(gè)更復(fù)雜的表達(dá)式丁侄，甚至可以是另一個(gè)子查詢
  這里的子查詢只能是標(biāo)量子查詢或者行子查詢，也就是子查詢的結(jié)果只能返回一個(gè)單一的值或者只能是一條記錄

• 2.[NOT] IN/ANY/SOME/ALL子查詢

• 3.EXISTS子查詢

子查詢語(yǔ)法注意事項(xiàng)

• 1.子查詢必須用小括號(hào)擴(kuò)起來(lái)谷誓。
• 2.在SELECT子句中的子查詢必須是標(biāo)量子查詢绒障。
• 3.在想要得到標(biāo)量子查詢或者行子查詢，但又不能保證子查詢的結(jié)果集只有一條記錄時(shí)捍歪，應(yīng)該使用LIMIT 1語(yǔ)句來(lái)限制記錄數(shù)量户辱。
• 4.對(duì)于[NOT] IN/ANY/SOME/ALL子查詢來(lái)說(shuō)，子查詢中不允許有LIMIT語(yǔ)句糙臼。
• 5.ORDER BY子句無(wú)意義：子查詢的結(jié)果其實(shí)就相當(dāng)于一個(gè)集合庐镐，集合里的值排不排序一點(diǎn)兒都不重要
• 6.DISTINCT語(yǔ)句無(wú)意義：集合里的值去不去重也沒(méi)啥意義
• 7.在沒(méi)有聚集函數(shù)以及HAVING子句時(shí)，GROUP BY子句就是個(gè)擺設(shè)
• 8.不允許在一條語(yǔ)句中增刪改某個(gè)表的記錄時(shí)同時(shí)還對(duì)該表進(jìn)行子查詢

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

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

• 1.不相關(guān)標(biāo)量子查詢或者行子查詢來(lái)說(shuō),先執(zhí)行子查詢?cè)賵?zhí)行外層查詢必逆。
• 2.對(duì)于相關(guān)的標(biāo)量子查詢或者行子查詢來(lái)說(shuō)：先從外層查詢獲取一條記錄，然后從該記錄中找出子查詢涉及到的列
  揽乱，最后根據(jù)子查詢的查詢結(jié)果來(lái)檢測(cè)外層查詢WHERE子句的條件是否成立名眉，如果成立，就把外層查詢的那條記錄加入到結(jié)果集凰棉，否則就丟棄损拢。
• 3.再次執(zhí)行第一步，獲取第二條外層查詢中的記錄撒犀，依次類推～

IN子查詢優(yōu)化

• 1.對(duì)于不相關(guān)的IN子查詢:子查詢的結(jié)果集中的記錄條數(shù)很少福压，那么把子查詢和外層查詢分別看成兩個(gè)單獨(dú)的單表查詢
  如果子查詢的結(jié)果集的記錄條數(shù)很多掏秩，采用臨時(shí)表。
• 2.臨時(shí)表中的記錄會(huì)被去重荆姆，如果子查詢結(jié)果集不是大的離譜蒙幻，可以基于內(nèi)存的使用Memory存儲(chǔ)引擎的臨時(shí)表，而且會(huì)為該表建立哈希索引
• 3.一旦子查詢的結(jié)果集非常大胆筒，超過(guò)了系統(tǒng)變量tmp_table_size或者max_heap_table_size邮破，臨時(shí)表會(huì)轉(zhuǎn)而使用基于磁盤(pán)的存儲(chǔ)引擎來(lái)保存結(jié)果集中的記錄，索引類型也對(duì)應(yīng)轉(zhuǎn)變?yōu)锽+樹(shù)索引
• 4.MySQL把這個(gè)將子查詢結(jié)果集中的記錄保存到臨時(shí)表的過(guò)程稱之為物化
• 5.因此通過(guò)索引執(zhí)行IN語(yǔ)句判斷某個(gè)操作數(shù)在不在子查詢結(jié)果集中變得非称途龋快决乎，從而提升了子查詢語(yǔ)句的性能。

物化表轉(zhuǎn)連接

• 1.最終in查詢?cè)谧優(yōu)槲锘淼臅r(shí)候整個(gè)查詢就變?yōu)閮?nèi)連接

將子查詢轉(zhuǎn)換為semi-join（半連接）

• 1.可以能不進(jìn)行物化操作直接把子查詢轉(zhuǎn)換為連接
• 2.對(duì)于s1表的某條記錄來(lái)說(shuō)派桩，我們只關(guān)心在s2表中是否存在與之匹配的記錄是否存在构诚，而不關(guān)心具體有多少條記錄與之匹配，最終的結(jié)果集中只保留s1表的記錄
• 3.這樣的意思就是當(dāng)s1有記錄符合in中的子查詢即可铆惑，無(wú)需關(guān)心符合幾個(gè)in的參數(shù)范嘱。
• 4.SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
  可以轉(zhuǎn)變（不是真正的轉(zhuǎn)變，只是為了表達(dá)意思）SELECT s1.* FROM s1 SEMI JOIN s2
  ON s1.key1 = s2.common_field
  WHERE key3 = 'a';

實(shí)現(xiàn)子查詢的方式

Table pullout （子查詢中的表上拉）

• 1.當(dāng)子查詢的查詢列表處只有主鍵或者唯一索引列時(shí)员魏，可以直接把子查詢中的表上拉到外層查詢的FROM子句中丑蛤，并把子查詢中的搜索條件合并到外層查詢的搜索條件中
• 2.因?yàn)檫@個(gè)時(shí)候可以確保子查詢只有一條記錄符合要求，因此可以拿到外部

DuplicateWeedout execution strategy （重復(fù)值消除）

• 1.1表中的某條記錄可能在s2表中有多條匹配的記錄撕阎，所以該條記錄可能多次被添加到最后的結(jié)果集中受裹，為了消除重復(fù)，我們可以建立一個(gè)臨時(shí)表

LooseScan execution strategy （松散索引掃描）

• 1.SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');
• 2.在子查詢中虏束，對(duì)于s2表的訪問(wèn)可以使用到key1列的索引棉饶，而恰好子查詢的查詢列表處就是key1列，這樣在將該查詢轉(zhuǎn)換為半連接查詢后镇匀，可以將s2作為驅(qū)動(dòng)表執(zhí)行查詢的話
• 3.掃描索引照藻，但只取值相同的記錄的第一條去做匹配操作的方式稱之為松散索引掃描。

Semi-join Materialization execution strategy

• 1.我們之前介紹的先把外層查詢的IN子句中的不相關(guān)子查詢進(jìn)行物化汗侵，然后再進(jìn)行外層查詢的表和物化表的連接本質(zhì)上也算是一種semi-join幸缕，只不過(guò)由于物化表中沒(méi)有重復(fù)的記錄，所以可以直接將子查詢轉(zhuǎn)為連接查詢晰韵。

FirstMatch execution strategy （首次匹配）

• 1.先取一條外層查詢的中的記錄发乔，然后到子查詢的表中尋找符合匹配條件的記錄，如果能找到一條雪猪，則將該外層查詢的記錄放入最終的結(jié)果集并且停止查找更多匹配的記錄栏尚，如果找不到則把該外層查詢的記錄丟棄掉；然后再開(kāi)始取下一條外層查詢中的記錄浪蹂，重復(fù)上邊這個(gè)過(guò)程

如何使用半連接

• 1.原始語(yǔ)句：SELECT * FROM s1
  WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3);
• 2.修改后的語(yǔ)句：ELECT s1.* FROM s1 SEMI JOIN s2
  ON s1.key1 = s2.common_field AND s1.key3 = s2.key3;
• 3.這樣就可以使用我們上述說(shuō)道的幾種半連接查詢方式抵栈。
• 4.對(duì)于相關(guān)子查詢 其并不是一個(gè)獨(dú)立的查詢，所以不能轉(zhuǎn)換為物化表來(lái)執(zhí)行查詢坤次。

semi-join的適用條件

• 1.該子查詢必須是和IN語(yǔ)句組成的布爾表達(dá)式古劲，并且在外層查詢的WHERE或者ON子句中出現(xiàn)
• 2.外層查詢也可以有其他的搜索條件，只不過(guò)和IN子查詢的搜索條件必須使用AND連接起來(lái)缰猴。
• 3.該子查詢必須是一個(gè)單一的查詢产艾，不能是由若干查詢由UNION連接起來(lái)的形式。
• 4.該子查詢不能包含GROUP BY或者HAVING語(yǔ)句或者聚集函數(shù)滑绒。

不適用于semi-join的情況

• 1.外層查詢的WHERE條件中有其他搜索條件與IN子查詢組成的布爾表達(dá)式使用OR連接起來(lái)
• 2.使用NOT IN而不是IN的情況
• 3.在SELECT子句中的IN子查詢的情況
• 4.子查詢中包含GROUP BY闷堡、HAVING或者聚集函數(shù)的情況
• 4.子查詢中包含UNION的情況