本篇內(nèi)容介紹了“怎么理解MySQL性能調(diào)優(yōu)”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
創(chuàng)新互聯(lián)公司主營河南網(wǎng)站建設的網(wǎng)絡公司,主營網(wǎng)站建設方案,成都App定制開發(fā),河南h5小程序制作搭建,河南網(wǎng)站營銷推廣歡迎河南等地區(qū)企業(yè)咨詢
影響MySQL性能的因素主要有環(huán)境問題(CPU、磁盤I/O、網(wǎng)絡性能、操作系統(tǒng)爭用)和 MySQL 配置（數(shù)據(jù)庫設計<索引、數(shù)據(jù)類型、標準化>、應用程序性能<特定請求、短時事務>、 配置變量<緩沖區(qū)、高速緩存、InnoDB 設置>）
MySQL 的性能受主機的性能特征影響。各種因素都會影響主機性能：CPU 速度和數(shù)量、磁盤吞吐量和訪問時間、網(wǎng)絡吞吐量以及操作系統(tǒng)上的競爭服務都對MySQL 實例的性能有一定的影響。
數(shù)據(jù)庫內(nèi)容及其配置也會影響MySQL 性能。
— 包含頻繁小更新的數(shù)據(jù)庫將因為精心設計和標準化而受益。
— 當您使用最小的適用數(shù)據(jù)類型來存儲數(shù)據(jù)時，數(shù)據(jù)庫吞吐量將提高。
— 僅請求表數(shù)據(jù)的一個子集的查詢將從精心設計的索引中獲益。
— 僅請求特定行和列的應用程序將減少冗余請求所產(chǎn)生的開銷。
— 事務越短，越不會導致其他事務的鎖定和延遲。
— 調(diào)節(jié)得當?shù)?a title="服務器" target="_blank" >服務器變量可優(yōu)化針對特定工作負荷和數(shù)據(jù)集的MySQL 緩沖區(qū)、高速緩存和其他資源的分配。
1.1. 性能監(jiān)控
要調(diào)節(jié)服務器的性能，必須了解其性能特征。為此，可以對整體性能進行基準測試，MySQL 安裝提供了以下基準測試工具：
l mysqlslap 是標準MySQL 分發(fā)的一部分。這是一個診斷程序，它模擬MySQL 服務器實例上的客戶機負載，并顯示每個階段的計時信息。
l sql-bench 是MySQL 源代碼分發(fā)的一部分，它是一系列Perl 腳本，用于執(zhí)行多個語句和收集狀態(tài)計時數(shù)據(jù)。
以下是使用mysqlslap 在SQL 腳本中設置模式并運行其他腳本中的查詢的示例：
shell>mysqlslap --iterations=5000 --concurrency=50 --query=workload.sql --create=schema.sql --delimiter=";"
此外，MySQL還提供了第三方基準測試套件。
也可以使用慢速查詢?nèi)罩?、一般查詢?nèi)罩荆珽XPLAIN 、PROCEDURE ANALYSE逐個分析事件；慢速查詢?nèi)罩居糜谟涗洺^long_query_time 和min_examined_row_limit 變量所設置的限制的語句。使用mysqldumpslow 可查看慢速查詢?nèi)罩镜膬?nèi)容。一般查詢?nèi)罩居糜谟涗汳ySQL 所接收的所有客戶機連接和請求。使用該日志可記錄一段時間內(nèi)（例如，生成供mysqlslap 或其他基準測試工具使用的工作負荷時）所接收的所有SQL語句。
使用SHOW STATUS和 mysqladmin extended-status 查看數(shù)據(jù)庫狀態(tài)，同時可以使用PERFORMANCE_SCHEMA 按組分析事件。
1.2. 性能模式
“性能模式”是在較低級別監(jiān)控MySQL 服務器執(zhí)行情況的功能。該功能是使用PERFORMANCE_SCHEMA 存儲引擎和performance_schema 數(shù)據(jù)庫實現(xiàn)的。從Oracle 下載的所有二進制版本的MySQL 中都提供了性能模式。默認情況下，將啟用性能模式，并在服務器啟動時使用performance_schema 變量對其進行控制。使用以下語句確認已啟用性能模式：
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
性能模式下允許您監(jiān)控并檢查MySQL 服務器中的被檢測代碼的性能特征。開發(fā)者將檢測函數(shù)和其他編碼事件以收集計時信息；公開的性能數(shù)據(jù)對MySQL 代碼庫的貢獻者、插件開發(fā)者很有幫助，同時對識別低級別的性能瓶頸，如日志文件I/O 等待或緩沖池互斥等任務有極大好處。性能模式所公開的信息可用于識別低級別瓶頸。該信息中大部分都是低級別的，可幫助MySQL 服務器產(chǎn)品系列的開發(fā)者調(diào)試性能問題，或者幫助系統(tǒng)架構師和性能顧問調(diào)節(jié)InnoDB 數(shù)據(jù)和日志文件存儲硬件。
l 檢測（INSTRUMENT）、實例（INSTANCE）、事件（EVENT）和使用者（CONSUMER）
性能模式數(shù)據(jù)庫包含配置和事件信息：
— 檢測"是服務器代碼中引發(fā)要監(jiān)控的事件的點，在setup_instruments表中進行配置；
— 每個被檢測的對象都是該檢測的一個“實例”，記錄在一系列實例表中。
— 當線程執(zhí)行檢測實例中的代碼時，MySQL 將識別所發(fā)生的“事件”，將其記錄在事件和匯總表中。
— 每個“使用者”都是性能模式中表的名稱，用于記錄和查詢事件以及事件的摘要，在SETUP_CONSUMERS 表中進行配置。
性能模式中的檢測是服務器源代碼中的點，MySQL 從該點引發(fā)事件。檢測具有分層命名約定。例如，以下是包含性能模式中數(shù)百個檢測中的一部分的簡短列表：
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 表的內(nèi)容：
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 是性能模式中用于查詢事件和摘要的表的名稱。被禁用的使用者不記錄信息，從而節(jié)省了系統(tǒng)資源。
當MySQL 識別了發(fā)生在檢測實例中的事件后，會將其記錄在事件表中。
— 主事件表為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
當使用性能模式識別瓶頸或其他問題時，請執(zhí)行以下操作：
1. 確保已針對適用于您所遇到的問題類型的一系列檢測和使用者啟用了性能模式。例如，如果您確定問題出在I/O 限制上，請使用wait/io/file/* 檢測；如果不確定根本原因，請使用更廣范圍的檢測。
2. 運行用于產(chǎn)生該問題的測試用例。
3. 查詢events_waits_* 表等使用者，尤其是使用適用的WHERE 子句過濾器查詢events_waits_history_long，以便進一步縮小問題原因的范圍。
4. 禁用那些用于評估已排除的問題的檢測。
5. 重試該測試用例。
1.3. 一般數(shù)據(jù)庫優(yōu)化
1) 標準化
對數(shù)據(jù)進行標準化可以消除冗余數(shù)據(jù)，提高事務性工作負荷的性能，提供對數(shù)據(jù)的靈活訪問，最大限度地減少數(shù)據(jù)不一致情況。
標準化是移除數(shù)據(jù)庫中的冗余和不當依賴關系（以避免將相同的數(shù)據(jù)存儲在多個地方以及出現(xiàn)異常的風險）的行為。標準化通常會產(chǎn)生以下結果：許多表的列變少，整體存儲要求降低，I/O 需求降低以及單次插入、更新和刪除操作加快。這提高了頻繁執(zhí)行小更新的事務性工作負荷的性能，但會使檢索大量數(shù)據(jù)的查詢變得復雜。
2) 數(shù)據(jù)類型和大小
選擇正確的數(shù)據(jù)類型和大小可以避免NULL，提高性能，保護數(shù)據(jù)，在適當情況下使用數(shù)據(jù)壓縮。
選擇正確的數(shù)據(jù)類型是表設計中一個很重要卻常常被忽視的部分，數(shù)據(jù)類型的大小可能會對表操作產(chǎn)生較大的影響。例如，選擇將SMALLINT 數(shù)字存儲為INT 會使該列所需的空間翻倍。在包含一百萬個行的表中，該決策將導致浪費額外的2 MB 存儲空間，并且磁盤操作速度會變慢，緩沖區(qū)和高速緩存將需要使用更多內(nèi)存。使用INSERT … COMPRESS(field_name) … 和SELECT … UNCOMPRESS(column_name) ... 可以在存儲和檢索字符串數(shù)據(jù)時對其進行壓縮和解壓縮。盡管也可以使用CHAR 或VARCHAR 字段來實現(xiàn)此目的，但是通過使用VARBINARY或BLOB 列存儲壓縮數(shù)據(jù)可以避免字符集轉換出現(xiàn)問題。
3) 高效索引
創(chuàng)建最佳索引可以提高查詢吞吐量，減少I/O 開銷。
如果您通過在WHERE 子句中指定一個字段來查詢表中的特定行，并且該表沒有為該字段創(chuàng)建索引，MySQL 將讀取該表中的每一行以找到每個匹配的行。這將導致很多不必要的磁盤訪問，并且對于大型表性能將顯著降低。索引是有序的成組數(shù)據(jù)，通過索引，MySQL 可以更容易地找到查詢行的正確位置。默認情況下，InnoDB 將按主鍵排列表的順序；該有序表稱為群集索引。InnoDB 表上的每個附加索引或輔助索引會在文件系統(tǒng)中占用額外的空間，因為索引包含索引字段的額外副本以及主鍵的副本。每次使用INSERT、UPDATE、REPLACE 或DELETE 操作修改數(shù)據(jù)時，MySQL 也必須更新所有包含修改字段的索引。因此，向表中添加多個索引會降低影響該表的數(shù)據(jù)修改操作的性能。不過，如果對索引進行了適當設計，依賴于索引字段的查詢便會在性能上有較大的獲益。如果查詢無法使用索引找到特定行，則必須執(zhí)行全表掃描；即，必須讀取整個表來找到該行。使用索引的查詢可以直接讀取相應行而不讀取其他行，這極大地提高了此類查詢的性能。
1.4. PROCEDURE ANALYSE
PROCEDURE ANALYSE() ，在優(yōu)化表結構時可以輔助參考分析語句。利用此語句，MySQL 幫你去分析你的字段和其實際的數(shù)據(jù)，并會給你一些有用的建議。但是，只有表中有實際的數(shù)據(jù)，這些建議才會變得有用，因為要做一些大的決定是需要有數(shù)據(jù)作為基礎的。
語法如下：
SELECT ... FROM table_name WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]]);
l max_elements (默認值 256) 是analyse注意到每列不同值的最高數(shù)目。analyse使用此參數(shù)來檢查是否最優(yōu)化的列的類型是ENUM類型。
l max_memory (默認值 8192) 是analyse在查找所有不同值時分配給每列的最大內(nèi)存數(shù)。
示例1：
以第4行的分析舉例，可以看出，br_Task.task_name字段：
列最小值:121 new
列最大值:韓-軟文-全國-銀牌見
最小長度:3字節(jié)
最大長度:52字節(jié)
平均長度:24.1852
優(yōu)化建議：字段的數(shù)據(jù)類型改成VARCHAR(52) NOT NULL。
示例2：
PROCEDURE ANALYSE 分析給定查詢中的列，并提供對每個字段的調(diào)節(jié)反饋：
mysql> SELECT CountryCode, District, Population
-> FROM City PROCEDURE ANALYSE(250,1024)\G
默認設置通常建議使用ENUM 類型來優(yōu)化表的設計。如果確定不想在分析列時使用PROCEDURE ANALYSE() 所建議的ENUM 值，請使用非默認參數(shù)。
第一個參數(shù)是分析ENUM 值是否適當時要考慮的不同元素數(shù)。此參數(shù)的默認值為256。
第二個參數(shù)是用于收集不同的值以供分析的最大內(nèi)存量。此參數(shù)的默認值為8192，表示8 KB。如果為此參數(shù)設置值，則PROCEDURE ANALYSE() 無法檢查不同的值以建議使用ENUM 類型。如果PROCEDURE ANALYSE() 無法存儲可接受范圍內(nèi)的候選ENUM 值（在參數(shù)設置的限制內(nèi)），則不會建議對該列使用ENUM 類型。
本示例建議對City.CountryCode 列使用CHAR(3) 類型。另一方面，如果使用默認參數(shù)，則PROCEDURE ANALYSE() 將建議ENUM('ABW','AFG',...,'ZMB','ZWE')，這是一種包含超過200 個元素的ENUM 類型，其中針對每個相應的CountryCode 值都包含一個不同值。
1.5. EXPLAIN
EXPLAIN 命令描述MySQL 打算如何執(zhí)行特定的SQL 語句，不返回數(shù)據(jù)集的任何數(shù)據(jù)，并提供有關MySQL 打算如何執(zhí)行該語句的信息
使用EXPLAIN 可檢查SELECT、INSERT、REPLACE、UPDATE 和DELETE 語句。 將EXPLAIN 置于語句之前，EXPLAIN SELECT ...、EXPLAIN UPDATE...；
EXPLAIN 將為語句中使用的每個表生成一行輸出。該輸出包含以下列：
— table：輸出行所對應的表
— select_type：查詢中使用的選擇類型。SIMPLE 意味著查詢未使用UNION 或子查詢。
— key：優(yōu)化程序所選擇的索引
— ref：與索引比較的列
— rows：優(yōu)化程序所檢查的行的估計數(shù)目
— Extra：優(yōu)化程序提供的每個查詢的其他信息
有關輸出列的完整論述，請訪問：
http://dev.mysql.com/doc/refman/5.6/en/explain-output.html
使用EXPLAIN EXTENDED ... 可查看優(yōu)化程序提供的其他信息。有關完整論述，請訪問：
http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html
例如，以下查詢可聯(lián)接兩個表的字段并執(zhí)行聚合：
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 對象表示法）是一種簡單的數(shù)據(jù)交換格式。以下輸出顯示了在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. 服務器狀態(tài)
1) 檢查服務器狀態(tài)的方法
MySQL 提供了多種查看服務器狀態(tài)變量的方法：
l 在mysql 提示符下查看，命令STATUS、SHOW STATUS;
l 在終端上查看：
mysqladmin --login-path=login-path status
mysqladmin -u user -p extended-status
MySQL 通過mysql 命令STATUS 和mysqladmin 命令status 提供簡短的狀態(tài)消息。通過mysql 命令SHOW STATUS 和mysqladmin 命令extended-status 顯示的長格式狀態(tài)輸出包含許多系統(tǒng)狀態(tài)變量的值，后面的將討論其中最重要的值。
對mysqladmin 使用選項可提供附加功能。例如，--sleep（或-i）選項可指定在迭代之間等待的秒數(shù)，并在等待該時間后自動重新執(zhí)行命令。--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) 主要狀態(tài)變量
? Created_tmp_disk_tables：顯示磁盤上的內(nèi)部臨時表的數(shù)量；獲取執(zhí)行語句時服務器所創(chuàng)建的臨時表數(shù)。如果該數(shù)值較高，則服務器已在磁盤上（而不是在內(nèi)存中）創(chuàng)建多個臨時表，從而導致查詢執(zhí)行較慢。
? Handler_read_first：顯示索引中第一個條目的讀取次數(shù)，如果該數(shù)值較高，則服務器已執(zhí)行多次完整索引掃描以完成查詢請求。
? Innodb_buffer_pool_wait_free：顯示服務器等待干凈頁面的次數(shù)，等待InnoDB 緩沖池中的頁面刷新后才可以完成查詢請求。如果該數(shù)值較高，則未正確設置InnoDB 緩沖池的大小，因而查詢性能受到影響。
? Max_used_connections：顯示自服務器啟動以來的最大并發(fā)連接數(shù)；此變量提供非常有用的信息來確定服務器必須支持的并發(fā)連接數(shù)。
? Open_tables：顯示給定時間內(nèi)打開的表的數(shù)量；將此變量與服務器系統(tǒng)變量table_cache 比較，可提供有關應該為表高速緩存預留多少內(nèi)存量的有用信息。如果Open_tables 狀態(tài)變量的值通常很低，請減小服務器系統(tǒng)變量table_cache 的大小。如果該值很高（接近服務器系統(tǒng)變量table_cache 的值），請增加分配給表高速緩存的內(nèi)存量來縮短查詢響應時間。
? Select_full_join：顯示執(zhí)行表掃描而不是使用索引的聯(lián)接數(shù)量，如果該值不是，則應該仔細檢查表的索引。
? Slow_queries：顯示用時比long_query_time 系統(tǒng)變量所指定的秒數(shù)長的查詢數(shù)；此狀態(tài)變量取決于對long_query_time 變量（默認值為10 秒）設置的了解。如果Slow_queries 狀態(tài)變量不是，請檢查long_query_time 的值和慢速查詢?nèi)罩?，并改進所捕獲的查詢。
? Sort_merge_passes：顯示排序算法所執(zhí)行的合并傳遞次數(shù)；排序操作需要內(nèi)存中的緩沖區(qū)。此狀態(tài)變量計算排序操作所需的經(jīng)過排序緩沖區(qū)的傳遞次數(shù)。如果該值較高，則可能表明排序緩沖區(qū)大小不足以執(zhí)行查詢的一次通過排序；請考慮增大sort_buffer_size 系統(tǒng)變量的值。
? Threads_connected：顯示當前打開的連接數(shù)；定期捕獲該值可提供有關服務器何時最活躍的有用信息。使用此變量可確定執(zhí)行服務器維護的最佳時間，或者可將其作為為服務器分配更多資源的依據(jù)。
? Uptime：顯示服務器持續(xù)運行的秒數(shù)；該值可以提供有關服務器運行狀況的有用信息，例如服務器需要重新啟動的頻率。
1.7. 系統(tǒng)變量調(diào)優(yōu)簡介
MySQL性能調(diào)優(yōu)應該首先調(diào)節(jié)查詢、模式和索引，因為每次操作可獲得比調(diào)節(jié)變量更多的收益；其次考慮針對服務器大小進行調(diào)節(jié)，例如內(nèi)存和I/O；然后針對應用程序配置進行調(diào)節(jié)；例如，存儲引擎設置，將物理RAM 的70%–85% 提供給InnoDB 緩沖池，最小化MyISAM 高速緩存和緩沖區(qū)等；最后，根據(jù)服務器負載類型（事務服務器、報告服務器）調(diào)整連接數(shù)等
一個常見的誤區(qū)是認為服務器變量配置是服務器調(diào)節(jié)中最重要的部分。事實上，從花費的精力來看，優(yōu)化模式、常見查詢和典型數(shù)據(jù)庫的索引可獲得比調(diào)節(jié)變量更多的好處。
2 默認設置
Oracle 的MySQL 工程師選擇默認設置來適應大多數(shù)生產(chǎn)系統(tǒng)，這些系統(tǒng)常常要處理頻繁的小事務、許多更新和少數(shù)大型慢速查詢（如用于生成報告的查詢）。然而，由于MySQL 在從小型設備（如銷售點系統(tǒng)和路由器）到具有大量內(nèi)存和快速磁盤陣列的大型Web 服務器等各種系統(tǒng)上都在使用，您可能會發(fā)現(xiàn)，對于您的特定環(huán)境和工作負荷，可以從更改服務器的某些默認設置中獲益。
2 InnoDB 設置
例如，在僅使用InnoDB 用戶表的MySQL 專用服務器上，可以將innodb_buffer_pool_size 的值增大到占服務器總內(nèi)存的較大比例(70%–85%)，同時要記住操作系統(tǒng)的需要，如cron 作業(yè)、備份、病毒掃描以及管理連接和任務。如果有幾GB 的RAM，則還可以通過使用多個innodb_buffer_pool_instances 而獲益，該設置可啟用多個緩沖池，從而避免爭用。
2 降低MyISAM 設置
在不將MyISAM 用作用戶表的系統(tǒng)上，減小僅適用于MyISAM 的選項的值（例如將key_buffer_size 的值減小為16 MB 等較小值），同時要記住某些內(nèi)部MySQL 操作將使用MyISAM。
2 報告系統(tǒng)
在用于運行少數(shù)大型慢速查詢（例如用于業(yè)務智能報表的查詢）的服務器上，使用join_buffer_size 和sort_buffer_size 等設置增加專用于緩沖區(qū)的內(nèi)存量。雖然默認服務器設置更適合事務系統(tǒng)，但默認的my.cnf 文件包含這些變量適用于報告服務器的替代值。
2 事務系統(tǒng)
在用于支持許多反復斷開并重新連接的快速并發(fā)事務的服務器上，請將thread_cache_size 的值設置為足夠大的值，以便大多數(shù)新連接可以使用高速緩存的線程；這可避免創(chuàng)建和斷開每個連接的線程時的服務器開銷。
在支持多寫入操作的服務器上，請?zhí)岣遡nnodb_log_file_size 和innodb_log_buffer_size 等日志設置，因為數(shù)據(jù)修改操作的性能在很大程度上依賴于InnoDB 日志的性能。請考慮更改innodb_flush_log_at_trx_commit 的值以提高每次提交的性能，但風險是：如果服務器出現(xiàn)故障，可能會丟失某些數(shù)據(jù)。
如果您的應用程序反復執(zhí)行相同的查詢（或多個相同的查詢），請考慮啟用查詢高速緩存，并根據(jù)常見查詢的結果調(diào)節(jié)其大小，方法是為query_cache_type 和query_cache_size 設置適當?shù)闹怠?/p>
2 平衡內(nèi)存使用
當您為每個查詢或每個連接的高速緩存和緩沖區(qū)設置較大的值時，會減少緩沖池的可用大小。調(diào)節(jié)服務器的配置變量是一個平衡過程，需要從默認值開始，提供盡可能多的內(nèi)存給緩沖池，然后調(diào)節(jié)與以下項最緊密相關的變量：調(diào)節(jié)目標、通過檢查服務器狀態(tài)識別出的問題以及通過查詢性能模式識別出的瓶頸。
1) 主要服務器系統(tǒng)變量：
? innodb_buffer_pool_size：定義InnoDB 用于緩存表數(shù)據(jù)和索引的內(nèi)存緩沖區(qū)大小（以字節(jié)為單位）；要想獲得最佳性能，請將此值設置為盡可能大，同時要記住值過高會導致操作系統(tǒng)交換頁面，從而大大降低性能。如果在專用數(shù)據(jù)庫服務器上僅使用了InnoDB 用戶表，請考慮將此變量設置為介于物理RAM 的70% 到85% 之間的值。
? innodb_flush_log_at_trx_commit：定義InnoDB 將日志緩沖區(qū)寫入日志文件的頻率，以及對日志文件執(zhí)行刷新到磁盤操作的頻率；此變量有三種可能的設置：
n ：每秒將日志緩沖區(qū)寫入磁盤一次。
n 1：每次提交時將日志刷新到磁盤；如果未發(fā)生提交，則每秒刷新一次。
n 2：將日志刷新到操作系統(tǒng)高速緩存中，并且每隔innodb_flush_log_at_timeout 秒（默認為一秒）刷新到磁盤一次。
? innodb_log_buffer_size：定義InnoDB 用于寫入磁盤上的日志文件的緩沖區(qū)的大小（以字節(jié)為單位）；此變量的默認值為8 MB。事務超過此大小會導致InnoDB 在事務提交之前將日志刷新到磁盤，從而降低性能。對于使用大量BLOB 或者在更新活動中具有較大峰值的應用程序，可通過增大該值提高事務性能。
? innodb_log_file_size：定義日志組中每個日志文件的大?。ㄒ宰止?jié)為單位）；對于大型數(shù)據(jù)集上的寫入密集型工作負荷，請設置此變量以便所有日志文件的最大總大小（通過innodb_log_files_in_group 設置）小于或等于緩沖池的大小。大型日志文件會減緩故障恢復，但可以通過減少檢查點刷新活動來提高整體性能。
? join_buffer_size：定義用于使用表掃描的聯(lián)接的最小緩沖區(qū)大??；對于包含無法使用索引的聯(lián)接的查詢，請以默認值(256 KB)為起點增大該值。運行此類查詢時請更改每個會話的值，以避免設置全局設置而使無需這么大值的查詢浪費內(nèi)存。
? query_cache_size：定義為緩存查詢結果而分配的內(nèi)存量；通過使用查詢高速緩存，提高針對極少更改的數(shù)據(jù)發(fā)出重復查詢的應用程序的性能。作為基線，請根據(jù)重復查詢的數(shù)量和所返回數(shù)據(jù)的大小將此變量設置為介于32 MB 和512 MB 之間的值。請監(jiān)控高速緩存命中率以確定此變量的有效性，并根據(jù)您的觀察調(diào)節(jié)其值。
? sort_buffer_size：定義分配給需要進行排序的會話的最大內(nèi)存量；如果Sort_merge_passes 狀態(tài)變量的值很高，請增大該值以提高ORDER BY 和GROUP BY 操作的性能。
? table_open_cache：定義所有線程打開的表的數(shù)量；請設置該值以使其大于N * max_connections，其中，N 是在應用程序的所有查詢中所使用的最大表數(shù)量。該值過高會導致出現(xiàn)錯誤“Too many open files（打開的文件太多）”。Open_tables 狀態(tài)變量的值較高表示MySQL 頻繁打開和關閉表，因此應該增大table_open_cache。
? thread_cache_size：定義服務器應緩存以供重用的線程數(shù)；默認情況下，此變量將自動調(diào)節(jié)大小。評估Threads_created 狀態(tài)變量可確定是否需要更改thread_cache_size 的值。
2) 準備調(diào)節(jié)
調(diào)節(jié)數(shù)據(jù)庫服務器可以與調(diào)節(jié)樂器類比，選擇要更改的值并確定一個目標，向上和向下調(diào)節(jié)該值并同時測試檢測的行為，確定最佳設置。
準備調(diào)節(jié)環(huán)境要盡可能地復制生產(chǎn)系；要減小與正在調(diào)節(jié)的變量無關的已更改因素的影響，請在停機期間對生產(chǎn)服務器執(zhí)行調(diào)節(jié)，或者最好在復制的系統(tǒng)上進行調(diào)節(jié)。
然后確定調(diào)節(jié)目標，例如每秒處理更多事務，更快生成復雜報表，通過并發(fā)連接的峰值提高性能；調(diào)節(jié)之前，請確定一個目標。所選的調(diào)節(jié)變量取決于您設置的目標。有很少幾個連接的報告服務器的最佳設置與有許多連接且每秒處理數(shù)百個小型事務的事務應用服務器的最佳設置有很大的不同。內(nèi)存與數(shù)據(jù)庫大小的比率較高的服務器與內(nèi)存較小但數(shù)據(jù)庫較大的服務器相比，具有非常不同的性能特征。繁重的寫入工作負荷需要的設置不同于只讀系統(tǒng)。選擇適當?shù)淖兞窟M行調(diào)節(jié)，如緩沖區(qū)、高速緩存、日志設置等；
最后，應用程序代碼，一般查詢?nèi)罩镜确椒ㄊ占硇哉Z句；為了最準確地模擬正在針對其進行調(diào)節(jié)的工作負荷，請收集一組有代表性的語句。從應用程序中選擇查詢和修改操作比例正確的語句序列。在要優(yōu)化的每天或每周期間內(nèi)，使用一般查詢?nèi)罩緩纳a(chǎn)服務器收集實際語句。
3) 練習調(diào)節(jié)
查找每個變量的最佳值的基準測試，首先將變量設置為低于其默認值的設置，然后進行基準測試，測量相關度量，如虛擬內(nèi)存使用、所花費的平均時間、相關狀態(tài)變量；
然后，增大變量值并重復基準測試，如果需要，刷新狀態(tài)變量。
最后，將結果繪制成圖，查找收益的下降點和性能的高峰，并根據(jù)所用資源和性能之間的最佳平衡來決定最終變量值。
要查看所選度量的值，請使用：
— mysqlslap 或mysql 來運行工作負荷并獲取平均執(zhí)行時間
— sql-bench 來運行更一般的基準測試
— mysqladmin extended-status 來獲取工作負荷前后的狀態(tài)變量的值
— top 等操作系統(tǒng)工具或/proc 文件系統(tǒng)來訪問過程度量
如果要針對特定變量使用多個不同值運行微調(diào)基準測試，或者如果要在很長一段時間內(nèi)反復運行相同的基準測試，請考慮使用腳本語言來自動化基準測試中所使用的步驟。
4) 調(diào)節(jié)示例：排序緩沖區(qū)大小
本示例顯示了一系列針對具有繁重排序工作負荷的數(shù)據(jù)庫的測試結果，其中，運行測試時更改了sort_buffer_size 變量。
圖表顯示：
— 在sort_buffer_size 從32 KB 增大到512 KB 時，Sort_merge_passes 狀態(tài)變量的值（可使用mysqladmin extended_status -r 查看）急劇下降，在此之后又緩慢降低；
— 測試工作負荷所花的平均時間（可使用mysqlslap 查看）在sort_buffer_size為512 KB 時降低，在4 MB 時達到極大峰值，然后在8 MB 時下降，最終在32 MB時達到最佳性能；
— mysqld 進程的總虛擬內(nèi)存（可使用top 查看）在sort_buffer_size 為512 KB時最小，此后一直到16 MB 都穩(wěn)步上升，在32 MB 時急劇上升；
查詢的平均時間最短時，sort_buffer_size 為32 MB，該設置使用了大量內(nèi)存，而緩沖池本來可以更好地利用這些內(nèi)存。在本示例中，針對測試中所使用的工作負荷、服務器和數(shù)據(jù)庫的特定組合，512 KB 設置可在性能和所用內(nèi)存之間提供最佳平衡。
“怎么理解MySQL性能調(diào)優(yōu)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質量的實用文章！
重慶分公司
重慶分公司
