[轉載] 在 MySQL 中使用 index

 以下文章 來至於 Edison home
http://remindme.blogbus.com/logs/34133550.html

在 MySQL 中使用 index 時,下列是一些該注意的事:

  • 在 MySQL 裡,將欄位設為 Primary 或 Unique 時,都同時具有 index 的效果。
  • 欲設為 index 的欄位長度是越短越好,這樣在維護 index table 時會快速一些;像 BLOB 與 TEXT 這類巨無霸型的資料型態,雖然在 MySQL 3.23.2 版之後也可以被設為 index 了,但還是少用為妙。
  • 欲設為 index 的欄位長度若是固定的話,會比變動的來得好。例如:同樣被設為 index,char(10) 的欄位,就比 varchar(10) 好。
  • 將多個欄位複合成為一組 index key 的話,要注意先後順序。例如:將「A+B+C」設為 index 時,下列的 SELECT 敘述將會使用到 index:
    SELECT * FROM tbl_name WHERE A = val1;
    SELECT * FROM tbl_name WHERE A = val1 AND B = val2;
    SELECT * FROM tbl_name WHERE A = val1 AND B = val2 AND C = val3;
     
    但像以下這樣的 SELECT 敘述將不會享受到 index 的好處:
    SELECT * FROM tbl_name WHERE B = val2;
    SELECT * FROM tbl_name WHERE C = val3;
    SELECT * FROM tbl_name WHERE B = val2 AND C = val3;
  • 當您使用「LIKE」參數時,若將萬用字元(%)置於關鍵詞後方,可以使用到 index;
    select * from tbl_name where key_col LIKE "Patrick%"; 
    若置於關鍵詞前方的話,則 index 不會起作用。
    select * from tbl_name where key_col LIKE "%Patrick%";


\"\" EXPLAIN  如果想知道每個 SELECT 敘述是否充分運用 index 的話,您可以試試使用「EXPLAIN」。當您 SELECT 敘述句之前加上「EXPLAIN」時,將可以看到 MySQL 對它的處理原則,包括「是否使用 index」等資訊。

score 》

SNum SName Score
75312 Chen 80
75524 Chuang 95
75207 Yeh 92
75302 Lee 90
75101 Chuang 89
75303 Ho 90
75120 Lin 92
75313 Chen 88
address 》

SNum Address
75312 高雄縣鳳山市五甲二路 424 號

 以上方的 score 表為例,我們將其中的「SNum」欄位設為 index key,請看 EXPLAIN 的用
法與結果: 
 
【例一】EXPLAIN SELECT * from score WHERE SNum = '75312';
\"\"  

【例二】EXPLAIN SELECT * from score WHERE SNum LIKE '753%';
\"\"  

【例三】EXPLAIN SELECT * from score WHERE SName LIKE '%531%';
\"\"  

【例四】EXPLAIN SELECT * from score WHERE SName = 'Chen';
\"\"  

【例五】EXPLAIN SELECT * FROM score LEFT JOIN address ON score.SNum = address.SNum WHERE score.SNum = '75312';
\"\" 
 我們來看看這些結果代表什麼意思:

  • table
    表示所引用的表格名稱。
  • type
    表示查詢時的「聯結類型」(join type),以下依序是「最佳」至「最差」的各種類型:
    • system
      表格中僅有一列。這是 const 類型的一個特例。
    • const
      表格中符合條件的只有一列。因為僅有一列,其值在後續的查詢中可被視為常數。
    • eq_ref
      表示在與其它表格的資料列結合時,此表格只有一列會被讀取。當 join 使用到資料表中的所有索引,並索引是 UNIQUE 或 PRIMARY KEY 時才會被用到。
    • ref
      表 示在與其它表格的資料列結合時,此表格中所有符合的資料列都會被讀出來。這是當 join 只使用到部份鍵值(註),或此鍵非 UNIQUE 或 PRIMARY KEY 時才會用到(依照 join 的條件仍然無法選定單一目標列)。若因此符合的資料列數不多的話,它也算是一種不錯的「聯結類型」。
      註:我們指定「A+B」欄位為 index key,但查詢時只用到「A」欄位。
    • range
      表示將在一定範圍內執行搜尋的動作。
    • index
      與 ALL 相同,但只有 index table 會被瀏覽。這通常比 ALL 快,因為 index table 通常比原始資料表來得小。
    • All
      表示這項查詢將對整個原始資料表瀏覽一遍,是最不好的類型。
  • possible_keys
    表示 MySQL 能夠藉由哪些 index 來搜尋目標。
  • key
    表示 MySQL 實際藉由哪個 index 來搜尋目標。
  • key_len
    表示 MySQL 實際使用的 key 長度。若 index key 是由兩個欄位以上複合而成的話,您可以在此看見 MySQL 使用了 index 的多少部份。
  • ref
    表示哪個欄位(或常數)將被用來與 key 一起比對。
  • rows
    表示 MySQL 粗略估計在查詢的過程中,必須瀏覽的資料列數。
  • Extra
    顯示 MySQL 在解決這項查詢工作時的一些附加訊息。例如:「where used」表示 where 子句將會限制某些資料列的輸出。

This entry was posted in MySQL and tagged . Bookmark the permalink.

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *