本篇內(nèi)容介紹了“怎么理解MySQL性能調(diào)優(yōu)”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

創(chuàng)新互聯(lián)公司主營(yíng)河南網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都App定制開發(fā),河南h5小程序制作搭建,河南網(wǎng)站營(yíng)銷推廣歡迎河南等地區(qū)企業(yè)咨詢

MySQL性能調(diào)優(yōu)概述

影響MySQL性能的因素主要有環(huán)境問題(CPU、磁盤I/O、網(wǎng)絡(luò)性能、操作系統(tǒng)爭(zhēng)用)和 MySQL 配置（數(shù)據(jù)庫(kù)設(shè)計(jì)<索引、數(shù)據(jù)類型、標(biāo)準(zhǔn)化>、應(yīng)用程序性能<特定請(qǐng)求、短時(shí)事務(wù)>、 配置變量<緩沖區(qū)、高速緩存、InnoDB 設(shè)置>）

MySQL 的性能受主機(jī)的性能特征影響。各種因素都會(huì)影響主機(jī)性能：CPU 速度和數(shù)量、磁盤吞吐量和訪問時(shí)間、網(wǎng)絡(luò)吞吐量以及操作系統(tǒng)上的競(jìng)爭(zhēng)服務(wù)都對(duì)MySQL 實(shí)例的性能有一定的影響。

數(shù)據(jù)庫(kù)內(nèi)容及其配置也會(huì)影響MySQL 性能。

— 包含頻繁小更新的數(shù)據(jù)庫(kù)將因?yàn)榫脑O(shè)計(jì)和標(biāo)準(zhǔn)化而受益。

— 當(dāng)您使用最小的適用數(shù)據(jù)類型來存儲(chǔ)數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)吞吐量將提高。

— 僅請(qǐng)求表數(shù)據(jù)的一個(gè)子集的查詢將從精心設(shè)計(jì)的索引中獲益。

— 僅請(qǐng)求特定行和列的應(yīng)用程序?qū)p少冗余請(qǐng)求所產(chǎn)生的開銷。

— 事務(wù)越短，越不會(huì)導(dǎo)致其他事務(wù)的鎖定和延遲。

— 調(diào)節(jié)得當(dāng)?shù)?a title="服務(wù)器" target="_blank" >服務(wù)器變量可優(yōu)化針對(duì)特定工作負(fù)荷和數(shù)據(jù)集的MySQL 緩沖區(qū)、高速緩存和其他資源的分配。

1.1.        性能監(jiān)控

要調(diào)節(jié)服務(wù)器的性能，必須了解其性能特征。為此，可以對(duì)整體性能進(jìn)行基準(zhǔn)測(cè)試，MySQL 安裝提供了以下基準(zhǔn)測(cè)試工具：

l  mysqlslap 是標(biāo)準(zhǔn)MySQL 分發(fā)的一部分。這是一個(gè)診斷程序，它模擬MySQL 服務(wù)器實(shí)例上的客戶機(jī)負(fù)載，并顯示每個(gè)階段的計(jì)時(shí)信息。

l  sql-bench 是MySQL 源代碼分發(fā)的一部分，它是一系列Perl 腳本，用于執(zhí)行多個(gè)語句和收集狀態(tài)計(jì)時(shí)數(shù)據(jù)。

以下是使用mysqlslap 在SQL 腳本中設(shè)置模式并運(yùn)行其他腳本中的查詢的示例：

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

此外，MySQL還提供了第三方基準(zhǔn)測(cè)試套件。

也可以使用慢速查詢?nèi)罩尽⒁话悴樵內(nèi)罩荆珽XPLAIN 、PROCEDURE ANALYSE逐個(gè)分析事件；慢速查詢?nèi)罩居糜谟涗洺^long_query_time 和min_examined_row_limit 變量所設(shè)置的限制的語句。使用mysqldumpslow 可查看慢速查詢?nèi)罩镜膬?nèi)容。一般查詢?nèi)罩居糜谟涗汳ySQL 所接收的所有客戶機(jī)連接和請(qǐng)求。使用該日志可記錄一段時(shí)間內(nèi)（例如，生成供mysqlslap 或其他基準(zhǔn)測(cè)試工具使用的工作負(fù)荷時(shí)）所接收的所有SQL語句。

使用SHOW STATUS和 mysqladmin extended-status 查看數(shù)據(jù)庫(kù)狀態(tài)，同時(shí)可以使用PERFORMANCE_SCHEMA 按組分析事件。

1.2.        性能模式

“性能模式”是在較低級(jí)別監(jiān)控MySQL 服務(wù)器執(zhí)行情況的功能。該功能是使用PERFORMANCE_SCHEMA 存儲(chǔ)引擎和performance_schema 數(shù)據(jù)庫(kù)實(shí)現(xiàn)的。從Oracle 下載的所有二進(jìn)制版本的MySQL 中都提供了性能模式。默認(rèn)情況下，將啟用性能模式，并在服務(wù)器啟動(dòng)時(shí)使用performance_schema 變量對(duì)其進(jìn)行控制。使用以下語句確認(rèn)已啟用性能模式：

mysql> SHOW VARIABLES LIKE 'performance_schema';

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

| Variable_name | Value |

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

| performance_schema | ON |

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

性能模式下允許您監(jiān)控并檢查MySQL 服務(wù)器中的被檢測(cè)代碼的性能特征。開發(fā)者將檢測(cè)函數(shù)和其他編碼事件以收集計(jì)時(shí)信息；公開的性能數(shù)據(jù)對(duì)MySQL 代碼庫(kù)的貢獻(xiàn)者、插件開發(fā)者很有幫助，同時(shí)對(duì)識(shí)別低級(jí)別的性能瓶頸，如日志文件I/O 等待或緩沖池互斥等任務(wù)有極大好處。性能模式所公開的信息可用于識(shí)別低級(jí)別瓶頸。該信息中大部分都是低級(jí)別的，可幫助MySQL 服務(wù)器產(chǎn)品系列的開發(fā)者調(diào)試性能問題，或者幫助系統(tǒng)架構(gòu)師和性能顧問調(diào)節(jié)InnoDB 數(shù)據(jù)和日志文件存儲(chǔ)硬件。

l  檢測(cè)（INSTRUMENT）、實(shí)例（INSTANCE）、事件（EVENT）和使用者（CONSUMER）

性能模式數(shù)據(jù)庫(kù)包含配置和事件信息：

— 檢測(cè)"是服務(wù)器代碼中引發(fā)要監(jiān)控的事件的點(diǎn)，在setup_instruments表中進(jìn)行配置；

— 每個(gè)被檢測(cè)的對(duì)象都是該檢測(cè)的一個(gè)“實(shí)例”，記錄在一系列實(shí)例表中。

— 當(dāng)線程執(zhí)行檢測(cè)實(shí)例中的代碼時(shí)，MySQL 將識(shí)別所發(fā)生的“事件”，將其記錄在事件和匯總表中。

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

性能模式中的檢測(cè)是服務(wù)器源代碼中的點(diǎn)，MySQL 從該點(diǎn)引發(fā)事件。檢測(cè)具有分層命名約定。例如，以下是包含性能模式中數(shù)百個(gè)檢測(cè)中的一部分的簡(jiǎn)短列表：

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

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

性能模式將記錄實(shí)例表中的每個(gè)檢測(cè)實(shí)例。例如，以下查詢顯示檢測(cè)wait/io/file/sql/FRM 記錄文件實(shí)例/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)

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

當(dāng)MySQL 識(shí)別了發(fā)生在檢測(cè)實(shí)例中的事件后，會(huì)將其記錄在事件表中。

— 主事件表為events_waits_current，該表中存儲(chǔ)了每個(gè)線程最近的事件。

— events_waits_history 存儲(chǔ)每個(gè)線程的最近10 個(gè)事件。

— events_waits_history_long 共存儲(chǔ)10,000 個(gè)最近事件。

events_waits_* 表都使用相同的模式。有關(guān)該模式的結(jié)構(gòu)信息，請(qǐng)?jiān)L問

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

當(dāng)使用性能模式識(shí)別瓶頸或其他問題時(shí)，請(qǐng)執(zhí)行以下操作：

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

2. 運(yùn)行用于產(chǎn)生該問題的測(cè)試用例。

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

4. 禁用那些用于評(píng)估已排除的問題的檢測(cè)。

5. 重試該測(cè)試用例。

1.3.        一般數(shù)據(jù)庫(kù)優(yōu)化

1)         標(biāo)準(zhǔn)化

對(duì)數(shù)據(jù)進(jìn)行標(biāo)準(zhǔn)化可以消除冗余數(shù)據(jù)，提高事務(wù)性工作負(fù)荷的性能，提供對(duì)數(shù)據(jù)的靈活訪問，最大限度地減少數(shù)據(jù)不一致情況。

標(biāo)準(zhǔn)化是移除數(shù)據(jù)庫(kù)中的冗余和不當(dāng)依賴關(guān)系（以避免將相同的數(shù)據(jù)存儲(chǔ)在多個(gè)地方以及出現(xiàn)異常的風(fēng)險(xiǎn)）的行為。標(biāo)準(zhǔn)化通常會(huì)產(chǎn)生以下結(jié)果：許多表的列變少，整體存儲(chǔ)要求降低，I/O 需求降低以及單次插入、更新和刪除操作加快。這提高了頻繁執(zhí)行小更新的事務(wù)性工作負(fù)荷的性能，但會(huì)使檢索大量數(shù)據(jù)的查詢變得復(fù)雜。

2)         數(shù)據(jù)類型和大小

選擇正確的數(shù)據(jù)類型和大小可以避免NULL，提高性能，保護(hù)數(shù)據(jù)，在適當(dāng)情況下使用數(shù)據(jù)壓縮。

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

3)         高效索引

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

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

1.4.        PROCEDURE ANALYSE

PROCEDURE ANALYSE() ，在優(yōu)化表結(jié)構(gòu)時(shí)可以輔助參考分析語句。利用此語句，MySQL 幫你去分析你的字段和其實(shí)際的數(shù)據(jù)，并會(huì)給你一些有用的建議。但是，只有表中有實(shí)際的數(shù)據(jù)，這些建議才會(huì)變得有用，因?yàn)橐鲆恍┐蟮臎Q定是需要有數(shù)據(jù)作為基礎(chǔ)的。

語法如下：

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

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

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

示例1：

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

列最小值:121 new

列最大值:韓-軟文-全國(guó)-銀牌見

最小長(zhǎng)度:3字節(jié)

最大長(zhǎng)度:52字節(jié)

平均長(zhǎng)度:24.1852

優(yōu)化建議：字段的數(shù)據(jù)類型改成VARCHAR(52) NOT NULL。

示例2：

PROCEDURE ANALYSE 分析給定查詢中的列，并提供對(duì)每個(gè)字段的調(diào)節(jié)反饋：

mysql> SELECT CountryCode, District, Population

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

默認(rèn)設(shè)置通常建議使用ENUM 類型來優(yōu)化表的設(shè)計(jì)。如果確定不想在分析列時(shí)使用PROCEDURE ANALYSE() 所建議的ENUM 值，請(qǐng)使用非默認(rèn)參數(shù)。

第一個(gè)參數(shù)是分析ENUM 值是否適當(dāng)時(shí)要考慮的不同元素?cái)?shù)。此參數(shù)的默認(rèn)值為256。

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

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

1.5.        EXPLAIN

EXPLAIN 命令描述MySQL 打算如何執(zhí)行特定的SQL 語句，不返回?cái)?shù)據(jù)集的任何數(shù)據(jù)，并提供有關(guān)MySQL 打算如何執(zhí)行該語句的信息

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

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

— table：輸出行所對(duì)應(yīng)的表

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

— key：優(yōu)化程序所選擇的索引

— ref：與索引比較的列

— rows：優(yōu)化程序所檢查的行的估計(jì)數(shù)目

— Extra：優(yōu)化程序提供的每個(gè)查詢的其他信息

有關(guān)輸出列的完整論述，請(qǐng)?jiān)L問：

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

使用EXPLAIN EXTENDED ... 可查看優(yōu)化程序提供的其他信息。有關(guān)完整論述，請(qǐng)?jiān)L問：

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

例如，以下查詢可聯(lián)接兩個(gè)表的字段并執(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 的結(jié)果：

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 格式的輸出，當(dāng)要將EXPLAIN 輸出傳遞給程序以供進(jìn)一步處理/分析時(shí)十分有用

JSON（JavaScript Object Notation，JavaScript 對(duì)象表示法）是一種簡(jiǎn)單的數(shù)據(jù)交換格式。以下輸出顯示了在EXPLAIN 語句中使用FORMAT=JSON 的結(jié)果：

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.        服務(wù)器狀態(tài)

1)         檢查服務(wù)器狀態(tài)的方法

MySQL 提供了多種查看服務(wù)器狀態(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 提供簡(jiǎn)短的狀態(tài)消息。通過mysql 命令SHOW STATUS 和mysqladmin 命令extended-status 顯示的長(zhǎng)格式狀態(tài)輸出包含許多系統(tǒng)狀態(tài)變量的值，后面的將討論其中最重要的值。

對(duì)mysqladmin 使用選項(xiàng)可提供附加功能。例如，--sleep（或-i）選項(xiàng)可指定在迭代之間等待的秒數(shù)，并在等待該時(shí)間后自動(dòng)重新執(zhí)行命令。--relative（或-r）選項(xiàng)顯示自上次迭代后每個(gè)變量的差異，而不是變量值。使用grep 等命令行工具可擴(kuò)展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í)表的數(shù)量；獲取執(zhí)行語句時(shí)服務(wù)器所創(chuàng)建的臨時(shí)表數(shù)。如果該數(shù)值較高，則服務(wù)器已在磁盤上（而不是在內(nèi)存中）創(chuàng)建多個(gè)臨時(shí)表，從而導(dǎo)致查詢執(zhí)行較慢。

?  Handler_read_first：顯示索引中第一個(gè)條目的讀取次數(shù)，如果該數(shù)值較高，則服務(wù)器已執(zhí)行多次完整索引掃描以完成查詢請(qǐng)求。

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

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

?  Open_tables：顯示給定時(shí)間內(nèi)打開的表的數(shù)量；將此變量與服務(wù)器系統(tǒng)變量table_cache 比較，可提供有關(guān)應(yīng)該為表高速緩存預(yù)留多少內(nèi)存量的有用信息。如果Open_tables 狀態(tài)變量的值通常很低，請(qǐng)減小服務(wù)器系統(tǒng)變量table_cache 的大小。如果該值很高（接近服務(wù)器系統(tǒng)變量table_cache 的值），請(qǐng)?jiān)黾臃峙浣o表高速緩存的內(nèi)存量來縮短查詢響應(yīng)時(shí)間。

?  Select_full_join：顯示執(zhí)行表掃描而不是使用索引的聯(lián)接數(shù)量，如果該值不是，則應(yīng)該仔細(xì)檢查表的索引。

?  Slow_queries：顯示用時(shí)比long_query_time 系統(tǒng)變量所指定的秒數(shù)長(zhǎng)的查詢數(shù)；此狀態(tài)變量取決于對(duì)long_query_time 變量（默認(rèn)值為10 秒）設(shè)置的了解。如果Slow_queries 狀態(tài)變量不是，請(qǐng)檢查long_query_time 的值和慢速查詢?nèi)罩荆⒏倪M(jìn)所捕獲的查詢。

?  Sort_merge_passes：顯示排序算法所執(zhí)行的合并傳遞次數(shù)；排序操作需要內(nèi)存中的緩沖區(qū)。此狀態(tài)變量計(jì)算排序操作所需的經(jīng)過排序緩沖區(qū)的傳遞次數(shù)。如果該值較高，則可能表明排序緩沖區(qū)大小不足以執(zhí)行查詢的一次通過排序；請(qǐng)考慮增大sort_buffer_size 系統(tǒng)變量的值。

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

?  Uptime：顯示服務(wù)器持續(xù)運(yùn)行的秒數(shù)；該值可以提供有關(guān)服務(wù)器運(yùn)行狀況的有用信息，例如服務(wù)器需要重新啟動(dòng)的頻率。

1.7.        系統(tǒng)變量調(diào)優(yōu)簡(jiǎn)介

MySQL性能調(diào)優(yōu)應(yīng)該首先調(diào)節(jié)查詢、模式和索引，因?yàn)槊看尾僮骺色@得比調(diào)節(jié)變量更多的收益；其次考慮針對(duì)服務(wù)器大小進(jìn)行調(diào)節(jié)，例如內(nèi)存和I/O；然后針對(duì)應(yīng)用程序配置進(jìn)行調(diào)節(jié)；例如，存儲(chǔ)引擎設(shè)置，將物理RAM 的70%–85% 提供給InnoDB 緩沖池，最小化MyISAM 高速緩存和緩沖區(qū)等；最后，根據(jù)服務(wù)器負(fù)載類型（事務(wù)服務(wù)器、報(bào)告服務(wù)器）調(diào)整連接數(shù)等

一個(gè)常見的誤區(qū)是認(rèn)為服務(wù)器變量配置是服務(wù)器調(diào)節(jié)中最重要的部分。事實(shí)上，從花費(fèi)的精力來看，優(yōu)化模式、常見查詢和典型數(shù)據(jù)庫(kù)的索引可獲得比調(diào)節(jié)變量更多的好處。

2  默認(rèn)設(shè)置

Oracle 的MySQL 工程師選擇默認(rèn)設(shè)置來適應(yīng)大多數(shù)生產(chǎn)系統(tǒng)，這些系統(tǒng)常常要處理頻繁的小事務(wù)、許多更新和少數(shù)大型慢速查詢（如用于生成報(bào)告的查詢）。然而，由于MySQL 在從小型設(shè)備（如銷售點(diǎn)系統(tǒng)和路由器）到具有大量?jī)?nèi)存和快速磁盤陣列的大型Web 服務(wù)器等各種系統(tǒng)上都在使用，您可能會(huì)發(fā)現(xiàn)，對(duì)于您的特定環(huán)境和工作負(fù)荷，可以從更改服務(wù)器的某些默認(rèn)設(shè)置中獲益。

2  InnoDB 設(shè)置

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

2  降低MyISAM 設(shè)置

在不將MyISAM 用作用戶表的系統(tǒng)上，減小僅適用于MyISAM 的選項(xiàng)的值（例如將key_buffer_size 的值減小為16 MB 等較小值），同時(shí)要記住某些內(nèi)部MySQL 操作將使用MyISAM。

2  報(bào)告系統(tǒng)

在用于運(yùn)行少數(shù)大型慢速查詢（例如用于業(yè)務(wù)智能報(bào)表的查詢）的服務(wù)器上，使用join_buffer_size 和sort_buffer_size 等設(shè)置增加專用于緩沖區(qū)的內(nèi)存量。雖然默認(rèn)服務(wù)器設(shè)置更適合事務(wù)系統(tǒng)，但默認(rèn)的my.cnf 文件包含這些變量適用于報(bào)告服務(wù)器的替代值。

2  事務(wù)系統(tǒng)

在用于支持許多反復(fù)斷開并重新連接的快速并發(fā)事務(wù)的服務(wù)器上，請(qǐng)將thread_cache_size 的值設(shè)置為足夠大的值，以便大多數(shù)新連接可以使用高速緩存的線程；這可避免創(chuàng)建和斷開每個(gè)連接的線程時(shí)的服務(wù)器開銷。

在支持多寫入操作的服務(wù)器上，請(qǐng)?zhí)岣遡nnodb_log_file_size 和innodb_log_buffer_size 等日志設(shè)置，因?yàn)閿?shù)據(jù)修改操作的性能在很大程度上依賴于InnoDB 日志的性能。請(qǐng)考慮更改innodb_flush_log_at_trx_commit 的值以提高每次提交的性能，但風(fēng)險(xiǎn)是：如果服務(wù)器出現(xiàn)故障，可能會(huì)丟失某些數(shù)據(jù)。

如果您的應(yīng)用程序反復(fù)執(zhí)行相同的查詢（或多個(gè)相同的查詢），請(qǐng)考慮啟用查詢高速緩存，并根據(jù)常見查詢的結(jié)果調(diào)節(jié)其大小，方法是為query_cache_type 和query_cache_size 設(shè)置適當(dāng)?shù)闹怠?/p>

2  平衡內(nèi)存使用

當(dāng)您為每個(gè)查詢或每個(gè)連接的高速緩存和緩沖區(qū)設(shè)置較大的值時(shí)，會(huì)減少緩沖池的可用大小。調(diào)節(jié)服務(wù)器的配置變量是一個(gè)平衡過程，需要從默認(rèn)值開始，提供盡可能多的內(nèi)存給緩沖池，然后調(diào)節(jié)與以下項(xiàng)最緊密相關(guān)的變量：調(diào)節(jié)目標(biāo)、通過檢查服務(wù)器狀態(tài)識(shí)別出的問題以及通過查詢性能模式識(shí)別出的瓶頸。

1)         主要服務(wù)器系統(tǒng)變量：

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

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

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

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

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

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

?  innodb_log_file_size：定義日志組中每個(gè)日志文件的大小（以字節(jié)為單位）；對(duì)于大型數(shù)據(jù)集上的寫入密集型工作負(fù)荷，請(qǐng)?jiān)O(shè)置此變量以便所有日志文件的最大總大小（通過innodb_log_files_in_group 設(shè)置）小于或等于緩沖池的大小。大型日志文件會(huì)減緩故障恢復(fù)，但可以通過減少檢查點(diǎn)刷新活動(dòng)來提高整體性能。

?  join_buffer_size：定義用于使用表掃描的聯(lián)接的最小緩沖區(qū)大小；對(duì)于包含無法使用索引的聯(lián)接的查詢，請(qǐng)以默認(rèn)值(256 KB)為起點(diǎn)增大該值。運(yùn)行此類查詢時(shí)請(qǐng)更改每個(gè)會(huì)話的值，以避免設(shè)置全局設(shè)置而使無需這么大值的查詢浪費(fèi)內(nèi)存。

?  query_cache_size：定義為緩存查詢結(jié)果而分配的內(nèi)存量；通過使用查詢高速緩存，提高針對(duì)極少更改的數(shù)據(jù)發(fā)出重復(fù)查詢的應(yīng)用程序的性能。作為基線，請(qǐng)根據(jù)重復(fù)查詢的數(shù)量和所返回?cái)?shù)據(jù)的大小將此變量設(shè)置為介于32 MB 和512 MB 之間的值。請(qǐng)監(jiān)控高速緩存命中率以確定此變量的有效性，并根據(jù)您的觀察調(diào)節(jié)其值。

?  sort_buffer_size：定義分配給需要進(jìn)行排序的會(huì)話的最大內(nèi)存量；如果Sort_merge_passes 狀態(tài)變量的值很高，請(qǐng)?jiān)龃笤撝狄蕴岣逴RDER BY 和GROUP BY 操作的性能。

?  table_open_cache：定義所有線程打開的表的數(shù)量；請(qǐng)?jiān)O(shè)置該值以使其大于N * max_connections，其中，N 是在應(yīng)用程序的所有查詢中所使用的最大表數(shù)量。該值過高會(huì)導(dǎo)致出現(xiàn)錯(cuò)誤“Too many open files（打開的文件太多）”。Open_tables 狀態(tài)變量的值較高表示MySQL 頻繁打開和關(guān)閉表，因此應(yīng)該增大table_open_cache。

?  thread_cache_size：定義服務(wù)器應(yīng)緩存以供重用的線程數(shù)；默認(rèn)情況下，此變量將自動(dòng)調(diào)節(jié)大小。評(píng)估Threads_created 狀態(tài)變量可確定是否需要更改thread_cache_size 的值。

2)         準(zhǔn)備調(diào)節(jié)

調(diào)節(jié)數(shù)據(jù)庫(kù)服務(wù)器可以與調(diào)節(jié)樂器類比，選擇要更改的值并確定一個(gè)目標(biāo)，向上和向下調(diào)節(jié)該值并同時(shí)測(cè)試檢測(cè)的行為，確定最佳設(shè)置。

準(zhǔn)備調(diào)節(jié)環(huán)境要盡可能地復(fù)制生產(chǎn)系；要減小與正在調(diào)節(jié)的變量無關(guān)的已更改因素的影響，請(qǐng)?jiān)谕C(jī)期間對(duì)生產(chǎn)服務(wù)器執(zhí)行調(diào)節(jié)，或者最好在復(fù)制的系統(tǒng)上進(jìn)行調(diào)節(jié)。

然后確定調(diào)節(jié)目標(biāo)，例如每秒處理更多事務(wù)，更快生成復(fù)雜報(bào)表，通過并發(fā)連接的峰值提高性能；調(diào)節(jié)之前，請(qǐng)確定一個(gè)目標(biāo)。所選的調(diào)節(jié)變量取決于您設(shè)置的目標(biāo)。有很少幾個(gè)連接的報(bào)告服務(wù)器的最佳設(shè)置與有許多連接且每秒處理數(shù)百個(gè)小型事務(wù)的事務(wù)應(yīng)用服務(wù)器的最佳設(shè)置有很大的不同。內(nèi)存與數(shù)據(jù)庫(kù)大小的比率較高的服務(wù)器與內(nèi)存較小但數(shù)據(jù)庫(kù)較大的服務(wù)器相比，具有非常不同的性能特征。繁重的寫入工作負(fù)荷需要的設(shè)置不同于只讀系統(tǒng)。選擇適當(dāng)?shù)淖兞窟M(jìn)行調(diào)節(jié)，如緩沖區(qū)、高速緩存、日志設(shè)置等；

最后，應(yīng)用程序代碼，一般查詢?nèi)罩镜确椒ㄊ占硇哉Z句；為了最準(zhǔn)確地模擬正在針對(duì)其進(jìn)行調(diào)節(jié)的工作負(fù)荷，請(qǐng)收集一組有代表性的語句。從應(yīng)用程序中選擇查詢和修改操作比例正確的語句序列。在要優(yōu)化的每天或每周期間內(nèi)，使用一般查詢?nèi)罩緩纳a(chǎn)服務(wù)器收集實(shí)際語句。

3)         練習(xí)調(diào)節(jié)

查找每個(gè)變量的最佳值的基準(zhǔn)測(cè)試，首先將變量設(shè)置為低于其默認(rèn)值的設(shè)置，然后進(jìn)行基準(zhǔn)測(cè)試，測(cè)量相關(guān)度量，如虛擬內(nèi)存使用、所花費(fèi)的平均時(shí)間、相關(guān)狀態(tài)變量；

然后，增大變量值并重復(fù)基準(zhǔn)測(cè)試，如果需要，刷新狀態(tài)變量。

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

要查看所選度量的值，請(qǐng)使用：

— mysqlslap 或mysql 來運(yùn)行工作負(fù)荷并獲取平均執(zhí)行時(shí)間

— sql-bench 來運(yùn)行更一般的基準(zhǔn)測(cè)試

— mysqladmin extended-status 來獲取工作負(fù)荷前后的狀態(tài)變量的值

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

如果要針對(duì)特定變量使用多個(gè)不同值運(yùn)行微調(diào)基準(zhǔn)測(cè)試，或者如果要在很長(zhǎng)一段時(shí)間內(nèi)反復(fù)運(yùn)行相同的基準(zhǔn)測(cè)試，請(qǐng)考慮使用腳本語言來自動(dòng)化基準(zhǔn)測(cè)試中所使用的步驟。

4)         調(diào)節(jié)示例：排序緩沖區(qū)大小

本示例顯示了一系列針對(duì)具有繁重排序工作負(fù)荷的數(shù)據(jù)庫(kù)的測(cè)試結(jié)果，其中，運(yùn)行測(cè)試時(shí)更改了sort_buffer_size 變量。

圖表顯示：

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

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

— mysqld 進(jìn)程的總虛擬內(nèi)存（可使用top 查看）在sort_buffer_size 為512 KB時(shí)最小，此后一直到16 MB 都穩(wěn)步上升，在32 MB 時(shí)急劇上升；

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

“怎么理解MySQL性能調(diào)優(yōu)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

分享文章：怎么理解MySQL性能調(diào)優(yōu)
文章來源：http://chinadenli.net/article0/pgjioo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、電子商務(wù)、網(wǎng)站內(nèi)鏈、網(wǎng)站收錄、響應(yīng)式網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)