【資料庫】資料庫設計規則、限制與索引概念

限制與索引

1、關系型資料庫設計規則

關系型資料庫設計規則

遵循ER模型和三範式

• E entity 代表實體的意思 對應到資料庫當中的一張表
• R relationship 代表關系的意思

ER模型 實體關系模型 Java OR Object relationship Hibernate、Mybatis ORM 對象關系映射

資料表中的那些鍵

資料庫中的鍵（Key）由一個或者多個屬性組成。

常用的幾種鍵和屬性的定義：

• 超鍵：能唯一辨別元組的屬性集叫做超鍵。

  唯一辨別元組

• 候選鍵：如果超鍵不包括多餘的屬性，那麼這個超鍵就是候選鍵。

  不包括多餘屬性的超鍵 候選鍵

• 主鍵：使用者可以從候選鍵中選擇一個作為主鍵。

  候選鍵的一個 主鍵

• 外鍵：如果資料表 R1 中的某屬性集不是 R1 的主鍵，而是另一個資料表 R2 的主鍵，那麼這個屬性集就是資料表 R1 的外鍵。

​ 外鍵 主鍵

• 主屬性：包含在任一候選鍵中的屬性稱為主屬性。

  包含在任一候選鍵的屬性稱為主屬性

• 非主屬性：與主屬性相對，指的是不包含在任何一個候選鍵中的屬性。

通常，也将候選鍵稱之為“碼”，把主鍵也稱為“主碼”。因為鍵可能是由多個屬性組成的，針對單個屬性，還可以用主屬性和非主屬性來進行區分。

候選鍵 碼 主鍵 主碼

• 三範式：1、列不能拆分 2、唯一辨別 3、關系引用主鍵

1.備援、重複 列不能拆分、設計表列細到不能拆分

2.唯一辨別 要區分

3.關系引用主鍵 一般引用盡量引用主鍵、唯一鍵

從 1NF 到 3NF

範式的定義會使用到主鍵和候選鍵（因為主鍵和候選鍵可以唯一辨別元組）

1NF 指的是資料庫表中的任何屬性都是原子性的，不可再分。這很好了解，列不能拆分了，在設計某個字段的時候，對于字段 X 來說，就不能把字段 X 拆分成字段 X-1 和字段 X-2。事實上，任何的 DBMS 都會滿足第一範式的要求，不會将字段進行拆分。列不能拆分

2NF 指的資料表裡的非主屬性都要和這個資料表的候選鍵有完全依賴關系。所謂完全依賴不同于部分依賴，也就是不能僅依賴候選鍵的一部分屬性，而必須依賴全部屬性。 非主屬性 候選鍵完全依賴

3NF 在滿足 2NF 的同時，對任何非主屬性都不傳遞依賴于候選鍵。也就是說不能存在非主屬性 A 依賴于非主屬性 B，非主屬性 B 依賴于候選鍵的情況。

具體展現

• 将資料放到表中，表再放到庫中。
• 一個資料庫中可以有多個表，每個表都有一個名字，用來辨別自己。表名具有唯一性。 資料庫有表，表有一個名字，唯一、辨別自己。
• 表具有一些特性，這些特性定義了資料在表中如何存儲，類似java和python中 “類”的設計。 資料在表中如何存儲？ 類 屬性、字段 字段名 資料類型

• 表由列組成，我們也稱為字段。每個字段描述了它所含有的資料的意義，資料表的設計實際上就是對字段的設計。建立資料表時，為每個字段配置設定一個資料類型，定義它們的資料長度和字段名。每個字段類似java 或者python中的“執行個體屬性”。字段 資料類型 字段名

  資料表的設計實際上就是對字段的設計

  設計字段 一個資料類型 資料長度 字段名

• 表中的資料是按行存儲的，一行即為一條記錄。每一行類似于java或python中的“對象”。

  資料按按行存儲，一行即為一條記錄，一個對象樣的

表 類

字段 屬性 列

行、記錄 對象、實體

屬性值一個個

設計資料表的原則

“三少一多”原則：

1.資料表的個數越少越好

RDBMS 的核心在于對實體和聯系的定義，也就是 E-R 圖（Entity Relationship Diagram），資料表越少，證明實體和聯系設計得越簡潔，既友善了解又友善操作。

關系型資料庫管理系統 實體和聯系的定義 E-R圖

資料表的個數越少越好，證明實體和聯系設計得越簡潔，既友善了解又友善操作。

2.資料表中的字段個數越少越好

字段個數越多，資料備援的可能性越大。設定字段個數少的前提是各個字段互相獨立，而不是某個字段的取值可以由其他字段計算出來。當然字段個數少是相對的，我們通常會在資料備援和檢索效率中進行平衡。

各個字段互相獨立 設計資料表 資料庫表中的字段個數越少越好

3.資料表中聯合主鍵的字段個數越少越好

設定主鍵是為了确定唯一性，當一個字段無法确定唯一性的時候，就需要采用聯合主鍵的方式（也就是用多個字段來定義一個主鍵）。聯合主鍵中的字段越多，占用的索引空間越大，不僅會加大了解難度，還會增加運作時間和索引空間，是以聯合主鍵的字段個數越少越好。

聯合主鍵個數越少越好

4.使用主鍵和外鍵越多越好

資料庫的設計實際上就是定義各種表，以及各種字段之間的關系。這些關系越多，證明這些實體之間的備援度越低，利用度越高。這樣做的好處在于不僅保證了資料表之間的獨立性，還能提升互相之間的關聯使用率。

使用主鍵和外鍵越多越好？？？

“三少一多”原則的核心就是簡單可複用。簡單指的是用更少的表、更少的字段、更少的聯合主鍵字段來完成資料表的設計。可複用則是通過主鍵、外鍵的使用來增強資料表之間的複用率。因為一個主鍵可以了解是一張表的代表。鍵設計得越多，證明它們之間的使用率越高。

簡單可複用 用更少的表 更少的字段 更少的聯合主鍵字段來完成資料表的設計

可複用 主鍵 外鍵的使用來增強資料表之間的複用率

2、限制與索引的概念

1、資料完整性（Data Integrity）是指資料的精确性（Accuracy）和可靠性（Reliability）。它是應防止資料庫中存在不符合語義規定的資料和防止因錯誤資訊的輸入輸出造成無效操作或錯誤資訊而提出的。

資料完整性 資料的精确性 可靠性

資料的完整性要從以下四個方面考慮：

• 實體完整性（Entity Integrity）：例如，同一個表中，不能存在兩條完全相同無法區分的記錄
• 域完整性（Domain Integrity）：例如：年齡範圍0-120，性别範圍“男/女”
• 引用完整性（Referential Integrity）：例如：員工所在部門，在部門表中要能找到這個部門
• 使用者自定義完整性（User-defined Integrity）：例如：使用者名唯一、密碼不能為空等，本部門經理的工資不得高于本部門職工的平均工資的5倍。

2、根據限制的特點，分為幾種：

• 鍵限制：主鍵限制、外鍵限制、唯一鍵限制
• Not NULL限制：非空限制
• Check限制：檢查限制
• Default限制：預設值限制
• 自增限制

鍵限制 主鍵限制 外鍵限制 唯一鍵限制

非空限制Not NULL 檢查限制Check 預設值限制Default 自增限制

資料表的常見限制

建立資料表的時候，還會對字段進行限制，限制的目的在于保證 RDBMS 裡面資料的準确性和一緻性。來看下常見的限制有哪些。

首先是主鍵限制。

主鍵起的作用是唯一辨別一條記錄，不能重複，不能為空，即 UNIQUE+NOT NULL。一個資料表的主鍵隻能有一個。主鍵可以是一個字段，也可以由多個字段複合組成。

其次還有外鍵限制。

外鍵確定了表與表之間引用的完整性。一個表中的外鍵對應另一張表的主鍵。外鍵可以是重複的，也可以為空。

除了對鍵進行限制外，還有字段限制。

唯一性限制。

唯一性限制表明了字段在表中的數值是唯一的，即使我們已經有了主鍵，還可以對其他字段進行唯一性限制。需要注意的是，唯一性限制和普通索引（NORMAL INDEX）之間是有差別的。唯一性限制相當于建立了一個限制和普通索引，目的是保證字段的正确性，而普通索引隻是提升資料檢索的速度，并不對字段的唯一性進行限制。

NOT NULL 限制。對字段定義了 NOT NULL，即表明該字段不應為空，必須有取值。

DEFAULT，表明了字段的預設值。如果在插入資料的時候，這個字段沒有取值，就設定為預設值。

CHECK 限制，用來檢查特定字段取值範圍的有效性，CHECK 限制的結果不能為 FALSE。

3、限制（CONSTRAINTS）與索引（INDEX）

constraints、index

限制是用來對資料業務規則和資料完整性進行實施、維護。限制的作用範圍僅限在目前資料庫，限制可以被當做資料庫對象來處理，它們具有名稱和關聯模式。是邏輯限制，不會因為設定限制而額外占用空間。

索引是一個單獨、實體的存儲在資料頁上的資料庫結構，它是表中一列或若幹列值的集合和相應的指向表中資料值的實體辨別資料頁的邏輯指針清單（類似于新華字典的目錄索引頁）。索引的存在會增加資料庫的存儲空間，也會使插入、修改資料的時間開銷變多(因為插入和修改資料時，索引也要随之變動），但是可以大大提高查詢速度。是以應該在鍵列、或其他經常要查詢、排序、按範圍查找的列上建立索引，而對于在查詢中很少使用和參考的列、修改非常頻繁的列，值很少的列（例如性别隻有男和女）等列上不應該建立索引。

資料庫中的索引，就好比一本書的目錄，它可以幫我們快速進行特定值的定位與查找，進而加快資料查詢的效率。

索引就是幫助資料庫管理系統高效擷取資料的資料結構。

雖然使用索引的本質目的是幫我們快速定位想要查找的資料，但實際上，索引有很多種類。

從功能邏輯上說，索引主要有 4 種，分别是普通索引、唯一索引、主鍵索引和全文索引。

普通索引是基礎的索引，沒有任何限制，主要用于提高查詢效率。唯一索引就是在普通索引的基礎上增加了資料唯一性的限制，在一張資料表裡可以有多個唯一索引。主鍵索引在唯一索引的基礎上增加了不為空的限制，也就是 NOT NULL+UNIQUE，一張表裡最多隻有一個主鍵索引。全文索引用的不多，MySQL 自帶的全文索引隻支援英文。我們通常可以采用專門的全文搜尋引擎，比如 ES(ElasticSearch) 和 Solr。

按照實體實作方式，索引可以分為 2 種：聚集索引和非聚集索引。我們也把非聚集索引稱為二級索引或者輔助索引。

Mysql會在主鍵、唯一鍵、外鍵列上自動建立索引，其他列需要建立索引的話，需要手動建立。

其中主鍵删除，對應的索引也會删除

删除唯一鍵的方式是通過删除對應的索引來實作的

删除外鍵，外鍵列上的索引還在，如果需要删除，需要單獨删除索引