讓數據庫跑的更快的7個MySQL優化建議！
發布時間：2018-02-02 瀏覽：373打印字號：大中小
性能是我們衡量應用的一種方式，而應用性能的一項指標就是用戶體驗，也就是平時我們常說的：“用戶需要等待超過合理的時間，才能獲得他們想要的東西嗎？”
在不同的情況和場景下，該指標會有所不同。比如說：對于移動購物應用來說，其響應時間不能超過幾秒鐘；而對于一個員工的人力資源頁面而言，其響應時間則允許比幾秒鐘更長。
因此，不管是什么樣的標準，維持應用程序的良好性能都是至關重要的，否則就會引發用戶的抱怨（或更糟的是用戶轉而使用其他的應用）。而數據庫性能就是影響應用程序性能的因素之一。
可以說，應用程序、網站和數據庫之間的交互會直接影響到應用服務水平的確立。
這種交互的一個核心組成部分是：各種應用程序如何去查詢數據庫，以及數據庫是如何響應各種請求的。
不論是哪一種標準，MySQL 都是時下最流行的數據庫管理系統之一。越來越多的企業已將 MySQL（和其他開源的數據庫）視為其生產環境中的數據庫解決方案。
MySQL 有許多配置方法可以確保您的數據庫能夠快速地響應各種查詢，同時僅對應用程序性能造成細微的下降。
以下就是能夠幫助您優化 MySQL 數據庫性能的 7 點必備技巧：
學習如何使用EXPLAIN創建正確的索引
拒絕默認設置
將數據庫載入內存中使用SSD存儲
橫向擴展
追求可視性
學習如何使用 EXPLAIN
在您對數據庫做任何設計決策時，有兩個方面非常重要：
應用實體之間如何被映射到各個數據表（數據庫模式架構）上。
應用程序如何獲取（查詢）到它們所需格式類型的數據。
復雜的應用程序必然有著復雜的模式架構和查詢。如果您想讓自己的各種應用具備所需的性能和擴展性，那就不能單純依靠直覺去理解各種查詢的執行機制。
建議您認真學習如何去使用 EXPLAIN 命令，而不是憑空猜想。該命令會向您展示查詢是如何被執行的；并深入地演示有關性能的真實表現情況，以及查詢是如何伴隨著數據量的變化進行擴展的。
像許多 MySQL Workbench 之類的工具都可以將 EXPLAIN 的輸出可視化地展示給您，不過您仍然需要了解與它相關的基本知識。
EXPLAIN 命令的輸出有兩種不同的格式：老式的表格形式和較新的、能夠提供更為細節化的、結構化的 JSON 文檔。
如下所示：
mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “762.40”
},
“table”: {
“table_name”: “sbtest1”,
“access_type”: “range”,
“possible_keys”: [
“PRIMARY”
],
“key”: “PRIMARY”,
“used_key_parts”: [
“id”
],
“key_length”: “4”,
“rows_examined_per_scan”: 1874,
“rows_produced_per_join”: 1874,
“filtered”: “100.00”,
“cost_info”: {
“read_cost”: “387.60”,
“eval_cost”: “374.80”,
“prefix_cost”: “762.40”,
“data_read_per_join”: “351K”
},
“used_columns”: [
“id”,
“k”
],
“attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)”
}
}
}
其中您需要重點查看的部分是：查詢成本。查詢成本是指基于查詢執行的總體成本和許多不同的因素考慮，MySQL 判定一次查詢所付出的花銷。
一般簡單查詢的成本會小于 1000。介于 1000 到 100,000 的成本值被視為中等成本的查詢。
因此，如果您每秒只是運行上百個（并非幾萬個）此類查詢的話，一般速度應該比較快。
查詢成本如果是超過 100,000 的話，那么開銷就比較大了。而通常當您的系統只有單個用戶時，此類查詢仍然可以被迅速地執行。
當然，您需要仔細考慮一下在交互式應用程序中，使用此類查詢的頻率（尤其在用戶數量增長的時候）。
雖然這些只是大概的數字，但是它們卻能夠反映出總體的規律。實際情況下，您的系統在處理查詢請求負載時會表現得更好還是更糟，完全取決于自身的架構與配置。
決定查詢成本的一個首要因素是：查詢是否正確地使用了各種索引。如果您沒有使用索引進行查詢，那么會被 EXPLAIN 命令所指出來，通常源于索引是如何在數據庫中被創建的，以及查詢本身是如何被設計的。
這也正是為什么 EXPLAIN 值得去好好學習和使用的原因。
創建正確的索引
索引是通過減少在數據庫里查詢時，必須掃描的數據量來提高查詢的自身效率。
在 MySQL 中，索引被用于加快對數據庫的訪問，并有助于遵循數據庫的各種約束（例如 UNIQUE 和 FOREIGN KEY）。
數據庫索引就像書的索引一樣，它們的位置信息被保存，并且包含有數據庫的主要信息。
它們是數據位置的一種參考方法或映射，因此索引并不會更改數據庫中的任何數據。它們只是指向數據存放的位置而已。
不過，索引并不總能匹配上任何的負載請求。在系統運行中，您應當不斷為查詢的上下文環境創建各種索引。
雖然有著良好索引的數據庫會運行更快速，但是如果出現單個索引的缺失，則會拖慢整個數據庫的效率。
因此，我們需要使用 EXPLAIN 來查找缺失的索引，并將其添加上去。
需要注意的是：不要添加您所不需要的索引，因為不必要的索引會反過來拖慢數據庫。
拒絕默認設置
就像其他任何軟件那樣，MySQL 也能通過各種可配置的設置，來修改其行為并最終優化其性能。
同時這些配置的設置經常會被管理員所忽略，并一直保持著默認值的狀態。
為了讓 MySQL 獲得優質的性能，了解如何配置 MySQL，以及將它們設置為最適合您的數據庫環境的狀態是非常重要的。
在默認情況下，MySQL 是針對小規模的發布、安裝進行調優的，而并非真正的生產環境規模。
因此，通常您需要將 MySQL 配置為使用所有可用的內存資源，并且能允許您的應用程序所需的連接數。
這里有三個有關 MySQL 性能優化的設置，值得您去仔細地配置：
innodb_buffer_pool_size
數據和索引被用作緩存的緩沖池。當您的數據庫服務器有著大量的系統內存時，可以用到該設置。
如果您只運行 InnoDB 存儲引擎，那么您通常可以分配 80％ 左右的內存給該緩沖池。
而如果您要運行非常復雜的查詢或者您有大量的并發數據庫連接，亦或您有非常大的數據表的情況，那么就可能需要將此值下調一個等級，以便為其他的調用分配更多的內存。
您在設置 InnoDB 緩沖池大小的時候，要確保其設置既不要過大，也不要頻繁引起交換（swapping），因為這些絕對會降低您的數據庫性能。有一個簡單的檢查方法就是在“Percona 監控和管理”。
如圖所示，如果你看到有大于 1MB 每秒的持續交換活動的話，您就需要減少緩沖池的大小了，或者使用其他的內存。
如果您一開始并沒有將 innodb_buffer_pool_size 的值設置正確，也不必擔心。
從 MySQL 5.7 開始，您可以動態地改變 InnoDB 緩沖池的大小，而不需要重新啟動數據庫服務器了。
innodb_log_file_size
這是指單個 InnoDB 日志文件的大小。默認情況下，InnoDB 使用兩個值，這樣您就可以通過將其增加一倍，來讓 InnoDB 獲得循環的重做日志空間，以確保交易的持久性。這同時也優化了對數據庫的寫入性能。
設置 innodb_log_file_size 的值是很值得推敲的：如果分配了較大的重做空間，那么對于寫入密集型的工作負載來說性能會越好。
但是如果您的系統遭受到斷電或其他問題導致崩潰的時候，那么其恢復時間則會越長。
您可能會問：怎么才能知道自己的 MySQL 性能是否受限于當前的 InnoDB 日志文件大小呢？
您可以通過查看未實際使用的重做日志空間大小來判定。最簡單的方法就是查看“Percona 監控和管理”的 InnoDB 指標儀表板。
在下圖中，InnoDB 的日志文件不夠大，使用空間已經屢屢接近于可用的重做日志空間了，如紅線所示：
因此，您的日志文件應該至少比使用量大 20％，從而保持系統處于優質的性能狀態。
max_connections
大型應用程序通常需要比默認數量多得多的連接。不同于其他的變量，如果您沒能將該值設置正確，您就會碰到性能方面的問題。
也就是說，如果連接的數量不足以滿足您的應用需求，那么應用程序將根本無法連接到數據庫，在用戶看來就像宕機了一樣。由此可見，將它設置正確是非常重要的。
對于在多臺服務器上運行著具有多個組件的復雜應用來說，您想獲知到底需要多少個連接是非常困難的。
幸運的是，MySQL 能夠在峰值操作時輕易地獲悉所用到的連接數量。通常，您需要確保在應用程序所使用到的連接數和可用的連接數之間至少有 30％ 的差額。
查看這些數字的一個簡單方法是：在“Percona 監控和管理”的系統概述界面中查看使用 MySQL 連接圖。
下圖顯示了一個健康的系統，它有著足夠數量的可用額外連接。
在這種情況下，您應該在數據庫性能上做文章，而不是簡單地允許建立更多的連接。更多的連接會使得潛在的性能問題更加惡化。
將數據庫載入內存中
近年來，出現了固態硬盤（SSD）方向上的轉變。盡管固態硬盤比傳統機械旋臂硬盤快得多，但是它們仍然敵不過將數據存在內存里。
這種差別不僅來自于存儲性能本身，還來自于數據庫從磁盤或 SSD 里存取數據時所產生的額外工作。
隨著近年來硬件技術的改進，不管您是運行在云端，還是管理著自己的硬件，將數據庫載入內存已經變得可行。
更令人振奮的是：您并不需要將整個數據庫載入內存以獲得其性能優勢，您只需要將最頻繁訪問的數據集放入其中便可。
您可能已經看過一些文章，有介紹將數據庫多少比例（如：10％ 到 33％）載入到內存里。
而事實上并不存在著“一刀切”的規律，數據的訪問量決定著載入內存所獲得的優質性能的提升程度。
您與其去尋找某個特定的“神奇”數字，不如去檢查數據庫達到穩定運行狀態時的 I/O（通常是在它開始運行的幾個小時之后）。
請查看一下數據的讀取，因為如果您的數據庫已載入到內存里的話，那么讀取會完全結束；而只要有內存可用，寫入操作總是會發生的。
下圖是“Percona 監控和管理”的 InnoDB 指標儀表板中的 InnoDB I/O圖：
如上圖所示，那些峰值高達每秒 2,000 的 I/O 操作表明（至少是流量負載的一部分）它們與載入內存中數據庫的數據集并不相配。
使用 SSD 存儲
無論您的數據庫是否已被載入內存，您都需要使用快速存儲來處理寫入操作，并且避免在數據庫啟動后（重啟之后）出現性能問題。這里的快速存儲就是指固態硬盤。
一些所謂的“專家”仍在基于成本和可靠性的基礎上，主張使用機械旋臂硬盤。坦率地說，當涉及到數據庫操作時，這些建議往往是過時的或是完全錯誤的。現如今，固態硬盤的性能已經非常卓越、可靠且價格低廉了。
并非所有的固態硬盤都是同等生產的。對于數據庫服務器來說，您應該選用那些專供服務器工作負載、且能精心呵護數據的 SSD。
例如：防止斷電損壞的，而避免使用那些專為臺式和筆記本電腦設計的商用固態硬盤。
通過 NVMe 或英特爾 Optane 技術來直接連接的 SSD 往往能夠提供優質的性能。
即使遠程連接到 SAN、NAS 或云端的塊設備上，固態硬盤也能比機械旋臂硬盤提供更為優越的性能。
橫向擴展
即使是性能最高的服務器也有局限性。業界一般用兩種方法來進行擴展：縱向和橫向。
縱向擴展意味著購買更多的硬件。這樣做不但成本昂貴，而且硬件折舊速度快。
而橫向擴展，則在處理負載方面有如下幾點優勢：
您可以從更小型、成本更低的系統中獲益。
橫向擴展使得系統的線性擴展更方便、更快捷。
由于數據庫會橫跨增長到多個物理機上，橫向擴展在保護數據庫的同時，消除了硬件單點故障。
盡管橫向擴展有著諸多優勢，不過它還是具有一定的局限性。橫向擴展需要數據復制，例如基本的 MySQL Replication 或是用于數據同步的 Percona XtraDB 群集。
但是作為回報，您也會獲得更高的性能和可用性。如果您需要更高級的擴展性，那么請考慮使用 MySQL 分片（sharding）。
另外，您還需要確保連接到群集架構的應用程序可以找到它們所需的數據。這通常是通過諸如 ProxySQL 或 HAProxy 的一些代理服務器和負載平衡器來實現的。
當然，過早地規劃橫向擴展，會增加分布式數據庫的復雜性。最近發布的 MySQL 8 候選版本已聲稱自己能夠在單一的系統上處理超過 200 萬個簡單查詢。
追求可視性
可視性是系統設計的優質境界，MySQL 也不例外。
一旦完成了 MySQL 環境的搭建、運行并調優，您千萬不要認為已經萬事大吉了。
數據庫環境既會受到來自系統更改或流量負荷的影響，也會遇到例如流量高峰、應用程序錯誤以及 MySQL 自身的各種問題。
為了快速、有效地解決各種問題，您需要建立和實施一些監控機制，從而能獲悉數據庫環境的狀態，并在出現錯誤時及時分析服務器上的數據。
因此理想情況就是在系統出現問題或是被用戶所察覺之前就做到防范于未然。
常用的監測工具有：
MySQL企業監控器（Enterprise Monitor）。
Monyog。
具有免費與開源版本的 Percona 監控和管理（PMM）。
這些工具在監控和故障排除方面提供了很好的操作可視性。
隨著越來越多的公司在大規模生產環境中使用開源的數據庫（特別是MySQL）來管理和服務他們的業務數據，他們需要把工作重心放在保持數據庫的調優和運行效率上。
MySQL 的確是一款能夠提升您的應用程序和網站性能的優秀數據庫，當然您需要通過對它進行調整，以滿足業務需求，監測、發現并防止任何瓶頸和性能方面的問題。
原文來自微信公眾號：51CTO技術棧
