MySQL數(shù)據(jù)庫(kù)：理解MySQL的性能優(yōu)化、優(yōu)化查詢

最近一直在為大家更新MySQL相關(guān)學(xué)習(xí)內(nèi)容，可能有朋友不懂MySQL的重要性。在程序，語(yǔ)言，架構(gòu)更新?lián)Q代頻繁的今天，MySQL 恐怕是大家使用最多的存儲(chǔ)數(shù)據(jù)庫(kù)了。由于MySQL的優(yōu)化范圍較廣，從軟件到硬件，從配置到應(yīng)用，無法一一道來。

大量信息的存儲(chǔ)和查詢都會(huì)用到MySQL，因此它的優(yōu)化就對(duì)系統(tǒng)性能提升就尤為重要了。

MySQL性能優(yōu)化就是通過合理安排資源，調(diào)整系統(tǒng)參數(shù)使MySQL運(yùn)行更快、更節(jié)省資源。MySQL性能優(yōu)化包括查詢速度優(yōu)化、數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化、MySQL服務(wù)器優(yōu)化等。今天，小編就和大家一起來分享下MySQL性能優(yōu)化。

優(yōu)化簡(jiǎn)介

優(yōu)化MySQL數(shù)據(jù)庫(kù)是數(shù)據(jù)庫(kù)管理員和數(shù)據(jù)庫(kù)開發(fā)人員的必備技能。MySQL優(yōu)化，一方面是找出系統(tǒng)的瓶頸，提高M(jìn)ySQL數(shù)據(jù)庫(kù)整體的性能；另一方面需要合理的結(jié)構(gòu)設(shè)計(jì)和參數(shù)調(diào)整，以提高用戶操作響應(yīng)的速度；同時(shí)還要盡可能節(jié)省系統(tǒng)資源，以便系統(tǒng)可以提供更大負(fù)荷的服務(wù)。本節(jié)將為大家介紹優(yōu)化的基本知識(shí)。

MySQL數(shù)據(jù)庫(kù)優(yōu)化是多方面的，原則是減少系統(tǒng)的瓶頸，減少資源的占用，增加系統(tǒng)的反應(yīng)速度。例如，通過優(yōu)化文件系統(tǒng)，提高磁盤IO的讀寫速度；通過優(yōu)化操作系統(tǒng)調(diào)度策略，提高M(jìn)ySQL在高負(fù)荷情況下的負(fù)載能力；優(yōu)化表結(jié)構(gòu)、索引、查詢語(yǔ)句等使查詢響應(yīng)更快。

在MySQL中可以使用SHOWSTATUS語(yǔ)句查詢一些MySQL數(shù)據(jù)庫(kù)的性能參數(shù)。SHOW STATUS語(yǔ)句語(yǔ)法如下：

SHOW STATUS LIKE ‘value’;

其中，value是要查詢的參數(shù)值，一些常用的性能參數(shù)如下：

Connections： 連接MySQL服務(wù)器的次數(shù)。

Uptime： MySQL 服務(wù)器的上線時(shí)間。

Slow_ queries： 慢查詢的次數(shù)。

Com select： 查詢操作的次數(shù)。

Com_ insert： 插入操作的次數(shù)。

Com_ update： 更新操作的次數(shù)。

Com_ delete： 刪除操作的次數(shù)。

優(yōu)化查詢

查詢是數(shù)據(jù)庫(kù)中最頻繁的操作，提高查詢速度可以有效地提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能。本節(jié)將為大家介紹優(yōu)化查詢的方法。

分析查詢語(yǔ)句

通過對(duì)查詢語(yǔ)句的分析，可以了解查詢語(yǔ)句的執(zhí)行情況，找出查詢語(yǔ)句執(zhí)行的瓶頸，從而優(yōu)化查詢語(yǔ)句。MySQL中提供了EXPLAIN語(yǔ)句和DESCRIBE語(yǔ)句，用來分析查詢語(yǔ)句。本小節(jié)將為大家介紹使用EXPLAIN語(yǔ)句和DESCRIBE語(yǔ)句分析查詢語(yǔ)句的方法。

EXPLAIN語(yǔ)句的基本語(yǔ)法如下：

EXPLAIN ［ EXTENDED］ SELECT select options

使用EXTENED關(guān)鍵字，EXPLAIN 語(yǔ)句將產(chǎn)生附加信息。select _options 是SELECT語(yǔ)句的查詢選項(xiàng)，包括FROM WHERE子句等。

執(zhí)行該語(yǔ)句，可以分析EXPLAIN后面的SELECT語(yǔ)句的執(zhí)行情況，并且能夠分析出所查詢的表的一些特征。

使用EXPLAIN語(yǔ)句來分析1個(gè)查詢語(yǔ)句，執(zhí)行如下語(yǔ)句：

下面對(duì)查詢結(jié)果進(jìn)行解釋。

1、id： SELECT識(shí)別符。這是SELECT的查詢序列號(hào)。

2、select_ type： 表示SELECT語(yǔ)句的類型。它可以是以下幾種取值：

SIMPLE表示簡(jiǎn)單查詢，其中不包括連接查詢和子查詢;

PRIMARY表示主查詢，或者是最外層的查詢語(yǔ)句;

UNION表示連接查詢的第2個(gè)或后面的查詢語(yǔ)句;

DEPENDENT UNION，連接查詢中的第2個(gè)或后面的SELECT語(yǔ)句，取決于外面的查詢;

UNION RESULT，連接查詢的結(jié)果; 。

SUBQUERY， 子查詢中的第1個(gè)SELECT語(yǔ)句;

DEPENDENT SUBQUERY，子查詢中的第1個(gè)SELECT，取決于外面的查詢;

DERIVED， 導(dǎo)出表的SELECT （FROM子句的子查詢）。

3、table： 表示查詢的表。

type： 表示表的連接類型。下面按照從最佳類型到最差類型的順序給出各種連接類型：

system

該表是僅有一-行的系統(tǒng)表。這是const連接類型的一個(gè)特例。

const

數(shù)據(jù)表最多只有一個(gè)匹配行，它將在查詢開始時(shí)被讀取，并在余下的查詢優(yōu)化中作為常量對(duì)待。const表查詢速度很快因?yàn)樗鼈冎蛔x取一次。const用于使用常數(shù)值比較PRIMARYKEY或UNIQUE索引的所有部分的場(chǎng)合。

在下面的查詢中，tbl_ name 可用于const表：

SELECT * from tbl name WHERE primary key=1 ;

SELECT* from tbl name

WHERE primary key part1=1AND primary key_ part2=2 ;

range

只檢索給定范圍的行，使用一一個(gè)索引來選擇行。key 列顯示使用了哪個(gè)索引。key_len 包含所使用索引的最長(zhǎng)關(guān)鍵元素。

當(dāng)使用=、《、》、》=、《、《=、IS NULL、《》、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時(shí)，類型為range。

下面介紹幾種檢索指定行情況：

index

該連接類型與ALL相同，除了只掃描索引樹。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。

ALL

對(duì)于前面的表的任意行組合，進(jìn)行完整的表掃描。如果表是第一一個(gè)沒標(biāo)記const的表，這樣不好，并且在其他情況下很差。通常可以增加更多的索引來避免使用ALL連接。

possible_ keys： 指出MySQL能使用哪個(gè)索引在該表中找到行。如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查WHERE子句看它是否引用某些列或適合索引的列來提高查詢性能。如果是這樣，可以創(chuàng)建適合的索引來提高查詢的性能。

key. 表示查詢實(shí)際使用到的索引，如果沒有選擇索引，該列的值是NULL要想強(qiáng)制MySQL使用或忽視possible_ keys 列中的索引，在查詢中使用FORCE INDEX. USE INDEX或者IGNORE INDEX.參見SELECT語(yǔ)法。

key_len：表示MySQL選擇的索引字段按字節(jié)計(jì)算的長(zhǎng)度，如果鍵是NULL，則長(zhǎng)度為NULL。注意通過key_ len 值可以確定MySQL將實(shí)際使用一個(gè)多列索引中的幾個(gè)字段。

ref： 表示使用哪個(gè)列或常數(shù)與索引一起來查詢記錄。

rows：顯示MySQL在表中進(jìn)行查詢時(shí)必須檢查的行數(shù)。

Extra：表示MySQL在處理查詢時(shí)的詳細(xì)信息。

DESCRIBE語(yǔ)句的使用方法與EXPLAIN 語(yǔ)句是一樣的，并且分析結(jié)果也是一樣的。DESCRIBE語(yǔ)句的語(yǔ)法形式如下：

DESCRIBE SELECT select_ options

DESCRIBE可以縮寫成DESC。

索引對(duì)查詢速度的影響

MySQL中提高性能的一個(gè)最有效的方式就是對(duì)數(shù)據(jù)表設(shè)計(jì)合理的索引。索引提供了高效訪問數(shù)據(jù)的方法，并且加快查詢的速度，因此，索引對(duì)查詢的速度有著至關(guān)重要的影響。使用索引可以快速地定位表中的某條記錄，從而提高數(shù)據(jù)庫(kù)查詢的速度，提高數(shù)據(jù)庫(kù)的性能。

如果查詢時(shí)沒有使用索引，查詢語(yǔ)句將掃描表中的所有記錄。在數(shù)據(jù)量大的情況下，這樣查詢的速度會(huì)很慢。如果使用索引進(jìn)行查詢，查詢語(yǔ)句可以根據(jù)索引快速定位到待查詢記錄，從而減少查詢的記錄數(shù)，達(dá)到提高查詢速度的目的。

使用索引查詢

索引可以提高查詢的速度。但并不是使用帶有索引的字段查詢時(shí)，索引都會(huì)起作用。使用索引有幾種特殊情況，在這些情況下，有可能使用帶有索引的字段查詢時(shí)，索引并沒有起作用，下面重點(diǎn)介紹這幾種特殊情況：

使用LIKE關(guān)鍵字的查詢語(yǔ)句

使用多列索引的查詢語(yǔ)句

使用OR關(guān)鍵字的查詢語(yǔ)句

優(yōu)化子查詢

MySQL從4.1版本開始支持子查詢，使用子查詢可以進(jìn)行SELECT語(yǔ)句的嵌套查詢，即一個(gè)SELECT查詢的結(jié)果作為另一個(gè)SELECT語(yǔ)句的條件。子查詢可以一次性完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作。子查詢雖然可以使查詢語(yǔ)句很靈活，但執(zhí)行效率不高。

執(zhí)行子查詢時(shí)，MySQL需要為內(nèi)層查詢語(yǔ)句的查詢結(jié)果建立一個(gè)臨時(shí)表。然后外層查詢語(yǔ)句從臨時(shí)表中查詢記錄。查詢完畢后，再撤銷這些臨時(shí)表。因此，子查詢的速度會(huì)受到一定的影響。如果查詢的數(shù)據(jù)量比較大，這種影響就會(huì)隨之增大。

在MySQL中，可以使用連接（JOIN） 查詢來替代子查詢。連接查詢不需要建立臨時(shí)表，其速度比子查詢要快，如果查詢中使用索引的話，性能會(huì)更好。連接之所以更有效率，是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來完成查詢工作。