MySQL 優化

• 表關聯查詢時務必遵循 小表驅動大表 原則；
• 使用查詢語句

  where

  條件時，不允許出現 函數，否則索引會失效；
• 使用單表查詢時，相同字段盡量不要用

  OR

  ，因為可能導緻索引失效，比如：

  SELECT * FROM table WHERE name = '手機' OR name = '電腦'

  ，可以使用

  UNION

  替代；

• LIKE

  語句不允許使用

  %

  開頭，否則索引會失效；
• 組合索引一定要遵循 從左到右 原則，否則索引會失效；比如：

  SELECT * FROM table WHERE name = '張三' AND age = 18

  ，那麼該組合索引必須是

  name,age

  形式；
• 索引不宜過多，根據實際情況決定，盡量不要超過 10 個；
• 每張表都必須有 主鍵，達到加快查詢效率的目的；
• 分表，可根據業務字段尾數中的個位或十位或百位（以此類推）做表名達到分表的目的；
• 分庫，可根據業務字段尾數中的個位或十位或百位（以此類推）做庫名達到分庫的目的；
• 表分區，類似于硬碟分區，可以将某個時間段的資料放在分區裡，加快查詢速度，可以配合 分表 + 表分區 結合使用；

#神器

EXPLAIN

語句

EXPLAIN

顯示了 MySQL 如何使用索引來處理

SELECT

語句以及連接配接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。

使用方法，在

SELECT

語句前加上

EXPLAIN

即可，如：

EXPLAIN SELECT * FROM tb_item WHERE cid IN (SELECT id FROM tb_item_cat)
           

1

• id： SELECT 識别符。這是 SELECT 的查詢序列号

• select_type：

  SELECT類型,可以為以下任何一種

  • SIMPLE: 簡單 SELECT(不使用 UNION 或子查詢)
  • PRIMARY: 最外面的 SELECT
  • UNION: UNION 中的第二個或後面的 SELECT 語句
  • DEPENDENT UNION: UNION 中的第二個或後面的 SELECT 語句,取決于外面的查詢
  • UNION RESULT: UNION 的結果
  • SUBQUERY: 子查詢中的第一個 SELECT
  • DEPENDENT SUBQUERY: 子查詢中的第一個 SELECT,取決于外面的查詢
  • DERIVED: 導出表的 SELECT(FROM 子句的子查詢)
• table： 輸出的行所引用的表
• partitions： 表分區

• type：

  聯接類型。下面給出各種聯接類型，按照

  從最佳類型到最壞類型

  進行排序

  • system: 表僅有一行(=系統表)。這是 const 聯接類型的一個特例。
  • const: 表最多有一個比對行,它将在查詢開始時被讀取。因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數。const 表很快,因為它們隻讀取一次!
  • eq_ref: 對于每個來自于前面的表的行組合, 從該表中讀取一行。這可能是最好的聯接類型, 除了 const 類型。
  • ref: 對于每個來自于前面的表的行組合, 所有有比對索引值的行将從這張表中讀取。
  • ref_or_null: 該聯接類型如同 ref,但是添加了 MySQL 可以專門搜尋包含 NULL 值的行。
  • index_merge: 該聯接類型表示使用了索引合并優化方法。
  • unique_subquery: 該類型替換了下面形式的 IN 子查詢的 ref:

    value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery

    是一個索引查找函數, 可以完全替換子查詢, 效率更高。
  • index_subquery: 該聯接類型類似于 unique_subquery。可以替換 IN 子查詢, 但隻适合下列形式的子查詢中的非唯一索引:

    value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range: 隻檢索給定範圍的行,使用一個索引來選擇行。
  • index: 該聯接類型與 ALL 相同,除了隻有索引樹被掃描。這通常比 ALL 快,因為索引檔案通常比資料檔案小。
  • ALL: 對于每個來自于先前的表的行組合, 進行完整的表掃描。
• possible_keys： 指出 MySQL 能使用哪個索引在該表中找到行
• key： 顯示 MySQL 實際決定使用的鍵(索引)。如果沒有選擇索引, 鍵是 NULL。
• key_len： 顯示 MySQL 決定使用的鍵長度。如果鍵是 NULL, 則長度為 NULL。
• ref： 顯示使用哪個列或常數與 key 一起從表中選擇行。
• rows： 顯示 MySQL 認為它執行查詢時必須檢查的行數。多行之間的資料相乘可以估算要處理的行數。
• filtered： 顯示了通過條件過濾出的行數的百分比估計值。

• Extra：

  該列包含 MySQL 解決查詢的詳細資訊

  • Distinct: MySQL 發現第 1 個比對行後,停止為目前的行組合搜尋更多的行。
  • Not exists: MySQL 能夠對查詢進行 LEFT JOIN 優化, 發現 1 個比對 LEFT JOIN 标準的行後, 不再為前面的的行組合在該表内檢查更多的行。
  • range checked for each record (index map: #): MySQL 沒有發現好的可以使用的索引, 但發現如果來自前面的表的列值已知, 可能部分索引可以使用。
  • Using filesort: MySQL 需要額外的一次傳遞, 以找出如何按排序順序檢索行。
  • Using index: 從隻使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。
  • Using temporary: 為了解決查詢, MySQL 需要建立一個臨時表來容納結果。
  • Using where: WHERE 子句用于限制哪一個行比對下一個表或發送到客戶。
  • Using sort_union(...), Using union(...), Using intersect(...): 這些函數說明如何為 index_merge 聯接類型合并索引掃描。
  • Using index for group-by: 類似于通路表的 Using index 方式,Using index for group-by 表示 MySQL 發現了一個索引,可以用來查詢 GROUP BY 或 DISTINCT 查詢的所有列, 而不要額外搜尋硬碟通路實際的表。

等你看到的時候，想變得有一點點不一樣