abstrait：性能優(yōu)化是通過(guò)某些有效的方法提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能。性能優(yōu)化的目的是為了是MySQL數(shù)據(jù)運(yùn)行速度更快、占用的磁盤空間更小。性能優(yōu)化包括很多方面，例如優(yōu)化查詢速度、優(yōu)化更新速度和優(yōu)化MySQL服務(wù)器等。MySQL數(shù)據(jù)庫(kù)的用戶和數(shù)據(jù)非常少的時(shí)候，很難判斷一個(gè)MySQL數(shù)據(jù)庫(kù)的性能的好壞。只有當(dāng)長(zhǎng)時(shí)間運(yùn)行，并且有大量用戶進(jìn)行頻繁操作時(shí)，MySQL數(shù)據(jù)庫(kù)的性能才能體現(xiàn)出來(lái)。例如，一個(gè)每天有幾萬(wàn)用戶同時(shí)

性能優(yōu)化是通過(guò)某些有效的方法提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能。性能優(yōu)化的目的是為了是MySQL數(shù)據(jù)運(yùn)行速度更快、占用的磁盤空間更小。性能優(yōu)化包括很多方面，例如優(yōu)化查詢速度、優(yōu)化更新速度和優(yōu)化MySQL服務(wù)器等。

MySQL數(shù)據(jù)庫(kù)的用戶和數(shù)據(jù)非常少的時(shí)候，很難判斷一個(gè)MySQL數(shù)據(jù)庫(kù)的性能的好壞。只有當(dāng)長(zhǎng)時(shí)間運(yùn)行，并且有大量用戶進(jìn)行頻繁操作時(shí)，MySQL數(shù)據(jù)庫(kù)的性能才能體現(xiàn)出來(lái)。例如，一個(gè)每天有幾萬(wàn)用戶同時(shí)在線的大型網(wǎng)站的數(shù)據(jù)庫(kù)性能的優(yōu)劣就很明顯。這么多用戶在同時(shí)連接MySQL數(shù)據(jù)庫(kù)，并且進(jìn)行查詢、插入和更新的操作。如果MySQL數(shù)據(jù)庫(kù)的性能很差，很可能無(wú)法承受如此多用戶同時(shí)操作。試想用戶查詢一條記錄需要花費(fèi)很長(zhǎng)時(shí)間，用戶很難會(huì)喜歡這個(gè)網(wǎng)站。

因此，為了提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能，需要進(jìn)行一系列的優(yōu)化措施。如果MySQL數(shù)據(jù)庫(kù)需要進(jìn)行大量的查詢操作，那么就需要對(duì)查詢語(yǔ)句進(jìn)行優(yōu)化。對(duì)于耗費(fèi)時(shí)間的查詢語(yǔ)句進(jìn)行優(yōu)化，可以提高整體的查詢速度。如果連接MySQL數(shù)據(jù)庫(kù)用戶很多，那么就需要對(duì)MySQL服務(wù)器進(jìn)行優(yōu)化。否則，大量的用戶同時(shí)連接MySQL數(shù)據(jù)庫(kù)，可能會(huì)造成數(shù)據(jù)庫(kù)系統(tǒng)崩潰。

數(shù)據(jù)庫(kù)管理員可以使用SHOW STATUS語(yǔ)句查詢MySQL數(shù)據(jù)庫(kù)的性能,通過(guò)這些參數(shù)可以分析MySQL數(shù)據(jù)庫(kù)性能，然后根據(jù)分析結(jié)果，進(jìn)行相應(yīng)的性能優(yōu)化。語(yǔ)法形式如下：

SHOW STATUS LIKE 'value'；

其中，value參數(shù)是常用的幾個(gè)統(tǒng)計(jì)參數(shù)。這些常用參數(shù)介紹如下。

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

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

Slow_queries：慢查詢的次數(shù)；

Com_select：查詢操作的次數(shù)；

Com_insert：插入操作的次數(shù)；

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

ps:MySQL中存在查詢InnoDB類型的表的一些參數(shù)。例如，Innodb_rows_read參數(shù)表示SELECT語(yǔ)句查詢的記錄數(shù)；Innodb_rows_inserted參數(shù)表示INSERT語(yǔ)句插入的記錄數(shù)；Innodb_rows_updated參數(shù)表示UPDATE語(yǔ)句更新的記錄數(shù)；Innodb_rows_deleted參數(shù)表示DELETE語(yǔ)句刪除的記錄數(shù)。

優(yōu)化查詢

分析語(yǔ)句查詢

在MySQL中，可以使用EXPLAIN語(yǔ)句和DESCRIBE語(yǔ)句來(lái)分析查詢語(yǔ)句。

應(yīng)用EXPLAIN關(guān)鍵字分析查詢語(yǔ)句，其語(yǔ)法結(jié)構(gòu)如下：

EXPLAIN SELECT語(yǔ)句；

EXPLAIN|DESCRIBE SELECT * FROM timeinfo；

其中，各字段所代表的意義如下所示：

id列：指定在整個(gè)查詢中SELECT的位置。
table列：存放查詢的表名。
type列：連接類型，該列中存儲(chǔ)很多值，范圍從const到ALL。
possible_keys列：指定為了提高查找速度，在MySQL中可以使用的索引。
key列：指定實(shí)際使用的鍵。
rows列：指定MySQL需要在相應(yīng)表中返回查詢結(jié)果所檢驗(yàn)的行數(shù)，為了得到該總行數(shù)，MySQL必須掃描處理整個(gè)查詢，再乘以每個(gè)表的行值。
Extra列：包含一些其他信息，設(shè)計(jì)MySQL如何處理查詢。

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

在查詢過(guò)程中使用索引，勢(shì)必會(huì)提高數(shù)據(jù)庫(kù)查詢效率，應(yīng)用索引來(lái)查詢數(shù)據(jù)庫(kù)中的內(nèi)容，可以減少查詢的記錄數(shù)，從而達(dá)到優(yōu)化查詢的目的。

使用索引查詢

在MySQL中，索引可以提高查詢的速度，但并不能充分發(fā)揮其作用，所以在應(yīng)用索引查詢時(shí)，也可以通過(guò)關(guān)鍵字或其他方式來(lái)對(duì)查詢進(jìn)行優(yōu)化處理。

1.應(yīng)用LIKE關(guān)鍵字優(yōu)化索引查詢

如果匹配字符串中，第一個(gè)字符為百分號(hào)“%”時(shí)，索引不會(huì)被使用，如果“%”所在匹配字符串中的位置不是第一位置，則索引會(huì)被正常使用。

2.查詢語(yǔ)句中使用多列索引

多列索引是指在表的多個(gè)字段上創(chuàng)建一個(gè)索引，當(dāng)且僅當(dāng)只有查詢條件中使用了這些字段中的一個(gè)字段時(shí)，索引才會(huì)被正常使用。

應(yīng)用多列索引在表的多個(gè)字段中創(chuàng)建一個(gè)索引，其命令如下：

CREATE INDEX index_student_info ON studentinfo（name, sex）；

ps:在應(yīng)用sex字段時(shí)，索引不能被正常使用。這就意味著索引并未在MySQL優(yōu)化中起到任何作用，故必須使用第一字段name時(shí)，索引才可以被正常使用。

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

在MySQL中，查詢語(yǔ)句只有包含OR關(guān)鍵字時(shí)，要求查詢的兩個(gè)字段必須同為索引，如果所搜索的條件中，有一個(gè)字段不為索引，則在查詢中不會(huì)應(yīng)用索引進(jìn)行查詢。其中，應(yīng)用OR關(guān)鍵字查詢索引的命令如下： SELECT * FROM studentinfo WHERE name='Chris' or sex='M'；

優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)

數(shù)據(jù)庫(kù)結(jié)構(gòu)是否合理，需要考慮是否存在冗余、對(duì)表的查詢和更新的速度、表中字段的數(shù)據(jù)類型是否合理等多方面的內(nèi)容。

將字段很多的表分解成多個(gè)表

有些表在設(shè)計(jì)時(shí)設(shè)置了很多的字段。這個(gè)表中有些字段的使用頻率很低。當(dāng)這個(gè)表的數(shù)據(jù)量很大時(shí)，查詢數(shù)據(jù)的速度就會(huì)很慢。對(duì)于這種字段特別多且有些字段的使用頻率很低的表，可以將其分解成多個(gè)表。

學(xué)生表中有很多字段，其中在extra字段中存儲(chǔ)著學(xué)生的備注信息。有些備注信息的內(nèi)容特別多，但是，備注信息很少使用。這樣就可以分解出另外一個(gè)表。將這個(gè)取名為student_extra的表中存儲(chǔ)兩個(gè)字段，分別為id和extra。其中，id字段為學(xué)生的學(xué)號(hào)，extra字段存儲(chǔ)備注信息。如果需要查詢某個(gè)學(xué)生的備注信息，可以用學(xué)號(hào)（id）來(lái)查詢。如果需要將學(xué)生的學(xué)籍信息與備注信息同時(shí)顯示時(shí)，可以將student表和student_extra表進(jìn)行聯(lián)表查詢，查詢語(yǔ)句如下：

SELECT*FROM student, student_extra WHERE student.id=student_extra.id；

通過(guò)這種分解，可以提高student表的查詢效率。因此，遇到這種字段很多，而且有些字段使用不頻繁的，可以通過(guò)這種分解的方式來(lái)優(yōu)化數(shù)據(jù)庫(kù)的性能。

增加中間表 

有時(shí)需要經(jīng)常查詢某兩個(gè)表中的幾個(gè)字段。如果經(jīng)常進(jìn)行聯(lián)表查詢，會(huì)降低MySQL數(shù)據(jù)庫(kù)的查詢速度。對(duì)于這種情況，可以建立中間表來(lái)提高查詢速度。

先分析經(jīng)常需要同時(shí)查詢哪幾個(gè)表中的哪些字段，然后將這些字段建立一個(gè)中間表，并將原來(lái)那幾個(gè)表的數(shù)據(jù)插入到中間表中，之后就可以使用中間表來(lái)進(jìn)行查詢和統(tǒng)計(jì)。

實(shí)際中經(jīng)常要查學(xué)生的學(xué)號(hào)、姓名和成績(jī)。根據(jù)這種情況可以創(chuàng)建一個(gè)temp_score表。temp_score表中存儲(chǔ)3個(gè)字段，分別是id、name和grade。CREATE語(yǔ)句執(zhí)行如下：

CREATE TABLE temp_score（

id INT NOT NULL，

Name VARCHAR（20）NOT NULL，

grade FLOAT）；

然后從student表和score表中將記錄導(dǎo)入到temp_score表中。INSERT語(yǔ)句如下：

INSERT INTO temp_score SELECT student.id, student.name, score.grade

FROM student, score WHERE student.id=score.stu_id；

將這些數(shù)據(jù)插入到temp_score表中以后，可以直接從temp_score表中查詢學(xué)生的學(xué)號(hào)、姓名和成績(jī)。這樣就省去了每次查詢時(shí)進(jìn)行表連接，從而提高數(shù)據(jù)庫(kù)的查詢速度。

優(yōu)化插入記錄的速度 

插入記錄時(shí)，索引、唯一性校驗(yàn)都會(huì)影響到插入記錄的速度；而且一次插入多條記錄和多次插入記錄所耗費(fèi)的時(shí)間是不一樣的。根據(jù)這些情況，分別進(jìn)行不同的優(yōu)化。

1.禁用索引

插入記錄時(shí)，MySQL會(huì)根據(jù)表的索引對(duì)插入的記錄進(jìn)行排序。如果插入大量數(shù)據(jù)時(shí)，這些排序會(huì)降低插入記錄的速度。為了解決這種情況，在插入記錄之前先禁用索引，等到記錄都插入完畢后再開啟索引。禁用索引的語(yǔ)句如下：

ALTER TABLE 表名 DISABLE KEYS；

重新開啟索引的語(yǔ)句如下：

ALTER TABLE 表名 ENABLE KEYS；

對(duì)于新創(chuàng)建的表，可以先不創(chuàng)建索引，等到記錄都導(dǎo)入以后再創(chuàng)建索引，這樣可以提高導(dǎo)入數(shù)據(jù)的速度。

2.禁用唯一性檢查

插入數(shù)據(jù)時(shí)，MySQL會(huì)對(duì)插入的記錄進(jìn)行校驗(yàn)。這種校驗(yàn)也會(huì)降低插入記錄的速度，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢后再開啟。禁用唯一性檢查的語(yǔ)句如下：

SET UNIQUE_CHECKS=0；

重新開啟唯一性檢查的語(yǔ)句如下：

SET UNIQUE_CHECKS=1；

3.優(yōu)化INSERT語(yǔ)句

插入多條記錄時(shí)，可以采取兩種寫INSERT語(yǔ)句的方式。第一種是一個(gè)INSERT語(yǔ)句插入多條記錄。INSERT語(yǔ)句的情形如下：

INSERT INTO food VALUES

（NULL，'果凍'，'CC果凍廠'，1.8，'2009'，'北京'），

（NULL，'咖啡'，'CF咖啡廠'，25，'2010'，'天津'），

（NULL，'奶糖'，'旺仔奶糖'，15，'2011'，'廣東'）；

第二種是一個(gè)INSERT語(yǔ)句只插入一條記錄，執(zhí)行多個(gè)INSERT語(yǔ)句來(lái)插入多條記錄。INSERT語(yǔ)句的情形如下：

INSERT INTO food VALUES（NULL，'果凍'，'CC果凍廠'，1.8，'2009'，'北京'）；

INSERT INTO food VALUES（NULL，'咖啡'，'CF咖啡廠'，25，'2010'，'天津'）；

INSERT INTO food VALUES（NULL，'奶糖'，'旺仔奶糖'，15，'2011'，'廣東'）；

第一種方式減少了與數(shù)據(jù)庫(kù)之間的連接等操作，其速度比第二種方式要快。

ps:當(dāng)插入大量數(shù)據(jù)時(shí)，建議使用一個(gè)INSERT語(yǔ)句插入多條記錄的方式；而且如果能用LOAD DATA INFILE語(yǔ)句，就盡量用LOAD DATA INFILE語(yǔ)句，因?yàn)長(zhǎng)OAD DATA INFILE語(yǔ)句導(dǎo)入數(shù)據(jù)的速度比INSERT語(yǔ)句的速度快。

分析表、檢查表和優(yōu)化表 

分析表主要作用是分析關(guān)鍵字的分布；檢查表主要作用是檢查表是否存在錯(cuò)誤；優(yōu)化表主要作用是消除刪除或者更新造成的空間浪費(fèi)。

1.分析表

MySQL中使用ANALYZE TABLE語(yǔ)句來(lái)分析表，該語(yǔ)句的基本語(yǔ)法如下：

analyze table 表名1，表名2……；

使用analyze table分析表的過(guò)程中，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)對(duì)表加一個(gè)只讀鎖，在分析期間，只能讀取表中的記錄，不能更新和插入記錄。ANALYZE TABLE語(yǔ)句能夠分析InnoDB和MyISAM類型的表。

詳細(xì)介紹如下：

Table：表示表的名稱；

Op：表示執(zhí)行的操作。analyze表示進(jìn)行分析操作；check表示進(jìn)行檢查查找；optimize表示進(jìn)行優(yōu)化操作；

Msg_type：表示信息類型，其顯示的值通常是狀態(tài)、警告、錯(cuò)誤和信息這四者之一；

Msg_text：顯示信息。

檢查表和優(yōu)化表之后也會(huì)出現(xiàn)這4列信息。

2.檢查表

MySQL中使用CHECK TABLE語(yǔ)句來(lái)檢查表。CHECK TABLE語(yǔ)句能夠檢查InnoDB和MyISAM類型的表是否存在錯(cuò)誤；而且，該語(yǔ)句還可以檢查視圖是否存在錯(cuò)誤。該語(yǔ)句的基本語(yǔ)法如下：

check table 表名1，表名2…… [option]；

其中，option參數(shù)有5個(gè)參數(shù)，分別是QUICK、FAST、CHANGED、MEDIUM和EXTENDED。這5個(gè)參數(shù)的執(zhí)行效率依次降低。option選項(xiàng)只對(duì)MyISAM類型的表有效，對(duì)InnoDB類型的表無(wú)效。CHECK TABLE語(yǔ)句在執(zhí)行過(guò)程中也會(huì)給表加上只讀鎖。

3.優(yōu)化表

MySQL中使用optimize table語(yǔ)句來(lái)優(yōu)化表。該語(yǔ)句對(duì)InnoDB和MyISAM類型的表都有效。但是，optimize table只能優(yōu)化表中的VARCHAR、BLOB或TEXT類型的字段。optimize table語(yǔ)句的基本語(yǔ)法如下：

optimize table 表名1，表名2……；

通過(guò)optimize table語(yǔ)句可以消除刪除和更新造成的磁盤碎片，從而減少空間的浪費(fèi)。OPTIMIZE TABLE語(yǔ)句在執(zhí)行過(guò)程中也會(huì)給表加上只讀鎖。

ps:如果一個(gè)表使用了TEXT或者BLOB這樣的數(shù)據(jù)類型，那么更新、刪除等操作就會(huì)造成磁盤空間的浪費(fèi)，因?yàn)?，更新和刪除操作后，以前分配的磁盤空間不會(huì)自動(dòng)收回。使用optimize table語(yǔ)句就可以將這些磁盤碎片整理出來(lái)，以便以后再利用。

查詢高速緩存

在MySQL中，用戶通過(guò)SELECT語(yǔ)句查詢數(shù)據(jù)時(shí)，該操作將結(jié)果集保存到一個(gè)特殊的高級(jí)緩存中，從而實(shí)現(xiàn)查詢操作。首次查詢后，當(dāng)用戶再次做相同查詢操作時(shí)，MySQL即可從高速緩存中檢索結(jié)果。這樣一來(lái)，既提高了查詢效率，同樣起到優(yōu)化查詢的作用。 

檢驗(yàn)高速緩存是否開啟 

SHOW VARIABLES LIKE'%query_cache%'；

主要的參數(shù)進(jìn)行說(shuō)明：

have_query_cache：表明服務(wù)器在默認(rèn)安裝條件下，是否已經(jīng)配置查詢高速緩存。

query_cache_size：高速緩存分配空間，如果該空間為86，則證明分配給高速緩存空間的大小為86MB。如果該值為0，則表明查詢高速緩存已經(jīng)關(guān)閉。

query_cache_type：判斷高速緩存開啟狀態(tài)，其變量值范圍為0～2。其中，當(dāng)該值為0或OFF時(shí)，表明查詢高速緩存已經(jīng)關(guān)閉；當(dāng)該值為1或ON時(shí)，表明高速緩存已經(jīng)打開；其值為2或DEMAND時(shí)，表明要根據(jù)需要運(yùn)行有SQL_CACHE選項(xiàng)的SELECT語(yǔ)句，提供查詢高速緩存。

使用高速緩存

在MySQL中，查詢高速緩存的具體語(yǔ)法結(jié)構(gòu)如下：

SELECT SQL_CACHE * FROM 表名；

 如果經(jīng)常運(yùn)行查詢高速緩存，將會(huì)提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能。

一旦表有變化，查詢這個(gè)表的高速緩存將會(huì)失效，且將從高速緩存中刪除。這樣防止查詢從舊表中返回?zé)o效數(shù)據(jù)。另外，不使用高速緩存查找可以應(yīng)用SQL_NO_CACHE關(guān)鍵字。