mysql資料庫設計規範

標籤：兩種   order   distinct   計算   資料類型   char   執行計畫   單表   mil   

　　　　　　　　　　　　　　　　　　　　　　　　　　　Mysql資料庫設計規範

1 資料庫所有對象均以小寫字母加底線（因為mysql是大小寫敏感的）

2 禁止使用mysql關鍵字

3 關聯列的列名和資料類型盡量保持一次，比如訂單表的使用者id user_id和購物車表裡面的使用者id  user_id,(因為如果不同的話，在關聯查詢時會進行隱式的轉換，這樣有可能造成列上的索引失效)

4 使用innodb為預設的儲存引擎

5 統一使用utf-8 字元集，mysql使用utf-8的一個字元，佔用三個位元組記憶體。如varchar(10)最多可佔用30個位元組

6 所有表和欄位均需要添加註釋，並且如果有枚舉含義的話注釋裡面需要指出欄位值的含義如 1代表進行中，2代表已完成等等

7 單表資料量最好控制在500萬行之內

8 UUID作為主鍵的優勢和劣勢是什嗎？

9 常用建立索引的列 where 從句出現 order by,group by distinct 列的出現 join 關聯列出現

10 聯合索引的建立原則

   1 區分度（唯一值的數量與總行數的比值）最高的列放在聯合索引的最左側

   2 在第一條相差不大的情況下，應該盡量把欄位長度小的列放在坐側

   3 避免冗餘索引和重複索引

11 盡量不使用外鍵約束，外鍵關係建議在業務代碼中實現(因為外鍵會降低效能)

12 優先選擇符合儲存需要的最小資料類型 如:

　　1 將字串轉換為數字類型儲存 因為int類型佔用空間一般更小，（將ip地址轉為整形） INET_ATON(‘255.255.255.255‘) = 4294967295 ,同時mysql還提供反      向功能 INET_NTOA(4294967295) = ‘255.255.255.255‘

　　2 非負數選擇unsigned儲存，因為這樣可以儲存的有效數更多

　　3 VARCHAR(N)中的N代表字元數而不是位元組數

13 避免使用TEXT,BLOB等類型，這兩種類型會使得sql效能變差，但也不是說不能使用者兩種類型

14 避免是mysql的ENUM類型，枚舉本身是字串類型，但內部儲存使用整數類型進行儲存，因此只能儲存65535個值，這是枚舉類型的一個優點，化字串為整形儲存

　　但枚舉類型也存在缺陷，修改ENUM值需要使用ALTER語句，ENUM 需要在進行order by操作時 需要先有整形轉字串 再進行排序操作，效率比較低 這也是字串轉整形的劣勢之一。另外 禁止使用整形值得枚舉類型

15  盡量把列都定義為not null, 第一 如果在這樣的列增加索引的話，null列需要更多的空間來儲存列是否為空白的狀態值，所以會佔用更多的索引空間，索引空間更大則索   引效率更低，其次 在進行比較和計算的時候 需要特別的處理

16  不要使用字串 儲存日期和時間 第一 無法使用時間函數 另外字串需要更多的空間來儲存

17  金額計算 使用decimal 類型 因為這種類型在計算的時候不會丟失精度

18 盡量使用先行編譯語句 ，第一 可以避免sql注入 ，第二 可多次使用執行計畫

19 避免隱式轉換 ， sql隱式轉換一般出現在where 語句中 當列類型和參數類型不一致時就是容易出現飲食轉換 如主鍵id的 where id= ‘11‘ 隱式轉換可能導致索引   無效

 

20 避免 前置模糊查詢和全模糊查詢 這樣不會使用索引，後置模糊查詢則會使用索引

21  使用 left join 和not exists 來最佳化not in操作 not in 操作會使索引失效

22  禁止select *

23  避免使用子查詢 ，可以使用關聯查詢來進行最佳化，因為子查詢效率更低 首先子查詢返回的暫存資料表 無論是記憶體暫存資料表還是磁碟暫存資料表 都是無法使用索引的，如果 　　子查詢返回的資料量大這毫無疑問會降低sql效能 ，最後子查詢產生的大量的暫存資料表會消耗io和cpu資源 產生大量的慢查詢，

24 避免連表太多，mysql 本身最多能關聯61張表查詢，建議不超過5張表 ，關聯表太多會出現更多的關聯緩衝，一些關聯操作也會產生暫存資料表 暫存資料表是無索引的，會降低效能

25 合并多個alter操作 alter操作會阻塞表

26 使用in代替or，in操作 會有效使用索引

27 禁止使用 order by rand() 推薦 程式來擷取隨機值

28 避免在where 從句中進行函數轉換和計算 ， 這樣無法使用索引

 　　可以使用以下來最佳化

　　where date(create_time) = ‘20160601’ 可以最佳化為

 　　where create_time>=  ‘20160601’ and  create_time <  ‘20160602’ 

29 當明顯不含有重複值得時候，使用union all 而不是union，union會把所有資料放到暫存資料表中再進行去重操作 當資料量很大的時候 這種去重操作會使sql效能急劇下降 　　union all 則不會去重操作，比如合并多種類型的訂單操作 由於一個訂單只會屬於一個類型 這時候就可以使用union all了

30 盡量做到資料的冷熱分離 減小表的寬度,比如使用者表，常用的欄位 使用者名稱 密碼等等 屬於熱資料,而生日 性別 等等屬於冷資料 ，因此我們也把冷資料拆分為使用者    擴充表 達到分離的效果

 

mysql資料庫設計規範