mysql5.1开始支持数据表分区

分区场景：改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率

mysql分区认识：
1、把数据分段划分在多个位置存放，可以是同一块磁盘也可以是不同的机器
2、分区后表面上还是一张表，但数据散列到多个位置了
3、读写时还是表明不变，数据库自动去组织分区的数据

mysql分区优势：

 1）、与单个磁盘或文件系统分区相比，可以存储更多的数据。
 2）、对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；
3）、一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
4）、涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
5）、通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
 

分区类型
mysql中的分区有：range、list、hash、key四种分区方法

 

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

 

range方法，就是划分范围，比如我们按照id字段划分。

对已有表进行分区

然后可以查看一下此表存储的位置，可以使用如下SQL语句查看存储位置：

show VARIABLES like '%datadir%';

开始分表：

ALTER TABLE users PARTITION by RANGE(`id`) 
 (
     PARTITION users_a VALUES less THAN(4),
     PARTITION users_b VALUES less THAN(8),
     PARTITION users_c VALUES less THAN(MAXVALUE)
 );



说明：我们对users 表，按照id 字段划分范围。id小于4的一个分区，然后id小于8的一个分区，最后剩下的一个分区。

KEY 分区语句：

alter table TABLENAME PARTITION BY key(COLUMN) PARTITIONS NUM;
1
TABLENAME ：表名
COLUMN：列名
NUM：分区数量

检查分区是否创建成功

EXPLAIN PARTITIONS SELECT * FROM `cars`
1

说明：key 分区，只有指定分区数目为质数，才能保证每个分区都有数据

 

KEY 分区和 HASH 分区相似，但是 KEY 分区支持除 text 和 BLOB 之外的所有数据类型的分区，而 HASH 分区只支持数字分区，KEY 分区不允许使用用户自定义的表达式进行分区，KEY 分区使用系统提供的 HASH 函数进行分区。
当表中存在主键或者唯一键时，如果创建 key 分区时没有指定字段系统默认会首选主键列作为分区字列，如果不存在主键列会选择非空唯一键列作为分区列，注意唯一列作为分区列唯一列不能为null。

查看已分区表的详情

SELECT *
 FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'TABLE_NAME';

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

      要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

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 HASH(store_id)
 PARTITIONS 4；


 如果没有包括一个PARTITIONS子句，那么分区的数量将默认

 

LINER HASH
MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规 哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字。

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 LINEAR HASH(YEAR(hired))
 PARTITIONS 4；

LIST分区
      类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

      LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
注释：在MySQL 5.1中，当使用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
 )；


 
假定有20个音像店，分布在4个有经销权的地区，如下表所示：

====================
地区      商店ID 号

------------------------------------

北区      3, 5, 6, 9, 17
东区      1, 2, 10, 11, 19, 20
西区      4, 12, 13, 14, 18
中心区   7, 8, 15, 16

====================
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：

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

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE employees DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE （删除）查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。
【要点】：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：

INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);
 
这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

LIST分区除了能和RANGE分区结合起来生成一个复合的子分区