一些增進 MySQL 效能的 tips

幾天前,跟 Solaris 叔叔 談到 MySQL 的 ENUM,回家後上網找了些資料,剛好找出了一些使用 MySQL 時,與效能有關的 tips。

稍微整理過後,放在這裡:

  • 善用 EXPLAIN SELECT 分析 SQL statement,以下列舉狀態解讀:
    EQ_REF 一對一比對
    REF 一對多比對
    RANGE 特定範圍的資料會被傳回
    INDEX 使用 INDEX 裡的資料
    ALL 對整個 TABLE 作掃瞄(最差)
  • 善用 INDEX,以這個 SQL statement 為例:
    SELECT `col_a`
    FROM `table_a` LEFT JOIN `table_b`
    ON `table_a`.`col_b` = `table_b`.`col_c`
    WHERE `col_d` = ....;
    
    -- SELECT 的欄位不需 INDEX,ex: `col_a`。
    -- JOIN 的欄位需要 INDEX,ex: `table_a`.`col_b` 與 `table_b`.`col_c`。
    -- WHERE 的欄位需要 INDEX,ex: `col_d`。
    
  • 儘量使用固定大小的欄位,MyISAM 搜尋固定大小欄位比較快;也就是說,能用 CHAR 就別用 VARCHAR。
  • 儘量將欄位指定為 NOT NULL。
  • ENUM 格式的欄位,資料處理速度很快(實測結果在這)。
  • 不要取不需要的資料,下面這種語法就是不好的例子:
    SELECT * ...
  • 把 BLOB 與 TEXT 拆開,減少 MySQL 開啟單一大檔(table 資料檔案過大)的機會。
  • JOIN 用的欄位資料格式最好是相同的,以免 MySQL 作 FULL TABLE SCAN。
  • 使用 LIKE 時,避免把 % 放在字串開頭,以免 INDEX 利用率不佳。
    -- 下列這種語法無法利用 INDEX
    SELECT ... WHERE `col` LIKE '%string%';
    
    -- 下列這種語法會利用 INDEX
    SELECT ... WHERE `col` LIKE 'string%';
  • 注意 WHERE 子句內的運算式。
    -- 下列這種語法會使用 INDEX,速度快。
    SELECT ... WHERE `col` < 100 / 10;
    
    -- 下列這種語法會導致 FULL TABLE SCAN,速度慢。
    SELECT ... WHERE `col` * 10 < 100;