mysql最佳化之查詢最佳化

標籤：

 

Posted by Money Talks on 2012/02/24 |

第一篇 序章
第二篇 串連最佳化
第三篇 索引最佳化
第四片 查詢最佳化
第五篇 到實戰中去

查詢最佳化

查詢最佳化涉及到使用者查詢資料時使用到的索引、排序、group 等操作，以及其書寫習慣。(原文連結http://ddbiz.com/?p=990)

1. 影響查詢的伺服器參數調整

   除了使用者的查詢習慣外，在整個資料查詢的處理期間，mysql伺服器中有些參數同樣影響這些查詢的執行，比如暫存資料表、臨時檔案等。

   1. max_heap_table_size

      命令列參數: –max_heap_table_size=#
      ini/cnf定義: max_heap_table_size
      mysql 變數: max_heap_table_size
      全域變數，可動態調整, 預設值為16M, 可調整範圍32bitOS 16k到4G，64bitOS 16k到你能提供的可用記憶體量(最大1Zetta)

      用於儲存引擎為 MEMORY 的使用者表

   2. tmp_table_size

      命令列參數: –tmp_table_size=#
      ini/cnf定義: tmp_table_size
      mysql 變數: tmp_table_size
      全域變數，可動態調整, 預設值以系統設定，最大值4G

      tmp_table_size是mysql在執行group時，產生的記憶體表的大小限制，和max_heap_table_size一樣，這是一個位元組大小值。在系統中此值將和要查詢的表的行長進行計算，用以決定mysql可以裝載的表的行數。如果查詢中的一個限制超過此值，在3.x以後的版本中，mysql會自動把這個暫存資料表改換為磁碟檔案：MyISAM 引擎。使用者主動建立的記憶體表是不包含在此限制中的。

      根據狀態 :created_tmp_disk_tables 和 created_tmp_tables 來判定是否需要增加 tmp_table_size。
      

      受如下參數影響: max_head_table_size
      將影響如下參數:
      調整觸發條件: 當created_tmp_disk_tables超過created_tmp_tables時，應該採取行動提高tmp_table_size值.

      特別說明：在下列情況下，記憶體暫存資料表將不能發揮作用，mysql將直接使用 MyISAM

      • 表中有TEXT或者BLOB欄位的情況
      • group by 或者 distinct 指定的欄位中，存在有長度大於512位元組的欄位的情況
      • 在union/union all的select中，select 的欄位有大於512位元組的情況

      在下列情況，mysql有可能把記憶體暫存資料表轉換為MyISAM引擎

      • 查詢中order by 與 group by 的欄位不同
      • 在join查詢中，order by或者 group by包含了不是第一個表的欄位
      • 對distinct查詢使用order by 排序

      注意：max_heap_table_size和tmp_table_size在分配給一個連接線程查詢時，其取值是 min(max_heap_table_size,tmp_table_size), 如果一個網站的並發串連(show status like ‘thread_running’)很多,則系統記憶體會分配=min(max_heap_table_size,tmp_table_size) * (show status like ‘thread_running’)。比如並發1000的網站中，如果每個串連可分配的記憶體是16m，那麼臨時記憶體將被分配 16m * 1000=16g. 如果你有那麼多記憶體可供使用，那麼這不是問題，否則的話，還是要把 tmp_table_size設定小一些。
      當然，這也提示我們，在一個即時網站中，因為每個使用者請求都不可能也不應該執行一個相對來講很複雜或涉及很多記錄的查詢，對於一些統計工作，應該把統計用的資料庫和online trancation資料庫分開。

   3. max_tmp_tables

      根據文檔(mysql5.5CE)，此參數尚未啟用！

       

   4. query_prealloc_size

      命令列參數: –query_prealloc_size=#
      ini/cnf定義: query_prealloc_size
      mysql 變數: query_prealloc_size
      全域變數，可動態調整, 預設值8k，最大值4G(32bitOS)或更高(64bitOS).
      query_prealloc_size 用於MySQL對SQL語句進行分析和執行的記憶體空間設定。當執行大的複雜的查詢時，可以考慮針對SESSION增加此值。

   5. query_cache_* 查詢結果緩衝

      查詢快取是指兩類：查詢的SELECT語句以及查詢的結果。這是一些列的系統變數設定：

      query_cache_size: select查詢的結果緩衝，0或者40k到4G(32bitOS)或更高(64bitOS)，預設為0；
      query_cache_type: OFF:不緩衝查詢結果；ON:緩衝除了SELECT SQL_NO_CACHE開頭的所有查詢結果;DEMAND:只緩衝SELECT SQL_CACHE開頭的查詢結果。
      query_cache_limit: 可以緩衝的最大值位元組數。
      query_cache_min_res_unit:查詢快取分配的最小塊大小，預設4k.
      query_cache_wlock_invalidate:MySQL5.5中預設為OFF，只是是否在WRITE lock存在時組織其他客戶查詢。

      注意事項：
      –>對於特別頻繁的小查詢來說（比如返回結果小於或者遠小於query_cache_min_res_unit)，很容易造成大量的記憶體片段。
      –>對於結果集較大的查詢來說，足夠大的query_cache_min_res_unit可以提高系統的效能。
      通過對系統狀態 Qcache_free_blocks 和 Qcache_lowmem_prunes 的查詢來確定是否需要調整query_cache_min_res_unit。

      Qcache_free_blocks 查詢快取中閒置記憶體塊
      Qcache_lowmem_prunes 表明了因為查詢快取不足而有查詢結果被從緩衝中移除的數量。

2. 深入理解Query Cache

   Query_cache_size在MySQL中預設是0，也就是說查詢快取預設是關閉的。既然查詢快取可以很好的提高使用者的查詢體驗，為什麼會被關閉呢？這要從MySQL的用戶端編程來講。Query_Cache_Size中的查詢快取，是依照查詢語句作為索引值來緩衝結果的。查詢語句必須要完全相同：大小寫、空格、乃至查詢變數。等等，為什麼查詢變數也會影響這個索引值呢？原因很奇特：MySQL的用戶端查詢語句，使用的是直接變數替換，而不是像Oracle中那樣的變數代入。看下面的這個查詢例子：
   
public function GetUserByAccount($dbname, $host, $username, $userpwd, $charset, $account)
{
$sql = "select id from tmp_userhere";
if (CTextHelper::IsValidEmail($account))
$sql .= ‘ where email=:key‘;
else
$sql .= ‘ where username=:key‘;
$sql .= ‘ limit 3‘;
$dsn = "mysql:dbname=".$dbname.";host=".$host;
$dbh = new PDO($dsn, $username, $userpwd);
$charset = isset($charset) ? $charset :"gbk";
$this->dbh->exec("set names ".$charset);
$sth = $dbh->prepare($sql);
$sth->bindParam(‘:key‘, $account);
$r = $sth->execute();
if (!$r) return false;
return $sth->fetchColumn(0);
}

   儘管在用戶端(php代碼)中使用的是變數綁定, 而實際的伺服器跟蹤看到的卻是：
   
   對於一個超大的資料表的查詢來說，完全相同的查詢幾率太小了。所以完全可以省下這個Query Cache的設定。在我的某個大資料量的查詢中，就讓他是0吧。

3. 使用者的查詢習慣

   有很多人為的因素都會影響到查詢的效率。我不打算在這裡一一列舉，可以在 Top 10 SQL Performance Tips中看到各種各樣的最佳化語句的技巧。

4. Group By

   如果查詢語句中使用到Group By，一般來說，MySQL將建立一個暫存資料表，然後對資料進行計算和排序，這時是不使用索引的。如下面的查詢：
   

   如果資料量很大，那麼這個查詢肯定會很慢。但是MySQL有一種方式，可以運行group by 通過索引來計算，條件是，group by中的所有欄位都是一個索引中的列。

WORKING ON…,and KEEP EYES ON IT…

mysql最佳化之查詢最佳化