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