MYSQL-innodb性能優化學習總結BSS測試部：newhackerman

數據庫參數 MYSQL數據庫的參數配置一般在my.ini配置文件中修改/添加（部分參數也可以用setglobal參數名=值做臨時調整，重啟后失效），配置完后需要重啟數據庫才生效。參數1：innodb_buffer_pool_size=Gb/MB說明：此參數類似于oracle的SGA配置，當主機做為mysql數據庫服務器時，一般配置為整機內存的60%~80%。參數2：innodb_buffer_pool_instances=N說明：內存緩沖池實例數，將innodb_buffer_pool_size配置的內存分割成N份，此參數當配置內存大小于1G時才生效，當數據庫有多個會話進行數據庫操作時，用于并行在多個內存塊中處理任務，一般配置值《=服務器CPU的個數。參數3：max_connections=2000說明：最大連接數，當數據庫面對高并發時，這個值需要調節為一個合理的值，才滿足業務的并發要求，避免數據庫拒絕連接。參數4：max_user_connections=1000說明：設置單個用戶的連接數。參數5：innodb_log_buffer_size=32M說明：日志緩沖區大小,一般不用設置太大，能存下1秒鐘操作的數據日志就行了，mysql默認1秒寫一輪詢寫一次日志到磁盤。參數6：innodb_flush_log_at_trx_commit說明：（這個配置很關鍵）一般的實時業務交易配置為2，取值0,1,20:數據操作時，直接寫內存，并不同時寫入磁盤；2:數據操作時，直接寫內存，并不同時寫入磁盤；1:就每個事務提交就會要刷新到磁盤后才算提交完成,這種情況是保證了事務的一致性,但性能會有很大的影響。0與2的區別：0：當mysql掛了之后，可能會損失前一秒的事務信息2：當mysql掛了之后，如果系統文件系統沒掛，不會有事務丟失。參數7：innodb_read_io_threads=16說明：數據庫讀操作時的線程數，用于并發。參數8：innodb_write_io_threads=16說明：數據庫寫操作時的線程數，用于并發。參數9：innodbfilepertable=1說明：每一個表是否使用獨立的數據表空間，默認為OFF（使用共享表空間），一般建議配置為1，InnoDB默認會將所有的數據庫InnoDB引擎的表數據存儲在一個共享空間中：（ibdata1），這樣就感覺不爽，增刪數據庫的時候，ibdata1文件不會自動收縮，單個數據庫的備份也將成為問題。通常只能將數據使用mysqldump導出，然后再導入解決這個問題。共享表空間在Insert操作上少有優勢。其它都沒獨立表空間表現好，如果數據庫基本上都插入操作則配置為0。參數10：innodb_stats_on_metadata={OFF|on}說明：是否動態收集統計信息，開啟時會影響數據庫的性能(一般關閉，找個時間手動刷新，或定時刷新）如果為關閉時，需要配置數據庫調度任務，定時刷新數據庫的統計信息。參數11：innodb_spin_wait_delay=10說明：控制CPU的輪詢時間間隔，默認是6,配置過低時，任務調度比較頻繁，會消耗CPU資源。參數12：innodb_lock_wait_timeout=30說明：控制鎖的超時時間，默認為50，這個值要注意，如果有特殊業務確實要耗時較長時，不能配置太短。執行計劃分析explainsql語句：mysql>explainselect*fromtest;或：explainselect*fromtest\G;--字段解釋：輸入explainselect*fromcustomer;后，出現一張表，個行的意思如下：table－顯示此行數據屬于哪張表；type－重要的一列，顯示使用了何種連接，從好到差依次為const、eq_ref、ref、range、index、all，下面詳細說明：type的描述：system－表只有一行，這是const連接類型的特例；const－表中一個記錄的最大值能夠匹配這個查詢(索引可以是主鍵或唯一索引)。因為只有一行，這個值實際就是常數，因為mysql先讀這個值，再把它當作常數對待eq_ref－從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄。在查詢使用索引為主鍵或唯一索引的全部時使用；ref－只有使用了不是主鍵或唯一索引的部分時發生。對于前面表的每一行聯合，全部記錄都將從表中讀出，這個連接類型嚴重依賴索引匹配記錄的多少－越少越好；range－使用索引返回一個范圍中的行，比如使用>或<查找時發生；index－這個連接類型對前面的表中的每一個記錄聯合進行完全掃描(比all好，因為索引一般小于表數據)；all－這個連接類型多前面的表中的每一個記錄聯合進行完全掃描，這個比較糟糕，應該盡量避免。possible_keys－可以應用在這張表中的索引，如果為null，則表示沒有可用索引；key－實際使用的索引，如為null，表示沒有用到索引；key_len－索引的長度，在不損失精確度的情況下，越短越好；ref－顯示索引的哪一列被使用了，如果可能的話，是個常數；rows－返回請求數據的行數；extra－關于mysql如何解析查詢的額外信息，下面會詳細說明。extra行的描述：distinct－mysql找到了域行聯合匹配的行，就不再搜索了；notexists－mysql優化了leftjoin，一旦找到了匹配leftjoin的行，就不再搜索了；rangecheckedforeach－沒找到理想的索引，一次對于從前面表中來的每一個行組合；record(indexmap:#)－檢查使用哪個索引，并用它從表中返回行，這是使用索引最慢的一種；usingfilesort－看到這個就需要優化查詢了，mysql需要額外的步驟來發現如何對返回的行排序。他根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行.usingindex－列數據是從單使用了索引中的信息而沒有讀取實際行的表返回的，這發生在對表的全部的請求列都是同一個索引時；usingtemporary－看到這個就需要優化查詢了，mysql需要創建一個臨時表來查詢存儲結果，這通常發生在多不同的列表進行orderby時，而不是groupby；whereused－使用了where從句來限制哪些行將與下一張表匹配或是返回給用戶。如不想返回表中用的全部行，并連接類型是all或index，這就會發生，也可能是查詢有問題。SQL語句優化：注意SQL語句的書寫規則，where條件，orderby,groupby,having,in,like,jionon,表順序，聚合函數的使用，子查詢等。索引優化1。是否有無重復索引2。索引字段類型，順序是否合理3。是否有無用索引4。索引利用率表結構優化1.表的字段類型是否合理2。數據是否冗余3。根據業務規則建立合理的約束4。建表時，盡量使字段值不為空(加notnull約束)，索引列值盡量離散（不重復）5。不常用的字段列可適當考慮折分表6。數據量較大的表，有存儲時間，IP地址數據時，轉為int,bigintINT類型的時間數據轉換：UNIX_TIMESTAMP('2015-01-1012:00:00')轉int（插入數據時）FROM_UNIXTIME(時間字段)取時間字段的值IP地址數據操作轉換：INET_ATON:IP地址轉bigint（inet_aton(192.168.1.1)）INET_NTOA:BIGINT轉IP地址（inet_ntoa(ip地址字段)）在mysql中int,比varchar處理要簡單，盡量少使用text類型數據庫主機參數優化網絡：（/etc/security/sysctl.conf）net.ipv4.tcp_keepalive_time=1200說明：表示當keepalive起用的時候，TCP發送keepalive消息的頻度。缺省是2小時，改為20分鐘。net.ipv4.ip_local_port_range=1000065000說明：表示用于向外連接的端口范圍,一般低位端口不要設置太低，有可能會用到其它程序固定的端口net.ipv4.tcp_max_syn_backlog=65535說明：表示SYN隊列的長度，默認為1024，加大隊列長度為65535，可以容納更多等待連接的網絡連接數。net.ipv4.tcp_max_tw_buckets=5000說明：示系統同時保持TIME_WAIT的最大數量，如果超過這個數字，TIME_WAIT將立刻被清除并打印警告信息。默認為180000，避免被大量的timewait拖死。net.ipv4.tcp_syncookies=1說明：表示開啟SYNCookiesnet.ipv4.tcp_tw_reuse=1說明：表示開啟重用,允許將TIME-WAITsockets重新用于新的TCP連接net.ipv4.tcp_recycle=1說明：表示開啟TCP連接中TIME-WAITsockets的快速回收，默認為0，表示關閉；net.ipv4.tcp_fin_timeout=10說明：修改系統默認的TIMEOUT時間。limit.conf內核相關參數優化： 詳見：《limits.conf詳解》 數據庫參數調節效果對比下列參數配置更改前與更改后在筆記本電腦上的測試結果對比。innodb_buffer_pool_size=1024Minnodb_buffer_pool_instances=4max_connections=2000max_u