Mysql數據庫性能優化一些經驗分享

數據庫的性能優化是一個非常重要的運維操作，如果優化不好負載不起網站就麻煩了，下面小編從各方面來給各位介紹Mysql數據庫性能優化經驗。

一,性能檢測與瓶頸分析

1性能檢測常用命令

show status

顯示狀態信息，參考：Mysql show status命令詳解

show processlist

查看當前SQL執行，包括執行狀態、是否鎖表等，參考：Mysql show processlist命令詳解

show variables

顯示系統變量，參考：Mysql show variables命令詳解

2瓶頸分析常用命令

獲取mysql用戶下的進程總數

ps -ef | awk '{print $1}' | grep "mysql" | grep -v "grep" | wc -l

主機性能狀態

# uptime
13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00
CPU使用率

# top 或 # vmstat
磁盤IO量

 

# vmstat 或 # iostat
swap進出量[內存]

# free -m
數據庫性能狀態

QPS(每秒Query量)

QPS = Questions(or Queries) / seconds

mysql > show /* global */ status like 'Question';
TPS(每秒事務量)

TPS = (Com_commit + Com_rollback) / seconds

mysql > show status like 'Com_commit';
mysql > show status like 'Com_rollback';
key Buffer 命中率

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

mysql> show status like 'Key%';
InnoDB Buffer命中率

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

mysql> show status like 'innodb_buffer_pool_read%';
Query Cache命中率

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

mysql> show status like 'Qcache%';
Table Cache狀態量

mysql> show status like 'open%';
Thread Cache 命中率

Thread_cache_hits = (1 - Threads_created / connections ) * 100%

mysql> show status like 'Thread%';
mysql> show status like 'Connections';

鎖定狀態

mysql> show status like '%lock%';

復制延時量

mysql > show slave status
Tmp Table 狀況(臨時表狀況)

mysql > show status like 'Create_tmp%';
Binlog Cache 使用狀況

mysql > show status like 'Binlog_cache%';
Innodb_log_waits 量

mysql > show status like 'innodb_log_waits';

二,慢查詢配置與分析

1慢日志配置

查看慢日志配置

查看慢查詢日志是否啟用

mysql> show variables like 'log_slow_queries';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | ON    |
+------------------+-------+
1 row in set (0.00 sec)
查看慢查詢時間設置

mysql> show variables like 'long_query_time';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1     |  
+-----------------+-------+
1 row in set (0.00 sec)
開啟慢查詢日志

方法一

打開my.ini文件（Linux下文件名為my.cnf），查找到[mysqld]區段，增加如下配置：

[mysqld]
slow_query_log=1
#開啟慢查詢日志，slow_query_log=0關閉，slow_query_log=1開啟。
log="C:/temp/mysql.log"
#日志文件存放目錄。
log_slow_queries="C:/temp/mysql_slow.log"
#慢查詢日志存放位置（5.5之前版本）。
slow_query_log_file="C:/temp/mysql_slow.log"
#慢查詢日志存放位置（5.5及以後版本）。
long_query_time=1
#執行時間超過1s的慢查詢將被記錄。
long_query_time=0.1
#5.21及以後版本支持毫秒級記錄，此處設置為100ms。
重啟mysql生效。

方法二

開啟慢查詢日志

mysql > SET GLOBAL slow_query_log = ON
設置慢查詢時間

mysql > SET long_query_time = 2
此配置為臨時配置，重啟mysql後失效。

測試慢查詢配置

進入mysql控制台，執行如下sql語句：

select sleep(2);
然後打開慢查詢日志查看即可。

2慢查詢分析工具 - mysqldumpslow

我們可以通過打開log文件查看得知哪些SQL執行效率低下

[root@localhost mysql]# more slow.log
 
# Time: 141028 17:00:29
# User@Host: root[root] @  localhost []
# Query_time: 2.000330  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1414486829;
select sleep(2);
從日志中，可以發現查詢時間超過2秒的SQL，而小於2秒的沒有出現在此日志中。

如果慢查詢日志中記錄內容很多，可以使用mysqldumpslow工具（MySQL客戶端安裝自帶）來對慢查詢日志進行分類匯總。mysqldumpslow對日志文件進行了分類匯總，顯示匯總後摘要結果。

進入log的存放目錄，運行：

[root@localhost mysql]# ./bin/mysqldumpslow slow.log
 
Reading mysql slow query log from slow.log
Count: 1  Time=2.00s (2s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@mysql
  select sleep(N)
mysqldumpslow命令

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log
這會輸出記錄次數最多的10條SQL語句，其中：

-s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；

-t, 是top n的意思，即為返回前面多少條的數據；

-g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；

例如：

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回記錄集最多的10個查詢。

/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照時間排序的前10條裡面含有左連接的查詢語句。

使用mysqldumpslow命令可以非常明確的得到各種我們需要的查詢語句，對MySQL查詢語句的監控、分析、優化是MySQL優化非常重要的一步。開啟慢查詢日志後，由於日志記錄操作，在一定程度上會占用CPU資源影響mysql的性能，但是可以階段性開啟來定位性能瓶頸。

3explain分析查詢

使用 EXPLAIN 關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。通過explain命令可以得到:

表的讀取順序

數據讀取操作的操作類型

哪些索引可以使用

哪些索引被實際使用

表之間的引用

每張表有多少行被優化器查詢

mysql> describe test;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | bigint(20) | NO   | PRI | NULL    | auto_increment |
| name  | char(10)   | YES  | MUL | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
 
mysql> explain select name from test where id = 5;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
EXPLAIN字段

Table：顯示這一行的數據是關於哪張表的

possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句

key：實際使用的索引。如果為NULL，則沒有使用索引。MYSQL很少會選擇優化不足的索引，此時可以在SELECT語句中使用USE INDEX（index）來強制使用一個索引或者用IGNORE INDEX（index）來強制忽略索引

key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows：MySQL認為必須檢索的用來返回請求數據的行數

type：這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型為system、const、eq_reg、ref、range、index和ALL

system、const：可以將查詢的變量轉為常量.  如id=1; id為 主鍵或唯一鍵.

eq_ref：訪問索引,返回某單一行的數據.(通常在聯接時出現，查詢使用的索引為主鍵或惟一鍵)

ref：訪問索引,返回某個值的數據.(可以返回多行) 通常使用=時發生

range：這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西，並且該字段上建有索引時發生的情況(注:不一定好於index)

index：以索引的順序進行全表掃描，優點是不用排序,缺點是還要全表掃描

ALL：全表掃描，應該盡量避免

Extra：關於MYSQL如何解析查詢的額外信息，主要有以下幾種

using index：只用到索引,可以避免訪問表.

using where：使用到where來過慮數據. 不是所有的where clause都要顯示using where. 如以=方式訪問索引.

using tmporary：用到臨時表

using filesort：用到額外的排序. (當使用order by v1,而沒用到索引時,就會使用額外的排序)

range checked for eache record(index map:N)：沒有好的索引.

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | abc  |
|  3 | aab  |
|  4 | acf  |
|  5 | aac  |
|  6 | acd  |
|  7 | cvf  |
+----+------+
7 rows in set (0.00 sec)
 
mysql> explain select * from test where name > 'a';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
 
mysql> explain select * from test where name > 'a' limit 3;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | test          | test | 31      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
4profiling分析查詢

通過慢日志查詢可以知道哪些SQL語句執行效率低下，通過explain我們可以得知SQL語句的具體執行情況，索引使用等，還可以結合show命令查看執行狀態。

如果覺得explain的信息不夠詳細，可以同通過profiling命令得到更准確的SQL執行消耗系統資源的信息。

Show Profile命令格式

SHOW PROFILE [type [, type] … ]                                   
    [FOR QUERY n]                                                           
    [LIMIT row_count [OFFSET offset]]
type:                                                                                 

  | ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU                                                                     
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
profiling默認是關閉的。可以通過以下語句查看

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)
打開功能： mysql>set profiling=1; 執行需要測試的sql 語句：

mysql> set profiling=1;
Query OK, 0 rows affected (0.02 sec)
 
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  3 | aab  |
|  5 | aac  |
|  2 | abc  |
|  6 | acd  |
|  4 | acf  |
|  7 | cvf  |
+----+------+
7 rows in set (0.39 sec)
 
mysql> show profilesG;
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.39353075
   Query: select * from test
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
mysql> show profile for query 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.024071 |
| Opening tables     | 0.305523 |
| System lock        | 0.000320 |
| Table lock         | 0.014546 |
| init               | 0.000050 |
| optimizing         | 0.000446 |
| statistics         | 0.000359 |
| preparing          | 0.000452 |
| executing          | 0.000006 |
| Sending data       | 0.047042 |
| end                | 0.000021 |
| query end          | 0.000005 |
| freeing items      | 0.000663 |
| logging slow query | 0.000025 |
| cleaning up        | 0.000004 |
+--------------------+----------+
15 rows in set (0.05 sec)
mysql> show profilesG; 可以得到被執行的SQL語句的時間和ID

mysql>show profile for query 1; 得到對應SQL語句執行的詳細信息

以上的15rows是針對非常簡單的select語句的資源信息，對於較復雜的SQL語句，會有更多的行和字段，比如converting HEAP to MyISAM 、Copying to tmp table等等，由於以上的SQL語句不存在復雜的表操作，所以未顯示這些字段。通過profiling資源耗費信息，我們可以采取針對性的優化措施。

測試完畢以後 ，關閉參數：mysql> set profiling=0

mysql> set profiling = 0;
Query OK, 0 rows affected (0.00 sec)

三,索引及查詢優化

1索引優化

索引的類型

普通索引：這是最基本的索引類型，沒唯一性之類的限制。

唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。

主鍵：主鍵是一種唯一索引，但必須指定為”PRIMARY KEY”。

全文索引：MYSQL從3.23.23開始支持全文索引和全文檢索。在MYSQL中，全文索引的索引類型為FULLTEXT。全文索引可以在VARCHAR或者TEXT類型的列上創建。

大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B樹中存儲。空間列類型的索引使用R-樹，MEMORY表支持hash索引。

單列索引和多列索引（復合索引）

索引可以是單列索引，也可以是多列索引。對相關的列使用索引是提高SELECT操作性能的最佳途徑之一。

多列索引

MySQL可以為多個列創建索引。一個索引可以包括15個列。對於某些列類型，可以索引列的左前綴，列的順序非常重要。

多列索引可以視為包含通過連接索引列的值而創建的值的排序的數組。一般來說，即使是限制最嚴格的單列索引，它的限制能力也遠遠低於多列索引。

最左前綴

多列索引有一個特點，即最左前綴（Leftmost Prefixing）。假如有一個多列索引為key(firstname lastname age)，當搜索條件是以下各種列的組合和順序時，MySQL將使用該多列索引：

firstname，lastname，age
firstname，lastname
firstname
也就是說，相當於還建立了key(firstname lastname)和key(firstname)。

索引主要用於下面的操作：

快速找出匹配一個WHERE子句的行。

刪除行。當執行聯接時，從其它表檢索行。

對具體有索引的列key_col找出MAX()或MIN()值。由預處理器進行優化，檢查是否對索引中在key_col之前發生所有關鍵字元素使用了WHERE key_part_# = constant。在這種情況下，MySQL為每個MIN()或MAX()表達式執行一次關鍵字查找，並用常數替換它。如果所有表達式替換為常量，查詢立即返回。例如：

[SQL]
1
SELECT MIN(key2), MAX (key2)  FROM tb WHERE key1=10;
如果對一個可用關鍵字的最左面的前綴進行了排序或分組(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有關鍵字元素後面有DESC，關鍵字以倒序被讀取。

在一些情況中，可以對一個查詢進行優化以便不用查詢數據行即可以檢索值。如果查詢只使用來自某個表的數字型並且構成某些關鍵字的最左面前綴的列，為了更快，可以從索引樹檢索出值。

[SQL]
1
SELECT key_part3 FROM tb WHERE key_part1=1;
有時MySQL不使用索引，即使有可用的索引。一種情形是當優化器估計到使用索引將需要MySQL訪問表中的大部分行時。(在這種情況下，表掃描可能會更快些）。然而，如果此類查詢使用LIMIT只搜索部分行，MySQL則使用索引，因為它可以更快地找到幾行並在結果中返回。

合理的建立索引的建議

越小的數據類型通常更好：越小的數據類型通常在磁盤、內存和CPU緩存中都需要更少的空間，處理起來更快。

簡單的數據類型更好：整型數據比起字符，處理開銷更小，因為字符串的比較更復雜。在MySQL中，應該用內置的日期和時間數據類型，而不是用字符串來存儲時間；以及用整型數據類型存儲IP地址。

盡量避免NULL：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值

關於索引和寫SQL語句時應當注意的一些瑣碎建議和注意點

當結果集只有一行數據時使用LIMIT 1

避免SELECT *，始終指定你需要的列

從表中讀取越多的數據，查詢會變得更慢。他增加了磁盤需要操作的時間，還是在數據庫服務器與WEB服務器是獨立分開的情況下。你將會經歷非常漫長的網絡延遲，僅僅是因為數據不必要的在服務器之間傳輸。

使用連接（JOIN）來代替子查詢(Sub-Queries)

連接（JOIN）.. 之所以更有效率一些，是因為MySQL不需要在內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

使用ENUM、CHAR 而不是VARCHAR，使用合理的字段屬性長度

盡可能的使用NOT NULL

固定長度的表會更快

拆分大的DELETE 或INSERT 語句

查詢的列越小越快

2查詢優化

在查詢中，WHERE條件也是一個比較重要的因素，盡量少並且是合理的where條件是很重要的，盡量在多個條件的時候，把會提取盡量少數據量的條件放在前面，減少後一個where條件的查詢時間。

where條件導致索引無效的情況

where子句的查詢條件裡有！=，MySQL將無法使用索引。

where子句使用了Mysql函數的時候，索引將無效，比如：select * from tb where left(name, 4) = ‘xxx’

使用LIKE進行搜索匹配的時候，這樣索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 時索引無效

四,配置優化

1連接請求的變量

max_connections

MySQL的最大連接數，增加該值增加mysqld 要求的文件描述符的數量。如果服務器的並發連接請求量比較大，建議調高此值，以增加並行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多，介於MySQL會為每個連接提供連接緩沖區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。

數值過小會經常出現ERROR 1040: Too many connections錯誤，可以過’conn%’通配符查看當前狀態的連接數量，以定奪該值的大小。

最大連接數

[SQL]
1
show variables like 'max_connections'
最大響應的連接數

[SQL]
1
show  status like 'max_used_connections'
如下：

[SQL]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.07 sec)
 
mysql> show status like 'max%connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1     |
+----------------------+-------+
1 row in set (0.03 sec)
max_used_connections / max_connections * 100% （理想值≈ 85%）

如果max_used_connections跟max_connections相同 那麼就是max_connections設置過低或者超過服務器負載上限了，低於10%則設置過大。

back_log

MySQL能暫存的連接數量。當主要MySQL線程在一個很短時間內得到非常多的連接請求，這就起作用。如果MySQL的連接數據達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源。

back_log值指出在MySQL暫時停止回答新請求之前的短時間內有多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊列的大小。

當觀察你主機進程列表（mysql> show full processlist），發現大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進程時，就要加大back_log 的值了。

默認數值是50，可調優為128，對於Linux系統設置范圍為小於512的整數。

interactive_timeout

一個交互連接在被服務器在關閉前等待行動的秒數。一個交互的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶。

默認數值是28800，可調優為7200。

2緩沖區變量

全局緩沖

key_buffer_size

key_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理。比例key_reads / key_read_requests應該盡可能的低，至少是1:100，1:1000更好（上述狀態值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁盤表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。

舉例如下：

mysql> show variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 536870912 |
+-----------------+-----------+
1 row in set (0.00 sec)
key_buffer_size為512MB，我們再看一下key_buffer_size的使用情況：

mysql> show global status like 'key_read%';
+-------------------+-------------+
| Variable_name     | Value       |
+-------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads         | 6798830     |
+-------------------+-------------+
2 rows in set (0.00 sec)
一共有27813678764個索引讀取請求，有6798830個請求在內存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：

key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，設置在1/1000左右較好

默認配置數值是8388600(8M)，主機有4GB內存，可以調優值為268435456(256MB)。

query_cache_size

使用查詢緩沖，MySQL將查詢結果存放在緩沖區中，今後對於同樣的SELECT語句（區分大小寫），將直接從緩沖區中讀取結果。

通過檢查狀態值Qcache_*，可以知道query_cache_size設置是否合理（上述狀態值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小；如果Qcache_hits的值不大，則表明你的查詢重復率很低，這種情況下使用查詢緩沖反而會影響效率，那麼可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。

與查詢緩沖有關的參數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查詢緩沖，可以設置為0、1、2，該變量是SESSION級的變量。

query_cache_limit指定單個查詢能夠使用的緩沖區大小，缺省為1M。

query_cache_min_res_unit是在4.1版本以後引入的，它指定分配緩沖區空間的最小單位，缺省為4K。檢查狀態值Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多，這就表明查詢結果都比較小，此時需要減小query_cache_min_res_unit。

舉例如下：

mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 22756     |
| Qcache_free_memory      | 76764704  |
| Qcache_hits             | 213028692 |
| Qcache_inserts          | 208894227 |
| Qcache_lowmem_prunes    | 4010916   |
| Qcache_not_cached       | 13385031  |
| Qcache_queries_in_cache | 43560　   |
| Qcache_total_blocks     | 111212　  |
+-------------------------+-----------+
8 rows in set (0.02 sec)
 
mysql>  show variables like 'query_cach%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 2097152   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 203423744 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.01 sec)
查詢緩存碎片率= 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%

示例服務器查詢緩存碎片率＝20.46％，查詢緩存利用率＝62.26％，查詢緩存命中率＝1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。

每個連接的緩沖

record_buffer_size

每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區。如果你做很多順序掃描，你可能想要增加該值。

默認數值是131072(128K)，可改為16773120 (16M)

read_rnd_buffer_size

隨機讀緩沖區大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區。進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數據，可適當調高該值。但MySQL會為每個客戶連接發放該緩沖空間，所以應盡量適當設置該值，以避免內存開銷過大。

一般可設置為16M

sort_buffer_size

每個需要進行排序的線程分配該大小的一個緩沖區。增加這值加速ORDER BY或GROUP BY操作。

默認數值是2097144(2M)，可改為16777208 (16M)。

join_buffer_size

聯合查詢操作所能使用的緩沖區大小

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個線程獨占，也就是說，如果有100個線程連接，則占用為16M*100

table_cache

表高速緩存的大小。每當MySQL訪問一個表時，如果在表緩沖區中還有空間，該表就被打開並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了（上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設置成很大的值。如果設置得太高，可能會造成文件描述符不足，從而造成性能不穩定或者連接失敗。

1G內存機器，推薦值是128－256。內存在4GB左右的服務器該參數可設置為256M或384M。

max_heap_table_size

用戶可以創建的內存表(memory table)的大小。這個值用來計算內存表的最大行數值。這個變量支持動態改變，即set @max_heap_table_size=#

這個變量和tmp_table_size一起限制了內部內存表的大小。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將內存中的heap表改為基於硬盤的MyISAM表。

tmp_table_size

通過設置tmp_table_size選項來增加一張臨時表的大小，例如做高級GROUP BY操作生成的臨時表。如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯接查詢速度的效果，建議盡量優化查詢，要確保查詢過程中生成的臨時表在內存中，避免臨時表過大導致生成基於硬盤的MyISAM表。

mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197   |
| Created_tmp_files       | 58      |
| Created_tmp_tables      | 1771587 |
+-------------------------+---------+
3 rows in set (0.00 sec)
每次創建臨時表，Created_tmp_tables增加，如果臨時表大小超過tmp_table_size，則是在磁盤上創建臨時表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務創建的臨時文件文件數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%,比如上面的服務器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應該相當好了

默認為16M，可調到64-256最佳，線程獨占，太大可能內存不夠I/O堵塞

thread_cache_size

可以復用的保存在中的線程的數量。如果有，新的線程從緩存中取得，當斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。

通過比較 Connections和Threads_created狀態的變量，可以看到這個變量的作用。

默認值為110，可調優為80。

thread_concurrency

推薦設置為服務器 CPU核數的2倍，例如雙核的CPU, 那麼thread_concurrency的應該為4；2個雙核的cpu, thread_concurrency的值應為8。默認為8

wait_timeout

指定一個請求的最大連接時間，對於4GB左右內存的服務器可以設置為5-10。

3配置InnoDB的幾個變量

innodb_buffer_pool_size

對於InnoDB表來說，innodb_buffer_pool_size的作用就相當於key_buffer_size對於MyISAM表的作用一樣。InnoDB使用該參數指定大小的內存來緩沖數據和索引。對於單獨的MySQL數據庫服務器，最大可以把該值設置成物理內存的80%。

根據MySQL手冊，對於2G內存的機器，推薦值是1G（50%）。

innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的數據寫入日志文件並flush磁盤的時間點，取值分別為0、1、2三個。0，表示當事務提交時，不做日志寫入操作，而是每秒鐘將log buffer中的數據寫入日志文件並flush磁盤一次；1，則在每秒鐘或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作，確保了事務的ACID；設置為2，每次事務提交引起寫入日志文件的動作，但每秒鐘完成一次flush磁盤操作。

實際測試發現，該值對插入數據的速度影響非常大，設置為2時插入10000條記錄只需要2秒，設置為0時只需要1秒，而設置為1時則需要229秒。因此，MySQL手冊也建議盡量將插入操作合並成一個事務，這樣可以大幅提高速度。

根據MySQL手冊，在允許丟失最近部分事務的危險的前提下，可以把該值設為0或2。

innodb_log_buffer_size

log緩存大小，一般為1-8M，默認為1M，對於較大的事務，可以增大緩存大小。

可設置為4M或8M。

innodb_additional_mem_pool_size

該參數指定InnoDB用來存儲數據字典和其他內部數據結構的內存池大小。缺省值是1M。通常不用太大，只要夠用就行，應該與表結構的復雜度有關系。如果不夠用，MySQL會在錯誤日志中寫入一條警告信息。

根據MySQL手冊，對於2G內存的機器，推薦值是20M，可適當增加。

innodb_thread_concurrency=8

推薦設置為 2*(NumCPUs+NumDisks)，默認一般為8.