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

1、MySQL數(shù)據(jù)庫(kù)設(shè)計(jì)、優(yōu)化葉金榮微博： yejinrong微信公眾號(hào)： MySQL中文網(wǎng)提綱規(guī)范基礎(chǔ)規(guī)范命名規(guī)范庫(kù)表規(guī)范字段規(guī)范索引規(guī)范開發(fā)環(huán)境優(yōu)化規(guī)范基礎(chǔ)規(guī)范全部使用InnoDB引擎，MyISAM適用場(chǎng)景非常少字符集：latin1 = utf8 = gbk用數(shù)據(jù)庫(kù)來持久化存儲(chǔ)以及保證事務(wù)一致性，不是運(yùn)算器讀寫分離，主庫(kù)只寫和少量實(shí)時(shí)讀取請(qǐng)求采用隊(duì)列方式合并多次寫請(qǐng)求，持續(xù)寫入，避免瞬間壓力超長(zhǎng)text/blob進(jìn)行垂直拆分，并先行壓縮冷熱數(shù)據(jù)進(jìn)行水平拆分，LRU原則快速更新大數(shù)據(jù)表禁止直接運(yùn)行count(*)統(tǒng)計(jì)規(guī)范基礎(chǔ)規(guī)范單表行記錄數(shù)控制在1000萬以內(nèi)，行平均長(zhǎng)度控制在16KB以內(nèi)，單表

2、20GB以內(nèi)單實(shí)例下數(shù)據(jù)表數(shù)量不超過2000個(gè)，單庫(kù)下數(shù)據(jù)表數(shù)量不超過500個(gè)禁止開發(fā)環(huán)境直連線上生產(chǎn)環(huán)境最少授權(quán)，只授予最基礎(chǔ)權(quán)限需求壓力分散，在線表和歸檔表（日志表）分開存儲(chǔ)線上數(shù)據(jù)庫(kù)和測(cè)試數(shù)據(jù)庫(kù)盡可能保持一致禁止明文存儲(chǔ)機(jī)密數(shù)據(jù)，需至少兩次加密（部分?jǐn)?shù)據(jù)可逆運(yùn)算）規(guī)范命名規(guī)范涉及系統(tǒng)目錄、文件、數(shù)據(jù)庫(kù)、表、字段名強(qiáng)烈建議只用小寫字符、數(shù)字、下劃線組合命名長(zhǎng)度不超過32個(gè)字符不使用select、show、update等保留字全英文或全中文，言之有意，不要半洋半中臨時(shí)用加上 tmp/temp 前綴/后綴統(tǒng)計(jì)表加上 stat/statistic 前綴/后綴歷史歸檔加上完整日期，例如：20130

3、802 mkdir -p /backup/user_log/2013/08 create table user_detail create table xxx_1234 create table access_log_20130820規(guī)范庫(kù)表規(guī)范少用分區(qū)表等未完善的新特性不對(duì)InnoDB引擎表做在線實(shí)時(shí)count(*)統(tǒng)計(jì)分庫(kù)、分表策略以用戶ID=123456為例，取N/100%10=4，取N%10=6最大10個(gè)分庫(kù)，10個(gè)分表，共100個(gè)分表則分配到DB_04庫(kù)下，分表TABLE_06中采用預(yù)存映射關(guān)系動(dòng)態(tài)分配更靈活，不受分表算法變化而影響，但數(shù)據(jù)庫(kù)開銷大規(guī)范字段規(guī)范用timestamp（4

4、字節(jié)int unsigned，且效率非常高）記錄時(shí)間，而非使用date/datetime/char/varcharIPV4地址采用4字節(jié)int unsigned，內(nèi)置INET_ATON/INET_NTOA快速轉(zhuǎn)換，采用char至少15字節(jié)性別、狀態(tài)、是否、小范圍枚舉使用tinyint（0 255，或 -128 127）SignedUnsignedTinyint-1281270255Samllint-3276832767065535Mediumint-83886088388607016777215Int-2147483648214748364704294967295Bigint-92233720

5、368547758089223372036854775807018446744073709551615規(guī)范字段規(guī)范char(10) VS varchar(10)盡可能不使用text/blob類型存儲(chǔ)字符型數(shù)據(jù)時(shí)，盡可能先壓縮或者序列化注意字符集問題，server=database(trigger、stored procedure、event scheduler)=table=column不要同時(shí)指定字符集（character set）和校驗(yàn)集（collect set），避免出現(xiàn)和默認(rèn)對(duì)應(yīng)關(guān)系不一致ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Req

6、uired 4 bytes1 byteabab 4 bytesab3 bytesabcdabcd4 bytesabcd5 bytesabcdefghabcd4 bytesabcd5 bytes規(guī)范字段規(guī)范顯式指定自增 int/bigint unsigned not null 作為主鍵杜絕使用UUID/HASH/MD5類型作為主鍵無須預(yù)留，越短越好，此處無須18cm O(_)O哈哈MySQL 5.5以上，Online DDL越來越方便顯式約束：NOT NULL規(guī)范SQL規(guī)范簡(jiǎn)化每一條SQL，短事務(wù)、快速執(zhí)行、無阻塞固定模式業(yè)務(wù)邏輯封裝成存儲(chǔ)過程用括號(hào)顯式確定AND、OR的先后順序，避免混淆注意引

7、號(hào)問題會(huì)導(dǎo)致類型轉(zhuǎn)換（where id = 1234）所有查詢想盡一切辦法使用索引：主鍵=唯一索引=索引有些查詢只需要掃描索引，無需掃描數(shù)據(jù)（SELECT id,user FROM table WHERE id = 1234）規(guī)范SQL規(guī)范過濾用戶提交SQL，防止注入杜絕 like %xxx%，不用/少用 like xxx%不用/少用子查詢，改造成連接（JOIN）不用/少用FOR UPDATE、LOCK IN SHARE MODE，防止鎖范圍擴(kuò)大化SQL中不用/少用函數(shù)，可能造成額外開銷或者導(dǎo)致無法使用索引分頁(yè)SQL采用內(nèi)連接（INNER JOIN）實(shí)現(xiàn)，更高效規(guī)范索引用途快速定位避免排序覆蓋

8、索引可直接返回結(jié)果，無需掃描數(shù)據(jù)唯一索引可實(shí)現(xiàn)唯一約束規(guī)范索引類型B+ TreeClustered index（InonDB vs MyISAM）Hash index規(guī)范索引類型B+ Tree規(guī)范索引類型Clustered indexInnoDB vs MyISAM規(guī)范索引類型Hash index規(guī)范索引規(guī)范顯式指定自增 int/bigint unsigned not null 作為主鍵不使用外鍵合理利用覆蓋索引，但字段盡量不超過5個(gè)合理利用最左索引（前綴索引/部分索引）及時(shí)刪除冗余索引選擇適當(dāng)?shù)乃饕樞?，選擇性高條件靠前規(guī)范索引規(guī)范基數(shù)（ Cardinality ）很低的字段不創(chuàng)建索引（My

9、SQL還不支持 bitmap 索引）采用第三方系統(tǒng)實(shí)現(xiàn)text/blob全文檢索常用排序（ORDER BY）、分組（GROUP BY）、取唯一（DISTINCT）字段上創(chuàng)建索引單表索引數(shù)量不超過5個(gè)索引字段條件不使用函數(shù)規(guī)范開發(fā)環(huán)境啟用log_queries_not_using_indexes設(shè)置long_query_time為最小值定期檢查分析slow log授權(quán)和生產(chǎn)環(huán)境一致關(guān)閉Query Cache設(shè)置較小InnoDB Buffer Pool、key buffer size數(shù)據(jù)量不能太少，否則有些性能問題無法提前規(guī)避規(guī)范行為規(guī)范批量導(dǎo)入、導(dǎo)出數(shù)據(jù)須提前通知DBA，請(qǐng)求協(xié)助觀察推廣活動(dòng)或上

10、線新功能須提前通知DBA，請(qǐng)求壓力評(píng)估不使用SUPER權(quán)限連接數(shù)據(jù)庫(kù)單表多次ALTER操作必須合并為一次操作數(shù)據(jù)庫(kù)DDL及重要SQL及早提交DBA評(píng)審重要業(yè)務(wù)庫(kù)須告知DBA重要等級(jí)、數(shù)據(jù)備份及時(shí)性要求不在業(yè)務(wù)高峰期批量更新、查詢數(shù)據(jù)庫(kù)提交線上DDL需求，所有SQL語(yǔ)句須有備注說明優(yōu)化硬件NUMA新架構(gòu)，CPU直接存取內(nèi)存，更高效CPU一般不是瓶頸，但MySQL多核支持仍不佳設(shè)備越來越廉價(jià)，大內(nèi)存解決很多問題SSD應(yīng)用越來越廣泛，未來是主力RAID卡可有效提升IOPS及數(shù)據(jù)安全（RAID 10 vs RAID 5）RAID卡必須配備BBU，設(shè)置FORCE WBFushionIO很NB，但還是貴族

11、優(yōu)化系統(tǒng)升級(jí)到64位/tmp使用/dev/shm的tmpfs內(nèi)核 IO調(diào)度：deadline，noop，反正不要cfq VM管理：vm.swappiness=0文件系統(tǒng)：xfs/zfs 全B+樹，高效 分配組，提高并發(fā)度 延遲分配，減少IO mount：nobarrier、data=ordered,writeback優(yōu)化MySQL配置memlockopen_files_limitmax_connectionslong_query_timetable_open_cachekey_buffer_sizequery_cache_sizetmp_table_size/max_heap_table_size優(yōu)化My