Mysql最佳化之語句及查詢最佳化

減少對Mysql的訪問

一、避免對同一資料做重複檢索：

應用中需要理清楚對資料庫的訪問邏輯，需要對相同表的訪問，盡量集中在相同sql訪問，一次提取結果，減少對資料庫的重複訪問。

二、使用mysql query cache：
作用：查詢快取儲存SELECT查詢的文本以及發送給用戶端的相應結果。如果隨後收到一個相同的查詢，伺服器從查詢快取中重新得到查詢結果，而不再需要解析和執行查詢。

適用範圍：不發生資料更新的表。當表更改（包括表結構和表資料）後，查詢快取值的相關條目被清空。

查詢快取的主要參數設定：

show variables like ‘%query_cache%’;
have_query_cache表明伺服器在安裝使已經配置了快取
query_cache_size表明緩衝區大小，單位為M
query_cache_type的變數值從0到2，含義分別為
0或者off（緩衝關閉）
1或者on（緩衝開啟，使用sql_no_cache的select除外）
2或者demand（只有帶sql_cache的select語句提供快取）

在 SHOW STATUS 中，你可以監視查詢快取的效能：
變數     含義
Qcache_queries_in_cache     在緩衝中登入的查詢數目
Qcache_inserts     被加入到緩衝中的查詢數目
Qcache_hits     緩衝採樣數數目
Qcache_lowmem_prunes     因為缺少記憶體而被從緩衝中刪除的查詢數目
Qcache_not_cached     沒有被緩衝的查詢數目 (不能被緩衝的，或由於 QUERY_CACHE_TYPE)
Qcache_free_memory     查詢快取的空閑記憶體總數
Qcache_free_blocks     查詢快取中的空閑記憶體塊的數目
Qcache_total_blocks     查詢快取中的塊的總數目

三、加cache層：
Cache（快取）、Memory（記憶體）、Hard disk（硬碟）都是資料存取單元，但存取速度卻有很大差異，呈依次遞減的順序。對於CPU來說，它可以從距離自己最近的Cache高速地存取資料，而不是從記憶體和硬碟以低幾個數量級的速度來存取資料。而Cache中所儲存的資料，往往是CPU要反覆存取的資料，有特定的機制（或程式）來保證Cache內資料的命中率（Hit Rate）。因此，CPU存取資料的速度在應用快取後得到了巨大的提高。

因為將資料寫入快取的任務由Cache Manager負責，所以對使用者來說快取的內容肯定是唯讀。需要你做的工作很少，程式中的SQL語句和直接存取DBMS時沒有分別，返回的結果也看不出有什麼差別。而資料庫廠商往往會在DB Server的設定檔中提供與Cache相關的參數，通過修改它們，可針對我們的應用最佳化Cache的管理。　

mysql最佳化語句最佳化

最佳化Insert語句
     1、如果你同時從同一客戶插入很多行，使用多個值表的INSERT語句。這比使用分開INSERT語句快(在一些情況中幾倍)。
    Insert into test values(1,2),(1,3),(1,4)…

2、如果你從不同客戶插入很多行，能通過使用INSERT DELAYED語句得到更高的速度。Delayed的含義是讓insert 語句馬上執行，其實資料都被放在記憶體的隊列中，並沒有真正寫入磁碟；這比每條語句分別插入要快的多；LOW_PRIORITY剛好相反，在所有其他使用者對錶的讀寫完後才進行插入；

3、將索引檔案和資料檔案分在不同的磁碟上存放（利用建表中的選項）；

4、如果進行批量插入，可以增加bulk_insert_buffer_size變數值的方法來提高速度，但是，這隻能對myisam表使用；

5、當從一個文字檔裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍；

6、根據應用情況使用replace語句代替insert；

7、根據應用情況使用ignore關鍵字忽略重複記錄。

最佳化Group By語句
預設情況下，MySQL排序所有GROUP BY col1，col2，....。查詢的方法如同在查詢中指定ORDER BY  col1，col2，...。如果顯式包括一個包含相同的列的ORDER BY子句，MySQL可以毫不減速地對它進行最佳化，儘管仍然進行排序。

如果查詢包括GROUP BY但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。例如：
    INSERT INTO foo
    SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

最佳化Order By  語句
在某些情況中，MySQL可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序。where條件和order by使用相同的索引，並且order by的順序和索引順序相同，並且order by的欄位都是升序或者都是降序。

例如：下列sql可以使用索引。
    SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
    SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
 
但是以下情況不使用索引：
①SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
--order by的欄位混合ASC和DESC

②SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
--用於查詢行的關鍵字與ORDER BY中所使用的不相同

③SELECT * FROM t1 ORDER BY key1, key2；
--對不同的關鍵字使用ORDER BY：

最佳化Join 語句

Mysql4.1開始支援SQL的子查詢。這個技術可以使用SELECT語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的串連(JOIN).. 替代。

假設我們要將所有沒有訂單記錄的使用者取出來，可以用下面這個查詢完成：
    SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
 
如果使用串連(JOIN).. 來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，效能將會更好，查詢如下：
    SELECT * FROM customerinfo
    LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
    WHERE salesinfo.CustomerID IS NULL
 
串連(JOIN).. 之所以更有效率一些，是因為 MySQL不需要在記憶體中建立暫存資料表來完成這個邏輯上的需要兩個步驟的查詢工作。

insert、update、delete的使用順序

MySQL還允許改變語句調度的優先順序，它可以使來自多個用戶端的查詢更好地協作，這樣單個用戶端就不會由於鎖定而等待很長時間。改變優先順序還可以確保特定類型的查詢被處理得更快。
我們首先應該確定應用的類型，判斷應用是以查詢為主還是以更新為主的，是確保查詢效率還是確保更新的效率，決定是查詢優先還是更新優先。

下面我們提到的改變調度策略的方法主要是針對Myisam儲存引擎的，對於Innodb儲存引擎，語句的執行是由獲得行鎖的順序決定的。
 
MySQL的預設的調度策略可用總結如下：

1.寫入操作優先於讀取操作。
2.對某張資料表的寫入操作某一時刻只能發生一次，寫入請求按照它們到達的次序來處理。
3.對某張資料表的多個讀取操作可以同時地進行。

MySQL提供了幾個語句調節符，允許你修改它的調度策略：
1.LOW_PRIORITY關鍵字應用於DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。
2.HIGH_PRIORITY關鍵字應用於SELECT和INSERT語句。
3.DELAYED關鍵字應用於INSERT和REPLACE語句。
 
如果寫入操作是一個LOW_PRIORITY（低優先順序）請求，那麼系統就不會認為它的優先順序高於讀取操作。在這種情況下，如果寫入者在等待的時候，第二個讀取者到達了，那麼就允許第二個讀取者插到寫入者之前。只有在沒有其它的讀取者的時候，才允許寫入者開始操作。這種調度修改可能存在LOW_PRIORITY寫入操作永遠被阻塞的情況。

SELECT查詢的HIGH_PRIORITY（高優先順序）關鍵字也類似。它允許SELECT插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優先順序更高。另外一種影響是，高優先順序的SELECT在正常的SELECT語句之前執行，因為這些語句會被寫入操作阻塞。

如果你希望所有支援LOW_PRIORITY選項的語句都預設地按照低優先順序來處理，那麼請使用--low-priority-updates選項來啟動伺服器。通過使用INSERT HIGH_PRIORITY來把INSERT語句提高到正常的寫入優先順序，可以消除該選項對單個INSERT語句的影響。