mysql 关于range 分区的认识

WindySky 2016-05-17

展开全文

msysql> delimiter // 模式进入

Create Table: CREATE TABLE `log_role_gold_change_bak` (
`username` varchar(50) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`roleid` bigint(20) NOT NULL,
`changecount` int(10) NOT NULL,
`newcount` int(10) NOT NULL,
`reason` varchar(20) NOT NULL,
`format_time` varchar(20) NOT NULL,
`create_time` int(10) NOT NULL,
PRIMARY KEY (`id`)
)

图：4-1

当被分区的字段没有在主键上时，会出现上图（4-1）这样的错误。

修改sql:alert table log_role_gold_change_bak drop primary key,add primary key(`id`,`roleid`);

图：4-2

Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

（重组的范围分区不能改变总范围除了最后一个分区,可以扩展范围）

如果想把原来的分区重新分区的话，必须是在原来的分区里面选择。 //这句没看懂的话接着往下看（图4-4）

仔细看上图两次 alter 的不同区别；然后再看此图。

图：4-3

所以如果要是第一次分区以后过了一段时间想重新分区的话，就可以这样做了，把最大的那个分区在分裂就行了。如上图

然后要是看创建分区的结构执行：

MySQL> show create table log_role_gold_change_bak\G;
*************************** 1. row ***************************
Table: log_role_gold_change_bak
Create Table: CREATE TABLE `log_role_gold_change_bak` (
`username` varchar(50) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`roleid` bigint(20) NOT NULL,
`changecount` int(10) NOT NULL,
`newcount` int(10) NOT NULL,
`reason` varchar(20) NOT NULL,
`create_time` int(10) NOT NULL,
PRIMARY KEY (`id`,`roleid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`roleid`)
(PARTITION p1 VALUES LESS THAN (110) ENGINE = InnoDB index directory='/data/testpath/path1/idx' data directory='/data/testpath/path1/data',
PARTITION p2 VALUES LESS THAN (10010) ENGINE = InnoDB index directory='/data/testpath/path2/idx' data directory='/data/testpath/path2/data',
PARTITION p3 VALUES LESS THAN (10011) ENGINE = InnoDB index directory='/data/testpath/path3/idx' data directory='/data/testpath/path3/data',
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB index directory='/data/testpath/path4/idx' data directory='/data/testpath/path4/data') */
1 row in set (0.00 sec)

目录必须有 mysql 读写权限 chown -R mysql:mysql 路径；

、、、、、、、、、、、仔细看下面的几个、、、、、、、、、、、、、、、、、、、、、

图4-4

可以看出如果想把s1,s2,s3,s4 重新分割的话，重新分区的最后一列(p2)必须大于等于重新分割的老分区的一列（s2）

最后结果为：

图4-5

总结： “多分一” ，“ 一分多” 可以直接分区，但是多分多的话，要注意分区范围了。

比如：

======一对多===多对一====

less than 10

less than 100       《========》 p1 values less than    (10) 或者 less than maxvalue 或者 100    或者101

less than maxvalue
======多对多=======

s1 values less than 10                                            p1    values less than 5

s2 values less than 100       《========》 p2 values less than    (100) 或者 less than maxvalue   或者101

s3 values less than maxvalue

**************end*************

ALTER TABLE log_role_gold_change_bak ADD PARTITION (PARTITION s5 VALUES LESS THAN (100000000));

这条sql 语句只有在没有 maxvalue 时才有效, 否则执行时会报错;

图4-6

针对时间进行分区看下图的不同：

图4-7

mysql5.5新的函数中增加了一个COLUMNS关键词,如果你想使用MySQL 5.1中的分区类型(range)，那你必须将类型转换成整数，需要使用一个额外的查找表，到了MySQL 5.5中，你可以不用再进行类型转换了，所以columns 字段是为了不再进行类型转换而加的，所以第一个sql报错了。第二个sql 用了 year 函数转换了。

下面这个图：

explain partitions 这个是用来查找当前的sql 用到了哪个分区，下面(图4-8)的貌似有问题 partitions 那个模块指向的参数是 s02,我认为应该是s03的有知道的话给说下

图4-8

通过上面的几个图片看出一种结果就是一个表里只能对一个字段进行分区，（如上面第一次给 roleid分区，第二次给 create_time分区）如果给另个字段，会把之前的覆盖掉。

warming:

图4-9

使用YEAR或TO_DAYS定义一个分区的确让人费解，查询时不得不使用赤裸列，因为加了函数的查询不能识别分区。

如果这时候你想通过 alter table log_role_gold_change_bak drop primary key,add primary key(`id`,`format_time`); 来改变主键上的字段，这样是不行的，会报如下错。

A PRIMARY KEY must include all columns in the table's partitioning function，和在增加分区主键上没有字段报的错是一样的。

这里报错的原因是因为你已经在这个字段上创建分区了。所以要想在主键上增加新的字段分区，必须先删除之前的分区。sql: alter table log_role_gold_change_bak remove partitioning; 然后再使用上面的sql进行增加主键分区字段。如下图：

图4-10

这样就行了。

mysql> alter table log_role_gold_change_bak partition by range columns(`format_time`)(   -- columns 5.5 才支持的
    -> partition s1 values less than ('2013-07-02 10:39:56'),
    -> partition s2 values less than ('2015-07-02 10:39:56'),
    -> partition s3 values less than (maxvalue)
    -> )
    -> //
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    109839
Current database: lszm_reku1

Query OK, 11 rows affected (0.32 sec)
Records: 11 Duplicates: 0 Warnings: 0

测试：

成功了，所以mysql 5.5 的 columns 这个关键字很有用啊(COLUMNS关键字现在允许字符串和日期列作为分区定义列)。这里可以看出此查询只是在s1,s2 中进行了查找，因为小于2013-07-02 10:39:56 的只能在s1,s2 分区中查找

下面对多个字段进行分区：

alter table log_role_gold_change_bak partition by range columns(`id`,`roleid`)(
   partition s1 values less than (5,100),
   partition s2 values less than (30,170),
   partition s3 values less than (150,1100),
   partition s4 values less than (450,maxvalue),
   partition s5 values less than (maxvalue,maxvalue)
)

当第一个值小于分区定义的第一个范围时，那么该行将属于这里了。

//虽然 150 属于s3 范围但是 10010101 不属于此范围，所以往下查找（这时150就不管查找范围内了，只看 roleid的查找范围了。）

当第一个值和第二个值等于他们对应的范围时，如果元组不小于定义的范围，那么它就不属于这里，继续下一步

可以看下面的几个图片：

转载于：http://images.51cto.com/files/uploadimg/20100222/102648467.jpg

图1

图2

图3

图4

SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME='log_role_gold_change_bak';

通过这个sql 可以看出当前每个分区里的大约的行数。如果存储引擎是InnoDB，上面的值就是一个近似值，如果你需要确切的值，那你就不能信任它们。如果是myisam 那就是确切的行数。

清空某一分区，但是留下分区结构：

alter table log_role_gold_change_baktruncate partition s1//

mysql> show create table log_role_gold_change_bak\G;
*************************** 1. row ***************************
Table: log_role_gold_change_bak
Create Table: CREATE TABLE `log_role_gold_change_bak` (
`username` varchar(50) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`roleid` bigint(20) NOT NULL,
`changecount` int(10) NOT NULL,
`newcount` int(10) NOT NULL,
`reason` varchar(20) NOT NULL,
`format_time` varchar(20) NOT NULL,
`create_time` int(10) NOT NULL,
PRIMARY KEY (`id`,`roleid`)
) ENGINE=InnoDB AUTO_INCREMENT=502 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(id,roleid)
(PARTITION s1 VALUES LESS THAN (5,100) ENGINE = InnoDB,
PARTITION s2 VALUES LESS THAN (30,170) ENGINE = InnoDB,
PARTITION s3 VALUES LESS THAN (150,1100) ENGINE = InnoDB,
PARTITION s4 VALUES LESS THAN (450,MAXVALUE) ENGINE = InnoDB,
PARTITION s5 VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.00 sec)

-> //
ERROR 1065 (42000): Query was empty

//删除某一分区

mysql> alter table log_role_gold_change_bak drop partition s1//
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table log_role_gold_change_bak\G;
*************************** 1. row ***************************
Table: log_role_gold_change_bak
Create Table: CREATE TABLE `log_role_gold_change_bak` (
`username` varchar(50) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`roleid` bigint(20) NOT NULL,
`changecount` int(10) NOT NULL,
`newcount` int(10) NOT NULL,
`reason` varchar(20) NOT NULL,
`format_time` varchar(20) NOT NULL,
`create_time` int(10) NOT NULL,
PRIMARY KEY (`id`,`roleid`)
) ENGINE=InnoDB AUTO_INCREMENT=502 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(id,roleid)
(PARTITION s2 VALUES LESS THAN (30,170) ENGINE = InnoDB,
PARTITION s3 VALUES LESS THAN (150,1100) ENGINE = InnoDB,
PARTITION s4 VALUES LESS THAN (450,MAXVALUE) ENGINE = InnoDB,
PARTITION s5 VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.00 sec)