MySQL 調優和使用必讀

MySQL 調優和使用必讀

MySQL 應該是最流行了 WEB 後端資料庫。WEB 開發語言最近發展很快，PHP， Ruby, Python, Java 各有特點，雖然 NOSQL 最近越來越多的被提到，但是相信大部分架構師還是會選擇 MySQL 來做資料存放區。

MySQL 如此方便和穩定，以至於我們在開發 WEB 程式的時候很少想到它。即使想到最佳化也是程式層級的，比如，不要寫過於消耗資源的 SQL 陳述式。但是除此之外，在整個系統上仍然有很多可以最佳化的地方。

1. 選擇合適的儲存引擎: InnoDB

除非你的資料表使用來做唯讀或者全文檢索索引 (相信現在提到全文檢索索引，沒人會用 MYSQL 了)，你應該預設選擇 InnoDB 。

你自己在測試的時候可能會發現 MyISAM 比 InnoDB 速度快，這是因為： MyISAM 只緩衝索引，而 InnoDB 快取資料和索引，MyISAM 不支援事務。但是 如果你使用 innodb_flush_log_at_trx_commit = 2 可以獲得接近的讀取效能 (相差百倍) 。

1.1 如何將現有的 MyISAM 資料庫轉換為 InnoDB:

1. mysql -u [USER_NAME]-p -e "SHOW TABLES IN [DATABASE_NAME];"| tail -n +2| xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;"> alter_table.sql
2. perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB/\1MyISAM/g' alter_table.sql
3. mysql -u [USER_NAME]-p [DATABASE_NAME]< alter_table.sql

1.2 為每個表分別建立 InnoDB FILE：

1. innodb_file_per_table=1

這樣可以保證 ibdata1 檔案不會過大，失去控制。尤其是在執行 mysqlcheck -o –all-databases 的時候。

2. 保證從記憶體中讀取資料，講資料儲存在記憶體中2.1 足夠大的 innodb_buffer_pool_size

推薦將資料完全儲存在 innodb_buffer_pool_size ，即按儲存量規劃 innodb_buffer_pool_size 的容量。這樣你可以完全從記憶體中讀取資料，最大限度減少磁碟操作。

2.1.1 如何確定 innodb_buffer_pool_size 足夠大，資料是從記憶體讀取而不是硬碟？

方法 1

1. mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
2. +----------------------------------+--------+
3. |Variable_name|Value|
4. +----------------------------------+--------+
5. |Innodb_buffer_pool_pages_data|129037|
6. |Innodb_buffer_pool_pages_dirty|362|
7. |Innodb_buffer_pool_pages_flushed|9998|
8. |Innodb_buffer_pool_pages_free|0|!!!!!!!!
9. |Innodb_buffer_pool_pages_misc|2035|
10. |Innodb_buffer_pool_pages_total|131072|
11. +----------------------------------+--------+
12. 6 rows inset(0.00 sec)

發現 Innodb_buffer_pool_pages_free 為 0，則說明 buffer pool 已經被用光，需要增大 innodb_buffer_pool_size

InnoDB 的其他幾個參數：

1. innodb_additional_mem_pool_size =1/200 of buffer_pool
2. innodb_max_dirty_pages_pct 80%

方法 2

或者用iostat -d -x -k 1 命令，查看硬碟的操作。

2.1.2 伺服器上是否有足夠記憶體用來規劃

執行 echo 1 > /proc/sys/vm/drop_caches 清除作業系統的檔案快取，可以看到真正的記憶體使用量量。

2.2 資料預熱

預設情況，只有某條資料被讀取一次，才會緩衝在 innodb_buffer_pool。所以，資料庫剛剛啟動，需要進行資料預熱，將磁碟上的所有資料緩衝到記憶體中。資料預熱可以提高讀取速度。

對於 InnoDB 資料庫，可以用以下方法，進行資料預熱:

1. 將以下指令碼儲存為 MakeSelectQueriesToLoad.sql

1. SELECT DISTINCT
2. CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
3. ' ORDER BY ',ndxcollist,';')SelectQueryToLoadCache
4. FROM
5. (
6. SELECT
7. engine,table_schema db,table_name tb,
8. index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
9. FROM
10. (
11. SELECT
12. B.engine,A.table_schema,A.table_name,
13. A.index_name,A.column_name,A.seq_in_index
14. FROM
15. information_schema.statistics A INNER JOIN
16. (
17. SELECT engine,table_schema,table_name
18. FROM information_schema.tables WHERE
19. engine='InnoDB'
20. ) B USING (table_schema,table_name)
21. WHERE B.table_schema NOT IN ('information_schema','mysql')
22. ORDER BY table_schema,table_name,index_name,seq_in_index
23. ) A
24. GROUP BY table_schema,table_name,index_name
25. ) AA
26. ORDER BY db,tb
27. ;

2. 執行

1. mysql -uroot -AN </root/MakeSelectQueriesToLoad.sql >/root/SelectQueriesToLoad.sql

3. 每次重啟資料庫，或者整庫備份前需要預熱的時候執行：

1. mysql -uroot </root/SelectQueriesToLoad.sql >/dev/null2>&1

2.3 不要讓資料存到 SWAP 中

如果是專用 MYSQL 伺服器，可以禁用 SWAP，如果是共用伺服器，確定 innodb_buffer_pool_size 足夠大。或者使用固定的記憶體空間做緩衝，使用 memlock 指令。

3. 定期最佳化重建資料庫

mysqlcheck -o –all-databases 會讓 ibdata1 不斷增大，真正的最佳化只有重建資料表結構：

1. CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
2. INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
3. ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
4. ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
5. DROP TABLE mydb.mytablezap;

4. 減少磁碟寫入操作4.1 使用足夠大的寫入緩衝 innodb_log_file_size

但是需要注意如果用 1G 的 innodb_log_file_size ，假如伺服器當機，需要 10 分鐘來恢複。

推薦 innodb_log_file_size 設定為 0.25 * innodb_buffer_pool_size

4.2 innodb_flush_log_at_trx_commit

這個選項和寫磁碟操作密切相關：

innodb_flush_log_at_trx_commit = 1 則每次修改寫入磁碟
innodb_flush_log_at_trx_commit = 0/2 每秒寫入磁碟

如果你的應用不涉及很高的安全性 (金融系統)，或者基礎架構足夠安全，或者 事務都很小，都可以用 0 或者 2 來降低磁碟操作。

4.3 避免雙寫入緩衝

1. innodb_flush_method=O_DIRECT

5. 提高磁碟讀寫速度

RAID0 尤其是在使用 EC2 這種虛擬磁碟 (EBS) 的時候，使用軟 RAID0 非常重要。

6. 充分使用索引6.1 查看現有表結構和索引

1. SHOW CREATE TABLE db1.tb1\G

6.2 添加必要的索引

索引是提高查詢速度的唯一方法，比如搜尋引擎用的倒排索引是一樣的原理。

索引的添加需要根據查詢來確定，比如通過慢查詢日誌或者查詢日誌,或者通過 EXPLAIN 命令分析查詢。

1. ADD UNIQUE INDEX
2. ADD INDEX

6.2.1 比如，最佳化使用者驗證表：

添加索引

1. ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
2. ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

每次重啟伺服器進行資料預熱

1. echo “select username,password from users;”>/var/lib/mysql/upcache.sql

添加啟動指令碼到 my.cnf

1. [mysqld]
2. init-file=/var/lib/mysql/upcache.sql

6.2.2 使用自動加索引的架構或者自動拆分表結構的架構

比如，Rails 這樣的架構，會自動添加索引，Drupal 這樣的架構會自動拆分表結構。會在你開發的初期指明正確的方向。所以，經驗不太豐富的人一開始就追求從 0 開始構建，實際是不好的做法。

7. 分析查詢日誌和慢查詢日誌

記錄所有查詢，這在用 ORM 系統或者產生查詢語句的系統很有用。

1. log=/var/log/mysql.log

注意不要在生產環境用，否則會佔滿你的磁碟空間。

記錄執行時間超過 1 秒的查詢：

1. long_query_time=1
2. log-slow-queries=/var/log/mysql/log-slow-queries.log

8. 激進的方法，使用記憶體磁碟

現在基礎設施的可靠性已經非常高了，比如 EC2 幾乎不用擔心伺服器硬體當機。而且記憶體實在是便宜，很容易買到幾十G記憶體的伺服器，可以用記憶體磁碟，定期備份到磁碟。

將 MYSQL 目錄遷移到 4G 的記憶體磁碟

1. mkdir -p /mnt/ramdisk
2. sudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/
3. mv /var/lib/mysql /mnt/ramdisk/mysql
4. ln -s /tmp/ramdisk/mysql /var/lib/mysql
5. chown mysql:mysql mysql

9. 用 NOSQL 的方式使用 MYSQL

B-TREE 仍然是最高效的索引之一，所有 MYSQL 仍然不會過時。

用 HandlerSocket 跳過 MYSQL 的 SQL 解析層，MYSQL 就真正變成了 NOSQL。

10. 其他

• 單條查詢最後增加 LIMIT 1，停止全表掃描。
• 將非”索引”資料分離，比如將大篇文章分離儲存，不影響其他自動查詢。
• 不用 MYSQL 內建的函數，因為內建函數不會建立查詢快取。
• PHP 的建立連線速度非常快，所有可以不用串連池，否則可能會造成超過串連數。當然不用串連池 PHP 程式也可能將
• 串連數佔滿比如用了 @ignore_user_abort(TRUE);
• 使用 IP 而不是網域名稱做資料庫路徑，避免 DNS 解析問題

11. 結束

你會發現最佳化後，資料庫的效能提高几倍到幾百倍。所以 MYSQL 基本還是可以適用大部分情境的應用的。最佳化現有系統的成本比系統重構或者遷移到 NOSQL 低很多。

本文永久更新連結地址：