15 個有用的 MySQL/MariaDB 性能調整和優化技巧
MySQL 是一個強大的開源關系數據庫管理系統（簡稱 RDBMS）。它發布于 1995 年（20年前）。它采用結構化查詢語言（SQL），這可能是數據庫內容管理中最流行的選擇。最新的 MySQL 版本是 5.6.25，于 2015 年 5 月 29 日發布。
關于 MySQL 一個有趣的事實是它的名字來自于 Michael Widenius（MySQL 的創始人）的女兒“ My”。盡管有許多關于 MySQL 有趣的傳聞，不過本文主要是向你展示一些有用的實踐，以幫助你管理你的 MySQL 服務器。
MySQL 性能優化
2009 年 4 月，MySQL 被 Oracle 收購。其結果是MySQL 社區分裂，創建了一個叫 MariaDB 的分支 。創建該分支的主要原因是為了保持這個項目可以在 GPL 下的自由。
今天，MySQL 和 MariaDB 是用于類似 WordPress、Joomla、Magento 和其他 web 應用程序的最流行的 RDMS 之一（如果不是最多的）。
這篇文章將告訴你一些基本的，但非常有用的關于如何優化 MySQL/MariaDB 性能的技巧。注意，本文假定您已經安裝了 MySQL 或 MariaDB。如果你仍然不知道如何在系統上安裝它們，你可以按照以下說明去安裝：
- 在 RHEL/CentOS 7 上安裝 LAMP
- 在 Fedora 22 上安裝 LAMP
- 在 Ubuntu 15.04 安裝 LAMP
- 在 Debian 8 上安裝 MariaDB
- 在 Gentoo Linux 上安裝 MariaDB
- 在 Arch Linux 上安裝 MariaDB </ul>
- MySQL/MariaDB 配置文件位于/etc/my.cnf。 每次更改此文件后你需要重啟 MySQL 服務，以使更改生效。
- 這篇文章使用 MySQL 5.6 版本。 </ul>
重要提示： 在開始之前，不要盲目的接受這些建議。每個 MySQL 設置都是不同的，在進行任何更改之前需要慎重考慮。
你需要明白這些：
1. 啟用 InnoDB 的每張表一個數據文件設置
首先，有一個重要的解釋， InnoDB 是一個存儲引擎。MySQL 和 MariaDB 使用 InnoDB 作為默認存儲引擎。以前，MySQL 使用系統表空間來保存數據庫中的表和索引。這意味著服務器唯一的目的就是數據庫處理，它們的存儲盤不用于其它目的。
InnoDB 提供了更靈活的方式，它把每個數據庫的信息保存在一個.ibd數據文件中。每個 .idb 文件代表它自己的表空間。通過這樣的方式可以更快地完成類似 “TRUNCATE” 的數據庫操作，當刪除或截斷一個數據庫表時，你也可以回收未使用的空間。
這樣配置的另一個好處是你可以將某些數據庫表放在一個單獨的存儲設備。這可以大大提升你磁盤的 I/O 負載。
MySQL 5.6及以上的版本默認啟用innodb_file_per_table。你可以在 /etc/my.cnf 文件中看到。該指令看起來是這樣的：
innodb_file_per_table=1
2. 將 MySQL 數據庫數據存儲到獨立分區上
注意：此設置只在 MySQL 上有效, 在 MariaDB 上無效。
有時候操作系統的讀/寫會降低你 MySQL 服務器的性能，尤其是如果操作系統和數據庫的數據位于同一塊磁盤上。因此，我建議你使用單獨的磁盤（最好是 SSD）用于 MySQL 服務。
要完成這步，你需要將新的磁盤連接到你的計算機/服務器上。對于這篇文章，我假定磁盤掛在到 /dev/sdb。
下一步是準備新的分區：
# fdisk /dev/sdb
現在按 “N” 來創建新的分區。接著按 “P”，使其創建為主分區。在此之后，從 1-4 設置分區號。之后，你可以選擇分區大小。這里按 enter。在下一步，你需要配置分區的大小。
如果你希望使用全部的磁盤，再按一次 enter。否則，你可以手動設置新分區的大小。準備就緒后按 “w” 保存更改。現在，我們需要為我們的新分區創建一個文件系統。這可以用下面命令輕松地完成：
# mkfs.ext4 /dev/sdb1
現在我們會掛載新分區到一個目錄。我在根目錄下創建了一個名為 “ssd” 的目錄：
# mkdir /ssd/
掛載新分區到剛才創建的目錄下：
# mount /dev/sdb1 /ssd/
你可以在 /etc/fstab 文件中添加如下行設置為開機自動掛載：
/dev/sdb1 /ssd ext3 defaults 0 0
現在我們將 MySQL 移動到新磁盤中
首先停止 MySQL 服務：
# service mysqld stop
我建議你同時停止 Apache/nginx，以防止任何試圖寫入數據庫的操作：
# service httpd stopservice nginx stop</pre>
現在復制整個 MySQL 目錄到新分區中:
# cp /var/lib/mysql /ssd/ -Rp這可能需要一段時間，具體取決于你的 MySQL 數據庫的大小。一旦這個過程完成后重命名 MySQL 目錄：
# mv /var/lib/mysql /var/lib/mysql-backup然后創建一個符號鏈接：
# ln -s /ssd/mysql /var/lib/mysql現在啟動你的 MySQL 和 web 服務：
# service mysqld startservice httpd start
service nginx start</pre>
以后你的數據庫將使用新的磁盤訪問。
3. 優化使用 InnoDB 的緩沖池
InnoDB 引擎在內存中有一個緩沖池用于緩存數據和索引。這當然有助于你更快地執行 MySQL/MariaDB 查詢語句。選擇合適的內存大小需要一些重要的決策并對系統的內存消耗有較多的認識。
下面是你需要考慮的:
- 其它的進程需要消耗多少內存。這包括你的系統進程，頁表，套接字緩沖。
- 你的服務器是否專門用于 MySQL 還是你運行著其它非常消耗內存的服務。 </ul>
在一個專用的機器上，你可能會把 60-70％ 的內存分配給innodb_buffer_pool_size。如果你打算在一個機器上運行更多的服務，你應該重新考慮專門用于innodb_buffer_pool_size的內存大小。
你需要設置 my.cnf 中的此項:
innodb_buffer_pool_size
4. 在 MySQL 中避免使用 Swappiness
“交換”是一個當系統移動部分內存到一個稱為 “交換空間” 的特殊磁盤空間時的過程。通常當你的系統用完物理內存后就會出現這種情況，系統將信息寫入磁盤而不是釋放一些內存。正如你猜測的磁盤比你的內存要慢得多。
該選項默認情況下是啟用的：
# sysctl vm.swappinessvm.swappiness = 60</pre>
運行以下命令關閉 swappiness：
# sysctl -w vm.swappiness=05. 設置 MySQL 的最大連接數
max_connections指令告訴你當前你的服務器允許多少并發連接。MySQL/MariaDB 服務器允許有 SUPER 權限的用戶在最大連接之外再建立一個連接。只有當執行 MySQL 請求的時候才會建立連接，執行完成后會關閉連接并被新的連接取代。
請記住，太多的連接會導致內存的使用量過高并且會鎖住你的 MySQL 服務器。一般小網站需要 100-200 的連接數，而較大可能需要 500-800 甚至更多。這里的值很大程度上取決于你 MySQL/MariaDB 的使用情況。
你可以動態地改變max_connections的值而無需重啟MySQL服務器：
# mysql -u root -p mysql> set global max_connections = 300;6. 配置 MySQL 的線程緩存數量
thread_cache_size指令用來設置你服務器緩存的線程數量。當客戶端斷開連接時，如果當前線程數小于thread_cache_size，它的線程將被放入緩存中。下一個請求通過使用緩存池中的線程來完成。
要提高服務器的性能，你可以設置thread_cache_size的值相對高一些。你可以通過以下方法來查看線程緩存命中率：
mysql> show status like 'Threads_created'; mysql> show status like 'Connections';你可以用以下公式來計算線程池的命中率：
100 - ((Threads_created / Connections) * 100)如果你得到一個較低的數字，這意味著大多數 mysql 連接使用新的線程，而不是從緩存加載。在這種情況下，你需要增加thread_cache_size。
這里有一個好處是可以動態地改變thread_cache_size而無需重啟 MySQL 服務。你可以通過以下方式來實現：
mysql> set global thread_cache_size = 16;7. 禁用 MySQL 的 DNS 反向查詢
默認情況下當新的連接出現時，MySQL/MariaDB 會進行 DNS 查詢解析用戶的 IP 地址/主機名。對于每個客戶端連接，它的 IP 都會被解析為主機名。然后，主機名又被反解析為 IP 來驗證兩者是否一致。
當 DNS 配置錯誤或服務器出現問題時，這很可能會導致延遲。這就是為什么要關閉 DNS 的反向查詢的原因，你可以在你的配置文件中添加以下選項去設定：
[mysqld]Skip reverse DNS lookup of clients
skip-name-resolve</pre>
更改后你需要重啟 MySQL 服務。
8. 配置 MySQL 的查詢緩存容量
如果你有很多重復的查詢并且數據不經常改變 – 請使用緩存查詢。 人們常常不理解query_cache_size的實際含義而將此值設置為 GB 級，這實際上會降低服務器的性能。
背后的原因是，在更新過程中線程需要鎖定緩存。通常設置為 200-300 MB應該足夠了。如果你的網站比較小的，你可以嘗試給 64M 并在以后及時去增加。
在你的 MySQL 配置文件中添加以下設置：
query_cache_type = 1 query_cache_limit = 256K query_cache_min_res_unit = 2k query_cache_size = 80M9. 配置臨時表容量和內存表最大容量
tmp_table_size和max_heap_table_size這兩個變量的大小應該相同，它們可以讓你避免磁盤寫入。tmp_table_size是內置內存表的最大空間。如果表的大小超出限值將會被轉換為磁盤上的 MyISAM 表。
這會影響數據庫的性能。管理員通常建議在服務器上設置這兩個值為每 GB 內存給 64M。
[mysqld] tmp_table_size= 64M max_heap_table_size= 64M10. 啟用 MySQL 慢查詢日志
記錄慢查詢可以幫助你定位數據庫中的問題并幫助你調試。這可以通過在你的 MySQL 配置文件中添加以下值來啟用：
slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log long_query_time = 1第一個變量啟用慢查詢日志，第二個告訴 MySQL 實際的日志文件存儲位置。使用long_query_time來定義完成 MySQL 查詢多少用時算長。
11. 檢查 MySQL 的空閑連接
空閑連接會消耗資源，可以的話應該被終止或者刷新。空閑連接是指處于 “sleep” 狀態并且保持了很長一段時間的連接。你可以通過運行以下命令查看空閑連接：
# mysqladmin processlist -u root -p | grep “Sleep”這會顯示處于睡眠狀態的進程列表。當代碼使用持久連接到數據庫時會出現這種情況。使用 PHP 調用 mysql_pconnect 可以打開這個連接，執行完查詢之后，刪除認證信息并保持連接為打開狀態。這會導致每個線程的緩沖都被保存在內存中，直到該線程結束。
首先你要做的就是檢查代碼問題并修復它。如果你不能訪問正在運行的代碼，你可以修改wait_timeout變量。默認值是 28800 秒，而你可以安全地將其降低到 60 ：
wait_timeout=6012. 為 MySQL 選擇正確的文件系統
選擇正確的文件系統對數據庫至關重要。在這里你需要考慮的最重要的事情是 - 數據的完整性，性能和易管理性。
按照 MariaDB 的建議，最好的文件系統是XFS、ext4 和 Btrfs。它們都是可以使用超大文件和大容量存儲卷的企業級日志型文件系統。
下面你可以找到一些關于這三個文件系統的有用信息：
| 文件系統 | XFS | Ext4 | Btrfs | </tr> </tbody>|||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 文件系統最大容量 | 8EB | 1EB | 16EB | </tr>|||||||||||||||||||||||||
| 最大文件大小 | 8EB | 16TB | 16EB | </tr> </tbody> </table>
