查詢性能優(yōu)化
深刻地理解MySQL如何真正地執(zhí)行查詢，并明白高效和低效的原因何在
為什么查詢速度會慢
查詢的生命周期（不完整）：從客戶端到服務(wù)器，然后服務(wù)器上進(jìn)行語法解析，生成執(zhí)行計(jì)劃，執(zhí)行，并給客戶端返回結(jié)果。
慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問
一條查詢，如果查詢得很慢，原因大概率是訪問的數(shù)據(jù)太多
對于低效的查詢，通過下面兩個(gè)步驟來分析總是很有效：
1. 確認(rèn)應(yīng)用程序是否在檢索大量且不必要的數(shù)據(jù)。這通常意味著訪問了多的行，但有時(shí)候也可能是訪問了太多的列。
2. 確認(rèn)MySQL服務(wù)器層是否在分析大量不需要的數(shù)據(jù)行。
一些導(dǎo)致慢查詢的操作：
- 查詢了不需要的記錄
- 多表聯(lián)接時(shí)返回全部列
- 總是取出全部列
- 重復(fù)查詢相同的數(shù)據(jù)
MySQL最簡單的衡量查詢開銷的三個(gè)指標(biāo)如下：
● 響應(yīng)時(shí)間
● 掃描的行數(shù)
● 返回的行數(shù)
如何判斷慢查詢
1.判斷響應(yīng)時(shí)間
概念
響應(yīng)時(shí)間其實(shí)有兩部分：服務(wù)時(shí)間和排隊(duì)時(shí)間。服務(wù)時(shí)間是指數(shù)據(jù)庫處理這個(gè)查詢真正花了多長時(shí)間。排隊(duì)時(shí)間是指服務(wù)器因?yàn)榈却承┵Y源而沒有真正執(zhí)行查詢的時(shí)間——可能是等I/O操作完成，也可能是等待行鎖，等等。
影響響應(yīng)時(shí)間的因素：
存儲引擎的鎖 （表鎖、行鎖）、高并發(fā)資源競爭、硬件響應(yīng)等
快速上限估計(jì)法：
用來估計(jì)響應(yīng)時(shí)間，了解這個(gè)查詢需要哪些索引以及它的執(zhí)行計(jì)劃是么，然后計(jì)算大概需要多少個(gè)順序和隨機(jī)I/O，再用其乘 以在具體硬件條件下一次I/O的消耗時(shí)間。最后把這些消耗都加起來，就可以獲得一個(gè)大概參考值來判斷當(dāng)前響應(yīng)時(shí)間是不是一個(gè)合理的值。
2.掃描的行數(shù)和返回的行數(shù)比例
理想情況下掃描的行數(shù)和返回的行數(shù)應(yīng)該是相同的，這種情況不多。
掃描的行數(shù)與返 回的行數(shù)的比率通常很低，一般在1：1到10：1之間。
3.掃描的行數(shù)和訪問類型
EXPLAIN語句中的type列反映了訪問類型。訪問類型有很多種，從全表掃描到索引掃描、 范圍掃描、唯一索引查詢、常數(shù)引用等。這里列出的這些，速度從慢到快，掃描的行數(shù)從 多到少。不需要記住這些訪問類型，但需要明白掃描表、掃描索引、范圍訪問和單值訪問的概念。
4.MySQL能夠使用如下三種方式應(yīng)用WHERE條件，從好到壞依次為：
● 在索引中使用WHERE條件來過濾不匹配的記錄。這是在存儲引擎層完成的。
● 使用索引覆蓋掃描（在Extra列中出現(xiàn)了Using index）來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結(jié)果。
● 從數(shù)據(jù)表中返回?cái)?shù)據(jù)，然后過濾不滿足條件的記錄
（在Extra列中出現(xiàn)Using
5.如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)但只返回少數(shù)行，要這樣改進(jìn)
● 使用覆蓋索引，把所有需要用的列都放到索引中，這樣存儲引擎無須回表獲取對應(yīng)行就可以返回結(jié)果了
● 改變庫表結(jié)構(gòu)。例如，使用單獨(dú)的匯總表（這是我們在第6章中討論的辦法）。
● 重寫這個(gè)復(fù)雜的查詢，讓MySQL優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個(gè)查詢（這是本章后續(xù)需要討論的問題）。
重構(gòu)查詢的方式
一個(gè)復(fù)雜查詢還是多個(gè)簡單查詢
MySQL從設(shè)計(jì)上讓連接和斷開連接都很輕量，在返回一個(gè)小的查詢結(jié)果方面很高效。現(xiàn)代的網(wǎng)絡(luò)速度比以前要快很多，能在很大程 度上降低延遲。
在某些版本的MySQL中，即使在一臺通用服務(wù)器上，也能夠運(yùn)行每秒超
過10萬次的簡單查詢，即使是一個(gè)千兆網(wǎng)卡也能輕松滿足每秒超過2000次的查詢。
所以運(yùn)行多個(gè)小查詢現(xiàn)在已經(jīng)不是大問題了。
在MySQL內(nèi)部，每秒能夠掃描內(nèi)存中上百萬行的數(shù)據(jù)
在其他條件都相同的時(shí)候，使用盡可能少的查詢當(dāng)然是更好的。但是有時(shí)候，將一個(gè)大查詢分解為多個(gè)小查詢是很有必要的。
切分查詢
定期清除大量數(shù)據(jù)時(shí)，如果用一個(gè)大的語句一次性完 成的話，則可能需要一次鎖住很多數(shù)據(jù)、占滿整個(gè)事務(wù)日志、耗盡系統(tǒng)資源、阻塞很多小
的但重要的查詢。將一個(gè)大的DELETE語句切分成多個(gè)較小的查詢可以盡可能小地影響MySQL的性能，同時(shí)還可以降低MySQL復(fù)制的延遲。
分解聯(lián)接查詢
用分解聯(lián)接查詢的方式重構(gòu)查詢有如下優(yōu)勢：
- 讓緩存的效率更高
- 將查詢分解后，執(zhí)行單個(gè)查詢可以減少鎖的競爭
- 查詢本身的效率也可能會有所提升。比如使用IN()代替聯(lián)接查詢，可以讓MySQL按照ID順序進(jìn)行查詢，這可能比隨機(jī)的聯(lián)接要更高效。
- 可以減少對冗余記錄的訪問
查詢執(zhí)行的基礎(chǔ)
MySQL執(zhí)行一個(gè)查詢的過程
1. 客戶端給服務(wù)器發(fā)送一條SQL查詢語句。
2. 服務(wù)器端進(jìn)行SQL語句解析、預(yù)處理，再由優(yōu)化器生成對應(yīng)的執(zhí)行計(jì)劃。
3. MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲引擎的API來執(zhí)行詢。
4. 將結(jié)果返回給客戶端
MySQL的客戶端/服務(wù)器通信協(xié)議
概念
MySQL的客戶端和服務(wù)器之間的通信協(xié)議是“半雙工”的，這意味著，在任 何時(shí)刻，要么是由服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是由客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。
理解
這樣設(shè)計(jì)的好處是讓MYSQL通信十分簡單快速,缺點(diǎn)是一旦一端開始發(fā)送消息，另一端要接收完整個(gè)消息才能響應(yīng)它。
緩存
多數(shù)連接MySQL的庫函數(shù)都可以獲得全部結(jié)果集并將結(jié)果緩存到內(nèi)存里，還可以逐行獲 取需要的數(shù)據(jù)。默認(rèn)一般是獲得全部結(jié)果集并將它們緩存到內(nèi)存中。
MySQL通常需要等所有的數(shù)據(jù)都已經(jīng)發(fā)送給客戶端才能釋放這條查詢所占用的資源，所以接收全部結(jié)果并緩存通?？梢詼p少服務(wù)器的壓力，讓查詢能夠早點(diǎn)結(jié)束、早點(diǎn)釋放相應(yīng)的資源。
查詢狀態(tài)
對于一個(gè)MySQL連接，或者一個(gè)線程，任何時(shí)刻都有一個(gè)狀態(tài)，該狀態(tài)表示了MySQL當(dāng) 前正在做什么。有很多種方式能查看當(dāng)前的狀態(tài)，最簡單的是使用SHOW FULL PROCESSLIST命令（該命令返回結(jié)果中的Command列，其就表示當(dāng)前的狀態(tài)）。
Sleep
線程正在等待客戶端發(fā)送新的請求。
Query
線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端。
Locked
在MySQL服務(wù)器層，該線程正在等待表鎖。在存儲引擎級別實(shí)現(xiàn)的鎖，例如InnoDB 的行鎖，并不會體現(xiàn)在線程狀態(tài)中。
Analyzing and statistics
線程正在檢查存儲引擎的統(tǒng)計(jì)信息，并優(yōu)化查詢。
Copying to tmp table [on disk]
線程正在執(zhí)行查詢，并且將其結(jié)果集復(fù)制到一個(gè)臨時(shí)表中，這種狀態(tài)一般要么是在做 GROUP BY操作，要么是在進(jìn)行文件排序操作，或者是在進(jìn)行UNION操作。如果這 個(gè)狀態(tài)后面還有“on disk”標(biāo)記，那表示MySQL正在將一個(gè)內(nèi)存臨時(shí)表放到磁盤上。
Sorting result
線程正在對結(jié)果集進(jìn)行排序。
了解這些狀態(tài)的基本含義非常有用，這可以讓你很快地了解當(dāng)前“誰正在持球”。在一個(gè)繁 忙的服務(wù)器上，可能會看到大量的不正常的狀態(tài)，例如，statistics正占用大量的時(shí)間。這 通常表示，某個(gè)地方有異常了。
MySQL如何對查詢進(jìn)行優(yōu)化
語法解析器和預(yù)處理
MySQL通過關(guān)鍵字將SQL語句進(jìn)行解析，并生成一棵對應(yīng)的“解析樹,MySQL解析器將進(jìn)行MySQL語法規(guī)則驗(yàn)證和解析查詢。它將驗(yàn)證是否使用了錯(cuò)誤的關(guān)鍵字，使用關(guān)鍵字的順序是否正確，或者它還會驗(yàn)證引號是否能前后正確匹配。
預(yù)處理器檢查生成的解析樹，以查找解析器無法解析的其他語義，例如，檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，還會解析名字和別名，看看它們是否有歧義。
下一步預(yù)處理器會驗(yàn)證權(quán)限。這通常很快，除非服務(wù)器上有非常多的權(quán)限配置。
此時(shí)解析樹是合法的了,然后,查詢優(yōu)化器將解析樹轉(zhuǎn)化成查詢執(zhí)行計(jì)劃
查詢優(yōu)化器
查詢優(yōu)化器會為一個(gè)查詢找到最好的執(zhí)行方式.
用什么來衡量"最好"呢?
MySQL使用基于成本的優(yōu)化器，它將嘗試預(yù)測一個(gè)查詢使用某種執(zhí)行計(jì)劃時(shí)的成本，并
選擇其中成本最小的一個(gè)。文章來源：http://www.zghlxwxcb.cn/news/detail-838337.html
優(yōu)化器在評估成本的 時(shí)候主要考慮每個(gè)表或者索引的頁面?zhèn)€數(shù)、索引的基 數(shù)（索引中不同值的數(shù)量）、索引和數(shù)據(jù)行的長度、索引分布情況。并不考慮任何層面的緩存帶來的影響，它假設(shè)讀取任何數(shù)據(jù)都需要一次磁盤I/O。文章來源地址http://www.zghlxwxcb.cn/news/detail-838337.html
到了這里，關(guān)于《高性能MYSQL》-- 查詢性能優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
