MYSQL資料庫設計規範與原則

標籤：

MYSQL資料庫設計規範1、資料庫命名規範採用26個英文字母(區分大小寫)和0-9的自然數(經常不需要)加上底線‘_‘組成;命名簡潔明確(長度不能超過30個字元);例如：user, stat, log, 也可以wifi_user, wifi_stat, wifi_log給資料庫加個首碼;除非是備份資料庫可以加0-9的自然數：user_db_20151210;2、資料庫表名命名規範採用26個英文字母(區分大小寫)和0-9的自然數(經常不需要)加上底線‘_‘組成;命名簡潔明確,多個單詞用底線‘_‘分隔;例如：user_login, user_profile, user_detail, user_role, user_role_relation, user_role_right, user_role_right_relation表首碼‘user_‘可以有效把相同關係的表顯示在一起;3、資料庫表欄位名命名規範採用26個英文字母(區分大小寫)和0-9的自然數(經常不需要)加上底線‘_‘組成;命名簡潔明確,多個單詞用底線‘_‘分隔;例如：user_login表欄位 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;每個表中必須有自增主鍵,add_time(預設系統時間)表與表之間的相關聯欄位名稱要求儘可能的相同;4、資料庫表欄位類型規範用盡量少的儲存空間來存數一個欄位的資料;例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);IP地址最好使用int類型;固定長度的類型最好使用char,例如：郵編;能使用tinyint就不要使用smallint,int;最好給每個欄位一個預設值,最好不能為null;5、資料庫表索引規範命名簡潔明確,例如：user_login表user_name欄位的索引應為user_name_index唯一索引;為每個表建立一個主鍵索引;為每個表建立合理的索引;建立複合索引請謹慎;6、簡單熟悉資料庫範式第一範式(1NF)：欄位值具有原子性,不能再分(所有關係型資料庫系統都滿足第一範式);例如：姓名欄位,其中姓和名是一個整體,如果區分姓和名那麼必須設立兩個獨立欄位;第二範式(2NF)：一個表必須有主鍵,即每行資料都能被唯一的區分;備忘：必須先滿足第一範式;第三範式(3NF)：一個表中不能包涵其他相關表中非關鍵字段的資訊,即資料表不能有沉餘欄位;備忘：必須先滿足第二範式;備忘：往往我們在設計表中不能遵守第三範式,因為合理的沉餘欄位將會給我們減少join的查詢;  例如：相簿表中會添加圖片的點擊數欄位,在相簿圖片表中也會添加圖片的點擊數欄位;  MYSQL資料庫設計原則1、核心原則不在資料庫做運算;cpu計算務必移至業務層;控制列數量(欄位少而精,欄位數建議在20以內);平衡範式與冗餘(效率優先；往往犧牲範式)拒絕3B(拒絕大sql語句：big sql、拒絕大事物：big transaction、拒絕大批量：big batch);2、欄位類原則用好數實值型別(用合適的欄位類型節約空間);字元轉化為數字(能轉化的最好轉化,同樣節約空間、提高查詢效能);避免使用NULL欄位(NULL欄位很難查詢最佳化、NULL欄位的索引需要額外空間、NULL欄位的複合索引無效);少用text類型(盡量使用varchar代替text欄位);3、索引類原則合理使用索引(改善查詢,減慢更新,索引一定不是越多越好);字元欄位必須建首碼索引;不在索引做列運算;innodb主鍵推薦使用自增列(主鍵建立聚簇索引,主鍵不應該被修改,字串不應該做主鍵)(理解Innodb的索引儲存結構就知道了);不用外鍵(由程式保證約束);4、sql類原則sql語句儘可能簡單(一條sql只能在一個cpu運算,大語句拆小語句,減少鎖時間,一條大sql可以堵死整個庫);簡單的事務;避免使用trig/func(觸發器、函數不用用戶端程式取而代之);不用select *(消耗cpu,io,記憶體,頻寬,這種程式不具有擴充性);OR改寫為IN(or的效率是n層級);OR改寫為UNION(mysql的索引合并很弱智);select id from t where phone = ’159′ or name = ‘john’;=>select id from t where phone=’159′unionselect id from t where name=’jonh’避免負向%;慎用count(*);limit高效分頁(limit越大，效率越低);使用union all替代union(union有去重開銷);少用串連join;使用group by;請使用同類型比較;打散批次更新;5、效能分析工具show profile;mysqlsla;mysqldumpslow;explain;show slow log;show processlist;

　　

MYSQL資料庫設計規範與原則