mysql 開發進階篇系列 4 SQL 優化

1 通過handler_read 查看索引使用情況

　　如果索引經常被用到 那麼handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數， 很低的值表明增加索引得到的性能改善不高，索引並不經常使用。
handler_read_rnd_next 的值高 則意味著查詢運行低效，應該建立索引， 這個值表示在數據文件中讀下一行的請求數，如果是正進行大量掃描 值會較高，一般是索引不正確或沒有利用到索引。

SHOW STATUS LIKE 'Handler_read%';

2. 優化定期分析表

　　analyze 語句用於分析和存儲表的關鍵字分佈，分析的結果將可以使得系統得到準確的統計信息，使得sql能夠生成正確的執行計劃。如果用戶感覺實際執行計劃並不是預期的執行計劃，執行一次分析表可能會解決問題。 在分析時使用一個讀取鎖對錶進行了鎖定，這個對於myisam,bdb,innodb表有作用。

-- 分析表
ANALYZE TABLE city;

3. 優化檢查表

　　check 檢查表的作用是檢查一個或多個表是否有錯誤。check table對myisam和innodb表有作用。

-- 檢查表
CHECK TABLE city;

4.    優化optimize

　　如果一個表已經刪除了一大部分，更者對可變長度行的表(varchar,blob,text)進行了很多更改，則就使用optimize table命令來進行優化, 它是將表空間碎片進行合併，可以消除由於刪除或者更新造成的空間浪費，對myisam, bdb ,innodb表起作用。具體參考mysql 開發基礎系列12 選擇合適的數據類型(上)

 -- 優化表
OPTIMIZE TABLE city;

　　總結： analyze, check, OPTIMIZE 執行期間將對錶進行鎖定，在繁忙時候不要操作。

5.  優化大批量插入數據

 　　5.1 針對大量數據導入到一個非空的myisam表，可以通過以下方式快速導入大量數據。

    ALTER TABLE tab_name DISABLE KEYS;
        loading the DATA
    ALTER TABLE tab_name ENABLE KEYS;

　　   DISABLE KEYS和 ENABLE KEYS是打開或者關閉myisam表非唯一索引的更新，對於myisam空表則預設是先導入數據然後才創建索引，所以不用設置。

　　5.2 針對innodb表

 　　　　在導入之前設置unique_checks=0 導完後開啟set unique_checks=1。設置autocommit=0 導完後開啟autocommit=1。

6. 優化insert 語句

(1) 不同客戶插入很多行數據時，更改INSERT INTO為 INSERT DELAYED INTO，這使語句得到更高的速度。
(2) 將索引文件和磁碟文件分在不同磁碟上存放（利用表的選項）。
(3) 如果是批量插入 對myisam表可使用bulk_insert_buffer_size 來提高速度。
(4) 使用load data infile 通常比insert語句快20倍。

7. 優化group by語句

　　預設情況下 group by 會對欄位進行排序，如果想避免排序結果帶來的消耗，可以指定order by null 來禁止排序 如下：

-- CityCode  預設使用了排序   (如果CityCode已建索引，預設就排序好了 不用優化)
EXPLAIN SELECT COUNT(country_id), CityCode FROM city   GROUP BY CityCode

-- 使用 order by null 來禁止排序
EXPLAIN SELECT COUNT(country_id), CityCode FROM city   GROUP BY CityCode ORDER BY NULL

8. 優化order by 語句

　　mysql 可以使用一個索引來滿足order by 子句,而不需要額外的排序(上面group by 就是未鍵索引 需要再排序),並且order by 的順序與索引順序相同，升序或降序。

--  order by 使用到了索引的排序
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ;
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ORDER BY country_id,city_id;
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ORDER BY country_id ASC ,city_id ASC;
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ORDER BY country_id DESC ,city_id DESC;
-- order by 未使用到索引的排序
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ORDER BY country_id ASC ,city_id DESC;