MySQL 數(shù)據(jù)庫規(guī)范

MySQL連接規(guī)范

【強(qiáng)制】必須設(shè)置mysql連接超時(shí)時(shí)間，一般設(shè)置為10秒

MySQL建表規(guī)范

【強(qiáng)制】表達(dá)是與否概念的字段，必須使用is_xxx的方式命名，數(shù)據(jù)類型是tinyint unsigned，1表示是，0表示否；

說明：任何為非負(fù)數(shù)的字段，必須設(shè)置為unsigned；

正例：表達(dá)邏輯刪除的字段名 is_deleted，1表示刪除，0表示未刪除；

【強(qiáng)制】數(shù)據(jù)庫名、表名、字段名統(tǒng)一采用下劃線拼接形式，由小寫字母或數(shù)字構(gòu)成，禁止出現(xiàn)數(shù)字開頭，禁止兩個(gè)下劃線中間只出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價(jià)很大，因?yàn)闊o法進(jìn)行預(yù)發(fā)布，所以字段名稱需要慎重考慮。數(shù)據(jù)庫名、表名一般控制在三個(gè)單詞范圍內(nèi)，字段名一般控制在兩個(gè)單詞范圍內(nèi)；

說明： MySQL在Windows下不區(qū)分大小寫，但在Linux下默認(rèn)是區(qū)分大小寫。因此，數(shù)據(jù)庫名、表名、字段名都禁止出現(xiàn)任何大寫字母，避免節(jié)外生枝；

正例：novel_chapter、novel_comment；

反例：novelChapter、novel_3_chapter；

【強(qiáng)制】表名不使用復(fù)數(shù)名詞；

說明：表名僅僅表示數(shù)據(jù)表里面的實(shí)體內(nèi)容，不應(yīng)該表示實(shí)體數(shù)量；

【強(qiáng)制】數(shù)據(jù)庫名、表名、字段名禁用保留字，如desc、name、type、time等等，請參考MySQL保留字官方文檔；

【強(qiáng)制】若主鍵需要與其他表做關(guān)聯(lián)，就取名xxxid，方便做跨表查詢；若無需與其他表做關(guān)聯(lián)，就叫id；

【強(qiáng)制】所有字段都必須設(shè)置為NOT NULL，若默認(rèn)值為空值，可通過DEFAULT ''進(jìn)行設(shè)置；

【強(qiáng)制】所有表名、字段名都必須加上注釋；

【強(qiáng)制】每張表中都必須加上create_time字段，視業(yè)務(wù)情況決定是否加上update_time字段：

`create_time`INT(11) NOT NULL COMMENT'創(chuàng)建時(shí)間',`update_time`INT(11) NOT NULL COMMENT'更新時(shí)間',

【強(qiáng)制】若時(shí)間字段需要做索引，必須使用int類型；

【強(qiáng)制】價(jià)格相關(guān)的字段必須設(shè)置為int類型，其他小數(shù)類型可以設(shè)置為decimal，禁止使用 float 和double；

說明：float和double在存儲的時(shí)候，存在精度丟失的問題。在進(jìn)行比較、運(yùn)算時(shí)，無法得到精確結(jié)果；

【強(qiáng)制】varchar是可變長字符串類型，不預(yù)先分配存儲空間，長度不要超過5000；如果存儲長度大于此值，定義字段類型為text，獨(dú)立出來一張表，用主鍵做對應(yīng)，避免影響其他字段的索引效率；

【推薦】若修改字段注釋，或?qū)ψ侄伪硎镜臓顟B(tài)進(jìn)行追加時(shí)，需要及時(shí)更新字段注釋；

【推薦】單表行數(shù)超過500萬行或者單表容量超過2GB時(shí)，才推薦進(jìn)行分庫分表；

說明：若預(yù)計(jì)三年后的數(shù)據(jù)量根本達(dá)不到這個(gè)級別，請不要在創(chuàng)建表時(shí)就分庫分表；

【參考】合適的字符存儲長度，不但節(jié)約數(shù)據(jù)庫表空間、節(jié)約索引存儲，更重要的是提升檢索速度；

正例：如下表所示，無符號值不僅避免誤存負(fù)數(shù)，還擴(kuò)大了表示范圍

對象年齡區(qū)間類型字節(jié)表示范圍

人150歲之內(nèi)tinyint unsigned1無符號值：0-255

龜數(shù)百年smallint unsigned2無符號值：0-65535

恐龍化石數(shù)千萬年int unsigned4無符號值：0-42.9億

太陽約50億年bigint unsigned8無符號值：0-10的19次方

索引規(guī)范

【強(qiáng)制】唯一索引名以大寫UNIQUE_開頭；普通索引名以大寫INDEX_開頭；

正例：INDEX_NOVELID、UNIQUE_COMMENTID_USERID；

【強(qiáng)制】業(yè)務(wù)上具有唯一特性的字段，即使是多個(gè)字段的組合，也必須建成唯一索引；

說明：不要以為唯一索引影響了insert速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的；另外，即使在應(yīng)用層做了非常完善的校驗(yàn)控制，只要沒有唯一索引，根據(jù)墨菲定律，必然會有臟數(shù)據(jù)產(chǎn)生的情況；

【強(qiáng)制】超過三張表禁止join操作。需要join的字段，數(shù)據(jù)類型必須絕對一致；多表關(guān)聯(lián)查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引；

說明：即使雙表join也要注意表索引、SQL性能；

【強(qiáng)制】在varchar字段上建立索引時(shí)，必須指定索引長度，沒必要對全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長度即可；

說明：索引的長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù)，長度為20的索引，區(qū)分度會高達(dá)90%以上，可以使用 count(distinct left(列名，索引長度)) / count(*) 的區(qū)分度來確定；

【推薦】頁面搜索嚴(yán)禁左模糊或全模糊，如果需要請走搜索引擎來解決；

說明：索引文件具有B-Tree的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引；

【推薦】如果有order by的場景，請注意利用索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn)file_sort的情況，影響查詢性能；

正例：where a=? and b=? and c=? ?組合索引:a_b_c

反例：索引中有范圍查找，那么索引有序性無法利用，如where a>10 order by b; 索引a_b無法排序；

【推薦】利用覆蓋索引來進(jìn)行查詢操作，避免回表；

說明：如果一本書需要知道第11章是什么標(biāo)題，會翻開第11章對應(yīng)的那一頁嗎？ 目錄瀏覽一下就好，這個(gè)目錄就是起到覆蓋索引的作用；

正例：能夠建立索引的種類分為主鍵索引、唯一索引、普通索引三種，而覆蓋索引只是一種查詢的效果，用explain的結(jié)果，extra列會出現(xiàn)using index;

【推薦】利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景；

說明：MySQL并不是跳過offset行，而是取offset+N行，然后返回放棄前offset行，返回N行，那當(dāng)offset特別大的時(shí)候，效率就非常低下，要么控制返回的總頁數(shù)，要么對超過特定閾值的頁數(shù)進(jìn)行SQL改寫；

正例：先快速定位需要獲取的id段，然后再關(guān)聯(lián)：

select a.* from 表1 a, (select id from 表1 where 條件 LIMIT100000,20) b where a.id=b.id

【推薦】SQL性能優(yōu)化的目標(biāo)：至少要達(dá)到range級別，要求是ref級別，如果可以是consts最好；

consts單表中最多只有一個(gè)匹配行（主鍵或者唯一索引），在優(yōu)化階段即可讀取到數(shù)據(jù)；

ref指的是使用普通索引；

range對索引進(jìn)行范圍檢索；

反例：explain表的結(jié)果，type=index，索引物理文件全掃描，速度非常慢，這個(gè)index級別比range還低，與全表掃描是小巫見大巫；

【推薦】建組合索引的時(shí)候，區(qū)分度最高的放在最左邊；

說明：存在非等號和等號混合時(shí)，在建索引時(shí)，請把等號條件的列前置。如where c>? and d=? 那么即使c的區(qū)分度更高，也必須把d放在索引的最前列，即索引：INDEX_D_C；

正例：where a=? and b=? ，如果a列幾乎接近于唯一值，那么只需要單建 INDEX_A 索引即可；

【推薦】防止因字段類型不同造成的隱式轉(zhuǎn)換，導(dǎo)致索引失效。如聯(lián)表操作時(shí)；

【參考】創(chuàng)建索引時(shí)避免有如下極端誤解：

寧濫勿缺：認(rèn)為一個(gè)查詢就需要建一個(gè)索引；

寧缺勿濫：認(rèn)為索引會消耗空間、嚴(yán)重拖慢更新和新增速度；

抵制唯一索引：認(rèn)為業(yè)務(wù)的唯一性一律需要在應(yīng)用層通過“先查后插”的方式解決；

SQL規(guī)范

【強(qiáng)制】不要使用 count(列名) 或 count(常量) 來替代 count(*) ， count(*) 是SQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟NULL和非NULL無關(guān)；

說明：count(*) 會統(tǒng)計(jì)值為NULL的行，而count(列名) 不會統(tǒng)計(jì)此列為NULL值的行；

【強(qiáng)制】count(distinct 列名) 計(jì)算該列除NULL之外的不重復(fù)行數(shù)，注意 count(distinct 列名1, 列名2)) 如果其中一列全為NULL，那么即使另一列有不同的值，也會返回為0；

【強(qiáng)制】當(dāng)某一列的值全是NULL時(shí)，count(列名) 的返回結(jié)果為0，但sum(列名) 的返回結(jié)果為NULL，因此使用sum()時(shí)需注意NPE問題；

正例：可以使用如下方式來避免sum的NPE問題：select if(isnull(sum(g)), 0, sum(g)) from table;

【強(qiáng)制】使用ISNULL()來判斷是否為NULL值：

說明：NULL與任何值的直接比較都為NULL

NULL <> NULL 的返回結(jié)果是NULL，而不是false；

NULL = NULL 的返回結(jié)果是NULL，而不是true;

NULL <> 1的返回結(jié)果是NULL，而不是true;

【強(qiáng)制】在代碼中寫分頁查詢邏輯時(shí)，若count為0應(yīng)直接返回，避免執(zhí)行后面的分頁語句；

【強(qiáng)制】禁止使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決

說明：以學(xué)生和成績的關(guān)系為例，學(xué)生表中的student_id是主鍵，那么成績表中的student_id則為外鍵。如果更新學(xué)生表中的student_id，同時(shí)觸發(fā)成績表中的student_id更新，即為級聯(lián)更新。外鍵與級聯(lián)更新適用于單機(jī)低并發(fā)，不適合分布式、高并發(fā)集群；級聯(lián)更新是強(qiáng)阻塞，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險(xiǎn)；外鍵影響數(shù)據(jù)庫的插入速度；

【強(qiáng)制】禁止使用存儲過程，存儲過程難以調(diào)試和擴(kuò)展，更沒有移植性；

【強(qiáng)制】數(shù)據(jù)訂正（特別是刪除、修改記錄操作）時(shí)，要先select，避免出現(xiàn)誤刪除，確認(rèn)無誤才能執(zhí)行更新語句；

【推薦】in操作能避免則避免，若實(shí)在避免不了，需要仔細(xì)評估in后邊的集合元素?cái)?shù)量，控制在1000個(gè)之內(nèi)；

【參考】如果需要存儲表情，那么選擇utf8mb4來進(jìn)行存儲；如果有國際化需要，所有字符存儲與表示，均采用utf-8編碼，注意字符統(tǒng)計(jì)函數(shù)的區(qū)別：

select length("輕松工作"); 返回值為12；

select character_length("輕松工作"); 返回值為4

【參考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少，但 TRUNCATE 無事務(wù)且不觸發(fā) trigger，有可能造成事故，故不建議在開發(fā)代碼中使用此語句；

說明：TRUNCATE TABLE在功能上與不帶where子句的DELETE語句相同；

ORM規(guī)范

【強(qiáng)制】在表查詢中，一律不要使用*作為查詢的字段列表，需要哪些字段必須明確寫明；

會增加查詢分析器解析成本；

無用字段增加網(wǎng)絡(luò)消耗，尤其是text類型的字段；