數據庫設計
設計的原則
1、熟悉需求
2、符合開發規范
命名規范
明確實體與數據表的關系
不能即沒有主鍵也沒有外鍵
3、審核數據庫設計
4、基本表的特征
1、原子性：字段不能再分解
2、原始性：是對原始數據的記錄，不是拼
接出來的信息
3、演繹行：有基本表和關系表中的
數據，可以派生出任何想要的數據
4、穩定性：相對穩定，長期保存
5、必須滿足一二范式，盡量滿足第三范式
第一范式：對屬性的原子性約束，不可再分
第二范式：記錄的唯一性約束，即必須有主鍵
第三范式：對字段冗余的約束，一個表中的字
段不能包含其他表的非主鍵字段
6、簡潔地E-R圖
7、符合完整性約束
設計的模型
關系模型ER 強調關系
對象模型OR 強調的是實體的個體
設計的技巧
1、表越少越好，表中的組合主鍵的字段越少越好、字段越少越好
2、檢查變化字段
3、避免使用數據庫保留字
4、采用刪除標記字段代替刪除
5、避免使用觸發器
6、發現在輸入時重復數據，則需要創建新的表核心的關系
7、使用數據庫設計工具
8、創建ER圖表和數據字典
9、添加字段修改記錄時間、修改人、修改後的版本
10、地址和電話采用多個字段
SQL優化
劣質SQL 對系統產生不好的影響的SQL
1、運行時間超長
2、引發嚴重的等待時間
3、不能滿足壓力測試
4、消耗大量的系統資源
索引
使用索引可以快速訪問數據庫表的特點信息
問題
1、占用表空間
2、影響DML性能
使用的條件
1、一個字段中包含大量的值
2、一個字段中包含多個空值
3、多個字段經常出現在where中
4、表非常大並且查詢返回的數據量低於總數據的20%
索引的創建
CREATE INDEX 索引名 ON 表名(列名) TABLESPACE 表空間名
索引類型
列值唯一性
唯一索引
非唯一索引
索引列個數
單列索引
復合索引，第一個列為主導列
使用情況
當SQL語句時WHERE字句用到復合索引的主導列
當某幾個列在SQL語句的WHERE字句通常用and連在一起使用
當SQL常常查詢幾個同樣的列值時
索引列物理組織方式
B樹索引 適用於列中數據較多的情況
反向鍵索引 用於大的集群系統，多用戶操作統一數據塊
CREATE INDEX 索引名 ON 表名(列名) REVERSE TABLESPACE 表空間名
基於函數的索引 基於一個或多個列上的函數或表達式創建的索引
用於一個表中的字段經常被函數所調用的情況
CREATE INDEX 索引名 ON 表名(函數(列名)) TABLESPACE 表空間名
位圖索引 處理數據的聚合關系，用於重復數據較多的情況
CREATE BITMAP INDEX 索引名 ON 表名(表名(列名)) TABLESPACE 表空間名
表分區
應用場景
為了提高數據的可用性，檔表內容或者結構非常大的時候
優點
1、提高數據可用性
2、減少管理負擔
3、改善語句性能(減少並發)
分區方式
區間分區 根據一個列值得范圍劃分
RERTITION BY RANGE(列名) (
PARTITION 分區表名 VALUES LESS THEN
(值) TABLESPACE 表空間
)
LESS THEN 表示小於
VALUES LESS THEN (maxvalue)表示其他
//============實例==================================
CREATE TABLE 表名 (
dt_draw date not null
)
RERTITION BY RANGE(dt_draw) (
PARTITION 分區表名 VALUES LESS THEN
(to_date('31/12/2008','dd/mm/yyyy')) TABLESPACE 表空間
)
散列分區 對分區間應用一個散列函數，依次確定
應放在N個分區的哪一個分區
分區表的樹木間以為2的N次方
RERTITION BY HASH(列名) (
PARTITION 分區表名 TABLESPACE 表空間
)
//============實例==================================
CREATE TABLE 表名 (
dt_draw date not null
)
RERTITION BY RANGE(dt_draw) (
PARTITION 分區表名1 TABLESPACE 表空間1，
PARTITION 分區表名2 TABLESPACE 表空間2，
PARTITION 分區表名3 TABLESPACE 表空間3
)
列表分區 將數據按照列的值分區
RERTITION BY LIST(列名) (
PARTITION 分區表名 values (指定值1,指定值2) TABLESPACE 表空間
)
//============實例==================================
CREATE TABLE 表名 (
dt_draw date not null
)
RERTITION BY LIST(dt_draw) (
PARTITION 分區表名 values (指定值1,指定值2),
PARTITION 分區表名 values (指定值3,指定值4),
PARTITION 分區表名 values (指定值5,指定值6)
)
tablespace 表空間
組合分區 之前分區方式的組合
區間散列組合
PARTITION BY RANGE(列1) SUBPARTITION BY HASH (列2) (
PARTITION VALUES LESS THAN (值) TABLESPACE 表空間 ,
)
區間列表組合
PARTITION BY RANGE(列1) SUBPARTITION BY LIST (列2) (
PARTITION VALUES LESS THAN (值) TABLESPACE 表空間 (
SUBPARTITION 子分區名 VALUES (指定值1,指定值2) TABLESPACE 表空間
)
)
維護方式
增加分區
ALTER TABLE 表名 ADD PARTITION 分區表名 VALUES LESS THAN (值)
注意，不能包含小於最大分區分區條件的分區
刪除分區
ALTER TABLE 表名 DROP PARTITION 分區表名
截斷分區 刪除分區中的數據
ALTER TABLE 表名 TRUNCATE PARTITION 分區表名
合並分區
ALTER TABLE 表名 MERGE PARTITIONS 要合並的分區 INTO PARTITION 合並到的分區
注意，高接線不能合並到低接線
分區維護
SQL語句的優化
1、選擇合適的Oracle優化器
CBO 基於成本優化的優化器
優化模式
Rule 基於規則
Choose 默認，有統計信息CBO，否則，RBO
First Row 以最快的速度返回前幾行
All Rows 以最快的速度返回所有行
2、選擇恰當的查詢方式
方式
全表掃描
ROWID掃描 單行數據
索引掃描 CBO下有效，索引中直接得到的數據
快速索引掃描
3、善於利用共享SQL語句
存放在SGA中，系統全局中
