第一章 總則

第1條 目的及適用范圍

規(guī)范生產(chǎn)環(huán)境數(shù)據(jù)庫設(shè)計嘹履，統(tǒng)一數(shù)據(jù)庫設(shè)計標準。避免設(shè)計不合理造成重復犯錯。

適用范圍：Web技術(shù)部的全體開發(fā)。

第二章 命名規(guī)范

第1條 基本規(guī)范

（1） 原則：清晰明了艇棕，簡潔準確，見名知意;

（2） 組成：英文字母, 下劃線, 阿拉伯數(shù)字;

（3） 長度：最長64個字符串塑，建議不超過30, 以免對后序的操作帶來不便;

（4） 命名方式

1. 名稱中除下劃線沼琉、數(shù)字的部分，要使用英文單詞或英文短語或相應縮寫桩匪，不推薦使用漢語拼音;

2. 禁止使用MySQL關(guān)鍵字和保留字打瘪，見附錄-關(guān)鍵字和保留字;

3. 禁止出現(xiàn)數(shù)字開頭;

4. 英文字母使用小寫形式，避免因操作系統(tǒng)或mysql服務配置的不同引起的問題;

5. 名詞使用單數(shù);

6. 多個單詞用下劃線分隔;

7. 禁止兩個下劃線中間只出現(xiàn)數(shù)字;

第2條 具體規(guī)范

（1） 庫命名

1. 使用項目名縮寫作為前綴吸祟，如游戲項目： game_xxx;

2. 庫名長度控制在10個字符內(nèi)瑟慈，超出10個字符時，盡量使用單詞縮寫;

3. 備份庫必須以bak_為前綴屋匕，以日期為后綴 例：bak_video_20180307;

4. 臨時庫必須以tmp_為前綴，以日期為后綴 例：tmp_video_20180307;

（2） 表命名

1. 用系統(tǒng)或模塊的英文名的縮寫作為前綴借杰，優(yōu)先使用模塊名;

2. 表名長度控制在15個字符內(nèi)过吻，超出15個字符時，盡量使用單詞縮寫;

3. 備份表必須以bak_為前綴，以日期為后綴 例：bak_video_user_20180307;

4. 臨時表必須以tmp_為前綴纤虽，以日期為后綴 例：tmp_video_user_20180307;

（3） 字段命名

1. 避免出現(xiàn)數(shù)字;

2. 除外鍵或其它表的主鍵外乳绕，一般不需要添加前綴;

3. 字段名長度控制在20個字符內(nèi)，超出20個字符時逼纸，使用單詞縮寫;

（4） 索引命名

1. 主鍵索引名為pk_字段名洋措，pk_即 primary key;

2. 非唯一索引 按照“idx_字段名稱字段名稱[字段名]”進行命名, idx_ 即 index;

3. 唯一索引 按照“uk_字段名稱字段名稱[字段名]”進行命名, uk_即unique key;

4. 如索引名過長，使用縮寫;

第三章 注釋

第1條 表注釋

（1） 每個表必須要有注釋;

（2） 描述數(shù)據(jù)表用途;

（3） 注釋不超過30個字符;

第2條 字段注釋

（1） 每個字段必須要有注釋;

（2） 描述該字段的用途及可能存儲的內(nèi)容杰刽，字段的取值含義或者范圍;

（3） 如果是枚舉類型菠发，將該字段中使用的內(nèi)容都定義出來;

（4） 注釋不超過30個字符;

第四章 MySQL字符集

第1條 推薦字符集

（1） 非特殊需求，所有字符存儲與表示贺嫂，均以utf-8編碼;

（2） 如果需要存儲Emoji表情滓鸠，那么字段選擇utf8mb4來進行存儲;

第2條 創(chuàng)建對象的字符集

（1） 字符集和校對規(guī)則的4個級別 （從上到下，級別遞減）

1. 服務器級別;

2. 數(shù)據(jù)庫級別;

3. 表級別第喳、列級別;

4. 連接級別;

（2） 更低級別的設(shè)置會繼承高級別的設(shè)置;

（3） 通用規(guī)則

1. 先為服務器或者數(shù)據(jù)庫選擇一個合理的字符集糜俗，然后根據(jù)不同的實際情況，讓某個列選擇自己的字符集;

2. 字符集能表達的字符范圍 utf8 > gbk > gb2312 > latin1;

3. 字符集中的ci 為 Case Insensitive (大小寫不敏感)的縮寫曲饱， cs 為Case Sensitive (大小寫敏感)的縮寫;

4. 字符集中 bin 表示用二進制存儲數(shù)據(jù)悠抹，用編碼值進行比較，區(qū)分大小寫;

第3條 控制server和client端交互通信的字符集

（1） character_set_server：mysql server默認字符集;

（2） character_set_database：數(shù)據(jù)庫默認字符集;

（3） character_set_client：MySQL server假定客戶端發(fā)送的查詢使用的字符集;

（4） character_set_connection：MySQL Server接收客戶端發(fā)布的查詢請求后扩淀，將其轉(zhuǎn)換為character_set_connection變量指定的字符集;

（5） character_set_results：mysql server把結(jié)果集和錯誤信息轉(zhuǎn)換為character_set_results指定的字符集锌钮，并發(fā)送給客戶端;

（6） character_set_system：系統(tǒng)元數(shù)據(jù)(字段名等)字符集;

第五章 數(shù)據(jù)庫設(shè)計

第1條 命名

見 庫命名規(guī)范;

第2條 排序規(guī)則/校驗規(guī)則 (collation)

（1） 盡量顯式地設(shè)置字符集，而不是依賴于MySQL的默認設(shè)置;

（2） 如果不考慮占用空間及帶寬因素引矩，推薦使用utf-8;

第3條 數(shù)據(jù)庫連接用完后,及時關(guān)閉

避免數(shù)據(jù)庫連接數(shù)過大;

第六章 數(shù)據(jù)表設(shè)計

第1條 命名

見 表命名規(guī)范

第2條 排序規(guī)則/校驗規(guī)則 (collation)

（1） 盡量顯式地設(shè)置字符集;

（2） 結(jié)合業(yè)務使用英文字母大小寫敏感/不敏感 的字符集;

第3條 表引擎

（1） 默認使用InnoDB;

（2） 從大量的select操作性能上考慮梁丘，日志及報表類數(shù)據(jù)表適合使用 MyISAM引擎，其余推薦使用InnoDB;

（3） 從支持事務旺韭，大量的insert, update 操作上來考慮氛谜，與交易、審核区端、金額相關(guān)的表用 InnoDB;

（4） 歸檔數(shù)據(jù)表(可查詢值漫，不更新刪除)，使用ARCHIVE引擎;

（5） 臨時數(shù)據(jù)表织盼，如數(shù)據(jù)量不大杨何，不需要較高的數(shù)據(jù)安全生性，使用Memory引擎;

第4條 數(shù)據(jù)表存儲內(nèi)容

（1） 單表數(shù)據(jù)行數(shù)建議控制在1000萬以內(nèi)沥邻，或者數(shù)據(jù)表占用磁盤空間不超過10G;

（2） 不在數(shù)據(jù)庫中存儲文件危虱，應使用對應文件的路徑;

（3） 禁止存儲明文密碼;

第5條 字段

（1） 單表字段數(shù)建議不超過30;

（2） 字段數(shù)過多就進行垂直分表

1. 冷熱數(shù)據(jù)分離;

2. 大字段分離;

3. 常在一起作為條件和返回列的字段不分離;

第6條 表注釋

如果要創(chuàng)建同表A一樣的表結(jié)構(gòu)B

推薦SQL語句：create table B like A, 或是先show create table A， 然后修改表名后唐全，執(zhí)行上一SQL結(jié)果中的表名埃跷，再執(zhí)行;

避免使用 create table B as select * from B 來創(chuàng)建表（此操作只會創(chuàng)建表字段蕊玷，而不會創(chuàng)建索引結(jié)構(gòu)）;

第7條 表注釋

添加簡潔明了的數(shù)據(jù)表功能說明, 詳細見 表注釋;

第七章 字段設(shè)計

第1條 命名

見 字段命名規(guī)范;

第2條 原則

（1） 用盡量少的存儲空間來存儲一個字段的數(shù)據(jù);

（2） 表內(nèi)的每一行都應當被唯一地標識;

（3） 同一庫中，不同表中相同含義的字段弥雹，字段名保持一致;

（4） 如無備注垃帅，所有字段都設(shè)置not null, 并設(shè)置默認值;

（5） 字段允許適當冗余，以提高查詢性能剪勿，但必須考慮數(shù)據(jù)一致贸诚。冗余字段應遵循:

1. 不是頻繁修改的字段;

2. 不是 varchar 超長字段，更不能是 text 字段;

第3條 強制字段

（1） id 主鍵厕吉，類型為unsigned int (bigint)酱固，自增，步長為1;

（2） 如業(yè)務需要以下字段赴涵，推薦以下命名與字段類型:

1. create_time, 創(chuàng)建時間媒怯； update_time, 更新時間;

2. 類型 推薦 datetime, 特點如下:

3. 日期查詢速度快;

4. 可讀性高;

5. 無時區(qū)變換問題;

第4條 字段類型選取

（1） 能用tinyint 就不用int;

（2） 能用int 就不用char或varchar;

（3） 字段如果為非負數(shù)，必須是 unsigned;

（4） 如果存儲的字符串長度相等髓窜，使用 char 定長字符串類型 ;

（5） 表達是與否概念的字段扇苞，必須使用 is_xxx 的方式命名，數(shù)據(jù)類型是 unsigned tinyint ( 1表示是寄纵，0表示否);

（6） 小數(shù)類型為 decimal鳖敷，禁止使用 float 和 double;

說明:float 和 double 在存儲的時候，存在精度損失的問題程拭，很可能在值的比較時定踱，得到不正確的結(jié)果。如果存儲的數(shù)據(jù)范圍超過 decimal 的范圍恃鞋，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)分開存儲;

（7） varchar 是可變長字符串崖媚，不預先分配存儲空間，長度不要超過 5000恤浪，如果存儲長度大于此值畅哑，定義字段類型為 text，獨立出來一張表水由，用主鍵來對應荠呐，避免影響其它字段索引效率 ;

（8） 減少text,blob類型，如必要砂客，需要將text,blob字段拆分后單獨存儲;

（9） 不建議使用enum類型泥张，考慮使用tinyint類型替代;

（10）使用int unsigned存儲IPV4 (使用php的 long2ip, ip2long 函數(shù)與 mysql 的 inet_aton, inet_aton) 進行轉(zhuǎn)換;

（11）InnoDB引擎優(yōu)先考慮使用varbinary存儲大小寫敏感的變長字符串或二進制內(nèi)容;

第5條 表字段順序

（1） 從前到后，按照字段的重要性和使用頻率排列鞠值，id 作為第一列;

（2） 按照字段的功能歸集排列媚创，功能相似的字段相鄰排列;

（3） create_time，update_time 放在最后;

第6條 字段注釋

添加簡潔明了的該列存儲數(shù)據(jù)說明, 詳細見 字段注釋;

第八章 索引設(shè)計

第1條 命名

見 索引命名規(guī)范;

第2條 原則

（1） 最左前綴匹配原則;

（2） 為經(jīng)常作為查詢條件的字段建立索引;

（3） 為經(jīng)常需要排序齿诉，分組的字段創(chuàng)建索引;

（4） 選擇區(qū)分度高的列作為索引（組合索引除外）

1. 區(qū)分度計算：count(distinct left(列名, 索引長度))/count(*);

2. 在 varchar 字段上建立索引時筝野，必須指定索引長度晌姚，沒必要對全字段建立索引粤剧，根據(jù) 實際文本區(qū)分度決定索引長度即可 ;

說明：索引的長度與區(qū)分度是一對矛盾體歇竟，長度越長，區(qū)分度越高;

（5） 使用前綴索引

1. 索引的值過長抵恋，會影響查詢速度;

2. 要結(jié)合區(qū)分度焕议，來選擇合適長度的字段來建立索引;

（6） 如果字段事實上是與其它表的關(guān)鍵字相關(guān)聯(lián)而未設(shè)計為外鍵引用，需建索引;

（7） 業(yè)務上具有唯一特性的字段弧关，即使是多個字段的組合盅安，也必須建成唯一索引;

（8） 多表關(guān)聯(lián)查詢時， 保證被關(guān)聯(lián)的字段需要有索引;

（9） 有null 值存在的列世囊，不要建索引;

（10）單張表中索引數(shù)量不超過5個;

（11）單個索引中的字段數(shù)不超過5個;

（12）禁止使用外鍵作為索引;

（13）索引長度不能超出限制

MyISAM: 所有索引字段定義長度的總和不能超過1000字節(jié);

InnoDB:單一索引字段定義長度的總和不能超過767字節(jié);

（14）使用explain 測試索引;

第3條 索引分類

（1） 唯一索引

1. 業(yè)務上具有唯一特性的字段别瞭，即使是多個字段的組合，也必須建成唯一索引;

2. 唯一索引的長度要和字段致;

3. 禁止使用某字段的前N個字符做唯一索引;

（2） 組合索引

1. 作為組合的查詢條件株憾、排序條件蝙寨、分組條件中涉及的字段，可以建立組合索引;

2. 建組合索引的時候嗤瞎，區(qū)分度最高的在最左邊

a) 如果 where a=? and b=? 墙歪，a 列的幾乎接近于唯一值，那么只需要單建 idx_a 索引即可;

b) 存在非等號和等號混合判斷條件時贝奇，在建索引時虹菲，請把等號條件的列前置。如:where a>? and b=? 那么即使 a 的區(qū)分度更高掉瞳，也必須把 b 放在索引的最前列;

3. order by 最后的字段是組合索引的一部分毕源，并且要放在索引組合順序的最后，避免出現(xiàn) file_sort 的情況陕习，影響查詢性能;

（3） 全文索引

1. 在MySQL5.6以下霎褐，只有存儲引擎為MyISAM的數(shù)據(jù)表支持全文檢索，MySQL5.6及以上衡查，InnoDB引擎表才開始支持全文檢索;

2. MySQL全文索引只能對英文進行全文檢索瘩欺，本身不支持中文分詞;

3. 只支持在char, varchar, text字段上創(chuàng)建fulltext indexes;

4. MySQL全文檢索時，所有FULLTEXT索引列必須使用相同的字符集;

5. MySQL全文檢索時拌牲，默認檢索長度為4俱饿，即關(guān)鍵詞的長度必須大于5才能被捕獲;

第九章 分庫分表

第1條 分庫分表的環(huán)境條件

（1） 單機負載過高;

（2） 數(shù)據(jù)表的數(shù)據(jù)量太大，正常的運維工作影響業(yè)務訪問

1. 對數(shù)據(jù)庫備份時間過長;

2. DDL操作鎖表時間長;

3. 讀寫壓力大;

（3） 數(shù)據(jù)表增長快塌忽，或無窮增長;

（4） 當前數(shù)據(jù)庫拍埠、數(shù)據(jù)表設(shè)計不合理;

第2條 分庫分表的原則

（1） 能不分就不分

1. 分庫分表增加了業(yè)務邏輯的復雜度;

2. 優(yōu)先從業(yè)務邏輯、實現(xiàn)方法上來解決問題;

（2） 出現(xiàn)在聯(lián)表查詢中的數(shù)據(jù)表土居，要分在一個庫中;

（3） 避免造成跨庫的事務;

第3條 分庫分表方法

（1） 垂直拆分

1. 根據(jù)業(yè)務按照模塊或冷熱數(shù)據(jù)(表)進行分庫 (如微服務所使用的方式);

2. 把數(shù)據(jù)表字段按照冷熱數(shù)據(jù)(字段)分離枣购、大數(shù)據(jù)(字段)分離嬉探、關(guān)聯(lián)數(shù)據(jù)(字段)不分離的原則進行拆分;

（2） 水平拆分

1. 如事先可估算出表能達到的量，可以通過一定的算法棉圈，計算每一條數(shù)據(jù)要存放的表名;

2. 如表能達到的量級不能估算涩堤，則使用號段分表， 如 id 小于 1000w對應partition_1, id介于1000w - 2000w對就partition_2….;

第十章 附錄

第1條 MySQL常用數(shù)據(jù)類型

（1） 整數(shù)類型

1. tinyint (1字節(jié))

2. smallint (2字節(jié))

3. mediumint (3字節(jié))

4. int (4字節(jié))

5. bigint (8字節(jié))

（2） 定點類型

decimal （對DECIMAL(M,D) 分瘾，如果M>D胎围，為M+2字節(jié)否則為D+2字節(jié)）

（3） 浮點類型

1. float (4字節(jié))

2. double (8字節(jié))

（4） 字符類型

1. char(Length) (Length字節(jié))

2. varchar (0-255字節(jié))

3. tinnytext (0-255字節(jié))

4. text (0-65535字節(jié))

5. mediumtext (0-16 777 215字節(jié))

6. longtext (0-4 294 967 295字節(jié))

7. tinyblob (0-255字節(jié))

8. blob (0-65535字節(jié))

9. mediumblob (0-16 777 215字節(jié))

10. longblob (0-4 294 967 295字節(jié))

（5） 日期類型

1. date (3字節(jié)) 格式：YYYY-MM-DD 范圍:1000-01-01/9999-12-31

2. time (3字節(jié)) 格式：HH:MM:SS 范圍: -838:59:59/838:59:59

3. year (1字節(jié)) 格式：YYYY 范圍: 1901/2155

4. timestamp （4字節(jié)）范圍：1970-01-01 00:00:00/2038-01-19 03:14:07 (GMT)

5. datetime （8字節(jié)）范圍：1000-01-01 00:00:00/9999-12-31 23:59:59

（6） 枚舉類型

1. enum 最多65535個成員

2. set 最多 64個成員

第2條 MySQL關(guān)鍵字與保留字

MySQL關(guān)鍵字與保留字 數(shù)量較多(MySQL5.7版本 622)，不再一一列舉德召，請見官方文檔：https://dev.mysql.com/doc/refman/5.N/en/keywords.html#keywords-in-current-series