1、MySQL開發(fā)規(guī)范和平臺簡介卓汝林About Me 2011 2012 就職于惠普, Java工程師 2012 2013 就職于58，MySQL和MongoDB DBA 2013 至今 就職于小米，MIUI、小米互娛等MySQL和Redis的運(yùn)營管理；關(guān)注自動化運(yùn)維設(shè)計(jì)和開發(fā) RogerZhuoAgendeMySQL開發(fā)規(guī)范簡介MySQL部分常用平臺簡介Redis Cluster的簡介MySQL開發(fā)規(guī)范簡介基礎(chǔ)規(guī)范庫表設(shè)計(jì)規(guī)范索引設(shè)計(jì)規(guī)范SQL規(guī)范基礎(chǔ)規(guī)范l 統(tǒng)一使用utf8字符集和utf8_general_ci字符排序規(guī)則，按需對表級設(shè)置utf8mb4l 表存儲引擎使用InnoDB; 默認(rèn)使

2、用REPEATABLE-READ事務(wù)隔離級別l 統(tǒng)一命名規(guī)范：默認(rèn)全小寫，禁用關(guān)鍵字和合理使用前綴l 禁止使用存儲過程和函數(shù)、視圖、觸發(fā)器、外鍵約束和Eventl 各環(huán)境進(jìn)行隔離，避免混用風(fēng)險：dev-test-staging-productionl 生產(chǎn)數(shù)據(jù)安全隱私為首重，要求項(xiàng)目數(shù)據(jù)閉環(huán)和數(shù)據(jù)范圍可控制庫表設(shè)計(jì)規(guī)范l 單實(shí)例表個數(shù)控制在3w以內(nèi),單表行數(shù)500w1000w內(nèi)l 某類分表個數(shù)控制在1000，避免過度拆分(DDL操作痛苦)l 采用統(tǒng)一的分庫分表原則，二進(jìn)制或十進(jìn)制l 禁用MySQL自帶分區(qū)表l 每個表必須設(shè)置主鍵， 盡量使用數(shù)值類型和增長趨勢l 核心業(yè)務(wù)表避免使用自增列，使用外

3、部ID生成器l 字段設(shè)置原則：Simple is good, Smaller is usually betterl 字段和類型從產(chǎn)品角度按需設(shè)置，禁用預(yù)留字段，避免存儲浪費(fèi)l 字段都設(shè)置not null和default值索引設(shè)計(jì)規(guī)范l InnoDB表主鍵的選擇策略-每個表必須顯示指定主鍵(RBR)-主鍵盡量用單字段，最好是數(shù)值類型-主鍵值與插入順序，盡量同步 禁用uuid(rebanlance和fragment)-禁止更新主鍵值 (邏輯定義就不合理)l 單表索引數(shù)建議別超5個，每個組合索引別超過5個字段l 盡量使用組合索引，避免單索引的index merge或回表操作l 注意組合索引的順序，最

4、左原則，根據(jù)SQL條件調(diào)整索引字段順序l 避免使用冗余索引，按需創(chuàng)建索引SQL規(guī)范l Where子句右值都用引號括起，避免隱式類型轉(zhuǎn)換l 多表Join時注意比較字段類型一致，避免隱式類型轉(zhuǎn)換l 禁止對索引列進(jìn)行函數(shù)和數(shù)值計(jì)算l Select/insert都枚舉字段名，禁用select *l 避免使用大SQL、大事務(wù)，或事務(wù)中等待用戶動作行為大事務(wù)/SQL會導(dǎo)致主從復(fù)制延時MySQL的單個SQL只能在一個CPU上運(yùn)行影響數(shù)據(jù)庫的并發(fā)性能，事務(wù)持有的鎖等資源只在事務(wù)rollback/commit時才能釋放存在比較長的readview(快照），如果持續(xù)時間幾十分鐘，數(shù)據(jù)庫響應(yīng)時間可能聚降 （mvcc

5、,undo)l 禁用update/delete limit N結(jié)構(gòu)SQLl 使用where in()，in子句中元素建議小于500運(yùn)營規(guī)范-舉例一二l 故障預(yù)案管理l 故障管理- postmortem機(jī)制MySQL部分常用平臺簡介監(jiān)控和告警-Open Falcon備份和還原-Xtrabackup Manager慢查詢報表-Anemometer高可用 MHA中間件 MariaDB MaxScale自動審核系統(tǒng) Qunar Inception自動巡檢系統(tǒng)XiaoMi DBaaS監(jiān)控和告警 Zabbix監(jiān)控方案Zabbix + Fromdual mysql-performance-monitorZa

6、bbix + Percona Monitoring Plugins監(jiān)控和告警 Zabbix監(jiān)控監(jiān)控和告警-Open-FalconOpen-Falcon 是小米運(yùn)維部開源的一款互聯(lián)網(wǎng)企業(yè)級監(jiān)控系統(tǒng)解決方案.項(xiàng)目首頁：http:/open-使用公司：https:/ 監(jiān)控和告警-Open-Falcon 監(jiān)控和告警-Open-FalconDashboard首頁監(jiān)控和告警-Open-Falcon vs Zabbix 告警方面 告警模板支持繼承的同時支持覆蓋策略項(xiàng) Tag化描述告警策略 與服務(wù)樹結(jié)合 實(shí)例上下線自動變更監(jiān)控 水平擴(kuò)展監(jiān)控和告警-Open-FalconOpen-Falcon MySQL指標(biāo)采

7、集-mymon項(xiàng)目地址: https:/ 系統(tǒng)Falcon自帶Agent單機(jī)采集400+指標(biāo):CPU, 磁盤, 內(nèi)存, swap,網(wǎng)絡(luò)相關(guān), IO,負(fù)載機(jī)器內(nèi)核參數(shù)，netstat采集等 存活MySQL_Alive和響應(yīng)時間性能MySQL和InnoDB狀態(tài)值采集300+指標(biāo)復(fù)制MySQL復(fù)制狀態(tài)監(jiān)控監(jiān)控和告警-Open-FalconMymon監(jiān)控告警項(xiàng) 告警項(xiàng)目影響描述故障級別處理方式告警方式MySQL is down MySQL掛了，不能提供服務(wù)P0立即通報&處理短信&郵件MySQL connection is full 達(dá)到連接數(shù)的上限，無法再創(chuàng)建新數(shù)據(jù)連接P1立即通報&a

8、mp;處理短信&郵件MySQL Is BusyMySQL并發(fā)處理連接數(shù)比較高，整體查詢響應(yīng)時間變長p1立即通報&處理短信&郵件Slave lagging behind Master從庫延時，影響從庫只讀一致性； HA架構(gòu)的切換有影響P1立即通報&處理短信&郵件IO thread stopped從庫不能正常同步，影響從庫只讀業(yè)務(wù)一致性；HA架構(gòu)的切換有影響P1立即通報&處理短信&郵件SQL thread stopped從庫不能正常同步，影響從庫只讀業(yè)務(wù)一致性；HA架構(gòu)的切換有影響P1立即通報&處理短信&郵件Slave is

9、NOT read only從庫非只讀，可能導(dǎo)致業(yè)務(wù)數(shù)據(jù)寫入從庫，P1立即通報&處理短信&郵件導(dǎo)致主從數(shù)據(jù)庫腦裂，很難merge,可能引起用戶數(shù)據(jù)丟失InnoDB Deadlock detected導(dǎo)致沖突的事務(wù)回滾，影響程序并發(fā)p2及時通報&處理短信&郵件Slow queries high 說明數(shù)據(jù)庫響應(yīng)程序請求過慢，造成用戶檢驗(yàn)差，過長慢查詢可能導(dǎo)致超時被killp2及時通報&處理短信&郵件Table open cache too small對表操作，有一定的性能影響，不太明顯p2及時通報&處理短信&郵件Net Interfac

10、e Traffic IS High (90M)網(wǎng)絡(luò)IO比較高，查詢響應(yīng)明顯變慢，超時變多CPU Idle is too low16)此時數(shù)據(jù)庫處理能力下降P1立即通報&處理短信&郵件disk.io.util (90%)IO響應(yīng)可能開始變慢p2立即通報&處理短信&郵件 Free disk space is less than 5% on volume (/和數(shù)據(jù)目錄）磁盤空間有滿了的風(fēng)險，如果占滿，MySQL無寫再入數(shù)據(jù)，基本掛死 P1立即通報&處理短信&郵件Free disk space is less than 20% on volume(/和

11、數(shù)據(jù)目錄）磁盤存儲空間需要擴(kuò)容p2及時處理短信&郵件Lack of available memory on server (5%)MySQL已經(jīng)開始使用swap, 某些查詢的數(shù)據(jù)落到swap的，形成si/so響應(yīng)時間明顯增大。如果swap也使用完，MySQL有被OOM Killer的危險p2及時處理短信&郵件監(jiān)控和告警-Open-FalconOpen-Falcon Redis指標(biāo)采集-redismon項(xiàng)目地址: https:/ 備份和還原數(shù)據(jù)備份重要性- 案例 2006年Couchsurfing 沙發(fā)旅行，因MySQL故障，且無binlog和備份，導(dǎo)致創(chuàng)業(yè)三年多公司直接關(guān)閉 2

12、009年Journal Space經(jīng)歷6年公司，由于Raid故障且無備份，掉了所有用戶數(shù)據(jù)，停止運(yùn)營 2013年下廚房由于數(shù)據(jù)庫升級和誤操作，最終各層災(zāi)難恢復(fù)，經(jīng)過1周恢復(fù)出99%的數(shù)據(jù)備份和還原關(guān)鍵因素 RPORTO容災(zāi)成本影響備份和還原 數(shù)據(jù)備份有效性加密壓縮分布式存儲數(shù)據(jù)還原延時從一致性校驗(yàn)基礎(chǔ)流程數(shù)據(jù)備份 1 全量備份：Percona XtraBackup2 binlog差異備份：mysqlbinlog實(shí)時遠(yuǎn)程備份分布式存儲 單服務(wù)器單數(shù)據(jù)中心單地區(qū)備份系統(tǒng) 慢查詢報表 項(xiàng)目首頁：https:/ 慢查詢?nèi)罩?+ pt-query-digest分析高可用 Uptime, Downtime

13、, Nine s90% 1 個9 36.5 天/ 年99% 2 個9 3.65 天/ 年99.9% 3 個個9 8.76 小時小時/ 年年99.99%4 個個9 52 分鐘分鐘/ 年年99.999% 5 個9 5 分鐘/ 年99.9999% 6 個9 31 秒 / 年高可用-MHA MHA for MySQL: Master High AvailabilityManager tools for MySQL項(xiàng)目首頁：http:/ server defaultserver defaultmanager_workdirmanager_log=user=mha_userpassword=pwdport

14、=3308repl_user=repl_password=ssh_user=rootmaster_binlog_dirremote_workdirping_interval=15ping_type=SELECTserver1server1hostname=idc1-host1port=3308candidate_master=1server2server2hostname=idc1-host2port=3308candidate_master=1server3server3hostname=idc2-host2port=3308no_master=1ignore_fail=1中間件 MySQL

15、中間件的功能定位自動分庫分表自動讀寫分離 MySQL Local AuthenticationSQL流量過濾防火墻限流 只讀負(fù)載均衡 無明顯性能損耗 只讀權(quán)重 從庫故障下線 主庫自動FailOver 自身高可用擴(kuò)展性安全高性能高可用中間件-MaxScale MaxScale: 是MariaDB Corporation開源的基于MySQL協(xié)議的智能中間件平臺項(xiàng)目首頁：https:/ MaxScale: 是MariaDB Corporation開源的基于MySQL協(xié)議的智能中間件平臺項(xiàng)目首頁：https:/ 審核上線為什么要自動化？背景數(shù)據(jù)庫變更操作(DML和DDL) 700+次/月，曾經(jīng)回58個

16、郵件/天，多次溝通協(xié)議一次收到40000行的SQL文件，人肉審核？每次DML操作，需對變更結(jié)果集手動備份每次DDL不合規(guī)范，打回后都要溝通和說明業(yè)務(wù)發(fā)希望1小時時間窗口內(nèi)，數(shù)據(jù)庫變更完成，業(yè)務(wù)功能上線DML操作where有誤，導(dǎo)致數(shù)據(jù)訂正出錯，需馬上回滾受影響的行“人肉審核”的問題DBA很累，時間和精力消耗大，我們希望DBA把80%時間投入到另外20%最有價值的工作中審核效率低錯誤率高過度依賴于個人，有主觀性（技術(shù)能力和職業(yè)素養(yǎng)）需要一個自化的平臺，把大家解放出來審核上線系統(tǒng) “技術(shù)債技術(shù)債”越早還越好，否則”復(fù)利效應(yīng)”+”時間”只會代價越來越大！審核上線系統(tǒng)- Qunar Inception

17、 Inception是去哪兒網(wǎng)開源的，集上線審核、備份和(預(yù))執(zhí)行的自動化審核上線系統(tǒng)項(xiàng)目首頁：https:/ Inception Inception支持檢查規(guī)則: 基本每個檢查項(xiàng)都對應(yīng)一個參數(shù)變量如：inception_max_keys (11024, default: 16):單表最多的索引個數(shù) DDL表操作檢查 檢查操作的庫，表必須存在 表引擎必須InnoDB 字符集要求utf8 不支持外鍵 表要求有注釋 列設(shè)置not null 自增列從1開始 字段不能用BLOB/TEXT 表必須包含主鍵 單表最多5個索引 不能有重復(fù)索引 .DML update/delete 檢查操作的庫，表必須存在

18、必須指定where條件 檢查update字段必須存在 Where指定字段必須存在 被影響行數(shù)大于1w條，告警 Delete不能有l(wèi)imit條件 .DML insert 檢查操作的庫，表必須存在 必須字段列表 指定的字段必須存在 .Qunar Inception Inception自動支持pt-online-schema-change的表變更.參數(shù)inception_osc_min_table_size,當(dāng)計(jì)算表的大小大于此值時，Alter自動切換為osc mode.inception_osc_on開關(guān)設(shè)置為ONInception參數(shù)參數(shù)pt-osc參數(shù)參數(shù)Inception參數(shù)參數(shù)pt-osc

19、參數(shù)參數(shù)inception_osc_check_interval-check-intervalinception_osc_max_lag -max-laginception_osc_chunk_size -chunk-sizeinception_osc_drop_new_table-no-drop-new-tableinception_osc_chunk_size_limit-chunk-size-limitinception_osc_drop_old_table-no-drop-old-tableinception_osc_chunk_time-chunk-timeinception_osc

20、_recursion_method -recursion-methoninception_osc_max_thread_connected-max-load Threads_conneted=xxxinception_osc_print_sql-printinception_osc_max_thread_running-max-load Threads_running=xxxinception_osc_critical_thread_connected-critical-load Threads_conneted=xxxinception_osc_critical_thread_running

21、-critical-load Threads_running=xxxQunar Inception Inception支持pt-online-schema-change的常用命令：pt-osc的參數(shù)變量查看和修改：inception get variables; inception set xxx=xxxpt-osc正在執(zhí)行的所有session: inception get osc processlist;查看某個pt-osc操作進(jìn)度等詳細(xì)信息：inception get osc_percent “”;中止某個pt-osc操作： inception stop alter “”;自動巡檢系統(tǒng) 巡

22、檢系統(tǒng) 定期自動檢查MySQL運(yùn)行健康狀態(tài)，生成報告；用于發(fā)現(xiàn)存在的隱患和進(jìn)行容量評估。Schema 無主鍵的表 非InnoDB業(yè)務(wù)表 存儲過程/外鍵/視圖檢查 自增ID主鍵溢出檢查(80%) 重復(fù)索引檢查 大表檢查容量 存儲空間剩余時長最小 TOP 10 內(nèi)存使用剩余時長最小 TOP 10 網(wǎng)絡(luò)流量峰值 Swap使用 SSD磨損剩余 QPS峰值性能 慢查詢數(shù)量 95%/99%分位 max和avg響應(yīng)時間 部份AWR報告和優(yōu)化建議巡檢項(xiàng)巡檢項(xiàng)響應(yīng)時間 響應(yīng)時間: first outbound packet-last inbound packet重要性：1 一個大系統(tǒng)的調(diào)用鏈都比較長，常常業(yè)務(wù)RT變慢，就會問是不是DB或cache響應(yīng)慢了2 有些系統(tǒng)對應(yīng)用1%的延時抖動都會敏感簡單實(shí)現(xiàn)：Tcprstat tcprstat -f %Tt%nt%95at%95Mt%99at%99Mt n -port=33