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 memory
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=256M
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=1G
innodb_log_buffer_size=256M

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

 

 

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysqld stop
rm -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=1
query_cache_limit=2M
query_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. 本人的配置