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