mysql表分区

表分区是mysql被Oracle收购后推出的一个新特性 

1、概述

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

分区是一种表的设计模式，正确的分区可以积极大的提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误的使用分区，n那么会造成不可逆的结果。

分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MySAM，NDB等都支持分区。但并不是所有的存储引擎都支持，如CSV,FEDORATED,MERGE等就不支持分区。在使用此分区功能前，应选择存储引擎对分区的支持有所了解。

MySQL数据库在5.1版本时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小，更可管理的部分。就访问数据库的应用而言，从逻辑上将，只有一个表或者一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大的随心的一部分进行处理。

MySQL数据库支持的分区类型为水平分区（指同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一个表中的不同列的记录分配到不同的物理文件中）。此外mysql数据库的分区是局部分区索引，一个分区中既放了数据又放了索引。而全局分区是指，数据存放在各个分区中，但是所有的数据的索引放在一个对象中，目前MySQL数据库还不支持全局分区。

2、表分区是什么

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

通俗地讲表分区是将一大表，根据条件分割成若干个小表。mysql5.1开始支持数据表分区。

如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

表分区的类型

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

RANGE分区（常用）

基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。

create table test_001(
    id int not null,
    hired date not null ,
		select_id int
) partition by range (select_id) (
    partition p0 values less than (1),
    partition p1 values less than (5000),
    partition p2 values less than (100000),
    partition p3 values less than (200000),
    partition p4 values less than (300000),
    partition p5 values less than (400000),
    partition p6 values less than (500000),
    partition p7 values less than (600000),
    partition p8 values less than (700000),
    partition p9 values less than (800000),
    partition p10 values less than (900000),
    partition p11 values less than maxvalue
);

SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.PARTITIONS WHERE
TABLE_SCHEMA = schema()
and TABLE_NAME = 'test_001';

#导入一百万条数据
CREATE PROCEDURE load_test_001()
BEGIN
DECLARE v int DEFAULT 0;
WHILE v < 1000000
DO
INSERT into test_001
VALUES (v,ADDDATE('1995-01-01',(RAND(v)*36520) MOD 3652),v);
set v = v + 1;
end while;
end

call load_test_001();

SELECT COUNT(1) FROM test_001
insert into test_001 SELECT * from test_001

创建分区并查看分区结果

 导入数据

 数据插入成功

3、表分区有什么好处？

（1）、与单个磁盘或文件系统分区相比，可以存储更多的数据。

（2）、对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

（3）、一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

（4）、涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

5）、通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。