MYSQL高效能學習查詢心得

對於低效的查詢，分兩步分析很有效：

1.確認應用程式是否在檢索大量超過需要的資料。這通常意味著訪問了太多的行，但有時候也能是訪問了太多的列

2.確認MySQl伺服器層是否分析大量超過需要的資料行

消耗應用伺服器的CPU和記憶體資源

1.查詢不需要的記錄

2.多表關聯時返回全部列

3.總是取出全部列 （會讓優化器無法完成索引覆蓋掃描這類優化，還會為伺服器帶來額外的I/O,記憶體和CPU的消耗，優點：複用性高，有快取機制的情況下可以考慮）

4.重複查詢相同的資料

對於MySQL，最簡單的衡量查詢開銷的三個指標如下：

響應時間 掃描的行數 返回的行數

一般MySQL能夠使用如下三種方式應用where條件：從好到壞依次為：

1.在索引中使用where條件過濾不匹配的記錄。這是在儲存引擎完成的

2.使用索引覆蓋掃描來返回記錄（在Extra列中出現了Using index）來返回記錄，直接從索引中過濾不需要的記錄並返回命中的結果。這是在MySQL伺服器層完成的，但無需再回表查詢記錄

3從資料表中返回資料，然後過濾不滿足條件的記錄（在Extra列中出現Using where）。這在MySQL伺服器層完成，MySQL需要先從資料庫表讀出記錄然後過濾

如果發現需要掃描大量的資料但只返回少數的行，那麼通常可以嘗試下面的技巧去優化它

1.使用索引覆蓋掃描，把所有需要的列都放到索引中，這樣儲存引擎無須回表獲取對應行就可以返回結果了

2.改變庫表結構。例如使用單獨的彙總表

3.重寫這個複雜的查詢，讓MySQL優化器能夠以更優化的方式執行這個查詢

重構查詢的方式

1.一個複雜查詢還是多個簡單查詢

2.切分查詢

3.關聯分解

  ·讓快取的效率更高
   ·將查詢分解後，執行單個查詢可以減少鎖的競爭

  ·在應用層做關聯，可以更容易對資料庫進行拆分，更容易做到高效能和可擴充套件

  ·查詢本身效率也可能會有所提升

  ·可以減少冗餘記錄的查詢。在應用層做關聯查詢，意味著對於某條記錄應用只需要查詢一次，而

    在資料庫中做關聯查詢，則可能需要重複地訪問一部分資料。從這點看，這樣的重構還可能會減

    少網路和記憶體的消耗

  ·更進一步，這樣做相當於在應用中實現了雜湊關聯，而不是在使用MySQL的巢狀迴圈關聯

MySQL客戶端/伺服器通訊協議

通訊方式：半雙工

無法也無須將一個資訊切成小塊獨立來傳送

max_allowed_packet

當使用多數連線MySQL的庫函式從MySQL獲取資料時，其結果看起來都像是從MySQL伺服器獲取資料，而實際上都是從這個庫函式的快取獲取資料。多數情況下這沒什麼問題，但是如果需要返回一個很大的結果集的時候，這樣做並不好，因為庫函式會花很多時間和記憶體來儲存所有的結果集。如果能夠儘早開始處理這些結果集，就能大大減少記憶體的消耗，這種情況下可以不使用快取來記錄結果而是直接處理。這樣做的缺點是，對於伺服器來說，需要查詢完成後才能釋放資源，所以在和客戶端互動的整個過程中，伺服器的資源都是被這個查詢所佔用的。

查詢的狀態

Sleep：執行緒正在等待客戶端傳送新的請求

Query：執行緒正在執行查詢或者正在將結果傳送給客戶端

Locked：在MySQL伺服器層，該執行緒正在等待表鎖

Analyzing and statistics：執行緒正在收集儲存引擎的統計資訊，並生成查詢的執行計劃

Copying to tmp table：執行緒正在執行查詢，並且將其結果集都複製到一個臨時表中，這種狀態一般要麼是在做GROUP BY操作，要麼是檔案排序操作，或者是UNION操作

Sorting result：執行緒正在對結果集進行排序

Sending data：這表示多種情況：執行緒可能在多個狀態之間傳送資料，或者在生成結果集，或者在向客戶端返回資料

查詢優化

MySQL使用基於成本的優化，它將嘗試預測一個查詢使用某種執行是的成本，並選擇其中成本最小的一個。最初，成本的最小單位是隨機讀取一個4K資料頁的成本，後來變得更加複雜，並且引入了一些因子來估算某系操作的代價，如當知心一次where條件比較的成本。可以通過查詢當前會話的Last_query_cost的值來

得知MySQL計算的當前查詢的成本

優化策略可以簡單地分為兩種：靜態優化，動態優化

靜態優化：可以直接對解析樹進行分析，並完成優化

動態優化：則和查詢的上下文有關，也可能和很多其他因素有關

優化型別

重新定義關聯表的型別

將外連線轉化為內連線

使用等價變化規則

優化count(),min()和max()

預估並轉化為常數表示式

覆蓋索引掃描

子查詢優化

提前終止查詢

等價轉播

列表In()的比較

實際上，MySQL在優化階段就為每個表建立一個handler例項，優化器根據這些例項的介面可以獲取表的相關資訊，包括表的列名，索引的統計資訊，等等

關聯查詢優化（關聯表的順序不同）

排序優化（兩種演算法）

優化關聯查詢

>確保ON或者USING子句中的列上有索引。在建立索引的時候就要考慮到關聯的順序。當表A和表B用列c關聯的時候，如果優化器的關聯順序是B，A，那麼就不需要在B表的對應列上建索引

>確保任何的GROUP BY和OREDER BY中的表示式只涉及到一個表中的列，這樣M有SQL才有可能使用索引來優化這個過程

>當升級MySQL的時候需要注意：關聯語法，運算子優先順序等其他可能會發生變化的地方

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29012686/viewspace-1139935/，如需轉載，請註明出處，否則將追究法律責任。