讓數(shù)據(jù)庫跑的更快的7個MySQL優(yōu)化建議!
性能是我們衡量應(yīng)用的一種方式,而應(yīng)用性能的一項指標(biāo)就是用戶體驗,也就是平時我們常說的:“用戶需要等待超過合理的時間,才能獲得他們想要的東西嗎?”
在不同的情況和場景下,該指標(biāo)會有所不同。比如說:對于移動購物應(yīng)用來說,其響應(yīng)時間不能超過幾秒鐘;而對于一個員工的人力資源頁面而言,其響應(yīng)時間則允許比幾秒鐘更長。
因此,不管是什么樣的標(biāo)準(zhǔn),維持應(yīng)用程序的良好性能都是至關(guān)重要的,否則就會引發(fā)用戶的抱怨(或更糟的是用戶轉(zhuǎn)而使用其他的應(yīng)用)。而數(shù)據(jù)庫性能就是影響應(yīng)用程序性能的因素之一。
可以說,應(yīng)用程序、網(wǎng)站和數(shù)據(jù)庫之間的交互會直接影響到應(yīng)用服務(wù)水平的確立。
這種交互的一個核心組成部分是:各種應(yīng)用程序如何去查詢數(shù)據(jù)庫,以及數(shù)據(jù)庫是如何響應(yīng)各種請求的。
不論是哪一種標(biāo)準(zhǔn),MySQL 都是時下最流行的數(shù)據(jù)庫管理系統(tǒng)之一。越來越多的企業(yè)已將 MySQL(和其他開源的數(shù)據(jù)庫)視為其生產(chǎn)環(huán)境中的數(shù)據(jù)庫解決方案。
MySQL 有許多配置方法可以確保您的數(shù)據(jù)庫能夠快速地響應(yīng)各種查詢,同時僅對應(yīng)用程序性能造成細(xì)微的下降。
以下就是能夠幫助您優(yōu)化 MySQL 數(shù)據(jù)庫性能的 7 點必備技巧:
- 學(xué)習(xí)如何使用EXPLAIN
- 創(chuàng)建正確的索引
- 拒絕默認(rèn)設(shè)置
- 將數(shù)據(jù)庫載入內(nèi)存中
- 使用SSD存儲
- 橫向擴展
- 追求可視性
學(xué)習(xí)如何使用 EXPLAIN
在您對數(shù)據(jù)庫做任何設(shè)計決策時,有兩個方面非常重要:
- 應(yīng)用實體之間如何被映射到各個數(shù)據(jù)表(數(shù)據(jù)庫模式架構(gòu))上。
- 應(yīng)用程序如何獲取(查詢)到它們所需格式類型的數(shù)據(jù)。
復(fù)雜的應(yīng)用程序必然有著復(fù)雜的模式架構(gòu)和查詢。如果您想讓自己的各種應(yīng)用具備所需的性能和擴展性,那就不能單純依靠直覺去理解各種查詢的執(zhí)行機制。
建議您認(rèn)真學(xué)習(xí)如何去使用 EXPLAIN 命令,而不是憑空猜想。該命令會向您展示查詢是如何被執(zhí)行的;并深入地演示有關(guān)性能的真實表現(xiàn)情況,以及查詢是如何伴隨著數(shù)據(jù)量的變化進行擴展的。
像許多 MySQL Workbench 之類的工具都可以將 EXPLAIN 的輸出可視化地展示給您,不過您仍然需要了解與它相關(guān)的基本知識。
EXPLAIN 命令的輸出有兩種不同的格式:老式的表格形式和較新的、能夠提供更為細(xì)節(jié)化的、結(jié)構(gòu)化的 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)”
- }
- }
- }
其中您需要重點查看的部分是:查詢成本。查詢成本是指基于查詢執(zhí)行的總體成本和許多不同的因素考慮,MySQL 判定一次查詢所付出的花銷。
一般簡單查詢的成本會小于 1000。介于 1000 到 100,000 的成本值被視為中等成本的查詢。
因此,如果您每秒只是運行上百個(并非幾萬個)此類查詢的話,一般速度應(yīng)該比較快。
查詢成本如果是超過 100,000 的話,那么開銷就比較大了。而通常當(dāng)您的系統(tǒng)只有單個用戶時,此類查詢?nèi)匀豢梢员谎杆俚貓?zhí)行。
當(dāng)然,您需要仔細(xì)考慮一下在交互式應(yīng)用程序中,使用此類查詢的頻率(尤其在用戶數(shù)量增長的時候)。
雖然這些只是大概的數(shù)字,但是它們卻能夠反映出總體的規(guī)律。實際情況下,您的系統(tǒng)在處理查詢請求負(fù)載時會表現(xiàn)得更好還是更糟,完全取決于自身的架構(gòu)與配置。
決定查詢成本的一個首要因素是:查詢是否正確地使用了各種索引。如果您沒有使用索引進行查詢,那么會被 EXPLAIN 命令所指出來,通常源于索引是如何在數(shù)據(jù)庫中被創(chuàng)建的,以及查詢本身是如何被設(shè)計的。
這也正是為什么 EXPLAIN 值得去好好學(xué)習(xí)和使用的原因。
創(chuàng)建正確的索引
索引是通過減少在數(shù)據(jù)庫里查詢時,必須掃描的數(shù)據(jù)量來提高查詢的自身效率。
在 MySQL 中,索引被用于加快對數(shù)據(jù)庫的訪問,并有助于遵循數(shù)據(jù)庫的各種約束(例如 UNIQUE 和 FOREIGN KEY)。
數(shù)據(jù)庫索引就像書的索引一樣,它們的位置信息被保存,并且包含有數(shù)據(jù)庫的主要信息。
它們是數(shù)據(jù)位置的一種參考方法或映射,因此索引并不會更改數(shù)據(jù)庫中的任何數(shù)據(jù)。它們只是指向數(shù)據(jù)存放的位置而已。
不過,索引并不總能匹配上任何的負(fù)載請求。在系統(tǒng)運行中,您應(yīng)當(dāng)不斷為查詢的上下文環(huán)境創(chuàng)建各種索引。
雖然有著良好索引的數(shù)據(jù)庫會運行更快速,但是如果出現(xiàn)單個索引的缺失,則會拖慢整個數(shù)據(jù)庫的效率。
因此,我們需要使用 EXPLAIN 來查找缺失的索引,并將其添加上去。
需要注意的是:不要添加您所不需要的索引,因為不必要的索引會反過來拖慢數(shù)據(jù)庫。
拒絕默認(rèn)設(shè)置
就像其他任何軟件那樣,MySQL 也能通過各種可配置的設(shè)置,來修改其行為并最終優(yōu)化其性能。
同時這些配置的設(shè)置經(jīng)常會被管理員所忽略,并一直保持著默認(rèn)值的狀態(tài)。
為了讓 MySQL 獲得最佳的性能,了解如何配置 MySQL,以及將它們設(shè)置為最適合您的數(shù)據(jù)庫環(huán)境的狀態(tài)是非常重要的。
在默認(rèn)情況下,MySQL 是針對小規(guī)模的發(fā)布、安裝進行調(diào)優(yōu)的,而并非真正的生產(chǎn)環(huán)境規(guī)模。
因此,通常您需要將 MySQL 配置為使用所有可用的內(nèi)存資源,并且能允許您的應(yīng)用程序所需的最大連接數(shù)。
這里有三個有關(guān) MySQL 性能優(yōu)化的設(shè)置,值得您去仔細(xì)地配置:
innodb_buffer_pool_size
數(shù)據(jù)和索引被用作緩存的緩沖池。當(dāng)您的數(shù)據(jù)庫服務(wù)器有著大量的系統(tǒng)內(nèi)存時,可以用到該設(shè)置。
如果您只運行 InnoDB 存儲引擎,那么您通常可以分配 80% 左右的內(nèi)存給該緩沖池。
而如果您要運行非常復(fù)雜的查詢或者您有大量的并發(fā)數(shù)據(jù)庫連接,亦或您有非常大的數(shù)據(jù)表的情況,那么就可能需要將此值下調(diào)一個等級,以便為其他的調(diào)用分配更多的內(nèi)存。
您在設(shè)置 InnoDB 緩沖池大小的時候,要確保其設(shè)置既不要過大,也不要頻繁引起交換(swapping),因為這些絕對會降低您的數(shù)據(jù)庫性能。有一個簡單的檢查方法就是在“Percona 監(jiān)控和管理”。
如圖所示,如果你看到有大于 1MB 每秒的持續(xù)交換活動的話,您就需要減少緩沖池的大小了,或者使用其他的內(nèi)存。
如果您一開始并沒有將 innodb_buffer_pool_size 的值設(shè)置正確,也不必?fù)?dān)心。
從 MySQL 5.7 開始,您可以動態(tài)地改變 InnoDB 緩沖池的大小,而不需要重新啟動數(shù)據(jù)庫服務(wù)器了。
innodb_log_file_size
這是指單個 InnoDB 日志文件的大小。默認(rèn)情況下,InnoDB 使用兩個值,這樣您就可以通過將其增加一倍,來讓 InnoDB 獲得循環(huán)的重做日志空間,以確保交易的持久性。這同時也優(yōu)化了對數(shù)據(jù)庫的寫入性能。
設(shè)置 innodb_log_file_size 的值是很值得推敲的:如果分配了較大的重做空間,那么對于寫入密集型的工作負(fù)載來說性能會越好。
但是如果您的系統(tǒng)遭受到斷電或其他問題導(dǎo)致崩潰的時候,那么其恢復(fù)時間則會越長。
您可能會問:怎么才能知道自己的 MySQL 性能是否受限于當(dāng)前的 InnoDB 日志文件大小呢?
您可以通過查看未實際使用的重做日志空間大小來判定。最簡單的方法就是查看“Percona 監(jiān)控和管理”的 InnoDB 指標(biāo)儀表板。
在下圖中,InnoDB 的日志文件不夠大,使用空間已經(jīng)屢屢接近于可用的重做日志空間了,如紅線所示:
因此,您的日志文件應(yīng)該至少比使用量大 20%,從而保持系統(tǒng)處于最佳的性能狀態(tài)。
max_connections
大型應(yīng)用程序通常需要比默認(rèn)數(shù)量多得多的連接。不同于其他的變量,如果您沒能將該值設(shè)置正確,您就會碰到性能方面的問題。
也就是說,如果連接的數(shù)量不足以滿足您的應(yīng)用需求,那么應(yīng)用程序?qū)⒏緹o法連接到數(shù)據(jù)庫,在用戶看來就像宕機了一樣。由此可見,將它設(shè)置正確是非常重要的。
對于在多臺服務(wù)器上運行著具有多個組件的復(fù)雜應(yīng)用來說,您想獲知到底需要多少個連接是非常困難的。
幸運的是,MySQL 能夠在峰值操作時輕易地獲悉所用到的連接數(shù)量。通常,您需要確保在應(yīng)用程序所使用到的最大連接數(shù)和可用的最大連接數(shù)之間至少有 30% 的差額。
查看這些數(shù)字的一個簡單方法是:在“Percona 監(jiān)控和管理”的系統(tǒng)概述界面中查看使用 MySQL 連接圖。
下圖顯示了一個健康的系統(tǒng),它有著足夠數(shù)量的可用額外連接。
還有一點需要記住:如果您的應(yīng)用程序所創(chuàng)建的連接數(shù)量過多,通常會導(dǎo)致數(shù)據(jù)庫運行緩慢。
在這種情況下,您應(yīng)該在數(shù)據(jù)庫性能上做文章,而不是簡單地允許建立更多的連接。更多的連接會使得潛在的性能問題更加惡化。
將數(shù)據(jù)庫載入內(nèi)存中
近年來,出現(xiàn)了固態(tài)硬盤(SSD)方向上的轉(zhuǎn)變。盡管固態(tài)硬盤比傳統(tǒng)機械旋臂硬盤快得多,但是它們?nèi)匀粩巢贿^將數(shù)據(jù)存在內(nèi)存里。
這種差別不僅來自于存儲性能本身,還來自于數(shù)據(jù)庫從磁盤或 SSD 里存取數(shù)據(jù)時所產(chǎn)生的額外工作。
隨著近年來硬件技術(shù)的改進,不管您是運行在云端,還是管理著自己的硬件,將數(shù)據(jù)庫載入內(nèi)存已經(jīng)變得可行。
更令人振奮的是:您并不需要將整個數(shù)據(jù)庫載入內(nèi)存以獲得其性能優(yōu)勢,您只需要將最頻繁訪問的數(shù)據(jù)集放入其中便可。
您可能已經(jīng)看過一些文章,有介紹將數(shù)據(jù)庫多少比例(如:10% 到 33%)載入到內(nèi)存里。
而事實上并不存在著“一刀切”的規(guī)律,數(shù)據(jù)的訪問量決定著載入內(nèi)存所獲得的最佳性能的提升程度。
您與其去尋找某個特定的“神奇”數(shù)字,不如去檢查數(shù)據(jù)庫達到穩(wěn)定運行狀態(tài)時的 I/O(通常是在它開始運行的幾個小時之后)。
請查看一下數(shù)據(jù)的讀取,因為如果您的數(shù)據(jù)庫已載入到內(nèi)存里的話,那么讀取會完全結(jié)束;而只要有內(nèi)存可用,寫入操作總是會發(fā)生的。
下圖是“Percona 監(jiān)控和管理”的 InnoDB 指標(biāo)儀表板中的 InnoDB I/O圖:
如上圖所示,那些峰值高達每秒 2,000 的 I/O 操作表明(至少是流量負(fù)載的一部分)它們與載入內(nèi)存中數(shù)據(jù)庫的數(shù)據(jù)集并不相配。
使用 SSD 存儲
無論您的數(shù)據(jù)庫是否已被載入內(nèi)存,您都需要使用快速存儲來處理寫入操作,并且避免在數(shù)據(jù)庫啟動后(重啟之后)出現(xiàn)性能問題。這里的快速存儲就是指固態(tài)硬盤。
一些所謂的“專家”仍在基于成本和可靠性的基礎(chǔ)上,主張使用機械旋臂硬盤。坦率地說,當(dāng)涉及到數(shù)據(jù)庫操作時,這些建議往往是過時的或是完全錯誤的。現(xiàn)如今,固態(tài)硬盤的性能已經(jīng)非常卓越、可靠且價格低廉了。
并非所有的固態(tài)硬盤都是同等生產(chǎn)的。對于數(shù)據(jù)庫服務(wù)器來說,您應(yīng)該選用那些專供服務(wù)器工作負(fù)載、且能精心呵護數(shù)據(jù)的 SSD。
例如:防止斷電損壞的,而避免使用那些專為臺式和筆記本電腦設(shè)計的商用固態(tài)硬盤。
通過 NVMe 或英特爾 Optane 技術(shù)來直接連接的 SSD 往往能夠提供最佳的性能。
即使遠(yuǎn)程連接到 SAN、NAS 或云端的塊設(shè)備上,固態(tài)硬盤也能比機械旋臂硬盤提供更為優(yōu)越的性能。
橫向擴展
即使是性能最高的服務(wù)器也有局限性。業(yè)界一般用兩種方法來進行擴展:縱向和橫向。
縱向擴展意味著購買更多的硬件。這樣做不但成本昂貴,而且硬件折舊速度快。
而橫向擴展,則在處理負(fù)載方面有如下幾點優(yōu)勢:
- 您可以從更小型、成本更低的系統(tǒng)中獲益。
- 橫向擴展使得系統(tǒng)的線性擴展更方便、更快捷。
- 由于數(shù)據(jù)庫會橫跨增長到多個物理機上,橫向擴展在保護數(shù)據(jù)庫的同時,消除了硬件單點故障。
盡管橫向擴展有著諸多優(yōu)勢,不過它還是具有一定的局限性。橫向擴展需要數(shù)據(jù)復(fù)制,例如基本的 MySQL Replication 或是用于數(shù)據(jù)同步的 Percona XtraDB 群集。
但是作為回報,您也會獲得更高的性能和可用性。如果您需要更高級的擴展性,那么請考慮使用 MySQL 分片(sharding)。
另外,您還需要確保連接到群集架構(gòu)的應(yīng)用程序可以找到它們所需的數(shù)據(jù)。這通常是通過諸如 ProxySQL 或 HAProxy 的一些代理服務(wù)器和負(fù)載平衡器來實現(xiàn)的。
當(dāng)然,過早地規(guī)劃橫向擴展,會增加分布式數(shù)據(jù)庫的復(fù)雜性。最近發(fā)布的 MySQL 8 候選版本已聲稱自己能夠在單一的系統(tǒng)上處理超過 200 萬個簡單查詢。
追求可視性
可視性是系統(tǒng)設(shè)計的最佳境界,MySQL 也不例外。
一旦完成了 MySQL 環(huán)境的搭建、運行并調(diào)優(yōu),您千萬不要認(rèn)為已經(jīng)萬事大吉了。
數(shù)據(jù)庫環(huán)境既會受到來自系統(tǒng)更改或流量負(fù)荷的影響,也會遇到例如流量高峰、應(yīng)用程序錯誤以及 MySQL 自身的各種問題。
為了快速、有效地解決各種問題,您需要建立和實施一些監(jiān)控機制,從而能獲悉數(shù)據(jù)庫環(huán)境的狀態(tài),并在出現(xiàn)錯誤時及時分析服務(wù)器上的數(shù)據(jù)。
因此理想情況就是在系統(tǒng)出現(xiàn)問題或是被用戶所察覺之前就做到防范于未然。
常用的監(jiān)測工具有:
- MySQL企業(yè)監(jiān)控器(Enterprise Monitor)。
- Monyog。
- 具有免費與開源版本的 Percona 監(jiān)控和管理(PMM)。
這些工具在監(jiān)控和故障排除方面提供了很好的操作可視性。
隨著越來越多的公司在大規(guī)模生產(chǎn)環(huán)境中使用開源的數(shù)據(jù)庫(特別是MySQL)來管理和服務(wù)他們的業(yè)務(wù)數(shù)據(jù),他們需要把工作重心放在保持?jǐn)?shù)據(jù)庫的調(diào)優(yōu)和運行效率上。
MySQL 的確是一款能夠提升您的應(yīng)用程序和網(wǎng)站性能的優(yōu)秀數(shù)據(jù)庫,當(dāng)然您需要通過對它進行調(diào)整,以滿足業(yè)務(wù)需求,監(jiān)測、發(fā)現(xiàn)并防止任何瓶頸和性能方面的問題。
責(zé)任編輯:售電衡衡
-
碳中和戰(zhàn)略|趙英民副部長致辭全文
2020-10-19碳中和,碳排放,趙英民 -
兩部門:推廣不停電作業(yè)技術(shù) 減少停電時間和停電次數(shù)
2020-09-28獲得電力,供電可靠性,供電企業(yè) -
國家發(fā)改委、國家能源局:推廣不停電作業(yè)技術(shù) 減少停電時間和停電次數(shù)
2020-09-28獲得電力,供電可靠性,供電企業(yè)
-
碳中和戰(zhàn)略|趙英民副部長致辭全文
2020-10-19碳中和,碳排放,趙英民 -
深度報告 | 基于分類監(jiān)管與當(dāng)量協(xié)同的碳市場框架設(shè)計方案
2020-07-21碳市場,碳排放,碳交易 -
碳市場讓重慶能源轉(zhuǎn)型與經(jīng)濟發(fā)展并進
2020-07-21碳市場,碳排放,重慶
-
兩部門:推廣不停電作業(yè)技術(shù) 減少停電時間和停電次數(shù)
2020-09-28獲得電力,供電可靠性,供電企業(yè) -
國家發(fā)改委、國家能源局:推廣不停電作業(yè)技術(shù) 減少停電時間和停電次數(shù)
2020-09-28獲得電力,供電可靠性,供電企業(yè) -
2020年二季度福建省統(tǒng)調(diào)燃煤電廠節(jié)能減排信息披露
2020-07-21火電環(huán)保,燃煤電廠,超低排放
-
四川“專線供電”身陷違法困境
2019-12-16專線供電 -
我國能源替代規(guī)范法律問題研究(上)
2019-10-31能源替代規(guī)范法律 -
區(qū)域鏈結(jié)構(gòu)對于數(shù)據(jù)中心有什么影響?這個影響是好是壞呢!