怎么理解MySQL性能調優

本篇內容介紹了“怎么理解MySQL性能調優”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

MySQL性能調優概述

影響MySQL性能的因素主要有環境問題(CPU、磁盤I/O、網絡性能、操作系統爭用)和 MySQL 配置（數據庫設計<索引、數據類型、標準化>、應用程序性能<特定請求、短時事務>、 配置變量<緩沖區、高速緩存、InnoDB 設置>）

MySQL 的性能受主機的性能特征影響。各種因素都會影響主機性能：CPU 速度和數量、磁盤吞吐量和訪問時間、網絡吞吐量以及操作系統上的競爭服務都對MySQL 實例的性能有一定的影響。

數據庫內容及其配置也會影響MySQL 性能。

— 包含頻繁小更新的數據庫將因為精心設計和標準化而受益。

— 當您使用最小的適用數據類型來存儲數據時，數據庫吞吐量將提高。

— 僅請求表數據的一個子集的查詢將從精心設計的索引中獲益。

— 僅請求特定行和列的應用程序將減少冗余請求所產生的開銷。

— 事務越短，越不會導致其他事務的鎖定和延遲。

— 調節得當的服務器變量可優化針對特定工作負荷和數據集的MySQL 緩沖區、高速緩存和其他資源的分配。

1.1.        性能監控

要調節服務器的性能，必須了解其性能特征。為此，可以對整體性能進行基準測試，MySQL 安裝提供了以下基準測試工具：

l  mysqlslap 是標準MySQL 分發的一部分。這是一個診斷程序，它模擬MySQL 服務器實例上的客戶機負載，并顯示每個階段的計時信息。

l  sql-bench 是MySQL 源代碼分發的一部分，它是一系列Perl 腳本，用于執行多個語句和收集狀態計時數據。

以下是使用mysqlslap 在SQL 腳本中設置模式并運行其他腳本中的查詢的示例：

shell>mysqlslap --iterations=5000 --concurrency=50 --query=workload.sql --create=schema.sql --delimiter=";"

此外，MySQL還提供了第三方基準測試套件。

也可以使用慢速查詢日志、一般查詢日志，EXPLAIN 、PROCEDURE ANALYSE逐個分析事件；慢速查詢日志用于記錄超過long_query_time 和min_examined_row_limit 變量所設置的限制的語句。使用mysqldumpslow 可查看慢速查詢日志的內容。一般查詢日志用于記錄MySQL 所接收的所有客戶機連接和請求。使用該日志可記錄一段時間內（例如，生成供mysqlslap 或其他基準測試工具使用的工作負荷時）所接收的所有SQL語句。

使用SHOW STATUS和 mysqladmin extended-status 查看數據庫狀態，同時可以使用PERFORMANCE_SCHEMA 按組分析事件。

1.2.        性能模式

“性能模式”是在較低級別監控MySQL 服務器執行情況的功能。該功能是使用PERFORMANCE_SCHEMA 存儲引擎和performance_schema 數據庫實現的。從Oracle 下載的所有二進制版本的MySQL 中都提供了性能模式。默認情況下，將啟用性能模式，并在服務器啟動時使用performance_schema 變量對其進行控制。使用以下語句確認已啟用性能模式：

mysql> SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| performance_schema | ON |

+--------------------+-------+

性能模式下允許您監控并檢查MySQL 服務器中的被檢測代碼的性能特征。開發者將檢測函數和其他編碼事件以收集計時信息；公開的性能數據對MySQL 代碼庫的貢獻者、插件開發者很有幫助，同時對識別低級別的性能瓶頸，如日志文件I/O 等待或緩沖池互斥等任務有極大好處。性能模式所公開的信息可用于識別低級別瓶頸。該信息中大部分都是低級別的，可幫助MySQL 服務器產品系列的開發者調試性能問題，或者幫助系統架構師和性能顧問調節InnoDB 數據和日志文件存儲硬件。

l  檢測（INSTRUMENT）、實例（INSTANCE）、事件（EVENT）和使用者（CONSUMER）

性能模式數據庫包含配置和事件信息：

— 檢測"是服務器代碼中引發要監控的事件的點，在setup_instruments表中進行配置；

— 每個被檢測的對象都是該檢測的一個“實例”，記錄在一系列實例表中。

— 當線程執行檢測實例中的代碼時，MySQL 將識別所發生的“事件”，將其記錄在事件和匯總表中。

— 每個“使用者”都是性能模式中表的名稱，用于記錄和查詢事件以及事件的摘要，在SETUP_CONSUMERS 表中進行配置。

性能模式中的檢測是服務器源代碼中的點，MySQL 從該點引發事件。檢測具有分層命名約定。例如，以下是包含性能模式中數百個檢測中的一部分的簡短列表：

stage/sql/statistics

statement/com/Binlog Dump

wait/io/file/innodb/innodb_data_file

wait/io/file/sql/binlog

wait/io/socket/sql/server_unix_socket

每個檢測由其類型、所屬的模塊、該特定檢測的變量或類組成。通過查詢performance_schema.setup_instruments 表可查看所有可用的檢測。

性能模式將記錄實例表中的每個檢測實例。例如，以下查詢顯示檢測wait/io/file/sql/FRM 記錄文件實例/var/lib/mysql/mem/tags.frm 上的事件。

mysql> SELECT file_name, event_name FROM file_instances LIMIT 1\G

*************************** 1. row ***************************

FILE_NAME: /var/lib/mysql/mem/tags.frm

EVENT_NAME: wait/io/file/sql/FRM

以下輸出顯示了setup_consumers 表的內容：

mysql> SELECT * FROM setup_consumers;

+--------------------------------+---------+

| NAME | ENABLED |

+--------------------------------+---------+

| events_stages_current | NO |

| events_stages_history | YES |

| events_stages_history_long | NO |

| events_statements_current | YES |

| events_statements_history | NO |

| events_statements_history_long | NO |

| events_waits_current | YES |

| events_waits_history | YES |

| events_waits_history_long | NO |

| global_instrumentation | YES |

| thread_instrumentation | YES |

| statements_digest | YES |

+--------------------------------+---------+

12 rows in set (0.00 sec)

每個使用者的NAME 是性能模式中用于查詢事件和摘要的表的名稱。被禁用的使用者不記錄信息，從而節省了系統資源。

當MySQL 識別了發生在檢測實例中的事件后，會將其記錄在事件表中。

— 主事件表為events_waits_current，該表中存儲了每個線程最近的事件。

— events_waits_history 存儲每個線程的最近10 個事件。

— events_waits_history_long 共存儲10,000 個最近事件。

events_waits_* 表都使用相同的模式。有關該模式的結構信息，請訪問

http://dev.mysql.com/doc/refman/5.6/en/events-waits-current-table.html

當使用性能模式識別瓶頸或其他問題時，請執行以下操作：

1. 確保已針對適用于您所遇到的問題類型的一系列檢測和使用者啟用了性能模式。例如，如果您確定問題出在I/O 限制上，請使用wait/io/file/* 檢測；如果不確定根本原因，請使用更廣范圍的檢測。

2. 運行用于產生該問題的測試用例。

3. 查詢events_waits_* 表等使用者，尤其是使用適用的WHERE 子句過濾器查詢events_waits_history_long，以便進一步縮小問題原因的范圍。

4. 禁用那些用于評估已排除的問題的檢測。

5. 重試該測試用例。

1.3.        一般數據庫優化

1)         標準化

對數據進行標準化可以消除冗余數據，提高事務性工作負荷的性能，提供對數據的靈活訪問，最大限度地減少數據不一致情況。

標準化是移除數據庫中的冗余和不當依賴關系（以避免將相同的數據存儲在多個地方以及出現異常的風險）的行為。標準化通常會產生以下結果：許多表的列變少，整體存儲要求降低，I/O 需求降低以及單次插入、更新和刪除操作加快。這提高了頻繁執行小更新的事務性工作負荷的性能，但會使檢索大量數據的查詢變得復雜。

2)         數據類型和大小

選擇正確的數據類型和大小可以避免NULL，提高性能，保護數據，在適當情況下使用數據壓縮。

選擇正確的數據類型是表設計中一個很重要卻常常被忽視的部分，數據類型的大小可能會對表操作產生較大的影響。例如，選擇將SMALLINT 數字存儲為INT 會使該列所需的空間翻倍。在包含一百萬個行的表中，該決策將導致浪費額外的2 MB 存儲空間，并且磁盤操作速度會變慢，緩沖區和高速緩存將需要使用更多內存。使用INSERT … COMPRESS(field_name) … 和SELECT … UNCOMPRESS(column_name) ... 可以在存儲和檢索字符串數據時對其進行壓縮和解壓縮。盡管也可以使用CHAR 或VARCHAR 字段來實現此目的，但是通過使用VARBINARY或BLOB 列存儲壓縮數據可以避免字符集轉換出現問題。

3)         高效索引

創建最佳索引可以提高查詢吞吐量，減少I/O 開銷。

如果您通過在WHERE 子句中指定一個字段來查詢表中的特定行，并且該表沒有為該字段創建索引，MySQL 將讀取該表中的每一行以找到每個匹配的行。這將導致很多不必要的磁盤訪問，并且對于大型表性能將顯著降低。索引是有序的成組數據，通過索引，MySQL 可以更容易地找到查詢行的正確位置。默認情況下，InnoDB 將按主鍵排列表的順序；該有序表稱為群集索引。InnoDB 表上的每個附加索引或輔助索引會在文件系統中占用額外的空間，因為索引包含索引字段的額外副本以及主鍵的副本。每次使用INSERT、UPDATE、REPLACE 或DELETE 操作修改數據時，MySQL 也必須更新所有包含修改字段的索引。因此，向表中添加多個索引會降低影響該表的數據修改操作的性能。不過，如果對索引進行了適當設計，依賴于索引字段的查詢便會在性能上有較大的獲益。如果查詢無法使用索引找到特定行，則必須執行全表掃描；即，必須讀取整個表來找到該行。使用索引的查詢可以直接讀取相應行而不讀取其他行，這極大地提高了此類查詢的性能。

1.4.        PROCEDURE ANALYSE

PROCEDURE ANALYSE() ，在優化表結構時可以輔助參考分析語句。利用此語句，MySQL 幫你去分析你的字段和其實際的數據，并會給你一些有用的建議。但是，只有表中有實際的數據，這些建議才會變得有用，因為要做一些大的決定是需要有數據作為基礎的。

語法如下：

SELECT ... FROM table_name WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]]);

l  max_elements (默認值 256) 是analyse注意到每列不同值的最高數目。analyse使用此參數來檢查是否最優化的列的類型是ENUM類型。

l  max_memory (默認值 8192) 是analyse在查找所有不同值時分配給每列的最大內存數。

示例1：

以第4行的分析舉例，可以看出，br_Task.task_name字段：

列最小值:121 new

列最大值:韓-軟文-全國-銀牌見

最小長度:3字節

最大長度:52字節

平均長度:24.1852

優化建議：字段的數據類型改成VARCHAR(52) NOT NULL。

示例2：

PROCEDURE ANALYSE 分析給定查詢中的列，并提供對每個字段的調節反饋：

mysql> SELECT CountryCode, District, Population

-> FROM City PROCEDURE ANALYSE(250,1024)\G

默認設置通常建議使用ENUM 類型來優化表的設計。如果確定不想在分析列時使用PROCEDURE ANALYSE() 所建議的ENUM 值，請使用非默認參數。

第一個參數是分析ENUM 值是否適當時要考慮的不同元素數。此參數的默認值為256。

第二個參數是用于收集不同的值以供分析的最大內存量。此參數的默認值為8192，表示8 KB。如果為此參數設置值，則PROCEDURE ANALYSE() 無法檢查不同的值以建議使用ENUM 類型。如果PROCEDURE ANALYSE() 無法存儲可接受范圍內的候選ENUM 值（在參數設置的限制內），則不會建議對該列使用ENUM 類型。

本示例建議對City.CountryCode 列使用CHAR(3) 類型。另一方面，如果使用默認參數，則PROCEDURE ANALYSE() 將建議ENUM('ABW','AFG',...,'ZMB','ZWE')，這是一種包含超過200 個元素的ENUM 類型，其中針對每個相應的CountryCode 值都包含一個不同值。

1.5.        EXPLAIN

EXPLAIN 命令描述MySQL 打算如何執行特定的SQL 語句，不返回數據集的任何數據，并提供有關MySQL 打算如何執行該語句的信息

使用EXPLAIN 可檢查SELECT、INSERT、REPLACE、UPDATE 和DELETE 語句。 將EXPLAIN 置于語句之前，EXPLAIN SELECT ...、EXPLAIN UPDATE...；

EXPLAIN 將為語句中使用的每個表生成一行輸出。該輸出包含以下列：

— table：輸出行所對應的表

— select_type：查詢中使用的選擇類型。SIMPLE 意味著查詢未使用UNION 或子查詢。

— key：優化程序所選擇的索引

— ref：與索引比較的列

— rows：優化程序所檢查的行的估計數目

— Extra：優化程序提供的每個查詢的其他信息

有關輸出列的完整論述，請訪問：

http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

使用EXPLAIN EXTENDED ... 可查看優化程序提供的其他信息。有關完整論述，請訪問：

http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html

例如，以下查詢可聯接兩個表的字段并執行聚合：

mysql> SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population,

> Continent FROM Country JOIN City ON CountryCode = Code

> GROUP BY Continent ORDER BY Population DESC;

+--------+--------------+---------------+

| Cities | Population | Continent |

+--------+--------------+---------------+

| 1765 | 900934498400 | Asia |

| 580 | 95052481000 | North America |

| 842 | 55127805400 | Europe |

| 470 | 48533025000 | South America |

| 366 | 16179610000 | Africa |

| 55 | 307500750 | Oceania |

+--------+--------------+---------------+

6 rows in set (0.01 sec)

以下輸出顯示了在查詢前使用EXPLAIN 的結果：

mysql> EXPLAIN SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population,

> Continent FROM Country JOIN City ON CountryCode = Code

> GROUP BY Continent ORDER BY Population DESC\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: Country

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 239

Extra: Using temporary; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: City

type: ref

possible_keys: CountryCode

key: CountryCode

key_len: 3

ref: world_innodb.Country.Code

rows: 9

Extra: Using index

2 rows in set (0.00 sec)

EXPLAIN 格式

EXPLAIN 輸出也提供其他格式：

1）可視化EXPLAIN，圖形格式的輸出在MySQL Workbench 中提供

2）EXPLAIN FORMAT=JSON，JSON 格式的輸出，當要將EXPLAIN 輸出傳遞給程序以供進一步處理/分析時十分有用

JSON（JavaScript Object Notation，JavaScript 對象表示法）是一種簡單的數據交換格式。以下輸出顯示了在EXPLAIN 語句中使用FORMAT=JSON 的結果：

mysql> EXPLAIN FORMAT=JSON SELECT COUNT(*) as 'Cities', SUM(Country.Population) AS Population, Continent FROM Country JOIN City ON CountryCode = Code GROUP BY Continent ORDER BY Population DESC\G

*************************** 1. row ***************************

EXPLAIN: {

"query_block": {

"select_id": 1,

"ordering_operation": {

"using_filesort": true,

"grouping_operation": {

"using_temporary_table": true,

"using_filesort": false,

…

1 row in set, 1 warning (0.00 sec)

1.6.        服務器狀態

1)         檢查服務器狀態的方法

MySQL 提供了多種查看服務器狀態變量的方法：

l  在mysql 提示符下查看，命令STATUS、SHOW STATUS;

l  在終端上查看：

mysqladmin --login-path=login-path status

mysqladmin -u user -p extended-status

MySQL 通過mysql 命令STATUS 和mysqladmin 命令status 提供簡短的狀態消息。通過mysql 命令SHOW STATUS 和mysqladmin 命令extended-status 顯示的長格式狀態輸出包含許多系統狀態變量的值，后面的將討論其中最重要的值。

對mysqladmin 使用選項可提供附加功能。例如，--sleep（或-i）選項可指定在迭代之間等待的秒數，并在等待該時間后自動重新執行命令。--relative（或-r）選項顯示自上次迭代后每個變量的差異，而不是變量值。使用grep 等命令行工具可擴展mysqladmin 的使用方式。例如，使用以下命令可僅顯示包含字符串cache_hits 的變量：

shell> mysqladmin --login-path=admin extended-status | grep cache_hits

| Qcache_hits | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_session_cache_hits | 0 |

| Table_open_cache_hits | 280 |

2)         主要狀態變量

?  Created_tmp_disk_tables：顯示磁盤上的內部臨時表的數量；獲取執行語句時服務器所創建的臨時表數。如果該數值較高，則服務器已在磁盤上（而不是在內存中）創建多個臨時表，從而導致查詢執行較慢。

?  Handler_read_first：顯示索引中第一個條目的讀取次數，如果該數值較高，則服務器已執行多次完整索引掃描以完成查詢請求。

?  Innodb_buffer_pool_wait_free：顯示服務器等待干凈頁面的次數，等待InnoDB 緩沖池中的頁面刷新后才可以完成查詢請求。如果該數值較高，則未正確設置InnoDB 緩沖池的大小，因而查詢性能受到影響。

?  Max_used_connections：顯示自服務器啟動以來的最大并發連接數；此變量提供非常有用的信息來確定服務器必須支持的并發連接數。

?  Open_tables：顯示給定時間內打開的表的數量；將此變量與服務器系統變量table_cache 比較，可提供有關應該為表高速緩存預留多少內存量的有用信息。如果Open_tables 狀態變量的值通常很低，請減小服務器系統變量table_cache 的大小。如果該值很高（接近服務器系統變量table_cache 的值），請增加分配給表高速緩存的內存量來縮短查詢響應時間。

?  Select_full_join：顯示執行表掃描而不是使用索引的聯接數量，如果該值不是，則應該仔細檢查表的索引。

?  Slow_queries：顯示用時比long_query_time 系統變量所指定的秒數長的查詢數；此狀態變量取決于對long_query_time 變量（默認值為10 秒）設置的了解。如果Slow_queries 狀態變量不是，請檢查long_query_time 的值和慢速查詢日志，并改進所捕獲的查詢。

?  Sort_merge_passes：顯示排序算法所執行的合并傳遞次數；排序操作需要內存中的緩沖區。此狀態變量計算排序操作所需的經過排序緩沖區的傳遞次數。如果該值較高，則可能表明排序緩沖區大小不足以執行查詢的一次通過排序；請考慮增大sort_buffer_size 系統變量的值。

?  Threads_connected：顯示當前打開的連接數；定期捕獲該值可提供有關服務器何時最活躍的有用信息。使用此變量可確定執行服務器維護的最佳時間，或者可將其作為為服務器分配更多資源的依據。

?  Uptime：顯示服務器持續運行的秒數；該值可以提供有關服務器運行狀況的有用信息，例如服務器需要重新啟動的頻率。

1.7.        系統變量調優簡介

MySQL性能調優應該首先調節查詢、模式和索引，因為每次操作可獲得比調節變量更多的收益；其次考慮針對服務器大小進行調節，例如內存和I/O；然后針對應用程序配置進行調節；例如，存儲引擎設置，將物理RAM 的70%–85% 提供給InnoDB 緩沖池，最小化MyISAM 高速緩存和緩沖區等；最后，根據服務器負載類型（事務服務器、報告服務器）調整連接數等

一個常見的誤區是認為服務器變量配置是服務器調節中最重要的部分。事實上，從花費的精力來看，優化模式、常見查詢和典型數據庫的索引可獲得比調節變量更多的好處。

2  默認設置

Oracle 的MySQL 工程師選擇默認設置來適應大多數生產系統，這些系統常常要處理頻繁的小事務、許多更新和少數大型慢速查詢（如用于生成報告的查詢）。然而，由于MySQL 在從小型設備（如銷售點系統和路由器）到具有大量內存和快速磁盤陣列的大型Web 服務器等各種系統上都在使用，您可能會發現，對于您的特定環境和工作負荷，可以從更改服務器的某些默認設置中獲益。

2  InnoDB 設置

例如，在僅使用InnoDB 用戶表的MySQL 專用服務器上，可以將innodb_buffer_pool_size 的值增大到占服務器總內存的較大比例(70%–85%)，同時要記住操作系統的需要，如cron 作業、備份、病毒掃描以及管理連接和任務。如果有幾GB 的RAM，則還可以通過使用多個innodb_buffer_pool_instances 而獲益，該設置可啟用多個緩沖池，從而避免爭用。

2  降低MyISAM 設置

在不將MyISAM 用作用戶表的系統上，減小僅適用于MyISAM 的選項的值（例如將key_buffer_size 的值減小為16 MB 等較小值），同時要記住某些內部MySQL 操作將使用MyISAM。

2  報告系統

在用于運行少數大型慢速查詢（例如用于業務智能報表的查詢）的服務器上，使用join_buffer_size 和sort_buffer_size 等設置增加專用于緩沖區的內存量。雖然默認服務器設置更適合事務系統，但默認的my.cnf 文件包含這些變量適用于報告服務器的替代值。

2  事務系統

在用于支持許多反復斷開并重新連接的快速并發事務的服務器上，請將thread_cache_size 的值設置為足夠大的值，以便大多數新連接可以使用高速緩存的線程；這可避免創建和斷開每個連接的線程時的服務器開銷。

在支持多寫入操作的服務器上，請提高innodb_log_file_size 和innodb_log_buffer_size 等日志設置，因為數據修改操作的性能在很大程度上依賴于InnoDB 日志的性能。請考慮更改innodb_flush_log_at_trx_commit 的值以提高每次提交的性能，但風險是：如果服務器出現故障，可能會丟失某些數據。

如果您的應用程序反復執行相同的查詢（或多個相同的查詢），請考慮啟用查詢高速緩存，并根據常見查詢的結果調節其大小，方法是為query_cache_type 和query_cache_size 設置適當的值。

2  平衡內存使用

當您為每個查詢或每個連接的高速緩存和緩沖區設置較大的值時，會減少緩沖池的可用大小。調節服務器的配置變量是一個平衡過程，需要從默認值開始，提供盡可能多的內存給緩沖池，然后調節與以下項最緊密相關的變量：調節目標、通過檢查服務器狀態識別出的問題以及通過查詢性能模式識別出的瓶頸。

1)         主要服務器系統變量：

?  innodb_buffer_pool_size：定義InnoDB 用于緩存表數據和索引的內存緩沖區大小（以字節為單位）；要想獲得最佳性能，請將此值設置為盡可能大，同時要記住值過高會導致操作系統交換頁面，從而大大降低性能。如果在專用數據庫服務器上僅使用了InnoDB 用戶表，請考慮將此變量設置為介于物理RAM 的70% 到85% 之間的值。

?  innodb_flush_log_at_trx_commit：定義InnoDB 將日志緩沖區寫入日志文件的頻率，以及對日志文件執行刷新到磁盤操作的頻率；此變量有三種可能的設置：

n  ：每秒將日志緩沖區寫入磁盤一次。

n  1：每次提交時將日志刷新到磁盤；如果未發生提交，則每秒刷新一次。

n  2：將日志刷新到操作系統高速緩存中，并且每隔innodb_flush_log_at_timeout 秒（默認為一秒）刷新到磁盤一次。

?  innodb_log_buffer_size：定義InnoDB 用于寫入磁盤上的日志文件的緩沖區的大小（以字節為單位）；此變量的默認值為8 MB。事務超過此大小會導致InnoDB 在事務提交之前將日志刷新到磁盤，從而降低性能。對于使用大量BLOB 或者在更新活動中具有較大峰值的應用程序，可通過增大該值提高事務性能。

?  innodb_log_file_size：定義日志組中每個日志文件的大小（以字節為單位）；對于大型數據集上的寫入密集型工作負荷，請設置此變量以便所有日志文件的最大總大小（通過innodb_log_files_in_group 設置）小于或等于緩沖池的大小。大型日志文件會減緩故障恢復，但可以通過減少檢查點刷新活動來提高整體性能。

?  join_buffer_size：定義用于使用表掃描的聯接的最小緩沖區大小；對于包含無法使用索引的聯接的查詢，請以默認值(256 KB)為起點增大該值。運行此類查詢時請更改每個會話的值，以避免設置全局設置而使無需這么大值的查詢浪費內存。

?  query_cache_size：定義為緩存查詢結果而分配的內存量；通過使用查詢高速緩存，提高針對極少更改的數據發出重復查詢的應用程序的性能。作為基線，請根據重復查詢的數量和所返回數據的大小將此變量設置為介于32 MB 和512 MB 之間的值。請監控高速緩存命中率以確定此變量的有效性，并根據您的觀察調節其值。

?  sort_buffer_size：定義分配給需要進行排序的會話的最大內存量；如果Sort_merge_passes 狀態變量的值很高，請增大該值以提高ORDER BY 和GROUP BY 操作的性能。

?  table_open_cache：定義所有線程打開的表的數量；請設置該值以使其大于N * max_connections，其中，N 是在應用程序的所有查詢中所使用的最大表數量。該值過高會導致出現錯誤“Too many open files（打開的文件太多）”。Open_tables 狀態變量的值較高表示MySQL 頻繁打開和關閉表，因此應該增大table_open_cache。

?  thread_cache_size：定義服務器應緩存以供重用的線程數；默認情況下，此變量將自動調節大小。評估Threads_created 狀態變量可確定是否需要更改thread_cache_size 的值。

2)         準備調節

調節數據庫服務器可以與調節樂器類比，選擇要更改的值并確定一個目標，向上和向下調節該值并同時測試檢測的行為，確定最佳設置。

準備調節環境要盡可能地復制生產系；要減小與正在調節的變量無關的已更改因素的影響，請在停機期間對生產服務器執行調節，或者最好在復制的系統上進行調節。

然后確定調節目標，例如每秒處理更多事務，更快生成復雜報表，通過并發連接的峰值提高性能；調節之前，請確定一個目標。所選的調節變量取決于您設置的目標。有很少幾個連接的報告服務器的最佳設置與有許多連接且每秒處理數百個小型事務的事務應用服務器的最佳設置有很大的不同。內存與數據庫大小的比率較高的服務器與內存較小但數據庫較大的服務器相比，具有非常不同的性能特征。繁重的寫入工作負荷需要的設置不同于只讀系統。選擇適當的變量進行調節，如緩沖區、高速緩存、日志設置等；

最后，應用程序代碼，一般查詢日志等方法收集代表性語句；為了最準確地模擬正在針對其進行調節的工作負荷，請收集一組有代表性的語句。從應用程序中選擇查詢和修改操作比例正確的語句序列。在要優化的每天或每周期間內，使用一般查詢日志從生產服務器收集實際語句。

3)         練習調節

查找每個變量的最佳值的基準測試，首先將變量設置為低于其默認值的設置，然后進行基準測試，測量相關度量，如虛擬內存使用、所花費的平均時間、相關狀態變量；

然后，增大變量值并重復基準測試，如果需要，刷新狀態變量。

最后，將結果繪制成圖，查找收益的下降點和性能的高峰，并根據所用資源和性能之間的最佳平衡來決定最終變量值。

要查看所選度量的值，請使用：

— mysqlslap 或mysql 來運行工作負荷并獲取平均執行時間

— sql-bench 來運行更一般的基準測試

— mysqladmin extended-status 來獲取工作負荷前后的狀態變量的值

— top 等操作系統工具或/proc 文件系統來訪問過程度量

如果要針對特定變量使用多個不同值運行微調基準測試，或者如果要在很長一段時間內反復運行相同的基準測試，請考慮使用腳本語言來自動化基準測試中所使用的步驟。

4)         調節示例：排序緩沖區大小

本示例顯示了一系列針對具有繁重排序工作負荷的數據庫的測試結果，其中，運行測試時更改了sort_buffer_size 變量。

圖表顯示：

— 在sort_buffer_size 從32 KB 增大到512 KB 時，Sort_merge_passes 狀態變量的值（可使用mysqladmin extended_status -r 查看）急劇下降，在此之后又緩慢降低；

— 測試工作負荷所花的平均時間（可使用mysqlslap 查看）在sort_buffer_size為512 KB 時降低，在4 MB 時達到極大峰值，然后在8 MB 時下降，最終在32 MB時達到最佳性能；

— mysqld 進程的總虛擬內存（可使用top 查看）在sort_buffer_size 為512 KB時最小，此后一直到16 MB 都穩步上升，在32 MB 時急劇上升；

查詢的平均時間最短時，sort_buffer_size 為32 MB，該設置使用了大量內存，而緩沖池本來可以更好地利用這些內存。在本示例中，針對測試中所使用的工作負荷、服務器和數據庫的特定組合，512 KB 設置可在性能和所用內存之間提供最佳平衡。

“怎么理解MySQL性能調優”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！