- 我的MYSQL學習心得（1） ：簡單語法
- 我的MYSQL學習心得（2） ：資料型別寬度
- 我的MYSQL學習心得（3） ： 檢視欄位長度
- 我的MYSQL學習心得（4） ： 資料型別
- 我的MYSQL學習心得（5） ： 運算子
- 我的MYSQL學習心得（6） ： 函式
- 我的MYSQL學習心得（7） ： 查詢
- 我的MYSQL學習心得（8） ： 插入 更新 刪除
- 我的MYSQL學習心得（9） ： 索引
- 我的MYSQL學習心得（10） ： 自定義儲存過程和函式
- 我的MYSQL學習心得（11） ： 檢視
- 我的MYSQL學習心得（12） ： 觸發器
- 我的MYSQL學習心得（13） ： 許可權管理
- 我的MYSQL學習心得（14） ： 備份和恢復
- 我的MYSQL學習心得（15） ： 日誌
一步一步走來已經寫到了第十六篇了~
這一篇主要介紹MYSQL的優化，優化MYSQL資料庫是DBA和開發人員的必備技能
MYSQL優化一方面是找出系統瓶頸，提高MYSQL資料庫整體效能；另一方面需要合理的結構設計和引數調整，以提高
使用者操作響應的速度；同時還有儘可能節省系統資源，以便系統可以提供更大負荷的服務
如果大家看過我寫的兩篇文章，那麼學習MYSQL的索引就不會太難，因為是相通的
其實MYSQL也有SQLSERVER堆表的概念
myisam允許沒有任何索引和主鍵的表存在，個人覺得沒有主鍵的myisam表都屬於堆表，因為MYSQL不支援非主鍵的聚集索引。
innodb引擎如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6位元組的主鍵(使用者不可見)
詳細參考：MyISAM vs InnoDB：MySQL儲存引擎詳解
不過《MyISAM vs InnoDB：MySQL儲存引擎詳解》文章也有一點錯誤，意向共享鎖就是表鎖，其實是不對的
1、優化簡介
mysql優化是多方面的，原則是減少系統的瓶頸，減少資源的佔用，增加系統的反應速度。
例如，通過優化檔案系統，提高磁碟I/O的讀寫速度；通過優化作業系統排程策略，提高mysql在高負荷情況下
的負載能力；優化表結構、索引、查詢語句等使查詢響應更快
在mysql中，可以使用show status語句查詢一些mysql的效能引數
|
1 |
show status like 'value'; |
其中value是要查詢的引數值，一些常用效能引數如下：
connections：連線mysql伺服器的次數
uptime：mysql伺服器的上線時間
slow_queries：慢查詢的次數
com_select:查詢操作次數
com_insert:插入操作次數
com_update:更新操作次數
com_delete:刪除操作次數
如果查詢mysql伺服器的連線次數，可以執行如下語句
|
1 |
show status like 'connections'; |
如果查詢mysql伺服器的慢查詢次數，可以執行如下語句
|
1 |
show status like 'slow_queries'; |
2、優化查詢
查詢是資料庫最頻繁的操作，提高查詢速度可以有效地提高mysql資料庫的效能
（1）分析查詢語句
通過對查詢語句的分析，可以瞭解查詢語句的執行情況找出查詢語句執行的瓶頸
mysql中提供了EXPLAIN語句和DESCRIBE語句，用來分析查詢語句
EXPLAIN語句的基本語法
|
1 |
EXPLAIN [EXTENDED] SELECT SELECT_OPTION |
使用EXTENDED關鍵字，EXPLAIN語句將產生附加資訊。SELECT_OPTION是SELECT 語句的查詢選項，包括FROM WHERE子句等
執行該語句，可以分析EXPLAIN後面的select語句的執行情況，並且能夠分析所查詢的表的一些特徵
使用EXPLAIN語句來分析1個查詢語句
|
1 2 |
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表：
|
1 2 |
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：
|
1 2 3 4 5 6 |
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：
|
1 2 3 4 5 6 7 8 |
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：
|
1 2 |
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; |
（6） index_merge
該聯接型別表示使用了索引合併優化方法。在這種情況下，key列包含了所用到的索引的清單，key_len列包含了所用到的索引的最長長度。
（7） unique_subquery
該型別替換了下面形式的IN子查詢的ref：
|
1 |
value IN (SELECT primary_key FROM single_table WHERE some_expr) |
unique_subquery是一個索引查詢型別，可以完全替換子查詢，效率更高。
（8） index_subquery
該聯接型別類似於unique_subquery，不過索引型別不需要是唯一索引，可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：
|
1 |
value IN (SELECT key_column FROM single_table WHERE some_expr) |
（9） range
只檢索給定範圍的行，使用一個索引來檢索行資料。key列顯示使用了哪個索引，key_len顯示所使用索引的長度。
在該型別中ref列為NULL。
當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，型別為range。
下面介紹幾種檢索指定行資料的情況
|
1 2 3 4 5 6 7 8 9 10 11 |
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檢查查詢。
如果要查詢一張表有什麼索引，可以使用
|
1 |
SHOW INDEX FROM tbl_name |
key
key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，那麼可能列的值是NULL。
要想強制MySQL使用或忽略possible_keys列中的索引，在查詢中可以使用
|
1 |
FORCE INDEX -- 強逼使用某個索引 |
|
1 |
USE INDEX --使用某個索引 |
|
1 |
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標準的行後，不再為前面的的行組合在該表內檢查更多的行。
下面是一個可以這樣優化的查詢型別的例子：
|
1 2 |
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語句的語法如下
|
1 |
DESCRIBE SELECT select_options |
DESCIBE可以縮寫成DESC
（2）索引對查詢速度的影響
mysql中提高效能的一個最有效的方式就是對資料表設計合理的索引。索引提供了高效訪問資料的方法，並且加快查詢速度
因此索引對查詢速度有著至關重要的影響。
如果查詢沒有索引，查詢語句將掃描表中所有記錄。在資料量大的情況下，這樣查詢的速度會很慢。如果使用索引進行查詢，
查詢語句可以根據索引快速定位到待查詢記錄，從而減少查詢的記錄數，達到提高查詢速度的目的。
下面是查詢語句中不使用索引和使用索引的對比，首先分析未使用索引的查詢情況，EXPLAIN語句執行如下
|
1 |
EXPLAIN SELECT `ID`,`name` FROM `test`.`emp` WHERE `name` ='nihao' |
可以看到，rows列的值是3說“SELECT ID,name FROM test.emp WHERE name =’nihao’” 語句掃描了表中的3條記錄
然後在emp表加上索引
|
1 |
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語句如下
|
1 2 |
USE test; EXPLAIN SELECT * FROM `test`.`emp` WHERE `name` LIKE '%x'; |
|
1 2 |
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欄位建立多列索引，驗證多列索引的情況
|
1 |
CREATE INDEX ix_person_name_age ON `person` (name,age) |
|
1 |
EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `Name` ='suse' |
|
1 |
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關鍵字的情況
我們再建立一個索引
|
1 |
CREATE INDEX ix_person_age ON `person` (age) |
|
1 |
EXPLAIN SELECT Name,Age FROM `person` WHERE `Name` ='SUSE' OR `job`='SPORTMAN' |
|
1 |
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會根據表的索引對插入的記錄建立索引。如果插入大量資料，建立索引會降低插入記錄的速度。
為了解決這個問題，可以在插入記錄之前禁用索引，資料插入完畢後再開啟索引
禁用索引語句如下：
|
1 |
ALTER TABLE table_name DISABLE KEYS ; |
其中table_name是禁用索引的表的表名
重新開啟索引語句如下：
|
1 |
ALTER TABLE table_name ENABLE KEYS ; |
對於空表批量匯入資料，則不需要進行此操作，因為myisam表是在匯入資料之後才建立索引！
2、禁用唯一性檢查
插入資料時，mysql會對插入的記錄進行唯一性校驗。這種唯一性校驗也會降低插入記錄的速度。
為了降低這種情況對查詢速度的影響，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢之後再開啟
禁用唯一性檢查的語句如下：
|
1 |
SET UNIQUE_CHECKS=0; |
開啟唯一性檢查的語句如下：
|
1 |
SET UNIQUE_CHECKS=1; |
3、使用批量插入
插入多條記錄時，可以使用一條INSERT語句插入一條記錄，也可以使用一條INSERT語句插入多條記錄。
第一種情況
|
1 2 3 |
INSERT INTO emp(id,name) VALUES (1,'suse'); INSERT INTO emp(id,name) VALUES (2,'lily'); INSERT INTO emp(id,name) VALUES (3,'tom'); |
第二種情況
|
1 |
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會對插入的記錄進行唯一性校驗。這種唯一性校驗也會降低插入記錄的速度。
為了降低這種情況對查詢速度的影響，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢之後再開啟
禁用唯一性檢查的語句如下：
|
1 |
SET UNIQUE_CHECKS=0; |
開啟唯一性檢查的語句如下：
|
1 |
SET UNIQUE_CHECKS=1; |
2、禁用外來鍵約束
插入資料之前執行禁止對外來鍵的檢查，資料插入完成之後再恢復對外來鍵的檢查。禁用外來鍵檢查的語句如下：
|
1 |
SET FOREIGN_KEY_CHECKS=0; |
恢復對外來鍵的檢查語句如下
|
1 |
SET FOREIGN_KEY_CHECKS=1; |
3、禁止自動提交
插入資料之前禁止事務的自動提交，資料匯入完成之後，執行恢復自動提交操作
或顯式指定事務
|
1 2 3 4 5 6 |
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 語句的基本語法如下
|
1 |
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表，執行語句如下：
|
1 |
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也可以檢查檢視是否有錯誤，
比如在檢視定義中被引用的表已不存在。
該語句基本語法如下：
|
1 2 |
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語句的基本語法如下：
|
1 |
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型別的表有效。
|
1 2 |
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會相應變化。
語法如下：
|
1 |
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]組中。
下面對幾個對效能影響較大的引數進行介紹
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
我們先看一下與網路連線的效能配置項及對效能的影響。 ● 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的值如下所示
|
1 2 3 |
[mysqld] query_cache_size=512M query_cache_type=1 |
query_cache_type=1表示開啟查詢緩衝區。只有在查詢語句中包含SQL_NO_CACHE關鍵字時，才不會使用查詢緩衝區。
可以使用FLUSH QUERY CACHE語句來重新整理緩衝區，清理查詢緩衝區中的碎片
注意：開啟查詢緩衝區是有風險的，如果命中率不高，或者更新修改語句較多，都會使查詢緩衝區失效，從而使命中率更加低
建議使用memcached等軟體來做二級快取，除非系統中修改語句較少，命中率較高，這樣才會看到明顯的效能提升
總結
本文闡述了MYSQL的效能優化面的內容，雖然網上對於MYSQL優化的資料很多
但是，MYSQL優化方面需要長期的進行研究探索才能找到適合於自己公司的業務系統最佳引數，否則只是使用網上的介紹只會人云亦云