MySQL分区表基础

2023-08-09 23:43:28

首先要确定MySQL是否支持分区：

Mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+
| Variable_name            | Value   |
+-----------------------+-------+
| have_partition_engine | YES     |
+-----------------------+-------+
1 row in set (0.00 sec)

当Value为NO时，需要打开MySQL分区使能：

Mysql> set @@global.have_partition_engine = ‘yes';

下面的例子给出了怎样创建一个通过HASH分成6个分区、使用InnoDB存储引擎的表：

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH(MONTH(tr_date))
PARTITIONS 6;

分区的优点：
  • · 与单个磁盘或文件系统分区相比，可以存储更多的数据。·
  • ·对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
  • · 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
  • · 涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BYsalesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
  • · 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
MySQL可以建立四种分区类型的分区：
  • RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
  • LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
  • KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
 
一般用得多的是RANGE分区和LIST分区。
 
按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。
 

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21),

PARTITION p4 VALUES LESS THAN MAXVALUE

)；

 
新增分区
mysql> ALTER TABLE employees
    ->   ADD PARTITION (PARTITION ps1 VALUES LESS THAN (26));
对于通过RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。
设法通过这种方式在现有分区的前面或之间增加一个新的分区，将会导致下面的一个错误：
mysql> ALTER TABLE employees
    ->   ADD PARTITION (PARTITION ps1 VALUES LESS THAN (3));

错误1463 (HY000): 对每个分区，VALUES LESS THAN 值必须严格增长。

如果需要完成类似需求，应该是进行分区的拆分，下面有描述。

不能添加这样一个新的LIST分区，该分区包含有已经包含在现有分区值列表中的任意值。

 
 
删除分区
--当删除了一个分区，也同时删除了该分区中所有的数据。
mysql> ALTER TABLE employees DROP PARTITION ps1;
 
分区的合并
mysql> ALTER TABLE employees
    ->   REORGANIZE PARTITION p0 ,p1,p2,p3,p4
    -> (
    ->   PARTITION pw1 VALUES LESS THAN (11),
    ->   PARTITION pw2 VALUES LESS THAN (21),
    ->   PARTITION pw3 VALUES LESS THAN MAXVALUE
    -> );
 
分区的拆分
mysql> ALTER TABLE employees REORGANIZE PARTITION pw3 INTO ( 
    ->     PARTITION pw3_1 VALUES LESS THAN 31,
    ->     PARTITION pw3_2 VALUES LESS THAN MAXVALUE 
    -> );
 
LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
)；

 
如果插入一行到按照RANGE或LIST分区的表，该行用来确定分区的列值为NULL，分区将把该NULL值视为0。
 
分区索引的局限：
  • 所有分区都要使用同样的引擎。
  • 分区表的每一个唯一索引必须包含由分区函数引用的列。
  • MySQLl能避免查询所有的分区，但仍然锁定了所有分区。
  • 分区函数能使用的函数和表达式有限，例如函数有上面的4种。
  • 分区不支持外键。
  • 不能使用LOAD INDEX INTO CACHE
  • 分区并不能总是改善性能，要进行性能评测。
　　可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据：
mysql> explain partitions select * from fenqubiao where day<'2011-09-12';
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | partitions    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | fenqubiao | p_2010,p_2011 | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
 

MySQL分区表中可以建立子分区

子分区是分区表中每个分区的再次分割。例如，考虑下面的CREATE TABLE 语句：

CREATE TABLE ts (id INT, purchased DATE)

PARTITION BY RANGE(YEAR(purchased))

SUBPARTITION BY HASH(TO_DAYS(purchased))

SUBPARTITIONS 2

(

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN MAXVALUE

)；

表ts 有3个RANGE分区。这3个分区中的每一个分区——p0, p1, 和 p2 ——又被进一步分成了2个子分区。实际上，整个表被分成了3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，这些分区的头2个只保存“purchased”列中值小于1990的那些记录。

上一篇：数据切分——Mysql分区表的建立及性能分析


下一篇：(3) MySQL分区表使用方法