MySQL分割區建索引以及分割區介紹總結

2022-04-14 10:03:57

MySQL 分割區建索引介紹

mysql分割區後每個分割區成了獨立的檔案，雖然從邏輯上還是一張表其實已經分成了多張獨立的表，從“information_schema.INNODB_SYS_TABLES”系統表可以看到每個分割區都存在獨立的TABLE_ID,由於Innodb資料和索引都是儲存在".ibd"檔案當中（從INNODB_SYS_INDEXES系統表中也可以得到每個索引都是對應各自的分割區(primary key和unique也不例外）），所以分割區表的索引也是隨著各個分割區單獨儲存。

在INNODB_SYS_INDEXES系統表中type代表索引的型別;0:一般的索引,1:(GEN_CLUST_INDEX)不存在主鍵索引的表,會自動生成一個6個位元組的標示值，2:unique索引,3:primary索引;所以當我們在分割區表中建立索引時其實也是在每個分割區中建立索引，每個分割區維護各自的索引（其實也就是local index）；對於一般的索引(非主鍵或者唯一)沒什麼問題由於索引樹中只保留了索引key和主鍵key(如果存在主鍵則是主鍵的key否則就是系統自動生成的6個的key)不受分割區的影響；但是如果表中存在主鍵就不一樣了，雖然在每個分割區檔案中都存在主鍵索引但是主鍵索引需要保證全域性的唯一性就是所有分割區中的主鍵的值都必須唯一（唯一鍵也是一樣的道理），所以在建立分割區時如果表中存在主鍵或者唯一鍵那麼分割區列必須包含主鍵或者唯一鍵的部分或者全部列（全部列還好理解，部分列也可以個人猜測是為了各個分割區和主鍵建立關係），由於需要保證全域性性又要保證插入資料更新資料到具體的分割區所以就需要將分割區和主鍵建立關係,由於通過一般的索引進行查詢其它非索引欄位需要通過主鍵如果主鍵不能保證全域性唯一性的話那麼就需要去每個分割區查詢了，這樣效能可想而知。

To enforce the uniqueness we only allow mapping of each unique/primary key value to one partition.If we removed this limitation it would mean that for every insert/update we need to check in every partition to verify that it is unique. Also PK-only lookups would need to look into every partition.

索引方式：

效能依次降低

1.主鍵分割區

主鍵分割區即欄位是主鍵同時也是分割區欄位，效能最好

2. 部分主鍵+分割區索引

使用組合主鍵裡面的部分欄位作為分割區欄位，同時將分割區欄位建索引

3.分割區索引

沒有主鍵，只有分割區欄位且分割區欄位建索引

4.分割區+分割區欄位沒有索引

只建了分割區，但是分割區欄位沒有建索引

MySQL 分割區介紹介紹

分割區是指根據一定的規則將一個大表分解成多個更小的部分，這裡的規則一般就是利用分割區規則將表進行水平切分；邏輯上沒有發生變化但實際上表已經被拆分成了多個物理物件，每個分成被劃分成了一個獨立的物件。相對於沒有分割區的當個表而言分割區的表有很多的優勢包括：並行統計查詢、快速歸檔刪除分割區資料、分散儲存、查詢效能更佳。

mysql5.7以後查詢語句支援指定分割區例如：“ SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 ”指定分割區同樣適用DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.

資料庫版本：mysql5.7.12

是否支援分割區

SHOW PLUGINS ;

查詢partition的的狀態是active就代表支援分割區，如果是原始碼安裝的話在編譯的過程中要新增“-DWITH_PARTITION_STORAGE_ENGINE=1 ”。

注意： MERGE, CSV, or FEDERATED儲存引擎不支援分割區，同一個表所有的分割區必須使用相同的儲存引擎，不能分割區1使用MYISAM分割區2又使用INNODB；不同的分割區表可以是不同的儲存引擎。

分割區介紹

目前mysql可用的分割區型別主要有以下幾種：

RANGE分割區：基於一個給定的連續區間範圍，RANGE主要是基於整數的分割區，對於非整形的欄位需要利用表示式將其轉換成整形。

LIST分割區：是基於列出的列舉值列表進行分割區。

COLUMNS分割區：可以無需通過表示式進行轉換直接對非整形欄位進行分割區，同時COLUMNS分割區還支援多個欄位組合分割區，只有RANGELIST存在COLUMNS分割區，COLUMNS是RANGE和LIST分割區的升級。

HASH分割區：基於給定的分割區個數，將資料分配到不同的分割區，HASH分割區只能針對整數進行HASH，對於非整形的欄位只能通過表示式將其轉換成整數。

KEY分割區：支援除text和BLOB之外的所有資料型別的分割區,key分割區可以直接基於欄位做分割區無需轉換成整數。

說明

1.注意分割區名的大小寫敏感問題，和關鍵字問題。

2.無論哪種分割區型別，要麼分割區表中沒有主鍵或唯一鍵，要麼主鍵或唯一鍵包含在分割區列裡面，對於存在主鍵或者唯一鍵的表不能使用主鍵或者唯一鍵之外的欄位作為分割區欄位。

3.5.7以前的版本顯示分割區的執行計劃使用：explain PARTITIONS；5.7以後直接執行：explain

4.沒有強制要求分割區列非空，建議分割區的列為NOT NULL的列；在RANGE 分割區中如果往分割區列中插入NULL值會被當作最小的值來處理，在LIST分割區中NULL值必須在列舉列表中否則插入失敗，在HASH/KEY分割區中NULL值會被當作0來處理。

5.基於時間型別的欄位的轉換函數mysql提供了"YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()"

6.拆分合並分割區後會導致修改的分割區的統計資訊失效,沒有修改的分割區的統計資訊還在,不影響新插入的值加入到統計資訊；這時需要對錶執行Analyze操作.

7.針對非整形欄位進行RANGLIST分割區建議使用COLUMNS分割區。

刪除增加分割區

在每個分割區內容介紹中詳細介紹了每種分割區的用法，但是都是介紹在建立表的時候建立分割區和修改刪除分割區單個，也可以在一張已經存在的表中加入分割區，可以一次性刪除整個表的分割區。

1.移除表的分割區

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意：使用remove移除分割區是僅僅移除分割區的定義，並不會刪除資料和drop PARTITION不一樣，後者會連同資料一起刪除

2.對已經存在記錄的表建立分割區，以增加range分割區為例，和建立表建分割區的語法一樣。

ALTER TABLE `tb_partition`.`tb_varchar` 
PARTITION BY RANGE(id) PARTITIONS 3( PARTITION part0 VALUES LESS THAN (5000),  PARTITION part1 VALUES LESS THAN (10000),  PARTITION part2 VALUES LESS THAN (MAXVALUE)) ;

注意：對已有的表建立分割區之後，資料會按照分割區的定義分佈到各個分割區檔案當中

分割區系列文章：

RANGE分割區：https://www.jb51.net/article/244269.htm

COLUMN分割區：https://www.jb51.net/article/96515.htm

LIST分割區：https://www.jb51.net/article/244256.htm

HASH分割區：https://www.jb51.net/article/244277.htm

KEY分割區：https://www.jb51.net/article/244282.htm

子分割區：https://www.jb51.net/article/244294.htm

指定各分割區路徑：https://www.jb51.net/article/244296.htm

分割區索引以及分割區介紹總結：https://www.jb51.net/article/244300.htm