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> explain partitions select * from log_role_gold_change_bak where format_time <='2013-07-02 10:39:56';//
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | log_role_gold_change_bak | s1,s2 | ALL | NULL | NULL | NULL | NULL | 12 | Using where |
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> select * from log_role_gold_change_bak where format_time <='2013-07-02 10:39:56';//
+----------------------------------+----+--------+-------------+----------+---------+---------------------+-------------+
| username | id | roleid | changecount | newcount | reason | format_time | create_time |
+----------------------------------+----+--------+-------------+----------+---------+---------------------+-------------+
| 6A376EC1A57BDEA109B63FAA89D4376C | 1 | 0 | 0 | 0 | getmail | 2012-07-02 10:39:56 | 1341196796 |
+----------------------------------+----+--------+-------------+----------+---------+---------------------+-------------+
1 row in set (0.00 sec)
成功了, 所以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)
)
当第一个值小于分区定义的第一个范围时,那么该行将属于这里了。
mysql> explain partitions select * from log_role_gold_change_bak where id=5 and roleid=100;//
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | log_role_gold_change_bak | s2 | const | PRIMARY | PRIMARY | 12 | const,const | 1 | |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)
mysql> select * from log_role_gold_change_bak where id=5 and roleid=100;//
+----------------------------------+----+--------+-------------+----------+--------------------+---------------------+-------------+
| username | id | roleid | changecount | newcount | reason | format_time | create_time |
+----------------------------------+----+--------+-------------+----------+--------------------+---------------------+-------------+
| C56F6C6EE70A0986F4C543549F1CAAC4 | 5 | 100 | -4 | 2 | all_server_cup_egg | 2014-05-22 13:37:25 | 1400737045 |
+----------------------------------+----+--------+-------------+----------+--------------------+---------------------+-------------+
1 row in set (0.00 sec)
-- 没有这条信息所以 下面的都为null
mysql> select * from log_role_gold_change_bak where id=30 and roleid=11100;//
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 111802
Current database: lszm_reku1
Empty set (0.22 sec)
mysql> explain partitions select * from log_role_gold_change_bak where id=30 and roleid=11100;//
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
因为 查的是固定的 某一行所以为 s3区
mysql> explain partitions select * from log_role_gold_change_bak where id=30 and roleid=11101;//
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | log_role_gold_change_bak | s3 | const | PRIMARY | PRIMARY | 12 | const,const | 1 | |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)
mysql> explain partitions select * from log_role_gold_change_bak where id=151 and roleid=1100;//
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 111865
Current database: lszm_reku1
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.19 sec)
mysql> explain partitions select * from log_role_gold_change_bak where id=150 and roleid=1100;//
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
当第一个值小于分区定义的第一个范围时,那么该行将属于这里了。
mysql> explain partitions select * from log_role_gold_change_bak where id=28 and roleid=11011;//
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | log_role_gold_change_bak | s2 | const | PRIMARY | PRIMARY | 12 | const,const | 1 | |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)
由于小于 28的 有s1 ,s2
mysql> explain partitions select * from log_role_gold_change_bak where id <28 and roleid<11011;//
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 111926
Current database: lszm_reku1
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | log_role_gold_change_bak | s1,s2 | range | PRIMARY | PRIMARY | 4 | NULL | 14 | Using where |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.19 sec)
//虽然 150 属于s3 范围 但是 10010101 不属于此范围 ,所以往下查找(这时150就不管查找范围内了,只看 roleid的查找范围了。)
当第一个值和第二个值等于他们对应的范围时,如果元组不小于定义的范围,那么它就不属于这里,继续下一步
mysql> explain partitions select * from log_role_gold_change_bak where id=150 and roleid=10010101;//
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | log_role_gold_change_bak | s4 | const | PRIMARY | PRIMARY | 12 | const,const | 1 | |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)
//再次验证了 当查找范围是 如果第一个判断值小于某一个分区的返回的第一个值,那么就在此分区了
mysql> explain partitions select * from log_role_gold_change_bak where id<5 and roleid<10010101;//
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | log_role_gold_change_bak | s1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from log_role_gold_change_bak where id<30 and roleid<10010101;//
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | log_role_gold_change_bak | s1,s2 | range | PRIMARY | PRIMARY | 4 | NULL | 14 | Using where |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from log_role_gold_change_bak where id<150 and roleid<10010101;//
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | log_role_gold_change_bak | s1,s2,s3 | range | PRIMARY | PRIMARY | 4 | NULL | 74 | Using where |
+----+-------------+--------------------------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)
可以看下面的几个图片:
转载于: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)
注意上面标红的。
联系客服