優化，優化網站

優化，優化網站

原文鏈接地址：http://www.cnblogs.com/lyhabc/p/3886402.html

一步一步走來已經寫到了第十六篇了~

這一篇主要介紹MYSQL的優化，優化MYSQL數據庫是DBA和開發人員的必備技能

MYSQL優化一方面是找出系統瓶頸，提高MYSQL數據庫整體性能；另一方面需要合理的結構設計和參數調整，以提高

用戶操作響應的速度；同時還有盡可能節省系統資源，以便系統可以提供更大負荷的服務

如果大家看過我寫的兩篇文章，那麼學習MYSQL的索引就不會太難，因為是相通的

SQLSERVER聚集索引與非聚集索引的再次研究（上）

SQLSERVER聚集索引與非聚集索引的再次研究（下）

其實MYSQL也有SQLSERVER堆表的概念

myisam允許沒有任何索引和主鍵的表存在，個人覺得沒有主鍵的myisam表都屬於堆表，因為MYSQL不支持非主鍵的聚集索引。

innodb引擎如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6字節的主鍵(用戶不可見)

詳細參考：MyISAM vs InnoDB：MySQL存儲引擎詳解

不過《MyISAM vs InnoDB：MySQL存儲引擎詳解》文章也有一點錯誤，意向共享鎖就是表鎖，其實是不對的

1、優化簡介

mysql優化是多方面的，原則是減少系統的瓶頸，減少資源的占用，增加系統的反應速度。

例如，通過優化文件系統，提高磁盤I/O的讀寫速度；通過優化操作系統調度策略，提高mysql在高負荷情況下

的負載能力；優化表結構、索引、查詢語句等使查詢響應更快

在mysql中，可以使用show status語句查詢一些mysql的性能參數

show status like 'value';

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

connections：連接mysql服務器的次數

uptime：mysql服務器的上線時間

slow_queries：慢查詢的次數

com_select:查詢操作次數

com_insert:插入操作次數

com_update:更新操作次數

com_delete:刪除操作次數

如果查詢mysql服務器的連接次數，可以執行如下語句

show status like 'connections';

如果查詢mysql服務器的慢查詢次數，可以執行如下語句

show status like 'slow_queries';

2、優化查詢

查詢是數據庫最頻繁的操作，提高查詢速度可以有效地提高mysql數據庫的性能

（1）分析查詢語句

通過對查詢語句的分析，可以了解查詢語句的執行情況找出查詢語句執行的瓶頸

mysql中提供了EXPLAIN語句和DESCRIBE語句，用來分析查詢語句

EXPLAIN語句的基本語法

EXPLAIN [EXTENDED] SELECT SELECT_OPTION

使用EXTENDED關鍵字，EXPLAIN語句將產生附加信息。SELECT_OPTION是SELECT 語句的查詢選項，包括FROM WHERE子句等

執行該語句，可以分析EXPLAIN後面的select語句的執行情況，並且能夠分析所查詢的表的一些特征

使用EXPLAIN語句來分析1個查詢語句

USE TEST;
EXPLAIN EXTENDED SELECT * FROM PERSON;

下面對結果進行解釋

· id

SELECT識別符。這是SELECT的查詢序列號。

· select_type

SELECT類型，可以為以下任何一種：

SIMPLE：簡單SELECT(不使用UNION或子查詢)

PRIMARY：表示主查詢，或者是最外層的查詢語句（多表連接的時候）

UNION：表示連接查詢的第二個或後面的查詢語句

DEPENDENT UNION：UNION連接查詢中的第二個或後面的SELECT語句，取決於外面的查詢

UNION RESULT：UNION連接查詢的結果

SUBQUERY：子查詢中的第一個SELECT語句

DEPENDENT SUBQUERY：子查詢中的第一個SELECT語句，取決於外面的查詢

DERIVED：導出表的SELECT(FROM子句的子查詢)

· table

表示查詢的表

· type

表示表的聯接類型

下面給出各種聯接類型，按照從最佳類型到最壞類型進行排序：

（1）system

表僅有一行(=系統表)。這是const聯接類型的一個特例。

（2）const

表最多只有一個匹配行，它將在查詢開始時被讀取。余下的查詢優化中被作為常量對待。const表查詢速度很快，因為它們只讀取一次。

const用於常數值比較PRIMARY KEY或UNIQUE索引的所有部分的場合。

在下面的查詢中，tbl_name可以用於const表：

SELECT * from tbl_name WHERE primary_key=1;SELECT * from tbl_name
WHERE primary_key_part1=1和 primary_key_part2=2;

（3）eq_ref

對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。

它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY時。

eq_ref可以用於使用“=” 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式。

在下面的例子中，MySQL可以使用eq_ref聯接來處理ref_tables：

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
 
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
 AND ref_table.key_column_part2=1;

（4）ref

對於每個來自於前面的表的任意行組合，將從該表中讀取所有匹配的行。

如果聯接只使用索引鍵的最左邊的前綴，或如果索引鍵不是UNIQUE或PRIMARY KEY，則使用ref。

如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。

ref可以用於使用=或<=>操作符的帶索引的列。

在下面的例子中，MySQL可以使用ref聯接來處理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr;
 
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
 
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

（5）ref_or_null

該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行，在解決子查詢中經常使用該聯接類型的優化。

在下面的例子中，MySQL可以使用ref_or_null聯接來處理ref_tables：

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

（6） index_merge

該聯接類型表示使用了索引合並優化方法。在這種情況下，key列包含了所用到的索引的清單，key_len列包含了所用到的索引的最長長度。

（7） unique_subquery

該類型替換了下面形式的IN子查詢的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery是一個索引查找類型，可以完全替換子查詢，效率更高。

（8） index_subquery

該聯接類型類似於unique_subquery，不過索引類型不需要是唯一索引，可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

（9） range

只檢索給定范圍的行，使用一個索引來檢索行數據。key列顯示使用了哪個索引，key_len顯示所使用索引的長度。

在該類型中ref列為NULL。

當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，類型為range。

下面介紹幾種檢索指定行數據的情況

SELECT * FROM tbl_name
WHERE key_column = 10;
 
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
 
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
 
SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);

（10）  index

該聯接類型與ALL相同，除了掃描索引樹。其他情況都比ALL快，因為索引文件通常比數據文件小。

當查詢只使用作為單索引一部分的列時，MySQL可以使用該聯接類型。

（11）   ALL

對於每個來自於先前的表的行組合，進行完整的表掃描。

如果第一個表沒標記為const，這樣執行計劃就不會很好。

通常可以增加更多的索引來擺脫ALL，使得行能基於前面的表中的常數值或列值被檢索出。

possible_keys

possible_keys列指出MySQL能供給使用的索引鍵有哪些。注意，該列完全獨立於EXPLAIN輸出所示的表的次序。

這意味著在possible_keys中的某些索引鍵實際上不能按生成的表次序使用。

如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句查看是否可以引用某些列或適合的索引列來提高查詢性能。

如果是這樣，創造一個適當的索引並且再次用EXPLAIN檢查查詢。

如果要查詢一張表有什麼索引，可以使用

SHOW INDEX FROM tbl_name

key

key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，那麼可能列的值是NULL。

要想強制MySQL使用或忽略possible_keys列中的索引，在查詢中可以使用

FORCE INDEX  -- 強逼使用某個索引

USE INDEX --使用某個索引

IGNORE INDEX -- 忽略某個索引

對於MyISAM引擎和BDB引擎的表，運行 ANALYZE TABLE 可以幫助優化器選擇更好的索引。

對於MyISAM表，可以使用myisamchk --analyze。

key_len

key_len列顯示MySQL決定使用的索引鍵的長度（按字節計算）。如果鍵是NULL，則長度為NULL。

注意通過key_len值我們可以確定MySQL將實際使用一個多索引鍵索引的幾個字段。

ref

ref列顯示使用哪個列或常數與索引一起查詢記錄。

rows

rows列顯示MySQL預估執行查詢時必須要檢索的行數。

Extra

該列包含MySQL處理查詢時的詳細信息。下面解釋了該列可以顯示的不同的文本字符串：

Distinct

MySQL發現第1個匹配行後，停止為當前的行組合搜索更多的行。

Not exists

MySQL能夠對查詢進行LEFT JOIN優化，發現1個匹配LEFT JOIN標准的行後，不再為前面的的行組合在該表內檢查更多的行。

下面是一個可以這樣優化的查詢類型的例子：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;

假定t2.id定義為NOT NULL。在這種情況下，MySQL使用t1.id的值掃描t1並查找t2中的行。

如果MySQL在t2中發現一個匹配的行，它知道t2.id絕不會為NULL，並且不再掃描t2內有相同的id值的行。換句話說，對於t1的每個行，MySQL只需要在t2中查找一次，無論t2內實際有多少匹配的行。

range checked for each record (index map: #)

MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。

對前面的表的每個行組合，MySQL檢查是否可以使用range或index_merge訪問方法來獲取行。

這並不很快，但比執行沒有索引的聯接要快得多。

可以參考一下這篇文章：一個用戶SQL慢查詢分析，原因及優化

裡面就提到了range checked for each record

Using filesort

MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。

通過根據聯接類型浏覽所有行並為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。

然後關鍵字被排序，並按排序順序檢索行

如果是order by操作就會用到這個Using filesort，當然filesort不是指使用文件來排序，大家不要誤會了。。。

Using index

從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。當查詢只使用作為單一索引一部分的列時，可以使用該策略。

Using temporary

為了解決查詢，MySQL需要創建一個臨時表來容納結果。

典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。

一般用到臨時表都會看到 Using temporary

Using where

WHERE子句用於限制哪一個行匹配下一個表或發送到客戶端。

除非你專門從表中索取或檢查所有行，如果Extra值不為Using where並且表聯接類型為ALL或index，查詢可能會有一些錯誤。

Using index for group-by

類似於訪問表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，

而不要額外搜索硬盤訪問實際的表。並且，按最有效的方式使用索引，以便對於每個組，只讀取少量索引條目。

DESCIBE語句的使用方法與EXPLAIN語句是一樣的，並且分享結果也是一樣的DESCIBE語句的語法如下

DESCRIBE SELECT select_options

DESCIBE可以縮寫成DESC

（2）索引對查詢速度的影響

mysql中提高性能的一個最有效的方式就是對數據表設計合理的索引。索引提供了高效訪問數據的方法，並且加快查詢速度

因此索引對查詢速度有著至關重要的影響。

如果查詢沒有索引，查詢語句將掃描表中所有記錄。在數據量大的情況下，這樣查詢的速度會很慢。如果使用索引進行查詢，

查詢語句可以根據索引快速定位到待查詢記錄，從而減少查詢的記錄數，達到提高查詢速度的目的。

下面是查詢語句中不使用索引和使用索引的對比，首先分析未使用索引的查詢情況，EXPLAIN語句執行如下

EXPLAIN SELECT `ID`,`name` FROM `test`.`emp` WHERE `name` ='nihao'

可以看到，rows列的值是3說“SELECT `ID`,`name` FROM `test`.`emp` WHERE `name` ='nihao'” 語句掃描了表中的3條記錄

然後在emp表加上索引

CREATE INDEX ix_emp_name ON emp(name)

現在再分析上面的查詢語句，執行的EXPLAIN語句結果如下

結果顯示，rows列的值為1。這表示這個查詢語句只掃描了表中的一條記錄，其他查詢速度自然比掃描3條記錄快。

而且possible_keys 和key的值都是ix_emp_name ，這說明查詢時使用了ix_emp_name 索引

如果表中記錄有100條、1000條、10000條優勢就顯現出來了

（3）使用索引查詢

索引可以提高查詢速度，但並不是使用帶有索引的字段查詢時，索引都會起作用。

下面的幾種情況跟跟SQLSERVER一樣，有可能用不到索引

（1）使用like關鍵字的查詢語句

使用like關鍵字進行查詢的時候，如果匹配字符串的第一個字符為“%”，索引不起作用。只有“%”不在第一個位置，索引

才會起作用

使用like關鍵字，並且匹配字符串中含有“%”字符，EXPLAIN語句如下

USE test;
EXPLAIN SELECT * FROM `test`.`emp` WHERE `name` LIKE '%x';

USE test;
EXPLAIN SELECT * FROM `test`.`emp` WHERE `name` LIKE 'x%';

name上有索引ix_emp_name

第一個查詢type為ALL，表示要全表掃描

第二個查詢TYPE為index，表示會掃描索引

like 關鍵字是否能利用上索引跟SQLSERVER是一樣的

我之前寫過一篇文章：like語句百分號前置會使用到索引嗎？

（2）使用多列索引的查詢語句

mysql可以為多個字段創建索引。一個索引可以包括16個字段（跟SQLSERVER一樣）對於多列索引，只有查詢條件中使用了

這些字段中的第一個字段時，索引才會被使用，這個字段叫：前導索引或前導列

在表person中name，age字段創建多列索引，驗證多列索引的情況

CREATE INDEX ix_person_name_age ON `person` (name,age)

EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `Name` ='suse'

EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `age` =12

從第一條查詢看出，WHERE `Name` ='suse'的記錄有一條，掃描了一條記錄並且使用了ix_person_name_age 索引

從第二條記錄可以看出，rows列的值為4，說明共掃描了4條記錄，並且key列值為NULL，說明EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `age` =12

語句並沒有使用索引。因為age字段是多列索引的第二個字段，只有查詢條件中使用了name字段才會使用ix_person_name_age 索引

這個跟SQLSERVER是一樣的，詳細請看：SQLSERVER聚集索引與非聚集索引的再次研究（下）

（3）使用OR關鍵字的查詢語句

查詢語句的查詢條件中只有OR關鍵字，而且OR前後的兩個條件中的列都是索引時，查詢中才使用索引，否則，查詢不使用索引

查詢語句使用OR關鍵字的情況

我們再創建一個索引

CREATE INDEX ix_person_age ON `person` (age)

EXPLAIN SELECT Name,Age FROM `person`  WHERE `Name` ='SUSE' OR `job`='SPORTMAN'

EXPLAIN SELECT Name,Age FROM `person`  WHERE   `AGE` =2 OR `Name` ='SUSE' 

大家要注意，這裡跟剛才不一樣，這次我們select的字段只有name和age，而不是select出全部字段

因為並沒有在job這個字段上建立索引，所以第一個查詢使用的是全表掃描

第二個查詢因為name字段和age字段都有索引，那麼mysql可以利用這兩個索引的其中之一，這裡是ix_person_name_age索引來查找記錄

利用索引來查找記錄會快很多

（4）優化子查詢

mysql從4.1版本開始支持子查詢，使用子查詢可以進行SELECT語句的嵌套查詢，即一個SELECT查詢的結果作為另一個SELECT語句的條件

子查詢可以一次性完成很多邏輯需要多個步驟才能完成的SQL操作。子查詢雖然使查詢語句靈活，但是執行效率不高。

執行子查詢時，mysql需要為內層查詢語句結果建立一個臨時表。然後外層查詢語句從臨時表中查詢記錄

查詢完畢後，再撤銷臨時表。因此，子查詢的速度會受到一定影響，如果查詢的數據量特別大，這種影響就會更大。

在mysql中，可以使用連接（join）查詢來代替子查詢。連接查詢不需要建立臨時表，其速度比子查詢快，如果查詢中使用索引的話，性能會更好。

所以很多網上的文章都說盡量使用join來代替子查詢，雖然網上也說mysql5.7對於子查詢有很大的改進，但是如果不是使用mysql5.7還是需要注意的

如果系統中join語句特別多還需要注意修改my.ini或my.cnf文件中的join_buffer_size大小，預防性能問題

---

優化數據庫結構

一個好的數據庫設計方案對於數據庫的性能常常起到事半功倍的效果。

數據庫結構的設計需要考慮數據冗余、查詢和更新速度、字段的數據類型是否合理等多方面

（1）將字段很多的表拆分成多個表

有時候有些字段使用頻率很低或者字段的數據類型比較大，那麼可以考慮垂直拆分的方法，把不常用的字段和大字段拆分出去

（2）增加中間表

對於需要經常聯合查詢的表，可以建立中間表以提高查詢效率。通過建立中間表，把需要經常聯合查詢的數據插入到中間表中，

然後將原來的聯合查詢改為對中間表的查詢，以此來提高查詢效率。

（3）增加冗余字段

設計數據庫表時應盡量遵循范式理論，盡可能減少冗余字段，但是現今存儲硬件越來越便宜，有時候查詢數據的時候需要join多個表

這樣在高峰期間會影響查詢的效率，我們需要反范式而為之，增加一些必要的冗余字段，以空間換時間

需要這樣做會增加開發的工作量和維護量，但是如果能換來可觀的性能提升，這樣做也是值得的

（4）優化插入記錄的速度

插入記錄時，影響插入速度的主要是索引、唯一性校驗、一次插入記錄條數等。

根據實際情況，可以分別進行優化

對於myisam表，常見優化方法如下：

1、禁用索引

對於非空表，插入記錄時，mysql會根據表的索引對插入的記錄建立索引。如果插入大量數據，建立索引會降低插入記錄的速度。

為了解決這個問題，可以在插入記錄之前禁用索引，數據插入完畢後再開啟索引

禁用索引語句如下：

ALTER TABLE table_name DISABLE KEYS ;

其中table_name是禁用索引的表的表名

重新開啟索引語句如下：

ALTER TABLE table_name ENABLE KEYS ;

對於空表批量導入數據，則不需要進行此操作，因為myisam表是在導入數據之後才建立索引！

2、禁用唯一性檢查

插入數據時，mysql會對插入的記錄進行唯一性校驗。這種唯一性校驗也會降低插入記錄的速度。

為了降低這種情況對查詢速度的影響，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢之後再開啟

禁用唯一性檢查的語句如下：

SET UNIQUE_CHECKS=0;

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

SET UNIQUE_CHECKS=1;

3、使用批量插入

插入多條記錄時，可以使用一條INSERT語句插入一條記錄，也可以使用一條INSERT語句插入多條記錄。

第一種情況

INSERT INTO emp(id,name) VALUES (1,'suse');
INSERT INTO emp(id,name) VALUES (2,'lily');
INSERT INTO emp(id,name) VALUES (3,'tom');

第二種情況

INSERT INTO emp(id,name) VALUES (1,'suse'),(2,'lily'),(3,'tom')

第二種情況要比第一種情況要快

4、使用LOAD DATA INFILE批量導入

當需要批量導入數據時，如果能用LOAD DATA INFILE語句，就盡量使用。因為LOAD DATA INFILE語句導入數據的速度比INSERT語句快很多

對於INNODB引擎的表，常見的優化方法如下：

1、禁用唯一性檢查

插入數據時，mysql會對插入的記錄進行唯一性校驗。這種唯一性校驗也會降低插入記錄的速度。

為了降低這種情況對查詢速度的影響，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢之後再開啟

禁用唯一性檢查的語句如下：

SET UNIQUE_CHECKS=0;

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

SET UNIQUE_CHECKS=1;

2、禁用外鍵約束

插入數據之前執行禁止對外鍵的檢查，數據插入完成之後再恢復對外鍵的檢查。禁用外鍵檢查的語句如下：

SET FOREIGN_KEY_CHECKS=0;

恢復對外鍵的檢查語句如下

SET FOREIGN_KEY_CHECKS=1;

3、禁止自動提交

插入數據之前禁止事務的自動提交，數據導入完成之後，執行恢復自動提交操作

或顯式指定事務

USE test;

START TRANSACTION;
INSERT INTO emp(name) VALUES('ming');
INSERT INTO emp(name) VALUES('lily');
commit;

（5）分析表、檢查表、優化表、修復表和CHECKSUM表

mysql提供了分析表、檢查表和優化表的語句

分析表主要是分析關鍵字的分布；

檢查表主要是檢查表是否存在錯誤；

優化表主要是消除刪除或者更新造成的空間浪費

修復表主要對myisam表文件進行修復

CHECKSUM表主要對表數據傳輸前和傳輸後進行比較

1、分析表

mysql中提供了ANALYZE TABLE 語句分析表，ANALYZE TABLE 語句的基本語法如下

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,TBL_NAME]...

LOCAL關鍵字是NO_WRITE_TO_BINLOG關鍵字的別名，二者都是執行過程不寫入二進制日志，tbl_name為分析的表的表名

可以有一個或多個

使用ANALYZE TABLE 分析表的過程中，數據庫系統會自動對表加一個只讀鎖。在分享期間，只能讀取表的記錄，不能更新和插入記錄

ANALYZE TABLE 語句能分析INNODB、BDB和MYISAM類型的表

使用ANALYZE TABLE 來分析emp表，執行語句如下：

ANALYZE TABLE emp;

上面結果顯示說明

table：表示分析的表名

op：表示執行的操作，analyze表示進行分析操作

msg_type：表示信息類型其值通常是狀態（status）、信息（info）、注意（note）、警告（warning）和錯誤（error）之一

msg_text：顯示信息

實際上分析表跟SQLSERVER裡的更新統計信息是差不多的

主要就是為了索引的基數更加准確，從而使查詢優化器能夠更加准確的預估行數

emp表的記錄行數是18

分析表之後，Cardinality 基數更加准確了

2、檢查表

mysql中使用check table語句來檢查表。check table語句能夠檢查innodb和myisam類型的表是否存在錯誤。

對於myisam類型的表，check table語句還會更新關鍵字統計數據。而且，check table也可以檢查視圖是否有錯誤，

比如在視圖定義中被引用的表已不存在。

該語句基本語法如下：

CHECK TABLE TBL_NAME [,tbl_name]...[option]...
option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

其中，tbl_name是表名；option參數有5個取值分別是QUICK、FAST、MEDIUM、EXTENDED、CHANGED

各個選項的意思分別是

QUICK：不掃描行，不檢查錯誤的連接

FAST：只檢查沒有被正確關閉的表

MEDIUM：掃描行，以驗證被刪除的連接是有效的，也可以計算各行的關鍵字校驗和，並使用計算出的校驗和驗證這一點

EXTENDED：對每行的所有關鍵字進行一個全面的關鍵字查找。這可以確保表是100%一致的，但是花的時間較長

CHANGED：只檢查上次檢查後被更改的表和沒有被正確關閉的表

option只對myisam表有效，對innodb表無效。check table語句在執行過程中也會給表加上只讀鎖。

3、優化表

mysql中使用OPTIMIZE TABLE語句來優化表。該語句對INNODB和MYISAM表都有效。但是，OPTIMIZE TABLE語句只能優化表中的

VARCHAR、BLOB、TEXT類型的字段

OPTIMIZE TABLE語句的基本語法如下：

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,TBL_NAME]...

LOCAL和NO_WRITE_TO_BINLOG關鍵字的意義和分析表相同，都是指定不寫入二進制日志

tbl_name是表名

通過OPTIMIZE TABLE語句可以消除刪除和更新造成的文件碎片。

OPTIMIZE TABLE語句在執行過程中也會給表加上只讀鎖。

  
提示：一個表使用了TEXT或者BLOB這樣的數據類型，如果已經刪除了表的一大部分，或者已經對含有可變長度行的表（含有VARCHAR、BLOB或TEXT列的表）

  
進行了很多更新，則應使用OPTIMIZE TABLE來重新利用未使用的空間，並整理數據文件的碎片。在多數設置中，根本不需要運行OPTIMIZE TABLE。

  
即使對可變長度的行進行了大量更新，也不需要經常運行，每周一次或每月一次即可，並且只需要對特定表進行OPTIMIZE TABLE

OPTIMIZE TABLE語句類似於SQLSERVER的重建索引和收縮數據文件的功能

4、修復表

mysql中使用Repair Table來修復myisam表，只對MyISAM和ARCHIVE類型的表有效。

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,tbl_name]...[option]...
option={QUICK|EXTENDED|USE_FRM}

選項的意思分別是：

QUICK：最快的選項，只修復索引樹。
EXTENDED：最慢的選項，需要逐行重建索引。
USE_FRM：只有當MYI文件丟失時才使用這個選項，全面重建整個索引。

與Analyze Table一樣，Repair Table也可以使用local來取消寫入binlog。

5、Checksum 表

數據在傳輸時，可能會發生變化，也有可能因為其它原因損壞，為了保證數據的一致，我們可以計算checksum（校驗值）。

使用MyISAM引擎的表會把checksum存儲起來，稱為live checksum，當數據發生變化時，checksum會相應變化。

語法如下：

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

quick：表示返回存儲的checksum值

extended：表示重新計算checksum

如果沒有指定選項，則默認使用extended。

Checksum 表主要用來對比在傳輸表數據之前和表數據之後，表的數據是否發生了變化，例如插入了數據或者刪除了數據，或者有數據損壞

CHECKSUM值都會改變。

---

優化MYSQL服務器

水電費優化mysql服務器主要從兩個方面入手，一方面是對硬件進行優化；另一方面是對mysql服務器的參數進行優化

1、優化服務器硬件

服務器的硬件性能直接決定著MYSQL數據庫的性能。硬件的性能瓶頸直接決定MYSQL數據庫的運行速度和效率。

優化服務器硬件的幾種方法

（1）配置較大的內存。足夠大的內存，是提高mysql數據庫性能之一。內存速度比磁盤I/O快得多，可以通過增加系統緩沖區容量，使數據庫

在內存停留時間更長，以減少磁盤I/O

（2）配置高速磁盤系統，以減少讀盤等待時間，提高響應速度

（3）合理分布磁盤I/O，把磁盤I/O分散在多個設備上，以減少資源競爭，提高並行操作能力

（4）配置多處理器，mysql是多線程的數據庫，多處理器可同時執行多個線程

2、優化MYSQL的參數

通過優化MYSQL的參數可以提高資源利用率，從而達到提高MYSQL服務器的性能的目的。

MYSQL服務器的配置參數都在my.cnf或者my.ini文件的[mysqld]組中。

下面對幾個對性能影響較大的參數進行介紹

我們先看一下與網絡連接的性能配置項及對性能的影響。
● max_conecctions：整個 MySQL 允許的最大連接數；
這個參數主要影響的是整個 MySQL 應用的並發處理能力，當系統中實際需要的連接量大於
max_conecctions 的情況下，由於 MySQL 的設置限制，那麼應用中必然會產生連接請求的等待，
從而限制了相應的並發量。所以一般來說，只要 MySQL 主機性能允許，都是將該參數設置的盡
可能大一點。一般來說 500 到 800 左右是一個比較合適的參考值
● max_user_connections：每個用戶允許的最大連接數；
上面的參數是限制了整個 MySQL 的連接數，而 max_user_connections 則是針對於單個用戶的連
接限制。在一般情況下我們可能都較少使用這個限制，只有在一些專門提供 MySQL 數據存儲服
務，或者是提供虛擬主機服務的應用中可能需要用到。除了限制的對象區別之外，其他方面和
max_connections 一樣。這個參數的設置完全依賴於應用程序的連接用戶數，對於普通的應用來
說，完全沒有做太多的限制，可以盡量放開一些。
● net_buffer_length：網絡包傳輸中，傳輸消息之前的 net buffer 初始化大小；
這個參數主要可能影響的是網絡傳輸的效率，由於該參數所設置的只是消息緩沖區的初始化大
小，所以造成的影響主要是當我們的每次消息都很大的時候 MySQL 總是需要多次申請擴展該緩
沖區大小。系統默認大小為 16KB，一般來說可以滿足大多數場景，當然如果我們的查詢都是非
常小，每次網絡傳輸量都很少，而且系統內存又比較緊缺的情況下，也可以適當將該值降低到
8KB。
● max_allowed_packet：在網絡傳輸中，一次傳消息輸量的最大值；
這個參數與 net_buffer_length 相對應，只不過是 net buffer 的最大值。當我們的消息傳輸量
大於 net_buffer_length 的設置時，MySQL 會自動增大 net buffer 的大小，直到緩沖區大小達
到 max_allowed_packet 所設置的值。系統默認值為 1MB，最大值是 1GB，必須設定為 1024 的倍
數，單位為字節。
● back_log：在 MySQL 的連接請求等待隊列中允許存放的最大連接請求數。
連接請求等待隊列，實際上是指當某一時刻客戶端的連接請求數量過大的時候，MySQL 主線程沒
辦法及時給每一個新的連接請求分配（或者創建）連接線程的時候，還沒有分配到連接線程的
所有請求將存放在一個等待隊列中，這個隊列就是 MySQL 的連接請求隊列。當我們的系統存在
瞬時的大量連接請求的時候，則應該注意 back_log 參數的設置。系統默認值為 50，最大可以設
置為 65535。當我們增大 back_log 的設置的時候，同時還需要主義 OS 級別對網絡監聽隊列的限
制，因為如果 OS 的網絡監聽設置小於 MySQL 的 back_log 設置的時候，我們加大“back_log”設
置是沒有意義的。
上面介紹了網絡連接交互相關的主要優化設置，下面我們再來看看與每一個客戶端連接想對應的連
接線程。
在 MySQL 中，為了盡可提高客戶端請求創建連接這個過程的性能，實現了一個 Thread Cache 池，將
空閒的連接線程存放在其中，而不是完成請求後就銷毀。這樣，當有新的連接請求的時候，MySQL 首先會
檢查 Thread Cache 池中是否存在空閒連接線程，如果存在則取出來直接使用，如果沒有空閒連接線程，
才創建新的連接線程。在 MySQL 中與連接線程相關的系統參數及狀態變量說明如下：
● thread_cache_size：Thread Cache 池中應該存放的連接線程數。
當系統最初啟動的時候，並不會馬上就創建 thread_cache_size 所設置數目的連接線程存放在
Thread Cache 池中，而是隨著連接線程的創建及使用，慢慢的將用完的連接線程存入其中。當
存放的連接線程達到 thread_cache_size 值之後，MySQL 就不會再續保存用完的連接線程了。
如果我們的應用程序使用的短連接，Thread Cache 池的功效是最明顯的。因為在短連接的數據
庫應用中，數據庫連接的創建和銷毀是非常頻繁的，如果每次都需要讓 MySQL 新建和銷毀相應
的連接線程，那麼這個資源消耗實際上是非常大的，而當我們使用了 Thread Cache 之後，由於
連接線程大部分都是在創建好了等待取用的狀態，既不需要每次都重新創建，又不需要在使用
完 之 後 銷 毀 ， 所 以 可 以 節 省 下 大 量 的 系 統 資 源 。 所 以 在 短 連 接 的 應 用 系 統 中 ，
thread_cache_size 的值應該設置的相對大一些，不應該小於應用系統對數據庫的實際並發請求
數。
而如果我們使用的是長連接的時候，Thread Cache 的功效可能並沒有使用短連接那樣的大，但
也並不是完全沒有價值。因為應用程序即使是使用了長連接，也很難保證他們所管理的所有連
接都能處於很穩定的狀態，仍然會有不少連接關閉和新建的操作出現。在有些並發量較高，應
用服務器數量較大的系統中，每分鐘十來次的連接創建與關閉的操作是很常見的。而且如果應
用服務器的連接池管理不是太好，容易產生連接池抖動的話，所產生的連接創建和銷毀操作將
會更多。所以即使是在使用長連接的應用環境中，Thread Cache 機制的利用仍然是對性能大有
幫助的。只不過在長連接的環境中我們不需要將 thread_cache_size 參數設置太大，一般來說
可能 50 到 100 之間應該就可以了。
● thread_stack：每個連接線程被創建的時候，MySQL 給他分配的內存大小。
當 MySQL 創建一個新的連接線程的時候，是需要給他分配一定大小的內存堆棧空間，以便存放
客戶端的請求 Query 以及自身的各種狀態和處理信息。不過一般來說如果不是對 MySQL 的連接線
程處理機制十分熟悉的話，不應該輕易調整該參數的大小，使用系統的默認值（192KB）基本上
可以所有的普通應用環境。如果該值設置太小，會影響 MySQL 連接線程能夠處理客戶端請求的
Query 內容的大小，以及用戶創建的 Procedures 和 Functions 等


計算出系統新建連接連接的 Thread
Cache 命中率，也就是通過 Thread Cache 池中取得連接線程的次數與系統接收的總連接次數的比率，如
下：
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%
我們可以通過上面的這個運算公式計算一下上面環境中的 Thread Cache 命中率：Thread_Cache_Hit
= (127 - 12) / 127 * 100% = 90.55%
一般來說，當系統穩定運行一段時間之後，我們的 Thread Cache 命中率應該保持在 90%左右甚至更
高的比率才算正常。可以看出上面環境中的 Thread Cache 命中比率基本還算是正常的。
Table Cache 相關的優化
我們先來看一下 MySQL 打開表的相關機制。由於多線程的實現機制，為了盡可能的提高性能，在
MySQL 中每個線程都是獨立的打開自己需要的表的文件描述符，而不是通過共享已經打開的表的文件描述
符的機制來實現。當然，針對於不同的存儲引擎可能有不同的處理方式。如 MyISAM 表，每一個客戶端線
程打開任何一個 MyISAM 表的數據文件都需要打開一個文件描述符，但如果是索引文件，則可以多個線程
共享同一個索引文件的描述符。對於 Innodb 的存儲引擎，如果我們使用的是共享表空間來存儲數據，那
麼我們需要打開的文件描述符就比較少，而如果我們使用的是獨享表空間方式來存儲數據，則同樣，由
於存儲表數據的數據文件較多，則同樣會打開很多的表文件描述符。除了數據庫的實際表或者索引打開
以外，臨時文件同樣也需要使用文件描述符，同樣會占用系統中 open_files_limit 的設置限額。
為了解決打開表文件描述符太過頻繁的問題，MySQL 在系統中實現了一個 Table Cache 的機制，和前
面介紹的 Thread Cache 機制有點類似，主要就是 Cache 打開的所有表文件的描述符，當有新的請求的時
候不需要再重新打開，使用結束的時候也不用立即關閉。通過這樣的方式來減少因為頻繁打開關閉文件
描述符所帶來的資源消耗。我們先看一看 Table Cache 相關的系統參數及狀態變量。
在 MySQL 中我們通過 table_cache（從 MySQL5.1.3 開始改為 table_open_cache），來設置系統中為
我們 Cache 的打開表文件描述符的數量。通過 MySQL 官方手冊中的介紹，我們設置 table_cache 大小的時
候應該通過 max_connections 參數計算得來，公式如下：
table_cache = max_connections * N；
其中 N 代表單個 Query 語句中所包含的最多 Table 的數量。但是我個人理解這樣的計算其實並不是太
准確，分析如下：
首先，max_connections 是系統同時可以接受的最大連接數，但是這些連接並不一定都是 active 狀
態的，也就是說可能裡面有不少連接都是處於 Sleep 狀態。而處於 Sleep 狀態的連接是不可能打開任何
Table 的。
其次，這個 N 為執行 Query 中包含最多的 Table 的 Query 所包含的 Table 的個數也並不是太合適，因
為我們不能忽略索引文件的打開。雖然索引文件在各個連接線程之間是可以共享打開的連接描述符的，
但總還是需要的。而且，如果我 Query 中的每個表的訪問都是通過現通過索引定位檢索的，甚至可能還
是通過多個索引，那麼該 Query 的執行所需要打開的文件描述符就更多了，可能是 N 的兩倍甚至三倍。
最後，這個計算的公式只能計算出我們同一時刻需要打開的描述符的最大數量，而 table_cache 的
設置也不一定非得根據這個極限值來設定，因為 table_cache 所設定的只是 Cache 打開的描述符的數量的
大小，而不是最多能夠打開的量的大小。


join_buffer_size ：當我們的 Join 是 ALL ， index ， rang 或者 index_merge 的時候使用的
Buffer；
實際上這種 Join 被稱為 Full Join。實際上參與 Join 的每一個表都需要一個 Join Buffer，所以在
Join 出現的時候，至少是兩個。Join Buffer 的設置在 MySQL 5.1.23 版本之前最大為 4GB，但是從
5.1.23 版本開始，在除了 Windows 之外的 64 位的平台上可以超出 4BG 的限制。系統默認是 128KB。
● sort_buffer_size：系統中對數據進行排序的時候使用的 Buffer；
Sort Buffer 同樣是針對單個 Thread 的，所以當多個 Thread 同時進行排序的時候，系統中就會出現
多個 Sort Buffer。一般我們可以通過增大 Sort Buffer 的大小來提高 ORDER BY 或者是 GROUP BY
的處理性能。系統默認大小為 2MB，最大限制和 Join Buffer 一樣，在 MySQL 5.1.23 版本之前最大
為 4GB，從 5.1.23 版本開始，在除了 Windows 之外的 64 位的平台上可以超出 4GB 的限制。
如果應用系統中很少有 Join 語句出現，則可以不用太在乎 join_buffer_size 參數的大小設置，但是
如果 Join 語句不是很少的話，個人建議可以適當增大 join_buffer_size 的設置到 1MB 左右，如果內存充
足甚至可以設置為 2MB。對於 sort_buffer_size 參數來說，一般設置為 2MB 到 4MB 之間可以滿足大多數
應用的需求。當然，如果應用系統中的排序都比較大，內存充足且並發量不是特別的大的時候，也可以
繼續增大 sort_buffer_size 的設置。在這兩個 Buffer 設置的時候，最需要注意的就是不要忘記是每個
Thread 都會創建自己獨立的 Buffer，而不是整個系統共享的 Buffer，不要因為設置過大而造成系統內存
不足。

配置完參數之後，需要重啟MYSQL服務才能生效

---

如何使用查詢緩沖區

查詢緩沖區可以提高查詢的速度，但是這種方式只適合查詢語句多、更新較少的情況。默認情況下查詢緩沖區的大小為0，也就是不可用

可以修改query_cache_size以調整查詢緩沖區大小；修改 query_cache_type以調整查詢緩沖區的類型。

在my.ini中修改query_cache_size和query_cache_type的值如下所示

[mysqld]
query_cache_size=512M
query_cache_type=1

query_cache_type=1表示開啟查詢緩沖區。只有在查詢語句中包含SQL_NO_CACHE關鍵字時，才不會使用查詢緩沖區。

可以使用FLUSH QUERY CACHE語句來刷新緩沖區，清理查詢緩沖區中的碎片

注意：開啟查詢緩沖區是有風險的，如果命中率不高，或者更新修改語句較多，都會使查詢緩沖區失效，從而使命中率更加低

建議使用memcached等軟件來做二級緩存，除非系統中修改語句較少，命中率較高，這樣才會看到明顯的性能提升

---

總結

本文闡述了MYSQL的性能優化面的內容，雖然網上對於MYSQL優化的資料很多

但是，MYSQL優化方面需要長期的進行研究探索才能找到適合於自己公司的業務系統最佳參數，否則只是使用網上的介紹只會人雲亦雲