mariadb 分区表

用途

• 即使使用优化的查询，很大的表和索引也可能很慢。但是，如果目标表已分区，则读取少量分区的查询会更快。
• 分区允许一个人在多个存储设备上分发文件。例如，我们可以将历史数据存储在较慢，较大的磁盘上（不应经常读取历史数据）。当前数据可以存储在速度更快的磁盘或SSD设备上。
• 如果我们将历史数据与最近的数据分开，则可能需要对一个分区而不是整个表进行定期备份。

查看系统是否支持分区表

MariaDB [(none)]> SHOW PLUGINS;
......
| unix_socket                   | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| user_variables                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+-------------------------------+----------+--------------------+---------+---------+
54 rows in set (0.002 sec)

分区表要确定用于分区的a partitioning type类型和表达式a partitioning expression.分区类型确定分区表的行如何跨分区分布。某些分区类型要求用户指定一个分区表达式，该表达式确定将行存储在哪个分区中。各个分区的大小取决于分区类型。读写性能受分区表达式的影响。因此，应谨慎选择这些选择。

mariadb支持的type: RANGE LIST RANGE COLUMNS and LIST COLUMNS, HASH COLUMNS HASH KEY LINEAR HASH, LINEAR KEY SYSTEM_TIME

RANGE分区

RANGE分区类型用于为每个分区分配由分区表达式生成的值的范围。范围必须是有序的，连续的且不重叠的。最小值始终包含在第一个范围内。最高值可以或可以不包括在最后一个范围内。

这种分区方法的一种变体RANGE COLUMNS允许我们使用多列和更多数据类型。

• 创建语法

RANGE PARTITION BY RANGE（partitioning_expression）
（
    PARTITION partition_name VALUES LESS THAN（value），
    [PARTITION partition_name VALUES LESS THAN（value），...] 
）

exampe:

#按年创建分区表
CREATE TABLE log
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    timestamp DATETIME NOT NULL,
    user INT UNSIGNED,
    ip BINARY(16) NOT NULL,
    action VARCHAR(20) NOT NULL,
    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)
);

#按年和月创建
CREATE TABLE log
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    timestamp TIMESTAMP NOT NULL,
    user INT UNSIGNED,
    ip BINARY(16) NOT NULL,
    action VARCHAR(20) NOT NULL,
    PRIMARY KEY (id, timestamp)
)
    ENGINE = InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp))
(
    PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01 00:00:00')),
    PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-01 00:00:00')),
    PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
    PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01 00:00:00'))
);

删除分区: ALTER TABLE log DROP PARTITION p0;

如果插入数据timestamp不在分区表范围会报错，通过IGNORE忽略错误

INSERT IGNORE INTO log(id,timestamp) VALUES 
  (1, '2016-01-01 01:01:01'), 
  (2, '2015-01-01 01:01:01');

SELECT * FROM log;
+----+---------------------+------+------------------+--------+
| id | timestamp           | user | ip               | action |
+----+---------------------+------+------------------+--------+
|  2 | 2015-01-01 01:01:01 | NULL |                  |        |
+----+---------------------+------+------------------+--------+

通过MAXVALUE可以定义一个超出范围的最大值分区

CREATE TABLE log
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    timestamp DATETIME NOT NULL,
    user INT UNSIGNED,
    ip BINARY(16) NOT NULL,
    action VARCHAR(20) NOT NULL,
    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),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

LIST Partitioning类型

LIST分区在概念上类似于RANGE分区。在这两种情况下，您都需要确定一个分区表达式（一个列，或者稍微复杂一点的计算），然后使用它来确定哪些分区将包含每一行。但是，对于RANGE类型，分区是通过为每个分区分配一个值范围来完成的。对于LIST类型，我们为每个分区分配一组值。如果分区表达式可以返回一组有限的值，则通常是首选方法。

这种分区方法的一种变体LIST COLUMNS允许我们使用多列和更多数据类型。

• 语法

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

RANGE COLUMNS和LIST COLUMNS分区类型

RANGE COLUMNS和LIST COLUMNS分别是RANGE和LIST的变体。对于这些分区类型，没有一个分区表达式。而是接受一个或多个列的列表。适用以下规则：

• 该列表可以包含一个或多个列。
• 列可以是任何integer，string，DATE和DATETIME类型。

• 仅允许使用裸列；没有分区表达式

• 创建分区表语法

#创建RANGE COLUMNS PARTITION 
PARTITION BY RANGE COLUMNS (col1, col2, ...)
(
    PARTITION partition_name VALUES LESS THAN (value1, value2, ...),
    [ PARTITION partition_name VALUES LESS THAN (value1, value2, ...), ... ]
)
#创建LIST COLUMNS PARTITION
PARTITION BY LIST COLUMNS (partitioning_expression)
(
    PARTITION partition_name VALUES IN (value1, value2, ...),
    [ PARTITION partition_name VALUES IN (value1, value2, ...), ... ]
        [ PARTITION partititon_name DEFAULT ]
)

分区表的限制

• 每个表最多可以包含8192个分区（来自MariaDB 10.0.4）。在MariaDB 5.5和10.0.3之前，限制为1024。
• 使涉及多个分区，查询不会并行化。
• 如果存储引擎支持分区，则只能对表进行分区。
• 所有分区必须使用相同的存储引擎。
• 分区表不能包含外键或由外键引用。
• 当binlog_format = ROW并且更新了分区表时，更新的运行速度可能会比非分区表的等效更新慢。
• 分区表的分区表达式中使用的所有列都必须是该表可能具有的每个唯一键的一部分。
• 查询缓存分区和分区修剪无效，修改一个分区将使与整个表相关的条目无效。

# 查看分区信息

SELECT
    *
FROM 
    INFORMATION_SCHEMA.PARTITIONS
WHERE 
    TABLE_SCHEMA = 'test'          # 库名
AND TABLE_NAME = 'log';    # 表名

# 常用列

 PARTITION_METHOD,
    PARTITION_NAME,  
    PARTITION_EXPRESSION,  
    PARTITION_DESCRIPTION,  
    TABLE_ROWS

如：

’通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区