1．獲取當(dāng)前配置參數(shù)

要優(yōu)化配置參數(shù)，首先要了解當(dāng)前的配置參數(shù)以及運(yùn)行情況。使用下列命令可以獲得目前服務(wù)器使用的配置參數(shù)：

mysqld –verbose –help

mysqladmin variables extended-status –u root –p

在MySQL控制臺(tái)里面，運(yùn)行下列命令可以獲取狀態(tài)變量的值：

mysql> SHOW STATUS;

如果只要檢查某幾個(gè)狀態(tài)變量，可以使用下列命令：

mysql> SHOW STATUS LIKE ‘[匹配模式]’; ( 可以使用%、?等 )

2．優(yōu)化參數(shù)

參數(shù)優(yōu)化基于一個(gè)前提，就是在我們的數(shù)據(jù)庫(kù)中通常都使用InnoDB表，而不使用MyISAM表。在優(yōu)化MySQL時(shí)，有兩個(gè)配置參數(shù)是最重要的，即table_cache和key_buffer_size。

table_cache

table_cache指定表高速緩存的大小。每當(dāng)MySQL訪問(wèn)一個(gè)表時(shí)，如果在表緩沖區(qū)中還有空間，該表就被打開(kāi)并放入其中，這樣可以更快地訪問(wèn)表內(nèi)容。通過(guò)檢查峰值時(shí)間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發(fā)現(xiàn)open_tables等于table_cache，并且opened_tables在不斷增長(zhǎng)，那么你就需要增加table_cache的值了（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設(shè)置成很大的值。如果設(shè)置得太高，可能會(huì)造成文件描述符不足，從而造成性能不穩(wěn)定或者連接失敗。

對(duì)于有1G內(nèi)存的機(jī)器，推薦值是128－256。

案例1：該案例來(lái)自一個(gè)不是特別繁忙的服務(wù)器

table_cache – 512

open_tables – 103

opened_tables – 1273

uptime – 4021421 (measured in seconds)

該案例中table_cache似乎設(shè)置得太高了。在峰值時(shí)間，打開(kāi)表的數(shù)目比table_cache要少得多。

案例2：該案例來(lái)自一臺(tái)開(kāi)發(fā)服務(wù)器。

table_cache – 64

open_tables – 64

opened-tables – 431

uptime – 1662790 (measured in seconds)

雖然open_tables已經(jīng)等于table_cache，但是相對(duì)于服務(wù)器運(yùn)行時(shí)間來(lái)說(shuō)，opened_tables的值也非常低。因此，增加table_cache的值應(yīng)該用處不大。

案例3：該案例來(lái)自一個(gè)upderperforming的服務(wù)器

table_cache – 64

open_tables – 64

opened_tables – 22423

uptime – 19538

該案例中table_cache設(shè)置得太低了。雖然運(yùn)行時(shí)間不到6小時(shí)，open_tables達(dá)到了最大值，opened_tables的值也非常高。這樣就需要增加table_cache的值。

key_buffer_size

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

key_buffer_size只對(duì)MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤(pán)表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。

對(duì)于1G內(nèi)存的機(jī)器，如果不使用MyISAM表，推薦值是16M（8-64M）。

案例1：健康狀況

key_buffer_size – 402649088 (384M)

key_read_requests – 597579931

key_reads - 56188

案例2：警報(bào)狀態(tài)

key_buffer_size – 16777216 (16M)

key_read_requests – 597579931

key_reads - 53832731

案例1中比例低于1:10000，是健康的情況；案例2中比例達(dá)到1:11，警報(bào)已經(jīng)拉響。

優(yōu)化query_cache_size

從4.0.1開(kāi)始，MySQL提供了查詢緩沖機(jī)制。使用查詢緩沖，MySQL將SELECT語(yǔ)句和查詢結(jié)果存放在緩沖區(qū)中，今后對(duì)于同樣的SELECT語(yǔ)句（區(qū)分大小寫(xiě)），將直接從緩沖區(qū)中讀取結(jié)果。根據(jù)MySQL用戶手冊(cè)，使用查詢緩沖最多可以達(dá)到238％的效率。

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

與查詢緩沖有關(guān)的參數(shù)還有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查詢緩沖，可以設(shè)置為0、1、2，該變量是SESSION級(jí)的變量。query_cache_limit指定單個(gè)查詢能夠使用的緩沖區(qū)大小，缺省為1M。query_cache_min_res_unit是在4.1版本以后引入的，它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢結(jié)果都比較小，此時(shí)需要減小query_cache_min_res_unit。

開(kāi)啟二進(jìn)制日志( Binary Log )

二進(jìn)制日志包含所有更新數(shù)據(jù)的語(yǔ)句，其目的是在恢復(fù)數(shù)據(jù)庫(kù)時(shí)用它來(lái)把數(shù)據(jù)盡可能恢復(fù)到最后的狀態(tài)。另外，如果做同步復(fù)制( Replication )的話，也需要使用二進(jìn)制日志傳送修改情況。

開(kāi)啟二進(jìn)制日志，需要設(shè)置參數(shù)log-bin。log_bin指定日志文件，如果不提供文件名，MySQL將自己產(chǎn)生缺省文件名。MySQL會(huì)在文件名后面自動(dòng)添加數(shù)字索引，每次啟動(dòng)服務(wù)時(shí)，都會(huì)重新生成一個(gè)新的二進(jìn)制文件。

此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定記錄的數(shù)據(jù)庫(kù)；使用binlog-ignore-db可以指定不記錄的數(shù)據(jù)庫(kù)。注意的是：binlog-do-db和binlog-ignore-db一次只指定一個(gè)數(shù)據(jù)庫(kù)，指定多個(gè)數(shù)據(jù)庫(kù)需要多個(gè)語(yǔ)句。而且，MySQL會(huì)將所有的數(shù)據(jù)庫(kù)名稱(chēng)改成小寫(xiě)，在指定數(shù)據(jù)庫(kù)時(shí)必須全部使用小寫(xiě)名字，否則不會(huì)起作用。

在MySQL中使用SHOW MASTER STATUS命令可以查看目前的二進(jìn)制日志狀態(tài)。

開(kāi)啟慢查詢?nèi)罩?/span>( slow query log )

慢查詢?nèi)罩緦?duì)于跟蹤有問(wèn)題的查詢非常有用。它記錄所有查過(guò)long_query_time的查詢，如果需要，還可以記錄不使用索引的記錄。下面是一個(gè)慢查詢?nèi)罩镜睦樱?/span>

開(kāi)啟慢查詢?nèi)罩荆枰O(shè)置參數(shù)log_slow_queries、long_query_times、log-queries-not-using-indexes。log_slow_queries指定日志文件，如果不提供文件名，MySQL將自己產(chǎn)生缺省文件名。long_query_times指定慢查詢的閾值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的參數(shù)，它指示記錄不使用索引的查詢。

配置InnoDB

相對(duì)于MyISAM表來(lái)說(shuō)，正確配置參數(shù)對(duì)于InnoDB表更加關(guān)鍵。其中，最重要的參數(shù)是innodb_data_file_path。它指定表數(shù)據(jù)和索引存儲(chǔ)的空間，可以是一個(gè)或者多個(gè)文件。最后一個(gè)數(shù)據(jù)文件必須是自動(dòng)擴(kuò)充的，也只有最后一個(gè)文件允許自動(dòng)擴(kuò)充。這樣，當(dāng)空間用完后，自動(dòng)擴(kuò)充數(shù)據(jù)文件就會(huì)自動(dòng)增長(zhǎng)（以8MB為單位）以容納額外的數(shù)據(jù)。例如：

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend

兩個(gè)數(shù)據(jù)文件放在不同的磁盤(pán)上。數(shù)據(jù)首先放在ibdata1中，當(dāng)達(dá)到900M以后，數(shù)據(jù)就放在ibdata2中。一旦達(dá)到50MB，ibdata2將以8MB為單位自動(dòng)增長(zhǎng)。

如果磁盤(pán)滿了，你需要在另外的磁盤(pán)上面增加一個(gè)數(shù)據(jù)文件。為此，你需要查看最后一個(gè)文件的尺寸，然后計(jì)算最接近的整數(shù)（MB）。然后手工修改該文件的大小，并添加新的數(shù)據(jù)文件。例如：假設(shè)ibdata2已經(jīng)有109MB數(shù)據(jù)，那么可以修改如下：

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend

flush_time

如果系統(tǒng)有問(wèn)題并且經(jīng)常鎖死或重新引導(dǎo)，應(yīng)將該變量設(shè)置為非零值，這將導(dǎo)致服務(wù)器按flush_time 秒來(lái)刷新表的高速緩存。用這種方法來(lái)寫(xiě)出對(duì)表的修改將降低性能，但可減少表訛誤或數(shù)據(jù)丟失的機(jī)會(huì)。

一般使用缺省值。

Binlog_cache_size

The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has binary log enabled(--log-bin option). If you often use big, multiple-statement transactions, you can increase this to get more performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.

3．存儲(chǔ)引擎

在MYSQL 3.23.0版本中，引入了MyISAM存儲(chǔ)引擎。它是一個(gè)非事務(wù)型的存儲(chǔ)引擎，成為了MYSQL的缺省存儲(chǔ)引擎。但是，如果使用設(shè)置向?qū)?lái)設(shè)置參數(shù)，則它會(huì)把InnoDB作為缺省的存儲(chǔ)引擎。InnoDB是一個(gè)事務(wù)型的存儲(chǔ)引擎。

創(chuàng)建表的時(shí)候，可以為表指定存儲(chǔ)引擎，語(yǔ)法如下：

CREATE TABLE t (i INT) ENGINE = MyISAM

CREATE TABLE t (i INT) TYPE = MyISAM

如果沒(méi)有指定，則使用缺省的存儲(chǔ)引擎。也可以使用ALTER TABLE來(lái)更換表引擎，語(yǔ)法如下：

ALTER TABLE t ENGINE = MyISAM

同一數(shù)據(jù)庫(kù)中可以包含不同存儲(chǔ)引擎的表。

事務(wù)型表具有以下特點(diǎn)：

Ø        Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.

Ø        You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).

Ø        You can execute ROLLBACK to ignore your changes (if autocommit is disabled).

Ø        If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.)

Ø        Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

非事務(wù)型表具有以下優(yōu)點(diǎn)：

Ø        Much faster

Ø        Lower disk space requirements

Ø        Less memory required to perform updates

4．MyISAM存儲(chǔ)引擎

下面MyISAM的參數(shù)是MySQL手冊(cè)推薦的參數(shù)，據(jù)說(shuō)適應(yīng)于大部分情況。對(duì)于如何監(jiān)視參數(shù)設(shè)置是否合理，仍然沒(méi)有頭緒。

max_connections=200

read_buffer_size=1M

read_rnd_buffer_size＝8M

sort_buffer_size=1M

Read_buffer_size

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you might want to increse this value.

Read_rnd_buffer_size

When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.

Bulk_insert_buffer_size

該參數(shù)于4.0.3中引入。MyISAM使用一個(gè)樹(shù)型的緩沖區(qū)來(lái)加速大量的插入，如INSERT…SELECT，INSERT…VALUES(…),VALUES(…),…,LOAD DATA INFILE等。該參數(shù)指定了緩沖區(qū)的大小。缺省值為8M，設(shè)置為0則表示不使用該優(yōu)化。

如果不使用MyISAM表，則可以將其設(shè)置為0。

5．InnoDB存儲(chǔ)引擎

參考了很多資料，都沒(méi)有明確地表明如何優(yōu)化InnoDB參數(shù)，以及如何監(jiān)視這些參數(shù)設(shè)置是否合理，只有根據(jù)MySQL用戶手冊(cè)上面的介紹來(lái)進(jìn)行設(shè)置。

innodb_buffer_pool_size

對(duì)于InnoDB表來(lái)說(shuō)，innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對(duì)于MyISAM表的作用一樣。InnoDB使用該參數(shù)指定大小的內(nèi)存來(lái)緩沖數(shù)據(jù)和索引。對(duì)于單獨(dú)的MySQL數(shù)據(jù)庫(kù)服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%。

根據(jù)MySQL手冊(cè)，對(duì)于2G內(nèi)存的機(jī)器，推薦值是1G（50%）。

innodb_flush_log_at_trx_commit

該值指定InnoDB記錄日志的方式。如果設(shè)置為1，則每個(gè)事務(wù)提交的時(shí)候，MySQL都會(huì)將事務(wù)日志寫(xiě)入磁盤(pán)。如果設(shè)置為0或者2，則大概每秒中將日志寫(xiě)入磁盤(pán)一次。（還不清楚0和2的區(qū)別）

實(shí)際測(cè)試發(fā)現(xiàn)，該值對(duì)插入數(shù)據(jù)的速度影響非常大，設(shè)置為2時(shí)插入10000條記錄只需要2秒，設(shè)置為0時(shí)只需要1秒，而設(shè)置為1時(shí)則需要229秒。因此，MySQL手冊(cè)也建議盡量將插入操作合并成一個(gè)事務(wù)，這樣可以大幅提高速度。

根據(jù)MySQL手冊(cè)，在存在丟失最近部分事務(wù)的危險(xiǎn)的前提下，可以把該值設(shè)為0。

innodb_log_file_size

The size of each log file in a log group. The default is 5MB. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But large log files also mean that recovery will be slower in case of a crash.

根據(jù)MySQL手冊(cè)，推薦值是innodb_buffer_pool_size的25%。

注意：在重新設(shè)置該值時(shí)，好像要把原來(lái)的文件刪除掉。

innodb_log_buffer_size

The size of the buffer that InnoDB uses to write to the log files on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger will save disk I/O.

根據(jù)MySQL手冊(cè)，推薦值是8M。

innodb_additional_mem_pool_size

該參數(shù)指定InnoDB用來(lái)存儲(chǔ)數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。缺省值是1M。通常不用太大，只要夠用就行，應(yīng)該與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。如果不夠用，MySQL會(huì)在錯(cuò)誤日志中寫(xiě)入一條警告信息。

根據(jù)MySQL手冊(cè)，對(duì)于2G內(nèi)存的機(jī)器，推薦值是20M。

SHOW INNODB STATUS

顯示InnoDB存儲(chǔ)引擎的狀態(tài)