MySQL之分区

秒速五厘米 2022-04-22 05:10 270阅读 0赞

### 文章目录 ###

*  分区类型
 *  查看MySQL是否支持分区？
 *  分区类型
 *   *  RANGE分区
     *  LIST分区
     *  COLUMNS分区
     *   *  RANGE COLUMNS
         *  LIST COLUMNS
     *  HASH分区
     *   *  线性HASH分区
     *  KEY分区
 *  分区对NULL的处理
 *  查看分区内容
 *  分区的优点
 *  分区的缺点
 *  分区键，主键和唯一键的关系
 *  分区的问题
 *   *  NULL值会使分区过滤无效
     *  分区列和索引列不匹配
     *  选择分区的成本可能很高

# 分区类型 #

MySQL分区分为横向分区（按行分区），纵向分区（按列分区）。

# 查看MySQL是否支持分区？ #

*  MySQL 5.6一下版本使用如下命令：

mysql> SHOW VARIABLES LIKE '%partition%';

*  MySQL 5.6以上版本使用如下命令

mysql> SHOW PLUGINS;

partition的status是active时表示支持分区。

# 分区类型 #

## RANGE分区 ##

基于一个给定的连续区间范围，把数据分配到不同的分区。

mysql> CREATE TABLE IF NOT EXISTS `user` (  
     	->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
     	->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
    	->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',  
    	->   PRIMARY KEY (`id`)  
    	-> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
    	-> PARTITION BY RANGE (id) (  
    	->   PARTITION p0 VALUES LESS THAN (3),  
    	->   PARTITION p1 VALUES LESS THAN (6),  
    	->   PARTITION p2 VALUES LESS THAN (9),  
    	->   PARTITION p3 VALUES LESS THAN (12),  
    	->   PARTITION p4 VALUES LESS THAN MAXVALUE  
    	-> );

MAXVALUE表示最大可能的整数值。  
当要删除过期的数据时候，只需要简单的语句如此来删除p0分区中的数据：

ALTER TABLE user DROP PARTITION p0;

## LIST分区 ##

*  类似RANGE分区，区别在LIST分区是基于枚举出的值列表分区，而RANGE分组是基于连续区间范围进行分区。
 *  通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式。然后通过“VALUES IN (value\_list)”的方式来定义每个分区，其中“value\_list”是一个通过逗号分隔的整数列表。 如下：

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

*  删除整个分区比起DELETE FROM要高效的多。
 *  如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。

## COLUMNS分区 ##

*  它是在MySQL 5.5引入的分区类型，实际上是为了解决RANGE和LIST分区只支持整数分区的问题。COLUMNS可以细分为RANGE?COLUMNS和LIST?COLUMNS分区，他们都支持整数，日期时间，字符串三大数据类型：

1.  所有的整数类型，int，tinyint，bigint等，但不支持decimal，float等；
2.  日期时间类型：date和datetime；
3.  字符类型：char，varchar，binary，varbinary；不支持text和blob类型；

*  COLUMNS分区仅支持一个或多个列名做为分区键，而并不支持表达式作为分区键， 如上RANGE分区year(datadate),即使返回整型也不可以；但是其中一大亮点是能够支持多列分区。

### RANGE COLUMNS ###

如下：

CREATE TABLE test1 (
        a INT,
        b INT
    )
    PARTITION BY RANGE COLUMNS(a, b) (
        PARTITION p0 VALUES LESS THAN (5, 12),
        PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
    );
    
    INSERT INTO test1 VALUES (5,10), (5,11), (5,12);

元组值比较如下，先用第一个比较，如果第一个值小于分区的第二个值则直接返回true，不进行第二个值的比较。如果第一个值相等，则进行第二个值的比较。

### LIST COLUMNS ###

CREATE TABLE test3 (
        first_name VARCHAR(25),
        last_name VARCHAR(25),
        street_1 VARCHAR(30),
        street_2 VARCHAR(30),
        city VARCHAR(15),
        renewal DATE
    )
    PARTITION BY LIST COLUMNS(city) (
        PARTITION pRegion_1 VALUES IN('Oskarshamn', 'H?gsby', 'M?nster?s'),
        PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'V?stervik'),
        PARTITION pRegion_3 VALUES IN('N?ssj?', 'Eksj?', 'Vetlanda'),
        PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'V?xjo')
    );

和RANGE COLUMNS一样，支持其他数据类型作分区键。

## HASH分区 ##

*  基于用户自定义的表达式返回值来选择分区，该表达式对要插入到表中的列值操作。
 *  根据给定的分区个数，将数据分布到不同的分区，HASH分区只对整数有效，对应非整型将会转换为整型，MySQL支持两种HASH分区：常规HASH和线性HASH。
 *  要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式或者列值此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。
 *  实际上常规的hash是基于取模运算来判断新记录应该插入到那个分区的。例如，id=2, 运算表达式为mod(2, 4)。
 *  常规hash可以使数据均匀地平均分布到每一个分区中，但由于分区在创建的时候已经固定了。所以新增或收缩分区的数据迁移非常大。

### 线性HASH分区 ###

*  线性HASH分区与常规HASH分区不同之处在于线性HASH使用线性的2的幂运算法则，并且分区关键字为LINEAR HASH。
 *  PARTITION BY LINEAR HASH(YEAR(hired))。
 *  线性HASH分区的优点是增加、删除、合并和拆分分区会变得更加快捷，有利于吃力大量数据的表。缺点是数据分布不均匀。

## KEY分区 ##

*  类似HASH分区，但是HASH分区允许使用用户自定义的表达式，而KEY分区只能使用MySQL服务器提供的HASH函数，同时HASH分区只支持整数分区，而KEY分区支持除BLOB和TEXT类型外的其他列。
 *  在创建key分区的时候，不指定分区键，则会默认使用主键作为分区键，没有主键的情况下会选择非空唯一键作为分区键。
 *  在key分区的时候使用LINER关键字会与LINER HASH有同样的效果。

# 分区对NULL的处理 #

*  MySQL不禁止在分区键上使用NULL，这时会把NULL作为0或最小值处理。

# 查看分区内容 #

select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where TABLE_SCHEMA = schema() AND table_name = 'test1'; // 表名

# 分区的优点 #

*  分区表的数据可以分布在多个不同的物理设备上，从而高效地利用多个硬件设备。
 *  和单个磁盘或者文件系统相比，可以存储更多的数据。
 *  优化查询。在where条件中包含分区条件时，可以只扫描对应分区来提高查询效率。
 *  涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
 *  分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
 *  可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

# 分区的缺点 #

*  一个表最多只能有1024个分区。
 *  MySQL 5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
 *  分区表无法使用外键约束。
 *  MySQL的分区适用于一个表的所有数据和索引。不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。
 *  如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

# 分区键，主键和唯一键的关系 #

*  分区表的分区表达式中使用的所有列必须是表可能具有的每个唯一键的一部分。

# 分区的问题 #

分区的策略的依据是：查询能够过滤（prunning）掉很多额外的分区、分区本身并不会带来很多额外的代价。

## NULL值会使分区过滤无效 ##

*  关于分区表一个容易让人误解的地方就是分区的表达式可以为NULL。
 *  第一个分区是一个特殊的分区，当列的值为NULL或非法值的时候都会插入到第一个分区。现在假设有下面的查询：WHERE order\_date BETWEEN ‘2012-01-01’ AND ‘2012-01-31’。这时候MySQL会检查两个分区，而不是一个。因为YEAR()函数在结束非法值的时候会返回NULL，这么这个值可能会放到第一个分区。其他很多函数，如TO\_DAYS()也一样。
 *  可以通过创建一个无用的分区来避免这种情况。如`PARTITION p_nulls VALUES LESS THAN（0）`，这样当插入的数据是有效值的时候，第一个分区就是空的，这样即使要检查第一个分区，代价也会非常小。
 *  解决这个问题最好的方式是使用MySQL 5.5的列分区，如，PARTITION BY RANGE COLUMNS（order\_date）。

## 分区列和索引列不匹配 ##

*  如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。
 *  在一个关联查询中，分区表在关联顺序中是第二个表，并且关联使用的索引和分区条件并不匹配。那么关联时针对第一个表符合条件的每一行，都需要访问并搜索第二个表的所有分区。

## 选择分区的成本可能很高 ##

*  不同类型的分区实现方式不同，性能也各不相同。尤其是范围分区，对于回答“这一行属于哪个分区”、“这些符合查询条件的行在哪些分区”这样的问题的成本可能会非常高，因为服务器需要扫描所有的分区定义的列表来找到正确的答案。类似这样的线性搜索的效率不高，所以随着分区数的增长，成本会越来越高。
 *  可以通过限制分区的数量来缓解此问题，根据实践经验，对大多数系统来说，100个左右的分区是没有问题的。