MySQL 分区表

今天统计数据的时候发现一张表使用了表分区，借此机会记录一下。

1. 什么是表分区？

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

2. 表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

3. 表分区有什么好处？

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

4. 分区表的限制因素

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

5. 如何判断当前MySQL是否支持分区？

命令：show variables like '%partition%' 运行结果:

mysql> show variables like '%partition%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.00 sec)

1 2 3 4 5 6 7

mysql > show variables like '%partition%' ; + -- -- -- -- -- -- -- -- -- - + -- -- -- - + | Variable_name      | Value | + -- -- -- -- -- -- -- -- -- - + -- -- -- - + | have_partitioning | YES    | + -- -- -- -- -- -- -- -- -- - + -- -- -- - + 1 row in set ( 0.00 sec )

have_partintioning 的值为YES，表示支持分区。

6. MySQL支持的分区类型有哪些？

1）RANGE分区：按照数据的区间范围分区 2）LIST分区：按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。 3）HASH分区 4）KEY分区 说明 在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY分区的时候，可以使用其他类型的列（BLOB，TEXT类型除外）作为分区键。

7. Range分区

利用取值范围进行分区，区间要连续并且不能互相重叠。 语法：

partition by range(exp)( //exp可以为列名或者表达式，比如to_date(created_date) partition p0 values less than(num) )

1 2 3

partition by range ( exp ) ( //exp可以为列名或者表达式，比如to_date(created_date)      partition p0 values less than ( num ) )

例如：

mysql> create table emp( -> id INT NOT null, -> store_id int not null -> ) -> partition by range(store_id)( -> partition p0 values less than(10), -> partition p1 values less than(20) -> );

1 2 3 4 5 6 7 8

mysql > create table emp (      -> id INT NOT null ,      -> store_id int not null      -> )      -> partition by range ( store_id ) (