如何讓MySQL保持高效的N個好習慣（47頁）.pdf

1、葉金榮-知數堂培訓聯合創始人2016.10.15高效MySQL的N個習慣MySQL如何保持高效運行？N個好習慣 用好硬件 讓OS跑得快 DDL、SQL寫的好 運維習慣好 其他好習慣時延用更好的硬件 用更好的CPU 主頻高，讓每個SQL處理時間更快，減少等待（L1/L2/L3）cache大，每次CPU計算速率更快 線程多，同時支持更多并發SQL，提高TPS同時 記得關閉NUMA并設置為最大性能模式 MySQL 5.6.27后，增加innodb_numa_interleave選項用更好的硬件 用更好的內存 主頻高，內存讀寫速率更高，更高吞吐，更低時延 內存大，更多數據在內存中，減少直接磁盤讀寫，提

2、高TPS用更好的硬件 用更好的磁盤 通常來說，磁盤I/O是最大的瓶頸 如果是機械盤，一定要配陣列卡，以及陣列卡的CACHE&BBU 并且使用（FORCE）WB策略 最好是選用SSD或者PCIe SSD，iops可以提升成千上萬倍用更好的硬件 用更好的網卡/網絡 文件傳輸速率高，異地文件備份更快 主從數據復制數據傳輸時延更小 適合大數據量的分布式存儲環境 老版本內核中，網絡請求太高時會引發中斷瓶頸，建議升級內核 多個網卡可以進行綁定，提高傳輸速率并能提高可用性讓OS跑的更快 關閉無用服務 減少系統開銷 避免潛在安全隱患讓OS跑的更快 盡可能使用本地高速存儲 堅決不使用nfs 除非是基于SSD的高

3、速網絡分布式存儲 用于備份場景除外讓OS跑的更快 讓數據庫跑在專用的服務器上，不混搭 性能上不相互影響 提高安全性 必須混撘時要做好權限管理以及安全隔離讓OS跑的更快 io scheduler 選擇deadline、noop，堅決不能用cfq讓OS跑的更快 文件系統選擇 優先選用xfs 或 ext4（rhel 7及以上，xfs已是默認fs）zfs/btrfs比較小眾 堅決不用ext3灰色為遮擋區域，排版請注意。讓OS跑的更快 其他內核選項 vm.swappiness 10 降低使用swap的概率 內核2.6.32-303及以上版本，慎重設置為0，可能引發OOM讓OS跑的更快 其他內核選項 vm

4、.dirty_ratio 5 vm.dirty_background_ratio 10 避免因為io壓力瞬間飆升導致內核進程卡死，os hung住INFO:task jbd/dm-0-8:389 blocked for more than 120 seconds.echo 0 /proc/sys/kernel/hung_task_timeout_secs disable this message.INFO:task flush-253:0:1324 blocked for more than 120 seconds.echo 0 /proc/sys/kernel/hung_task_timeo

5、ut_secs disable this message.DDL、SQL寫得好 一定要有主鍵（PRIMARY KEY）沒有主鍵會怎樣 數據多次讀寫后可能更離散，有更多隨機I/O MySQL復制環境中，如果選擇RBR模式，沒有主鍵的update需要讀全表，導致復制延遲DDL、SQL寫得好 一定要有主鍵（PRIMARY KEY）好的主鍵特點 沒有業務用途 數值呈連續增長，最好是自增 堅決不能選用CHAR/UUID等類型DDL、SQL寫得好 關于數據長度 夠用前提下，越短越好 消耗更少的存儲空間 需要進行排序時，消耗更少的內存空間 例如用INT UNSIGNED存儲IPV4地址，不用CHAR(15)

6、類型 案例：11個字符長度的數值，bigint vs char(120)vs char(11)，1萬條記錄，Logical_read：111 vs 1170 vs 224DDL、SQL寫得好 適當使用TEXT/BLOB類型 data page默認16KB 每行長度超過8KB時，就需要分裂data page 產生更多離散I/O 案例：一個100G的表拆分成4個表后，總大小僅25GDDL、SQL寫得好 每個表增加create_time、update_time兩個字段 分別表示寫入時間以及最后更新時間 業務上可能用不到，但是對日常運維管理則非常有用 可以用來判斷哪些是可以歸檔的老數據，定期進行歸檔

7、用來做自定義的差異備份也很方便DDL、SQL寫得好 索引很重要 InnoDB行鎖是基于索引實現 如果沒有索引，將會是災難性的 讀取時，全表掃描 修改時，全表記錄鎖DDL、SQL寫得好 索引設計 基數（cardinality）低的字段一般沒必要建立單列索引單列索引 字符型字段上建索引時優先采用部分索引（prefix index）5.6.9之后，optimizer能識別到普通索引同時存儲主鍵值，無需顯式定義加上主鍵列（Index Extensions）優先多列聯合索引（multi column index），少用單列索引DDL、SQL寫得好 怎么算是好SQL 所有WHERE條件都加上引號 避免潛在

8、的類型隱式轉換風險 避免個別條件失效時SQL語法錯誤DDL、SQL寫得好 怎么算是好SQL 不SELECT*減少不必要的I/O 提高可以利用覆蓋索引的幾率DDL、SQL寫得好 怎么算是好SQL 避免SQL注入風險 所有用戶輸入值都要做過濾 利用PREPARE做預處理 利用SQL_MODE做限制DDL、SQL寫得好 怎么算是好SQL LIKE查詢時，不要用%通配符最左前導（無法使用索引）能UNION ALL就不要UNION（UNION需要去重，會產生臨時表）SQL中最好不要有運算 WHERE子句中，不要有函數DDL、SQL寫得好 關于JOIN 滿足業務需求前提下盡量用inner join，讓優化

9、器自動選擇驅動表 有時候優化器選擇的驅動表未必是最優的，可以嘗試手動調整 最后的排序字段如果不在驅動表中，則會有filesortDDL、SQL寫得好糟糕的SQLUPDATE t SET c2=?AND c3=?WHERE c1=?=UPDATE t SET c2=?,c3=?WHERE c1=?DDL、SQL寫得好糟糕的SQLUPDATE t SET c2=?WHERE c1=?=UPDATE t SET c2=?WHERE c1=?DDL、SQL寫得好糟糕的SQLSELECT*FROM t WHERE c1=?=SELECT c2,c3 FROM t WHERE c1=?DDL、SQL寫得好

10、糟糕的SQLSELECT c2,c3 FROM t WHERE c4 like%?%=SELECT c2,c3 FROM t WHERE c1=?AND c1 SELECT c2,c3 FROM t WHERE c1=2016-10-15 AND c1 SELECT c2,c3 FROM t WHERE char_col=int_valueDDL、SQL寫得好 關于EXPLAIN 關鍵業務SQL上線前，都要EXPLAIN確認其執行計劃 或提前分析slow query log，防患未然 EXPLAIN中如果有Using temporary、Using filesort、或type=ALL時，盡量

11、想辦法進行優化運維習慣好 存儲引擎 拋棄MyISAM，InnoDB為王 適當的場景下可以采用TokuDB 誤區：MEMORY可不見得就快運維習慣好 關閉QUERY CACHE 絕大多數情況下雞肋，最好關閉 QC鎖是全局鎖，每次更新QC的內存塊鎖代價高，出現Waiting for query cache lock狀態的頻率很高 實例啟動前設置query_cache_type=0&query_cache_size=0 參考：http:/ http:/ 使用獨立undo表空間 避免ibdata1文件存儲空間暴漲 MySQL 5.6開始支持獨立表空間 MySQL 5.7還可以回收已經purge的表空間

12、 提高file i/o能力，并適當增加purge線程數 innodb_purge_threads 事務及時提交，不要積壓。并且默認打開autocommit=1運維習慣好 啟用thread pool 應對突發短連接 extra port 沒thread pool怎么辦 想辦法啟用連接池或其他替代方案 適當調低超時閾值，減少空閑連接運維習慣好 幾個關鍵選項 innodb_buffer_pool_size，約物理內存的50%70%innodb_log_file_size，5.5及以上2G+，5.5以下建議不超512M innodb_flush_log_at_trx_commit，0=最快數據最不安全

13、，1=最慢最安全，2=折中 innodb_max_dirty_pages_pct，25%50%為宜 max_connections，突發最大連接數的80%為宜，過大容易導致全部卡死其他好習慣 啟用輔助監控機制 干掉超過N秒的SQL 干掉疑似注入SQL 干掉長時間不活躍的sleep連接其他好習慣 online ddl 優先用pt-osc 但不見得一定要用pt-osc 尤其是5.6以后對online ddl有了很大提升改善其他好習慣 刪除大表 不要真的刪除，而是先rename 確認對業務真的沒有影響 再用硬連接的方法物理刪除，效率更高其他好習慣 autocommit 避免某些行鎖被長時間持有，影響tps 更嚴重時，可能連接數暴漲，導致整個實例掛掉 采用gui客戶端連接時，記得及時關閉連接，或設置超時閾值以及自動提交，否則容易發生行鎖等待問題