Mysql效能最佳化小建議，Mysql效能最佳化

Mysql效能最佳化小建議，Mysql效能最佳化

Mysql的效能最佳化主要參考文章[1]，[2]，和[3]，其中已使用且比較有效果的有：

1. 實用最佳化

（1）禁止autocommit, 防止每次插入都提交，重新整理log

SET autocommit=0;... SQL import statements ...COMMIT;

（2） 對頻繁查詢的欄位建立索引，但要注意加入索引後，執行插入操作時會變慢

（3）當只要一行資料時使用 LIMIT 1

SELECT 1 FROM tbl_name LIMIT 1

註：SELECT 1 是用來查看是否有記錄的，並一般用作條件查詢（normally it will be used with WHERE and often EXISTS）, 返回的所有行的值都是1。效率上來說，1>anycol>*，因為不用查字典表。[4]

（4）永遠為每張表設定一個ID
為資料庫裡的每張表都設定一個ID做為其主鍵，而且最好的是一個INT型的（推薦使用UNSIGNED），並設定上自動增加的AUTO_INCREMENT標誌。

（5）一次插入多行

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

2. 配置最佳化

下面對Disk I/O和query_cache的最佳化做一個總結，並假設我們使用的機器記憶體為8GB。以下的參數都在檔案my.cnf的[mysqld]下設定。

（1）innodb_buffer_pool_size 和 innodb_log_file_size

建議設定大小來自文章[5]

## Set buffer pool size to 50-80% of your computer's memoryinnodb_buffer_pool_size=4Ginnodb_additional_mem_pool_size=256M## Set the log file size to about 25% of the buffer pool sizeinnodb_log_file_size=1Ginnodb_log_buffer_size=256M

怎麼安全的更改這個配置，來自於[6]

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"service mysqld stoprm -f /var/lib/mysql/ib_logfile[01]service mysqld start

其中 service mysqld stop是centos下的命令，但ubuntu等其他Linux系統可能實用service mysql stop
（2）query_cache_size

參數的說明見文章[7] ，但並未提到建議大小，反而提到設定太大也會有壞處。本人設定的大小如下：

query_cache_type=1query_cache_limit=2Mquery_cache_size=128M

其中query_chache_type=1表示開啟查詢快取，query_cache_size是總的查詢快取大小，query_cahce_limit表示單個查詢最大的緩衝大小。

設定完之後，執行以下操作便可：

service mysqld restart

（3） innodb_flush_method

innodb_flush_method設定成O_DIRECT還是O_DSYNC，文章[2]中說設定成O_DIRECT會增加效能，但在文章[8]和[2]中這兩個參數在實際使用時差不多，並且跟具體使用的硬體相關。所以設定成O_DIRECT是否會最佳化效能，還不是很確定

（4）max_allowed_packet

此參數是當網路傳輸資料時，需要控制的參數，如果傳輸的資料太大（特別是當存在large BLOB columns or long strings資料時），超過max_allowed_packet的上限時，就有可能發生錯誤，所以就要提高此參數。本人的設定是：

max_allowed_packet = 16M

註：

1. 查看系統變數的一些命令

show variables like 'innodb_buffer%';SHOW GLOBAL STATUS LIKE '%innodb%';show global status like 'Qc%';

2. 本人的配置

Reference

[1] InnoDB Performance Tuning Tips

[2] How to improve MySQL INSERT and UPDATE performance?

[3] MySQL效能最佳化的最佳20+條經驗

[4] select 1 from ... sql語句中的1代表什麼意思？

[5] InnoDB Configuration

[6] How to safely change MySQL innodb variable 'innodb_log_file_size'?

[7] Query Cache Configuration

[8] mysql setting variable innodb_flush_method to O_DSYNC or O_DIRECT