MySQL 是一種流行的開(kāi)源數據庫應用程序,它以一種有意義且易于訪(fǎng)問(wèn)的方式存儲和構造數據。對于大型應用程序,龐大的數據量可能會(huì )導致性能問(wèn)題。
本指南提供了一些關(guān)于如何提高 MySQL 數據庫性能的調優(yōu)技巧。
先決條件
- 安裝并運行 MySQL 的 Linux 系統,Centos或Ubuntu
- 現有數據庫
- 操作系統和數據庫的管理員憑據
系統 MySQL 性能調優(yōu)
在系統級別,您將調整硬件和軟件選項以提高 MySQL 性能。
1.平衡四大硬件資源
貯存
花點(diǎn)時(shí)間評估您的存儲空間。如果您使用的是傳統硬盤(pán)驅動(dòng)器 (HDD),則可以升級到固廢驅動(dòng)器(SSD)以提高性能。
使用sysstat包中的iotop或sar之類(lèi)的工具來(lái)監控磁盤(pán)輸入/輸出速率。如果磁盤(pán)使用率遠高于其他資源的使用率,請考慮添加更多存儲或升級到更快的存儲。
處理器
處理器通常被認為是衡量系統速度的指標。使用Linuxtop命令了解資源的使用情況。注意 MySQL 進(jìn)程及其所需的處理器使用百分比。
處理器的升級成本更高,但如果您的 CPU 是瓶頸,則可能需要升級。
記憶
內存表示您的MySQL數據庫存儲服務(wù)器中的 RAM 總量。您可以調整內存緩存(稍后會(huì )詳細介紹)以提高性能。
如果您沒(méi)有足夠的內存,或者現有內存沒(méi)有優(yōu)化,您最終可能會(huì )損害您的性能而不是提高它。
像其他瓶頸一樣,如果您的服務(wù)器經(jīng)常耗盡內存,您可以通過(guò)添加更多來(lái)升級。如果您的內存不足,
您的服務(wù)器將緩存數據存儲(如硬盤(pán))以充當內存。數據庫緩存會(huì )降低您的性能。
網(wǎng)絡(luò )
監控網(wǎng)絡(luò )流量以確保您有足夠的基礎設施來(lái)管理負載非常重要。
網(wǎng)絡(luò )過(guò)載會(huì )導致延遲、丟包甚至服務(wù)器中斷。確保您有足夠的網(wǎng)絡(luò )寬帶來(lái)容納正常水平的數據庫流量。
2. 使用 InnoDB,而不是 MyISAM
MyISAM是一種較舊的數據庫樣式,用于某些 MySQL 數據庫。這是一種效率較低的數據庫設計。
較新的InnoDB支持更高級的功能并具有內置的優(yōu)化機制。
InnoDB 使用聚集索引并將數據保存在頁(yè)面中,這些頁(yè)面存儲在連續的物理塊中。如果某個(gè)頁(yè)面的值太大,
InnoDB 會(huì )將其移動(dòng)到另一個(gè)位置,然后索引該值。此功能有助于將相關(guān)數據保存在存儲設備上的同一位置,
這意味著(zhù)物理硬盤(pán)驅動(dòng)器訪(fǎng)問(wèn)數據所需的時(shí)間更少。
3.使用最新版本的MySQL
對于舊的和遺留的數據庫,使用最新版本并不總是可行的。但只要有可能,您應該檢查正在使用的MySQL版本并升級到最新版本。
正在進(jìn)行的開(kāi)發(fā)的一部分包括性能增強。一些常見(jiàn)的性能調整可能會(huì )被較新版本的MySQL 淘汰。一般來(lái)說(shuō),
使用原生 MySQL 性能增強總是比腳本和配置文件更好。
軟件 MySQL 性能調優(yōu)
SQL 性能調優(yōu)是在關(guān)系數據庫上******化查詢(xún)速度的過(guò)程。該任務(wù)通常涉及多種工具和技術(shù)。
這些方法包括:
- 調整 MySQL 配置文件。
- 編寫(xiě)更高效的數據庫查詢(xún)。
- 構建數據庫以更有效地檢索數據。
注意:調整配置設置時(shí),最好進(jìn)行小的增量調整。重大調整可能會(huì )使另一個(gè)值負擔過(guò)重并降低性能。此外,
建議您一次進(jìn)行一項更改,然后進(jìn)行測試。當您一次只更改一個(gè)變量時(shí),更容易跟蹤錯誤或錯誤配置。
4.考慮使用自動(dòng)性能改進(jìn)工具
與大多數軟件一樣,并非所有工具都適用于所有版本的 MySQL。
我們將檢查三個(gè)實(shí)用程序來(lái)評估您的 MySQL 數據庫并推薦更改以提高性能。
第一個(gè)是tuning-primer。這個(gè)工具有點(diǎn)老,專(zhuān)為 MySQL 5.5 - 5.7 設計。
它可以分析您的數據庫并建議設置以提高性能。
例如,如果感覺(jué)您的系統無(wú)法足夠快地處理查詢(xún)以保持緩存清晰,它可能會(huì )建議您提高query_cache_size參數。
對大多數現代 SQL 數據庫有用的第二個(gè)調優(yōu)工具是MySQLTuner。該腳本 ( mysqltuner.pl )是用 Perl 編寫(xiě)的。
與tuning-primer 一樣,它會(huì )分析您的數據庫配置,尋找瓶頸和低效率。輸出顯示指標和建議:
在輸出的頂部,您可以看到 MySQLTuner 工具和您的數據庫的版本。
該腳本適用于 MySQL 8.x。日志文件建議是列表中的第一個(gè),但如果您滾動(dòng)到底部,您可以看到提高 MySQL 性能的一般建議。
您可能已經(jīng)擁有的第三個(gè)實(shí)用程序是phpMyAdmin Advisor。與其他兩個(gè)實(shí)用程序一樣,它會(huì )評估您的數據庫并建議調整。
如果您已經(jīng)在使用 phpMyAdmin,Advisor 是您可以在 GUI 中使用的有用工具。
注意:查看我們的頂級SQL查詢(xún)優(yōu)化工具列表,并使用我們對每個(gè)工具的深??入分析來(lái)找到最適合您任務(wù)的工具。
5.優(yōu)化查詢(xún)
查詢(xún)是一個(gè)編碼請求,用于在數據庫中搜索與某個(gè)值匹配的數據。有一些查詢(xún)運算符,就其本質(zhì)而言,需要很長(cháng)時(shí)間才能運行。
SQL 性能調優(yōu)技術(shù)有助于優(yōu)化查詢(xún)以獲得更好的運行時(shí)間。
檢測執行時(shí)間很短的查詢(xún)是性能調優(yōu)的主要任務(wù)之一。通常在大型數據集上實(shí)現的查詢(xún)速度很慢并且占用數據庫。
因此,這些表不可用于任何其他任務(wù)。
注意:考慮研究數據倉庫架構,它將生產(chǎn)數據庫與分析數據庫分開(kāi)。
例如,OLTP數據庫需要快速事務(wù)和有效的查詢(xún)處理。運行效率低下的查詢(xún)會(huì )阻止數據庫的使用并停止信息更新。
如果您的環(huán)境依賴(lài)于觸發(fā)器等自動(dòng)查詢(xún),它們可能會(huì )影響性能。檢查并終止可能及時(shí)堆積的MySQL進(jìn)程
6. 在適當的地方使用索引
許多數據庫查詢(xún)使用與此類(lèi)似的結構:
SELECT… WHERE
這些查詢(xún)涉及評估、過(guò)濾和檢索結果。您可以通過(guò)為相關(guān)表添加一小組索引來(lái)重組它們。查詢(xún)可以直接指向索引,加快查詢(xún)速度。
7. 謂詞中的函數
避免在查詢(xún)的謂詞中使用函數。例如:
SELECT* FROM MYTABLE WHERE UPPER(COL1)='123'Copy
該UPPER符號創(chuàng )建一個(gè)函數,該函數必須在操作期間SELECT運行。這會(huì )使查詢(xún)的工作加倍,如果可能,您應該避免它。
8. 避免在謂詞中使用 % 通配符
在搜索文本數據時(shí),通配符有助于進(jìn)行更廣泛的搜索。例如,要選擇所有以ch開(kāi)頭的名稱(chēng),請在 name 列上創(chuàng )建索引并運行:
SELECT* FROM person WHERE name LIKE "ch%"
查詢(xún)掃描索引,使查詢(xún)成本低:
但是,在開(kāi)頭使用通配符搜索名稱(chēng)會(huì )顯著(zhù)增加查詢(xún)成本,因為索引掃描不適用于字符串的結尾:
搜索開(kāi)頭的通配符不應用索引。相反,全表掃描單獨搜索每一行,增加了該過(guò)程中的查詢(xún)成本。在示例查詢(xún)中,
在末尾使用通配符有助于降低查詢(xún)成本,因為要經(jīng)過(guò)更少的表行。
注意:查看我們的MySQL命令備忘單,其中包含索引命令。
搜索字符串結尾的一種方法是反轉字符串,索引反轉的字符串并查看起始字符。
現在將通配符放在末尾會(huì )搜索反轉字符串的開(kāi)頭,從而提高搜索效率。
9.在SELECT函數中指定列
分析和探索性查詢(xún)的常用表達式是SELECT *. 選擇超出您的需要會(huì )導致不必要的性能損失和冗余。
如果您指定您需要的列,您的查詢(xún)將不需要掃描不相關(guān)的列。
如果需要所有列,則沒(méi)有其他方法可以解決。但是,大多數業(yè)務(wù)需求不需要數據集中所有可用的列??紤]改為選擇特定列。
總而言之,避免使用:
SELECT* FROM table
相反,請嘗試:
SELECTcolumn1, column2 FROM table
10. 恰當地使用 ORDER BY
該ORDER BY表達式按指定列對結果進(jìn)行排序。它可用于一次按兩列排序。這些應該以相同的順序排序,升序或降序。
如果您嘗試以不同的順序對不同的列進(jìn)行排序,則會(huì )降低性能。您可以將其與索引結合起來(lái)以加快排序。
11. GROUP BY 代替 SELECT DISTINCT
嘗試刪除重復值時(shí),SELECT DISTINCT 查詢(xún)會(huì )派上用場(chǎng)。但是,該語(yǔ)句需要大量的處理能力。
只要有可能,請避免使用SELECT DISTINCT,因為它效率非常低,有時(shí)會(huì )令人困惑。例如,如果一個(gè)表列出了具有以下結構的客戶(hù)信息:
ID | 姓名 | 姓 | 地址 | 城市 | 狀態(tài) | 壓縮 |
0 | 約翰 | 史密斯 | 花街652號 | 洛杉磯 | 加州 | 90017 |
1 | 約翰 | 史密斯 | 1215海洋大道 | 洛杉磯 | 加州 | 90802 |
2 | 瑪莎 | 馬修斯 | 皮克大道 3104 號 | 洛杉磯 | 加州 | 90019 |
3 | 瑪莎 | 瓊斯 | 威尼斯大道 2712 號 | 洛杉磯 | 加州 | 90019 |
運行以下查詢(xún)會(huì )返回四個(gè)結果:
SELECTDISTINCT name, address FROM person
該聲明似乎應該返回一個(gè)不同名稱(chēng)的列表及其地址。相反,查詢(xún)同時(shí)查看名稱(chēng)和地址列。雖然有兩對同名的客戶(hù),但他們的地址不同。
要過(guò)濾掉重復的名稱(chēng)并返回地址,請嘗試使用以下GROUPBY語(yǔ)句:
SELECTname, address FROM person GROUP BY name
結果返回第一個(gè)不同的名稱(chēng)以及地址,使語(yǔ)句不那么模棱兩可。要按唯一地址分組,
GROUPBY參數只需更改為地址并DISTINCT更快地返回與語(yǔ)句相同的結果。
總而言之,避免使用:
SELECTDISTINCT column1, column2 FROM table
相反,請嘗試使用:
SELECTcolumn1, column2 FROM table GROUP BY column1
12. JOIN、WHERE、UNION、DISTINCT
盡可能?chē)L試使用內部聯(lián)接。外連接查看指定列之外的附加數據。如果您需要這些數據,那很好,但是包含不需要的數據會(huì )浪費性能。
使用INNER JOIN是連接表的標準方法。大多數數據庫引擎也接受使用WHERE。例如,以下兩個(gè)查詢(xún)輸出相同的結果:
SELECT* FROM table1 INNER JOIN table2 ON table1.id = table2.id
和....相比:
SELECT* FROM table1, table2 WHERE table1.id = table2.id
理論上,它們也具有相同的運行時(shí)間。
是使用還是查詢(xún)的選擇取決于數據庫引擎。雖然大多數引擎對這兩種方法具有相同的運行時(shí),但在某些數據庫系統中,
一種運行速度比另一種快。JOINWHERE
注意:了解有關(guān)MySQL JOINS以及如何使用它們的更多信息。
和命令有時(shí)包含在查詢(xún)中。與外連接一樣,如果需要,可以使用這些表達式。但是,它們增加了對數據庫的額外排序和讀取。
如果你不需要它們,最好找到更有效的表達方式。UNIONDISTINCT
13. 使用 EXPLAIN 函數
現代 MySQL 數據庫包含一個(gè)EXPLAIN函數。
將表達式附加EXPLAIN到查詢(xún)的開(kāi)頭將讀取和評估查詢(xún)。如果有低效的表達方式或令人困惑的結構,EXPLAIN可以幫助您找到它們。
然后,您可以調整查詢(xún)的措辭以避免無(wú)意的表掃描或其他性能損失。
14. MySQL 服務(wù)器配置
此配置涉及更改您的/etc/mysql/my.cnf文件。謹慎行事,一次進(jìn)行細微的更改。
query_cache_size– 指定等待運行的 MySQL 查詢(xún)的緩存大小。建議從 10MB 左右的小值開(kāi)始,然后增加到不超過(guò) 100-200MB。
如果緩存查詢(xún)過(guò)多,您可能會(huì )遇到“等待緩存鎖定”的級聯(lián)查詢(xún)。如果您的查詢(xún)不斷備份,
則更好的過(guò)程是使用EXPLAIN評估每個(gè)查詢(xún)并找到提高它們效率的方法。
max_connection– 指允許進(jìn)入數據庫的連接數。如果您收到引用“連接太多”的錯誤,則增加此值可能會(huì )有所幫助。
innodb_buffer_pool_size – 此設置將系統內存分配為數據庫的數據緩存。如果您有大量數據,請增加此值。
記下運行其他系統資源所需的 RAM。
innodb_io_capacity – 此變量設置存儲設備的輸入/輸出速率。這與存儲驅動(dòng)器的類(lèi)型和速度直接相關(guān)。
5400 rpm HDD 的容量將比高端SSD或Intel Optane低得多。您可以調整此值以更好地匹配您的硬件。
結論
您現在應該知道如何提高 MySQL 性能和調整數據庫。
尋找瓶頸(硬件和軟件)、工作量超出需要的查詢(xún),并考慮使用自動(dòng)化工具和EXPLAIN功能來(lái)評估您的數據庫。
優(yōu)化 MySQL 表有助于在專(zhuān)用存儲服務(wù)器中重新排序信息,以提高數據輸入和輸出速度。了解更多相關(guān)知識,聯(lián)系曉林電腦服務(wù)。