如何優化mySQL：索引，慢速查詢，配置

MySQL 依然是全球最流行的關係型數據庫，然而，它也是最容易被低效使用的數據庫——許多人使用默認設置，而不去進一步研究。本文將回顧之前介紹過的一些 MySQL 優化技巧，並結合最新的改進方法。

核心要點

• 通過調整關鍵參數（如 innodb_buffer_pool_size、innodb_log_file_size 和 innodb_flush_method）來優化 MySQL 配置，從而更好地利用服務器資源並提高數據庫性能。
• 有效利用索引來加快查詢處理速度；根據查詢需求和數據唯一性，考慮使用唯一索引、主鍵索引和全文索引。
• 使用 Percona Toolkit for MySQL 等工具來自動識別和解決重複或未使用的索引等問題，從而優化數據庫效率。
• 使用 MySQL 的慢查詢日誌和 pt-query-digest 等工具來監控和分析慢查詢，從而檢測瓶頸並優化查詢性能。
• 定期運行 MySQL Tuner 和其他性能監控工具，以收集有關數據庫操作的見解，並根據實際使用模式微調配置。

配置優化

對 MySQL 進行的第一個也是最容易被忽略的性能提升，就是調整配置。 5.7 版本（當前版本）比之前的版本有了更好的默認值，但仍然可以進行改進。

我們假設您使用的是基於 Linux 的主機或像我們改進後的 Homestead 這樣的 Vagrant 虛擬機，因此您的配置文件位於 /etc/mysql/my.cnf。您的安裝程序可能會將輔助配置文件加載到該配置文件中，因此請檢查一下——如果 my.cnf 文件內容不多，則可能是 /etc/mysql/mysql.conf.d/mysqld.cnf 文件。

編輯配置

您需要熟悉使用命令行。即使您以前沒有接觸過，現在也是個好時機。

如果您在 Vagrant 虛擬機上本地編輯，可以使用 cp /etc/mysql/my.cnf /home/vagrant/Code 命令將文件複製到主文件系統中的共享文件夾中，使用普通的文本編輯器進行編輯，完成後再复制回原處。否則，使用像 vim 這樣的簡單文本編輯器，執行 sudo vim /etc/mysql/my.cnf 命令。

注意：修改上述路徑以匹配配置文件的實際位置——它可能實際上位於 /etc/mysql/mysql.conf.d/mysqld.cnf

手動調整

以下手動調整應該立即進行。根據這些技巧，在 [mysqld] 部分中將以下內容添加到配置文件：

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• innodb_buffer_pool_size –緩衝池是用於在內存中緩存數據和索引的存儲區域。它用於將頻繁訪問的數據保存在內存中，當您運行專用服務器或虛擬服務器且數據庫經常成為瓶頸時，為應用程序的這部分分配最多的 RAM 是有意義的。因此，我們為其分配 50-70% 的所有 RAM。 MySQL 文檔中提供緩衝池大小調整指南。
• 日誌文件大小在此處有很好的解釋，但簡而言之，它是在擦除日誌之前要存儲在日誌中的數據量。請注意，在這種情況下，日誌不是錯誤日誌或您可能習慣的日誌，而是指示檢查點時間，因為對於 MySQL，寫入發生在後台，但仍然會影響前台性能。較大的日誌文件意味著更好的性能，因為創建的新的較小的檢查點較少，但在發生崩潰時恢復時間較長（需要將更多內容重新寫入數據庫）。
• innodb_flush_log_at_trx_commit 在此處有解釋，它指示日誌文件會發生什麼情況。使用 1，我們擁有最安全的設置，因為日誌在每次事務後都會刷新到磁盤。使用 0 或 2，它 ACID 性較低，但性能更高。在這種情況下，差異不足以超過設置 1 的穩定性優勢。
• innodb_flush_method –為了完成刷新工作，將其設置為 O_DIRECT 以避免雙緩衝。除非 I/O 系統性能非常低，否則應該始終這樣做。在大多數託管服務器（如 DigitalOcean Droplets）上，您將擁有 SSD，因此 I/O 系統的性能將很高。

還有另一個來自 Percona 的工具可以幫助我們自動查找剩餘問題。請注意，如果我們在沒有上述手動調整的情況下運行它，則只有 4 個修復中的 1 個可以手動識別，因為其他 3 個取決於用戶偏好和應用程序的環境。

變量檢查器

要在 Ubuntu 上安裝變量檢查器：

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

對於其他系統，請按照說明操作。

然後，使用以下命令運行工具包：

pt-variable-advisor h=localhost,u=homestead,p=secret

您應該會看到類似於以下輸出：

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

這些都不是關鍵問題，不需要修復。我們唯一可以添加的是用於復制和快照的二進制日誌記錄。

注意：在較新版本中，binlog 大小將默認為 1G，並且 PT 不會注意到它。

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• max_binlog_size 設置確定二進制日誌的大小。這些日誌記錄您的事務和查詢並創建檢查點。如果事務大於最大值，則保存到磁盤時日誌可能大於最大值——否則，MySQL 將將其保持在該限制內。
• log_bin 選項完全啟用二進制日誌記錄。沒有它，就沒有快照或複制。請注意，這可能會對磁盤空間造成很大的壓力。激活二進制日誌記錄時，服務器 ID 是一個必要的選項，因此日誌知道它們來自哪個服務器（用於復制），格式只是寫入日誌的方式。

如您所見，新的 MySQL 具有合理的默認值，使事情幾乎可以立即投入生產。當然，每個應用程序都不同，並且有額外的自定義調整適用。

MySQL Tuner

Tuner 將以較長的間隔監控數據庫（在實時應用程序上每星期運行一次左右），並根據其在日誌中看到的內容建議更改。

只需下載即可安裝它：

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

使用 ./mysqltuner.pl 運行它將詢問您數據庫的管理員用戶名和密碼，並輸出快速掃描的信息。例如，這是我的 InnoDB 部分：

pt-variable-advisor h=localhost,u=homestead,p=secret

同樣，重要的是要注意，此工具應該在服務器運行後每星期運行一次左右。更改配置值並重新啟動服務器後，應該從那時起的一周後運行它。最好設置一個 cron 作業來為您執行此操作並定期向您發送結果。

---

每次更改配置後，請確保重新啟動 mysql 服務器：

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

索引

接下來，讓我們關注索引——許多業餘數據庫管理員的主要痛點！特別是那些立即跳入 ORM 並因此從未真正接觸過原始 SQL 的人。

注意：術語鍵和索引可以互換使用。

您可以將 MySQL 索引與書中的索引進行比較，它可以讓您輕鬆找到包含您要查找主題的正確頁面。如果沒有索引，您就必須通讀整本書才能搜索包含該主題的頁面。

您可以想像，通過索引搜索比必須遍歷每個頁面要快得多。因此，通常情況下，向數據庫添加索引可以加快 select 查詢的速度。但是，索引也必須創建和存儲。因此，更新和插入查詢將變慢，並且會佔用更多磁盤空間。通常，如果您正確地為表編制了索引，則不會注意到更新和插入的差異，因此建議在正確的位置添加索引。

僅包含幾行的表實際上並不受益於索引。您可以想像，搜索 5 頁並不比先去索引、獲取頁碼然後打開特定頁面慢多少。

那麼，我們如何找出要添加哪些索引以及存在哪些類型的索引呢？

唯一/主鍵索引

主鍵索引是數據的索引，是尋址數據的默認方式。對於用戶帳戶，這可能是用戶 ID 或用戶名，甚至是主要電子郵件。主鍵索引是唯一的。唯一索引是不能在一組數據中重複的索引。

例如，如果用戶選擇了一個特定的用戶名，則其他人都不應該能夠使用它。向用戶名列添加“唯一”索引可以解決此問題。如果其他人嘗試插入具有已存在用戶名的行，MySQL 將會報錯。

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

主鍵/索引通常在表創建時定義，唯一索引是通過更改表來定義的。

主鍵和唯一鍵都可以在一個或多個列上創建。例如，如果您想確保每個國家/地區只有一個用戶名可以定義，則可以在這兩個列上創建唯一索引，如下所示：

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

唯一索引被添加到您經常會訪問的列上。因此，如果經常請求用戶帳戶並且數據庫中有許多用戶帳戶，這是一個很好的用例。

常規索引

常規索引簡化查找。當您需要快速查找特定列或列組合的數據但該數據不需要唯一時，它們非常有用。

pt-variable-advisor h=localhost,u=homestead,p=secret

上述操作將加快按國家/地區搜索用戶名的速度。

索引還有助於提高排序和分組速度。

全文索引

全文索引用於全文搜索。只有 InnoDB 和 MyISAM 存儲引擎支持全文索引，並且僅支持 CHAR、VARCHAR 和 TEXT 列。

這些索引對於您可能需要執行的所有文本搜索都非常有用。全文索引擅長在文本正文中查找單詞。如果您經常允許在應用程序中搜索帖子、評論、說明、評論等，請在這些內容上使用這些索引。

降序索引

不是一種特殊類型，而是一種更改。從 8.0 版本開始，MySQL 支持降序索引，這意味著它可以按降序存儲索引。當您擁有經常需要先獲取最後添加的數據的大型表或優先處理條目時，這會派上用場。始終可以按降序排序，但這會帶來一點性能損失。這進一步加快了速度。

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

在處理寫入數據庫的日誌、按從後到前的順序加載的帖子和評論以及類似內容時，請考慮將 DESC 應用於索引。

輔助工具：Explain

在查看優化查詢時，EXPLAIN 工具將是無價的。在簡單查詢前添加 EXPLAIN 將以非常深入的方式處理它，分析正在使用的索引，並顯示命中和未命中的比率。您將注意到它必須處理多少行才能獲得您要查找的結果。

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

您可以使用 EXTENDED 進一步擴展它：

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

請參閱如何使用它以及通過閱讀這篇優秀的詳細文章來應用發現。

輔助工具：Percona 用於檢測重複索引

先前安裝的 Percona Toolkit 還提供了一個用於檢測重複索引的工具，當使用第三方 CMS 或只是檢查是否意外添加了比需要更多的索引時，這會派上用場。例如，默認的 WordPress 安裝在 wp_posts 表中具有重複索引：

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

如最後一行所示，它還會提供有關如何刪除重複索引的建議。

輔助工具：Percona 用於檢測未使用的索引

Percona 還可以檢測未使用的索引。如果您正在記錄慢查詢（請參見下面的“瓶頸”部分），則可以運行該工具，它將檢查這些記錄的查詢是否正在使用與查詢相關的表中的索引。

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

有關此工具的詳細用法，請參見此處。

瓶頸

本節將解釋如何檢測和監控數據庫中的瓶頸。

pt-variable-advisor h=localhost,u=homestead,p=secret

上述內容應添加到配置中。它將監控執行時間超過 1 秒的查詢以及那些未使用索引的查詢。

一旦此日誌有一些數據，您可以使用前面提到的 pt-index-usage 工具或 pt-query-digest 工具來分析其索引使用情況，該工具會生成如下結果：

<code># WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.</code>

如果您更喜歡手動分析這些日誌，也可以這樣做——但首先您需要將日誌導出為更易於“分析”的格式。這可以通過以下方式完成：

<code>max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format = 'ROW'</code>

其他參數可以進一步過濾數據並確保僅導出重要內容。例如：按平均執行時間排序的前 10 個查詢。

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

有關其他參數，請參見文檔。

結論

在這篇全面的 MySQL 優化文章中，我們研究了各種使 MySQL 運行速度更快的方法。

我們處理了配置優化，我們完成了索引，並且我們擺脫了一些瓶頸。然而，這大部分都是理論上的——有關在實際應用程序上應用這些技術的實際用例，請關注我們即將推出的性能提升項目！

我們錯過了任何技術和技巧嗎？請告訴我們！

MySQL 索引和慢查詢優化常見問題解答 (FAQ)

MySQL 索引在查詢優化中的重要性是什麼？

MySQL 索引對於查詢優化至關重要，因為它們可以顯著加快數據檢索速度。它們的工作方式類似於書中的索引，允許數據庫查找和檢索數據，而無需掃描表中的每一行。這會導致查詢執行速度更快，尤其是在大型數據庫中。但是，需要注意的是，雖然索引提高了讀取速度，但它們可能會降低寫入速度，因為在插入或更新數據時也需要更新索引。

如何識別 MySQL 中的慢查詢？

MySQL 提供了一個名為慢查詢日誌的有用工具。此工具記錄有關所有執行時間超過指定時間的 SQL 查詢的信息。您可以在 MySQL 配置文件中啟用它，並將 long_query_time 設置為查詢在被視為慢查詢之前應花費的秒數。

MySQL 索引有哪些不同類型？

MySQL 支持多種類型的索引，包括 B 樹、哈希、R 樹和全文索引。 B 樹是默認的索引類型，適用於各種查詢。哈希索引用於相等比較，並且對於此類查詢的速度比 B 樹快。 R 樹索引用於空間數據類型，全文索引用於全文搜索。

如何優化我的 MySQL 配置？

MySQL 配置優化涉及調整各種服務器變量以提高性能。這包括調整緩衝池大小、日誌文件大小和查詢緩存大小等。重要的是要定期監控服務器的性能，並根據需要調整這些變量。

哪些工具可以幫助我優化 MySQL 查詢和索引？

有幾個工具可用於 MySQL 查詢和索引優化。這些工具包括 MySQL 的內置 EXPLAIN 語句（提供有關 MySQL 如何執行查詢的信息）以及 Percona Toolkit 和 MySQL Workbench 等第三方工具。

EXPLAIN 語句如何幫助查詢優化？

MySQL 中的 EXPLAIN 語句提供有關 MySQL 如何執行查詢的信息。這包括有關訪問的表、訪問表的順序、使用的特定索引以及讀取的行數估計的信息。這些信息可以幫助識別潛在的性能問題並指導索引優化。

索引對 MySQL 中的寫入操作有什麼影響？

雖然索引通過加快數據檢索速度來顯著提高讀取操作，但它可能會降低寫入操作的速度。這是因為每次插入或更新數據時，都需要更新相應的索引。因此，在創建索引時，重要的是要在讀取和寫入操作之間取得平衡。

如何使用索引優化 MySQL 中的 JOIN 操作？

索引可以顯著提高 MySQL 中 JOIN 操作的性能。通過在 JOIN 條件中使用的列上創建索引，MySQL 可以快速找到已連接表中的匹配行。這減少了對全文掃描的需求，並導致查詢執行速度更快。

查詢緩存在 MySQL 性能優化中的作用是什麼？

MySQL 中的查詢緩存存儲 SELECT 查詢的結果以及查詢本身。當接收到相同的查詢時，MySQL 可以從緩存中檢索結果，而不是再次執行查詢。這可以顯著提高性能，尤其對於復雜的查詢或頻繁執行的查詢。

如何監控我的 MySQL 服務器的性能？

MySQL 提供了多個用於監控服務器性能的工具。這些工具包括性能模式（提供詳細的性能指標）和信息模式（提供有關數據庫元數據的信息）。此外，SHOW STATUS 命令可用於獲取有關服務器運行狀態的信息。

以上是如何優化mySQL：索引，慢速查詢，配置的詳細內容。更多資訊請關注PHP中文網其他相關文章！