資料庫設計注意事項

DBMS資料庫管理系統

資料庫設計

1、有效存儲
2、高效通路      

目的：

1、減少資料備援
2、避免資料維護異常
3、節約存儲空間
4、高效的通路      

資料庫設計過程:

1、需求分析: 分析需要存儲的資料是哪些,這些資料有哪些屬性,這些屬性各自的特點是什麼
2、邏輯設計: 使用ER圖對資料庫進行邏輯模組化,
3、實體設計: 使用哪款資料庫設計建表(oracle,myslq,sqlserver)
4、維護優化: 索引優化,大表拆分      

資料庫設計布置

需求分析—>邏輯設計—>實體設計—>優化      

需求分析

資料内容

資料存儲特性：時效性

資料生命周期：增長快、量大、非核心，分庫分表，歸檔清理規則

實體間及實體與表的關系，1對1，1對多，多對多

實體的屬性，單一屬性or屬性組合 可以唯一辨別實體的

邏輯設計

ER圖例說明:

矩形:表示實體集,矩形内寫實體的名字
菱形:表示聯系集
橢圓:表示實體屬性
線段:将屬性連結到實體集      

名詞解釋

關系是表，
元組是行，
屬性是列。
候選碼是屬性組，可以唯一的确定一個元祖（列）
候選碼中選一個當主碼
域是屬性的取值範圍
分量是元組中的一個屬性值      

常見資料庫設計範式：

第一範式、所有字段不可拆分
第二範式、單關鍵字
第三範式、不存在傳遞依賴
BC範式(Boyce.Codd)、解決部分主鍵依賴于非主鍵部分
第四範式
第五範式      

插入異常，更新異常，删除異常

資料備援：重複資料

資料庫實體設計

1、選擇合适的資料庫管理系統

-商業資料庫 oracle、sqlserver

-開源資料庫 mysql pgsql

2、定義資料庫、表以及字段的命名規範

3、根據所選的dbms系統選擇合适的字段類型

4、反範式化設計：刻意增加備援，以空間換取時間

MySQL常用的存儲引擎：

MylSAM   不支援事務  表級鎖 讀寫效率更高
MRG_MYISAM   不支援事務，表級鎖 分段歸檔
Innodb（推薦）  支援事務   行級鎖 
Archive 行級鎖  日志記錄
Ndb cluster(MYSQL叢集) 支援事務 行級鎖 高可用      

資料庫表及字段的命名規則：

可讀性原則（用大小寫區分來提高可讀性等）
表意性原則（表的名稱應能展現其存儲内容等）
長名原則（少用縮寫）      

字段類型選擇原則

優先選擇數字類型,其次是日期和二進制類型,最後才是字元串類型;
字元比資料處理慢;
在資料庫中,資料處理以頁為機關,列的長度越小,利于性能提升;
磁盤的I/O性能決定了資料庫的性能。
數字的查詢和排序操作優于char和varchar;      

decimal 與 float 如何選擇:

decimal用于存儲精确資料
float隻能用于存儲非精确資料      

char 與 varchar 如何選擇：

如果列中要存儲的資料長度差不多是一緻的，則應該考慮用char，反之使用varchar。
如果列中的最大資料長度小于50byte（utf-8格式中大概13個字元），則一般也考慮用char。（如果這個 列很少用，内裡基于節省空間和減少I/O的考慮，還是可以選擇用varchar）
一般不宜定義大于50Byte的char類型列
在mysql中，utf8的一個字元占3個位元組，
當某個字段的字元數大于15時，要用varchar，小于就用char      

時間類型如何存儲：

使用int來存儲時間字段的優缺點
優點： 字段長度比datetime小。
缺點：使用不友善，要進行函數轉換。
限制：隻能存儲到2038-1-19 11:14:07 即2^32為2147483648      

注意事項：

主鍵: 可以存儲業務主鍵(辨別業務資料,進行智語表關聯)和資料庫主鍵(優化資料庫)
避免使用外鍵限制: 降低資料導入效率,增加維護成本
避免使用觸發器: 導緻意想不到的資料異常,是業務邏輯變複雜
嚴禁預留字段      

為什麼反範式化

讀 > 寫
減少表的關聯數量
增加資料的讀取效率
反範式化一定要适度      

維護資料

維護各優化中要做什麼

1、維護資料字典
2、維護索引
3、維護表結構
4、在适當的時候對表進行水準拆分或垂直拆分      

導出資料字典

SELECT a.table_name,b.TABLE_COMMENT,a.COLUMN_NAME,a.COLUMN_TYPE,a.COLUMN_COMMENT FROM information_schema.COLUMNS a JOIN information_schema.TABLES b ON a.table_schema = b.table_schema AND a.table_name = b.table_name WHERE a.table_name = 'customer'      

如何維護索引

建立索引：出現在WHERE從句， GROUP BY從句， ORDER BY 從句中的列
可選擇性高的列要放到索引的前面
索引中不要包括太長的資料類型      

索引并不是越多越好，過多的索引不但會降低寫效率而且會降低讀的效率

定期維護索引碎片

在SQL語句中不要使用強制索引關鍵字      

資料庫中适合的操作

批量操作
禁止使用 select * 這樣的查詢
控制使用使用者自定義函數
不要使用資料庫中的全文索引      

垂直拆分：

經常一起查詢的列放在一起
text，blob等大字段拆分出到附加表中      

水準拆分

表結構相同 資料不同
通過主鍵hash->平均分表