高性能mysql
六：查詢優化
為什麼查詢會變慢
慢查詢基礎：優化資料通路
1，是否向資料庫請求了不想要的資料
查詢不需要的資料
多表關聯傳回全部列
總是取出全部列
重複查詢相同的資料
2，mysql是否在掃描額外的記錄
查詢開銷的三個名額：響應時間，掃描行數，傳回行數，通路類型（全表掃描，範圍掃描，索引掃描）
3，重構查詢方式
3.1一個複雜查詢還是多個簡單查詢
mysql在連接配接和斷開連結都很輕量級，在傳回一個小的查詢結果方面很高效，現在網絡速度快，是以有時候一個複雜查詢拆成多個簡單查詢是有必要的。
3.2切分查詢
有時候對于一個大查詢我們需要分而治之，将大查詢切分成小查詢，每個查詢功能完全一樣，隻是完成一小部分，每次隻傳回一小部分查詢結果。
3.3分解關聯查詢
對關聯查詢進行分解，拆成單表的簡單查詢，然後将查詢結果在程式中進行關聯。
優勢：
讓緩存的效率更高；
分解後，執行單個查詢可以減少鎖的競争；
在程式中做關聯更容易做到高性能和可擴充；
查詢本身效率也會有所提升，例如用in(id)代替關聯，可以讓mysql按照id順序進行查詢；
減少備援記錄的查詢；
更進一步，在程式中可以實作哈希關聯，并不是使用mysql的嵌套循環關聯，
3.4 查詢執行的基礎
mysql會做緩存
3.4.1 通信協定
mysql用戶端和伺服器之間的通信協定是“半雙共”的，這意味着在任何一個時刻，要麼由伺服器向用戶端發送資料，要麼由用戶端向伺服器發送資料，這兩個動作不能同僚發生。是以一次查詢的資料包不能太大， 不然會報錯，是以limit很重要，參數：max_allowed_packet配置最大資料包。
3.4.2查詢狀态
使用show full processlist指令（傳回結果中的command清單示目前狀态）
sleep：縣城正在等待用戶端發送新的請求
query：線程正在執行查詢或者正在将結果發送給用戶端
locked：在mysql服務層，該線程正在等待表鎖，在存儲引擎級别實作的鎖
analyzing and statistics：線程正在收集儲存引擎的統計資訊，并生成查詢的執行計劃。
copying to tmp table[on disk]：線程正在執行查詢，将結果複制到一個臨時表
sorting result：線程正在對結果集進行排序
sending data：線程可能在多個狀态之間傳送資料，或者在生成結果集，或者在向用戶端傳回資料。
3.4.2查詢緩存
在解析一個查詢語句之前，如果緩存打開的，mysql會優先檢查緩存。精确比對緩存（區分大小寫）
3.5 查詢優化處理
文法解析器和預處理
查詢優化器（最優執行計劃）
優化類型：
重新定義關聯表的順序，
将外連結轉成内連結，
使用等價變換規則，
優化count()/min()/max()，
預估并轉化為常數表達式，
覆寫索引掃描，
子查詢優化，
提前終止查詢，
等值傳播，
清單in()的比較:mysql将in()清單中的資料先進行排序，然後二分法判斷，in比or快
資料和索引的統計資訊：統計資訊由存儲引擎實作
mysql如何執行關聯查詢
union：先将一系列的單個查詢結果放在一個臨時表中，然後再重新讀取臨時表資料來完成union查詢。
mysql關聯執行的政策很簡單：mysql對任何關聯都執行嵌套循環關聯操作，既mysql先在一個表中循環取出單條資料，然後再嵌套到下一個表中尋找比對的行，依次下去，直到結束。然後根據各個表比對的行，傳回查詢中需要的各個列，mysql會嘗試在最後一個關聯表中找到所有比對的行，如果最後一個關聯表無法找到更多的行以後，mysql傳回到上一層關聯表，看是否能夠找到更多的比對記錄，依次類推。
在from遇到子查詢時，先限制子查詢并将其結果放在一個臨時表中。
執行計劃
mysql生成查詢的一顆指令樹，然後通過存儲引擎執行完成這顆指令樹并傳回結果，最終的執行計劃包含了重構查詢的全部資訊。
關聯查詢優化器
innor join會優先查詢表中資料少的那張表，然後再關聯嵌套其他表，并不是按照sql的順序
不過有時候優化器給出的并不是最優解，可以通過straight_join來重寫查詢，
例子：select straight_join a,b,from table
當需要關聯的表超過optimizer_search_depth（指定大小）的限制時，就會選擇貪婪搜尋模式，
排序優化
當有索引時用索引排序，沒有的話mysql會自己排序，資料量小在記憶體，大在磁盤，統稱為檔案排序。
兩種排序：
兩次傳輸排序（舊版使用）：讀取行指針和需要排序的字段，對其進行排序，需要從表中讀取兩次資料，兩次傳輸成本很高
單次傳輸排序（新版）：先查詢所需要的所有列，那後根據給定列進行排序
mysql在進行檔案排序的時候需要使用的臨時存儲空間可能會比想象的要大的多，原因是在于mysql在排序時，對每一個排序記錄都會配置設定一個足夠長的定長空間來存放
在關聯查詢時如果需要排序，mysql會分兩種情況來處理排序，如果order by字句中的所有列都來自關聯的第一個表，那麼mysql在關聯處理第一個表的時候就進行檔案排序，如果是這樣，那麼在mysql的explain結果中可以看到extra字段會有“using,filesort”，除次之外的所有情況，mysql都會先将關聯的結果存放在一個臨時表中，然後在所有關聯都結束後，再進行檔案排序，，在這種情況下，在expain結果的extra字段可以看到using temporary;using filsort。如果在查詢中有limit的話，limit也會在排序之後應用，是以即使需要傳回較少的資料，臨時表盒需要排序的資料量仍然會非常大。
自己總結：是以在關聯時盡量少排序，即便用了limit。
查詢執行引擎
傳回結果給用戶端
查詢執行的最後一個階段是将結果傳回給用戶端，如果查詢可以被緩存，那麼會将結果緩存，mysql将結果集傳回用戶端是一個增量，逐漸傳回的過程，好處：伺服器無須儲存太多的結果，也就不會因為傳回太多結果而消耗太多記憶體，另外，也讓mysql用戶端第一時間獲得傳回結果，結果集中的每一行都會以一個滿足通信協定的封裝包發送，再通過tcp協定進行傳輸，在tcp傳輸過程中，可能對mysql的封裝包進行緩存然後批量傳輸。
查詢優化器的局限性
關聯子查詢
mysql的子查詢實作的不好，最不好的事where條件中包含in的子查詢。是以建議使用exists()等效的改寫查詢來擷取更好的效率，
如何用好關聯子查詢
有時候left join 和exists效率差不多，在連結清單查詢時由于有重複資料需要group by和distinct時，子查詢exists會比連結清單更好，
優化關聯查詢
1，確定on或者using字句中的列有索引
2，確定任何的group by 和order by中的表達式隻涉及一個表的列
3，當更新mysql的時候需要注意：關聯文法，運算符優先級等可能會變化的地方。
優化子查詢
方法是盡可能使用關聯查詢代替
union的限制
如果希望union的各個字句都能夠根據limit隻取部分結果集，或者希望能夠先排好序再合并結果集的話，就需要在union的各個字句中分别使用這些字句，如果想在union之後全局進行排序取部分集時，那就要在全局加上order by 和limit
union優化
除非确實需要伺服器消除重複行，否則就一定使用union all，如果沒有all，mysql會給臨時表加上distinct 這會導緻對整個臨時表資料做唯一檢查，
等值傳遞
總結，in清單不能太多
mysql不能并行執行和哈希關聯松散索引掃描
最大值最小值優化
mysql對于最大值最小值，如果不是索引列的話，那麼會進行全表掃描，辦法是曲線救國，用limit來代替max()和min()
mysql不允許在同一個表上查詢和更新
優化特定類型的查詢
1，優化count()查詢
1.1統計列時要求列值是非空的（不統計null）
1.2count(*)并不會像我們想象的那樣擴充成所有列，實際上，他會忽略列直接統計行數，是以如果想知道結果集的行數時，最好使用count(*)
1.3 myisam的count(*)在沒有where條件時是最快的，
簡單的優化
使用近似值的優化，有時候不需要精确值，所有減少where條件來count
2，優化group by 和distinct
這兩個關鍵字字句中列最好是有索引的。
在mysql中，當無法使用索引的時候，group by使用兩種政策來完成：使用臨時表或者檔案排序來做分組。
優化group by with rollup
分組查詢的一個變種就是要求mysql對傳回的分組結果再做一次超級聚合，可以使用with rollup字句來實作這種邏輯，但是效率不好，建議還是在程式中做超級聚合更好。
3，優化limit分頁
延遲關聯
使用id來進行分頁