Try   HackMD

MySQL: INDEX & Partition

tags: RD1
目錄

INDEX

索引類型

  • 主索引鍵 ( primary key )
    一個表中通常會有一個,而且只能有一個。
    設定為主索引鍵的欄位值不可重複,而且不可儲存NULL值

  • 唯一索引 ( unique index )
    也稱為「不可重複索引」,在一個表中,設定為唯一索引的欄位值不可以重複,
    但是可以儲存NULL值

  • 非唯一索引 ( non-unique index )
    用來增加查詢與維護資料效率的索引。
    設定為非唯一索引的欄位值可以重複,也可以儲存NULL值

  • 全文檢索 ( full-text search)
    只能用在「 CHAR 」、「 VARCHAR 」與「 TEXT 」型態的欄位字元型態
    允許NULL的欄位所組成。

操作方法

一次建立一個
CREATE INDEX email_index ON table_name(email);

一次可建立多個
ALTER TABLE table_name ADD INDEX(email);
刪除索引
DROP INDEX email ON table_name;
查看索引
SHOW INDEXES FROM table_name IN database_name;;

複合索引

搭配好幾個欄位做搜尋排序時,使用複合索引的效率比單一索引效率要高。

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

參考資料

Partition (分區)

在建立資料表同時建立分區,新增資料時按照分區條件劃入不同空間中。

是一種水平分區的方式,將同一個資料表的資料分成不同的檔案儲存,甚至可以放在不同磁碟,
查詢時針對特定分區執行,縮小範圍進而增加存取速度 (分區後所有的資料還是在同一張表)

四種方式 (range、list、hash、key)

採用哪種分區方式要取決於需求以及資料本身特性,如果選到不適合的分區方式,效能甚至會不進反退。
不同的分區方式,也有不同的維護方法,在決定方式前一定都要考量到。

  • Range 連續值橫向分區:

資料以id依照範圍設定分為三個區

PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (11),
    PARTITION p1 VALUES LESS THAN (21),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

註:若用來分區的欄位不是主鍵,會出現錯誤訊息

  • List 列舉值橫向分區:
    指定集合內的元素(int)分別屬於哪個分區。
PARTITION BY LIST (id) (
    PARTITION pA VALUES IN (1,3,5),
    PARTITION pB VALUES IN (12,15)
);
  • Hash 讓資料在設定好的分區均勻分布:
    指定分區數量 N,以單一 int 欄位或以回傳 int 的表達式結果,決定資料屬於哪個分區。
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://github.com/esther-lin069/go-mysql-api

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

  1. 發言類型 更耗時

A. DB執行時間:20.507707785s / 資料列數:2333
B. DB執行時間:1m34.1675092s / 資料列數:5041

  1. 發言人(共有20個)

A. DB執行時間:1.890655134s / 資料列數:2333
B. DB執行時間:23.2711352s / 資料列數:5041

  1. 已讀標記 更耗時

A. DB執行時間:22.053848687s / 資料列數:2333
B. DB執行時間:2m59.8551417s / 資料列數:5041

  1. 分別建立發言人與類型兩個索引 變快?
ALTER TABLE messages ADD INDEX(sender)
ALTER TABLE messages ADD INDEX(type)

A. DB執行時間:605.020578ms / 資料列數:2333
B. DB執行時間:9.133879s / 資料列數:5041

複合索引

A容量狀態為:

  1. 為發言人、類型、已讀標記增加複合索引
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的單索引

ALTER TABLE messages ADD INDEX(room_id)

A. DB執行時間:176.098068ms / 資料列數:2333 (沒有明顯變化)
B.

  1. 發言人、類型複合索引(沒有已讀標記的版本)
ALTER TABLE `chat_data`.`messages` ADD INDEX `sender_type_index` (`sender`, `type`)

A. DB執行時間:289.66093ms / 資料列數:2333
B. DB執行時間:5.4436063s / 資料列數:5041

  1. 發言人、聊天室id、類型、已讀標記4複合索引 超快
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

  1. 聊天室id、發言人、類型3複合索引 沒有幫助
ALTER TABLE `chat_data`.`messages` ADD INDEX `room_sender_type` ( `room_id`,`sender`, `type`)

A. DB執行時間:1.523979225s / 資料列數:2333
B. DB執行時間:10.6058784s / 資料列數:5041

(需要掃過所有資料,沒有效率的索引)

  1. 發言人、類型、聊天室id 3複合索引
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 (沒啥幫助)