一岭埠、查詢?yōu)槭裁磿?huì)變慢匣椰？
需要明確的是編寫快速的slq，真正重要的是響應(yīng)時(shí)間
可以把查詢看做是一個(gè)任務(wù)橄镜，其中包含一系列子任務(wù)，要優(yōu)化查詢冯乘，實(shí)際上是要優(yōu)化子任務(wù)：
- 要么刪除某些子任務(wù)洽胶。
- 要么減少子任務(wù)執(zhí)行次數(shù)。
- 要么讓子任務(wù)執(zhí)行更快裆馒。
通常來(lái)講姊氓，msql查詢生命周期大致可以按照：從客戶端到服務(wù)器，然后服務(wù)器解析喷好、生成執(zhí)行計(jì)劃翔横、然后調(diào)用存儲(chǔ)引擎API執(zhí)行查詢，存儲(chǔ)引擎檢索梗搅、排序禾唁、分組處理，最后返回客戶端无切。在完成查詢?nèi)蝿?wù)時(shí)荡短，查詢?nèi)蝿?wù)需要在不同地方花費(fèi)時(shí)間：
- 網(wǎng)絡(luò)耗時(shí)
- CPU計(jì)算
- 生成統(tǒng)計(jì)信息和執(zhí)行計(jì)劃
- 鎖等待（互斥）
- 存儲(chǔ)引擎檢索數(shù)據(jù)
- 磁盤I/O
- 上下文切換等
每次出現(xiàn)消耗大量時(shí)間的查詢通常是出現(xiàn)一些不必要的額外操作、某些操作被執(zhí)行多次哆键、某操作執(zhí)行太慢掘托。優(yōu)化查詢的目的就是消除或減少這些操作花費(fèi)的時(shí)間。
二籍嘹、優(yōu)化數(shù)據(jù)訪問(wèn)
查詢性能最基本的原因是訪問(wèn)的數(shù)據(jù)過(guò)多闪盔。大部分性能地下的查詢都可以通過(guò)減少數(shù)據(jù)訪問(wèn)量的方式進(jìn)行優(yōu)化弯院。通過(guò)下面2種方法分析宗師很有效：
- 確認(rèn)程序是否存在檢索大量超過(guò)需要的數(shù)據(jù)。這通常意味著訪問(wèn)了太多行或列泪掀。
- mysql服務(wù)器是否存在分析大量超過(guò)需要的行听绳。
分析是否向數(shù)據(jù)庫(kù)請(qǐng)求了不需要的數(shù)據(jù)
有些查詢會(huì)會(huì)請(qǐng)求超過(guò)需要的數(shù)據(jù)，然后這些多余的數(shù)據(jù)會(huì)被應(yīng)用丟掉族淮。這會(huì)給mysql服務(wù)器帶來(lái)額外負(fù)擔(dān)辫红，并增加網(wǎng)絡(luò)開(kāi)銷，同時(shí)也會(huì)消耗內(nèi)存和CPU資源祝辣。
典型案例
查詢不必要記錄
常見(jiàn)的錯(cuò)誤就是會(huì)誤以為mysql只會(huì)返回需要的數(shù)據(jù)贴妻，需要開(kāi)發(fā)設(shè)計(jì)人員習(xí)慣使用這樣的設(shè)計(jì)，先使用select語(yǔ)句查詢大量數(shù)據(jù)蝙斜，然后獲取前面的N行后關(guān)閉結(jié)果集（例如查詢1000比商品信息名惩，然后前端頁(yè)面只展示前面10條）。開(kāi)發(fā)人員會(huì)任務(wù)msql只會(huì)查詢需要的10筆記錄孕荠，然后停止查詢娩鹉。實(shí)際mysql會(huì)檢索出全部的結(jié)果集，客戶端程序會(huì)接收全部結(jié)果集然后拋棄其中大部分?jǐn)?shù)據(jù)稚伍。最簡(jiǎn)單的優(yōu)化手段就是在這樣的查詢加上LIMIT弯予。
多表關(guān)聯(lián)時(shí)返回全部的列
在設(shè)計(jì)中會(huì)經(jīng)常看到類似這樣的sql个曙，查詢電影的演員表：
explain select * from actor inner join film_actor using(actor_id)
inner join film using(film_id) where film.title='ACE GOLDFINGER';
該查詢將返回三個(gè)表全部的數(shù)據(jù)锈嫩，在開(kāi)發(fā)中應(yīng)該避免出現(xiàn)這樣的sql。正確的查詢應(yīng)該是：
explain select actor.* from actor inner join film_actor using(actor_id)
inner join film using(film_id) where film.title='ACE GOLDFINGER';
總是取出所有列
每次看到select * 時(shí)都應(yīng)該審視垦搬，是否需要返回全部的列呼寸？實(shí)際應(yīng)該中大部分場(chǎng)景是不需要的。取出全部列猴贰，會(huì)讓優(yōu)化器無(wú)法使用覆蓋索引掃描這類優(yōu)化对雪，同時(shí)還會(huì)對(duì)服務(wù)器帶來(lái)額外I/O、CPU米绕、內(nèi)存網(wǎng)絡(luò)消耗瑟捣。尤其是應(yīng)用和數(shù)據(jù)庫(kù)服務(wù)器不是不是部署在一個(gè)節(jié)點(diǎn)，網(wǎng)絡(luò)開(kāi)銷就別人明顯了栅干。
重復(fù)查詢相同數(shù)據(jù)
在應(yīng)用中經(jīng)常會(huì)出現(xiàn)重復(fù)執(zhí)行相同查詢并返回相同結(jié)果蝶柿。比如一個(gè)投票應(yīng)用，投票者訪問(wèn)參賽者個(gè)人簡(jiǎn)介的時(shí)候可能會(huì)反復(fù)查詢這個(gè)數(shù)據(jù)非驮，比較好的方案就是初次查詢時(shí)將該數(shù)據(jù)緩存起來(lái)交汤，需要時(shí)從緩存取出，避免重復(fù)執(zhí)行數(shù)據(jù)庫(kù)訪問(wèn)。
分析是否在掃描額外的記錄
確定查詢只返回需要的數(shù)據(jù)后芙扎，接下來(lái)可以分析查詢是否掃描了不需要的記錄星岗。最簡(jiǎn)單的衡量查詢開(kāi)銷的三個(gè)指標(biāo)：
- 響應(yīng)時(shí)間
- 掃描行數(shù)
- 返回的行數(shù)
沒(méi)有那個(gè)指標(biāo)能夠完美衡量查詢開(kāi)銷，但它們大致反映了在數(shù)據(jù)庫(kù)中內(nèi)部執(zhí)行查詢需要掃描多少數(shù)據(jù)戒洼，并大致推算出查詢運(yùn)行時(shí)間俏橘。這三個(gè)指標(biāo)會(huì)記錄在慢查詢?nèi)罩局小?/p>
- 響應(yīng)時(shí)間
響應(yīng)時(shí)間是衡量查詢開(kāi)銷最重要指標(biāo)，響應(yīng)時(shí)間分為服務(wù)時(shí)間和排隊(duì)時(shí)間兩部分：服務(wù)時(shí)間是數(shù)據(jù)庫(kù)處理這個(gè)查詢真正花費(fèi)的時(shí)間圈浇，排隊(duì)時(shí)間是指服務(wù)器因?yàn)橘Y源等待沒(méi)有執(zhí)行真正查詢的時(shí)間-I/O等待寥掐、鎖等待等。
當(dāng)看到一個(gè)查詢響應(yīng)時(shí)間時(shí)磷蜀，首先需要分析響應(yīng)時(shí)間是否是一個(gè)合理的值（快速上限估計(jì)）召耘。 - 掃描行數(shù)和返回的行數(shù)
分析查詢時(shí)，查看該查詢掃描的行數(shù)和返回的行數(shù)是非常有幫助的褐隆。當(dāng)掃描行數(shù)與返回行數(shù)比值太大污它，在一定程度能夠說(shuō)明查詢找到需要的數(shù)據(jù)效率并不是太高。理想情況掃描行數(shù)和返回行數(shù)應(yīng)該相等庶弃，但實(shí)際不可能出現(xiàn)衫贬。比如關(guān)聯(lián)查詢，數(shù)據(jù)庫(kù)服務(wù)器必須掃描多行才能生成結(jié)果集中的一行歇攻。 - 掃描行數(shù)和訪問(wèn)類型
在評(píng)估查詢開(kāi)銷時(shí)固惯，需要考慮從數(shù)據(jù)庫(kù)表中找到一行數(shù)據(jù)的成本。mysql有多種訪問(wèn)方式可以查找并返回一行結(jié)果缴守。有些訪問(wèn)需要掃描多行才能返回一行結(jié)果缝呕，有的可能無(wú)需掃描就可以返回。explain語(yǔ)句中的type列反映了訪問(wèn)方式斧散。訪問(wèn)方式有多種： - 全表掃描（ALL）
- 索引掃描(index)
- 范圍掃描(range)
- 多列等值掃描(range)
- 唯一索引\主鍵(const)
- 常數(shù)引用
這些掃描方式速度是從慢到快，掃描的行數(shù)也是從大到小摊聋。
explain語(yǔ)句的Extra列反映where條件使用鸡捐，mysql提供三種方式應(yīng)用where條件：
- 在索引中使用where過(guò)濾不匹配的數(shù)據(jù)，這是在存儲(chǔ)引擎層完成的麻裁。
- 使用索引覆蓋掃描來(lái)返回記錄（Extra出現(xiàn)Using index）箍镜，直接從索引中過(guò)濾不需要的數(shù)據(jù)并返回命中的結(jié)果。這是在服務(wù)器層完成的煎源，但無(wú)需在回表查詢記錄色迂。
- 從數(shù)據(jù)表中返回?cái)?shù)據(jù)，然后使用where過(guò)濾不滿足條件的記錄（在Extra顯示 Using where）手销，這在mysql服務(wù)器層完成歇僧。
explain select last_name from actor;--使用覆蓋索引，雖然掃描了全部索引，但是無(wú)需回表查詢直接命中結(jié)果
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor index idx_actor_last_name 137 200 100.00 Using index
explain select * from actor where last_name like 'A%';--使用索引過(guò)濾
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor range idx_actor_last_name idx_actor_last_name 137 7 100.00 Using index condition
explain select * from actor where first_name like 'A%';-- 使用where在服務(wù)器層過(guò)濾
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor ALL 200 11.11 Using where
常見(jiàn)的優(yōu)化查詢需要掃描大量數(shù)據(jù)的技巧和方法:
- 使用索引覆蓋掃描诈悍，把所有需要查詢的列都放到索引中祸轮，這樣存儲(chǔ)引擎無(wú)需回表訪問(wèn)對(duì)應(yīng)的行就可以直接返回結(jié)果。
- 改變庫(kù)表結(jié)果侥钳，使用單獨(dú)的匯總表适袜。
- 重寫復(fù)雜的查詢。
三舷夺、重構(gòu)查詢方式
mysql在設(shè)計(jì)上讓連接和斷開(kāi)連接很輕量級(jí)苦酱，在返回一個(gè)很小的查詢結(jié)果方面很高效。mysql內(nèi)部每秒能夠掃描百萬(wàn)級(jí)數(shù)據(jù)给猾，相比之下mysql響應(yīng)數(shù)據(jù)給客戶端就慢的多疫萤。在其他情況都相同時(shí)，使用盡可能少的查詢是非常好的策略耙册。
切分查詢
有時(shí)候?qū)τ谝粋€(gè)大查詢我們需要分而治之给僵，將大查詢拆分為多個(gè)相同功能的小查詢，每個(gè)小查詢只完成一分部任務(wù)详拙。例如數(shù)據(jù)庫(kù)表復(fù)制帝际、清理數(shù)據(jù)等。如果用一個(gè)大查詢一次性完成的話饶辙，可能會(huì)一次性鎖定大量行蹲诀，贊滿整個(gè)事務(wù)日志、耗盡系統(tǒng)資源弃揽、阻塞很小但很重要的查詢等脯爪。
例如：每個(gè)月定期清理消息表數(shù)據(jù)
優(yōu)化前
delete from message where create_time < date_sub(now(),INTERVAL 3 MONTH);
優(yōu)化后
rows_affected = 0
do {
rows_affected = do_query(
"delete from message where create_time < date_sub(now(),INTERVAL 3 MONTH) limit 1000"
)} while rows_affected > 0
分解關(guān)聯(lián)查詢
很多高性能應(yīng)用都會(huì)對(duì)關(guān)聯(lián)查詢進(jìn)行分解。簡(jiǎn)單的就是多每個(gè)表進(jìn)行一次查詢矿微，然后在應(yīng)用程序中進(jìn)行關(guān)聯(lián)痕慢。用分解關(guān)聯(lián)的方式拆分查詢有如下優(yōu)勢(shì)：
- 讓緩存效率更高。應(yīng)用程序可以方便的緩存單表查詢對(duì)應(yīng)的結(jié)果涌矢。另外對(duì)于mysql的查詢緩存來(lái)說(shuō)掖举，如果關(guān)聯(lián)查詢某個(gè)表發(fā)生改變，那么msyql緩存就不能使用了娜庇，而拆分關(guān)聯(lián)查詢后塔次，經(jīng)常不發(fā)生改變的表的查詢就可以重復(fù)利用該表的緩存了。
- 將查詢分解后名秀，執(zhí)行單個(gè)查詢可以減少鎖競(jìng)爭(zhēng)励负。
- 在應(yīng)用層做數(shù)據(jù)管理，可以更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分匕得，更容易做到高性能和擴(kuò)展性继榆。
查詢效率本身也會(huì)提升，例如使用in()列表等值查詢，可以使msql根據(jù)ID順序查詢裕照，比使用隨機(jī)查詢高效的多攒发。 - 可以減少冗余記錄的查詢。在應(yīng)用層做關(guān)聯(lián)意味著某些記錄只需要查詢一次晋南，而做關(guān)聯(lián)查詢惠猿，可能需要重復(fù)訪問(wèn)一部分?jǐn)?shù)據(jù)。
在應(yīng)用中做關(guān)聯(lián)相當(dāng)于實(shí)現(xiàn)hash關(guān)聯(lián)负间，而不是msql的循環(huán)關(guān)聯(lián)偶妖。
四、查詢執(zhí)行的基礎(chǔ)
當(dāng)希望mysql能夠以更高性能運(yùn)行查詢時(shí)政溃，最好的辦法是弄清楚mysql是如何優(yōu)化和執(zhí)行查詢的趾访。
向mysql發(fā)起一個(gè)查詢請(qǐng)求，mysql執(zhí)行路徑以上如所示：
- 客戶端發(fā)起一條查詢給服務(wù)器董虱。
- 服務(wù)器先檢查緩存扼鞋，如果緩存命中，則立即返回緩存中結(jié)果愤诱，否則進(jìn)行下一步云头。
- 服務(wù)器進(jìn)行sql解析、預(yù)處理淫半，在由優(yōu)化器生成執(zhí)行計(jì)劃溃槐。
- mysql根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃調(diào)用存儲(chǔ)引擎API執(zhí)行查詢。
- 將結(jié)果返回客戶端科吭。
mysql客戶端/服務(wù)端通訊協(xié)議
mysql客戶端/服務(wù)端通訊協(xié)議是“半雙工”的昏滴，在任一時(shí)刻，要么是服務(wù)端向客戶端發(fā)送數(shù)據(jù)对人，要么是客戶端向服務(wù)端發(fā)送數(shù)據(jù)谣殊，兩個(gè)動(dòng)作不能同時(shí)發(fā)生。也無(wú)法將一個(gè)消息切成小塊獨(dú)立來(lái)發(fā)送牺弄。這種協(xié)議方式使得mysql通訊簡(jiǎn)單快速姻几，也從很多地方限制了mysql，一個(gè)明顯的限制就是mysql無(wú)法做流量控制：一端開(kāi)始發(fā)生消息猖闪，另一端必須接收整個(gè)完整消息才能相應(yīng)它。
客戶端請(qǐng)求
客戶端使用一個(gè)單獨(dú)的包將查詢傳輸給服務(wù)端肌厨。當(dāng)查詢特別長(zhǎng)時(shí)培慌，參數(shù)max_allowed_packet（5.7版本默認(rèn)4M）非常重要，如果查詢過(guò)大柑爸，服務(wù)端會(huì)拒絕接收更多數(shù)據(jù)并拋出相應(yīng)錯(cuò)誤吵护。
show variables like 'max_allowed_packet';
Variable_name Value
max_allowed_packet 4194304
4*1024*1024
服務(wù)端響應(yīng)
一般服務(wù)端響應(yīng)給客戶端數(shù)據(jù)通常非常多，由多個(gè)數(shù)據(jù)包組成。當(dāng)服務(wù)端向客戶端響應(yīng)數(shù)據(jù)時(shí)馅而，客戶端必須完整的接收整個(gè)返回結(jié)果祥诽，而不能被簡(jiǎn)單的只取前幾條記錄就讓服務(wù)端停止傳輸數(shù)據(jù)。這是為什么在必要的時(shí)候必須在查詢中加上LIMIT的原因瓮恭。mysql服務(wù)端在向客戶端傳輸數(shù)據(jù)時(shí)雄坪，實(shí)際是向客戶端推送數(shù)據(jù)的過(guò)程，客戶端沒(méi)法使服務(wù)端停止下來(lái)屯蹦。
查詢狀態(tài)
對(duì)于一個(gè)sql連接或者一個(gè)線程维哈，任何時(shí)刻都有一個(gè)狀態(tài)，該狀態(tài)表示了mysql目前正在做什么登澜±樱可以使用SHOW FULL PROCESSLIST 命令查看當(dāng)前連接的狀態(tài)。
show full processlist;
Id User Host db Command Time State Info
5 root localhost:10310 Sleep 271
6 root localhost:10311 sakila Query 0 starting show full processlist
mysql查詢狀態(tài)解釋：
- Sleep:線程正在等待客戶端發(fā)送新的請(qǐng)求脑蠕。
- Query:線程正在執(zhí)行查詢或者正在講結(jié)果發(fā)送給客戶端购撼。
- Locked：在服務(wù)器層，該線程正在等待表鎖谴仙。在存儲(chǔ)引擎級(jí)別實(shí)現(xiàn)的鎖迂求，比如InnoDB實(shí)現(xiàn)的行鎖，并不會(huì)體現(xiàn)在線程狀態(tài)中狞甚。
- Analyzing and statistics:線程正在收集存儲(chǔ)引擎的統(tǒng)計(jì)信息并生成存儲(chǔ)計(jì)劃锁摔。
- Copying to temp table:線程正在執(zhí)行查詢，并將結(jié)果都復(fù)制到一個(gè)臨時(shí)表哼审。這種狀態(tài)要么是在執(zhí)行g(shù)roup by谐腰、 要么是在執(zhí)行文件排序、要么是在執(zhí)行UNION操作涩盾。如果后面加上[on disk]十气，那么表示mysql正在講臨時(shí)表存儲(chǔ)到磁盤中。
- Sorting result:線程正在對(duì)結(jié)果集排序春霍。
- Sending data:線程可能在多個(gè)狀態(tài)之間傳送數(shù)據(jù)砸西，或者生成結(jié)果集，或者在向客戶端發(fā)送數(shù)據(jù)址儒。
了解這些狀態(tài)非常重要芹枷，可以通過(guò)這些狀態(tài)判斷當(dāng)前線程誰(shuí)在“持球”。
查詢緩存
在解析查詢語(yǔ)句前莲趣，如果查詢緩存是打開(kāi)的鸳慈，mysql會(huì)優(yōu)先檢查這個(gè)查詢是否命中查詢緩存中的數(shù)據(jù)，如果命中直接返回查詢緩存中的結(jié)果喧伞。檢查規(guī)則是根據(jù)大小敏感的哈希查找實(shí)現(xiàn)走芋。
查詢優(yōu)化處理
查詢?cè)谖疵芯彺婧蠹ɡ桑M(jìn)入服務(wù)器sql轉(zhuǎn)換為執(zhí)行計(jì)劃過(guò)程：包括解析sql、預(yù)處理翁逞、生成執(zhí)行計(jì)劃肋杖。過(guò)程出現(xiàn)任何錯(cuò)誤都可能終止查詢。
語(yǔ)法解析器和預(yù)處理器
- mysql首先通過(guò)關(guān)鍵字將sql語(yǔ)句進(jìn)行解析挖函，生成一顆對(duì)應(yīng)的“解析樹(shù)”状植，mysql解析器使用mysql語(yǔ)法規(guī)則驗(yàn)證和解析查詢。解析器主要驗(yàn)證sql關(guān)鍵字是否使用錯(cuò)誤或者關(guān)鍵字順序是否正確以及引號(hào)是否能夠前后正確匹配挪圾。
- 預(yù)處理器根據(jù)mysql規(guī)則進(jìn)一步驗(yàn)證“解析樹(shù)”是否合法：驗(yàn)證數(shù)據(jù)表和數(shù)據(jù)列是否存在浅萧，數(shù)據(jù)別名是否存在歧義，以及權(quán)限驗(yàn)證（這一步通常非痴芩迹快洼畅，除非服務(wù)器設(shè)置了許多復(fù)雜驗(yàn)證規(guī)則）。
查詢優(yōu)化器
sql語(yǔ)法檢查通過(guò)后棚赔，由優(yōu)化器將其轉(zhuǎn)換為sql執(zhí)行計(jì)劃帝簇，一個(gè)查詢可以有多種執(zhí)行方式，最后都返回相同結(jié)果靠益，優(yōu)化器的作用就是找到最合適的執(zhí)行計(jì)劃丧肴。
mysql使用基于成本的優(yōu)化器。將嘗試預(yù)測(cè)一個(gè)查詢使用某種計(jì)劃的成本胧后，并選擇一個(gè)成本最小的一個(gè)芋浮。優(yōu)化器基于存儲(chǔ)引擎的統(tǒng)計(jì)信息:每個(gè)表或索引的頁(yè)面?zhèn)€數(shù)、索引的基數(shù)（索引中不同值得數(shù)量）壳快、索引與數(shù)據(jù)行的長(zhǎng)度纸巷、索引的分布情況等來(lái)評(píng)估成本。優(yōu)化器在評(píng)估成本是不會(huì)考慮任何層面的緩存眶痰。
導(dǎo)致優(yōu)化器選擇錯(cuò)誤執(zhí)行計(jì)劃原因：
- 統(tǒng)計(jì)信息不齊備：比如InnoDB因?yàn)槠銶VCC架構(gòu)瘤旨，不可能維護(hù)一個(gè)數(shù)據(jù)表的精確統(tǒng)計(jì)信息。
- 執(zhí)行計(jì)劃成本估計(jì)不等于實(shí)際執(zhí)行的成本竖伯。
- mysql最優(yōu)和實(shí)際期待的最優(yōu)不一樣存哲。
- 優(yōu)化器不考慮其他并發(fā)的查詢。
優(yōu)化器不考慮一些不受控制的成本七婴，比如CPU計(jì)算祟偷、執(zhí)行存儲(chǔ)過(guò)程或自定義函數(shù)的消耗等。
mysql優(yōu)化策略簡(jiǎn)單可以劃分為兩種打厘，靜態(tài)優(yōu)化和動(dòng)態(tài)優(yōu)化修肠。靜態(tài)優(yōu)化直接對(duì)解析樹(shù)進(jìn)行分析并完成優(yōu)化。動(dòng)態(tài)優(yōu)化和查詢的上下文有關(guān)婚惫。在執(zhí)行語(yǔ)句或者存儲(chǔ)過(guò)程的時(shí)候氛赐，靜態(tài)優(yōu)化和動(dòng)態(tài)優(yōu)化的區(qū)別非常重要，mysql對(duì)查詢的靜態(tài)優(yōu)化在整個(gè)過(guò)程只執(zhí)行一次先舷，而動(dòng)態(tài)優(yōu)化則需要每次執(zhí)行時(shí)都重新評(píng)估艰管。
mysql能夠處理以下優(yōu)化類型：
重- 新定義關(guān)聯(lián)表的順序：數(shù)據(jù)關(guān)聯(lián)表查詢并不是總按sql指定的關(guān)聯(lián)順序進(jìn)行。決定關(guān)聯(lián)順序是優(yōu)化器很重要的一個(gè)功能蒋川。
- 外鏈接轉(zhuǎn)換內(nèi)連接
使用等價(jià)變換規(guī)則：mysql會(huì)使用等價(jià)變換規(guī)則簡(jiǎn)化并規(guī)范表達(dá)式牲芋。可以合并和減少一個(gè)比較以及移除一些恒成立和恒不成立的條件捺球。例如（1=1 and a > 5）將變換為a > 5缸浦。類似（a<b and b=c） and a=5 則會(huì)改寫成 b>5 and b=c and a=5。 - 優(yōu)化count()氮兵、min()裂逐、max():索引和列是否為空可以幫助優(yōu)化這些函數(shù)表達(dá)式。
- 預(yù)估并轉(zhuǎn)換為常數(shù)表達(dá)式：當(dāng)mysql檢測(cè)到一個(gè)表達(dá)式可以轉(zhuǎn)化為常數(shù)時(shí)泣栈，就會(huì)一直把該表達(dá)式當(dāng)做常數(shù)進(jìn)行優(yōu)化處理卜高。
- 覆蓋索引掃描：當(dāng)存在索引列覆蓋查詢所有列時(shí)，msyql會(huì)使用索引直接返回查詢結(jié)果南片。
- 子查詢優(yōu)化
- 提前終止查詢
- 等值傳播
列表in的比較：在很多數(shù)據(jù)庫(kù)系統(tǒng)in()完全等同于多個(gè)or條件掺涛，但是在mysql是完全不同等。mysql將in()列表中的數(shù)據(jù)先進(jìn)行排序疼进，然后使用二分查找的方式來(lái)確定列表中的值是否滿足條件薪缆，其查詢復(fù)雜度是O(logN)的操作，等價(jià)轉(zhuǎn)換為OR查詢的復(fù)雜度為O(N),對(duì)于In()列表中有大量取值的時(shí)候伞广，mysql處理速度回更快拣帽。
mysql關(guān)聯(lián)查詢執(zhí)行方式
在mysql中，任何一次查詢都是一次“關(guān)聯(lián)”—這不僅僅是一個(gè)查詢需要兩張表匹配才叫關(guān)聯(lián)赔癌。在處理關(guān)聯(lián)查詢時(shí)诞外，mysql會(huì)先將一系列單個(gè)查詢的結(jié)果放到一個(gè)臨時(shí)表中，然后在重新讀取臨時(shí)表中數(shù)據(jù)進(jìn)行關(guān)聯(lián)操作灾票。
mysql執(zhí)行關(guān)聯(lián)策略很簡(jiǎn)單：對(duì)任何關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作峡谊，即msyql先在一個(gè)表中循環(huán)取出單挑數(shù)據(jù)，然后在嵌套循環(huán)到下一張表匹配數(shù)據(jù)刊苍，依次下去直到找到表中所有匹配為止既们。然后根據(jù)匹配的行，返回查詢中需要的列正什。
執(zhí)行計(jì)劃
和大對(duì)數(shù)數(shù)據(jù)庫(kù)系統(tǒng)不同啥纸，mysql不會(huì)生成查詢字節(jié)碼來(lái)執(zhí)行查詢。msyql服務(wù)器生成一顆查詢指令樹(shù)婴氮，然后通過(guò)存儲(chǔ)引擎執(zhí)行完成這顆指令樹(shù)并返回結(jié)果斯棒。
排序優(yōu)化
排序是成本很高的操作盾致，從性能角度考慮，應(yīng)該盡量避免排序或者避免大數(shù)據(jù)的排序荣暮。當(dāng)不能使用索引排序時(shí)庭惜，mysql需要自己排序：數(shù)據(jù)量小的在內(nèi)存中排序，數(shù)據(jù)大的在磁盤中排序穗酥，mysql將這過(guò)程統(tǒng)一稱為文件排序护赊。
如果需要排序的數(shù)據(jù)量小于排序緩存區(qū)大小mysql則直接在內(nèi)存中快速排序。如果大于排序緩沖區(qū)砾跃，mysql會(huì)將數(shù)據(jù)分塊骏啰，然后對(duì)每個(gè)獨(dú)立的塊進(jìn)行排序，然后將各個(gè)塊排序結(jié)果存儲(chǔ)到磁盤抽高，再將各個(gè)塊的排序結(jié)果進(jìn)行合并判耕，最后返回排序結(jié)果捍掺。
查詢執(zhí)行引擎
在解析和優(yōu)化器階段蛤育，mysql服務(wù)器將sql生成對(duì)應(yīng)的執(zhí)行計(jì)劃，而存儲(chǔ)引擎則根據(jù)執(zhí)行計(jì)劃完成整個(gè)查詢操软。這里的執(zhí)行計(jì)劃是一種數(shù)據(jù)結(jié)構(gòu)（指令樹(shù)）而不是字節(jié)碼雏胃。
五请毛、msyql查詢優(yōu)化器的局限
msyql查詢優(yōu)化器不是萬(wàn)能，對(duì)少部分查詢不適用瞭亮。對(duì)于不適合的場(chǎng)景我們可以重構(gòu)改寫sql讓mysql高效的完成查詢方仿。
UNION限制
有時(shí)候mysql優(yōu)化器無(wú)法將限制條件從外層“下推”到內(nèi)層，這使得原本能夠限制內(nèi)層的條件無(wú)法應(yīng)該到內(nèi)層查詢的優(yōu)化中统翩。
如果希望在UNION個(gè)子查詢能夠根據(jù)LIMIT只取部分結(jié)果集或者希望能夠先排好序然后在合并結(jié)果集的話仙蚜，需要在每個(gè)個(gè)子句分別使用LIMIT或者排序。
比如
explain (select first_name,last_name from actor order by last_name)
union all
(select first_name,last_name from customer order by last_name) limit 20;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY actor ALL 200 100.00
2 UNION customer ALL 599 100.00
這個(gè)查詢會(huì)將actor的200條記錄和customer的599條記錄放到臨時(shí)表中厂汗，然后在LIMIT 20條記錄返回結(jié)果委粉。
對(duì)這個(gè)查詢的優(yōu)化是分別在兩個(gè)子查詢使用LIMIT 20減少臨時(shí)表的數(shù)據(jù)
explain (select first_name,last_name from actor order by last_name limit 20)
union all
(select first_name,last_name from customer order by last_name limit 20) limit 20;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY actor ALL 200 100.00 Using filesort
2 UNION customer ALL 599 100.00 Using filesort
索引合并優(yōu)化
mysql能夠訪問(wèn)單個(gè)表的多個(gè)索引以合并和交叉過(guò)濾的方式來(lái)定位需要查找的行，在合并過(guò)程需要消耗大量CPU計(jì)算和內(nèi)存資源娶桦。
等值傳遞
某些時(shí)候等值傳遞會(huì)出現(xiàn)意想不到的額外消耗贾节，比如當(dāng)有一個(gè)非常大的in()列表，而mysql優(yōu)化器發(fā)現(xiàn)存在where衷畦、using栗涂、on的子句，將這個(gè)列表的值和另一個(gè)表的某個(gè)列關(guān)聯(lián)祈争。優(yōu)化器會(huì)將in()列表的值復(fù)制應(yīng)該到關(guān)聯(lián)的表中斤程。通常如果in()列表小的時(shí)候，增加了過(guò)濾條件會(huì)提高效率菩混，但是當(dāng)in列表過(guò)大時(shí)忿墅，反而會(huì)導(dǎo)致查詢變慢扁藕。
無(wú)法并行執(zhí)行
mysql無(wú)法利用多核特性來(lái)并行執(zhí)行查詢?nèi)蝿?wù)。
最大值和最小值優(yōu)化
mysql對(duì)min()疚脐、max()的優(yōu)化做的并不好纹磺，可以看一個(gè)例子：
explain select min(actor_id) from actor where first_name like 'A%';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor ALL 200 11.11 Using where
因?yàn)閒irst_name上沒(méi)有索引，因此會(huì)執(zhí)行一次全表掃描亮曹。actor_id是actor表主鍵，理論上mysql讀到的第一個(gè)值就是我們需要的最小值了秘症，因?yàn)橹麈I是嚴(yán)格按照大小排序的照卦。但是實(shí)際mysql只會(huì)做全表掃描。比較曲線的優(yōu)化就是去掉min使用LIMIT
explain select actor_id from actor where first_name like 'A%' order by actor_id asc limit 1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor index PRIMARY 2 1 11.11 Using where
不允許在同一個(gè)表上查詢和更新
mysql不允許同一時(shí)刻在同一張表上同時(shí)進(jìn)行查詢和更新乡摹。
六役耕、查詢優(yōu)化器的提示
如果對(duì)查詢優(yōu)化器的優(yōu)化結(jié)果不滿意可以根據(jù)優(yōu)化器的幾個(gè)提示來(lái)控制最終執(zhí)行計(jì)劃。
- delayed:這個(gè)提示對(duì)insert和replace有效聪廉，mysql會(huì)將使用該提示的語(yǔ)句立即返回給客戶端瞬痘，并將插入的行數(shù)據(jù)放入緩沖區(qū)，然后在閑時(shí)批量執(zhí)行寫入板熊。日志系統(tǒng)或者客戶端不需要等待單條記錄完成I/O的應(yīng)用非常適合這個(gè)提示框全。需要注意并不是所有存儲(chǔ)引擎支持該提供，同時(shí)可能導(dǎo)致LAST_INSERT_ID()函數(shù)無(wú)法使用干签。
- straight_jion:這個(gè)提示可以放到select語(yǔ)句的select關(guān)鍵字后面津辩，也可以放到任何關(guān)聯(lián)表的前面。該提示有兩個(gè)作用：讓查詢中所有表安裝sql語(yǔ)句中的關(guān)聯(lián)順序進(jìn)行關(guān)聯(lián)以及固定前后兩個(gè)表的關(guān)聯(lián)順序容劳。當(dāng)mysql優(yōu)化器無(wú)法正確選擇關(guān)聯(lián)順序喘沿，或者關(guān)聯(lián)太多優(yōu)化器無(wú)法評(píng)估關(guān)聯(lián)順序時(shí)，straight_jion都很有用竭贩⊙劣。可以使用explain查詢關(guān)聯(lián)查詢順序和執(zhí)行效率，然后使用straight_jion提示固定查詢順序留量，在使用explain查詢執(zhí)行效率窄赋，選擇最佳的順序。
- sql_mall_result和sql_big_result：該提示只有對(duì)select語(yǔ)句有效楼熄，它告訴優(yōu)化器對(duì)group by和distinct查詢?nèi)绾问褂门R時(shí)表和排序寝凌。sql_mall_result告訴優(yōu)化器這個(gè)結(jié)果集很小，可以直接使用排序緩沖區(qū)排序孝赫，sql_big_result告訴優(yōu)化器這個(gè)結(jié)果集很大较木，直接使用磁盤臨時(shí)表做排序操作。
- sql_buffer_result：告訴優(yōu)化器將查詢放入臨時(shí)表青柄，然后盡可能釋放鎖伐债。
- sql_cache和sql_no_cache：告訴優(yōu)化器是否將查詢結(jié)果放入緩存预侯。
- for update和lock in share mode：這兩個(gè)提示不是優(yōu)化器提示，這兩個(gè)提示控制了select 語(yǔ)句的鎖機(jī)制峰锁，但只對(duì)行級(jí)鎖存儲(chǔ)引擎有效萎馅。
七、優(yōu)化特定類型查詢（優(yōu)化案例匯總）
count()查詢優(yōu)化
count()聚合函數(shù)虹蒋，是一個(gè)特殊函數(shù)糜芳，有兩種不同作用：
- 統(tǒng)計(jì)某個(gè)列值的數(shù)量或統(tǒng)計(jì)行數(shù)。統(tǒng)計(jì)列值時(shí)魄衅，要求列值非空（不統(tǒng)計(jì)null）峭竣。如果在count函數(shù)中指定了列的表達(dá)式，那么就是統(tǒng)計(jì)這個(gè)表達(dá)式有值得結(jié)果數(shù)晃虫。
- 統(tǒng)計(jì)結(jié)果集的行數(shù)皆撩。當(dāng)mysql確定統(tǒng)計(jì)的列值不可能為空時(shí)，實(shí)際上就是統(tǒng)計(jì)的行數(shù)哲银，最簡(jiǎn)單的就是count()的時(shí)候通配符并不會(huì)擴(kuò)展到每一列扛吞，實(shí)際上，mysql會(huì)忽略所有列而直接統(tǒng)計(jì)所有行荆责。
如果我們是希望統(tǒng)計(jì)行數(shù)滥比，那么直接使用count()，這樣寫更有意義做院，效率也更高守呜。 - 誤解MyISAM count()函數(shù)
MyISAM存儲(chǔ)引擎的count()函數(shù)不是總是很快的，只有當(dāng)沒(méi)有任何where條件時(shí)才是最快的山憨，因?yàn)楦鶕?jù)MyISAM存取引擎特性查乒，使用不帶where條件的count(*)函數(shù)直接可以獲得表的行數(shù)，而無(wú)需去統(tǒng)計(jì)郁竟。當(dāng)統(tǒng)計(jì)帶where條件的行數(shù)時(shí)玛迄，MyISAM和其他存儲(chǔ)引擎就沒(méi)任何區(qū)別了。 - 使用近似值
有時(shí)候某些業(yè)務(wù)并不需要精確的統(tǒng)計(jì)count值棚亩，這時(shí)可以使用近似值代替蓖议。explain出來(lái)的優(yōu)化器估算行數(shù)就是一個(gè)不錯(cuò)的近似值，執(zhí)行explain并不會(huì)真正執(zhí)行查詢讥蟆，所以成本很低勒虾。 - 更復(fù)雜優(yōu)化
使用count()函數(shù)本來(lái)就意味著需要掃描大量的行才能獲得精確的結(jié)果，是比較難優(yōu)化的瘸彤。在mysql層面還能做的就是使用覆蓋索引掃描修然。甚至修改應(yīng)用實(shí)現(xiàn)，增加匯總表或者引入外部緩存系統(tǒng)了。
優(yōu)化關(guān)聯(lián)查詢
確保using()和on子句上的列有索引愕宋。在創(chuàng)建索引是就要考慮關(guān)聯(lián)順序玻靡。當(dāng)表A、B使用列C關(guān)聯(lián)時(shí)中贝，當(dāng)關(guān)聯(lián)的順序是B囤捻、A，那么就不需要在B表上創(chuàng)建關(guān)聯(lián)字段的索引了邻寿。一般來(lái)說(shuō)蝎土，除非有其他理由，否則只應(yīng)該在關(guān)聯(lián)順序的第二個(gè)表的相應(yīng)列創(chuàng)建索引绣否。
確保group by和order by表達(dá)式只涉及到一個(gè)表的列誊涯，這樣的sql才能使用索引來(lái)優(yōu)化排序和組合的過(guò)程。
升級(jí)mysql版本時(shí)需要主要：關(guān)聯(lián)語(yǔ)法枝秤、運(yùn)算符優(yōu)先級(jí)等可能發(fā)生變化。
優(yōu)化group by 和distinct
mysql對(duì)這兩種查詢都使用相同方法優(yōu)化慷嗜，這兩種查詢都可以使用索引來(lái)優(yōu)化淀弹。當(dāng)無(wú)法使用索引時(shí)，group by使用兩種策略來(lái)優(yōu)化：使用臨時(shí)表或文件排序來(lái)分組庆械∞崩＃可以使用sql_big_result和sql_small_result提示告訴優(yōu)化器按照希望的方式運(yùn)行。
如果需要對(duì)關(guān)聯(lián)查詢進(jìn)行分組缭乘，并且是按照查找表的某列進(jìn)行分組沐序，那么采用查找表的標(biāo)識(shí)列分組會(huì)比其他列效率更高。
比如該查詢效率可能不會(huì)太好：
explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.first_name,actor.last_name;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor ALL PRIMARY 200 100.00 Using temporary; Using filesort
1 SIMPLE film_actor ref PRIMARY PRIMARY 2 sakila.actor.actor_id 27 100.00 Using index
explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by film_actor.actor_id;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor ALL PRIMARY 200 100.00 Using temporary; Using filesort
1 SIMPLE film_actor ref PRIMARY,idx_fk_film_id PRIMARY 2 sakila.actor.actor_id 27 100.00 Using index
使用關(guān)聯(lián)順序第二張表效率會(huì)更高
explain select first_name,last_name,count(*) from film_actor inner join actor using(actor_id) group by actor.actor_id;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor index PRIMARY,idx_actor_last_name PRIMARY 2 200 100.00
1 SIMPLE film_actor ref PRIMARY PRIMARY 2 sakila.actor.actor_id 27 100.00 Using index
優(yōu)化LIMIT分頁(yè)
在系統(tǒng)中進(jìn)行分頁(yè)操作時(shí)堕绩，我們通常使用LIMIT加上偏移量實(shí)現(xiàn)策幼，同時(shí)加上合適的order by子句。如果order by 可以實(shí)現(xiàn)索引奴紧，效率通常會(huì)不錯(cuò)特姐。如果不能使用索引那么只能使用文件排序。同時(shí)當(dāng)偏移量特別大時(shí)黍氮，即時(shí)使用了索引唐含，最后查詢效率也會(huì)變得很慢，比如limit 100000 10沫浆。對(duì)于這類場(chǎng)景要么是限制客戶分頁(yè)捷枯，要么是優(yōu)化大便宜量性能。
優(yōu)化分頁(yè)查詢最好的手段就是使用覆蓋索引掃描专执。然后在根據(jù)需要做一次關(guān)聯(lián)查詢獲取需要的記錄淮捆。
explain select * from film order by title asc limit 50, 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film ALL 1000 100.00 Using filesort
優(yōu)化后：
explain select * from film inner join (select film_id from film order by title asc limit 50, 5) t using(film_id) ;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL 55 100.00
1 PRIMARY film eq_ref PRIMARY PRIMARY 2 t.film_id 1 100.00
2 DERIVED film index idx_title 767 55 100.00 Using index
另外也可以通過(guò)計(jì)算邊界值的方式優(yōu)化LIMIT或者使用一些冗余表的方式。
優(yōu)化UNION
mysql總是創(chuàng)建并填充臨時(shí)表的方式來(lái)執(zhí)行UNION查詢，因?yàn)闆](méi)有過(guò)多優(yōu)化策略争剿。最主要的就是mysql無(wú)法將外部條件下沉到內(nèi)部已艰，因?yàn)樽硬樵冃枰哂嗟母髯詫?shí)現(xiàn)自己的過(guò)濾和排序等規(guī)則。另外除非必須消除重復(fù)的行蚕苇，否則應(yīng)該使用UNION ALL哩掺。
靜態(tài)查詢分析
Percona Toolkit中的pt-query-advior能夠解析查詢?nèi)罩尽⒎治霾樵兡Ｊ缴裕缓蠼o出所有可能存在潛在危險(xiǎn)的查詢嚼吞，然后給出足夠詳細(xì)建議。
