【PDF】高效Mysql的N個(gè)習(xí)慣-阿里巴巴技術(shù)文檔

高效MySQL的N個(gè)習(xí)慣葉金榮-知數(shù)堂培訓(xùn)聯(lián)合創(chuàng)始人MySQL如何保持高效運(yùn)行?N個(gè)好習(xí)慣用更好的硬件-主頻高，讓每個(gè)SQL處理時(shí)間更快，減少等待-線程多，同時(shí)支持更多并發(fā)SQL,提高TPS同時(shí)-記得關(guān)閉NUMA并設(shè)置為最大性能模式云棲社區(qū)云棲社區(qū)用更好的硬件-主頻高，內(nèi)存讀寫速率更高，更高吞吐，更低時(shí)延-內(nèi)存大，更多數(shù)據(jù)在內(nèi)存中，減少直接磁盤讀寫，提高TPS用更好的硬件-通常來說，磁盤I/0是最大的瓶頸-如果是機(jī)械盤，一定要配陣列卡，以及陣列卡的CACHE&BBU-最好是選用SSD或者PCleSSD,iops可以提升成千上萬倍會(huì)會(huì)云棲社區(qū)用更好的硬件-文件傳輸速率高，異地文件備份更快-主從數(shù)據(jù)復(fù)制數(shù)據(jù)傳輸時(shí)延更小-適合大數(shù)據(jù)量的分布式存儲(chǔ)環(huán)境-老版本內(nèi)核中，網(wǎng)絡(luò)請(qǐng)求太高時(shí)會(huì)引發(fā)中斷瓶頸，建議升級(jí)內(nèi)核-多個(gè)網(wǎng)卡可以進(jìn)行綁定，提高傳輸速率并能提高可用性讓OS跑的更快-減少系統(tǒng)開銷-避免潛在安全隱患讓OS跑的更快-堅(jiān)決不使用nfs-除非是基于SSD的高速網(wǎng)絡(luò)分布式存儲(chǔ)-用于備份場(chǎng)景除外-性能上不相互影響-提高安全性-必須混搭時(shí)要做好權(quán)限管理以及安全隔離THECOMPUTINGCONFERENCE品time>time>-zfs/btrfs比較小眾-降低使用swap的概率-內(nèi)核2.6.32-303及以上版本，慎重設(shè)置為0,可能引發(fā)OOMINFO:taskjbd/dm-0-8:389blockedformorethan120seconds."echo0>/proc/sys/kernel/hungtasktimeoutsecs"disablethismessage.INFO:taskflush-253:0:1324blockedformorethan120seconds.echo0>/proc/sys/kernel/hungtasktimeoutsecs"disablethismessage.-數(shù)據(jù)多次讀寫后可能更離散，有更多隨機(jī)I/0-沒有業(yè)務(wù)用途-數(shù)值呈連續(xù)增長(zhǎng)，最好是自增-堅(jiān)決不能選用CHAR/UUID等類型-夠用前提下，越短越好-消耗更少的存儲(chǔ)空間-需要進(jìn)行排序時(shí)，消耗更少的內(nèi)存空間-每行長(zhǎng)度超過8KB時(shí)，就需要分裂data產(chǎn)生更多離散I/0一案例：一個(gè)100G的表拆分成4個(gè)表后，總大小僅25G-分別表示寫入時(shí)間以及最后更新時(shí)間-業(yè)務(wù)上可能用不到，但是對(duì)日常運(yùn)維管理則非常有用-可以用來判斷哪些是可以歸檔的老數(shù)據(jù)，定期進(jìn)行歸檔-用來做自定義的差異備份也很方便-InnoDB行鎖是基于索引實(shí)現(xiàn)-如果沒有索引，將會(huì)是災(zāi)難性的-基數(shù)(cardinality)低的字段一般沒必要建立單列索引-字符型字段上建索引時(shí)優(yōu)先采用部分索引(prefixindex)-優(yōu)先多列聯(lián)合索引(multicolumnindex),少用單列索引-避免潛在的類型隱式轉(zhuǎn)換風(fēng)險(xiǎn)-避免個(gè)別條件失效時(shí)SQL語(yǔ)法錯(cuò)誤-減少不必要的I/0-提高可以利用覆蓋索引的幾率-避免SQL注入風(fēng)險(xiǎn)-所有用戶輸入值都要做過濾-LIKE查詢時(shí)，不要用%通配符最左前導(dǎo)(無法使用索引)-能UNIONALL就不要UNION(UNION需要去重，會(huì)產(chǎn)生臨時(shí)表)-滿足業(yè)務(wù)需求前提下盡量用innerjoin,讓優(yōu)化器自動(dòng)選擇驅(qū)動(dòng)表-有時(shí)候優(yōu)化器選擇的驅(qū)動(dòng)表未必是最優(yōu)的，可以嘗試手動(dòng)調(diào)整糟糕的SQL____-關(guān)鍵業(yè)務(wù)SQL上線前，都要EXPLAIN確認(rèn)其執(zhí)行計(jì)劃盡量想辦法進(jìn)行優(yōu)化運(yùn)維習(xí)慣好-適當(dāng)?shù)膱?chǎng)景下可以采用TokuDB運(yùn)維習(xí)慣好-絕大多數(shù)情況下雞肋，最好關(guān)閉-QC鎖是全局鎖，每次更新QC的內(nèi)存塊鎖代價(jià)高，出現(xiàn)Waitingfor-實(shí)例啟動(dòng)前設(shè)置querycachetype=0&querycachesize=0運(yùn)維習(xí)慣好-MySQL5.6開始支持獨(dú)立表空間-MySQL5.7還可以回收已經(jīng)purge的表空間-事務(wù)及時(shí)提交，不要積壓。并且默認(rèn)打開autocommit=1運(yùn)維習(xí)慣好運(yùn)維習(xí)慣好-maxconnections,突發(fā)最大連接數(shù)的80%為宜，過大容易導(dǎo)致全-干掉超過N秒的SQL-干掉疑似注入SQL-干掉長(zhǎng)時(shí)間不活躍的sleep連接其他好習(xí)慣-但不見得一定要用pt-osc-不要真的刪除，而是先rena