優(yōu)化性能需要查詢優(yōu)化、索引優(yōu)化、庫表結構優(yōu)化這三輛馬車齊頭并進。這篇文章主要圍繞查詢優(yōu)化，要對查詢進行優(yōu)化首先需要了解查詢是如何被執(zhí)行的，這一點在Mysql的邏輯架構一文中已經闡述了。

一、查詢的生命周期

性能是完成某任務的時間度量，也就是響應時間；優(yōu)化查詢性能就是提高查詢的響應速度。響應時間包括執(zhí)行時間和等待時間，等待時間又包括等待IO和等待鎖的時間。那么查詢的時間都花在哪兒了呢？我們可以通過show full processlist查看線程狀態(tài)進而查看查詢的生命周期：

1）Sleep：等待客戶端發(fā)送請求；2）Query：正在執(zhí)行查詢，或者正在返回結果給客戶端；3）Locked：在服務器層等待表鎖，等待InnoDB的行鎖并不會在此顯示；4）analysing & statistices：正在收集存儲引擎統計信息，生成執(zhí)行計劃；5）Copying to temp table(on disk)：正在執(zhí)行查詢并把結果復制到臨時表，在group by、文件排序和union等操作出現；6）sorting result：正在排序；7）Sending data：在多個狀態(tài)間傳送數據，或者正在生成結果集，或者向客戶端返回數據；

二、查詢優(yōu)化的思路

優(yōu)化我們的查詢語句需要考慮以下幾個點：

1）客戶端是否向數據庫請求了不需要的數據：1. 可使用limit減少返回的行；2. 可通過避免使用select * 減少返回的列，但有時select * 配合緩存總體性能也不錯；3. 通過緩存避免重復查詢相同的數據。

2）通過日志中記錄的掃描行數和返回行數，查看服務端是否掃描了不需要的數據。理想情況下，掃描行數等于返回行數；但聯表查詢時掃碼多行才能連結為一行返回，掃描行數會明顯大于返回行數。Where條件對應的3種處理方式，掃描行數從少到多依次是：1. 索引作為查詢條件，在存儲引擎層完成；2. 索引覆蓋掃描(using index)，服務層直接從索引中過濾掉不需要的數據；3. 服務層過濾不滿足條件的記錄(using where)。

3）單行訪問速度慢，最好讀取的數據塊包含盡可能多需要的數據行；小查詢性能不是問題：mysql采用線程緩存，使得建立/斷開連接更高效；現代網速也很給力。

三、重構查詢的方式

1）切分查詢：刪除過期數據時通常采用切分刪除方式，例如每月執(zhí)行一次任務，刪除最近一個月過期的數據，而不是每年執(zhí)行一次，刪除該年過期的數據。區(qū)別在于鎖定更少數據，從而減輕對其他線程的阻塞。

2）分解關聯查詢：詞條表tag、微博表post、關聯表tag_post，查找‘mysql’詞條對應的微博；可以用聯表查詢，也可以拆分成3條查詢：首先查詢‘mysql’對應的tagId，然后查詢tagid對應的post ids，最后找到post。拆分的好處在于：1. 緩存效率更高；2. 減少鎖競爭；3. 更容易對數據庫拆分，更容易做到高性能和可擴展，例如tag和post可以存儲在不同數據庫服務器；4. 查詢本身效率的提升，如用in代替關聯查詢；5. 減少掃描行數。

四、Mysql對低效查詢的處理方式

典型的低效查詢如聯表查詢，子查詢，聚合查詢等。

1）Union查詢：先將單個查詢結果放到臨時表，再從臨時表讀取數據做union。建議把where、limit、order by等子句下沉到union的子查詢，以便優(yōu)化器利用這些子句進行優(yōu)化。

2）from中的嵌套子查詢：先執(zhí)行子查詢并把結果放在臨時表；

3）聯表查詢的排序：建議order by中所有的列來自于同一張表；如果order by中所有列來自第一個表，則查詢第一張表時就進行排序，執(zhí)行計劃顯示using filesort；否則mysql把查詢結果放到臨時表，在關聯查詢結束后進行排序，執(zhí)行計劃顯示using temporary using filesort。

4）group by和distinct：mysql對兩種查詢用相同的方式處理。最有效的優(yōu)化方式是索引，如選用主鍵作為group by的列(分組列)；不使用索引則使用臨時表或文件排序來做分組。避免在select中使用非分組列。使用group by時，自動按照分組字段進行排序，可以在group by中指定asc/desc；也可以指定order by null告訴mysql不要排序。

5）自定義變量：通過set @var := (select * from tbl)設置自定義變量，并通過select * from tbl where col in @var等方式引用變量。變量采用動態(tài)類型，無法預設類型，只能通過賦值決定類型；使用未定義變量會在語法檢查階段報錯。變量在一個連接內有效；使用變量的查詢不能用緩存；不能在使用表名/列名的位置使用變量。

6）Left outer join：左表為準，左表行對應右表有值，則返回(left, right)，否則返回(left, null)；right join會被mysql等價改寫為left join。

7）Count聚合函數：count(列名)用于統計某個列值的數量，count(*)用于統計行數

8）文件排序優(yōu)化：當查詢列和排序列的總大小超過maxLengthForSortData、或涉及blob字段排序時，時采用two pass算法。否則，仍然使用single pass算法為所有參與排序的行創(chuàng)建固定大小的緩沖。必須排序blob/text時，推薦用前綴排序。

五、查詢優(yōu)化案例

5.1?用mysql構建一個隊列表

需求：用數據表維護任務對列，包含未處理任務、已處理任務和正在處理任務；調用方在數據表中查找未處理記錄，更新為正在處理，處理完成后更新為已處理。

問題和解決方案：1）伴隨隊列表越來越大，找到未處理記錄的速度變慢；解決方法是把已處理記錄歸檔到歷史表，從而始終保持對列列表很??；2）終極解決方案是把對列從數據庫遷移出來，用redis或者rabbitMQ實現

5.2?計算兩點之間到距離

需求：地圖應用中需要查找某個點附近所有的停車場；或者社交網站中查找周邊用戶。

問題和解決方案：查詢條件是一個復雜的計算方式，不僅無法使用索引，還會消耗CPU時間。把精確計算公式轉化為估算，使用lat between valueA and valueB的方式使用索引；如果能把between優(yōu)化為in則可以進一步優(yōu)化，因為索引只能支持最多一個范圍查找；把基于估算篩選出的少量數據再利用精算進行過濾。