mysql笔记03 查询性能优化
查詢性能優化
1. 為什么查詢速度會慢？
? ? 1). 如果把查詢看作是一個任務，那么它由一系列子任務組成，每個子任務都會消耗一定的時間。如果要優化查詢，實際上要優化其子任務，要么消除其中一些子任務，要么減少子任務的執行次數，要么讓子任務運行的更快。
? ? 2). 通常來說，查詢的生命周期大致可以按照順序來看：從客戶端，到服務器端，然后在服務器上進行解析，生成執行計劃，執行，并返回結果給客戶端。其中"執行"可以認為是整個生命周期中最重要的階段，這其中包括
? ? ? ? ?大量為了檢索數據到存儲引擎的調用以及調用后的數據處理，包括排序、分組等。
? ? 3). 在完成這些任務的時候，查詢需要在不同的地方花費時間，包括網絡，CPU計算，生成統計信息和執行計劃、鎖等待(互斥等待)等操作，尤其是向底層存儲引擎檢索數據的調用操作，這些調用需要在內存中操作、CPU操作
? ? ? ? ?和內存不足時導致的IO操作上消耗時間，根據上下文不同，可能會產生大量的上下文切換以及系統調用。
2. 慢查詢基礎：優化數據訪問
? ? 查詢性能低下最基本的原因是訪問的數據太多。某些查詢可能不可避免地需要篩選大量數據，但這并不常見。大部分性能低下的查詢都可以通過減少訪問的數量的方式進行優化。
? ? 對于低效查詢，可以通過下面兩個步驟來分析：
? ? 1). 確認應用程序是否在檢索大量超過需要的數據。這通常意味著訪問了太多的行，但有時候可能是訪問了太多的列。
? ? 2). 確認MySQL服務器層是否在分析大量超過需要的數據行。
? ? 2.1 是否向數據庫請求了不需要的數據
? ? ? ? ? 1). ?一些典型案例
? ? ? ? ? ? ? ? a. 查詢不需要的記錄：一個常見的錯誤是常常會誤以為MySQL會只返回需要的數據，實際上MySQL卻是返回全部結果集在進行計算。最簡單有效的解決方法是在這樣的查詢后面加上LIMIT。
? ? ? ? ? ? ? ? b. 多表關聯時返回全部列
? ? ? ? ? ? ? ? c. 總是取出全部列：每次看到SELECT * 的時候都需要用懷疑的眼光審視，是不是真的需要返回全部列？取出全部列會讓優化器無法完成索引覆蓋掃描這類優化，還會為服務器帶來額外的網絡、IO、內存和
? ? ? ? ? ? ? ? ? ? CPU的消耗。
? ? ? ? ? ? ? ? d. 重復查詢相同的數據：比較好的方案是，當初次查詢的時候將這個數據緩存起來，需要的時候從緩存中取出，這樣性能會更好。
? ? 2.2 MySQL是否在掃描額外的記錄：
? ? ? ? ? 1). 對于MySQL，最簡單的衡量查詢開銷的三個指標如下：
? ? ? ? ? ? ? ?a. 響應時間：響應時間是兩部分之和：服務時間和排隊時間。服務時間是指數據庫處理這個查詢真正花多長時間。排隊時間是指服務器因為等待某些資源而沒有真正執行查詢的時間--可能是等IO操作完成，也可能
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?是等待行鎖等等。
? ? ? ? ? ? ? ?b. 掃描的行數和返回的行數：分析查詢時，查看該查詢掃描的行數是非常有幫助的。這在一定程度上能夠說明該查詢找到需要的數據的效率高不高。
? ? ? ? ? ? ? ?c. 掃描的行數和訪問類型：在評估查詢開銷的時候，需要考慮一下從表中找到某一行數據的成本。MySQL有好幾種訪問方式可以查詢并返回一行結果。有些方式可能需要掃描很多行才能返回一行結果，也有些訪問
? ? ? ? ? ? ? ? ? 方式可能無需掃描就能返回結果。
? ? ? ? ? ? ? ? ? 在EXPALIN語句中的type列反應了訪問類型。訪問類型有很多種，從全表掃描到索引掃描、范圍掃描、唯一索引掃描、常數引用等。這里列的這些，速度是從慢到快，掃描的行數是從多到少。你不要記住這
? ? ? ? ? ? ? ? ? 些訪問類型，但需要明白掃描表、掃描索引、范圍訪問和單值訪問的概念。
? ? ? ? ? 2). 一般MySQL能使用如下三種方式應用WHERE條件，從好到壞依次為：
? ? ? ? ? ? ? ?a. 在?索引?中使用WHERE條件來過濾不匹配的記錄。這是在存儲引擎層完成的。
? ? ? ? ? ? ? ?b. 使用索引覆蓋掃描(在Extra列中出現Using index)來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結果。這是在MySQL服務器層完成的，但無需再回表查詢記錄。
? ? ? ? ? ? ? ?c. 從數據表中返回數據，然后過濾掉不滿足條件的記錄(在Extra列中出現Using Where)。這在MySQL服務器層完成，MySQL需要先從數據表讀取記錄然后過濾。
? ? ? ? ? 3). 如果發現查詢需要掃描大量的數據但只返回少數的行(使用聚合函數等)，那么通常可以嘗試下面的技巧去優化它們：
? ? ? ? ? ? ? ?a. 使用索引覆蓋掃描，把所有需要用的列都放到索引中，這樣存儲引擎無需回表獲取對應行就可以返回結果了。
? ? ? ? ? ? ? ?b. 改變庫表結構。例如使用單獨的匯總表。
? ? ? ? ? ? ? ?c. 重寫這個復雜的查詢，讓MySQL優化器能夠以更優的方式執行這個查詢。?
3. 重構查詢的方式：有時候，可以將查詢轉換一種寫法讓其返回一樣的結果，但性能更好。
? ? ?3.1 一個復雜查詢還是多個簡單查詢
? ? ? ? ? ?a. 設計查詢的時候一個需要考慮的重要問題是，是否需要將一個復雜的查詢分成過個簡單的查詢。在傳統實現中，總是強調需要數據庫層完成盡可能多的工作，這樣做的邏輯在于以前總是認為網絡通信、
? ? ? ? ? ? ? ?查詢解析和優化是一件代價很高的事情。但是這樣的想法對于MySQL并不適用，MySQL從設計上讓連接和斷開連接都很輕量級，在返回一個小的查詢結果方面很高效。現代的網絡速度比以前要快的多，
? ? ? ? ? ? ? ?無論是帶寬還是延遲。
? ? ? ? ? ?b. MySQL內部每秒能夠掃描內存中上百萬行數據，相比之下，MySQL響應數據給客戶端就慢得多了。在其他條件都相同的時候，使用盡可能少的查詢當然是更好的。但是有時候，將一個大查詢分解成
? ? ? ? ? ? ? ?多個小查詢也是很有必要的。
? ? ?3.2 切分查詢：刪除舊數據是一個很好的例子。定期清除大量數據時，如果用一個大的語句一次性刪除完成的話，則可能需要一次鎖住很多數據、占滿整個事務日志、耗盡系統資源、阻塞很多小的但很重要的查詢。
? ? ? ? ? ?同時需要注意，如果每次刪除數據后，都暫停一會再做下一次刪除，可以經服務器壓力分散到很長的時間段中。
? ? ?3.3 分解關聯查詢：
? ? ? ? ? 分解關聯查詢的方式重構查詢有如下的優勢：
? ? ? ? ? a. 讓緩存的效率更高。許多應用程序可以方便地使用緩存單表查詢對應的結果集。
? ? ? ? ? b. 將查詢分解后，執行單個查詢可以減少鎖的競爭。
? ? ? ? ? c. 在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展性。
? ? ? ? ? d. 查詢本身效率也可能會有所提升。
? ? ? ? ? e. 可以減少冗余記錄的查詢。管理查詢中可能需要重復地訪問一部分數據。
? ? ? ? ? f. 更進一步，這樣做相當于在應用中實現了哈希關聯，而不是使用MySQL的嵌套循環關聯。某些場景哈希關聯的效率要高很多。
4. 查詢執行的基礎：
? ? 查詢執行的過程：
? ? 1). 客戶端發送一條查詢給服務器
? ? 2). 服務器檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果。否則進入下一階段。
? ? 3). 服務器端進行SQL解析、預處理，再由優化器生成對應的執行計劃。
? ? 4). MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢。
? ? 5). 將結果返回給客戶端。?
? ? 4.1 MySQL客戶端/服務器通信協議：MySQL客戶端和服務器之間的通信協議是"半雙工"的，這意味著，在任何一個時刻，要么是由服務器向客戶端發送數據，要么是由客戶端向服務器發送數據，這兩個動作不能
? ? ? ? ? 同時發生，所以我們也無法將一個消息切換成小塊獨立來發送。
? ? ? ? ? 1). MySQL通常要等待所有的數據都已經發送給客戶端才能釋放這條查詢所占的資源，所以接收全部結果并緩存通常可以減少服務器的壓力(?)，讓查詢能夠早點結束、早點釋放相應的資源。
? ? ? ? ? 2). 查詢狀態：對于一個MySQL連接，或者說一個線程，任何時刻都有一個狀態，該狀態表示了MySQL當前正在做什么。有很多種方式能查看當前的狀態，最簡單的是使用SHOW FULL PROCESSLIST命令(
? ? ? ? ? ? ? ?該命令返回結果中的Command列就表示當前的狀態)。下面將這些狀態列出來，并做一個簡單的解釋：
? ? ? ? ? ? ? ?a. Sleep：線程正在等待客戶端發送新的請求。
? ? ? ? ? ? ? ?b. Query：線程正在執行查詢或者正在將結果發送給客戶端。
? ? ? ? ? ? ? ?c. Locked：在MySQL服務器層，該線程正在等待表鎖。
? ? ? ? ? ? ? ?d. Analyzing and statistics : 線程正在收集存儲引擎的統計信息，并生成查詢的執行計劃。
? ? ? ? ? ? ? ?e. Coping to tmp table [on disk]：線程正在執行查詢，并且將其結果都復制到一個臨時表中，這種狀態一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果這個狀態后面
? ? ? ? ? ? ? ? ? ?還有"on disk"標記，那表示MySQL正在講一個內存臨時表放到磁盤上。
? ? ? ? ? ? ? ?f. Sorting result：線程正在對結果集進行排序。
? ? ? ? ? ? ? ?g. Sending data：這表示多種情況：線程可能在對多個狀態之間傳輸數據，或者而在生成結果集，或者在向客戶端返回數據。
? ? ?4.2 查詢緩存：在解析一個查詢語句之前，如果查詢緩存時打開的，那么MySQL會優先檢查這個查詢是否命中查詢緩存中的數據。這個檢查是通過一個大小寫敏感的哈希查找實現的。查詢和緩存中的查詢即使只有
? ? ? ? ? ?一個字節不同，那也不會匹配緩存結果，這種情況下查詢會進入下一個階段處理。
? ? ?4.3 查詢優化處理：這個階段包括多個子階段：解析SQL、預處理、優化SQL查詢計劃。這個過程中任何錯誤(例如語法錯誤)都可能終止。
? ? ? ? ? ?a. 語法解析器和預處理：MySQL通過關鍵字將SQL語句進行解析，并生成一顆對應的"解析樹"，MySQL解析器將使用MySQL語法規則驗證和解析查詢。預處理則根據一些MySQL規則進一步檢查解析樹是否合法。
? ? ? ? ? ?b. 查詢優化器：一條查詢語可以有很多執行方式，最后都返回相同的結果。優化器的作用是找到這其中最好的執行計劃。MySQL使用基于成本的優化器，它將嘗試預測一個查詢使用某種執行時的成本，并選擇其中
? ? ? ? ? ? ? ?成本最小的一個。
? ? ? ? ? ?1). 有很多種原因會導致MySQL優化器選擇錯誤的執行計劃，如下所示：
? ? ? ? ? ? ? ? a. 統計信息不準確。MySQL依賴存儲引擎提供的統計信息來評估成本，但是有的存儲引擎提供的信息時不準確的，有的偏差可能非常大。例如：InnoDB因為其MVCC的機構，并不維護一個數據表的行數的精確信息。
? ? ? ? ? ? ? ? b. 執行計劃中的成本估算不等同于實際執行的成本。
? ? ? ? ? ? ? ? c. MySQL的最優可能和你想的最優不一樣。你可能希望執行時間盡可能的短，但是MySQL只是基于其成本模型選擇最優的執行計劃，而有些時候付這并不是最快的。
? ? ? ? ? ? ? ? d. MySQL從不考慮其他并發執行的查詢，這可能會影響到當前的查詢速度。
? ? ? ? ? ? ? ? f. MySQL也并不是任何時候都是基于成本的優化。
? ? ? ? ? ? ? ? g. MySQL不會考慮不受其控制的操作成本，例如執行存儲過程或者用戶自定義函數的成本。
? ? ? ? ? ? ? ? h. 優化器有時候無法去估算所有可能的執行計劃，所以他可能錯估實際上最優的執行計劃。
? ? ? ? ?2). 優化策略可以簡單的分為兩種：一種靜態優化，一種動態優化。
? ? ? ? ? ? ? a. 靜態優化可以直接對解析樹進行分析，并完成優化。靜態優化在第一次完成后就一直有效，即使使用不同的參數重復執行也不會發生變化。可以認為這是一種"編譯時優化"。
? ? ? ? ? ? ? b. 動態優化則和查詢的上下文有關，也可能和很多其他因素有關。例如WHERE條件中的取值、索引中條目對應的數據行數等。需要在每次查詢的時候重新評估，可以認為是一種"運行時優化"。
? ? ? ? ? ? ? c. MySQL對查詢的靜態優化只需要做一次，但對查詢的動態優化則在每次執行時都需要重新評估。有時候甚至在查詢的執行過程中也會重新優化。
? ? ? ? ?3). 下面是一些MySQL能夠處理的優化過程：
? ? ? ? ? ? ? ?a. 重新定義關聯表的順序：
? ? ? ? ? ? ? ?b. 將外連接轉換為內連接
? ? ? ? ? ? ? ?c. 使用等價變化規則：MySQL可以使用一些等價變化來簡化并規范表達式。它可以合并和減少一些比較，還可以移除一些恒成立和一些恒不成立的判斷。例如：(5=5 ADN a>5) --> a>5。
? ? ? ? ? ? ? ?d. 優化COUNT(),MIN()和MAX()：索引和列是否可為空通常可以幫助MySQL優化這類表達式。例如：要找到某一列的最小值，只需要查詢對應B-Tree索引最左端的記錄，MySQL可以直接獲取索引的最小行。
? ? ? ? ? ? ? ?e. 預估并轉化為常數表達式：當MySQL檢測到一個表達式可以轉化為常數的時候，就會一直把該表達式作為常數進行優化處理。
? ? ? ? ? ? ? ?f. 覆蓋索引掃描
? ? ? ? ? ? ? ?g. 子查詢優化
? ? ? ? ? ? ? ?h. 提前終止查詢：在發現已滿足查詢需求的時候，MySQL總是能夠立刻終止查詢。一個典型的例子是使用LIMIT。
? ? ? ? ? ? ? ?i. 等值傳播：USING(film_id)
? ? ? ? ? ? ? ?j. 列表IN()的比較：在很多數據系統中，IN()完全等同于多個OR條件的子句，因為這兩者是完全等價的。在MySQL中這點是不成立的，MySQL將IN()列表中的數據先進行排序，然后通過二分查找的方式來確定
? ? ? ? ? ? ? ? ? 列表中的值是否滿足條件，這是一個O(log n)復雜度的操作，等價地轉換成OR查詢的復雜度為O(n),對于IN()列表中有大量取值的時候，MySQL的處理速度將會更快。
? ? ? ? ?4). 數據和索引的統計信息：因為服務器層沒有任何統計信息，所以MySQL查詢優化器在生成查詢的執行計劃時，需要向存儲引擎獲取相應的統計信息。
? ? ? ? ?5). MySQL如何執行關聯查詢：當前MySQL關聯執行的策略很簡單：MySQL對任何關聯都執行嵌套循環操作，即MySQL先在一個表中循環取出單條數據，然后再嵌套循環到下一個表中尋找匹配的行，依次下去，直
? ? ? ? ? ? ? ?到所有表中匹配的行為止。然后根據各個表匹配的行，返回查詢中需要的各個列。MySQL會嘗試在最后一個關聯表中找到所有匹配的行，如果最后關聯表無法找到更多的行以后，MySQL返回到上一層次關聯表，
? ? ? ? ? ? ? ?看是否能夠找到更多匹配記錄，以此類推迭代執行。
? ? ? ? ?6). 關聯查詢優化器：MySQL優化器最重要的一部分就是關聯查詢優化，它決定了多個表關聯時順序。通常多表關聯的時候，可以有多種不同的關聯順序來獲得相同的執行結果。關聯查詢優化器則通過評估不同的順序
? ? ? ? ? ? ? 時的成本來選擇一個代價最小的關聯順序。優化器會將數據量少的表先進行查詢(個人認為第一個查詢的表越小，臨時表就越小？嵌套查詢，減少查詢次數？)。
? ? ? ? ?7). 排序優化：無論如何排序都是一個成本很高的操作，所以從性能角度考慮，應盡可能避免排序或者盡可能避免對大量數據進行排序。盡量通過索引進行排序。當不能使用索引生成排序結果的時候，MySQL需要自己
? ? ? ? ? ? ? 進行排序，如果數據量小則在內存中進行，如果數量大則需要使用磁盤，不過MySQL將這個過程統一稱為文件排序，即使完全是內存排序不需要任何磁盤文件時也是如此。
? ? ? ? ? ? ? MySQL有如下兩種排序算法：
? ? ? ? ? ? ? a. 兩次傳輸排序(舊版本使用)：讀取行指針和需要排序的字段，對其進行排序，然后再根據排序結果讀取所需要的數據行。需要進行兩次傳輸，即需要從數據表中讀取兩次數據，第二次讀取數據的時候，因為是讀
? ? ? ? ? ? ? ? ? 取排序列進行排序后的所有記錄。這回產生大量的隨機IO。
? ? ? ? ? ? ? b. 單次傳輸排序(新版本使用)：先讀取查詢所需要的所有列，然后在根據給定列進行排序，最后直接返回排序結果。效率更高，但占用內存更大。
? ? ? ? ? ? ? 如果查詢中有LIMIT的話，LIMIT也會在排序之后應用的，所以即使需要返回較少的數據，臨時表和需要排序的數據量仍然后非常大。貌似5.6版本有所改進，會先拋棄不滿足條件的記錄，然后再進行排序。
? ? 4.4 查詢執行引擎：在解析和優化階段，MySQL將生成查詢對應的執行計劃，MySQL的查詢執行引擎則根據這個執行計劃來完成整個查詢。這里執行計劃是一個數據結構，而不是和很多其他的關系型數據庫那樣會
? ? ? ? ? 生成對應的字節碼。?
? ? 4.5 返回結果給客戶端：MySQL將結果集返回客戶端是一個增量、逐步返回的過程。開始生成第一條結果時，MySQL就開始向客戶端逐步返回結果集了。
5. MySQL查詢優化器的局限性：
? ? 5.1 關聯子查詢：MySQL的子查詢實現非常糟糕(5.6版本以后有改進)。最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句。
? ? ? ? ? 1). 因為使用IN()加子查詢，性能經常會非常糟，所以通常建議使用EXISTS()等效的改寫查詢來獲取更好的效率。
? ? ? ? ? 2). 一般建議使用左外連接(LEFT OUTER JOIN)代替子查詢(?)。
? ? 5.2 UNION的限制：MySQL無法將限制條件從外層"下推"到內層，這使得原本能夠限制部分返回結果的條件無法應用到內層查詢的優化上。
? ? ? ? ? 例如如果希望UNION的各個子句能夠根據LIMIT只去部分結果集，或者希望能夠先排好序再合并結果集的話，就需要在UNION的各個子句中分別使用這些語句。
? ? ? ? ? (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;
? ? ? ? ? 優化后：
? ? ? ? ??(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;
? ? 5.3 當WHERE子句包含多個復雜條件的時候，MySQL能夠訪問單個表的多個索引以合并和交叉過濾的方式來定位需要查找的行。
? ? 5.4 等值查詢：某些時候，等值查詢會帶來一些意想不到額外消耗。例如：有一個非常大的IN()列表，而MySQ優化器發現存在WHERE、ON或者USING的子句。
? ? 5.5 并行執行：MySQL無法利用多核特性來并行執行查詢(貌似5.6以后有改進)。
? ? 5.6 哈希關聯：MySQL不支持哈希關聯。
? ? 5.7 松散索引掃描：MySQL并不支持松散索引掃描，也就無法按照不連續的方式掃描一個索引。通常，MySQL的索引掃描需要先定義一個起點和終點，即使需要的數據只是這段索引中的很少幾個，MySQL仍需掃描這段索引中
? ? ? ? ? 的每一個條目。
? ? 5.8 最大值和最小值優化：對于MIN()和MAX()查詢，MySQL的優化做的并不好。例如：
? ? ? ? ? SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOPE'
? ? ? ? ? 因為first_name上沒有索引，所以會進行全表掃描。如果MySQL能夠進行主鍵掃描，那么理論上，當MySQL讀到第一個滿足條件的記錄的時候，就是我們需要的最小值，因為主鍵是嚴格按照actor_id大小字段排序的。
? ? ? ? ? 一個曲線優化的辦法是移除MIN(),然后使用LIMIT來將查詢重寫。
? ?5.9 在同一個表上查詢和更新：MySQL不允許對同一張表同時進行查詢和更新。
6. 查詢優化器的提示(hint)：如果對優化器選擇的執行計劃不滿意，可以使用優化器提供的幾個提示(hint)來控制最終的執行計劃。
7. 優化特定類型的查詢
? ? 7.1 優化COUNT()查詢
? ? ? ? ? 1). COUNT()是一個特殊的函數，有兩種非常不同的作用：它可以統計某個列值的數量，也可以統計行數。在統計列值的時候要求列值是非空的(不統計NULL)。如果COUNT()的括號中指定了列或者列的表達式，則
? ? ? ? ? ? ? ?統計的就是這個表達式有值的結果數。最簡單的就是我們使用count(*)的時候，這種情況下通配符*并不會向我們猜想的那樣擴展所有的行，實際上，它會忽略所有的值而直接統計所有的行數。
? ? ? ? ? 2). 使用近似值：有時候某些業務場景并不要求完全精確的COUNT值，此時可以用近似值來代替。
? ? ? ? ? 3). 更復雜的優化：覆蓋索引，增加匯總表等。
? ? 7.2 優化關聯查詢：
? ? ? ? ? 1). 確保ON或者USING子句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表A和表B用到列C關聯的時候，如果優化器關聯順序是B、A，那就不需要在B表的對應列上建立索引。沒有用到的索引只會
? ? ? ? ? ? ? ?帶來額外的負擔。一般來說，除非有其他理由，否則只需要在關聯順序中的第二個表的相應列上創建索引。
? ? ? ? ? 2). 確保任何的GROUP BY 和ORDER BY中的表達式只涉及到一個表中的列。這樣MySQL才有可能使用索引來優化這個過程。
? ? 7.3 優化子查詢：關于優化子查詢我們給出的最重要的優化建議就是盡可能使用關聯查詢代替，至少當前MySQL版本需要這樣。
? ? 7.4 優化GROUP BY和DISTINCT：
? ? ? ? ? 1). 它們都可以使用索引來優化，這也是最有效的方法。
? ? ? ? ? 2). 在MySQL中，當無法使用索引的時候，GROUP BY使用兩種策略來完成：使用臨時表或文件排序來做分組。對于任何查詢語句，這兩種策略的性能都有可以提升的地方。可以通過使用提示SQL_BIG_RESULT和
? ? ? ? ? ? ? ?SQL_SMALL_RESULT來讓優化器按你希望的方式運行。
? ? ? ? ? 3). 如果需要對關聯查詢分組(GROUP BY)，并且是按照查找表中的某個列進行分組，那么通常采用查找表的標識列分組的效率比其他列更高。
? ? ? ? ? 4). 如果沒有通過ORDER BY子句顯式地指定排序列，當查詢使用GROUP BY 子句的時候，結果集會自動按照分組的列進行排序。如果不關心結果集的順序，而這中默認排序又導致了需要文件排序，則可以使用
? ? ? ? ? ? ? ?ORDER BY NULL，讓MySQL文件不再進行排序。也可以在GROUP BY子句中直接使用DESC或者ASC關鍵字，使分組的結果集按照需要的方向排序。
? ? ? ? ? 5). 優化GROUP BY WITH ROLLUP：分組查詢的一個變種思想就是要求MySQL對返回的分組結果再做一次超級聚合。最好的辦法盡可能的將WITH ROLLUP 功能轉移到應用程序中處理。
? ? 7.5 優化LIMIT分頁：
? ? ? ? ? 1). 使用索引
? ? ? ? ? 2). 要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。
? ? ? ? ? 3). 盡肯能的使用索引覆蓋
? ? ? ? ? 4). 延遲關聯
? ? ? ? ? 5). 有時候也可以將LIMIT查詢轉換為已知位置的查詢，讓MySQL通過范圍掃描找到對應的結果。
? ? ? ? ? 6). 其他優化辦法還包括使用預先計算的匯總表，或者關聯一個冗余表，冗余表只包含主鍵列和需要做排序的數據列。
? ? 7.6 優化SQL_CALC_FOUND_ROWS：分頁的時候，另一個常用的技巧是在LIMIT語句中加上SQL_CALC_FOUND_ROWS提示(hint)，這樣就可以獲得去掉LIMIT以滿足條件的行數，因此可以作為分頁的總數。
? ? ? ? ? 用業務的手段解決：下一頁，獲取更多數據等。
? ? 7.7 優化UNION查詢：
? ? ? ? ? 1). MySQL總是通過創建填充臨時表的方式來執行UNION查詢。因此很多優化策略在UNION查詢中都沒法很好地使用。經常需要手工地將WHERE,LIMIT,ORDER BY等子句"下推"到UNION的各個子查詢中，以
? ? ? ? ? ? ? ?便優化器可以充分利用這些條件進行優化。
? ? ? ? ? 2). 除非確實需要服務器消除重復的行，否則就一定要使用UNION ALL，這一點很重要。如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這回導致對臨時表做唯一性檢查。這樣做的代價非常高，
? ? ? ? ? ? ? ?即使有ALL關鍵字，MySQL仍然會使用臨時表存儲結果。事實上，MySQL總是經結果放入臨時表，然后再讀出，再返回給客戶端。
? ? 7.8 靜態查詢分析：Percona Toolkit中的pt-query-advisor 能夠解析查詢日志、分析查詢模式，然后再給出所有可能存在的潛在問題的查詢，并給出足夠詳細的建議。這像是給MySQL所有的查詢做一次全面的健康
? ? ? ? ? 檢查，它能檢測出很多問題。
? ? 7.9 用戶自定義變量：
8. 一般，我們要盡量避免使用SELECT_FOR_UPDATE。不光是隊列表，任何情況下都要盡量避免。
9. 需要處理一種特殊的情況：那些正在被進程處理，而進程本身卻由于某種原因退出的情況。這種情況處理起來很簡單。你只需要定期運行UPDATE語句將它都更新成原始狀態就可以了，然后執行SHOW PROCESSLIST，
? ? 獲得當前正在工作的線程ID，并使用一些WHERE條件避免取到那些剛開始處理的進程。
?
?
?
1. 是否向數據庫請求了不需要的數據
? ??a. 查詢不需要的記錄：一個常見的錯誤是常常會誤以為MySQL會只返回需要的數據，實際上MySQL卻是返回全部結果集在進行計算。最簡單有效的解決方法是在這樣的查詢后面加上LIMIT。
? ? b. 多表關聯時返回全部列
? ? c. 總是取出全部列：每次看到SELECT * 的時候都需要用懷疑的眼光審視，是不是真的需要返回全部列？取出全部列會讓優化器無法完成索引覆蓋掃描這類優化，還會為服務器帶來額外的網絡、IO、內存和CPU的消耗。
? ? d. 重復查詢相同的數據：比較好的方案是，當初次查詢的時候將這個數據緩存起來，需要的時候從緩存中取出，這樣性能會更好。
2. MySQL是否在掃描額外的記錄：
? ? 1). 一般MySQL能使用如下三種方式應用WHERE條件，從好到壞依次為：
? ? ? ? ??a. 在索引中使用WHERE條件來過濾不匹配的記錄。這是在存儲引擎層完成的。
? ? ? ? ? b. 使用索引覆蓋掃描(在Extra列中出現Using index)來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結果。這是在MySQL服務器層完成的，但無需再回表查詢記錄。
? ? ? ? ? c. 從數據表中返回數據，然后過濾掉不滿足條件的記錄(在Extra列中出現Using Where)。這在MySQL服務器層完成，MySQL需要先從數據表讀取記錄然后過濾。
? ? 3). 如果發現查詢需要掃描大量的數據但只返回少數的行(使用聚合函數等)，那么通常可以嘗試下面的技巧去優化它們：
? ? ? ? ? a. 使用索引覆蓋掃描，把所有需要用的列都放到索引中，這樣存儲引擎無需回表獲取對應行就可以返回結果了。
? ? ? ? ? b. 改變庫表結構。例如使用單獨的匯總表。
? ? ? ? ? c. 重寫這個復雜的查詢，讓MySQL優化器能夠以更優的方式執行這個查詢。?
3. 重構查詢的方式：切分查詢 ，分解關聯查詢
4. 數據庫連接池：預編譯
5. 優化特定類型的查詢
? ??1). 優化COUNT()查詢
? ? ? ? ? 1). COUNT()是一個特殊的函數，有兩種非常不同的作用：它可以統計某個列值的數量，也可以統計行數。在統計列值的時候要求列值是非空的(不統計NULL)。如果COUNT()的括號中指定了列或者列的表達式，則
? ? ? ? ? ? ? ?統計的就是這個表達式有值的結果數。最簡單的就是我們使用count(*)的時候，這種情況下通配符*并不會向我們猜想的那樣擴展所有的行，實際上，它會忽略所有的值而直接統計所有的行數。
? ? ? ? ? 2). 使用近似值：有時候某些業務場景并不要求完全精確的COUNT值，此時可以用近似值來代替。
? ? ? ? ? 3). 更復雜的優化：覆蓋索引，增加匯總表等。
? ??2). 優化關聯查詢(確保關聯查詢中可以使用索引)：
? ? ? ??? 1). 確保ON或者USING子句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表A和表B用到列C關聯的時候，如果優化器關聯順序是B、A，那就不需要在B表的對應列上建立索引。沒有用到的索引只會
? ? ? ? ? ? ? ?帶來額外的負擔。一般來說，除非有其他理由，否則只需要在關聯順序中的第二個表的相應列上創建索引。
? ? ? ? ??2). 確保任何的GROUP BY 和ORDER BY中的表達式只涉及到一個表中的列。這樣MySQL才有可能使用索引來優化這個過程。
? ?? 3). 優化子查詢：關于優化子查詢我們給出的最重要的優化建議就是盡可能使用關聯查詢代替，至少當前MySQL版本需要這樣。
? ?? 4). 優化GROUP BY和DISTINCT：
? ? ? ? ? 1). 它們都可以使用索引來優化，這也是最有效的方法。
? ? ? ? ? 2). 在MySQL中，當無法使用索引的時候，GROUP BY使用兩種策略來完成：使用臨時表或文件排序來做分組。對于任何查詢語句，這兩種策略的性能都有可以提升的地方。可以通過使用提示SQL_BIG_RESULT和
? ? ? ? ? ? ? ?SQL_SMALL_RESULT來讓優化器按你希望的方式運行。
? ? ? ? ? 3). 如果需要對關聯查詢分組(GROUP BY)，并且是按照查找表中的某個列進行分組，那么通常采用查找表的標識列分組的效率比其他列更高。
? ? ? ? ??4). 如果沒有通過ORDER BY子句顯式地指定排序列，當查詢使用GROUP BY 子句的時候，結果集會自動按照分組的列進行排序。如果不關心結果集的順序，而這中默認排序又導致了需要文件排序，則可以使用
? ? ? ? ? ? ? ?ORDER BY NULL，讓MySQL文件不再進行排序。也可以在GROUP BY子句中直接使用DESC或者ASC關鍵字，使分組的結果集按照需要的方向排序。
? ? ? ? ??5). 優化GROUP BY WITH ROLLUP：分組查詢的一個變種思想就是要求MySQL對返回的分組結果再做一次超級聚合。最好的辦法盡可能的將WITH ROLLUP 功能轉移到應用程序中處理。
? ? ?5). 優化LIMIT分頁：
? ? ? ? ??1). 使用索引
? ? ? ? ? 2). 要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。
? ? ? ? ??3). 盡肯能的使用索引覆蓋
? ? ? ? ? 4). 延遲關聯
? ? ? ? ??5). 有時候也可以將LIMIT查詢轉換為已知位置的查詢，讓MySQL通過范圍掃描找到對應的結果。
? ? ? ? ??6). 其他優化辦法還包括使用預先計算的匯總表，或者關聯一個冗余表，冗余表只包含主鍵列和需要做排序的數據列。
? ? ?6). 優化SQL_CALC_FOUND_ROWS：分頁的時候，另一個常用的技巧是在LIMIT語句中加上SQL_CALC_FOUND_ROWS提示(hint)，這樣就可以獲得去掉LIMIT以滿足條件的行數，因此可以作為分頁的總數。
? ? ? ? ? 用業務的手段解決：下一頁，獲取更多數據等。
? ? ?7). 優化UNION查詢：
? ? ? ? ? 1). MySQL總是通過創建填充臨時表的方式來執行UNION查詢。因此很多優化策略在UNION查詢中都沒法很好地使用。經常需要手工地將WHERE,LIMIT,ORDER BY等子句"下推"到UNION的各個子查詢中，以
? ? ? ? ? ? ? ?便優化器可以充分利用這些條件進行優化。
? ? ? ? ??2). 除非確實需要服務器消除重復的行，否則就一定要使用UNION ALL，這一點很重要。如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這回導致對臨時表做唯一性檢查。這樣做的代價非常高，
? ? ? ? ? ? ? ?即使有ALL關鍵字，MySQL仍然會使用臨時表存儲結果。事實上，MySQL總是經結果放入臨時表，然后再讀出，再返回給客戶端。
? ? ?8). ?靜態查詢分析：Percona Toolkit中的pt-query-advisor 能夠解析查詢日志、分析查詢模式，然后再給出所有可能存在的潛在問題的查詢，并給出足夠詳細的建議。這像是給MySQL所有的查詢做一次全面的健康
? ? ? ? ? ?檢查，它能檢測出很多問題。
5. 是否使用查詢緩存，以及怎么使用查詢緩存。多插入時，禁用查詢緩存
6. 批量插入
?
轉載于:https://www.cnblogs.com/Jtianlin/p/5154456.html
總結
以上是生活随笔為你收集整理的mysql笔记03 查询性能优化的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： LeetCode77:Combinati
- 下一篇： FZU 2150 Fire Game b