MySQL數(shù)據(jù)庫設(shè)計、優(yōu)化

MySQL數(shù)據(jù)庫設(shè)計、優(yōu)化第一頁，共24頁。提綱規(guī)范基礎(chǔ)規(guī)范命名規(guī)范庫表規(guī)范字段規(guī)范索引規(guī)范開發(fā)環(huán)境優(yōu)化第二頁，共24頁。規(guī)范基礎(chǔ)規(guī)范全部使用InnoDB引擎，MyISAM適用場景非常少字符集：latin1=>utf8=>gbk用數(shù)據(jù)庫來持久化存儲以及保證事務(wù)一致性，不是運算器讀寫分離，主庫只寫和少量實時讀取請求采用隊列方式合并多次寫請求，持續(xù)寫入，避免瞬間壓力超長text/blob進行垂直拆分，并先行壓縮冷熱數(shù)據(jù)進行水平拆分，LRU原則快速更新大數(shù)據(jù)表禁止直接運行count(*)統(tǒng)計第三頁，共24頁。規(guī)范基礎(chǔ)規(guī)范單表行記錄數(shù)控制在1000萬以內(nèi)，行平均長度控制在16KB以內(nèi)，單表20GB以內(nèi)單實例下數(shù)據(jù)表數(shù)量不超過2000個，單庫下數(shù)據(jù)表數(shù)量不超過500個禁止開發(fā)環(huán)境直連線上生產(chǎn)環(huán)境最少授權(quán)，只授予最基礎(chǔ)權(quán)限需求壓力分散，在線表和歸檔表（日志表）分開存儲線上數(shù)據(jù)庫和測試數(shù)據(jù)庫盡可能保持一致禁止明文存儲機密數(shù)據(jù)，需至少兩次加密（部分?jǐn)?shù)據(jù)可逆運算）第四頁，共24頁。規(guī)范命名規(guī)范涉及系統(tǒng)目錄、文件、數(shù)據(jù)庫、表、字段名強烈建議只用小寫字符、數(shù)字、下劃線組合命名長度不超過32個字符不使用select、show、update等保留字全英文或全中文，言之有意，不要半洋半中臨時用加上tmp/temp前綴/后綴統(tǒng)計表加上stat/statistic前綴/后綴歷史歸檔加上完整日期，例如：mkdir-p/backup/user_log/2013/08createtableuser_detailcreatetablexxx_1234createtableaccess_log_20130820第五頁，共24頁。規(guī)范庫表規(guī)范少用分區(qū)表等未完善的新特性不對InnoDB引擎表做在線實時count(*)統(tǒng)計分庫、分表策略以用戶ID=123456為例，取N/100%10=4，取N%10=6最大10個分庫，10個分表，共100個分表則分配到DB_04庫下，分表TABLE_06中采用預(yù)存映射關(guān)系動態(tài)分配更靈活，不受分表算法變化而影響，但數(shù)據(jù)庫開銷大第六頁，共24頁。規(guī)范字段規(guī)范用timestamp（4字節(jié)intunsigned，且效率非常高）記錄時間，而非使用date/datetime/char/varcharIPV4地址采用4字節(jié)intunsigned，內(nèi)置INET_ATON/INET_NTOA快速轉(zhuǎn)換，采用char至少15字節(jié)性別、狀態(tài)、是否、小范圍枚舉使用tinyint（0~255，或-128~127）SignedUnsignedTinyint-128~1270~255Samllint-32768~327670~65535Mediumint-8388608~83886070~16777215Int-2147483648~21474836470~4294967295Bigint-92233728~922337270~184467445第七頁，共24頁。規(guī)范字段規(guī)范char(10)VSvarchar(10)盡可能不使用text/blob類型存儲字符型數(shù)據(jù)時，盡可能先壓縮或者序列化注意字符集問題，server=>database(trigger、storedprocedure、eventscheduler)=>table=>column不要同時指定字符集（characterset）和校驗集（collectset），避免出現(xiàn)和默認對應(yīng)關(guān)系不一致ValueCHAR(4)StorageRequiredVARCHAR(4)StorageRequired'''

'4bytes''1byte'ab''ab

'4bytes'ab'3bytes'abcd''abcd'4bytes'abcd'5bytes'abcdefgh''abcd'4bytes'abcd'5bytes第八頁，共24頁。規(guī)范字段規(guī)范顯式指定自增int/bigintunsignednotnull作為主鍵杜絕使用UUID/HASH/MD5類型作為主鍵無須預(yù)留，越短越好，此處無須18cmO(∩_∩)O哈哈~MySQL5.5以上，OnlineDDL越來越方便顯式約束：NOTNULL第九頁，共24頁。規(guī)范SQL規(guī)范簡化每一條SQL，短事務(wù)、快速執(zhí)行、無阻塞固定模式業(yè)務(wù)邏輯封裝成存儲過程用括號顯式確定AND、OR的先后順序，避免混淆注意引號問題會導(dǎo)致類型轉(zhuǎn)換（whereid=‘1234’）所有查詢想盡一切辦法使用索引：主鍵=>唯一索引=>索引有些查詢只需要掃描索引，無需掃描數(shù)據(jù)（SELECTid,userFROMtableWHEREid=1234）第十頁，共24頁。規(guī)范SQL規(guī)范過濾用戶提交SQL，防止注入杜絕like‘%xxx%’，不用/少用like‘xxx%’不用/少用子查詢，改造成連接（JOIN）不用/少用FORUPDATE、LOCKINSHAREMODE，防止鎖范圍擴大化SQL中不用/少用函數(shù)，可能造成額外開銷或者導(dǎo)致無法使用索引分頁SQL采用內(nèi)連接（INNERJOIN）實現(xiàn)，更高效第十一頁，共24頁。規(guī)范索引用途快速定位避免排序覆蓋索引可直接返回結(jié)果，無需掃描數(shù)據(jù)唯一索引可實現(xiàn)唯一約束第十二頁，共24頁。規(guī)范索引類型B+TreeClusteredindex（InonDBvsMyISAM）Hashindex第十三頁，共24頁。規(guī)范索引類型B+Tree第十四頁，共24頁。規(guī)范索引類型ClusteredindexInnoDBvsMyISAM第十五頁，共24頁。規(guī)范索引類型Hashindex第十六頁，共24頁。規(guī)范索引規(guī)范顯式指定自增int/bigintunsignednotnull作為主鍵不使用外鍵合理利用覆蓋索引，但字段盡量不超過5個合理利用最左索引（前綴索引/部分索引）及時刪除冗余索引選擇適當(dāng)?shù)乃饕樞?，選擇性高條件靠前第十七頁，共24頁。規(guī)范索引規(guī)范基數(shù)（Cardinality）很低的字段不創(chuàng)建索引（MySQL還不支持bitmap索引）采用第三方系統(tǒng)實現(xiàn)text/blob全文檢索常用排序（ORDERBY）、分組（GROUPBY）、取唯一（DISTINCT）字段上創(chuàng)建索引單表索引數(shù)量不超過5個索引字段條件不使用函數(shù)第十八頁，共24頁。規(guī)范開發(fā)環(huán)境啟用log_queries_not_using_indexes設(shè)置long_query_time為最小值定期檢查分析slowlog授權(quán)和生產(chǎn)環(huán)境一致關(guān)閉QueryCache設(shè)置較小InnoDBBufferPool、keybuffersize數(shù)據(jù)量不能太少，否則有些性能問題無法提前規(guī)避第十九頁，共24頁。規(guī)范行為規(guī)范批量導(dǎo)入、導(dǎo)出數(shù)據(jù)須提前通知DBA，請求協(xié)助觀察推廣活動或上線新功能須提前通知DBA，請求壓力評估不使用SUPER權(quán)限連接數(shù)據(jù)庫單表多次ALTER操作必須合并為一次操作數(shù)據(jù)庫DDL及重要SQL及早提交DBA評審重要業(yè)務(wù)庫須告知DBA重要等級、數(shù)據(jù)備份及時性要求不在業(yè)務(wù)高峰期批量更新、查詢數(shù)據(jù)庫提交線上DDL需求，所有SQL語句須有備注說明第二十頁，共24頁。優(yōu)化硬件NUMA新架構(gòu)，CPU直接存取內(nèi)存，更高效CPU一般不是瓶頸，但MySQL多核支持仍不佳設(shè)備越來越廉價，大內(nèi)存解決很多問題SSD應(yīng)用越來越廣泛，未來是主力RAID卡可有效提升IOPS及數(shù)據(jù)安全（RAID10vsRAID5）RAID卡必須配備BBU，設(shè)置FORCEWBFushionIO很NB，但還是貴族第二十一頁，共24頁。優(yōu)化系統(tǒng)升級到64位/tmp使用/dev/shm的tmpfs內(nèi)核IO調(diào)度：deadline，noop，反正不要cfqVM管理：vm.s文件系統(tǒng)：xfs/zfs全B+樹，高效分配組，提高并發(fā)度延遲分配，減少IOmount：nobarrier、data=o