MySQL怎麼優化效能？優化技巧分享

當談到資料庫效能最佳化時，最重要的事情就是選擇正確的。你應該決定你的應用程式是需要關係型資料庫還是非關係型資料庫。即使在一種類型中，你也將有多種選擇。與關聯式資料庫一樣，你可能會發現 Oracle、MySQL、SQL Server 和 PostgreSQL。另一方面，非關係型資料庫引入了 MongoDB、Cassandra 和 CouchDB。

你可能希望我建議使用非關聯式資料庫以獲得更快的讀/寫效能。但是，透過一些改進和調整，您可以推動關聯式資料庫超出其已知限制。因此，在本文中，我將向您介紹一些技巧，以使您的 MySQL 資料庫更快。

如果您特別想知道為什麼要使用 MySQL，那麼答案很簡單，因為它是免費的、開源的，並且在 PHP 社群中非常受歡迎，而 Oracle 因價格昂貴而未被廣泛使用。其他選項不如 MySQL 受歡迎。

- MySQL伺服器設定：\

好吧，首先你應該知道設定檔的位置，這取決於你的作業系統。在 Linux 系統中，它位於「/etc/mysql/my.cnf」中。 \
現在是時候選擇你的引擎 InnoDB 和 MyISAM 了。為了讓選擇更容易，你應該知道InnoDB 成為MySQL 5.5 的預設引擎，因為它支援“行級鎖定、外鍵和事務”，而MyISAM 不支援任何提到的特性，這使得它在現代很少有用應用程式. \
選擇正確的引擎後，是時候在 my.cnf 檔案中設定一些配置變數了。

max_connection 變數：\

max_connection 變數表示應用程式允許的連線數。預設值為151 連接，但是，如果你收到錯誤訊息“MySQL 錯誤，連接太多...”，你可以輕鬆增加此數量\

最大连接数 = 170

innodb_buffer_pool_size 變數：\

為了加快速度，MySQL 會將資料快取在你的伺服器記憶體中，這個變數告訴MySQL 它可以使用多少GigaBytes。如果你在資料庫中保存大 blob，這個變數非常有用。你可以將其設定為伺服器記憶體的 80–90%。因此，如果你的伺服器記憶體為 16GB，則可以設定為 14GB。

innodb_buffer_pool_size = 14GB

innodb_io_capacity 變數：\

這個變數告訴 MySQL 它可以使用多少輸入/輸出操作，它取決於你的磁碟。例如，單一 7200 RPM 磁碟機限制為 200 I/O，而企業 SSD 磁碟限制為 50,000 I/O。你可以在作業系統上透過命令列輕鬆找到輸入/輸出值，並將變數設定為可用 I/O 的 90%，因此 MySQL 永遠不會使用太多 I/O 操作。

innodb_io_capacity = 21000

query_cache_limit 和query_cache_size 變數:\

MySQL 也支援記憶體中快取數據，但是我們不能依賴它來做快取系統，因為每次你的程式向資料庫表寫入資料的時候，MySQL 將會重建整個表的查詢快取。因此如果你的程式有很高的負載，MySQL 快取將完全無用，這種情況下，兩個變數最好設定為0，節省MySQL快取的開銷，相反，你可以使用類似 Redis 的東西來管理快取。

query_cache_limit = 0

query_cache_size = 0

慢查詢日誌:\

慢查詢日誌將會顯示你的哪些查詢超過你定義的閾值，而不需要猜測哪個查詢更慢。 \
首先，你必須在你的設定檔中啟用 slow_query_log 。在Linux伺服器中，開啟 「/etc/mysql/my.cnf」或是你係統上同等的檔案。 \
並新增:

slow_query_log = 1

long_query_time = 1

那麼，這兩個選項將啟用慢查詢日誌，並記錄任何需要超過一秒的查詢。如果你喜歡在一個表中而不是在一個文件中查看日誌，你可以添加：

log_output = 'TABLE'

然後你可以在“slow_log”表中找到你的日誌。你可以在那裡看到所有執行超過一秒的慢查詢的資訊。這些資訊包括查詢的確切執行時間和受影響的行數，以及哪個使用者執行的它。

查詢最佳化\

在你得到所有的慢查詢後，你需要一種方式來最佳化它們，使它們更快。因此，你可以在查詢語句前面加上「explain」關鍵字，從而獲得相關查詢的詳情信息，例如：explain select * from users where active=1;

「解釋」關鍵字可以幫助你定義查詢命中哪些索引以及為取得資料而查詢的行數。這些資訊可以告訴你是否需要建立更多索引或重組資料庫表。

非規範化與限制：\

非規範化是透過新增冗餘資料或將它們分組來提高讀取效能的過程。例如，如果你有一個「產品」表和一個「類別」表，並且每​​次查詢「產品」表時，你還需要取得每個產品的「類別名稱」。在這種情況下，你可以使用“join”來檢索“category_name”。但是，這意味著每次使用者開啟產品頁面時，都會執行一個複雜的連線查詢。因此，你可以考慮在「產品」表中加入「類別名稱」。儘管有冗餘數據，但讀取效能的提高還是值得的。

非規範化方法可能會導致「產品」表中的「類別名稱」過時。所以你需要定義一個「外鍵」約束，但是你需要知道一個「外鍵」會使寫入效能稍微變慢，因為 MySQL 需要在寫入資料之前檢查約束。所以做出最佳選擇始終是你的任務。

英文原文網址：https://codeburst.io/database-performance-optimization-8d8407808b5b

翻譯網址：https://learnku.com/mysql/t/71571

【相關推薦：mysql影片教學】

以上是MySQL怎麼優化效能？優化技巧分享的詳細內容。更多資訊請關注PHP中文網其他相關文章！