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

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

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

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

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

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

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

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

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

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

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