六、查詢性能優(yōu)化

6.1 查詢?yōu)槭裁磿?huì)慢

將查詢看作一個(gè)任務(wù)，那么它由一系列子任務(wù)組成，實(shí)際我們所做的就是：

• 消除一些子任務(wù)
• 減少子任務(wù)的執(zhí)行次數(shù)
• 讓子任務(wù)運(yùn)行更快

6.2 慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問(wèn)

查詢性能低下最基本的原因是 訪問(wèn)的數(shù)據(jù)太多 。

某些查詢可能不可避免地需要篩選大量數(shù)據(jù)，但這并不常見(jiàn)。大部分性能低下的查詢都可以通過(guò)減少訪問(wèn)的數(shù)據(jù)量的方式進(jìn)行優(yōu)化。

1. 確認(rèn)應(yīng)用程序是否在檢索大量超過(guò)需要的數(shù)據(jù)。這通常意味著訪問(wèn)了太多的行，但有時(shí)候也可能是訪問(wèn)了太多的列。
2. 確認(rèn)MySQL服務(wù)器層是否在分析大量超過(guò)需要的數(shù)據(jù)行。

6.2.1 是否向數(shù)據(jù)庫(kù)請(qǐng)求了不需要的數(shù)據(jù)

查詢不需要的記錄

一個(gè)常見(jiàn)的錯(cuò)誤是常常會(huì)誤以為MySQL會(huì)只返回需要的數(shù)據(jù)，實(shí)際上MySQL卻是先返回全部結(jié)果集再進(jìn)行計(jì)算。

一些開(kāi)發(fā)者習(xí)慣使用這樣的技術(shù)，先使用SELECT語(yǔ)句查詢大量的結(jié)果，然后獲取前面的N行后關(guān)閉結(jié)果集(例如在新聞網(wǎng)站中取出100條記錄,但是只是在頁(yè)面上顯示前面10條)。他們認(rèn)為MySQL會(huì)執(zhí)行查詢，并只返回他們需要的10條數(shù)據(jù)，然后停止查詢。

實(shí)際情況是MySQL會(huì)查詢出全部的結(jié)果集，客戶端的應(yīng)用程序會(huì)接收全部的結(jié)果集數(shù)據(jù)，然后拋棄其中大部分?jǐn)?shù)據(jù)。最簡(jiǎn)單有效的解決方法就是在這樣的查詢后面加上LIMIT。

多表關(guān)聯(lián)時(shí)返回全部列

如果你想查詢所有在電影Academy Dinosaur中出現(xiàn)的演員，千萬(wàn)不要按下面的寫(xiě)法編寫(xiě)查詢:

mysql> SELECT * FROM sakila.actor
-> INNER JOIN sakila.film_actor USING(actor_id)
-> INNER JOIN sakila.film USING(film_id)
-> WHERE sakila.film.title = 'Academy Dinosaur';

這將返回這三個(gè)表的全部數(shù)據(jù)列。

正確的方式應(yīng)該是像下面這樣只取需要的列:

mysql> SELECT sakila.actor.* FROM sakila.actor...;

總是取出全部列

每次看到SELECT*的時(shí)候都需要用懷疑的眼光審視，是不是真的需要返回全部的列?很可能不是必需的。

取出全部列，會(huì)讓優(yōu)化器無(wú)法完成索引覆蓋掃描這類優(yōu)化，還會(huì)為服務(wù)器帶來(lái)額外的I/O、內(nèi)存和CPU的消耗。因此，一些DBA是嚴(yán)格禁止SELECT *的寫(xiě)法的，這樣做有時(shí)候還能避免某些列被修改帶來(lái)的問(wèn)題。

重復(fù)查詢相同的數(shù)據(jù)

如果你不太小心，很容易出現(xiàn)這樣的錯(cuò)誤一不斷地重復(fù)執(zhí)行相同的查詢，然后每次都返回完全相同的數(shù)據(jù)。

6.2.2 MySQL 是否在掃描額外的記錄

在確定查詢只返回需要的數(shù)據(jù)以后，接下來(lái)應(yīng)該看看查詢?yōu)榱朔祷亟Y(jié)果是否掃描了過(guò)多的數(shù)據(jù)。對(duì)于MySQL,最簡(jiǎn)單的衡量查詢開(kāi)銷的三個(gè)指標(biāo)如下:

• 響應(yīng)時(shí)間
• 掃描的行數(shù)
• 返回的行數(shù)

沒(méi)有哪個(gè)指標(biāo)能夠完美地衡量查詢的開(kāi)銷，但它們大致反映了MySQL在內(nèi)部執(zhí)行查詢時(shí)需要訪問(wèn)多少數(shù)據(jù)，并可以大概推算出查詢運(yùn)行的時(shí)間。這三個(gè)指標(biāo)都會(huì)記錄到MySQL的慢日志中，所以檢查慢日志記錄是找出掃描行數(shù)過(guò)多的查詢的好辦法。

響應(yīng)時(shí)間

$$響應(yīng)時(shí)間\{\begin{array}{c}服務(wù)時(shí)間：實(shí)際處理任務(wù)花費(fèi)時(shí)間\\ 排隊(duì)時(shí)間：等待任務(wù)開(kāi)始的時(shí)間\end{array}$$

掃描的行數(shù)與返回的行數(shù)

理想情況:掃描行數(shù)=返回行數(shù)，實(shí)際一般 掃描:返回 的值一般在 1:1~10:1。

掃描的行數(shù)與返回的類型

MySQL有好幾種訪問(wèn)方式可以查找并返回一行結(jié)果。有些訪問(wèn)方式可能需要掃描很多行才能返回一行結(jié)果，也有些訪問(wèn)方式可能無(wú)須掃描就能返回結(jié)果。

在EXPLAIN語(yǔ)句中的type列反應(yīng)了訪問(wèn)類型。訪問(wèn)類型有很多種，從全表掃描到：索引掃描、范圍掃描、唯一索引查詢、常數(shù)引用等。這里列的這些，速度是從慢到快，掃描的行數(shù)也是從小到大。

需要明白掃描表、掃描索引、范圍訪問(wèn)和單值訪問(wèn)的概念。

如果查詢沒(méi)有辦法找到合適的訪問(wèn)類型，那么解決的最好辦法通常就是增加一個(gè)合適的索引。

一般MySQL能夠使用如下三種方式應(yīng)用WHERE條件，從好到壞依次為:

• 在索引中使用WHERE條件來(lái)過(guò)濾不匹配的記錄。這是在存儲(chǔ)引擎層完成的。
• 使用索引覆蓋掃描(在Extra列中出現(xiàn)了Using index)來(lái)返回記錄，直接從索引中過(guò)濾不需要的記錄并返回命中的結(jié)果。這是在MySQL服務(wù)器層完成的，但無(wú)須再回表查詢記錄。
• 從數(shù)據(jù)表中返回?cái)?shù)據(jù)，然后過(guò)濾不滿足條件的記錄(在Extra列中出現(xiàn)Using Where)。這在MySQL服務(wù)器層完成，MySQL需要先從數(shù)據(jù)表讀出記錄然后過(guò)濾。

6.3 重構(gòu)查詢的方式

在優(yōu)化有問(wèn)題的查詢時(shí)，目標(biāo)應(yīng)該是找到一個(gè)更優(yōu)的方法獲得實(shí)際需要的結(jié)果一而不一定總是需要從MySQL獲取一模一樣的結(jié)果集。

6.3.1 一個(gè)復(fù)雜查詢還是多個(gè)簡(jiǎn)單查詢

MySQL從設(shè)計(jì)上讓連接和斷開(kāi)連接都很輕量級(jí)，在返回一個(gè)小的查詢結(jié)果方面很高效。現(xiàn)代的網(wǎng)絡(luò)速度比以前要快很多，運(yùn)行多個(gè)小查詢現(xiàn)在已經(jīng)不是大問(wèn)題了。

MySQL內(nèi)部每秒能夠掃描內(nèi)存中上百萬(wàn)行數(shù)據(jù)，相比之下，MySQL響應(yīng)數(shù)據(jù)給客戶端就慢得多了。在其他條件都相同的時(shí)候,使用盡可能少的查詢當(dāng)然是更好的。但是有時(shí)候，將一個(gè)大查詢分解為多個(gè)小查詢是很有必要的。

不過(guò)，在應(yīng)用設(shè)計(jì)的時(shí)候，如果一個(gè)查詢能夠勝任時(shí)還寫(xiě)成多個(gè)獨(dú)立查詢是不明智的。

6.3.2 切分查詢

有時(shí)候?qū)τ谝粋€(gè)大查詢我們需要“分而治之”，將大查詢切分成小查詢，每個(gè)查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結(jié)果。

刪除舊的數(shù)據(jù)就是一個(gè)很好的例子。定期地清除大量數(shù)據(jù)時(shí)，如果用一個(gè)大的語(yǔ)句一次性完成的話，則可能需要一次鎖住很多數(shù)據(jù)、占滿整個(gè)事務(wù)日志、耗盡系統(tǒng)資源、阻塞很多小的但重要的查詢。

將一個(gè)大的DELETE語(yǔ)句切分成多個(gè)較小的查詢可以盡可能小地影響MySQL性能，同時(shí)還可以減少M(fèi)ySQL復(fù)制的延遲。

6.3.3 分解關(guān)聯(lián)查詢

就是把一個(gè)復(fù)雜的關(guān)聯(lián)查詢，拆解到業(yè)務(wù)中去分開(kāi)查詢

用分解關(guān)聯(lián)查詢的方式重構(gòu)查詢有如下的優(yōu)勢(shì):

• 讓緩存的效率更高。許多應(yīng)用程序可以方便地緩存單表查詢對(duì)應(yīng)的結(jié)果對(duì)象。另外，對(duì)MySQL的查詢緩存來(lái)說(shuō),如果關(guān)聯(lián)中的某個(gè)表發(fā)生了變化，那么就無(wú)法使用查詢緩存了，而拆分后，如果某個(gè)表很少改變，那么基于該表的查詢就可以重復(fù)利用查詢緩存結(jié)果了。

• 將查詢分解后，執(zhí)行單個(gè)查詢可以減少鎖的競(jìng)爭(zhēng)。

• 在應(yīng)用層做關(guān)聯(lián)，可以更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，更容易做到高性能和可擴(kuò)展。查詢本身效率也可能會(huì)有所提升。這個(gè)例子中，使用IN()代替關(guān)聯(lián)查詢，可以讓MySQL按照ID順序進(jìn)行查詢，這可能比隨機(jī)的關(guān)聯(lián)要更高效。

• 可以減少冗余記錄的查詢。在應(yīng)用層做關(guān)聯(lián)查詢，意味著對(duì)于某條記錄應(yīng)用只需要查詢一次,而在數(shù)據(jù)庫(kù)中做關(guān)聯(lián)查詢,則可能需要重復(fù)地訪問(wèn)一部分?jǐn)?shù)據(jù)。從這點(diǎn)看,這樣的重構(gòu)還可能會(huì)減少網(wǎng)絡(luò)和內(nèi)存的消耗。

• 更進(jìn)一步，這樣做相當(dāng)于在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)，而不是使用MySQL的嵌套循環(huán)關(guān)聯(lián)。某些場(chǎng)景哈希關(guān)聯(lián)的效率要高很多。

6.4 查詢執(zhí)行的基礎(chǔ)

當(dāng)希望MySQL能夠以更高的性能運(yùn)行查詢時(shí)，最好的辦法就是弄清楚MySQL是如何優(yōu)化和執(zhí)行查詢的。

根據(jù)圖6-1，我們可以看到當(dāng)向MySQL發(fā)送一個(gè)請(qǐng)求的時(shí)候，MySQL到底做了些什么:

1. 客戶端發(fā)送一條查詢給服務(wù)器。
2. 服務(wù)器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲(chǔ)在緩存中的結(jié)果。否則進(jìn)入下一階段。
3. 服務(wù)器端進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。
4. MySQL 根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API來(lái)執(zhí)行查詢。
5. 將結(jié)果返回給客戶端。

6.4.1 MySQL 客戶端/服務(wù)器通信協(xié)議

MySQL客戶端和服務(wù)器之間的通信協(xié)議是“半雙工”的，在任何一個(gè)時(shí)刻，要么是由服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是由客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。所以，我們無(wú)法也無(wú)須將一個(gè)消息切成小塊獨(dú)立來(lái)
發(fā)送。

這種協(xié)議讓MySQL通信簡(jiǎn)單快速，但是也從很多地方限制了MySQL。一個(gè)明顯的限制是，這意味著沒(méi)法進(jìn)行流量控制。一旦一端開(kāi)始發(fā)生消息，另一端要接收完整個(gè)消息才能響應(yīng)它。

• 客戶端用一個(gè)單獨(dú)的數(shù)據(jù)包將查詢傳給服務(wù)器。這也是為什么當(dāng)查詢的語(yǔ)句很長(zhǎng)的時(shí)候，參數(shù)max_allowed_ packet 就特別重要了。一旦客戶端發(fā)送了請(qǐng)求，它能做的事情就只是等待結(jié)果了。

• 相反的，一般服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常很多，由多個(gè)數(shù)據(jù)包組成。當(dāng)服務(wù)器開(kāi)始響應(yīng)客戶端請(qǐng)求時(shí)，客戶端必須完整地接收整個(gè)返回結(jié)果，而不能簡(jiǎn)單地只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送數(shù)據(jù)。這種情況下，客戶端若接收完整的結(jié)果，然后取前面幾條需要的結(jié)果，或者接收完幾條結(jié)果后就“粗暴”地?cái)嚅_(kāi)連接，都不是好主意。這也是在必要的時(shí)候一定要在查詢中加上LIMIT限制的原因。

多數(shù)連接MySQL的庫(kù)函數(shù)都可以獲得全部結(jié)果集并緩存到內(nèi)存里，還可以逐行獲取需要的數(shù)據(jù)。默認(rèn)一般是獲得全部結(jié)果集并緩存到內(nèi)存中。

MySQL通常需要等所有的數(shù)據(jù)都已經(jīng)發(fā)送給客戶端才能釋放這條查詢所占用的資源，所以接收全部結(jié)果并緩存通常可以減少服務(wù)器的壓力，讓查詢能夠早點(diǎn)結(jié)束、早點(diǎn)釋放相應(yīng)的資源。

• 當(dāng)使用多數(shù)連接MySQL的庫(kù)函數(shù)從MySQL獲取數(shù)據(jù)時(shí)，其結(jié)果看起來(lái)都像是從MySQL服務(wù)器獲取數(shù)據(jù)，而實(shí)際上都是從這個(gè)庫(kù)函數(shù)的緩存獲取數(shù)據(jù)。
• 多數(shù)情況下這沒(méi)什么問(wèn)題，但是如果需要返回一個(gè)很大的結(jié)果集的時(shí)候，這樣做并不好，因?yàn)閹?kù)函數(shù)會(huì)花很多時(shí)間和內(nèi)存來(lái)存儲(chǔ)所有的結(jié)果集。如果能夠盡早開(kāi)始處理這些結(jié)果集，就能大大減少內(nèi)存的消耗，這種情況下可以不使用緩存來(lái)記錄結(jié)果而是直接處理。

這樣做的缺點(diǎn)是，對(duì)于服務(wù)器來(lái)說(shuō)，需要查詢完成后才能釋放資源，所以在和客戶端交互的整個(gè)過(guò)程中，服務(wù)器的資源都是被這個(gè)查詢所占用的。

我們看看當(dāng)使用PHP的時(shí)候是什么情況。首先，下面是我們連接MySQL的通常寫(xiě)法:

< ?php
$link = mysql_connect('localhost', 'user' , 'p4ssword');
$result = mysql_query('SELECT * FROM HUGE_TABLE', $link);
while($row = mysql_fetch_array($result)) {
	// Do something with result
}
?>

這段代碼看起來(lái)像是只有當(dāng)你需要的時(shí)候，才通過(guò)循環(huán)從服務(wù)器端取出數(shù)據(jù)。而實(shí)際上,在上面的代碼中，在調(diào)用mysql_query() 的時(shí)候，PHP就已經(jīng)將整個(gè)結(jié)果集緩存到內(nèi)存中。

下面的while循環(huán)只是從這個(gè)緩存中逐行取出數(shù)據(jù)，相反如果使用下面的查詢，用mysql_unbuffered_ query() 代替mysql_query(), PHP則不會(huì)緩存結(jié)果:

<?php
$link = mysql_connect('localhost', 'user' , 'password');
$result = mysql_unbuffered_query('SELECT * FROM HUGE_TABLE', $link);
while($row = mysql_fetch_array($result)) {
	// Do something with result
}
?>

mybatis的緩存使用-MyBatis的緩存機(jī)制

查詢狀態(tài)

對(duì)于一個(gè)MySQL連接，或者說(shuō)一個(gè)線程，任何時(shí)刻都有一個(gè)狀態(tài)，該狀態(tài)表示了MySQL當(dāng)前正在做什么。

有很多種方式能查看當(dāng)前的狀態(tài)，最簡(jiǎn)單的是使用

SHOW FULL PROCESSLIST

命令( 該命令返回結(jié)果中的Command列就表示當(dāng)前的狀態(tài))。在一個(gè)查詢的生命周期中，狀態(tài)會(huì)變化很多次。

• Sleep

線程正在等待客戶端發(fā)送新的請(qǐng)求。

• Query

線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端。

• Locked

在MySQL服務(wù)器層，該線程正在等待表鎖。

在存儲(chǔ)引擎級(jí)別實(shí)現(xiàn)的鎖，例如InnoDB的行鎖，并不會(huì)體現(xiàn)在線程狀態(tài)中。

對(duì)于MyISAM來(lái)說(shuō)這是一個(gè)比較典型的狀態(tài)，但在其他沒(méi)有行鎖的引擎中也經(jīng)常會(huì)出現(xiàn)。

• Analyzing and statistics

線程正在收集存儲(chǔ)引擎的統(tǒng)計(jì)信息，并生成查詢的執(zhí)行計(jì)劃。

• Copying to tmp table [on disk]

線程正在執(zhí)行查詢，并且將其結(jié)果集都復(fù)制到一個(gè)臨時(shí)表中，這種狀態(tài)一般要么是在做GROUP BY 操作，要么是文件排序操作，或者是UNION操作。

如果這個(gè)狀態(tài)后面還有“on disk”標(biāo)記，那表示MySQL正在將-一個(gè)內(nèi)存臨時(shí)表放到磁盤上。

• Sorting result

線程正在對(duì)結(jié)果集進(jìn)行排序。

• Sending data

這表示多種情況:線程可能在多個(gè)狀態(tài)之間傳送數(shù)據(jù)，或者在生成結(jié)果集，或者在向客戶端返回?cái)?shù)據(jù)。

6.4.2 查詢緩存

在解析一個(gè)查詢語(yǔ)句之前，如果查詢緩存是打開(kāi)的，那么MySQL會(huì)優(yōu)先檢查這個(gè)查詢是否命中查詢緩存中的數(shù)據(jù)。

這個(gè)檢查是通過(guò)一個(gè)對(duì)大小寫(xiě)敏感的哈希查找實(shí)現(xiàn)的。查詢和緩存中的查詢即使只有一個(gè)字節(jié)不同，那也不會(huì)匹配緩存結(jié)果,這種情況下查詢就會(huì)進(jìn)入下一階段的處理。

如果當(dāng)前的查詢恰好命中了查詢緩存，那么在返回查詢結(jié)果之前MySQL會(huì)檢查一次用戶權(quán)限。

這仍然是無(wú)須解析查詢SQL語(yǔ)句的，因?yàn)樵诓樵兙彺嬷幸呀?jīng)存放了當(dāng)前查詢需要訪問(wèn)的表信息。

如果權(quán)限沒(méi)有問(wèn)題，MySQL會(huì)跳過(guò)所有其他階段，直接從緩存中拿到結(jié)果并返回給客戶端。這種情況下，查詢不會(huì)被解析,不用生成執(zhí)行計(jì)劃，不會(huì)被執(zhí)行。

6.4.3 查詢優(yōu)化處理

查詢的生命周期的下一步是將一個(gè)SQL轉(zhuǎn)換成一個(gè)執(zhí)行計(jì)劃，MySQL再依照這個(gè)執(zhí)行計(jì)劃和存儲(chǔ)引擎進(jìn)行交互。

這包括多個(gè)子階段:解析SQL、預(yù)處理、優(yōu)化SQL執(zhí)行計(jì)劃。

這個(gè)過(guò)程中任何錯(cuò)誤(例如語(yǔ)法錯(cuò)誤)都可能終止查詢。

語(yǔ)法解析器和預(yù)處理

首先，MySQL通過(guò)關(guān)鍵字將SQL語(yǔ)句進(jìn)行解析，并生成一棵對(duì)應(yīng)的“解析樹(shù)”。

MySQL解析器將使用MySQL語(yǔ)法規(guī)則驗(yàn)證和解析查詢。

預(yù)處理器則根據(jù)一些MySQL規(guī)則進(jìn)一步檢查解析樹(shù)是否合法。

下一步預(yù)處理器會(huì)驗(yàn)證權(quán)限。這通常很快，除非服務(wù)器上有非常多的權(quán)限配置。

查詢優(yōu)化器

一條查詢可以有很多種執(zhí)行方式，最后都返回相同的結(jié)果。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計(jì)劃。

MySQL使用基于成本的優(yōu)化器，它將嘗試預(yù)測(cè)一個(gè)查詢使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最小的一個(gè)。

最初，成本的最小單位是隨機(jī)讀取一個(gè)4K數(shù)據(jù)頁(yè)的成本，后來(lái)(成本計(jì)算公式)變得更加復(fù)雜，并且引入了一些“因子”來(lái)估算某些操作的代價(jià),如當(dāng)執(zhí)行一次 WHERE條件比較的成本。

可以通過(guò)查詢當(dāng)前會(huì)話的Last_query_cost的值來(lái)得知MySQL計(jì)算的當(dāng)前查詢的成本。

有很多種原因會(huì)導(dǎo)致MySQL優(yōu)化器選擇錯(cuò)誤的執(zhí)行計(jì)劃，如下所示:

• 統(tǒng)計(jì)信息不準(zhǔn)確。MySQL依賴存儲(chǔ)引擎提供的統(tǒng)計(jì)信息來(lái)評(píng)估成本，但是有的存儲(chǔ)引擎提供的信息是準(zhǔn)確的，有的偏差可能非常大。

例如，InnoDB因?yàn)槠銶VCC的架構(gòu)，并不能維護(hù)一個(gè)數(shù)據(jù)表的行數(shù)的精確統(tǒng)計(jì)信息。

• 執(zhí)行計(jì)劃中的成本估算不等同于實(shí)際執(zhí)行的成本。所以即使統(tǒng)計(jì)信息精準(zhǔn)，優(yōu)化器給出的執(zhí)行計(jì)劃也可能不是最優(yōu)的。

例如有時(shí)候某個(gè)執(zhí)行計(jì)劃雖然需要讀取更多的頁(yè)面，但是它的成本卻更小。因?yàn)槿绻@些頁(yè)面都是順序讀或者這些頁(yè)面都已經(jīng)在內(nèi)存中的話，那么它的訪問(wèn)成本將很小。MySQL層面并不知道哪些頁(yè)面在內(nèi)存中、哪些在磁盤上，所以查詢實(shí)際執(zhí)行過(guò)程中到底需要多少次物理I/O是無(wú)法得知的。

• MySQL的最優(yōu)可能和你想的最優(yōu)不-一樣。你可能希望執(zhí)行時(shí)間盡可能的短，但是MySQL只是基于其成本模型選擇最優(yōu)的執(zhí)行計(jì)劃，而有些時(shí)候這并不是最快的執(zhí)行方式。所以，這里我們看到根據(jù)執(zhí)行成本來(lái)選擇執(zhí)行計(jì)劃并不是完美的模型。
• MySQL從不考慮其他并發(fā)執(zhí)行的查詢，這可能會(huì)影響到當(dāng)前查詢的速度。
• MySQL也并不是任何時(shí)候都是基于成本的優(yōu)化。有時(shí)也會(huì)基于一些固定的規(guī)則

例如，如果存在全文搜索的MATCH()子句，則在存在全文索引的時(shí)候就使用全文索引。即使有時(shí)候使用別的索引和WHERE條件可以遠(yuǎn)比這種方式要快，MySQL 也仍然會(huì)使用對(duì)應(yīng)的全文索引。

• MySQL不會(huì)考慮不受其控制的操作的成本，例如執(zhí)行存儲(chǔ)過(guò)程或者用戶自定義函數(shù)的成本。
• 優(yōu)化器有時(shí)候無(wú)法去估算所有可能的執(zhí)行計(jì)劃，所以它可能錯(cuò)過(guò)實(shí)際上最優(yōu)的執(zhí)行計(jì)劃。

MySQL的查詢優(yōu)化器是一個(gè)非常復(fù)雜的部件，它使用了很多優(yōu)化策略來(lái)生成一個(gè)最優(yōu)的執(zhí)行計(jì)劃。

優(yōu)化策略可以簡(jiǎn)單地分為兩種：

• 一種是靜態(tài)優(yōu)化：靜態(tài)優(yōu)化可以直接對(duì)解析樹(shù)進(jìn)行分析，并完成優(yōu)化。
  • 例如，優(yōu)化器可以通過(guò)一些簡(jiǎn)單的代數(shù)變換將WHERE條件轉(zhuǎn)換成另一種等價(jià)形式。
  • 靜態(tài)優(yōu)化不依賴于特別的數(shù)值，如WHERE條件中帶入的一些常數(shù)等。靜態(tài)優(yōu)化在第一次完成后就一直有效，即使使用不同的參數(shù)重復(fù)執(zhí)行查詢也不會(huì)發(fā)生變化。
  • 可以認(rèn)為這是一種“編譯時(shí)優(yōu)化”。
• 一種是動(dòng)態(tài)優(yōu)化：動(dòng)態(tài)優(yōu)化則和查詢的上下文有關(guān)，也可能和很多其他因素有關(guān)。
  • 例如WHERE條件中的取值、索引中條目對(duì)應(yīng)的數(shù)據(jù)行數(shù)等。
  • 這需要在每次查詢的時(shí)候都重新評(píng)估，可以認(rèn)為這是“運(yùn)行時(shí)優(yōu)化”。

在執(zhí)行語(yǔ)句和存儲(chǔ)過(guò)程的時(shí)候，動(dòng)態(tài)優(yōu)化和靜態(tài)優(yōu)化的區(qū)別非常重要。

MySQL對(duì)查詢的靜態(tài)優(yōu)化只需要做一次，但對(duì)查詢的動(dòng)態(tài)優(yōu)化則在每次執(zhí)行時(shí)都需要重新評(píng)估。有時(shí)候甚至在查詢的執(zhí)行過(guò)程中也會(huì)重新優(yōu)化。

下面是一些MySQL能夠處理的優(yōu)化類型:

• 重新定義關(guān)聯(lián)表的順序

數(shù)據(jù)表的關(guān)聯(lián)并不總是按照在查詢中指定的順序進(jìn)行。決定關(guān)聯(lián)的順序是優(yōu)化器很重要的一部分功能。

• 將外連接轉(zhuǎn)化成內(nèi)連接

并不是所有的 OUTER JOIN 語(yǔ)句都必須以外連接的方式執(zhí)行。

諸多因素，例如WHERE條件、庫(kù)表結(jié)構(gòu)都可能會(huì)讓外連接等價(jià)于一個(gè)內(nèi)連接。MySQL能夠識(shí)別這點(diǎn)并重寫(xiě)查詢，讓其可以調(diào)整關(guān)聯(lián)順序。

• 使用等價(jià)變換規(guī)則

MySQL可以使用一些等價(jià)變換來(lái)簡(jiǎn)化并規(guī)范表達(dá)式。它可以合并和減少一些比較,還可以移除一些恒成立和一些恒不成立的判斷。例如，(5=5 AND a>5) 將被改寫(xiě)為a>5。類似的，如果有(a<b AND b=c) AND a=5 則會(huì)改寫(xiě)為b>5 AND b=c AND a=5。

這些規(guī)則對(duì)于我們編寫(xiě)條件語(yǔ)句很有用，我們將在本章后續(xù)繼續(xù)討論。

• 優(yōu)化COUNT()、 MIN() 和MAX()

索引和列是否可為空通?？梢詭椭鶰ySQL優(yōu)化這類表達(dá)式。

例如，要找到某一列的最小值，只需要查詢對(duì)應(yīng)B-Tree索引最左端的記錄，MySQL可以直接獲取索引的第一行記錄。在優(yōu)化器生成執(zhí)行計(jì)劃的時(shí)候就可以利用這一點(diǎn),在B-Tree索引中，優(yōu)化器會(huì)將這個(gè)表達(dá)式作為一個(gè)常數(shù)對(duì)待。

類似的,如果要查找一個(gè)最大值，也只需讀取B-Tree索引的最后一條記錄。

如果MySQL使用了這種類型的優(yōu)化，那么在EXPLAIN中就可以看到“ Select tables optimized away”。從字面意思可以看出，它表示優(yōu)化器已經(jīng)從執(zhí)行計(jì)劃中移除了該表，并以一個(gè)常數(shù)取而代之。

類似的，沒(méi)有任何WHERE條件的COUNT(*)查詢通常也可以使用存儲(chǔ)引擎提供的一些優(yōu)化(例如，MyISAM維護(hù)了一個(gè)變量來(lái)存放數(shù)據(jù)表的行數(shù))。

• 預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式

當(dāng)MySQL檢測(cè)到一個(gè)表達(dá)式可以轉(zhuǎn)化為常數(shù)的時(shí)候，就會(huì)一直把該表達(dá)式作為常數(shù)進(jìn)行優(yōu)化處理。

例如，一個(gè)用戶自定義變量在查詢中沒(méi)有發(fā)生變化時(shí)就可以轉(zhuǎn)換為一個(gè)常數(shù)。

數(shù)學(xué)表達(dá)式則是另一種典型的例子。在優(yōu)化階段，有時(shí)候甚至一個(gè)查詢也能夠轉(zhuǎn)化為一個(gè)常數(shù)。

一個(gè)例子是在索引列上執(zhí)行MIN()函數(shù)。甚至是主鍵或者唯一鍵查找語(yǔ)句也可以轉(zhuǎn)換為常數(shù)表達(dá)式。

如果WHERE子句中使用了該類索引的常數(shù)條件，MySQL可以在查詢開(kāi)始階段就先查找到這些值，這樣優(yōu)化器就能夠知道并轉(zhuǎn)換為常數(shù)表達(dá)式。

下面是一個(gè)例子:

mysql> EXPLAIN SELECT film.film_id,film_actor.actor_id
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE film.film_id = 1;

MySQL分兩步來(lái)執(zhí)行這個(gè)查詢，也就是上面執(zhí)行計(jì)劃的兩行輸出。

• 第一步先從film表找到需要的行。因?yàn)樵趂ilm_id 字段上有主鍵索引，所以MySQL優(yōu)化器知道這只會(huì)返回一行數(shù)據(jù)，優(yōu)化器在生成執(zhí)行計(jì)劃的時(shí)候，就已經(jīng)通過(guò)索引信息知道將返回多少行數(shù)據(jù)。因?yàn)閮?yōu)化器已經(jīng)明確知道有多少個(gè)值(WHERE條件中的值)需要做索引查詢，所以這里的表訪問(wèn)類型是const。

• 在執(zhí)行計(jì)劃的第二步，MySQL將第一步中返回的film_id列當(dāng)作一個(gè)已知取值的列來(lái)處理。因?yàn)閮?yōu)化器清楚在第一步執(zhí)行完成后，該值就會(huì)是明確的了。注意到正如第一步中一樣，使用film_actor字段對(duì)表的訪問(wèn)類型也是const。

另一種會(huì)看到常數(shù)條件的情況是通過(guò)等式將常數(shù)值從一個(gè)表傳到另一個(gè)表，這可以通過(guò)WHERE、USING 或者ON語(yǔ)句來(lái)限制某列取值為常數(shù)。

在上面的例子中，因?yàn)槭褂昧薝SING子句，優(yōu)化器知道這也限制了film_id在整個(gè)查詢過(guò)程中都始終是一個(gè)常量——因?yàn)樗仨毜?于WHERE子句中的那個(gè)取值。

• 覆蓋索引掃描

當(dāng)索引中的列包含所有查詢中需要使用的列的時(shí)候，MySQL就可以使用索引返回需要的數(shù)據(jù)，而無(wú)須查詢對(duì)應(yīng)的數(shù)據(jù)行。

• 子查詢優(yōu)化

MySQL在某些情況下可以將子查詢轉(zhuǎn)換-種效率更高的形式，從而減少多個(gè)查詢多次對(duì)數(shù)據(jù)進(jìn)行訪問(wèn)。

• 提前終止查詢

在發(fā)現(xiàn)已經(jīng)滿足查詢需求的時(shí)候，MySQL總是能夠立刻終止查詢。-一個(gè)典型的例子就是當(dāng)使用了LIMIT子句的時(shí)候。

除此之外，MySQL還有幾類情況也會(huì)提前終止查詢，例如發(fā)現(xiàn)了一個(gè)不成立的條件，這時(shí)MySQL可以立刻返回一個(gè)空結(jié)果。

從下面的例子可以看到這一點(diǎn):

從這個(gè)例子看到查詢?cè)趦?yōu)化階段就已經(jīng)終止。

除此之外，MySQL在執(zhí)行過(guò)程中，如果發(fā)現(xiàn)某些特殊的條件，則會(huì)提前終止查詢。

當(dāng)存儲(chǔ)引擎需要檢索“不同取值”或者判斷存在性的時(shí)候，MySQL都可以使用這類優(yōu)化。例如，我們現(xiàn)在需要找到?jīng)]有演員的所有電影。

• 等值傳播

如果兩個(gè)列的值通過(guò)等式關(guān)聯(lián)，那么MySQL能夠把其中一個(gè)列的WHERE條件傳遞到另一列上。

例如，我們看下面的查詢:

mysql> SELECT film.film_id
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(fi1m_ id)
-> WHERE film.film_ id > 500;

因?yàn)檫@里使用了film_ id字段進(jìn)行等值關(guān)聯(lián)，MySQL知道這里的WHERE子句不僅適用于film表,而且對(duì)于film_actor表同樣適用。

如果使用的是其他的數(shù)據(jù)庫(kù)管理系統(tǒng)，可能還需要手動(dòng)通過(guò)一些條件來(lái)告知優(yōu)化器這個(gè)WHERE條件適用于兩個(gè)表，那么寫(xiě)法就會(huì)如下:

... WHERE film.film_id > 500 AND film_actor.film_id > 500

在MySQL中不需要。

• 列表IN()的比較

在很多數(shù)據(jù)庫(kù)系統(tǒng)中，IN() 完全等同于多個(gè)OR條件的子句，因?yàn)檫@兩者是完全等價(jià)的。

在MySQL中這點(diǎn)是不成立的，MySQL將IN()列表中的數(shù)據(jù)先進(jìn)行排序，然后通過(guò)二分查找的方式來(lái)確定列表中的值是否滿足條件，這是一個(gè)O(log n)復(fù)雜度的操作，等價(jià)地轉(zhuǎn)換成OR查詢的復(fù)雜度為O(n)，對(duì)于IN()列表中有大量取值的時(shí)候，MySQL的處理速度將會(huì)更快。

數(shù)據(jù)和索引的統(tǒng)計(jì)信息

MySQL架構(gòu)由多個(gè)層次組成。在服務(wù)器層有查詢優(yōu)化器，卻沒(méi)有保存數(shù)據(jù)和索引的統(tǒng)計(jì)信息。

統(tǒng)計(jì)信息由存儲(chǔ)引擎實(shí)現(xiàn)，不同的存儲(chǔ)引擎可能會(huì)存儲(chǔ)不同的統(tǒng)計(jì)信息(也可以按照不同的格式存儲(chǔ)統(tǒng)計(jì)信息)。

某些引擎,例如Archive引擎,則根本就沒(méi)有存儲(chǔ)任何統(tǒng)計(jì)信息.

因?yàn)榉?wù)器層沒(méi)有任何統(tǒng)計(jì)信息，所以MySQL查詢優(yōu)化器在生成查詢的執(zhí)行計(jì)劃時(shí)，需要向存儲(chǔ)引擎獲取相應(yīng)的統(tǒng)計(jì)信息。

存儲(chǔ)引擎則提供給優(yōu)化器對(duì)應(yīng)的統(tǒng)計(jì)信息，包括:

• 每個(gè)表或者索引有多少個(gè)頁(yè)面
• 每個(gè)表的每個(gè)索引的基數(shù)是多少
• 數(shù)據(jù)行和索引長(zhǎng)度
• 索引的分布信息
• … …

優(yōu)化器根據(jù)這些信息來(lái)選擇一個(gè)最優(yōu)的執(zhí)行計(jì)劃。

MySQL如何執(zhí)行關(guān)聯(lián)查詢

MySQL中“關(guān)聯(lián)”一詞所包含的意義比一般意義上理解的要更廣泛?？偟膩?lái)說(shuō)，MySQL認(rèn)為任何一個(gè)查詢都是一次“關(guān)聯(lián)”一并不僅僅是一個(gè) 查詢需要到兩個(gè)表匹配才叫關(guān)聯(lián)，所以在MySQL中，每一個(gè)查詢，每一個(gè)片段(包括子查詢，甚至基于單表的SELECT)都可能是關(guān)聯(lián)。

對(duì)于UNION查詢，MySQL先將一系列的 單個(gè)查詢結(jié)果放到一個(gè)臨時(shí)表中，然后再重新讀出臨時(shí)表數(shù)據(jù)來(lái)完成UNION查詢。在MySQL的概念中，每個(gè)查詢都是一次關(guān)聯(lián)，所以讀取結(jié)果臨時(shí)表也是一次關(guān)聯(lián)。

當(dāng)前MySQL關(guān)聯(lián)執(zhí)行的策略很簡(jiǎn)單:

MySQL對(duì)任何關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作，即MySQL先在一個(gè)表中循環(huán)取出單條數(shù)據(jù)，然后再嵌套循環(huán)到下一個(gè)表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止。

然后根據(jù)各個(gè)表匹配的行，返回查詢中需要的各個(gè)列。

MySQL會(huì)嘗試在最后一個(gè)關(guān)聯(lián)表中找到所有匹配的行，如果最后一個(gè)關(guān)聯(lián)表無(wú)法找到更多的行以后，MySQL返回到上一層次關(guān)聯(lián)表，看是否能夠找到更多的匹配記錄，依此類推迭代執(zhí)行。

按照這樣的方式查找第一個(gè)表記錄，再嵌套查詢下一個(gè)關(guān)聯(lián)表，然后回溯到上一個(gè)表，在MySQL中是通過(guò)嵌套循環(huán)的方式實(shí)現(xiàn)一正如其名“嵌套循環(huán)關(guān)聯(lián)”。

如下例子中的簡(jiǎn)單查詢:

mysql> SELECT tbl1.co1, tbl2.col2
-> FROM tbl1 INNER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6);

假設(shè)MySQL按照查詢中的表順序進(jìn)行關(guān)聯(lián)操作，我們則可以用下面的偽代碼表示MySQL將如何完成這個(gè)查詢:

outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
	inner_iter = iterator over tbl2 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

上面的執(zhí)行計(jì)劃對(duì)于單表查詢和多表關(guān)聯(lián)查詢都適用，如果是一個(gè)單表查詢，那么只需完成上面外層的基本操作。

對(duì)于外連接上面的執(zhí)行過(guò)程仍然適用。

例如，我們將上面查詢修改如下:

mysql> SELECT tbl1.col1, tbl2.col2
-> FROM tbl1 LEFT OUTER JOIN tbl2 USING(col3)
-> WHERE tbl1.col1 IN(5,6);

對(duì)應(yīng)的偽代碼如下:

outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_ row = outer_ iter.next
while outer_ row
	inner_ iter = iterator over tbl2 where col3 = outer_ row.col3
	inner_ row = inner_ iter.next
	if inner_ row
		while 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

或者如下6-2“泳道圖”

從本質(zhì)上說(shuō)，MySQL對(duì)所有的類型的查詢都以同樣的方式運(yùn)行。例如，MySQL在FROM子句中遇到子查詢時(shí)，先執(zhí)行子查詢并將其結(jié)果放到一個(gè)臨時(shí)表中,然后將這個(gè)臨時(shí)表當(dāng)作一個(gè)普通表對(duì)待(正如其名‘派生表”)。 MySQL在執(zhí)行UNION查詢時(shí)也使用類似的臨時(shí)表，在遇到右外連接的時(shí)候，MySQL將其改寫(xiě)成等價(jià)的左外連接。

簡(jiǎn)而言之，當(dāng)前版本的MySQL會(huì)將所有的查詢類型都轉(zhuǎn)換成類似的執(zhí)行計(jì)劃。

不過(guò)，不是所有的查詢都可以轉(zhuǎn)換成上面的形式。

例如，全外連接就無(wú)法通過(guò)嵌套循環(huán)和回溯的方式完成，這時(shí)當(dāng)發(fā)現(xiàn)關(guān)聯(lián)表中沒(méi)有找到任何匹配行的時(shí)候，則可能是因?yàn)殛P(guān)聯(lián)是恰好從一個(gè)沒(méi)有任何匹配的表開(kāi)始。這大概也是MySQL并不支持全外連接的原因。

還有些場(chǎng)景，雖然可以轉(zhuǎn)換成嵌套循環(huán)的方式，但是效率卻非常差。

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

和很多其他關(guān)系數(shù)據(jù)庫(kù)不同，MySQL并不會(huì)生成查詢字節(jié)碼來(lái)執(zhí)行查詢。MySQL生成查詢的一棵指令樹(shù)，然后通過(guò)存儲(chǔ)引擎執(zhí)行完成這棵指令樹(shù)并返回結(jié)果。最終的執(zhí)行計(jì)劃包含了重構(gòu)查詢的全部信息。

如果對(duì)某個(gè)查詢執(zhí)行EXPLAIN EXTENDED 后，再執(zhí)行SHOWWARNINGS，就可以看到重構(gòu)出的查詢。

任何多表查詢都可以使用一棵樹(shù)表示，例如，可以按照?qǐng)D6-3執(zhí)行一個(gè)四表的關(guān)聯(lián)操作。

在計(jì)算機(jī)科學(xué)中，這被稱為一顆平衡樹(shù)。但是，這并不是MySQL執(zhí)行查詢的方式。

MySQL總是從一個(gè)表開(kāi)始一直嵌套循環(huán)、回溯完成所有表關(guān)聯(lián)。所以，MySQL的執(zhí)行計(jì)劃總是如圖6-4所示，是一棵左測(cè)深度優(yōu)先的樹(shù)。

關(guān)聯(lián)查詢優(yōu)化器

MySQL優(yōu)化器最重要的一部分就是關(guān)聯(lián)查詢優(yōu)化，它決定了多個(gè)表關(guān)聯(lián)時(shí)的順序。

通常多表關(guān)聯(lián)的時(shí)候，可以有多種不同的關(guān)聯(lián)順序來(lái)獲得相同的執(zhí)行結(jié)果。聯(lián)查詢優(yōu)化器則通過(guò)評(píng)估不同順序時(shí)的成本來(lái)選擇一個(gè)代價(jià)最小的關(guān)聯(lián)順序。

下面的查詢可以通過(guò)不同順序的關(guān)聯(lián)最后都獲得相同的結(jié)果:

mysql> SELECT film.film_id,film.title,film.release_year,actor.actor.id,
actor.first_ name,actor.last_ name
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_ id)
-> INNER JOIN sakila.actor USING(actor_ id);

容易看出，可以通過(guò)一些不同的執(zhí)行計(jì)劃來(lái)完成上面的查詢。

例如，MySQL可以從film表開(kāi)始,使用film actor表的索引film id來(lái)查找對(duì)應(yīng)的actor. id值,然后再根據(jù)actor表的主鍵找到對(duì)應(yīng)的記錄。Oracle 用戶會(huì)用下面的術(shù)語(yǔ)描述: “film表作為驅(qū)動(dòng)表先查找fle_ actor 表,然后以此結(jié)果為驅(qū)動(dòng)表再查找actor表”。

這樣做效率應(yīng)該會(huì)不錯(cuò)，我們?cè)偈褂肊XPLAIN看看MySQL將如何執(zhí)行這個(gè)查詢:

與我們假設(shè)的不同。

我們先使用STRAIGHT_J0IN關(guān)鍵字，按照之前的順序執(zhí)行

MySQL基礎(chǔ)之STRAIGHT JOIN用法簡(jiǎn)介

這里是對(duì)應(yīng)的EXPLAIN輸出結(jié)果:

我們來(lái)分析一下為什么MySQL會(huì)將關(guān)聯(lián)順序倒轉(zhuǎn)過(guò)來(lái):可以看到，關(guān)聯(lián)順序倒轉(zhuǎn)后的第一個(gè)關(guān)聯(lián)表只需要掃描很少的行數(shù)。在

兩種關(guān)聯(lián)順序下，第二個(gè)和第三個(gè)關(guān)聯(lián)表都是根據(jù)索引查詢，速度都很快，不同的是需要掃描的索引項(xiàng)的數(shù)量是不同的:

• 將film表作為第一個(gè)關(guān)聯(lián)表時(shí)，會(huì)找到951條記錄，然后對(duì)film_ actor和actor表進(jìn)行嵌套循環(huán)查詢。
• 如果MySQL選擇首先掃描actor表，只會(huì)返回200條記錄進(jìn)行后面的嵌套循環(huán)查詢。

換句話說(shuō)，倒轉(zhuǎn)的關(guān)聯(lián)順序會(huì)讓查詢進(jìn)行更少的嵌套循環(huán)和回溯操作。為了驗(yàn)證優(yōu)化器的選擇是否正確，我們單獨(dú)執(zhí)行這兩個(gè)查詢，并且看看對(duì)應(yīng)的Last_ query_cost狀態(tài)值。我們看到倒轉(zhuǎn)的關(guān)聯(lián)順序的預(yù)估成本為241，而原來(lái)的查詢的預(yù)估成本為1154。

關(guān)聯(lián)優(yōu)化器會(huì)嘗試在所有的關(guān)聯(lián)順序中選擇一個(gè)成本最小的來(lái)生成執(zhí)行計(jì)劃樹(shù)。如果可能，優(yōu)化器會(huì)遍歷每一個(gè)表然后逐個(gè)做嵌套循環(huán)計(jì)算每–棵可能的執(zhí)行計(jì)劃樹(shù)的成本，最后返回一個(gè)最優(yōu)的執(zhí)行計(jì)劃。

不過(guò)，如果有超過(guò)n個(gè)表的關(guān)聯(lián)，那么需要檢查n的階乘種關(guān)聯(lián)順序。我們稱之為所有可能的執(zhí)行計(jì)劃的“搜索空間”,搜索空間的增長(zhǎng)速度非?？?。當(dāng)搜索空間非常大的時(shí)候，優(yōu)化器選擇使用“貪婪”搜索的方式查找“ 最優(yōu)”的關(guān)聯(lián)順序。

實(shí)際上，當(dāng)需要關(guān)聯(lián)的表超過(guò)optimizer_ search_depth的限制的時(shí)候，就會(huì)選擇“ 貪婪”搜索模式了(optimizer_ search_ depth 參數(shù)可以根據(jù)需要指定大小)。

排序優(yōu)化

無(wú)論如何排序都是一個(gè)成本很高的操作，所以從性能角度考慮，應(yīng)盡可能避免排序或者盡可能避免對(duì)大量數(shù)據(jù)進(jìn)行排序。

無(wú)法使用索引排序的時(shí)候，MySQL 需要自己進(jìn)行排序，如果數(shù)據(jù)量小則在內(nèi)存中進(jìn)行，如果數(shù)據(jù)量大則需要使用磁盤，不過(guò)MySQL將這個(gè)過(guò)程統(tǒng)一稱為文件排序(filesort)， 即使完全是內(nèi)存排序不需要任何磁盤文件時(shí)也是如此。

如果需要排序的數(shù)據(jù)量小于“排序緩沖區(qū)”，MySQL使用內(nèi)存進(jìn)行“快速排序”操作。如果內(nèi)存不夠排序，那么MySQL會(huì)先將數(shù)據(jù)分塊，對(duì)每個(gè)獨(dú)立的塊使用“ 快速排序”進(jìn)行排序，并將各個(gè)塊的排序結(jié)果存放在磁盤上，然后將各個(gè)排好序的塊進(jìn)行 合并(merge) ，最后返回排序結(jié)果。

MySQL有如下兩種排序算法

• 兩次傳輸排序(舊版本使用)

讀取行指針和需要排序的字段，對(duì)其進(jìn)行排序，然后再根據(jù)排序結(jié)果讀取所需要的數(shù)據(jù)行。

這需要進(jìn)行兩次數(shù)據(jù)傳輸，即需要從數(shù)據(jù)表中讀取兩次數(shù)據(jù)，第二次讀取數(shù)據(jù)的時(shí)候，因?yàn)槭亲x取排序列進(jìn)行排序后的所有記錄，這會(huì)產(chǎn)生大量的隨機(jī)I/O，所以兩次數(shù)據(jù)傳輸?shù)某杀痉浅８?。?dāng)使用的是MyISAM表的時(shí)候，成本可能會(huì)更高，因?yàn)镸yISAM使用系統(tǒng)調(diào)用進(jìn)行數(shù)據(jù)的讀取(MyISAM非常依賴操作系統(tǒng)對(duì)數(shù)據(jù)的緩存)。

不過(guò)這樣做的優(yōu)點(diǎn)是，在排序的時(shí)候存儲(chǔ)盡可能少的數(shù)據(jù)，這就讓“排序緩沖區(qū)”中可能容納盡可能多的行數(shù)進(jìn)行排序。

• 單次傳輸排序(新版本使用)

先讀取查詢所需要的所有列，然后再根據(jù)給定列進(jìn)行排序，最后直接返回排序結(jié)果。

這個(gè)算法只在MySQL 4.1和后續(xù)更新的版本才引入。因?yàn)椴辉傩枰獜臄?shù)據(jù)表中讀取兩次數(shù)據(jù),對(duì)于I/O密集型的應(yīng)用,這樣做的效率高了很多。另外,相比兩次傳輸排序,這個(gè)算法只需要一次順序I/O讀取所有的數(shù)據(jù)，而無(wú)須任何的隨機(jī)I/O。

缺點(diǎn)是，如果需要返回的列非常多、非常大，會(huì)額外占用大量的空間，而這些列對(duì)排序操作本身來(lái)說(shuō)是沒(méi)有任何作用的。因?yàn)閱螚l排序記錄很大,所以可能會(huì)有更多的排序塊需要合并。

兩種算法都有各自最好和最糟的場(chǎng)景。當(dāng)查詢需要所有列的總長(zhǎng)度不超過(guò)參數(shù)max_ length_ for_ sort_data 時(shí)，MySQL使用“單次傳輸排序”，可以通過(guò)調(diào)整這個(gè)參數(shù)來(lái)影響MySQL排序算法的選擇。

MySQL文件排序使用的臨時(shí)空間可能比較多。MySQL在進(jìn)行文件排序的時(shí)候需要使用的臨時(shí)存儲(chǔ)空間可能會(huì)比想象的要大得多。原因在于MySQL在排序時(shí)，對(duì)每一個(gè)排序記錄都會(huì)分配一個(gè)足夠長(zhǎng)的定長(zhǎng)空間來(lái)存放。

這個(gè)定長(zhǎng)空間必須足夠長(zhǎng)以容納其中 最長(zhǎng)的字符串 ，例如，如果是VARCHAR列則需要分配其完整長(zhǎng)度;如果使用UTF-8字符集，那么MySQL將會(huì)為每個(gè)字符預(yù)留三個(gè)字節(jié)。

在關(guān)聯(lián)查詢的時(shí)候如果需要排序，MySQL會(huì)分兩種情況來(lái)處理這樣的文件排序。

1. 如果ORDER BY 子句中的所有列都來(lái)自關(guān)聯(lián)的第一個(gè)表，那么MySQL在關(guān)聯(lián)處理第一個(gè)表的時(shí)候就進(jìn)行文件排序。如果是這樣，那么在MySQL的EXPLAIN結(jié)果中可以看到Extra字段會(huì)有“Using filesort" 。
2. 除此之外的所有情況，MySQL 都會(huì)先將關(guān)聯(lián)的結(jié)果存放到一個(gè)臨時(shí)表中，然后在所有的關(guān)聯(lián)都結(jié)束后，再進(jìn)行文件排序。這種情況下，在MySQL的EXPLAIN結(jié)果的Extra字段可以看到“Using temporary; Using filesort”。

• 如果查詢中有LIMIT的話，LIMIT也會(huì)在排序之后應(yīng)用，所以即使需要返回較少的數(shù)據(jù)，臨時(shí)表和需要排序的數(shù)據(jù)量仍然會(huì)非常大。
• MySQL 5.6在這里做了很多重要的改進(jìn)。當(dāng)只需要返回部分排序結(jié)果的時(shí)候，例如使用了LIMIT子句，MySQL不再對(duì)所有的結(jié)果進(jìn)行排序，而是根據(jù)實(shí)際情況，選擇拋棄不滿足條件的結(jié)果，然后再進(jìn)行排序。

6.4.4 查詢執(zhí)行引擎

在解析和優(yōu)化階段，MySQL將生成查詢對(duì)應(yīng)的執(zhí)行計(jì)劃，MySQL的查詢執(zhí)行引擎則根據(jù)這個(gè)執(zhí)行計(jì)劃來(lái)完成整個(gè)查詢。這里執(zhí)行計(jì)劃是-一個(gè)數(shù)據(jù)結(jié)構(gòu)，而不是和很多其他的關(guān)系型數(shù)據(jù)庫(kù)那樣會(huì)生成對(duì)應(yīng)的字節(jié)碼。

相對(duì)于查詢優(yōu)化階段，查詢執(zhí)行階段不是那么復(fù)雜:MySQL只是簡(jiǎn)單地根據(jù)執(zhí)行計(jì)劃給出的指令逐步執(zhí)行。在根據(jù)執(zhí)行計(jì)劃逐步執(zhí)行的過(guò)程中，有大量的操作需要通過(guò)調(diào)用存儲(chǔ)弓|擎實(shí)現(xiàn)的接口來(lái)完成，這些接口也就是我們稱為“handler API”的接口。

查詢中的每一個(gè)表由一個(gè)handler的實(shí)例表示。MySQL在優(yōu)化階段就為每個(gè)表創(chuàng)建了一個(gè)handler實(shí)例，優(yōu)化器根據(jù)這些實(shí)例的接口可以獲取表的相關(guān)信息，包括表的所有列名、索引統(tǒng)計(jì)信息，等等。

存儲(chǔ)引擎接口有著非常豐富的功能，但是底層接口卻只有幾十個(gè)，這些接口像“搭積木”一樣能夠完成查詢的大部分操作。簡(jiǎn)單的接口模式，讓MySQL的存儲(chǔ)引擎插件式架構(gòu)成為可能，但也給優(yōu)化器帶來(lái)了一定的限制。

并不是所有的操作都由handler完成。

例如，當(dāng)MySQL需要進(jìn)行表鎖的時(shí)候。handler可能會(huì)實(shí)現(xiàn)自己的級(jí)別的、更細(xì)粒度的鎖，如InnoDB就實(shí)現(xiàn)了自己的行基本鎖，但這并不能代替服務(wù)器層的表鎖。

6.4.5 返回結(jié)果給客戶端

查詢執(zhí)行的最后一個(gè)階段是將結(jié)果返回給客戶端。即使查詢不需要返回結(jié)果集給客戶端，MySQL仍然會(huì)返回這個(gè)查詢一些信息，如該查詢影響到的行數(shù)。

如果查詢可以被緩存，那么MySQL在這個(gè)階段也會(huì)將結(jié)果存放到查詢緩存中。

MySQL將結(jié)果集返回客戶端是一個(gè)增量、逐步返回的過(guò)程。例如，我們回頭看看前面的關(guān)聯(lián)操作，一旦服務(wù)器處理完最后一個(gè)關(guān)聯(lián)表，開(kāi)始生成第一條結(jié)果時(shí)，MySQL就可以開(kāi)始向客戶端逐步返回結(jié)果集了。

這樣處理有兩個(gè)好處:

• 服務(wù)器端無(wú)須存儲(chǔ)太多的結(jié)果，也就不會(huì)因?yàn)橐祷靥嘟Y(jié)果而消耗太多內(nèi)存。
• 這樣的處理也讓MySQL客戶端第一時(shí)間獲得返回的結(jié)果。

結(jié)果集中的每一行都會(huì)以一個(gè)滿足MySQL客戶端/服務(wù)器通信協(xié)議的封包發(fā)送，再通過(guò)TCP協(xié)議進(jìn)行傳輸，在TCP傳輸?shù)倪^(guò)程中，可能對(duì)MySQL的封包進(jìn)行緩存然后批量傳輸。

6.5 MySQL查詢優(yōu)化器的局限性

對(duì)于開(kāi)發(fā)中使用的版本是否存在這類缺陷， 請(qǐng)自行在使用中判斷。

6.5.1 關(guān)聯(lián)子查詢

MySQL的子查詢實(shí)現(xiàn)得非常糟糕。

最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語(yǔ)句。

例如，我們希望找到Sakila數(shù)據(jù)庫(kù)中，演員Penelope Guiness (他的actor_ id為1)參演過(guò)的所有影片信息。很自然的，我們會(huì)按照下面的方式用子查詢實(shí)現(xiàn):

mysql> SELECT * FROM sakila.film
-> WHERE film_id IN(
->	SELECT film_id FROM sakila.film_actor WHERE actor_id = 1
-> );

因?yàn)镸ySQL對(duì)IN()列表中的選項(xiàng)有專門的優(yōu)化策略，一般會(huì)認(rèn)為MySQL會(huì)先執(zhí)行子查詢返回所有包含actor_id為1的film_id。一般來(lái)說(shuō)，IN()列表查詢速度很快，所以我們會(huì)認(rèn)為上面的查詢會(huì)這樣執(zhí)行:

-- SELECT GROUP_ CONCAT(film_id) FROM sakila.film _actor WHERE actor_id = 1;
-- Result: 1,23,25, 106, 140,166,277,361,438,499, 506, 509, 605, 635, 749,832, 939,970, 980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25, 106, 140, 166,277,361,438,499, 506, 509, 605,635, 749,832,939,970,980); 

很不幸，MySQL不是這樣做的。MySQL會(huì)將相關(guān)的外層表壓到子查詢中，它認(rèn)為這樣
可以更高效率地查找到數(shù)據(jù)行。也就是說(shuō)，MySQL會(huì)將查詢改寫(xiě)成下面的樣子:

SELECT * FROM sakila.film
WHERE EXISTS (
	SELECT * FROM sakila.film actor WHERE actor_id = 1
	AND film_actor.film_id = film.film_id
);

這時(shí)，子查詢需要根據(jù)film_id來(lái)關(guān)聯(lián)外部表film,因?yàn)樾枰猣ilm_ id字段,所以MySQL認(rèn)為無(wú)法先執(zhí)行這個(gè)子查詢。通過(guò)EXPLAIN我們可以看到子查詢是一個(gè)相關(guān)子查詢(DEPENDENT SUBQUERY) ( 可以使用EXPLAIN EXTENDED 來(lái)查看這個(gè)查詢被改寫(xiě)成了什么樣子) :

可以看到，MySQL將film表全部掃描出來(lái)，然后判斷exists里面的條件

可以用下面的辦法來(lái)重寫(xiě)這個(gè)查詢:

mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;

是否使用關(guān)聯(lián)子查詢

建議自己寫(xiě)一遍后判斷。

6.5.2 UNION的限制

有時(shí)，MySQL無(wú)法將限制條件從外層“下推”到內(nèi)層，這使得原本能夠限制部分返回
結(jié)果的條件無(wú)法應(yīng)用到內(nèi)層查詢的優(yōu)化上。

如果希望UNION的各個(gè)子句能夠根據(jù)LIMIT只取部分結(jié)果集，或者希望能夠先排好序再合并結(jié)果集的話，就需要在UNION的各個(gè)子句中分別使用這些子句。

例如，想將兩個(gè)子查詢結(jié)果聯(lián)合起來(lái)，然后再取前20條記錄，那么MySQL會(huì)將兩個(gè)表都存放到同一個(gè)臨時(shí)表中，然后再取出前20行記錄:

(SELECT first_name,last_name
	FROM sakila.actor
	ORDER BY last_name)
UNION ALL
(SELECT first_name,last_name
	FROM sakila.customer
	ORDER BY last_name)
LIMIT 20;

這條查詢將會(huì)把a(bǔ)ctor中的200條記錄和customer表中的599條記錄存放在一個(gè)臨時(shí)表中，然后再?gòu)呐R時(shí)表中取出前20條?？梢酝ㄟ^(guò)在UNION的兩個(gè)子查詢中分別加上一個(gè)LIMIT 20來(lái)減少臨時(shí)表中的數(shù)據(jù):

(SELECT first name, last_ name
	FROM sakila.actor
	ORDER BY last_name
LIMIT 20)
UNION ALL
(SELECT first_name, last_name
	FROM sakila.customer
	ORDER BY last_name
	LIMIT 20)
LIMIT 20;

現(xiàn)在中間的臨時(shí)表只會(huì)包含40條記錄了，除了性能考慮之外，在這里還需要注意一點(diǎn):

• 從臨時(shí)表中取出數(shù)據(jù)的順序并不是一定的，所以如果想獲得正確的順序，還需要加上一個(gè)全局的ORDERBY和LIMIT操作。

6.5.3 索引合并優(yōu)化

前文

6.5.4 等值傳遞

某些時(shí)候，等值傳遞會(huì)帶來(lái)一些意想不到的額外消耗。

例如，有-個(gè)非常大的IN()列表,而MySQL優(yōu)化器發(fā)現(xiàn)存在WHERE、ON或者USING的子句，將這個(gè)列表的值和另-一個(gè)表的某個(gè)列相關(guān)聯(lián)。

那么優(yōu)化器會(huì)將IN()列表都復(fù)制應(yīng)用到關(guān)聯(lián)的各個(gè)表中。通常，因?yàn)楦鱾€(gè)表新增了過(guò)濾條件，優(yōu)化器可以更高效地從存儲(chǔ)引擎過(guò)濾記錄。但是如果這個(gè)列表非常大，則會(huì)導(dǎo)致優(yōu)化和執(zhí)行都會(huì)變慢。至?xí)霭鏁r(shí)間，除了修改MySQL源代碼，目前還沒(méi)有什么.辦法能夠繞過(guò)該問(wèn)題(不過(guò)這個(gè)問(wèn)題很少會(huì)碰到)。

6.5.5 并行執(zhí)行

MySQL無(wú)法利用多核特性來(lái)并行執(zhí)行查詢。

很多其他的關(guān)系型數(shù)據(jù)庫(kù)能夠提供這個(gè)特性，但是MySQL做不到。

6.5.6 哈希關(guān)聯(lián)

至?xí)霭鏁r(shí)間，MySQL并不支持哈希關(guān)聯(lián)——MySQL 的所有關(guān)聯(lián)都是嵌套循環(huán)關(guān)聯(lián)。

不過(guò)，可以通過(guò)建立一個(gè)哈希索引來(lái)曲線地實(shí)現(xiàn)哈希關(guān)聯(lián)。

如果使用的是Memory存儲(chǔ)引擎，則索引都是哈希索引，所以關(guān)聯(lián)的時(shí)候也類似于哈希關(guān)聯(lián)。

MariaDB 已經(jīng)實(shí)現(xiàn)了真正的哈希關(guān)聯(lián)。

6.5.7松散索引掃描

由于歷史原因，MySQL并不支持松散索引掃描，也就無(wú)法按照不連續(xù)的方式掃描一個(gè)索引。

通常，MySQL的索引掃描需要先定義一個(gè)起點(diǎn)和終點(diǎn)，即使需要的數(shù)據(jù)只是這段索引中很少數(shù)的幾個(gè)，MySQL仍需要掃描這段索引中每一個(gè)條目。

假設(shè)我們有如下索引(a, b)，有下面的查詢:

mysql> SELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;

索引的前導(dǎo)字段是列a，在查詢中只指定了字段b, 于是MySQL無(wú)法使用這個(gè)索引，從而只能通過(guò)全表掃描找到匹配的行，如圖6-5所示。

松散的查詢則是可以通過(guò)查詢所有a=1下的b，然后再查詢所有a=2下的b，但是MySQL不支持。

MySQL 5.0之后的版本，在某些特殊的場(chǎng)景下是可以使用松散索引掃描的，例如，在一個(gè)分組查詢中需要找到分組的最大值和最小值。

6.5.8 最大值和最小值優(yōu)化

對(duì)于MIN()和MAX()查詢，MySQL的優(yōu)化做得并不好。

mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';

first_name 字段上并沒(méi)有索引，此時(shí)MySQL將會(huì)進(jìn)行一次全表掃描。

如果MySQL能夠進(jìn)行主鍵掃描,那么理論上,當(dāng)MySQL讀到第一個(gè)滿足條件的記錄的時(shí)候,就是我們需要找的最小值了，因?yàn)橹麈I是嚴(yán)格按照actor_ id 字段的大小順序排列的。

但是MySQL這時(shí)只會(huì)做全表掃描，我們可以通過(guò)查看SHOW STATUS 的全表掃描計(jì)數(shù)器來(lái)驗(yàn)證這一點(diǎn)。一個(gè)曲線的優(yōu)化辦法是移除MIN()，然后使用LIMIT來(lái)將查詢重寫(xiě)如下:

mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
-> WHERE first_name ='PENELOPE' LIMIT 1;

這個(gè)策略可以讓MySQL掃描盡可能少的記錄數(shù)。如果你是一個(gè)完美主義者，可能會(huì)說(shuō)這個(gè)SQL已經(jīng)無(wú)法表達(dá)她的本意了。一般我們通過(guò)SQL告訴服務(wù)器我們需要什么數(shù)據(jù),由服務(wù)器來(lái)決定如何最優(yōu)地獲取數(shù)據(jù)，不過(guò)在這個(gè)案例中，我們其實(shí)是告訴MySQL如何去獲取我們需要的數(shù)據(jù)，通過(guò)SQL并不能一眼就看出我們其實(shí)是想要一個(gè)最小值。確實(shí)如此，有時(shí)候?yàn)榱双@得更高的性能，我們不得不放棄一些原則。

6.5.9 在同一個(gè)表.上查詢和更新

MySQL不允許對(duì)同一張表同時(shí)進(jìn)行查詢和更新。

這其實(shí)并不是優(yōu)化器的限制，如果清楚MySQL是如何執(zhí)行查詢的，就可以避免這種情況。

下面是一個(gè)無(wú)法運(yùn)行的SQL，雖然這是一個(gè)符合標(biāo)準(zhǔn)的SQL語(yǔ)句。這個(gè)SQL語(yǔ)句嘗試將兩個(gè)表中相似行的數(shù)量記錄到字段cnt中:

mysql> UPDATE tbl AS outer_tbl
-> SET cnt = (
-> SELECT count(*) FROM tbl AS inner_tbl
-> WHERE inner_tbl.type = outer_tbl.type
-> );
ERROR 1093 (H000): You can't specify target table ' outer_tbl' for update in FROM
clause

可以通過(guò)使用生成表的形式來(lái)繞過(guò)上面的限制，因?yàn)镸ySQL只會(huì)把這個(gè)表當(dāng)作一個(gè)臨
時(shí)表來(lái)處理。

實(shí)際上，這執(zhí)行了兩個(gè)查詢:

• 一個(gè)是子查詢中的SELECT語(yǔ)句
• 一個(gè)是多表關(guān)聯(lián)UPDATE,只是關(guān)聯(lián)的表是一個(gè)臨時(shí)表

子查詢會(huì)在UPDATE語(yǔ)句打開(kāi)表之前就完成，所以下面的查詢將會(huì)正常執(zhí)行:

mysql> UPDATE tbl
-> INNER JOIN(
-> SELECT type, count(*) AS cnt
-> FROM tbl
-> GROUP BY type
-> ) AS der USING(type)
-> SET tbl.cnt = der.cnt;

6.6 查詢優(yōu)化器的提示

提示、引導(dǎo)優(yōu)化器如何生成最終的SQL語(yǔ)句，已達(dá)到預(yù)想的效果。

這里可以直接查看，書(shū)中文字略。

• MySQL中文文檔-優(yōu)化器提示
• MySQL英文文檔-Optimizer Hints

6.7 優(yōu)化特定的查詢

6.7.1 優(yōu)化COUNT()查詢

COUNT()的作用

COUNT()是一個(gè)特殊的函數(shù)，有兩種非常不同的作用

1. 可以統(tǒng)計(jì)某個(gè)列值的數(shù)量

在統(tǒng)計(jì)列值時(shí)要求列值是 非空的 (不統(tǒng)計(jì)NULL)。

如果在COUNT()的括號(hào)中指定了列或者列的表達(dá)式，則統(tǒng)計(jì)的就是這個(gè)表達(dá)式有值的結(jié)果數(shù)(NOT NULL)。

2. 可以統(tǒng)計(jì)行數(shù)

最簡(jiǎn)單的就是當(dāng)我們使用COUNT(*)的時(shí)候，這種情況下通配符*并不會(huì)像我們猜想的那樣擴(kuò)展成所有的列，實(shí)際上，它會(huì)忽略所有的列而直接統(tǒng)計(jì)所有的行數(shù)。

我們發(fā)現(xiàn)一個(gè)最常見(jiàn)的錯(cuò)誤就是，在括號(hào)內(nèi)指定了一個(gè)列卻希望統(tǒng)計(jì)結(jié)果集的行數(shù)。如果希望知道的是結(jié)果集的行數(shù)，最好使用COUNT(*)，這樣寫(xiě)意義清晰，性能也會(huì)很好。

關(guān)于MyISAM的神話

一個(gè)容易產(chǎn)生的誤解就是:

• MyISAM的COUNT()函數(shù)總是非?？?/li>

不過(guò)這是有前提條件的,即只有沒(méi)有任何WHERE條件的COUNT(*)才非?？?因?yàn)榇藭r(shí)無(wú)須實(shí)際地去計(jì)算表的行數(shù)。

MySQL可以利用存儲(chǔ)引擎的特性直接獲得這個(gè)值。如果MySQL知道某列coL不可能為NULL值，那么MySQL內(nèi)部會(huì)將COUNT(col)表達(dá)式優(yōu)化為COUNT(*)

當(dāng)統(tǒng)計(jì)帶WHERE子句的結(jié)果集行數(shù)，可以是統(tǒng)計(jì)某個(gè)列值的數(shù)量時(shí)，MyISAM的COUNT()和其他存儲(chǔ)引擎沒(méi)有任何不同。

所以在MyISAM引擎表上執(zhí)行COUNT( )有時(shí)候比別的引擎快，有時(shí)候比別的引擎慢。

簡(jiǎn)單的優(yōu)化

有時(shí)候可以使用MyISAM在COUNT(*)全表非?？斓倪@個(gè)特性，來(lái)加速一些特定條件的COUNT()的查詢。

在下面的例子中，我們使用標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)world來(lái)看看如何快速查找到所有ID大于5的城市。

可以像下面這樣來(lái)寫(xiě)這個(gè)查詢:

mysql> SELECT COUNT(*) FROM world.City WHERE ID > 5;

通過(guò)SHOW STATUS 的結(jié)果可以看到該查詢需要掃描4097行數(shù)據(jù)。

如果將條件反轉(zhuǎn)一下，卻可以將掃描的行數(shù)減少到5行以內(nèi):

mysql> SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*)
-> FROM world.City WHERE ID <= 5;

在郵件組和IRC聊天頻道中，通常會(huì)看到這樣的問(wèn)題:如何在同一個(gè)查詢中統(tǒng)計(jì)同一個(gè)列的不同值的數(shù)量，以減少查詢的語(yǔ)句量。

例如，假設(shè)可能需要通過(guò)一個(gè)查詢返回各種不同顏色的商品數(shù)量

• 不能使用OR語(yǔ)句(比如SELECT COUNT(color= ‘blue’ OR color=‘red’) FROM items;), 因?yàn)檫@樣做就無(wú)法區(qū)分不同顏色的商品數(shù)量
• 不能在WHERE條件中指定顏色( 比如SELECT COUNT(*) FROM ‘items WHERE color=’ blue’ AND color=’ RED’ ; )，因?yàn)轭伾臈l件是互斥的。

下面的查詢可以在一定程度上解決這個(gè)問(wèn)題。

mysql> SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0))
-> AS red FROM items;

也可以使用COUNT()而不是SUM()實(shí)現(xiàn)同樣的目的，只需要將滿足條件設(shè)置為真，不滿
足條件設(shè)置為NULL即可:

mysql> SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL)
-> AS red FROM items;

使用近似值

有時(shí)候某些業(yè)務(wù)場(chǎng)景并不要求完全精確的COUNT值，此時(shí)可以用近似值來(lái)代替。

EXPLAIN出來(lái)的優(yōu)化器估算的行數(shù)就是一個(gè)不錯(cuò)的近似值，執(zhí)行EXPLAIN并不需要真正地去執(zhí)行查詢，所以成本很低。

更復(fù)雜的優(yōu)化

通常來(lái)說(shuō)，COUNT()都需要掃描大量的行(意味著要訪問(wèn)大量數(shù)據(jù))才能獲得精確的結(jié)果，因此是很難優(yōu)化的。

除了前面的方法，在MySQL層面還能做的就只有索引覆蓋掃描了。

如果這還不夠,就需要考慮修改應(yīng)用的架構(gòu),可以增加匯總表,或者增加類似Memcached這樣的外部緩存系統(tǒng)。

“快速，精確和實(shí)現(xiàn)簡(jiǎn)單”，三者永遠(yuǎn)只能滿足其二，必須舍掉其中一個(gè)。

6.7.2 優(yōu)化關(guān)聯(lián)查詢

• 確保ON或者USING子句中的列上有索引
• 確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列
• 當(dāng)升級(jí)MySQL的時(shí)候需要注意:關(guān)聯(lián)語(yǔ)法、運(yùn)算符優(yōu)先級(jí)等其他可能會(huì)發(fā)生變化的地方。因?yàn)橐郧笆瞧胀P(guān)聯(lián)的地方可能會(huì)變成笛卡兒積，不同類型的關(guān)聯(lián)可能會(huì)生成不同的結(jié)果等

6.7.3 優(yōu)化子查詢

關(guān)于子查詢優(yōu)化書(shū)中（因?yàn)榈堑冒姹荆┙o出的最重要的優(yōu)化建議就是盡可能使用關(guān)聯(lián)查詢代替。

實(shí)際如何,需要自己跑一遍

6.7.4 優(yōu)化GROUP BY和DISTINCT

在很多場(chǎng)景下，MySQL都使用同樣的辦法優(yōu)化這兩種查詢，事實(shí)上，MySQL優(yōu)化器會(huì)在內(nèi)部處理的時(shí)候相互轉(zhuǎn)化這兩類查詢。它們都可以使用索引來(lái)優(yōu)化，這也是最有效的優(yōu)化辦法。

在MySQL中，當(dāng)無(wú)法使用索引的時(shí)候，GROUP BY 使用兩種策略來(lái)完成:

• 使用臨時(shí)表
• 文件排序來(lái)做分組

對(duì)于任何查詢語(yǔ)句，這兩種策略的性能都有可以提升的地方。可以通過(guò)使用提示SQL_BIG_RESULT 和SQL_SMALL_RESULT 來(lái)讓優(yōu)化器按照你希望的方式運(yùn)行。

如果需要對(duì)關(guān)聯(lián)查詢做分組(GROUP BY), 并且是按照查找表中的某個(gè)列進(jìn)行分組，那么通常采用查找表的標(biāo)識(shí)列分組的效率會(huì)比其他列更高。

但顯然不是所有的關(guān)聯(lián)語(yǔ)句的分組查詢都可以改寫(xiě)成在SELECT中直接使用非分組列的形式
的。

甚至可能會(huì)在服務(wù)器上設(shè)置SQL_MODE來(lái)禁止這樣的寫(xiě)法。

如果是這樣，也可以通過(guò)MIN()或者M(jìn)AX()函數(shù)來(lái)繞過(guò)這種限制，但一定要清楚，SELECT 后面出現(xiàn)的非分組列一定是直接依賴分組列，并且在每個(gè)組內(nèi)的值是唯一的，或者是業(yè)務(wù)上根本不在乎這個(gè)值
具體是什么:

mysql> SELECT MIN(actor.first_ name), MAX(actor.last_name), ...;

較真的人可能會(huì)說(shuō)這樣寫(xiě)的分組查詢是有問(wèn)題的，確實(shí)如此。從MIN()或者M(jìn)AX()函數(shù)的用法就可以看出這個(gè)查詢是有問(wèn)題的。但若更在乎的是MySQL運(yùn)行查詢的效率時(shí)這樣做也無(wú)可厚非。如果實(shí)在較真的話也可以改寫(xiě)成下面的形式:

mysql> SELECT actor.first_name, actor.last_name, c.cnt
-> FROM sakila.actor
->INNER JOIN (
-> SELECT actor_id, COUNT(*) AS cnt 
-> FROM sakila.film_actor
-> GROUP BY actor_id
->) AS C USING(actor_id);

這樣寫(xiě)更滿足關(guān)系理論，但成本有點(diǎn)高，因?yàn)樽硬樵冃枰獎(jiǎng)?chuàng)建和填充臨時(shí)表,而子查詢中創(chuàng)建的臨時(shí)表是沒(méi)有任何索引的。

在分組查詢的SELECT中直接使用非分組列通常都不是什么好主意，因?yàn)檫@樣的結(jié)果通常是不定的，當(dāng)索引改變，或者優(yōu)化器選擇不同的優(yōu)化策略時(shí)都可能導(dǎo)致結(jié)果不一樣。

大多數(shù)這種查詢最后都導(dǎo)致了故障(因?yàn)镸ySQL不會(huì)對(duì)這類查詢返回錯(cuò)誤)，而且這種寫(xiě)法大部分是由于偷懶而不是為優(yōu)化而故意這么設(shè)計(jì)的。

事實(shí)上，我們建議將MySQL的SQL_MODE 設(shè)置為包含ONLY_FULL GROUP_BY,這時(shí)MySQL會(huì)對(duì)這類查詢直接返回一個(gè)錯(cuò)誤，提醒你需要重寫(xiě)這個(gè)查詢。

如果沒(méi)有通過(guò)ORDER BY 子句顯式地指定排序列，當(dāng)查詢使用GROUP BY子句的時(shí)候，結(jié)果集會(huì)自動(dòng)按照分組的字段進(jìn)行排序。

如果不關(guān)心結(jié)果集的順序，而這種默認(rèn)排序又導(dǎo)致了需要文件排序，則可以使用ORDER BY NULL， 讓MySQL不再進(jìn)行文件排序。也可以在GROUPBY子句中直接使用DESC或者ASC關(guān)鍵字,使分組的結(jié)果集按需要的方向排序。

優(yōu)化GROUP BY WITH ROLLUP

分組查詢的一個(gè)變種就是要求MySQL對(duì)返回的分組結(jié)果再做一次超級(jí)聚合?？梢允褂肳ITH ROLLUP 子句來(lái)實(shí)現(xiàn)這種邏輯，但可能會(huì)不夠優(yōu)化?？梢酝ㄟ^(guò)EXPLAIN來(lái)觀察其執(zhí)行計(jì)劃，特別要注意分組是否是通過(guò)文件排序或者臨時(shí)表實(shí)現(xiàn)的。然后再去掉WITH ROLLUP子句看執(zhí)行計(jì)劃是否相同。也可以通過(guò)本節(jié)前面介紹的優(yōu)化器提示來(lái)固定執(zhí)行計(jì)劃。

很多時(shí)候，如果可以，在應(yīng)用程序中做超級(jí)聚合是更好的，雖然這需要返回給客戶端更多的結(jié)果。也可以在FROM子句中嵌套使用子查詢,或者是通過(guò)一個(gè)臨時(shí)表存放中間數(shù)據(jù)，然后和臨時(shí)表執(zhí)行UNION來(lái)得到最終結(jié)果。

最好的辦法是盡可能的將WITH ROLLUP 功能轉(zhuǎn)移到應(yīng)用程序中處理。

6.7.5 優(yōu)化LIMIT分頁(yè)

在系統(tǒng)中需要進(jìn)行分頁(yè)操作的時(shí)候，我們通常會(huì)使用LIMIT加上偏移量的辦法實(shí)現(xiàn)，同時(shí)加上合適的ORDER BY 子句。

如果有對(duì)應(yīng)的索引，通常效率會(huì)不錯(cuò)，否則，MySQL需要做大量的文件排序操作。

一個(gè)非常常見(jiàn)又令人頭疼的問(wèn)題就是，在偏移量非常大的時(shí)候，例如可能是LIMIT1000 , 20這樣的查詢，這時(shí)MySQL需要查詢10 020條記錄然后只返回最后20條，前面10000條記錄都將被拋棄，這樣的代價(jià)非常高。

要優(yōu)化這種查詢，要么是在頁(yè)面中限制分頁(yè)的數(shù)量，要么是優(yōu)化大偏移量的性能。

優(yōu)化此類分頁(yè)查詢的一個(gè)最簡(jiǎn)單的辦法就是盡可能地使用索引覆蓋掃描，而不是查詢所有的列。然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列。對(duì)于偏移量很大的時(shí)候，這樣做的效率會(huì)提升非常大。

考慮下面的查詢:

mysql> SELECT fi1m_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果這個(gè)表非常大，那么這個(gè)查詢最好改寫(xiě)成下面的樣子:

mysql> SELECT film.film_id, film. description
-> FROM sakila.film
-> INNER JOIN (
-> SELECT film_ id FROM sakila.film
-> ORDER BY title LIMIT 50, 5
-> ) AS lim USING(film_id);

這里的“延遲關(guān)聯(lián)”將大大提升查詢效率，它讓MySQL掃描盡可能少的頁(yè)面，獲取需要訪問(wèn)的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢需要的所有列。

這個(gè)技術(shù)也可以用于優(yōu)化關(guān)聯(lián)查詢中的LIMIT子句。

有時(shí)候也可以將LIMIT查詢轉(zhuǎn)換為已知位置的查詢，讓MySQL通過(guò)范圍掃描獲得到對(duì)應(yīng)的結(jié)果。例如，如果在一個(gè)位置列上有索引，并且預(yù)先計(jì)算出了邊界值，上面的查詢就可以改寫(xiě)為:

mysql> SELECT film_id, description FROM sakila.film
-> WHERE position BETWEEN 50 AND 54 ORDER BY position;

6.7.6 優(yōu)化SQL_CALC_FOUND_ROWS

分頁(yè)的時(shí)候,另一個(gè)常用的技巧是在LIMIT語(yǔ)句中加上SQL_CALC_FOUND_ROWS 提示(hint),這樣就可以獲得去掉LIMIT以后滿足條件的行數(shù)，因此可以作為分頁(yè)的總數(shù)。

看起來(lái)，MySQL做了一些非?！案呱睢钡膬?yōu)化，像是通過(guò)某種方法預(yù)測(cè)了總行數(shù)。但實(shí)際上,MySQL只有在掃描了所有滿足條件行以后，才會(huì)知道行數(shù)，所以加上這個(gè)提示以后，不管是否需要，MySQL都會(huì)掃描所有滿足條件的行，然后再拋棄掉不需要的行，而不是在滿足LIMIT的行數(shù)后就終止掃描。所以該提示的代價(jià)可能非常高。

• 一個(gè)更好的設(shè)計(jì)是將具體的頁(yè)數(shù)換成“下一頁(yè)”按鈕，假設(shè)每頁(yè)顯示20條記錄，那么我們每次查詢時(shí)都是用LIMIT返回21條記錄并只顯示20條，如果第21條存在，那么我們就顯示“下一頁(yè)”按鈕，否則就說(shuō)明沒(méi)有更多的數(shù)據(jù)，也就無(wú)須顯示“下一頁(yè)"按鈕了。
• 另一種做法是先獲取并緩存較多的數(shù)據(jù)一例如， 緩存1000 條一然后 每次分頁(yè)都從這個(gè)緩存中獲取。這樣做可以讓?xiě)?yīng)用程序根據(jù)結(jié)果集的大小采取不同的策略，如果結(jié)果集少于1 000，就可以在頁(yè)面上顯示所有的分頁(yè)鏈接，因?yàn)閿?shù)據(jù)都在緩存中，所以這樣做性能不會(huì)有問(wèn)題。如果結(jié)果集大于1 000，則可以在頁(yè)面上設(shè)計(jì)一個(gè)額外的“找到的結(jié)果多于1000條”之類的按鈕。

這兩種策略都比每次生成全部結(jié)果集再拋棄掉不需要的數(shù)據(jù)的效率要高很多。

有時(shí)候也可以考慮使用EXPLAIN的結(jié)果中的rows列的值來(lái)作為結(jié)果集總數(shù)的近似值。

當(dāng)需要精確結(jié)果的時(shí)候，再單獨(dú)使用COUNT(*)來(lái)滿足需求，這時(shí)如果能夠使用索引覆蓋掃描則通常也會(huì)比SQL_CALC_ FOUND_ROWS快得多。

6.7.7 優(yōu)化UNION查詢

MySQL總是通過(guò)創(chuàng)建并填充臨時(shí)表的方式來(lái)執(zhí)行UNION查詢。因此很多優(yōu)化策略在UNION查詢中都沒(méi)法很好地使用。經(jīng)常需要手工地將WHERE、LIMIT. ORDER BY等子句“下推”到UNION的各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化。

除非確實(shí)需要服務(wù)器消除重復(fù)的行，否則就一定要使用UNION ALL， 這一點(diǎn)很重要。如果沒(méi)有ALL關(guān)鍵字，MySQL會(huì)給臨時(shí)表加上DISTINCT選項(xiàng)，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表的數(shù)據(jù)做唯一性檢查。這樣做的代價(jià)非常高。即使有ALL關(guān)鍵字，MySQL仍然會(huì)使用臨時(shí)表存儲(chǔ)結(jié)果。事實(shí)上, MySQL總是將結(jié)果放入臨時(shí)表,然后再讀出，再返回給客戶端。

6.7.8靜態(tài)查詢分析

Percona Toolkit 中的pt-query advisor能夠解析查詢?nèi)罩?、分析查詢模式，然后給出所有可能存在潛在問(wèn)題的查詢，并給出足夠詳細(xì)的建議。這像是給MySQL所有的查詢做一次全面的健康檢查。它能檢測(cè)出許多常見(jiàn)的問(wèn)題，諸如我們前面介紹的內(nèi)容。

6.7.9 使用用戶自定義變量

用戶自定義變量是一個(gè)容易被遺忘的MySQL特性，但是如果能夠用好，發(fā)揮其潛力，在某些場(chǎng)景可以寫(xiě)出非常高效的查詢語(yǔ)句。

在查詢中混合使用過(guò)程化和關(guān)系化邏輯的時(shí)候，自定義變量可能會(huì)非常有用。

單純的關(guān)系查詢將所有的東西都當(dāng)成無(wú)序的數(shù)據(jù)集合，并且一次性操作它們。MySQL則采用了更加程序化的處理方式。MySQL的這種方式有
它的弱點(diǎn)，但如果能熟練地掌握，則會(huì)發(fā)現(xiàn)其強(qiáng)大之處，而用戶自定義變量也可以給這種方式帶來(lái)很大的幫助。

用戶自定義變量是-個(gè)用來(lái)存儲(chǔ)內(nèi)容的臨時(shí)容器，在連接MySQL的整個(gè)過(guò)程中都存在。

可以使用下面的SET和SELECT語(yǔ)句來(lái)定義它們 :

mysql> SET @one:= 1;
mysql> SET @min_actor:= (SELECT MIN(actor_id) FROM sakila.actor);
mysql> SET @last_week:= CURRENT_DATE-INTERVAL 1 WEEK;

select、declare、set均可申明變量預(yù)賦值，具體可以參考-SQL server數(shù)據(jù)庫(kù)declare和set、用法技巧,賦值建議使用:=而不是=

然后可以在任何可以使用表達(dá)式的地方使用這些自定義變量:

mysql> SELECT ... WHERE col <= @last_week;

下我們不能使用用戶自定義變量:

• 使用自定義變量的查詢，無(wú)法使用查詢緩存。
• 不能在使用常量或者標(biāo)識(shí)符的地方使用自定義變量,例如表名、列名和LIMIT子句中。
• 用戶自定義變量的生命周期是在一個(gè)連接中有效，所以不能用它們來(lái)做連接間的通信。
• 如果使用連接池或者持久化連接，自定義變量可能讓看起來(lái)毫無(wú)關(guān)系的代碼發(fā)生交互。
• 在5.0之前的版本，是大小寫(xiě)敏感的，所以要注意代碼在不同MySQL版本間的兼容性問(wèn)題。
• 不能顯式地聲明自定義變量的類型。確定未定義變量的具體類型的時(shí)機(jī)在不同MySQL版本中也可能不一樣。如果你希望變量是整數(shù)類型,那么最好在初始化的時(shí)候就賦值為0,如果希望是浮點(diǎn)型則賦值為0.0，如果希望是字符串則賦值為",用戶自定義變量的類型在賦值的時(shí)候會(huì)改變。MySQL的用戶自定義變量是一個(gè)動(dòng)態(tài)類型。
• MySQL優(yōu)化器在某些場(chǎng)景下可能會(huì)將這些變量?jī)?yōu)化掉，這可能導(dǎo)致代碼不按預(yù)想的方式運(yùn)行。賦值的順序和賦值的時(shí)間點(diǎn)并不總是固定的，這依賴于優(yōu)化器的決定。實(shí)際情況可能很讓人困惑，后面我們將看到這一點(diǎn)。
• 賦值符號(hào):= 的優(yōu)先級(jí)非常低，所以需要注意，賦值表達(dá)式應(yīng)該使用明確的括號(hào)。
• 使用未定義變量不會(huì)產(chǎn)生任何語(yǔ)法錯(cuò)誤，如果沒(méi)有意識(shí)到這一點(diǎn)，非常容易犯錯(cuò)。

優(yōu)化排名語(yǔ)句

下面的例子展示了如何使用變量來(lái)實(shí)現(xiàn)一個(gè)類似“行號(hào)(row number)”的功能:

mysql> SET @rownum := 0;
mysql> SELECT actor_ id, @rownum := @rownum + 1 AS rownum
-> FROM sakila.actor LIMIT 3;

在我們來(lái)看一個(gè)更復(fù)雜的用法。我們先編寫(xiě)一個(gè)查詢獲取演過(guò)最多電影的前10位演員，然后根據(jù)他們的出演電影次數(shù)做一個(gè)排名，如果出演的電影數(shù)量一樣，則排名相同。我們先編寫(xiě)一個(gè)查詢，返回每個(gè)演員參演電影的數(shù)量:

mysql> SELECT actor_id, COUNT(*) as cnt
-> FROM sakila.film_actor
-> GROUP BY actor_id
-> ORDER BY cnt DESC
-> LIMIT 10; 

現(xiàn)在我們?cè)侔雅琶由先?，這里看到有四名演員都參演了35部電影，所以他們的排名應(yīng)該是相同的。

我們使用三個(gè)變量來(lái)實(shí)現(xiàn):

• 一個(gè)用來(lái)記錄當(dāng)前的排名
• 一個(gè)用來(lái)記錄前一個(gè)演員的排名
• 還有一個(gè)用來(lái)記錄當(dāng)前演員參演的電影數(shù)量

只有當(dāng)前演員參演的電影的數(shù)量和前一個(gè)演員不同時(shí)，排名才變化。

mysql> SET @curr_cnt:= 0, @prev_cnt:= 0, @rank:= 0;
mysql> SELECT actor_id,
-> @curr_cnt:= COUNT(*) AS cnt,
-> @rank:= IF(@prev_cnt<> @curr_cnt,@rank + 1, @rank) AS rank,
-> @prev_cnt:= @curr_cnt AS dummy
-> FROM sakila.film_actor
-> GROUP BY actor_id
-> ORDER BY cnt DESC
-> LIMIT 10;

2. 可以使用子查詢生成子表

mysql> SET @curr_cnt:= 0, @prev_cnt:= 0, @rank:= 0;
mysql> SELECT actor_id,
-> @curr_cnt:= cnt AS cnt,
-> @rank:= IF(@prev_cnt<> @curr_cnt,@rank + 1, @rank) AS rank,
-> @prev_cnt:= @curr_cnt AS dummy
-> FROM (SELECT actor_id,COUNT(*) as cnt
-> 	FROM sakila.film_actor
-> 	GROUP BY actor_id
-> 	ORDER BY cnt DESC
-> 	LIMIT 10
-> )AS der;

避免重復(fù)查詢剛剛更新的數(shù)據(jù)

ySQL并不支持像PostgreSQL那樣的UPDATE RETURNING 語(yǔ)法，這個(gè)語(yǔ)法可以幫你在更新行的時(shí)候同時(shí)返回該行的信息。

在MySQL中你可以使用變量來(lái)解決這個(gè)問(wèn)題。

例如，我們的一個(gè)客戶希望能夠更高效地更新一條記錄的時(shí)間戳，同時(shí)希望查詢當(dāng)前記錄中存放的時(shí)間戳是什么。

簡(jiǎn)單地，可以用下面的代碼來(lái)實(shí)現(xiàn):

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1;
SELECT lastUpdated FROM t1 WHERE id = 1;

使用變量，我們可以按如下方式重寫(xiě)查詢:

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now:= NOW();
SELECT @now;

上面看起來(lái)仍然需要兩個(gè)查詢，需要兩次網(wǎng)絡(luò)來(lái)回，但是這里的第二個(gè)查詢無(wú)須訪問(wèn)任何數(shù)據(jù)表，所以會(huì)快非常多。

統(tǒng)計(jì)更新和插入的數(shù)量

當(dāng)使用了INSERT ON DUPLICATE KEY UPDATE的時(shí)候,如果想知道到底插入了多少行數(shù)據(jù),到底有多少數(shù)據(jù)是因?yàn)闆_突而改寫(xiě)成更新操作的?

現(xiàn)辦法的本質(zhì)如下:

INSERT INTO t1(c1, c2) 
VALUES(4, 4), (2, 1), (3, 1)
ON DUPLICATE KEY UPDATE C1 = VALUES(c1) + (0*(@x :=@x+1 ) );

當(dāng)每次由于沖突導(dǎo)致更新時(shí)對(duì)變量@x自增一次。然后通過(guò)對(duì)這個(gè)表達(dá)式乘以0來(lái)讓其不影響要更新的內(nèi)容。

另外，MySQL的協(xié)議會(huì)返回被更改的總行數(shù)，所以不需要單獨(dú)統(tǒng)計(jì)這個(gè)值。

確定取值的順序

使用用戶自定義變量的一個(gè)最常見(jiàn)的問(wèn)題就是沒(méi)有注意到在賦值和讀取變量的時(shí)候可能是在查詢的不同階段。

例如，在SELECT子句中進(jìn)行賦值然后在WHERE子句中讀取變量，則可能變量取值并不如你所想。下面的查詢看起來(lái)只返回一個(gè)結(jié)果，但事實(shí)并非如此:

mysql> SET @rownum:= 0;
mysql> SELECT actor_id, @rownum:=@rownum + 1 AS cnt
->FROM sakila.actor
-> WHERE @rownum <= 1;

因?yàn)閃HERE和SELECT是在查詢執(zhí)行的不同階段被執(zhí)行的。如果在查詢中再加入ORDERBY的話，結(jié)果可能會(huì)更不同：

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
-> FROM sakila.actor
-> WHERE @rownum <= 1
-> ORDER BY first name;

這是因?yàn)镺RDERBY引入了文件排序，而WHERE條件是在文件排序操作之前取值的，所以這條查詢會(huì)返回表中的全部記錄。解決這個(gè)問(wèn)題的辦法是讓變量的賦值和取值發(fā)生在執(zhí)行查詢的同一階段:

mysq1> SET @rownum := 0;
mysql> SELECT actor.id, @rownum AS rownum
-> FROM sakila.actor
-> WHERE (@rownum := @rownum + 1) <= 1;

編寫(xiě)偷懶的UNION

假設(shè)需要編寫(xiě)一個(gè)UNION查詢，其第一個(gè)子查詢作為分支條件先執(zhí)行，如果找到了匹配的行，則跳過(guò)第二個(gè)分支。

在某些業(yè)務(wù)場(chǎng)景中確實(shí)會(huì)有這樣的需求，比如先在一個(gè)頻繁訪問(wèn)的表中查找“ 熱”數(shù)據(jù)，找不到再去另外一個(gè)較少訪問(wèn)的表中查找“冷”數(shù)據(jù)。(區(qū)分熱數(shù)據(jù)和冷數(shù)據(jù)是一個(gè)很好的提高緩存命中率的辦法)。

下面的查詢會(huì)在兩個(gè)地方查找一個(gè)用戶一個(gè)主用戶表、 一個(gè)長(zhǎng)時(shí)間不活躍的用戶表，不活躍用戶表的目的是為了實(shí)現(xiàn)更高效的歸檔。

SELECT id FROM users WHERE id = 123
UNION ALL
SELECT id FROM users_archived WHERE id = 123;

上述的查詢可以用用戶變量來(lái)優(yōu)化

SELECT GREATEST(@found := -1, id) AS id, 'users' AS which_tbl
FROM users WHERE id = 1
UNION ALL
SELECT id, 'users_archived'
FROM users_archived WHERE id = 1 AND @found IS NULL
UNION ALL
SELECT 1，'reset' FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;

其余案例略

附錄

《高性能MySQL》
Baron Schwartz, Peter Zaitsev, Vadim Tkachenko 著
寧海元 周振興 彭立勛 翟衛(wèi)祥 劉輝 譯文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-637826.html

到了這里，關(guān)于《高性能MySQL》——查詢性能優(yōu)化（筆記）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！