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

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

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

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

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

SHOW STATUS LIKE 'value'；

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

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

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

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

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

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

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

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

優(yōu)化查詢

分析語句查詢

在MySQL中，可以使用EXPLAIN語句和DESCRIBE語句來分析查詢語句。

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

EXPLAIN SELECT語句；

EXPLAIN|DESCRIBE SELECT * FROM timeinfo；

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

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

索引對查詢速度的影響

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

使用索引查詢

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

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

如果匹配字符串中，第一個字符為百分號“%”時，索引不會被使用，如果“%”所在匹配字符串中的位置不是第一位置，則索引會被正常使用。

2.查詢語句中使用多列索引

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

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

CREATE INDEX index_student_info ON studentinfo（name, sex）；

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

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

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

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

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

將字段很多的表分解成多個表

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

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

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

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

增加中間表 

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

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

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

CREATE TABLE temp_score（

id INT NOT NULL，

Name VARCHAR（20）NOT NULL，

grade FLOAT）；

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

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é)號、姓名和成績。這樣就省去了每次查詢時進(jìn)行表連接，從而提高數(shù)據(jù)庫的查詢速度。

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

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

1.禁用索引

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

ALTER TABLE 表名 DISABLE KEYS；

重新開啟索引的語句如下：

ALTER TABLE 表名 ENABLE KEYS；

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

2.禁用唯一性檢查

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

SET UNIQUE_CHECKS=0；

重新開啟唯一性檢查的語句如下：

SET UNIQUE_CHECKS=1；

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

插入多條記錄時，可以采取兩種寫INSERT語句的方式。第一種是一個INSERT語句插入多條記錄。INSERT語句的情形如下：

INSERT INTO food VALUES

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

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

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

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

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ù)庫之間的連接等操作，其速度比第二種方式要快。

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

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

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

1.分析表

MySQL中使用ANALYZE TABLE語句來分析表，該語句的基本語法如下：

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

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

詳細(xì)介紹如下：

Table：表示表的名稱；

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

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

Msg_text：顯示信息。

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

2.檢查表

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

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

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

3.優(yōu)化表

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

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

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

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

查詢高速緩存

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

檢驗高速緩存是否開啟 

SHOW VARIABLES LIKE'%query_cache%'；

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

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時，表明查詢高速緩存已經(jīng)關(guān)閉；當(dāng)該值為1或ON時，表明高速緩存已經(jīng)打開；其值為2或DEMAND時，表明要根據(jù)需要運行有SQL_CACHE選項的SELECT語句，提供查詢高速緩存。

使用高速緩存

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

SELECT SQL_CACHE * FROM 表名；

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

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