1、我的MYSQ岸習心得我的 mysql 學習心得（十六）優化一步一步走來已經寫到了第十六篇了 這一篇主要介紹 mysql 的優化 , 優化 mysql 數據庫是 dba 和開發人員的必備技能mysql 優化一方面是找出系統瓶頸, 提高 mysql 數據庫整體性能；另一方面需要合理的結構設計和參數調整, 以提高用戶操作響應的速度；同時還有盡可能節省系統資源, 以便系統可以提供更大負荷的服務如果大家看過我寫的兩篇文章, 那么學習 mysql 的索引就不會太難 , 因為是相通的其實 mysql 也有 sqlserver 堆表的概念myisam允許沒有任何索引和主鍵的表存在，個人覺得沒有主鍵的 myis

2、am表都屬于堆表，因為mysql不支持非主鍵的聚集索引.innodb 引擎如果沒有設定主鍵或者非空唯一索引 , 就會自動生成一個 6 字節的主鍵（用戶不可見）詳細參考 :不過 myisamvsinnodb:mysql 存儲引擎詳解文章也有一點錯誤 , 意向共享鎖就是表鎖 , 其實是不對的1、優化簡介mysql 優化是多方面的 , 原則是減少系統的瓶頸 , 減少資源的占用增加系統的反應速度.例如 , 通過優化文件系統, 提高磁盤 i/o 的讀寫速度；通過優化操作系統調度策略, 提高 mysql 在高負荷情況下的負載能力；優化表結構、索引、查詢語句等使查詢響應更快在 mysql 中, 可以使用 s

3、howstatus 語句查詢一些 mysql 的性能參數showstatuslike"value"其中 value 是要查詢的參數值, 一些常用性能參數如下 :connections: 連接 mysql 服務器的次數uptime:mysql 服務器的上線時間slow_queries: 慢查詢的次數com_select: 查詢操作次數com_insert: 插入操作次數com_update:更新操作次數com_delete: 刪除操作次數如果查詢 mysql 服務器的連接次數, 可以執行如下語句showstatuslike"connections"如果查詢

4、 mysql 服務器的慢查詢次數, 可以執行如下語句showstatuslike"slow_queries"2、優化查詢查詢是數據庫最頻繁的操作, 提高查詢速度可以有效地提高mysql 數據庫的性能（ 1）分析查詢語句通過對查詢語句的分析 , 可以了解查詢語句的執行情況找出查詢語句執行的瓶頸mysql 中提供了 explain 語句和 describe 語句 , 用來分析查詢語explain 語句的基本語法explainextendedselectselect_option使用 extended 關鍵字 ,explain 語句將產生附加信息 .select_option 是

5、 select 語句的查詢選項 , 包括 fromwhere 子句 等執行該語句 , 可以分析 explain 后面的 select 語句的執行情況 , 并且能夠分析所查詢的表的一些特征使用 explain 語句來分析 1 個查詢語句usetest;explainextendedselect*fromperson;下面對結果進行解釋 idselect 識別符 . 這是 select 的查詢序列號. select_typeselect 類型 , 可以為以下任何一種 :simple: 簡單 select（ 不使用 union 或子查詢 ）primary: 表示主查詢 , 或者是最外層的查詢語句（多

6、表連接的時候）union: 表示連接查詢的第二個或后面的查詢語句dependentunion:union 連接查詢中的第二個或后面的 select 語 句 , 取決于外面的查詢unionresult:union 連接查詢的結果subquery: 子查詢中的第一個select 語句dependentsubquery: 子查詢中的第一個select 語句 , 取決于外面的查詢derived: 導出表的 select（from 子句的子查詢 ） table表示查詢的表 type表示表的聯接類型下面給出各種聯接類型, 按照從最佳類型到最壞類型進行排序 :（ 1） system表僅有一行（= 系統表 ）

7、. 這是 const 聯接類型的一個特例 .（ 2） const表最多只有一個匹配行, 它將在查詢開始時被讀取. 余下的查詢優化中被作為常量對待.const 表查詢速度很快, 因為它們只讀取一次.const 用于常數值比較primarykey 或 unique 索引的所有部分的場合 .在下面的查詢中 ,tbl_name 可以用于 const 表:（ 3） eq_ref對于每個來自于前面的表的行組合, 從該表中讀取一行. 這可能是最好的聯接類型 , 除了 const 類型 .它用在一個索引的所有部分被聯接使用并且索引是unique 或primarykey 時.eq_ref 可以用于使用“=”操作

8、符比較的帶索引的列. 比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式 .在下面的例子中 ,mysql 可以使用 eq_ref 聯接來處理ref_tables:select*fromref_table,other_tablewhereref_table.key_column=other_table.column;select*fromref_table,other_tablewherer ef_table.key_column_part1=other_table.columnandref_table.ke y_column_part2=1;（ 4） ref對于每個來自于前面的表的任意

9、行組合, 將從該表中讀取所有匹配的行 .如果聯接只使用索引鍵的最左邊的前綴, 或如果索引鍵不是unique 或 primarykey, 則使用 ref.如果使用的鍵僅僅匹配少量行, 該聯接類型是不錯的 .ref 可以用于使用=或 <=>操作符的帶索引的列.在下面的例子中 ,mysql 可以使用 ref 聯接來處理ref_tables:select*fromref_tablewherekey_column=expr;select*fromref_ table,other_tablewhereref_table.key_column=other_table.colu mn;select

10、*fromref_table,other_tablewhereref_table.key_colum n_part1=other_table.columnandref_table.key_column_part2=1;（ 5） ref_or_null該聯接類型如同 ref, 但是添加了 mysql 可以專門搜索包含 null 值的行 , 在解決子查詢中經常使用該聯接類型的優化 .在下面的例子中 ,mysql 可以使用 ref_or_null 聯接來處理ref_tables:select*fromref_tablewherekey_column=exprorkey_columnisnu ll;（

11、 6） index_merge該聯接類型表示使用了索引合并優化方法. 在這種情況下 ,key 列包含了所用到的索引的清單 ,key_len 列包含了所用到的索引的最長 長度 .該類型替換了下面形式的 in 子查詢的 ref:valuein(selectprimary_keyfromsingle_tablewheresome_expr)unique_subquery 是一個索引查找類型 , 可以完全替換子查詢 , 效 率更高 .( 8) index_subquery該聯接類型類似于 unique_subquery, 不過索引類型不需要是唯一索引 , 可以替換 in 子查詢 , 但只適合下列形式的

12、子查詢中的非唯一索引 :valuein(selectkey_columnfromsingle_tablewheresome_expr)( 9) range只檢索給定范圍的行, 使用一個索引來檢索行數據.key 列顯示使用了哪個索引 ,key_len 顯示所使用索引的長度.在該類型中 ref 列為 null.當使用 =、 <>、 >、 >=、 <、 <=、 isnull 、 <=>、 between 或者 in操作符 , 用常量比較關鍵字列時 , 類型為 range.下面介紹幾種檢索指定行數據的情況select*fromtbl_namewherek

13、ey_column=10;select*fromtbl_nam ewherekey_columnbetween10and20;select*fromtbl_namewherekey_ columnin(10,20,30);select*fromtbl_namewherekey_part1=10andk ey_part2in(10,20,30);( 10) index該聯接類型與all 相同 , 除了掃描索引樹. 其他情況都比 all 快 ,因為索引文件通常比數據文件小 .當查詢只使用作為單索引一部分的列時,mysql 可以使用該聯接類型 .11) all對于每個來自于先前的表的行組合, 進行完

14、整的表掃描如果第一個表沒標記為 const, 這樣執行計劃就不會很好通常可以增加更多的索引來擺脫all, 使得行能基于前面的表中的常數值或列值被檢索出 .possible_keyspossible_keys 列指出 mysql 能供給使用的索引鍵有哪些 . 注意 ,該列完全獨立于 explain 輸出所示的表的次序 .這意味著在possible_keys 中的某些索引鍵實際上不能按生成的表次序使用 .如果該列是null, 則沒有相關的索引 . 在這種情況下 , 可以通過檢查 where 子句查看是否可以引用某些列或適合的索引列來提高查詢性能 .如果是這樣, 創造一個適當的索引并且再次用 exp

15、lain 檢查查詢 .如果要查詢一張表有什么索引 , 可以使用showindexfromtbl_namekeykey 列顯示 mysql 實際決定使用的鍵（索引） . 如果沒有選擇索 引, 那么可能列的值是null.要想強制 mysql 使用或忽略possible_keys 列中的索引 , 在查詢中可以使用forceindex- 強逼使用某個索引 useindex- 使用某個索引ignoreindex- 忽略某個索引對于myisam 引擎和bdb 引擎的表 , 運行 analyzetable 可以幫助優化器選擇更好的索引 .對于 myisam表，可以使用 myisamchk-analyze.k

16、ey_lenkey_len 列顯示 mysql 決定使用的索引鍵的長度（按字節計算）如果鍵是 null, 則長度為 null.注意通過 key_len 值我們可以確定mysql 將實際使用一個多索引鍵索引的幾個字段.refref 列顯示使用哪個列或常數與索引一起查詢記錄 .rowsrows 列顯示 mysql 預估執行查詢時必須要檢索的行數.extra該列包含 mysql 處理查詢時的詳細信息 . 下面解釋了該列可以顯示的不同的文本字符串 :distinctmysql 發現第 1 個匹配行后 , 停止為當前的行組合搜索更多的行.notexistsmysql 能夠對查詢進行leftjoin 優化

17、 , 發現 1 個匹配 leftjoin標準的行后 , 不再為前面的的行組合在該表內檢查更多的行 .下面是一個可以這樣優化的查詢類型的例子:select*fromt1leftjoint2ont1.id=t2.idwheret2.idisnull;假定 t2.id 定義為 notnull. 在這種情況下 ,mysql 使用 t1.id 的 值掃描 t1 并查找 t2 中的行 .如果 mysql 在 t2 中發現一個匹配的行, 它知道 t2.id 絕不會為null, 并且不再掃描t2 內有相同的 id 值的行 . 換句話說 , 對于 t1 的每個行 ,mysql 只需要在 t2 中查找一次, 無論

18、 t2 內實際有多少匹配的行 .rangecheckedforeachrecord(indexmap:#)mysql 沒有發現好的可以使用的索引 , 但發現如果來自前面的表的列值已知 , 可能部分索引可以使用 .對前面的表的每個行組合,mysql 檢查是否可以使用 range 或index_merge 訪問方法來獲取行.這并不很快, 但比執行沒有索引的聯接要快得多 .可以參考一下這篇文章:里面就提到了 rangecheckedforeachrecordusingfilesortmysql 需要額外的一次傳遞, 以找出如何按排序順序檢索行.通過根據聯接類型瀏覽所有行并為所有匹配where子句的行

19、保存排序關鍵字和行的指針來完成排序 .然后關鍵字被排序 , 并按排序順序檢索行如果是 orderby 操作就會用到這個usingfilesort, 當然filesort 不是指使用文件來排序, 大家不要誤會了 .usingindex從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息 . 當查詢只使用作為單一索引一部分的列時, 可以使用該策略 .usingtemporary為了解決查詢 ,mysql 需要創建一個臨時表來容納結果.典型情況如查詢包含可以按不同情況列出列的 groupby 和orderby 子句時 .一般用到臨時表都會看到 usingtemporaryusing

20、wherewhere子句用于限制哪一個行匹配下一個表或發送到客戶端除非你專門從表中索取或檢查所有行, 如果 extra 值不為usingwhere 并且表聯接類型為 all 或 index, 查詢可能會有一些錯誤usingindexforgroup-by類似于訪問表的 usingindex 方式 ,usingindexforgroup-by 表示 mysql 發現了一個索引 , 可以用來查詢 groupby 或 distinct 查詢的 所有列 ,而不要額外搜索硬盤訪問實際的表. 并且 , 按最有效的方式使用索引, 以便對于每個組, 只讀取少量索引條目 .descibe 語句的使用方法與 ex

21、plain 語句是一樣的 , 并且分享結果也是一樣的 descibe 語句的語法如下describeselectselect_optionsdescibe 可以縮寫成desc（ 2）索引對查詢速度的影響mysql 中提高性能的一個最有效的方式就是對數據表設計合理的索引 . 索引提供了高效訪問數據的方法 , 并且加快查詢速度因此索引對查詢速度有著至關重要的影響 .如果查詢沒有索引 , 查詢語句將掃描表中所有記錄 . 在數據量大的情況下 , 這樣查詢的速度會很慢. 如果使用索引進行查詢 ,查詢語句可以根據索引快速定位到待查詢記錄 , 從而減少查詢的記錄數 , 達到提高查詢速度的目的 .下面是查詢語

22、句中不使用索引和使用索引的對比 , 首先分析未使用索引的查詢情況,explain 語句執行如下explainselect'id','name'from'test'.'emp'where'name'="nihao"可以看到 ,rows 列的值是3說“select'id','name'from'test'.'emp'where'name'="nihao" ” 語句掃描了表中的 3 條記錄然后在emp表

23、加上索引createindexix_emp_nameonemp（name）現在再分析上面的查詢語句 , 執行的 explain 語句結果如下結果顯示 ,rows 列的值為 1.這表示這個查詢語句只掃描了表中的一條記錄 , 其他查詢速度自然比掃描3 條記錄快 .而且 possible_keys 和key的值都是ix_emp_name這說明查詢 時使用了 ix_emp_name索弓I如果表中記錄有100條、 1000條、 10000條優勢就顯現出來了（ 3）使用索引查詢索引可以提高查詢速度, 但并不是使用帶有索引的字段查詢時, 索引都會起作用 .下面的幾種情況跟跟sqlserver 一樣 , 有可

24、能用不到索引（ 1）使用 like 關鍵字的查詢語句使用 like 關鍵字進行查詢的時候, 如果匹配字符串的第一個字符為“ %” , 索引不起作用 . 只有“%”不在第一個位置, 索引才會起作用使用 like 關鍵字 , 并且匹配字符串中含有“%”字符,explain 語句如下usetest;explainselect*from'test'.'emp'where'name'like"%xH.usetest;explainselect*from'test'.'emp'where'name'l

25、ike"x%H.name上有索弓 I ix_emp_name第一個查詢type 為 all, 表示要全表掃描第二個查詢type 為 index, 表示會掃描索引like 關鍵字是否能利用上索引跟sqlserver 是一樣的我之前寫過一篇文章:（ 2）使用多列索引的查詢語句mysql 可以為多個字段創建索引 . 一個索引可以包括16個字段（跟 sqlserver 一樣）對于多列索引 , 只有查詢條件中使用了這些字段中的第一個字段時, 索引才會被使用 , 這個字段叫 :在表 person 中 name,age 字段創建多列索引, 驗證多列索引的情況createindexix_person

26、_name_ageon'person'（name,age）explain selectid,name,age,jobfrom'person'where'name'="suse"explainselectid,name,age,jobfrom'person'where'age'=12從第一條查詢看出，where'name'="suse"的記錄有一條，掃描了一條記錄并且使用了 ix_person_name_age 索引從第二條記錄可以看出 ,rows 列的值為 4

27、,說明共掃描了4 條記錄 ,并且 key 列值為 null, 說明explainselectid,name,age,jobfrom'person'where'age'=12語句并沒有使用索引.因為age字段是多列索引的第二個字段，只 有查詢條件中使用了 nam#段才會使用ix_person_name_age索引這個跟 sqlserver 是一樣的 , 詳細請看 :（ 3)使用 or 關鍵字的查詢語句查詢語句的查詢條件中只有 or 關鍵字 , 而且 or 前后的兩個條件中的列都是索引時, 查詢中才使用索引 , 否則 , 查詢不使用索引查詢語句使用 or 關鍵字的情

28、況我們再創建一個索引createindexix_person_ageon'person'(age)explainselectname,agefrom'person'where'name'="suse"or'jo b'="sportman"explainselectname,agefrom'person'where'age'=2or'name'="s use大家要注意, 這里跟剛才不一樣, 這次我們 select 的字段只有nam褥口

29、age,而不是select 出全部字段因為并沒有在job 這個字段上建立索引 , 所以第一個查詢使用的是全表掃描第二個查詢因為nam棄段和age字段都有索引，那么mysql可以 利用這兩個索引的其中之一, 這里是 ix_person_name_age 索引來查找記錄利用索引來查找記錄會快很多（ 4）優化子查詢mysql 從 4.1 版本開始支持子查詢 , 使用子查詢可以進行select語句的嵌套查詢 , 即一個 select 查詢的結果作為另一個select 語句的條件子查詢可以一次性完成很多邏輯需要多個步驟才能完成的 sql 操 作 . 子查詢雖然使查詢語句靈活 , 但是執行效率不高 .執行

30、子查詢時,mysql 需要為內層查詢語句結果建立一個臨時表 .然后外層查詢語句從臨時表中查詢記錄查詢完畢后, 再撤銷臨時表. 因此 , 子查詢的速度會受到一定影響 ,如果查詢的數據量特別大, 這種影響就會更大.在 mysql 中, 可以使用連接（ join ）查詢來代替子查詢 . 連接查詢不需要建立臨時表, 其速度比子查詢快, 如果查詢中使用索引的話, 性能會更好 .所以很多網上的文章都說盡量使用 join 來代替子查詢 , 雖然網上也說 mysql5.7 對于子查詢有很大的改進, 但是如果不是使用mysql5.7 還是需要注意的如果系統中 join 語句特別多還需要注意修改 my.ini 或

31、 f文件中的 join_buffer_size 大小 , 預防性能問題優化數據庫結構一個好的數據庫設計方案對于數據庫的性能常常起到事半功倍的效果 .數據庫結構的設計需要考慮數據冗余、查詢和更新速度、字段的數據類型是否合理等多方面（ 1）將字段很多的表拆分成多個表有時候有些字段使用頻率很低或者字段的數據類型比較大, 那么可以考慮垂直拆分的方法, 把不常用的字段和大字段拆分出去（ 2）增加中間表對于需要經常聯合查詢的表, 可以建立中間表以提高查詢效率. 通過建立中間表, 把需要經常聯合查詢的數據插入到中間表中 ,然后將原來的聯合查詢改為對中間表的查詢 , 以此來提高查詢效 率.（ 3）增加冗余字段

32、設計數據庫表時應盡量遵循范式理論, 盡可能減少冗余字段, 但是現今存儲硬件越來越便宜 , 有時候查詢數據的時候需要join 多個表這樣在高峰期間會影響查詢的效率 , 我們需要反范式而為之, 增加一些必要的冗余字段, 以空間換時間需要這樣做會增加開發的工作量和維護量 , 但是如果能換來可觀的性能提升, 這樣做也是值得的（ 4）優化插入記錄的速度插入記錄時, 影響插入速度的主要是索引、唯一性校驗、一次插入記錄條數等 .根據實際情況, 可以分別進行優化對于myisam表，常見優化方法如下1、禁用索引對于非空表, 插入記錄時,mysql 會根據表的索引對插入的記錄建立索引 . 如果插入大量數據, 建立

33、索引會降低插入記錄的速度.為了解決這個問題, 可以在插入記錄之前禁用索引, 數據插入完畢后再開啟索引禁用索引語句如下:altertabletable_namedisablekeys;其中 table_name 是禁用索引的表的表名重新開啟索引語句如下 :altertabletable_nameenablekeys;對于空表批量導入數據，則不需要進行此操作，因為myisam表是 在導入數據之后才建立索引！2、禁用唯一性檢查插入數據時,mysql 會對插入的記錄進行唯一性校驗. 這種唯一性校驗也會降低插入記錄的速度.為了降低這種情況對查詢速度的影響 , 可以在插入記錄之前禁用唯一性檢查, 等到記錄

34、插入完畢之后再開啟禁用唯一性檢查的語句如下:setunique_checks=0;開啟唯一性檢查的語句如下:setunique_checks=1;3、使用批量插入插入多條記錄時, 可以使用一條insert 語句插入一條記錄 , 也可以使用一條insert 語句插入多條記錄 .第一種情況insertintoemp(id,name)values(1,"suse");insertintoemp(id, name)values(2,"lily");insertintoemp(id,name)values(3,"tom");第二種情況inser

35、tintoemp(id,name)values(1,"suse"),(2,"lily"),(3,"to m")第二種情況要比第一種情況要快4、使用loaddatainfile 批量導入當需要批量導入數據時, 如果能用 loaddatainfile 語句 , 就盡量使用 . 因為 loaddatainfile 語句導入數據的速度比 insert 語句快很 多對于 innodb 引擎的表 , 常見的優化方法如下 :1、禁用唯一性檢查插入數據時,mysql 會對插入的記錄進行唯一性校驗. 這種唯一性校驗也會降低插入記錄的速度.為了降低這種情

36、況對查詢速度的影響 , 可以在插入記錄之前禁用唯一性檢查, 等到記錄插入完畢之后再開啟禁用唯一性檢查的語句如下:setunique_checks=0;開啟唯一性檢查的語句如下:setunique_checks=1;2、禁用外鍵約束插入數據之前執行禁止對外鍵的檢查, 數據插入完成之后再恢復對外鍵的檢查 . 禁用外鍵檢查的語句如下 :setforeign_key_checks=0;恢復對外鍵的檢查語句如下setforeign_key_checks=1;3、禁止自動提交插入數據之前禁止事務的自動提交 , 數據導入完成之后 , 執行恢復自動提交操作或顯式指定事務(5)分析表、檢查表、優化表、修復表和c

37、hecksum表mysql 提供了分析表、檢查表和優化表的語句分析表主要是分析關鍵字的分布；檢查表主要是檢查表是否存在錯誤；優化表主要是消除刪除或者更新造成的空間浪費修復表主要對myisam表文件進行修復checksum表主要對表數據傳輸前和傳輸后進行比較1、分析表mysql 中提供了 analyzetable 語句分析表 ,analyzetable 語句的基本語法如下analyzelocal|no_write_to_binlogtabletbl_name,tbl_name.local 關鍵字是 no_write_to_binlog 關鍵字的別名 , 二者都是執行過程不寫入二進制日志 ,tbl

38、_name 為分析的表的表名可以有一個或多個使用 analyzetable 分析表的過程中 , 數據庫系統會自動對表加一個只讀鎖 . 在分享期間 , 只能讀取表的記錄 , 不能更新和插入記錄analyzetable 語句能分析innodb、bdb和myisam類型的表 使用analyzetable 來分析em速，執行語句如下： analyzetableemp;上面結果顯示說明table: 表示分析的表名op: 表示執行的操作,analyze 表示進行分析操作msg_type: 表示信息類型其值通常是狀態（ status ）、信息（info ）、注意（note）、警告（warning ）和錯誤（

39、error ）之一msg_text: 顯示信息實際上分析表跟sqlserver 里的更新統計信息是差不多的主要就是為了索引的基數更加準確 , 從而使查詢優化器能夠更加準確的預估行數emp表的記錄行數是18分析表之后 ,cardinality 基數更加準確了2、檢查表mysql 中使用 checktable 語句來檢查表.checktable 語句能夠檢查innodb和myisam類型的表是否存在錯誤.對于myisam類型的表,checktable語句還會更新關鍵字統計數據 . 而且 ,checktable 也可以檢查視圖是否有錯誤,比如在視圖定義中被引用的表已不存在.該語句基本語法如下 :ch

40、ecktabletbl_name,tbl_name.option.option=quick|fast|medium|extended|changed其中 ,tbl_name 是表名； option 參數有 5個取值分別是quick 、fast 、 medium、 extended 、 changed各個選項的意思分別是quick: 不掃描行 , 不檢查錯誤的連接fast: 只檢查沒有被正確關閉的表medium:掃描行，以驗證被刪除的連接是有效的，也可以計算各行的關鍵字校驗和 , 并使用計算出的校驗和驗證這一點extended: 對每行的所有關鍵字進行一個全面的關鍵字查找. 這可以確保表是100

41、%一致的, 但是花的時間較長changed: 只檢查上次檢查后被更改的表和沒有被正確關閉的表option 只對 myisam表有效,對innodb 表無效.checktable 語句在執行過程中也會給表加上只讀鎖 .3、優化表mysql 中使用 optimizetable 語句來優化表. 該語句對 innodb 和myisam表都有效.但是,optimizetable 語句只能優化表中的varchar 、 blob 、 text 類型的字段optimizetable 語句的基本語法如下 :optimizelocal|no_write_to_binlogtabletbl_name,tbl_nam

42、 e.local 和 no_write_to_binlog 關鍵字的意義和分析表相同 , 都是指定不寫入二進制日志tbl_name 是表名通過 optimizetable 語句可以消除刪除和更新造成的文件碎片 .optimizetable 語句在執行過程中也會給表加上只讀鎖 .提示 : 一個表使用了 text 或者 blob 這樣的數據類型 , 如果已經刪除了表的一大部分, 或者已經對含有可變長度行的表（含有 varchar 、blob 或 text 列的表）進行了很多更新, 則應使用 optimizetable 來重新利用未使用的空間 , 并整理數據文件的碎片 . 在多數設置中 , 根本不需

43、要運行optimizetable.即使對可變長度的行進行了大量更新, 也不需要經常運行, 每周一次或每月一次即可, 并且只需要對特定表進行optimizetableoptimizetable 語句類似于 sqlserver 的重建索引和收縮數據文件的功能4、修復表mysql中使用 repairtable 來修復 myisam表，只對 myisam和archive 類型的表有效.repairlocal|no_write_to_binlogtabletbl_name,tbl_name.option.option=quick|extended|use_frm選項的意思分別是:quick: 最快的選項

44、 , 只修復索引樹.extended: 最慢的選項 , 需要逐行重建索引 .use_frm: 只有當 myi 文件丟失時才使用這個選項 , 全面重建整個索引 .與 analyzetable 一樣 ,repairtable 也可以使用 local 來取消寫入 binlog.5、checksum表數據在傳輸時, 可能會發生變化 , 也有可能因為其它原因損壞, 為了保證數據的一致，我們可以計算checksum （校驗值）.使用myisam引擎的表會把checksum存儲起來，稱為livechecksum, 當數據發生變化時,checksum 會相應變化 .語法如下 :checksumtabletbl

45、_name,tbl_name.quick|extendedquick:表示返回存儲的 checksum值extended: 表示重新計算checksum如果沒有指定選項 , 則默認使用 extended.checksum表主要用來對比在傳輸表數據之前和表數據之后，表的數據是否發生了變化 , 例如插入了數據或者刪除了數據, 或者有數據損壞checksum值都會改變.優化 mysql 服務器水電費優化 mysql 服務器主要從兩個方面入手, 一方面是對硬件進行優化；另一方面是對mysql 服務器的參數進行優化1、優化服務器硬件服務器的硬件性能直接決定著 mysql 數據庫的性能 . 硬件的性能瓶頸

46、直接決定mysql 數據庫的運行速度和效率.優化服務器硬件的幾種方法（ 1）配置較大的內存. 足夠大的內存, 是提高 mysql 數據庫性能之一 . 內存速度比磁盤i/o 快得多 , 可以通過增加系統緩沖區容量, 使數據庫在內存停留時間更長, 以減少磁盤i/o（ 2）配置高速磁盤系統, 以減少讀盤等待時間 , 提高響應速度（ 3）合理分布磁盤i/o, 把磁盤 i/o 分散在多個設備上 , 以減少資源競爭 , 提高并行操作能力（ 4）配置多處理器,mysql 是多線程的數據庫, 多處理器可同時執行多個線程2、優化mysql 的參數通過優化 mysql 的參數可以提高資源利用率, 從而達到提高my

47、sql 服務器的性能的目的 .mysql 服務器的配置參數都在f 或者 my.ini 文件的mysqld 組中 .下面對幾個對性能影響較大的參數進行介紹我們先看一下與網絡連接的性能配置項及對性能的影響. max_conecctions: 整個 mysql 允許的最大連接數；這個參數主要影響的是整個mysql 應用的并發處理能力 , 當系統中實際需要的連接量大于 max_conecctions 的情況下 , 由于 mysql 的設置限制 , 那么應用中必然會產生連接請求的等待, 從而限制了相應的并發量. 所以一般來說 , 只要 mysql 主機性能允許, 都是將該參數設置的盡可能大一點 . 一般

48、來說 500 到 800 左右是一個比較合適的參考值 max_user_connections: 每個用戶允許的最大連接數；上面的參數是限制了整個mysql 的連接數 , 而 max_user_connections 則是針對于單個用戶的連接限制. 在一般情況下我們可能都較少使用這個限制只有在一些專門提供mysql 數據存儲服務, 或者是提供虛擬主機服務的應用中可能需要用到. 除了限制的對象區別之外, 其他方面和max_connections 一樣 . 這個參數的設置完全依賴于應用程序的連接用戶數 , 對于普通的應用來說, 完全沒有做太多的限制 , 可以盡量放開一些 . net_buffer_

49、length: 網絡包傳輸中 , 傳輸消息之前的netbuffer 初始化大小；這個參數主要可能影響的是網絡傳輸的效率 , 由于該參數所設置的只是消息緩沖區的初始化大小 , 所以造成的影響主要是當我們的每次消息都很大的時候mysql 總是需要多次申請擴展該緩沖區大小 . 系統默認大小為 16kb, 一般來說可以滿足大多數場景 , 當然如果我們的查詢都是非常小 , 每次網絡傳輸量都很少 , 而且系統內存又比較緊缺的情況下 , 也可以適當將該值降低到8kb. max_allowed_packet: 在網絡傳輸中 , 一次傳消息輸量的最大值；這個參數與net_buffer_length相對應 , 只

50、不過是 netbuffer 的最大值 . 當我們的消息傳輸量大于 net_buffer_length 的設置時 ,mysql 會自動增大netbuffer 的大小 , 直到緩沖區大小達到max_allowed_packet所設置的值.系統默認值為1mb,最大值是1gb, 必須設定為 1024的倍數 , 單位為字節. back_log: 在 mysql 的連接請求等待隊列中允許存放的最大連接請求數. 連接請求等待隊列 , 實際上是指當某一時刻客戶端的連接請求數量過大的時候,mysql 主線程沒辦法及時給每一個新的連接請求分配（或者創建）連接線程的時候 , 還沒有分配到連接線程的所有請求將存放在一

51、個等待隊列中 ,這個隊列就是mysql 的連接請求隊列 . 當我們的系統存在瞬時的大量連接請求的時候, 則應該注意back_log 參數的設置. 系統默認值為50, 最大可以設置為 65535. 當我們增大back_log 的設置的時候, 同時還需要主義os 級別對網絡監聽隊列的限制 , 因為如果 os 的網絡監聽設置小于 mysql 的 back_log 設置的時候, 我們加大“ back_log ”設置是沒有意義的 . 上面介紹了網絡連接交互相關的主要優化設置 ,下面我們再來看看與每一個客戶端連接想對應的連接線程. 在 mysql中, 為了盡可提高客戶端請求創建連接這個過程的性能 , 實現

52、了一個threadcache 池 , 將空閑的連接線程存放在其中 , 而不是完成請求后就銷毀 . 這樣 , 當有新的連接請求的時候,mysql 首先會檢查threadcache 池中是否存在空閑連接線程, 如果存在則取出來直接使用, 如果沒有空閑連接線程, 才創建新的連接線程. 在 mysql 中與連接線程相關的系統參數及狀態變量說明如下 : thread_cache_size:threadcache 池中應該存放的連接線程數當系統最初啟動的時候, 并不會馬上就創建thread_cache_size 所設置數目的連接線程存放在threadcache 池中 , 而是隨著連接線程的創建及使用 ,

53、慢慢的將用完的連接線程存入其中 . 當存放的連接線程達到 thread_cache_size 值之后 ,mysql 就不會再續保存用完的連接線程了 . 如果我們的應用程序使用的短連接,threadcache 池的功效是最明顯的 . 因為在短連接的數據庫應用中 , 數據庫連接的創建和銷毀是非常頻繁的 , 如果每次都需要讓mysql 新建和銷毀相應的連接線程那么這個資源消耗實際上是非常大的 , 而當我們使用了 threadcache之后 , 由于連接線程大部分都是在創建好了等待取用的狀態, 既不需要每次都重新創建, 又不需要在使用完之后銷毀, 所以可以節省下大量的系統資源. 所以在短連接的應用系統

54、中 ,thread_cache_size 的值應該設置的相對大一些, 不應該小于應用系統對數據庫的實際并發請求數 . 而如果我們使用的是長連接的時候,threadcache 的功效可能并沒有使用短連接那樣的大, 但也并不是完全沒有價值. 因為應用程序即使是使用了長連接, 也很難保證他們所管理的所有連接都能處于很穩定的狀態, 仍然會有不少連接關閉和新建的操作出現. 在有些并發量較高，應用服務器數量較大的系統中，每分鐘十來次的連接創建與關閉的操作是很常見的 . 而且如果應用服務器的連接池管理不是太好 , 容易產生連接池抖動的話, 所產生的連接創建和銷毀操作將會更多 . 所以即使是在使用長連接的應用

55、環境中 ,threadcache 機制的利用仍然是對性能大有幫助的 . 只不過在長連接的環境中我們不需要將 thread_cache_size 參數設置太大, 一般來說可能50到 100之間應該就可以了 . thread_stack: 每個連接線程被創建的時候,mysql給他分配的內存大小 . 當 mysql 創建一個新的連接線程的時候, 是需要給他分配一定大小的內存堆棧空間 , 以便存放客戶端的請求query以及自身的各種狀態和處理信息 . 不過一般來說如果不是對mysql 的連接線程處理機制十分熟悉的話, 不應該輕易調整該參數的大小 , 使用系統的默認值(192kb)基本上可以所有的普通應

56、用環境.如果該 值設置太小 , 會影響 mysql 連接線程能夠處理客戶端請求的 query 內 容的大小 , 以及用戶創建的 procedures 和 functions 等計算出系統 新建連接連接的 threadcache 命中率 , 也就是通過threadcache 池中取得連接線程的次數與系統接收的總連接次數的比率, 如下 :threads_cache_hit=(connections- threads_created)/connections*100% 我們可以通過上面的這個運算 公式計算一下上面環境中的 threadcache 命中率:thread_cache_hit=(127-12)/127*100%=90.55%一般來說，當系統穩定運行一段時間之后 , 我們的 threadcache 命中率應該保持在 90% 左右甚至更高的比率才算正常. 可以看出上面環境中的 threadcache命中比率基本還算是正常的.tablecache 相關的優化我們先來看一下 mysql 打開表的相關機制. 由于多線程的實現