15個(gè)有用的MySQL/MariaDB性能調(diào)整和優(yōu)化技巧

這篇文章將告訴你一些基本的，但非常有用的關(guān)于如何優(yōu)化 MySQL/MariaDB 性能的技巧。注意，本文假定您已經(jīng)安裝了 MySQL 或 Maria

MySQL 是一個(gè)強(qiáng)大的開源關(guān)系數(shù)據(jù)庫管理系統(tǒng)（簡(jiǎn)稱 RDBMS）。它發(fā)布于 1995 年（20年前）。它采用結(jié)構(gòu)化查詢語言（SQL），這可能是數(shù)據(jù)庫內(nèi)容管理中最流行的選擇。最新的 MySQL 版本是 5.6.25，于 2015 年 5 月 29 日發(fā)布。

關(guān)于 MySQL 一個(gè)有趣的事實(shí)是它的名字來自于 Michael Widenius（MySQL 的創(chuàng)始人）的女兒“ My”。盡管有許多關(guān)于 MySQL 有趣的傳聞，不過本文主要是向你展示一些有用的實(shí)踐，以幫助你管理你的 MySQL 服務(wù)器。

MySQL 性能優(yōu)化

2009 年 4 月，MySQL 被 Oracle 收購。其結(jié)果是MySQL 社區(qū)分裂，創(chuàng)建了一個(gè)叫 MariaDB 的分支 。創(chuàng)建該分支的主要原因是為了保持這個(gè)項(xiàng)目可以在 GPL 下的自由。

今天，MySQL 和 MariaDB 是用于類似 WordPress、Joomla、Magento 和其他 web 應(yīng)用程序的最流行的 RDMS 之一（如果不是最多的）。

這篇文章將告訴你一些基本的，但非常有用的關(guān)于如何優(yōu)化 MySQL/MariaDB 性能的技巧。注意，本文假定您已經(jīng)安裝了 MySQL 或 MariaDB。如果你仍然不知道如何在系統(tǒng)上安裝它們，你可以按照以下說明去安裝：

重要提示： 在開始之前，不要盲目的接受這些建議。每個(gè) MySQL 設(shè)置都是不同的，在進(jìn)行任何更改之前需要慎重考慮。

你需要明白這些：

MySQL/MariaDB 配置文件位于 /etc/my.cnf。 每次更改此文件后你需要重啟 MySQL 服務(wù)，以使更改生效。

這篇文章使用 MySQL 5.6 版本。

1. 啟用 InnoDB 的每張表一個(gè)數(shù)據(jù)文件設(shè)置

首先，有一個(gè)重要的解釋， InnoDB 是一個(gè)存儲(chǔ)引擎。MySQL 和 MariaDB 使用 InnoDB 作為默認(rèn)存儲(chǔ)引擎。以前，MySQL 使用系統(tǒng)表空間來保存數(shù)據(jù)庫中的表和索引。這意味著服務(wù)器唯一的目的就是數(shù)據(jù)庫處理，它們的存儲(chǔ)盤不用于其它目的。

InnoDB 提供了更靈活的方式，它把每個(gè)數(shù)據(jù)庫的信息保存在一個(gè) .ibd 數(shù)據(jù)文件中。每個(gè) .idb 文件代表它自己的表空間。通過這樣的方式可以更快地完成類似 “TRUNCATE” 的數(shù)據(jù)庫操作，當(dāng)刪除或截?cái)嘁粋€(gè)數(shù)據(jù)庫表時(shí)，你也可以回收未使用的空間。

這樣配置的另一個(gè)好處是你可以將某些數(shù)據(jù)庫表放在一個(gè)單獨(dú)的存儲(chǔ)設(shè)備。這可以大大提升你磁盤的 I/O 負(fù)載。

MySQL 5.6及以上的版本默認(rèn)啟用 innodb_file_per_table。你可以在 /etc/my.cnf 文件中看到。該指令看起來是這樣的：

2. 將 MySQL 數(shù)據(jù)庫數(shù)據(jù)存儲(chǔ)到獨(dú)立分區(qū)上

注意：此設(shè)置只在 MySQL 上有效, 在 MariaDB 上無效。

有時(shí)候操作系統(tǒng)的讀/寫會(huì)降低你 MySQL 服務(wù)器的性能，尤其是如果操作系統(tǒng)和數(shù)據(jù)庫的數(shù)據(jù)位于同一塊磁盤上。因此，我建議你使用單獨(dú)的磁盤（最好是 SSD）用于 MySQL 服務(wù)。

要完成這步，你需要將新的磁盤連接到你的計(jì)算機(jī)/服務(wù)器上。對(duì)于這篇文章，我假定磁盤掛在到 /dev/sdb。

下一步是準(zhǔn)備新的分區(qū)：

# fdisk /dev/sdb

現(xiàn)在按 “N” 來創(chuàng)建新的分區(qū)。接著按 “P”，使其創(chuàng)建為主分區(qū)。在此之后，從 1-4 設(shè)置分區(qū)號(hào)。之后，你可以選擇分區(qū)大小。這里按 enter。在下一步，你需要配置分區(qū)的大小。

如果你希望使用全部的磁盤，再按一次 enter。否則，你可以手動(dòng)設(shè)置新分區(qū)的大小。準(zhǔn)備就緒后按 “w” 保存更改?，F(xiàn)在，我們需要為我們的新分區(qū)創(chuàng)建一個(gè)文件系統(tǒng)。這可以用下面命令輕松地完成：

# mkfs.ext4 /dev/sdb1

現(xiàn)在我們會(huì)掛載新分區(qū)到一個(gè)目錄。我在根目錄下創(chuàng)建了一個(gè)名為 “ssd” 的目錄：

# mkdir /ssd/

掛載新分區(qū)到剛才創(chuàng)建的目錄下：

# mount /dev/sdb1 /ssd/

你可以在 /etc/fstab 文件中添加如下行設(shè)置為開機(jī)自動(dòng)掛載：

現(xiàn)在我們將 MySQL 移動(dòng)到新磁盤中

首先停止 MySQL 服務(wù)：

# service mysqld stop

我建議你同時(shí)停止 Apache/nginx，以防止任何試圖寫入數(shù)據(jù)庫的操作：

# service httpd stop

# service nginx stop

現(xiàn)在復(fù)制整個(gè) MySQL 目錄到新分區(qū)中:

# cp /var/lib/mysql /ssd/ -Rp

這可能需要一段時(shí)間，具體取決于你的 MySQL 數(shù)據(jù)庫的大小。一旦這個(gè)過程完成后重命名 MySQL 目錄：

# mv /var/lib/mysql /var/lib/mysql-backup

然后創(chuàng)建一個(gè)符號(hào)鏈接：

# ln -s /ssd/mysql /var/lib/mysql

現(xiàn)在啟動(dòng)你的 MySQL 和 web 服務(wù)：

以后你的數(shù)據(jù)庫將使用新的磁盤訪問。

3. 優(yōu)化使用 InnoDB 的緩沖池

InnoDB 引擎在內(nèi)存中有一個(gè)緩沖池用于緩存數(shù)據(jù)和索引。這當(dāng)然有助于你更快地執(zhí)行 MySQL/MariaDB 查詢語句。選擇合適的內(nèi)存大小需要一些重要的決策并對(duì)系統(tǒng)的內(nèi)存消耗有較多的認(rèn)識(shí)。

下面是你需要考慮的:

其它的進(jìn)程需要消耗多少內(nèi)存。這包括你的系統(tǒng)進(jìn)程，頁表，套接字緩沖。

你的服務(wù)器是否專門用于 MySQL 還是你運(yùn)行著其它非常消耗內(nèi)存的服務(wù)。

在一個(gè)專用的機(jī)器上，你可能會(huì)把 60-70％ 的內(nèi)存分配給 innodb_buffer_pool_size。如果你打算在一個(gè)機(jī)器上運(yùn)行更多的服務(wù)，你應(yīng)該重新考慮專門用于 innodb_buffer_pool_size 的內(nèi)存大小。

你需要設(shè)置 my.cnf 中的此項(xiàng):

innodb_buffer_pool_size

4. 在 MySQL 中避免使用 Swappiness

“交換”是一個(gè)當(dāng)系統(tǒng)移動(dòng)部分內(nèi)存到一個(gè)稱為 “交換空間” 的特殊磁盤空間時(shí)的過程。通常當(dāng)你的系統(tǒng)用完物理內(nèi)存后就會(huì)出現(xiàn)這種情況，系統(tǒng)將信息寫入磁盤而不是釋放一些內(nèi)存。正如你猜測(cè)的磁盤比你的內(nèi)存要慢得多。

該選項(xiàng)默認(rèn)情況下是啟用的：

# sysctl vm.swappiness

運(yùn)行以下命令關(guān)閉 swappiness：

# sysctl -w vm.swappiness=0

5. 設(shè)置 MySQL 的最大連接數(shù)

max_connections 指令告訴你當(dāng)前你的服務(wù)器允許多少并發(fā)連接。MySQL/MariaDB 服務(wù)器允許有 SUPER 權(quán)限的用戶在最大連接之外再建立一個(gè)連接。只有當(dāng)執(zhí)行 MySQL 請(qǐng)求的時(shí)候才會(huì)建立連接，執(zhí)行完成后會(huì)關(guān)閉連接并被新的連接取代。

請(qǐng)記住，，太多的連接會(huì)導(dǎo)致內(nèi)存的使用量過高并且會(huì)鎖住你的 MySQL 服務(wù)器。一般小網(wǎng)站需要 100-200 的連接數(shù)，而較大可能需要 500-800 甚至更多。這里的值很大程度上取決于你 MySQL/MariaDB 的使用情況。

你可以動(dòng)態(tài)地改變 max_connections 的值而無需重啟MySQL服務(wù)器：

# mysql -u root -p

6. 配置 MySQL 的線程緩存數(shù)量

thread_cache_size 指令用來設(shè)置你服務(wù)器緩存的線程數(shù)量。當(dāng)客戶端斷開連接時(shí)，如果當(dāng)前線程數(shù)小于 thread_cache_size，它的線程將被放入緩存中。下一個(gè)請(qǐng)求通過使用緩存池中的線程來完成。

要提高服務(wù)器的性能，你可以設(shè)置 thread_cache_size 的值相對(duì)高一些。你可以通過以下方法來查看線程緩存命中率：

你可以用以下公式來計(jì)算線程池的命中率：

如果你得到一個(gè)較低的數(shù)字，這意味著大多數(shù) mysql 連接使用新的線程，而不是從緩存加載。在這種情況下，你需要增加 thread_cache_size。

這里有一個(gè)好處是可以動(dòng)態(tài)地改變 thread_cache_size 而無需重啟 MySQL 服務(wù)。你可以通過以下方式來實(shí)現(xiàn)：

7. 禁用 MySQL 的 DNS 反向查詢

默認(rèn)情況下當(dāng)新的連接出現(xiàn)時(shí)，MySQL/MariaDB 會(huì)進(jìn)行 DNS 查詢解析用戶的 IP 地址/主機(jī)名。對(duì)于每個(gè)客戶端連接，它的 IP 都會(huì)被解析為主機(jī)名。然后，主機(jī)名又被反解析為 IP 來驗(yàn)證兩者是否一致。

當(dāng) DNS 配置錯(cuò)誤或服務(wù)器出現(xiàn)問題時(shí)，這很可能會(huì)導(dǎo)致延遲。這就是為什么要關(guān)閉 DNS 的反向查詢的原因，你可以在你的配置文件中添加以下選項(xiàng)去設(shè)定：

更改后你需要重啟 MySQL 服務(wù)。

8. 配置 MySQL 的查詢緩存容量

如果你有很多重復(fù)的查詢并且數(shù)據(jù)不經(jīng)常改變 – 請(qǐng)使用緩存查詢。 人們常常不理解 query_cache_size 的實(shí)際含義而將此值設(shè)置為 GB 級(jí)，這實(shí)際上會(huì)降低服務(wù)器的性能。

背后的原因是，在更新過程中線程需要鎖定緩存。通常設(shè)置為 200-300 MB應(yīng)該足夠了。如果你的網(wǎng)站比較小的，你可以嘗試給 64M 并在以后及時(shí)去增加。

在你的 MySQL 配置文件中添加以下設(shè)置：

9. 配置臨時(shí)表容量和內(nèi)存表最大容量

tmp_table_size 和 max_heap_table_size 這兩個(gè)變量的大小應(yīng)該相同，它們可以讓你避免磁盤寫入。tmp_table_size 是內(nèi)置內(nèi)存表的最大空間。如果表的大小超出限值將會(huì)被轉(zhuǎn)換為磁盤上的 MyISAM 表。

這會(huì)影響數(shù)據(jù)庫的性能。管理員通常建議在服務(wù)器上設(shè)置這兩個(gè)值為每 GB 內(nèi)存給 64M。

10. 啟用 MySQL 慢查詢?nèi)罩?

記錄慢查詢可以幫助你定位數(shù)據(jù)庫中的問題并幫助你調(diào)試。這可以通過在你的 MySQL 配置文件中添加以下值來啟用：

第一個(gè)變量啟用慢查詢?nèi)罩?，第二個(gè)告訴 MySQL 實(shí)際的日志文件存儲(chǔ)位置。使用 long_query_time 來定義完成 MySQL 查詢多少用時(shí)算長。

11. 檢查 MySQL 的空閑連接

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com