mysql設(shè)計(jì)規(guī)范

1、Mysql設(shè)計(jì)規(guī)范1. 模型設(shè)計(jì)流程l 設(shè)計(jì)工具待定2. 命名規(guī)范2.1 基本原則數(shù)據(jù)模型中的表(Table)、字段(Column)、索引(Index)、視圖(View)等數(shù)據(jù)庫(kù)對(duì)象均應(yīng)遵循以下命名規(guī)范：l 命名全部采用小寫(xiě)字母l 名稱的各部分之間用下劃線"_"連接l 命名應(yīng)采用能夠準(zhǔn)確反映對(duì)象中文含義的英文單詞或英文縮寫(xiě)，盡量不使用漢語(yǔ)拼音（中英文命名對(duì)照表詳見(jiàn)附錄A）l 命名長(zhǎng)度一般不超過(guò)32個(gè)字符l 名稱較長(zhǎng)時(shí)使用英文縮寫(xiě)，相同縮寫(xiě)含義應(yīng)相同l 禁止使用mysql保留字l 默認(rèn)使用 innodb 存儲(chǔ)引擎，使用其他引擎必須注明緣由l 使用utf8字符集l 表必須有主鍵

2、,推薦使用UNSIGNED自增列作為主鍵l 避免使用存儲(chǔ)過(guò)程、觸發(fā)器、視圖、自定義函數(shù)等2.2 表名l 表名構(gòu)成：模塊名前綴 + 表主體名 + 表后綴（可選） l 模塊名前綴：表歸屬模塊l 表主體名：準(zhǔn)確表達(dá)表含義，盡量使用英文全稱，表名較長(zhǎng)時(shí)使用英文縮寫(xiě)l 表后綴：可選項(xiàng)，表示關(guān)系（rlt）、明細(xì)（dtl）、周期（mm/dd）等表的類型2.3 字段名l 字段名需準(zhǔn)確表達(dá)字段的含義l 可根據(jù)情況增加表名前綴l 不同表中相同字段名的含義、字段類型、取值含義應(yīng)相同l 不使用數(shù)據(jù)庫(kù)關(guān)鍵字l 盡量使用英文縮寫(xiě)l 盡量不使用單個(gè)單詞（約定的通用字段除外）l 字段全部定義為 NOT NULL2.4 主鍵名

3、l 主鍵名構(gòu)成：pk_ + 表名l 名稱較長(zhǎng)時(shí)使用表名縮寫(xiě)減小長(zhǎng)度，注意表名縮寫(xiě)的唯一性2.5 索引2.5.1 索引命名l 索引名稱全部使用小寫(xiě)l 非唯一索引按照“idx_字段名稱_字段名稱_字段名稱”進(jìn)行命名l 唯一索引按照“uq_字段名稱_字段名稱_字段名稱”進(jìn)行命名2.5.2 建立索引規(guī)范l 唯一索引由 3 個(gè)以下字段組成并且字段都是整形時(shí)，使用唯一索引作為主鍵。沒(méi)有唯一索引或唯一索引不符合上述條件時(shí)，使用自增 id 作為主鍵。注意唯一索引不和主鍵重復(fù)l 單張表的索引數(shù)量控制在字段數(shù)的 20%以內(nèi)，至多 5 個(gè)，索引數(shù)量過(guò)多會(huì)導(dǎo)致寫(xiě)入性能 的顯著下降l 合理創(chuàng)建復(fù)合索引。首先要避免冗余，

4、ix_a_b_c 相當(dāng)于同時(shí)創(chuàng)建了 ix-_a,ix_a_b,ix_a_b_c 三個(gè)索引；其次要避免索引過(guò)大，建議最大 4 列復(fù)合，列數(shù)過(guò)多很難提升索引的區(qū)分度，反而降低索引的性能l 合理使用覆蓋索引l 對(duì)于長(zhǎng)度大于 100 的 varchar 字段建立索引時(shí)，使用其他方法l 使用 EXPLAIN 判斷 SQL 語(yǔ)句是否合理使用索引，盡量避免 extra 列出現(xiàn) FILE SORT,USINGTEMPORARYl 索引不只用于 select 查詢，update 和 delete 語(yǔ)句也需要根據(jù) where 條件合理設(shè)計(jì)索引l where 條件中的非等值條件（IN,BETWEEN,<,&l

5、t;=,>,>=）會(huì)導(dǎo)致后面的條件無(wú)法使用索引2.6 視圖l 視圖名構(gòu)成：v_ + 模塊名前綴 + 視圖主體名 + 后綴（可選） 3. 數(shù)據(jù)類型規(guī)范l 數(shù)值型- 存儲(chǔ)精確浮點(diǎn)數(shù)使用 DECIMAL,替代 FLOAT 和 DOUBLE;- 根據(jù)字段長(zhǎng)度選擇合適的字段類型，如數(shù)字類型有 tinyint，smallint，mediuint，int，bigint 五種類型，分別占用 1byte，2byte，3byte，4byte，8byte。需要特別注意，int（10）和 int（2）無(wú)區(qū)別，應(yīng)該采用 tinyint（2）替代 int（2）;- 盡量使用 tinyint 代替 enum 和

6、 set 類型，減少后臺(tái)類型轉(zhuǎn)換;- 使用UNSIGNED存儲(chǔ)非負(fù)數(shù)值l 字符型- Varchar(N)中，N 表示的是字符數(shù)不是字節(jié)數(shù)，如 varchar（255），可以最大存儲(chǔ)255 個(gè)漢字。N 值應(yīng)盡可能小，單表 varchar 字段最大長(zhǎng)度為 65536 個(gè)字節(jié)，在排序和創(chuàng)建臨時(shí)表等內(nèi)存操作時(shí)，會(huì)使用 N 值來(lái)申請(qǐng)內(nèi)存，而非存儲(chǔ)值的實(shí)際長(zhǎng)度l 日期時(shí)間型- 存儲(chǔ)年使用 year 類型，存儲(chǔ)日期使用 date 類型- 存儲(chǔ)日期使用 date 類型- 存儲(chǔ)時(shí)間（精確到秒）使用timestamp 類型, 而非 datetime 類型, 因?yàn)?timestamp 使用 4 個(gè)字節(jié)，而 date

7、time 使用 8 個(gè)字節(jié)l 其它數(shù)據(jù)類型- 使用 int unsigned 存儲(chǔ) IP 地址，通過(guò) MySQL 函數(shù) inet_ntoa 和inet_aton 來(lái)進(jìn)行轉(zhuǎn)化- 避免使用 text、blob 字段類型4. SQL 語(yǔ)句設(shè)計(jì)l 使用 prepared statement，可以提升性能并且避免 SQL 注入l 降低 SQL 的復(fù)雜度，把 MySQL 盡量當(dāng)做存儲(chǔ)使用- 避免在 SQL 語(yǔ)句中進(jìn)行數(shù)學(xué)運(yùn)算、函數(shù)計(jì)算、邏輯判斷等操作- 避免多表 join，盡量拆分成多條查詢。如無(wú)法避免，在 join 表時(shí)應(yīng)使用相同類型的列，并且在列上有索引- 避免使用存儲(chǔ)過(guò)程、觸發(fā)器、函數(shù)等l Inse

8、rt 語(yǔ)句使用 batch 提交（insert into table values （），（），（），）,values 的個(gè)數(shù)不超過(guò) 500；sql 語(yǔ)句中 in 包含的值不超過(guò) 500l Update，delete 語(yǔ)句避免使用 limit，如果確實(shí)需要分配處理大量數(shù)據(jù)，可以增加其他字段來(lái)限制每次處理的記錄數(shù)，比如主鍵 idl 避免使用 select *l 避免使用 order by rand（），使用其他方式替換l 使用合理的分頁(yè)方式以提高分頁(yè)的效率l 統(tǒng)計(jì)表中記錄數(shù)時(shí)使用 count(*),而不是 count（pk）或 count（1）l 數(shù)據(jù)庫(kù)默認(rèn)開(kāi)啟查詢緩存，合理利用查詢緩存提升 s

9、ql 效率l 當(dāng)只需要 1 行數(shù)據(jù)時(shí)使用 limit 1l 拆分大的 delete 和 insertl Where 條件中使用合適的類型，數(shù)值不加引號(hào)，字符加引號(hào)，避免 MySQL 進(jìn)行隱式類型轉(zhuǎn)換，從而無(wú)法使用索引l 避免使用 or，對(duì)同一個(gè)字段將 or 改為 in，對(duì)不同字段將 or 改為 unionl 盡量避免負(fù)向查詢，如 NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE 等l 針對(duì)同一張表的 alter table 操作，應(yīng)該用逗號(hào)分隔，一次完成l 注意 MySQL 中 insert ignore into；insert on

10、 duplicate key update；replace into 的區(qū)別，在應(yīng)用中合理使用5. 其他l 盡量減小事務(wù)：事務(wù)使用原則是即開(kāi)即用，用完即關(guān) ；事務(wù)無(wú)關(guān)操作放到事務(wù)外面, 減少鎖資源的占用；在不破壞一致性前提下，使用多個(gè)短事務(wù)代替長(zhǎng)事務(wù)l 可重復(fù)讀（repeatable read）是 MySQL 的默認(rèn)事務(wù)隔離級(jí)別，解決了臟讀（dirty read）和幻讀（phantom read）的問(wèn)題，原則上禁止修改事務(wù)的隔離級(jí)別l 盡量使用短連接，完成查詢后要主動(dòng)釋放連接，避免 MySQL 中出現(xiàn)大量 sleep 線程l 不管使用連接池還是直連 MySQL，執(zhí)行查詢前都需要考慮獲取的 MySQL 連接可能已經(jīng)斷開(kāi)，如斷開(kāi)可以重連。執(zhí)行查詢后都要檢查查詢是否成功，如不成功，考慮是否需要重新執(zhí)行6. 模塊劃分模塊名稱英文名稱模塊前綴模塊說(shuō)明7. 文檔列表l 邏輯模型（*.cdm）按業(yè)務(wù)需求功能及規(guī)范設(shè)計(jì)的邏輯模型（包括實(shí)體、關(guān)系、屬性、碼等）l 物理模型（*.pdm）從邏輯模型生成某特定數(shù)據(jù)庫(kù)（如