數(shù)據(jù)量不大的中小型規(guī)模 size 的 Table 原則是還是采用 Table+Index 設(shè)計為最佳化的思考重點。

在大數(shù)據(jù)考慮數(shù)據(jù)分片的時候，有兩點也是重點：

一是空間，數(shù)據(jù)存放的存儲空間是否足夠，易于擴展。
二是時間，對于數(shù)據(jù)存取是否有限制，能否優(yōu)化。

此外就是：

基本的數(shù)據(jù)與索引區(qū)隔無法滿足 Big Data 所需的數(shù)據(jù)分片
高達數(shù)百 TB 的數(shù)據(jù)表毫無疑問將導致 DML 語法執(zhí)行缺乏效率
海量型數(shù)據(jù)建議采用分區(qū)（Partition）機制分離儲存數(shù)據(jù)
數(shù)據(jù)分區(qū)存放可以有效提升數(shù)據(jù)查詢與異動操作

對于 MyISAM 引擎，可以設(shè)計分離數(shù)據(jù)文件和索引文件來加快數(shù)據(jù)存取。例如：

CREATE TABLE  X  (
   …
)
ENGINE=MyISAM,
DATA DIRECTORY = '/var/p1',
INDEX DIRECTORY = '/var/p2';

當然，InnoDB 采用 System TableSpace 集中存放,無法支持此種方式，需要通過設(shè)定去轉(zhuǎn)換成一個 table 一個 file。

MariaDB 表分區(qū)（Table Partition）

MariaDB 10 提供 Table 分區(qū)儲存功能，大量數(shù)據(jù)切割成不同儲存區(qū)域(Partition)，Partiton 底層的檔案可再切成多檔方式儲存(Sub-Partition)。

使用 Plugin 方式擴充

由 Storage Engine 自行實作，MariaDB 已支持的包括 InnoDB, TokuDB , Memory, Aria, Spider、MyISAM, Archive, BLACKHOLE

MySQL 僅支援 InnoDB

MariaDB 透過內(nèi)建的 Partiton Storage Engine 提供此項分區(qū)服務(wù)。

查看是否有安裝此引擎：

SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME like "%part%";

查看是否啟用分區(qū)插件

系統(tǒng)管理方式與一般 tables 相同,System Partition 提供元數(shù)據(jù)（Metadata）: Informaton_Schema.PARTITIONS。

分區(qū)作業(yè)時，有些東西還需要特別考慮，例如分區(qū)類型、分區(qū)計算、目標表的形態(tài)、分區(qū)前后的應用等等。

MariaDB 的分區(qū)類型使用示例

MariaDB 的分區(qū)類型主要有：

RANGE ( 單一字段)
LIST ( 單一字段)
RANGE COLUMNS and LIST COLUMNS, HASH COLUMNS(多欄)
HASH ( 單一字段)
KEY ( 單一字段 )
LINEAR HASH, LINEAR KEY
SYSTEM_TIME

RANGE 分區(qū)類型

RANGE 分區(qū)類型用于為每個分區(qū)分配由分區(qū)表達式生成的值的范圍。范圍必須是有序的，連續(xù)的且不重疊的。最小值始終包含在第一個范圍內(nèi)。最高值可以包含在最后一個范圍內(nèi)，也可以不包含在最后一個范圍內(nèi)。

這種分區(qū)方法的一種變體 RANGE COLUMNS 允許我們使用多列和更多數(shù)據(jù)類型。

語法：

CREATE TABLE 語句的最后一部分可以是新表分區(qū)的定義。對于 RANGE 分區(qū)

PARTITION BY RANGE (partitioning_expression)
(
    PARTITION partition_name VALUES LESS THAN (value),
    [ PARTITION partition_name VALUES LESS THAN (value), ... ]
)

說明：

partitioning_expression 是一個 SQL 表達式，從每行返回一個值，最簡單的就是一個字段名(column name)，用于確定哪個分區(qū)需要包含一行數(shù)據(jù)( which partition should contain a row)。
partition_name 是分區(qū)的名稱。
value 指示該分區(qū)的上限，表達式必須回傳 deterministic / nonconstant 值(Integer 或 NULL)
不支持 stored functions 以及 user-defined functions
不支持 / 除法運算子( DIV , MOD 可 ) ( / 回傳的是 Float 10/3 = 3.3 )
表達式不可消耗過多資源/時間
使用針對 Partition 最佳化過的分區(qū)函數(shù)(partitioning function)，例如 YEAR() , TO_DAYS(), TO_SECONDS() 。
如果存在問題，可以將 MAXVALUE 指定為最后一個分區(qū)的值。但是請注意，不能拆分現(xiàn)有 RANGE 分區(qū)表的分區(qū)?？梢愿郊有碌姆謪^(qū)，但是如果最后一個分區(qū)的上限為 MAXVALUE，則將無法添加新分區(qū)。

示例：

通過年份對日志表進行分區(qū)

CREATE TABLE test200221.log
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    timestamp DATETIME NOT NULL,
    user INT UNSIGNED,
    ip BINARY(16),
    action VARCHAR(20),
    PRIMARY KEY (id,timestamp)
)
    ENGINE = InnoDB
PARTITION BY RANGE (YEAR(timestamp))
(
    PARTITION p0 VALUES LESS THAN (2013),
    PARTITION p1 VALUES LESS THAN (2014),
    PARTITION p2 VALUES LESS THAN (2015),
    PARTITION p3 VALUES LESS THAN (2016)
);

注意：

1、partitioning_expression 表達式中所引用的字段必須是 Primary key 中的成員
2、Primary key 包含了表達式中所有字段的組合，限縮 Unique keys 的使用

如果 partitioning_expression 表達式引用的字段不是，就會出現(xiàn)以下錯誤：

SQL 錯誤 [1503] [HY000]: (conn=51) A PRIMARY KEY must include all columns in the table's partitioning function

3、一般情況下，不支持的分區(qū)字段類型有 TEXT,LongText，BLOB,CLOB …(前面有提到，返回值需要是 Integer 或 NULL)
4、此外，在使用分區(qū)函數(shù)(partitioning function)時，要注意函數(shù)和字段的正確性，否則可能會出現(xiàn)類似的問題：

SQL 錯誤 [1486] [HY000]: (conn=51) Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

5、注意插入的數(shù)據(jù)是否有對應的分區(qū)。例如示例中分區(qū)有 4 個，對應 2013~2016 年的日志，如果此時插入的有是 2020 年的日志，沒有該分區(qū)，可能出現(xiàn)類似的錯誤：

無對應分區(qū)報錯

但可以添加 IGNORE 關(guān)鍵詞去去除不符合的值。上述就會插入 2015 年的那條：

去除不符合分區(qū)的值

6、部分 Partition 語法需要對 Partition 進行命名，MariaDB 可自行命名( pN , N(0~N ) )，人工編碼最長長度 61 字符，推薦有意義的命名。

LIST 分區(qū)類型

LIST 分區(qū)在概念上類似于 RANGE 分區(qū)。在這兩種情況下，都需要確定一個分區(qū)表達式（一個字段(column)，或者稍微復雜一些的計算），然后使用它來確定哪些分區(qū)將包含每一行。但是，對于 RANGE 類型，分區(qū)是通過為每個分區(qū)分配一個值范圍來完成的。對于 LIST 類型，將會為每個分區(qū)分配一組值。如果分區(qū)表達式可以返回一組有限的值(a limited set of values)，則通常是首選方法。

這種分區(qū)方法的一種變體 LIST COLUMNS 允許使用多列和更多數(shù)據(jù)類型。

語法：

PARTITION BY LIST (partitioning_expression)
(
    PARTITION partition_name VALUES IN (value_list),
    [ PARTITION partition_name VALUES IN (value_list), ... ]
    [ PARTITION partition_name DEFAULT ]
)

說明：

依據(jù)數(shù)據(jù)類型,符合指定項目則存入指定 Partition
partitioning_expression 是一個 SQL 表達式，從每行返回一個值，最簡單的就是一個字段名(column name)，用于確定哪個分區(qū)需要包含一行數(shù)據(jù)( which partition should contain a row)。
partition_name 是分區(qū)的名稱。
value 是一系列值(a list of values)，表達式返回這些值，就能存入該分區(qū)
EXPRESSION 表達式必須是 Integer 回傳值
搭配 DEFAULT 收納所有不符合其它分區(qū)條件的數(shù)據(jù)( 10.2 之后加入)，但也只能有一個 DEFUILT 分區(qū)。可設(shè)定超出范圍的處理: NULL

示例：

依據(jù)文章語言分類，所有可能的篩選值必須判斷，Language 事實上是 Foreign key 對應到 Language Table（所以用 id 表示），Partition table 不支持 Fkey，Language 必須是正整數(shù)或 null。

DROP table if exists test200221.article;
CREATE TABLE test200221.article(
    id integer unsigned not null auto_increment,
    date date not null,
    author varchar(100),
    language tinyint unsigned,
    text text,
    primary key(id,language)
)ENGINE=InnoDB
partition by LIST (language)(
    PARTITION p0 VALUES  IN (1),
    PARTITION p1 VALUES  IN (2,3),
    PARTITION p2 VALUES  IN (4,5,6,7,8,9),
    PARTITION px VALUES  IN (NULL ) -- 或    PARTITION px DEFAULT
)

RANGE COLUMNS 和 LIST COLUMNS 分區(qū)類型

RANGE COLUMNS 和 LIST COLUMNS 分別是 RANGE 和 LIST 的變體。對于這些分區(qū)類型，不是一個分區(qū)表達式(partitioning _expression)。而是接受一個或多個字段(columns)。適用以下規(guī)則：

該列表可以包含一個或多個字段(columns)。

字段(columns)可以是任何 integer，string，DATE 和 DATETIME 類型。

僅允許使用純字段(bare columns)，不能加表達式。
將所有指定的列與指定的值進行比較，以確定哪個分區(qū)應包含特定的行。

字段型別:
Integer ( 不可產(chǎn)生負數(shù) )、Date, DateTime、CHAR, VARCHAR, BINARY , VARBINARY、不可使用任何 functions, 運算子符號 (只能單純使用字段…)

語法：

RANGE COLUMNS 分區(qū)類型：

PARTITION BY RANGE COLUMNS (col1, col2, ...)
(
    PARTITION partition_name VALUES LESS THAN (value1, value2, ...),
    [ PARTITION partition_name VALUES LESS THAN (value1, value2, ...), ... ]
)

LIST COLUMNS 分區(qū)類型：

PARTITION BY LIST COLUMNS (partitioning_expression)
(
    PARTITION partition_name VALUES IN (value1, value2, ...),
    [ PARTITION partition_name VALUES IN (value1, value2, ...), ... ]
    [ PARTITION partititon_name DEFAULT ]
)

兩者的區(qū)別：

RANGE COLUMNS 是返回的值小于指定的值，第一個匹配條件的分區(qū)將包含該值；
LIST COLUMNS 返回的值包含在給定的值里面，同樣允許且僅運行一個 DEFAULT 分區(qū)。

示例，修改上述 article 表，新加 year 字段：

DROP table if exists test200221.article2;
CREATE TABLE test200221.article2 (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  year CHAR(4) NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, language, year)
)
  ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (language, year) (
  PARTITION p0 VALUES LESS THAN (1, '2010'),
  PARTITION p1 VALUES LESS THAN (1, '2020'),
  PARTITION p2 VALUES LESS THAN (100, '2010'),
  PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

MariaDB 的分區(qū)的限制(Partitioning Limitations)

每個表最多可包含 8192 個分區(qū)（自 MariaDB 10.0.4）。在 MariaDB 5.5 和 10.0.3 中，限制是 1024。

目前，查詢永遠不會并行化，即使它們涉及多個分區(qū)。

只有當存儲引擎支持分區(qū)時，才能對表進行分區(qū)。

所有分區(qū)必須使用相同的存儲引擎。

分區(qū)表不能包含外鍵，也不能被外鍵引用。

查詢緩存(query cache)不知道分區(qū)和分區(qū)修剪(partitioning and partition pruning)。修改分區(qū)將使與整個表相關(guān)的條目失效。

當 binlog_format=ROW 且分區(qū)表被更新(update)時，更新的速度可能比等效的非分區(qū)表更慢。

分區(qū)表的分區(qū)表達式中使用的所有字段(column)必須是必須是 unique 結(jié)果( PRIMARY, UNIQUE_KEY 可支持)。

分區(qū)文件

分割后的 Table 將產(chǎn)生多個個別檔案

文件名編碼: table_name#P#partition_name.ext

在 InnoDB 下，會有以下 3 類:

table_name.frm
table_name.par
table_name#P#partition_name.ibd

分區(qū)文件示例

分區(qū)修剪(Partition Pruning)和分區(qū)選擇(partition selection)

當 WHERE 子句與分區(qū)表達式有關(guān)聯(lián)時，優(yōu)化器知道哪些分區(qū)與查詢相關(guān)。其它分區(qū)將不會被讀取。這種優(yōu)化稱為分區(qū)修剪。

可以使用 EXPLAIN 分區(qū)來了解將為給定的查詢讀取哪些分區(qū)。名為 partitions 的列將包含以逗號分隔的被訪問分區(qū)列表。

以之前的 article 表為例，回顧一下創(chuàng)建語言：

CREATE TABLE test200221.article(
    id integer unsigned not null auto_increment,
    publish_date date not null,
    author varchar(100),
    language tinyint unsigned,
    text text,
    primary key(id,language)
)ENGINE=InnoDB
partition by LIST (language)(
    PARTITION p0 VALUES  IN (1),
    PARTITION p1 VALUES  IN (2,3),
    PARTITION p2 VALUES  IN (4,5,6,7,8,9),
    PARTITION px VALUES  IN (NULL ) --  PARTITION px DEFAULT
)

使用EXPLAIN PARTITIONS來查看哪些分區(qū)會被使用到：

EXPLAIN PARTITIONS SELECT * FROM article WHERE language < 4;

使用EXPLAIN查看被使用分區(qū)

從結(jié)果來看，的確在WHERE language < 4;的條件下，只有 p0 和 p1 分區(qū)會有訪問到。

如果優(yōu)化器不知道或無法推斷出哪些分區(qū)會被使用到，可以通過 PARTITION 子句強制 MariaDB 僅訪問給定分區(qū)(MariaDB 10.0 開始)，這也被稱為分區(qū)選擇。

例如：

SELECT * FROM article PARTITION (p2) WHERE language = 5;

所有 DML 語句均支持 PARTITION 子句：SELECT、INSERT、UPDATE、DELETE、REPLACE、LOAD DATA 等。

通常情況下，分區(qū)修剪會用在觸發(fā)器(triggers)語句中。

但是如果在表上定義了 BEFORE INSERT 或者 BEFORE UPDATE 的觸發(fā)器，則 MariaDB 不會預先知道分區(qū)表達式中使用的字段(column)是否會更改。因此，被迫鎖定所有分區(qū)。

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

（十二）MariaDB中的分區(qū)操作

（十二）MariaDB中的分區(qū)操作

MariaDB 表分區(qū)（Table Partition）

MariaDB 的分區(qū)類型使用示例

RANGE 分區(qū)類型

LIST 分區(qū)類型

RANGE COLUMNS 和 LIST COLUMNS 分區(qū)類型

MariaDB 的分區(qū)的限制(Partitioning Limitations)

分區(qū)文件

分區(qū)修剪(Partition Pruning)和分區(qū)選擇(partition selection)

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

（十二）MariaDB中的分區(qū)操作

MariaDB 表分區(qū)（Table Partition）

MariaDB 的分區(qū)類型使用示例

RANGE 分區(qū)類型

LIST 分區(qū)類型

RANGE COLUMNS 和 LIST COLUMNS 分區(qū)類型

MariaDB 的分區(qū)的限制(Partitioning Limitations)

分區(qū)文件

分區(qū)修剪(Partition Pruning)和分區(qū)選擇(partition selection)

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av