1.5.2　利用tuning-primer腳本來調優MySQL數據庫

MySQL在線上穩定運行一段時間后，就可以調用MySQL調優腳本tuning-primer.sh來檢查參數的設置是否合理，該腳本的下載地址為：

http://www.day32.com/MySQL/tuning-primer.sh。 

該腳本使用“SHOW STATUS LIKE…”和“SHOW VARIABLES LIKE…”命令獲得MySQL的相關變量和運行狀態。然后根據推薦的調優參數對當前的MySQL數據庫進行測試。最后根據不同顏色的標識來提醒用戶需要注意的各個參數設置。

當前版本會處理如下這些推薦的參數：

·Slow Query Log（慢查詢日志）

·Max Connections（最大連接數）

·Worker Threads（工作線程）

·Key Buffer（Key緩沖）

·Query Cache（查詢緩存）

·Sort Buffer（排序緩存）

·Joins（連接）

·Temp Tables（臨時表）

·Table（Open&Definition）Cache（表緩存）

·Table Locking（表鎖定）

·Table Scans（read_buffer）（表掃描，讀緩沖）

·InnoDB Status（InnoDB狀態）

筆者之前所在公司的主營業務是CPA電子廣告平臺，公司規模比較小，所以沒有配備專業的MySQL DBA，線上的MySQL數據庫（四核CPU）服務器問題比較多，用tuning-primer.sh腳本掃描后發現有如下問題：

·MySQL數據庫有時連接非常慢，嚴重時會被拖死。

通過show full processlist命令可以發現大量的“unauthenticated user”連接，數據庫肯定每次都要響應，所以速度越來越慢，解決方法其實很簡單：在mysql.cnf里添加skip-name-resolve，即不啟用DNS反向解析。

發生這種情況的原因其實也很簡單，MySQL的認證實際上是user＋host的形式（也就是說user可以相同），所以MySQL在處理新連接時會試著去解析客戶端連接的IP，啟用參數skip-name-resolve后MySQL授權的時候就只能使用純IP的形式了。

·數據庫在繁忙期間負載很大，長期達到了13，遠遠超過了系統平均負載4，這個肯定是不正常的。

通過腳本掃描，發現沒有新建thread_cache_size，所以加上了thread_cache_size＝256，然后重啟數據庫，數據庫的平均負載一下子降到了5~6。

·發現數據庫里有張new_cheat_id表，讀取很頻繁，而且長期處于Sending data狀態。

懷疑是磁盤I/O壓力過大所致，所以操作如下：

explain SELECT count(new_cheat_id)  FROM new_cheat WHERE account_id = '14348612' AND offer_id = '689'\G;

顯示結果如下所示：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: new_cheat
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2529529
        Extra: Using where
1 row in set (0.00 sec)

上面出現的這種問題很嚴重，new_cheat沒有建好索引，導致每次都要全表掃描2529529行記錄，嚴重消耗了服務器的I/O資源，所以立即建好索引，并用show index命令查看了表索引：

show index from new_cheat;

命令顯示結果如下所示：

+-----------+------------+------------+--------------+--------------+-
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------+--------------+--------------+-
| new_cheat |          0 | PRIMARY    |            1 | new_cheat_id | A         |     2577704 |     NULL | NULL   |      | BTREE      |         | 
| new_cheat |          1 | ip         |            1 | ip           | A         |     1288852 |     NULL | NULL   |      | BTREE      |         | 
| new_cheat |          1 | account_id |            1 | account_id   | A         |     1288852 |     NULL | NULL   |      | BTREE      |         | 
+-----------+------------+------------+--------------+--------------+-
3 rows in set (0.01 sec)

再來查看explain結果：

explain SELECT count(new_cheat_id)  FROM new_cheat WHERE account_id = '14348612' AND offer_id = '689'\G;

顯示結果如下所示：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: new_cheat
         type: ref
possible_keys: account_id
          key: account_id
      key_len: 4
          ref: const
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

大家可以發現，加好了索引后，此SQL通過account_id索引直接讀取了6條記錄（請對比關注rows這行）就獲得了查詢結果，系統負載由5~6直接降到了3.07~3.66了，這個負載還是能在可接受范圍之內的。

注意

MySQL的explain命令可用于SQL語句的查詢執行計劃（QEP）。這條命令的輸出結果能夠讓我們了解MySQL優化器是如何執行SQL語句的。這條命令并沒有提供任何調整建議，但 它提供的重要信息能夠幫助你做出調優決策。

最后要說明一點的是，對于網站來說，MySQL單機優化對整體性能提升的作用畢竟有限，尤其是在MySQL單機寫入方面，如果在工作中遇到了那種對MySQL即時寫入和讀取速度要求很高的場景，建議大家可以多關注分布式的SQL解決方案，例如Hadoop的HBase和AWS的RedShift等分布式SQL系統。