## 什么是數(shù)據(jù)庫, 數(shù)據(jù)庫管理系統(tǒng), 數(shù)據(jù)庫系統(tǒng), 數(shù)據(jù)庫管理員?

* 數(shù)據(jù)庫：是長期儲存在計算機內、有組織的、可共享的大量**數(shù)據(jù)的集合**。

* 數(shù)據(jù)庫管理系統(tǒng)（DBMS）：例如mysql，是**位于用戶與操作系統(tǒng)之間**的一層數(shù)據(jù)管理軟件。DBMS能夠科學地組織和存儲數(shù)據(jù)、高效地獲取和維護數(shù)據(jù)。DBMS具有數(shù)據(jù)定義，數(shù)據(jù)組織、存儲和管理，數(shù)據(jù)庫的事務管理和運行管理（安全性等），數(shù)據(jù)庫的建立和維護等功能。

* 數(shù)據(jù)庫系統(tǒng)（DBS）：數(shù)據(jù)庫系統(tǒng)由**數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)（及其應用開發(fā)工具）、應用程序、數(shù)據(jù)庫管理員**四部分構成。

- 數(shù)據(jù)庫管理員 : 數(shù)據(jù)庫管理員 (DBA) 負責全面管理和控制數(shù)據(jù)庫系統(tǒng)。

## 通用語法及分類

- DDL: 數(shù)據(jù)定義語言，用來定義數(shù)據(jù)庫對象（數(shù)據(jù)庫、表、字段）

- DML: 數(shù)據(jù)操作語言，用來對數(shù)據(jù)庫表中的數(shù)據(jù)進行增刪改

- DQL: 數(shù)據(jù)查詢語言，用來查詢數(shù)據(jù)庫中表的記錄

- DCL: 數(shù)據(jù)控制語言，用來創(chuàng)建數(shù)據(jù)庫用戶、控制數(shù)據(jù)庫的控制權限

## ER圖

由實體，屬性，聯(lián)系組成

## 三范式

- **1NF(第一范式)：屬性不可再分。**

?

- **2NF(第二范式)：1NF 的基礎之上，消除了非主屬性對于碼的部分函數(shù)依賴。**

?- **碼**：唯一標識實體的列。

?- **候選碼**：若關系中的某一屬性或屬性組的值能唯一的標識一個元組，而其任何、子集都不能再標識，則稱該屬性組為候選碼。

?- **非主屬性**：不是候選碼的屬性。

?- **函數(shù)依賴**：在屬性X 的確定的情況下，必定能確定屬性 Y 的值，那么就可以說 Y 函數(shù)依賴于 X。

?- **部分函數(shù)依賴**：Y依賴X，且Y還依賴X的一部分（真子集）。

?> 表中任意一個主鍵（或聯(lián)合主鍵），可以確定除該主鍵外的所有的非主鍵值。

?>

?> 根據(jù)主鍵或者聯(lián)合主鍵,將一個表最有關聯(lián)的屬性分別放在一起

- **3NF(第三范式)：3NF 在 2NF 的基礎之上，消除了非主屬性對于碼的傳遞函數(shù)依賴 。**

? 如在關系 R(學號 , 姓名, 系名，系主任)中，學號 → 系名，系名 → 系主任，所以存在非主屬性系主任對于學號的傳遞函數(shù)依賴。

## 數(shù)據(jù)庫設計通常分為哪幾步?

1. **需求分析** : 分析用戶的需求，包括數(shù)據(jù)、功能和性能需求。

2. **概念結構設計** : 主要采用 E-R 模型進行設計，包括畫 E-R 圖。

3. **邏輯結構設計** : 通過將 E-R 圖轉換成表，實現(xiàn)從 E-R 模型到關系模型的轉換。

4. **物理結構設計** : 主要是為所設計的數(shù)據(jù)庫選擇合適的存儲結構和存取路徑。

5. **數(shù)據(jù)庫實施** : 包括編程、測試和試運行

6. **數(shù)據(jù)庫的運行和維護** : 系統(tǒng)的運行與數(shù)據(jù)庫的日常維護。

## MYSQL

1. 默認端口號是**3306**。

2. 當前默認的存儲引擎是 InnoDB

3. 采用的是 **插件式架構**，存儲引擎是基于表的，而不是數(shù)據(jù)庫，因此可以為不同的數(shù)據(jù)庫表設置不同的存儲引擎以適應不同場景的需要。

4. MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默認存儲引擎。區(qū)別：

??1. MyISAM 只有表級鎖，InnoDB 支持行級鎖和表級鎖

??2. MyISAM 不提供事務支持。InnoDB 提供事務支持

??3. MyISAM 不支持外鍵，而 InnoDB 支持外鍵

??4. MyISAM 不支持數(shù)據(jù)庫異常崩潰后的安全恢復，而 InnoDB 支持

??5. InnoDB 的性能比 MyISAM 更強大

## 事務的 ACID 特性

1. **原子性**（`Atomicity`）：事務是最小的執(zhí)行單位，不允許分割。事務的原子性確保動作要么全部完成，要么完全不起作用；

2. **一致性**（`Consistency`）：執(zhí)行事務前后，數(shù)據(jù)保持一致，例如轉賬業(yè)務中，無論事務是否成功，轉賬者和收款人的總額應該是不變的；一致性是AID的目的。

3. **隔離性**（`Isolation`）：并發(fā)訪問數(shù)據(jù)庫時，一個用戶的事務不被其他事務所干擾，各并發(fā)事務之間數(shù)據(jù)庫是獨立的；

4. **持久性**（`Durability`）：一個事務被提交之后。它對數(shù)據(jù)庫中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫發(fā)生故障也不應該對其有任何影響。

## 并發(fā)事務帶來了哪些問題?

1. 臟讀：讀到了另一個事務修改但沒有提交的數(shù)據(jù)，之后另一個事務又回滾，則讀到的是臟數(shù)據(jù)。

2. 不可重復讀：一個事務內多次讀同一數(shù)據(jù)值不一樣（被另一個事物修改并提交）。

3. 幻讀：一個事物內多次讀取數(shù)據(jù)時發(fā)現(xiàn)查到的記錄增加了。（是不可重復讀的一種情況）

4. 丟失修改：兩個事務都修改同一數(shù)據(jù)，導致第一個事物修改的丟失。

## 事物隔離級別

并發(fā)事務隔離級別：

| 隔離級別?| 臟讀?| 不可重復讀?| 幻讀?|

| ------------ | ------------ | ------------ | ------------ |

| Read uncommitted?| √?| √?| √?|

| Read committed?| ×?| √?| √?|

| Repeatable Read(默認)?| ×?| ×?| √?|

| Serializable?| ×?| ×?| ×?|

## 當前讀和快照讀

**當前讀：**讀取的是記錄的最新版本，會對讀取的記錄進行加鎖（保證其他并發(fā)事務不能修改當前記錄）。對于select ... lock in share mode(共享鎖)，select ... for update、 update、insert、delete(排他鎖)都是一種當前讀。

**快照讀：**簡單的select (不加鎖) 就是快照讀，快照讀，讀取的是記錄數(shù)據(jù)的可見版本，有可能是歷史數(shù)據(jù)，不加鎖，是非阻塞讀。

- Read Committed: 每次select，都生成一個快照讀

- Repeatable Read:開啟事務后第一個select語句才是快照讀的地方

- Serializable:快照讀會退化為當前讀

## 鎖

* **分類**

按照粒度分：

1. 全局鎖：鎖數(shù)據(jù)庫中所有表。使用場景：全庫備份。

2. 表級鎖：鎖整張表

3. 行級鎖：鎖對應行

### 共享鎖和排他鎖

不論是表級鎖還是行級鎖，都存在共享鎖和排他鎖

> 排他鎖與任何的鎖都不兼容，S僅和S兼容。

- 共享鎖（S 鎖）：又稱讀鎖，事務在讀取記錄的時候獲取共享鎖。

- 排他鎖（X 鎖）：又稱寫鎖/獨占鎖，事務在修改記錄的時候獲取排他鎖。

**意向共享鎖和意向排他鎖**

意向鎖是由數(shù)據(jù)引擎自己維護的，無法手動操作，在為數(shù)據(jù)行加共享/排他鎖之前，InooDB 會先獲取該數(shù)據(jù)行所在在數(shù)據(jù)表的對應意向鎖。意向鎖是表級鎖，共有兩種：意向共享鎖（Intention Shared Lock，IS 鎖）和意向排他鎖（Intention Exclusive Lock，IX 鎖。

> 意向鎖之間互不排斥，但除了 IS 與 S 兼容外，意向鎖會與 S/ X 互斥。

* IX，IS是表級鎖，不會和行級的X，S鎖發(fā)生沖突。只會和表級的X，S發(fā)生沖突。

* 意向鎖在保證并發(fā)性的前提下，實現(xiàn)了行鎖和表鎖共存且滿足事務隔離性的要求。

### 表級鎖和行級鎖

- 表級鎖：對整張表加鎖，針對非索引字段加鎖，簡單，開銷少，加鎖快，不會出現(xiàn)死鎖，易沖突。

行級鎖：粒度最小的鎖，針對**索引**字段。會發(fā)生死鎖。當 UPDATE、DELETE 時，如果 WHERE條件中字段沒有命中唯一索引或者索引失效的話，就會導致掃描全表對表中的所有行記錄進行加鎖。

?**行級鎖分類**：

- 記錄鎖：單個行記錄上的鎖

- 間隙鎖：鎖定一個范圍，不包括記錄本身

- 臨鍵鎖：鎖定一個范圍，包含記錄本身

##MySQL優(yōu)化

1. 避免關聯(lián)操作

??- 經(jīng)常一起使用的列放到一個表中

??- 不要過分追求滿足第三范式

??- 在多表 join 的關聯(lián)列建立索引

2. 避免使用 TEXT 類型

??- MySQL 內存臨時表不支持 TEXT這樣的大數(shù)據(jù)類型，如果查詢中包含這樣的數(shù)據(jù)，在排序等操作時，就不能使用內存臨時表，必須使用磁盤臨時表進行。而且對于這種數(shù)據(jù)，MySQL 還是要進行二次查詢，會使 sql 性能變得很差。

3. 盡可能把所有列定義為 NOT NULL，要用""

??- 索引 NULL 列需要額外的空間來保存，所以要占用更多的空間；

??- 進行比較和計算時要對 NULL 值做特別的處理。

4. 使用 TIMESTAMP(4 字節(jié)) 或 DATETIME 類型 (8 字節(jié)) 存儲時間

??- 方便使用日期函數(shù)進行計算和比較

??- 節(jié)省空間

??- Timestamp 和時區(qū)有關，在不同時區(qū)，查詢到同一個條記錄此字段的值會不一樣。

??- 表示的時間范圍：

??- DateTime：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

??- Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

??- 時間戳也是一個選擇，4字節(jié)，缺點是可讀性不高。

???

5. 金額類數(shù)據(jù)必須使用 decimal 類型

??- 在計算時不會丟失精度

??- 占用空間由定義的寬度決定，可存儲比 bigint 更大的整型數(shù)據(jù)

6. 禁止使用 SELECT * 必須使用 SELECT <字段列表> 查詢

7. 減少同數(shù)據(jù)庫的交互次數(shù)

8. 避免隱式數(shù)據(jù)類型轉換

??- 當操作符左右兩邊的數(shù)據(jù)類型不一致時，會發(fā)生隱式轉換。

??- 隱式轉換會導致索引失效

9. 超 100 萬行的批量寫操作,要分批多次進行操作

??- 會造成主庫與從庫長時間的延遲情況

## 索引

### 索引分類：

**InnoDB**

1. 主鍵索引：設定為主鍵后數(shù)據(jù)庫會自動建立索引，innodb為聚效索引：MySQL每張表中都必須有一個主鍵索引，在InnoDB的實現(xiàn)中，把主鍵作為關鍵字組織到B+樹的各個節(jié)點上，而葉子節(jié)點上存儲的是主鍵列的值和對應的整行數(shù)據(jù)。

2. 單值索引：即一個索引只包含單個列，一個表可以有多個單列索引

3. 唯一索引：索引列的值必須唯一，但允許有空值（但只能存在一個null）。

4. 復合索引：即一個素引包含多個列。遵循最左前綴法則：索引為 name age sex，會匹配（name），（name age）, （name age sex）。

**MyISAM**

全文索引：目前只有 `CHAR`、`VARCHAR` ，`TEXT` 列上可以創(chuàng)建全文索引。一般不會使用。

## 二級索引

在使用中為了提高查詢效率，開發(fā)者會根據(jù)查詢條件自己創(chuàng)建一些索引，而這些索引就叫作二級索引。二級索引又稱為輔助索引.

**InnoDB**中的二級索引的葉子結點中存的是索引列的值和主鍵值，所以在使用二級索引查詢的時候，首先通過二級索引查找到主鍵值，然后再根據(jù)主鍵值到主鍵索引的葉子結點中查到對應的整行數(shù)據(jù)。

**MyISAM**的二級索引的葉子節(jié)點中保存的是指向物理數(shù)據(jù)的指針，因此它的主建索引和二級索引的結構并沒有任何區(qū)別，只是說主鍵索引的索引值是唯一且非空的。

唯一索引，普通索引，前綴索引，全文索引屬于二級索引。

### 二級索引不一定要回表

如果一個索引包含所有需要查詢的字段的值，我們就稱之為 **覆蓋索引**，要查詢的字段正好是索引的字段，那么直接根據(jù)該索引，就可以查到數(shù)據(jù)了，而無需回表查詢。

## 聚簇索引與非聚簇索引

> 聚簇索引和非聚簇索引本質的區(qū)別就是B+樹的葉子節(jié)點上存儲的是行數(shù)據(jù)還是行數(shù)據(jù)的地址(行號)。

### 聚簇索引：

索引結構和數(shù)據(jù)一起存放的索引。InnoDB中每張表有且僅有一個聚簇索引(就是主鍵索引)

- **優(yōu)點：**

1. 速度快

- **缺點：**

1. 依賴于有序的數(shù)據(jù)

2. 更新代價大

### 非聚簇索引：

索引結構和數(shù)據(jù)分開存放的索引，InnoDB中的二級索引是非聚簇索引。MySQL 的 MyISAM 引擎，不管主鍵還是非主鍵，使用的都是非聚簇索引。

- **優(yōu)點**

1. 更新代價比聚簇索引要小

- **缺點**

1. 依賴有序的數(shù)據(jù)

2. 可能會回表

## 索引優(yōu)化

### 1. 選擇合適的字段創(chuàng)建索引

- 不為 NULL 的字段：對于數(shù)據(jù)為 NULL 的字段，數(shù)據(jù)庫較難優(yōu)化。

- 被頻繁查詢的字段。

- 被作為條件查詢的字段。

- 頻繁需要排序的字段。

- 被經(jīng)常頻繁用于連接的字段。

### 2. 頻繁修改的字段不宜建立索引

### 3. 限制每張表上的索引數(shù)量

??建議單張表索引不超過 5 個，MySQL 優(yōu)化器在選擇如何優(yōu)化查詢時，會根據(jù)統(tǒng)一信息，對每一個可以用到的索引來進行評估，以生成出一個最好的執(zhí)行計劃，如果同時有很多個索引都可以用于查詢，就會增加 MySQL 優(yōu)化器生成執(zhí)行計劃的時間，同樣會降低查詢性能。

### 4. 盡可能的考慮建立聯(lián)合索引而不是單列索引

可以節(jié)省磁盤空間，提高修改效率

### 5. 注意避免冗余索引

### 6. 避免索引失效

- 創(chuàng)建了組合索引，但查詢條件未遵守最左匹配原則;

- 在索引列上進行計算、函數(shù)、類型轉換等操作;

- 發(fā)生隱式轉換;

### 7.使用 EXPLAIN 命令來分析 SQL 的 執(zhí)行計劃?

執(zhí)行計劃是指一條 SQL 語句在經(jīng)過 MySQL 查詢優(yōu)化器的優(yōu)化會后，具體的執(zhí)行方式。

##?