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)：一個表中不能包涵其他相關表中非關鍵字段的資訊,即資料表不能有沉澱字段;

備註：必須先滿足第二範式;

資料庫的三範式：

①欄位不可分。

②有主鍵，非主鍵欄位依賴主鍵。

③非主鍵欄位不能互相依賴。

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′
union
select 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教學》

以上是MySQL設計規範與原則的詳細內容。更多資訊請關注PHP中文網其他相關文章！