MySQL 分區(qū)建索引介紹

mysql分區(qū)后每個分區(qū)成了獨立的文件，雖然從邏輯上還是一張表其實已經(jīng)分成了多張獨立的表，從“information_schema.INNODB_SYS_TABLES”系統(tǒng)表可以看到每個分區(qū)都存在獨立的TABLE_ID,由于Innodb數(shù)據(jù)和索引都是保存在".ibd"文件當中（從INNODB_SYS_INDEXES系統(tǒng)表中也可以得到每個索引都是對應各自的分區(qū)(primary key和unique也不例外）），所以分區(qū)表的索引也是隨著各個分區(qū)單獨存儲。

在INNODB_SYS_INDEXES系統(tǒng)表中type代表索引的類型;0:一般的索引,1:(GEN_CLUST_INDEX)不存在主鍵索引的表,會自動生成一個6個字節(jié)的標示值，2:unique索引,3:primary索引;所以當我們在分區(qū)表中創(chuàng)建索引時其實也是在每個分區(qū)中創(chuàng)建索引，每個分區(qū)維護各自的索引（其實也就是local index）；對于一般的索引(非主鍵或者唯一)沒什么問題由于索引樹中只保留了索引key和主鍵key(如果存在主鍵則是主鍵的key否則就是系統(tǒng)自動生成的6個的key)不受分區(qū)的影響；但是如果表中存在主鍵就不一樣了，雖然在每個分區(qū)文件中都存在主鍵索引但是主鍵索引需要保證全局的唯一性就是所有分區(qū)中的主鍵的值都必須唯一（唯一鍵也是一樣的道理），所以在創(chuàng)建分區(qū)時如果表中存在主鍵或者唯一鍵那么分區(qū)列必須包含主鍵或者唯一鍵的部分或者全部列（全部列還好理解，部分列也可以個人猜測是為了各個分區(qū)和主鍵建立關(guān)系），由于需要保證全局性又要保證插入數(shù)據(jù)更新數(shù)據(jù)到具體的分區(qū)所以就需要將分區(qū)和主鍵建立關(guān)系,由于通過一般的索引進行查找其它非索引字段需要通過主鍵如果主鍵不能保證全局唯一性的話那么就需要去每個分區(qū)查找了，這樣性能可想而知。

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.主鍵分區(qū)

主鍵分區(qū)即字段是主鍵同時也是分區(qū)字段，性能最好

2. 部分主鍵+分區(qū)索引

使用組合主鍵里面的部分字段作為分區(qū)字段，同時將分區(qū)字段建索引

3.分區(qū)索引

沒有主鍵，只有分區(qū)字段且分區(qū)字段建索引

4.分區(qū)+分區(qū)字段沒有索引

只建了分區(qū)，但是分區(qū)字段沒有建索引

MySQL 分區(qū)介紹介紹

分區(qū)是指根據(jù)一定的規(guī)則將一個大表分解成多個更小的部分，這里的規(guī)則一般就是利用分區(qū)規(guī)則將表進行水平切分；邏輯上沒有發(fā)生變化但實際上表已經(jīng)被拆分成了多個物理對象，每個分成被劃分成了一個獨立的對象。相對于沒有分區(qū)的當個表而言分區(qū)的表有很多的優(yōu)勢包括： 并發(fā)統(tǒng)計查詢、快速歸檔刪除分區(qū)數(shù)據(jù)、分散存儲、查詢性能更佳。

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

數(shù)據(jù)庫版本：mysql5.7.12

是否支持分區(qū)

SHOW PLUGINS ;

查詢partition的的狀態(tài)是active就代表支持分區(qū)，如果是源碼安裝的話在編譯的過程中要添加“-DWITH_PARTITION_STORAGE_ENGINE=1 \”。

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

分區(qū)介紹

目前mysql可用的分區(qū)類型主要有以下幾種：

RANGE分區(qū)：基于一個給定的連續(xù)區(qū)間范圍，RANGE主要是基于整數(shù)的分區(qū)，對于非整形的字段需要利用表達式將其轉(zhuǎn)換成整形。

LIST分區(qū)：是基于列出的枚舉值列表進行分區(qū)。

COLUMNS分區(qū)：可以無需通過表達式進行轉(zhuǎn)換直接對非整形字段進行分區(qū)，同時COLUMNS分區(qū)還支持多個字段組合分區(qū)，只有RANGELIST存在COLUMNS分區(qū)，COLUMNS是RANGE和LIST分區(qū)的升級。

HASH分區(qū)：基于給定的分區(qū)個數(shù)，將數(shù)據(jù)分配到不同的分區(qū)，HASH分區(qū)只能針對整數(shù)進行HASH，對于非整形的字段只能通過表達式將其轉(zhuǎn)換成整數(shù)。

KEY分區(qū)：支持除text和BLOB之外的所有數(shù)據(jù)類型的分區(qū),key分區(qū)可以直接基于字段做分區(qū)無需轉(zhuǎn)換成整數(shù)。

說明

1.注意分區(qū)名的大小寫敏感問題，和關(guān)鍵字問題。

2.無論哪種分區(qū)類型，要么分區(qū)表中沒有主鍵或唯一鍵，要么主鍵或唯一鍵包含在分區(qū)列里面，對于存在主鍵或者唯一鍵的表不能使用主鍵或者唯一鍵之外的字段作為分區(qū)字段。

3.5.7以前的版本顯示分區(qū)的執(zhí)行計劃使用：explain PARTITIONS；5.7以后直接執(zhí)行：explain

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

5.基于時間類型的字段的轉(zhuǎn)換函數(shù)mysql提供了"YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()"

6.拆分合并分區(qū)后會導致修改的分區(qū)的統(tǒng)計信息失效,沒有修改的分區(qū)的統(tǒng)計信息還在,不影響新插入的值加入到統(tǒng)計信息；這時需要對表執(zhí)行Analyze操作.

7.針對非整形字段進行RANG\LIST分區(qū)建議使用COLUMNS分區(qū)。

刪除增加分區(qū)

在每個分區(qū)內(nèi)容介紹中詳細介紹了每種分區(qū)的用法，但是都是介紹在創(chuàng)建表的時候創(chuàng)建分區(qū)和修改刪除分區(qū)單個，也可以在一張已經(jīng)存在的表中加入分區(qū)，可以一次性刪除整個表的分區(qū)。

1.移除表的分區(qū)

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意：使用remove移除分區(qū)是僅僅移除分區(qū)的定義，并不會刪除數(shù)據(jù)和drop PARTITION不一樣，后者會連同數(shù)據(jù)一起刪除

2.對已經(jīng)存在記錄的表創(chuàng)建分區(qū)，以增加range分區(qū)為例，和創(chuàng)建表建分區(qū)的語法一樣。

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)) ;

注意：對已有的表創(chuàng)建分區(qū)之后，數(shù)據(jù)會按照分區(qū)的定義分布到各個分區(qū)文件當中

分區(qū)系列文章： 

RANGE分區(qū)：http://www.dbjr.com.cn/article/244269.htm

COLUMN分區(qū)：http://www.dbjr.com.cn/article/96515.htm

LIST分區(qū)：http://www.dbjr.com.cn/article/244256.htm

HASH分區(qū)：http://www.dbjr.com.cn/article/244277.htm

KEY分區(qū)：http://www.dbjr.com.cn/article/244282.htm

子分區(qū)：http://www.dbjr.com.cn/article/244294.htm

指定各分區(qū)路徑：http://www.dbjr.com.cn/article/244296.htm

分區(qū)索引以及分區(qū)介紹總結(jié)：http://www.dbjr.com.cn/article/244300.htm

總結(jié)

到此這篇關(guān)于MySQL分區(qū)建索引以及分區(qū)介紹總結(jié)的文章就介紹到這了,更多相關(guān)MySQL分區(qū)建索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論