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ù)。
