Mysql 效能優化--基礎引數

mysql> show global status like 'Thread%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Threads_cached    | 0       |
| Threads_connected | 25      |
| Threads_created   | 1426023 |
| Threads_running   | 2       |
+-------------------+---------+
4 rows in set (0.00 sec)
在MySQL伺服器配置檔案中設定了thread_cache_size，當客戶端斷開之後，伺服器處理此客戶的執行緒將會快取起來以響應下一個客戶而不是銷燬（前提是快取數未達上限）。Threads_created表示建立過的執行緒數，如果發現Threads_created值過大的話，表明MySQL伺服器一直在建立執行緒，這也是比較耗資源，可以適當增加配置檔案中thread_cache_size值
查詢伺服器thread_cache_size配置：
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 0     |
+-------------------+-------+
1 row in set (0.00 sec)


查詢快取
mysql> show global status like '%qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)
MySQL查詢快取變數解釋：
Qcache_free_blocks：快取中相鄰記憶體塊的個數。數目大說明可能有碎片。FLUSH QUERY CACHE會對快取中的碎片進行整理，從而得到一個空閒塊。
Qcache_free_memory：快取中的空閒記憶體。
Qcache_hits：每次查詢在快取中命中時就增大
Qcache_inserts：每次插入一個查詢時就增大。命中次數除以插入次數就是不中比率。
Qcache_lowmem_prunes：快取出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者記憶體很少。（上面的 free_blocks和free_memory可以告訴您屬於哪種情況）
Qcache_not_cached：不適合進行快取的查詢的數量，通常是由於這些查詢不是 SELECT 語句或者用了now()之類的函式。
Qcache_queries_in_cache：當前快取的查詢（和響應）的數量。
Qcache_total_blocks：快取中塊的數量。

再查詢一下伺服器關於query_cache的配置：
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)
各欄位的解釋：
query_cache_limit：超過此大小的查詢將不快取
query_cache_min_res_unit：快取塊的最小大小
query_cache_size：查詢快取大小
query_cache_type：快取型別，決定快取什麼樣的查詢，示例中表示不快取 select sql_no_cache 查詢
query_cache_wlock_invalidate：當有其他客戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成再讀表獲取結果。
query_cache_min_res_unit的配置是一柄”雙刃劍”，預設是4KB，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易造成記憶體碎片和浪費。

查詢快取碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢快取碎片率超過20%，可以用FLUSH QUERY CACHE整理快取碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。
查詢快取利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%
查詢快取利用率在25%以下的話說明query_cache_size設定的過大，可適當減小；查詢快取利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。
查詢快取命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%


排序使用情況
mysql> show global status like 'sort%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Sort_merge_passes | 7        |
| Sort_range        | 81766    |
| Sort_rows         | 19196256 |
| Sort_scan         | 645901   |
+-------------------+----------+
4 rows in set (0.00 sec)
Sort_merge_passes 包括兩步。MySQL 首先會嘗試在記憶體中做排序，使用的記憶體大小由系統變數 Sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到記憶體中，MySQL 就會把每次在記憶體中排序的結果存到臨時檔案中，等 MySQL 找到所有記錄之後，再把臨時檔案中的記錄做一次排序。這再次排序就會增加 Sort_merge_passes。實際上，MySQL 會用另一個臨時檔案來存再次排序的結果，所以通常會看到 Sort_merge_passes 增加的數值是建臨時檔案數的兩倍。因為用到了臨時檔案，所以速度可能會比較慢，增加 Sort_buffer_size 會減少 Sort_merge_passes 和 建立臨時檔案的次數。但盲目的增加 Sort_buffer_size 並不一定能提高速度，見 How fast can you sort data with MySQL?（另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的好處

檔案開啟數
mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 33    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 25000 |
+------------------+-------+
1 row in set (0.00 sec)

比較合適的設定：Open_files / open_files_limit * 100% <= 75％

表鎖情況

mysql> show global status like 'table_locks%';
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Table_locks_immediate | 106767354 |
| Table_locks_waited    | 11783     |
+-----------------------+-----------+
2 rows in set (0.01 sec)

mysql> select 106767354/11783;
+-----------------+
| 106767354/11783 |
+-----------------+
|       9061.1350 |
+-----------------+
1 row in set (0.00 sec)
Table_locks_immediate表示立即釋放表鎖數，Table_locks_waited表示需要等待的表鎖數，如果Table_locks_immediate / Table_locks_waited > 5000，最好採用InnoDB引擎，因為InnoDB是行鎖而MyISAM是表鎖，對於高併發寫入的應用InnoDB效果會好些。

表掃描
mysql> show global status like 'handler_read%';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Handler_read_first    | 20336636     |
| Handler_read_key      | 589222889    |
| Handler_read_next     | 251390653    |
| Handler_read_prev     | 221          |
| Handler_read_rnd      | 4330823      |
| Handler_read_rnd_next | 202640993647 |
+-----------------------+--------------+
6 rows in set (0.00 sec)

mysql> show global status like 'com_select';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_select    | 16108125 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> select 202640993647/16108125;
+-----------------------+
| 202640993647/16108125 |
+-----------------------+
|            12580.0485 |
+-----------------------+
1 row in set (0.00 sec)

計算表掃描率：
表掃描率 ＝ Handler_read_rnd_next / Com_select
表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8MB。

mysql> show variables like '%read_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)