處理Mysql性能上的問題，可以利用一些Linux常用的命令來查看Mysql對服務(wù)器的CUP和I/O使用情況，通過慢查詢?nèi)罩菊页鲇写齼?yōu)化的sql，通過show processlist查看正在執(zhí)行的sql的情況以及及時kill死鎖的sql，通過EXPLAIN分析需要優(yōu)化的sql語句。對Mysql內(nèi)部配置做一些調(diào)整。

一、查看Linux系統(tǒng)性能的常用命令

MySQL數(shù)據(jù)庫是常見的兩個瓶頸是CPU和I/O的瓶頸。CPU在飽和的時候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時候，磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時候，如果應(yīng)用分布在網(wǎng)絡(luò)上，那么查詢量相當(dāng)大的時候那么瓶頸就會出現(xiàn)在網(wǎng)絡(luò)上。Linux中我們常用mpstat、vmstat、iostat、sar和top來查看系統(tǒng)的性能狀態(tài)。

mpstat

mpstat是Multiprocessor Statistics的縮寫，是實時系統(tǒng)監(jiān)控工具。其報告為CPU的一些統(tǒng)計信息，這些信息存放在/proc/stat文件中。在多CPUs系統(tǒng)里，其不但能查看所有CPU的平均狀況信息，而且能夠查看特定CPU的信息。mpstat最大的特點是可以查看多核心cpu中每個計算核心的統(tǒng)計數(shù)據(jù)，而類似工具vmstat只能查看系統(tǒng)整體cpu情況。

vmstat

vmstat命令是最常見的Linux/Unix監(jiān)控工具，可以展現(xiàn)給定時間間隔的服務(wù)器的狀態(tài)值，包括服務(wù)器的CPU使用率，內(nèi)存使用，虛擬內(nèi)存交換情況，IO讀寫情況。這個命令是我查看Linux/Unix最喜愛的命令，一個是Linux/Unix都支持，二是相比top，我可以看到整個機器的CPU、內(nèi)存、IO的使用情況，而不是單單看到各個進程的CPU使用率和內(nèi)存使用率(使用場景不一樣)。

iostat

主要用于監(jiān)控系統(tǒng)設(shè)備的IO負(fù)載情況，iostat首次運行時顯示自系統(tǒng)啟動開始的各項統(tǒng)計信息，之后運行iostat將顯示自上次運行該命令以后的統(tǒng)計信息。用戶可以通過指定統(tǒng)計的次數(shù)和時間來獲得所需的統(tǒng)計信息。

sar

sar（System Activity Reporter系統(tǒng)活動情況報告）是目前 Linux 上最為全面的系統(tǒng)性能分析工具之一，可以從多方面對系統(tǒng)的活動進行報告，包括：文件的讀寫情況、系統(tǒng)調(diào)用的使用情況、磁盤I/O、CPU效率、內(nèi)存使用狀況、進程活動及IPC有關(guān)的活動等。

top

top命令是Linux下常用的性能分析工具，能夠?qū)崟r顯示系統(tǒng)中各個進程的資源占用狀況，類似于Windows的任務(wù)管理器。top顯示系統(tǒng)當(dāng)前的進程和其他狀況，是一個動態(tài)顯示過程,即可以通過用戶按鍵來不斷刷新當(dāng)前狀態(tài).如果在前臺執(zhí)行該命令，它將獨占前臺，直到用戶終止該程序為止。比較準(zhǔn)確的說，top命令提供了實時的對系統(tǒng)處理器的狀態(tài)監(jiān)視。它將顯示系統(tǒng)中CPU最“敏感”的任務(wù)列表。該命令可以按CPU使用。內(nèi)存使用和執(zhí)行時間對任務(wù)進行排序；而且該命令的很多特性都可以通過交互式命令或者在個人定制文件中進行設(shè)定。

除了服務(wù)器硬件的性能瓶頸，對于MySQL系統(tǒng)本身，我們可以使用工具來優(yōu)化數(shù)據(jù)庫的性能，通常有三種：使用索引，使用EXPLAIN分析查詢以及調(diào)整MySQL的內(nèi)部配置。

二、查詢與索引分析方法以及優(yōu)化方式

在優(yōu)化MySQL時，通常需要對數(shù)據(jù)庫進行分析，常見的分析手段有慢查詢?nèi)罩?，EXPLAIN 分析查詢，profiling分析以及show命令查詢系統(tǒng)狀態(tài)及系統(tǒng)變量，通過定位分析性能的瓶頸，才能更好的優(yōu)化數(shù)據(jù)庫系統(tǒng)的性能。

性能瓶頸定位

我們可以通過show命令查看MySQL狀態(tài)及變量，找到系統(tǒng)的瓶頸：

Mysql> show status ——顯示狀態(tài)信息（擴展show status like ‘XXX’）

Mysql> show variables ——顯示系統(tǒng)變量（擴展show variables like ‘XXX’）

Mysql> show innodb status ——顯示InnoDB存儲引擎的狀態(tài)

Mysql> show processlist ——查看當(dāng)前SQL執(zhí)行，包括執(zhí)行狀態(tài)、是否鎖表等

Shell> mysqladmin variables -u username -p password——顯示系統(tǒng)變量

Shell> mysqladmin extended-status -u username -p password——顯示狀態(tài)信息

我常用的主要有show status和show processlist。

慢查詢?nèi)罩痉治龇?/h3>

慢查詢?nèi)罩鹃_啟方法一：在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個配置參數(shù)

log-slow-queries=/data/mysqldata/slow-query.log

long_query_time=2

注：log-slow-queries參數(shù)為慢查詢?nèi)罩敬娣诺奈恢?，一般這個目錄要有mysql的運行帳號的可寫權(quán)限，一般都將這個目錄設(shè)置為mysql的數(shù)據(jù)存放目錄；long_query_time=2中的2表示查詢超過兩秒才記錄；在my.cnf或者my.ini中添加log-queries-not-using-indexes參數(shù)，表示記錄下沒有使用索引的查詢。

如下：

log-slow-queries=/data/mysqldata/slow-query.log

long_query_time=10

log-queries-not-using-indexes

慢查詢?nèi)罩鹃_啟方法二：當(dāng)然我們也可以通過命令行設(shè)置變量來即時啟動慢日志查詢，這里就不詳細(xì)介紹啦。

接下來就是打開log文件查看得知哪些SQL執(zhí)行效率低下，再進行分析和處理。

explain（執(zhí)行計劃）分析查詢

使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。通過explain命令可以得到:

– 表的讀取順序

– 數(shù)據(jù)讀取操作的操作類型

– 哪些索引可以使用

– 哪些索引被實際使用

– 表之間的引用

– 每張表有多少行被優(yōu)化器查詢

EXPLAIN字段：

ØTable：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的。

Øpossible_keys：顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引。

Økey：實際使用的索引。如果為NULL，則沒有使用索引。MYSQL很少會選擇優(yōu)化不足的索引，此時可以在SELECT語句中使用USE INDEX（index）來強制使用一個索引或者用IGNORE INDEX（index）來強制忽略索引。

Økey_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好。

Øref：顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)。

Ørows：MySQL認(rèn)為必須檢索的用來返回請求數(shù)據(jù)的行數(shù)。

Øtype：這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型為system、const、eq_reg、ref、range、index和ALL。

system、const：可以將查詢的變量轉(zhuǎn)為常量。如id=1; id為主鍵或唯一鍵.

eq_ref：訪問索引，返回某單一行的數(shù)據(jù)。(通常在聯(lián)接時出現(xiàn)，查詢使用的索引為主鍵或惟一鍵)。

ref：訪問索引,返回某個值的數(shù)據(jù).(可以返回多行) 通常使用=時發(fā)生

range：這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西，并且該字段上建有索引時發(fā)生的情況(注:不一定好于index)。

index：以索引的順序進行全表掃描，優(yōu)點是不用排序，缺點是還要全表掃描。

ALL：全表掃描，應(yīng)該盡量避免。

ØExtra：關(guān)于MYSQL如何解析查詢的額外信息，主要有以下幾種：

using index：只用到索引，可以避免訪問表。

using where：使用到where來過慮數(shù)據(jù)。不是所有的where clause都要顯示using where.。如以=方式訪問索引。

using tmporary：用到臨時表。

using filesort：用到額外的排序.。(當(dāng)使用order by v1，而沒用到索引時，就會使用額外的排序)。

range checked for eache record(index map:N)：沒有好的索引。

profiling分析查詢

通過慢日志查詢可以知道哪些SQL語句執(zhí)行效率低下，通過explain我們可以得知SQL語句的具體執(zhí)行情況，索引使用等，還可以結(jié)合show命令查看執(zhí)行狀態(tài)。

如果覺得explain的信息不夠詳細(xì)，可以同通過profiling命令得到更準(zhǔn)確的SQL執(zhí)行消耗系統(tǒng)資源的信息。

profiling默認(rèn)是關(guān)閉的，打開方式 ：

mysql>set profiling=1;

索引查詢優(yōu)化

索引的類型

Ø 普通索引：這是最基本的索引類型，沒唯一性之類的限制。

Ø 唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。

Ø 主鍵：主鍵是一種唯一索引，但必須指定為”PRIMARY KEY”。

Ø 全文索引：MYSQL從3.23.23開始支持全文索引和全文檢索。在MYSQL中，全文索引的索引類型為FULLTEXT。全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建。

大多數(shù)MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B樹中存儲。空間列類型的索引使用R-樹，MEMORY表支持hash索引。

單列索引和多列索引（復(fù)合索引）

索引可以是單列索引，也可以是多列索引。對相關(guān)的列使用索引是提高SELECT操作性能的最佳途徑之一。

多列索引：

MySQL可以為多個列創(chuàng)建索引。一個索引可以包括15個列。對于某些列類型，可以索引列的左前綴，列的順序非常重要。

多列索引可以視為包含通過連接索引列的值而創(chuàng)建的值的排序的數(shù)組。一般來說，即使是限制最嚴(yán)格的單列索引，它的限制能力也遠(yuǎn)遠(yuǎn)低于多列索引。

最左前綴：

多列索引有一個特點，即最左前綴（Leftmost Prefixing）。假如有一個多列索引為key(firstname lastname age)，當(dāng)搜索條件是以下各種列的組合和順序時，MySQL將使用該多列索引：

firstname，lastname，age

firstname，lastname

firstname

也就是說，相當(dāng)于還建立了key(firstname lastname)和key(firstname)。

索引主要用于下面的操作：

Ø 快速找出匹配一個WHERE子句的行。

Ø 刪除行。當(dāng)執(zhí)行聯(lián)接時，從其它表檢索行。

Ø 對具體有索引的列key_col找出MAX()或MIN()值。由預(yù)處理器進行優(yōu)化，檢查是否對索引中在key_col之前發(fā)生所有關(guān)鍵字元素使用了WHERE key_part_# = constant。在這種情況下，MySQL為每個MIN()或MAX()表達式執(zhí)行一次關(guān)鍵字查找，并用常數(shù)替換它。如果所有表達式替換為常量，查詢立即返回。例如：

SELECT MIN(key2), MAX (key2)  FROM tb WHERE key1=10;

Ø 如果對一個可用關(guān)鍵字的最左面的前綴進行了排序或分組(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有關(guān)鍵字元素后面有DESC，關(guān)鍵字以倒序被讀取。

Ø 在一些情況中，可以對一個查詢進行優(yōu)化以便不用查詢數(shù)據(jù)行即可以檢索值。如果查詢只使用來自某個表的數(shù)字型并且構(gòu)成某些關(guān)鍵字的最左面前綴的列，為了更快，可以從索引樹檢索出值。

SELECT key_part3 FROM tb WHERE key_part1=1;

有時MySQL不使用索引，即使有可用的索引。一種情形是當(dāng)優(yōu)化器估計到使用索引將需要MySQL訪問表中的大部分行時。(在這種情況下，表掃描可能會更快些）。然而，如果此類查詢使用LIMIT只搜索部分行，MySQL則使用索引，因為它可以更快地找到幾行并在結(jié)果中返回。

合理的建立索引

(1)  越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來更快。

(2)  簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因為字符串的比較更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型，而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址。

(3)  盡量避免NULL：應(yīng)該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值。

設(shè)置索引和編寫SQL語句時應(yīng)當(dāng)注意的一些瑣碎建議和注意點：

(1)當(dāng)結(jié)果集只有一行數(shù)據(jù)時使用LIMIT 1。

(2)避免SELECT *，始終指定你需要的列

從表中讀取越多的數(shù)據(jù)，查詢會變得更慢。他增加了磁盤需要操作的時間，還是在數(shù)據(jù)庫服務(wù)器與WEB服務(wù)器是獨立分開的情況下。你將會經(jīng)歷非常漫長的網(wǎng)絡(luò)延遲，僅僅是因為數(shù)據(jù)不必要的在服務(wù)器之間傳輸。

(3)使用連接（JOIN）來代替子查詢(Sub-Queries)

連接（JOIN）.. 之所以更有效率一些，是因為MySQL不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

(4)使用ENUM、CHAR而不是VARCHAR，使用合理的字段屬性長度。

(5)盡可能的使用NOT NULL。

(6)固定長度的表會更快。

(7)拆分大的DELETE或INSERT語句。

(8)查詢的列越小越快。

Where條件

在查詢中，WHERE條件也是一個比較重要的因素，盡量少并且是合理的where條件是很重要的，盡量在多個條件的時候，把會提取盡量少數(shù)據(jù)量的條件放在前面，減少后一個where條件的查詢時間。

避免索引無效的集中情況

Ø where子句的查詢條件里有！=，MySQL將無法使用索引。

Ø where子句使用了Mysql函數(shù)的時候，索引將無效，比如：select * from tb where left(name, 4) = ‘xxx’。

Ø 使用LIKE進行搜索匹配的時候，這樣索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 時索引無效。

三、Mysql內(nèi)部配置優(yōu)化

安裝MySQL后，配置文件my.cnf在 /MySQL安裝目錄/share/mysql目錄中，該目錄中還包含多個配置文件可供參考，有my-large.cnf ，my-huge.cnf，  my-medium.cnf，my-small.cnf，分別對應(yīng)大中小型數(shù)據(jù)庫應(yīng)用的配置。win環(huán)境下即存在于MySQL安裝目錄中的.ini文件。

下面列出了對性能優(yōu)化影響較大的主要變量，主要分為連接請求的變量和緩沖區(qū)變量。

連接請求的變量

1）max_connections

MySQL的最大連接數(shù)，如果服務(wù)器的并發(fā)連接請求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，當(dāng)然這建立在機器能支撐的情況下，因為如果連接數(shù)越多，介于MySQL會為每個連接提供連接緩沖區(qū)，就會開銷越多的內(nèi)存，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值。

數(shù)值過小會經(jīng)常出現(xiàn)ERROR 1040: Too many connections錯誤，可以通過’conn%’通配符查看當(dāng)前狀態(tài)的連接數(shù)量，以定奪該值的大小。

show variables like ‘max_connections’  #最大連接數(shù)

show  status like ‘max_used_connections’  #響應(yīng)的連接數(shù)

max_used_connections / max_connections * 100% （理想值≈ 85%）

如果max_used_connections跟max_connections相同，那么就是max_connections設(shè)置過低或者超過服務(wù)器負(fù)載上限了，低于10%則設(shè)置過大。

2）back_log

MySQL能暫存的連接數(shù)量。當(dāng)主要MySQL線程在一個很短時間內(nèi)得到非常多的連接請求，這就起作用。如果MySQL的連接數(shù)據(jù)達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log，將不被授予連接資源。

back_log值指出在MySQL暫時停止回答新請求之前的短時間內(nèi)有多少個請求可以被存在堆棧中。只有如果期望在一個短時間內(nèi)有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊列的大小。

當(dāng)觀察你主機進程列表（mysql> show full processlist），發(fā)現(xiàn)大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進程時，就要加大back_log 的值了。

默認(rèn)數(shù)值是50，可調(diào)優(yōu)為128，對于Linux系統(tǒng)設(shè)置范圍為小于512的整數(shù)。

3）interactive_timeout

一個交互連接在被服務(wù)器在關(guān)閉前等待行動的秒數(shù)。一個交互的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶。

默認(rèn)數(shù)值是28800，可調(diào)優(yōu)為7200。

緩沖區(qū)變量

全局緩沖：

1）key_buffer_size

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時磁盤表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。

舉例如下：

mysql> show variables like ‘key_buffer_size‘;

+——————-+————+

| Variable_name | Value      |

+———————+————+

| key_buffer_size | 536870912 |

+———— ———-+————+

key_buffer_size為512MB，我們再看一下key_buffer_size的使用情況：

mysql> show global status like ‘key_read%‘;

+————————+————-+

| Variable_name　  | Value    |

+————————+————-+

| Key_read_requests| 27813678764 |

| Key_reads　　　|  6798830      |

+————————+————-+

一共有27813678764個索引讀取請求，有6798830個請求在內(nèi)存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：

key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，設(shè)置在1/1000左右較好

默認(rèn)配置數(shù)值是8388600(8M)，主機有4GB內(nèi)存，可以調(diào)優(yōu)值為268435456(256MB)。

2） query_cache_size

使用查詢緩沖，MySQL將查詢結(jié)果存放在緩沖區(qū)中，今后對于同樣的SELECT語句（區(qū)分大小寫），將直接從緩沖區(qū)中讀取結(jié)果。

通過檢查狀態(tài)值Qcache_*，可以知道query_cache_size設(shè)置是否合理（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小；如果Qcache_hits的值不大，則表明你的查詢重復(fù)率很低，這種情況下使用查詢緩沖反而會影響效率，那么可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。

與查詢緩沖有關(guān)的參數(shù)還有query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查詢緩沖，可以設(shè)置為0、1、2，該變量是SESSION級的變量。

query_cache_limit指定單個查詢能夠使用的緩沖區(qū)大小，缺省為1M。

query_cache_min_res_unit是在4.1版本以后引入的，它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢結(jié)果都比較小，此時需要減小query_cache_min_res_unit。

舉例如下：

mysql> show global status like ‘qcache%‘;

+——————————-+—————–+

| Variable_name                  | Value　       |

+——————————-+—————–+

| Qcache_free_blocks　       | 22756　      |

| Qcache_free_memory　    | 76764704    |

| Qcache_hits　　　　　      | 213028692 |

| Qcache_inserts　　　　     | 208894227   |

| Qcache_lowmem_prunes   | 4010916      |

| Qcache_not_cached　| 13385031    |

| Qcache_queries_in_cache | 43560　|

| Qcache_total_blocks          | 111212　     |

+——————————-+—————–+

mysql> show variables like ‘query_cache%‘;

+————————————–+————–+

| Variable_name　　　　　       | Value　     |

+————————————–+———–+

| query_cache_limit　　　　　    | 2097152     |

| query_cache_min_res_unit　     | 4096　　  |

| query_cache_size　　　　　    | 203423744 |

| query_cache_type　　　　　   | ON　          |

| query_cache_wlock_invalidate | OFF　  |

+————————————–+—————+

查詢緩存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。

查詢緩存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大，可適當(dāng)減?。徊樵兙彺胬寐试?0％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。

查詢緩存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

示例服務(wù)器查詢緩存碎片率＝20.46％，查詢緩存利用率＝62.26％，查詢緩存命中率＝1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。

每個連接的緩沖

1）record_buffer_size

每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描，你可能想要增加該值。

默認(rèn)數(shù)值是131072(128K)，可改為16773120 (16M)

2）read_rnd_buffer_size

隨機讀緩沖區(qū)大小。當(dāng)按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但MySQL會為每個客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大。

一般可設(shè)置為16M

3） sort_buffer_size

每個需要進行排序的線程分配該大小的一個緩沖區(qū)。增加這值加速ORDER BY或GROUP BY操作。

默認(rèn)數(shù)值是2097144(2M)，可改為16777208 (16M)。

4） join_buffer_size

聯(lián)合查詢操作所能使用的緩沖區(qū)大小

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個線程獨占，也就是說，如果有100個線程連接，則占用為16M*100

5） table_cache

表高速緩存的大小。每當(dāng)MySQL訪問一個表時，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內(nèi)容。通過檢查峰值時間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發(fā)現(xiàn)open_tables等于table_cache，并且opened_tables在不斷增長，那么你就需要增加table_cache的值了（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設(shè)置成很大的值。如果設(shè)置得太高，可能會造成文件描述符不足，從而造成性能不穩(wěn)定或者連接失敗。

1G內(nèi)存機器，推薦值是128－256。內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M。

6） max_heap_table_size

用戶可以創(chuàng)建的內(nèi)存表(memory table)的大小。這個值用來計算內(nèi)存表的最大行數(shù)值。這個變量支持動態(tài)改變，即set @max_heap_table_size=#

這個變量和tmp_table_size一起限制了內(nèi)部內(nèi)存表的大小。如果某個內(nèi)部heap（堆積）表大小超過tmp_table_size，MySQL可以根據(jù)需要自動將內(nèi)存中的heap表改為基于硬盤的MyISAM表。

7）tmp_table_size

通過設(shè)置tmp_table_size選項來增加一張臨時表的大小，例如做高級GROUP BY操作生成的臨時表。如果調(diào)高該值，MySQL同時將增加heap表的大小，可達到提高聯(lián)接查詢速度的效果，建議盡量優(yōu)化查詢，要確保查詢過程中生成的臨時表在內(nèi)存中，避免臨時表過大導(dǎo)致生成基于硬盤的MyISAM表。

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+

| Variable_name　　           | Value　|

+———————————-+———+

| Created_tmp_disk_tables | 21197  |

| Created_tmp_files　　　| 58　　|

| Created_tmp_tables　　| 1771587 |

+——————————–+———–+

每次創(chuàng)建臨時表，Created_tmp_tables增加，如果臨時表大小超過tmp_table_size，則是在磁盤上創(chuàng)建臨時表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時文件文件數(shù)，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應(yīng)該相當(dāng)好了

默認(rèn)為16M，可調(diào)到64-256最佳，線程獨占，太大可能內(nèi)存不夠I/O堵塞

8）thread_cache_size

可以復(fù)用的保存在中的線程的數(shù)量。如果有，新的線程從緩存中取得，當(dāng)斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。

通過比較 Connections和Threads_created狀態(tài)的變量，可以看到這個變量的作用。

默認(rèn)值為110，可調(diào)優(yōu)為80。

9） thread_concurrency

推薦設(shè)置為服務(wù)器 CPU核數(shù)的2倍，例如雙核的CPU, 那么thread_concurrency的應(yīng)該為4；2個雙核的cpu, thread_concurrency的值應(yīng)為8。默認(rèn)為8

10）wait_timeout

指定一個請求的最大連接時間，對于4GB左右內(nèi)存的服務(wù)器可以設(shè)置為5-10。

3.3、配置InnoDB的幾個變量

innodb_buffer_pool_size

對于InnoDB表來說，innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引。對于單獨的MySQL數(shù)據(jù)庫服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%。

根據(jù)MySQL手冊，對于2G內(nèi)存的機器，推薦值是1G（50%）。

innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時間點，取值分別為0、1、2三個。0，表示當(dāng)事務(wù)提交時，不做日志寫入操作，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次；1，則在每秒鐘或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作，確保了事務(wù)的ACID；設(shè)置為2，每次事務(wù)提交引起寫入日志文件的動作，但每秒鐘完成一次flush磁盤操作。

實際測試發(fā)現(xiàn)，該值對插入數(shù)據(jù)的速度影響非常大，設(shè)置為2時插入10000條記錄只需要2秒，設(shè)置為0時只需要1秒，而設(shè)置為1時則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務(wù)，這樣可以大幅提高速度。

根據(jù)MySQL手冊，在允許丟失最近部分事務(wù)的危險的前提下，可以把該值設(shè)為0或2。

innodb_log_buffer_size

log緩存大小，一般為1-8M，默認(rèn)為1M，對于較大的事務(wù)，可以增大緩存大小。

可設(shè)置為4M或8M。

innodb_additional_mem_pool_size

該參數(shù)指定InnoDB用來存儲數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。缺省值是1M。通常不用太大，只要夠用就行，應(yīng)該與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。如果不夠用，MySQL會在錯誤日志中寫入一條警告信息。

根據(jù)MySQL手冊，對于2G內(nèi)存的機器，推薦值是20M，可適當(dāng)增加。

innodb_thread_concurrency=8

推薦設(shè)置為 2*(NumCPUs+NumDisks)，默認(rèn)一般為8

總結(jié)

本文主要講的內(nèi)容包括：查看Linux系統(tǒng)性能的常用命令、查詢與索引分析方法以及優(yōu)化方式、Mysql內(nèi)部配置優(yōu)化。

到此這篇關(guān)于mysql中sql查詢性能分析和配置優(yōu)化的文章就介紹到這了,更多相關(guān)mysql中sql性能優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論