# MySQL: INDEX & Partition
###### tags: `RD1`
:::spoiler 目錄
[TOC]
:::
## INDEX
### 索引類型
* **主索引鍵 ( primary key )**
一個表中通常會有一個，而且只能有一個。
設定為主索引鍵的欄位值**不可重複**，而且**不可儲存NULL值**。
* **唯一索引 ( unique index )**
也稱為「不可重複索引」，在一個表中，設定為唯一索引的欄位值不可以重複，
但是**可以儲存NULL值**。
* **非唯一索引 ( non-unique index )**
用來增加查詢與維護資料效率的索引。
設定為非唯一索引的**欄位值可以重**複，也**可以儲存NULL值**。
* **全文檢索 ( full-text search)**
只能用在「 CHAR 」、「 VARCHAR 」與「 TEXT 」型態的欄位字元型態
且**允許NULL**的欄位所組成。
### 操作方法
```sql
一次建立一個
CREATE INDEX email_index ON table_name(email);
一次可建立多個
ALTER TABLE table_name ADD INDEX(email);
```
```sql
刪除索引
DROP INDEX email ON table_name;
```
```sql
查看索引
SHOW INDEXES FROM table_name IN database_name;;
```
### 複合索引
搭配好幾個欄位做搜尋排序時，使用複合索引的效率比單一索引效率要高。
```sql
ALTER TABLE member ADD INDEX name_of_index (name, age, sex);
```
1. 在建立複合索引時要考慮索引的先後順序，愈有識別性的欄位應該放在前面
ex. `index_1(name, age, sex)` 比 `index_2(sex, name, age)` 要有效率
2. 最左前綴 ( Leftmost Prefixing ) 概念
```
name, age, sex
name, age
name
```
創建複合索引 (A,B,C) 相當於創建一個單一索引 A 和兩個複合索引 A+B 與 A+B+C
### 建立索引之原則
最常見在「WHERE」子句中判斷條件的欄位，還有「 ORDER BY 」與「 GROUP BY 」中指定的欄位，也都可以使用建立索引來增加效率。
不過建立這樣的索引的前提，表格所儲存的資料量要夠大，如果量不大的話，建立索引反而會浪費儲存的空間，效率也增加不多，而且還會讓執行新增或修改時的效率變差。
1. **選擇唯一性索引:** 唯一性索引的值是唯一的，可以更快速的透過該索引來找尋資料。
2. **經常需要排序、分組和操作的欄位建立索引:** 排序會浪費很多時間。
如果建立索引，可以有效地避免排序操作。
3. **索引不要建立過多:** 每個索引都需要佔用空間，索引越多，需要的空間就越大。
4. 索引的欄位儘量不要有「NULL」值
5. **最左前綴匹配原則:** 最具識別性的欄位放在鍵的左邊
6. 雖然某個欄位很常使用在 WHERE、 ORDER BY 或 GROUP BY 子句中，也不一定要建立索引。
例如性別欄位的值只有兩種(F , M)，建立索引所增加的效率也不多。
7. 如果有 or，where 後面就必須全部條件有 index，否則 index 將不會起作用，所以要使用 or 的就要為相對應的欄位加上 index
[參考資料](https://medium.com/@michael80402/mysql索引-e002f707a5f4)
## Partition (分區)
在建立資料表同時建立分區，新增資料時按照分區條件劃入不同空間中。
是一種水平分區的方式，將同一個資料表的資料分成不同的檔案儲存，甚至可以放在不同磁碟，
查詢時針對特定分區執行，縮小範圍進而增加存取速度 （分區後所有的資料還是在同一張表）
### 四種方式 (range、list、hash、key)
採用哪種分區方式要取決於需求以及資料本身特性，如果選到不適合的分區方式，效能甚至會不進反退。
不同的分區方式，也有不同的維護方法，在決定方式前一定都要考量到。
* Range 連續值橫向分區：
資料以id依照範圍設定分為三個區
```sql
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (11),
PARTITION p1 VALUES LESS THAN (21),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
```
> 註：若用來分區的欄位不是主鍵，會出現錯誤訊息
* List 列舉值橫向分區：
指定集合內的元素(int)分別屬於哪個分區。
```sql
PARTITION BY LIST (id) (
PARTITION pA VALUES IN (1,3,5),
PARTITION pB VALUES IN (12,15)
);
```
* Hash 讓資料在設定好的分區均勻分布：
指定分區數量 N，以單一 int 欄位或以回傳 int 的表達式結果，決定資料屬於哪個分區。
```sql
CREATE TABLE aa (
id INT NOT NULL,
mydate DATE NOT NULL DEFAULT '1970-01-01'
)
PARTITION BY HASH(id) /or/ PARTITION BY HASH(YEAR(mydate))
PARTITIONS 3;
```
* Key（與hash分區類似）：
指定分區數量 N，支援多個欄位為 partition key，用內建 hash function 決定資料屬於哪個分區。
[MySQL 資料表分區(partition)](https://xyz.cinc.biz/2015/07/mysql-partition.html)
## 實作紀錄
專案：https://github.com/esther-lin069/go-mysql-api
```sql
room_id BETWEEN 1 AND 100 AND type = 'normal' AND sender = 'Anna' AND read_tab = 0
```
* A測資：查詢條件如上，當前DB資料量為4583164筆（662MB）
* B測資：查詢條件如上，當前DB資料量為9980689筆（1.3GB）
### 沒有索引
> **A.** DB執行時間：1.51620212s / 資料列數：2333
> **B.** DB執行時間：10.3352946s / 資料列數：5041
### 單個索引
1. 聊天室id
> **A.** DB執行時間：1.535088086s / 資料列數：2333
> **B.** DB執行時間：10.9309112s / 資料列數：5041
2. 發言類型 ==更耗時==
> **A.** DB執行時間：20.507707785s / 資料列數：2333
> **B.** DB執行時間：1m34.1675092s / 資料列數：5041
3. 發言人（共有20個）
> **A.** DB執行時間：1.890655134s / 資料列數：2333
> **B.** DB執行時間：23.2711352s / 資料列數：5041
4. 已讀標記 ==更耗時==
> **A.** DB執行時間：22.053848687s / 資料列數：2333
> **B.** DB執行時間：2m59.8551417s / 資料列數：5041
5. 分別建立發言人與類型兩個索引 ==變快?==
```sql
ALTER TABLE messages ADD INDEX(sender)
ALTER TABLE messages ADD INDEX(type)
```
> **A.** DB執行時間：605.020578ms / 資料列數：2333
> **B.** DB執行時間：9.133879s / 資料列數：5041
### 複合索引
A容量狀態為：
1. 為發言人、類型、已讀標記增加複合索引
```sql
ALTER TABLE `chat_data`.`messages` ADD INDEX `sender_type_read_index` (`sender`, `type`, `read_tab`)
```
> **A.** DB執行時間：178.530348ms / 資料列數：2333
> **B.** DB執行時間：3.100755s / 資料列數：5041
上述複合索引加上聊天室id的單索引
```sql
ALTER TABLE messages ADD INDEX(room_id)
```
> **A.** DB執行時間：176.098068ms / 資料列數：2333 (沒有明顯變化)
> **B.**
2. 發言人、類型複合索引（沒有已讀標記的版本）
```sql
ALTER TABLE `chat_data`.`messages` ADD INDEX `sender_type_index` (`sender`, `type`)
```
> **A.** DB執行時間：289.66093ms / 資料列數：2333
> **B.** DB執行時間：5.4436063s / 資料列數：5041
3. 發言人、聊天室id、類型、已讀標記4複合索引 ==超快==
```sql
ALTER TABLE `chat_data`.`messages` ADD INDEX `sender_room_type_read_index` (`sender`, `room_id`, `type`, `read_tab`)
```
> **A.** DB執行時間：28.988757ms / 資料列數：2333
> **B.** DB執行時間：278.2587ms / 資料列數：5041
4. 聊天室id、發言人、類型3複合索引 ==沒有幫助==
```sql
ALTER TABLE `chat_data`.`messages` ADD INDEX `room_sender_type` ( `room_id`,`sender`, `type`)
```
> **A.** DB執行時間：1.523979225s / 資料列數：2333
> **B.** DB執行時間：10.6058784s / 資料列數：5041
(需要掃過所有資料，沒有效率的索引)
5. 發言人、類型、聊天室id 3複合索引 ==快==
```sql
ALTER TABLE `chat_data`.`messages` ADD INDEX `sender_type_room_index` (`sender`, `type`,`room_id`)
```
> **A.** DB執行時間：33.912341ms / 資料列數：2333
> **B.** DB執行時間：375.1045ms / 資料列數：5041
### 分區：Range
1. 依照id
2. 依照room_id (250一區，分四區)
資料總筆數 5059044
* 同上測試條件
> DB執行時間：767.0391ms / 資料列數：2558 (比原始狀態快一倍)
* 搜索跨區資料：聊天室id範圍調整為200-300
> DB執行時間：1.6681611s / 資料列數：2497 (跟原始狀態差不多)
* 再加上發言人、類型、已讀標記三複合索引
> DB執行時間：28.4168ms / 資料列數：2497
### 分區：List
**list的分組對象必須是int類型且為主鍵**
1. 依照read_tab
資料總筆數 4958729
> DB執行時間：1.7339688s / 資料列數：2396 (沒啥幫助)
Sign in with Wallet
Connect another wallet