mysql 5.5 非整数进行分区_MySQL5.5的分区表

变更普通表baby_user_change_log为分区表

一、 表列描述

mysql> desc baby_user_change_log ;

+--------------+------------------+------+-----+---------+----------------+

| Field        | Type             | Null | Key | Default | Extra          |

+--------------+------------------+------+-----+---------+----------------+

| id           | int(11) unsigned | NO   | PRI | NULL    | auto_increment |

| account_id   | int(11) unsigned | NO   | MUL | NULL    |                |

| app_id       | int(11)          | YES  |     | NULL    |                |

| operate      | varchar(20)      | YES  |     | NULL    |                |

| old_data     | varchar(2000)    | YES  |     | NULL    |                |

| new_data     | varchar(2000)    | YES  |     | NULL    |                |

| change_data  | varchar(2000)    | YES  |     | NULL    |                |

| operate_time | int(11)          | YES  |     | NULL    |                |

+--------------+------------------+------+-----+---------+----------------+

二、 表结构特征

CREATE TABLE `baby_user_change_log` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',

`account_id` int(11) unsigned NOT NULL COMMENT 'account_id',

`app_id` int(11) DEFAULT NULL COMMENT '平台ID',

`operate` varchar(20) DEFAULT NULL COMMENT '操作类型',

`old_data` varchar(2000) DEFAULT NULL COMMENT '修改之前的数据',

`new_data` varchar(2000) DEFAULT NULL COMMENT '修改之后的数据',

`change_data` varchar(2000) DEFAULT NULL COMMENT '被修改的数据',

`operate_time` int(11) DEFAULT NULL COMMENT '时间',

PRIMARY KEY (`id`),

KEY `idx_account_id` (`account_id`)

)ENGINE=MyISAM AUTO_INCREMENT=18543058 DEFAULT CHARSET=utf8;

三、适合的分区方案

1)表总数据记录条数：

mysql> select count(*) from baby_user_change_log;

+----------+

| count(*) |

+----------+

| 18552945 |

+----------+

2)其中app_id 列具有按照RANGE分区的特征

mysql> select distinct(app_id) from baby_user_change_log;

+--------+

| app_id |

+--------+

|      7 |

|      5 |

|      3 |

|      1 |

+--------+

3)具体的分区表结构SQL

CREATE TABLE `baby_user_change_log_partition` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',

`account_id` int(11) unsigned NOT NULL COMMENT 'account_id',

`app_id` int(11) DEFAULT NULL COMMENT '平台ID',

`operate` varchar(20) DEFAULT NULL COMMENT '操作类型',

`old_data` varchar(2000) DEFAULT NULL COMMENT '修改之前的数据',

`new_data` varchar(2000) DEFAULT NULL COMMENT '修改之后的数据',

`change_data` varchar(2000) DEFAULT NULL COMMENT '被修改的数据',

`operate_time` int(11) DEFAULT NULL COMMENT '时间',

PRIMARY KEY (`id`,`app_id`),

KEY `idx_account_id` (`account_id`)

)ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

PARTITION BY RANGE (app_id) (

PARTITION p0 VALUES LESS THAN (1),

PARTITION p1 VALUES LESS THAN (3),

PARTITION p2 VALUES LESS THAN (5),

PARTITION p3 VALUES LESS THAN (7),

PARTITION p4 VALUES LESS THAN MAXVALUE

);

4)插入数据

insert into baby_user_change_log_partition select* from baby_user_change_log;

5)验证结果

mysql> explain partitions select *  from baby_user_change_log_partition where app_id=1;

+----+-------------+-----------------------------------------+------------+------+---------------+------------+---------+-------+-------+-------+

| id | select_type | table                                   | partitions | type | possible_keys | key        | key_len | ref   | rows  | Extra |

+----+-------------+-----------------------------------------+------------+------+---------------+------------+---------+-------+-------+-------+

|  1 | SIMPLE      | baby_user_change_log_partition | p1         | ref  | idx_app_id    | idx_app_id | 4       | const | 25739 |       |

+----+-------------+-----------------------------------------+------------+------+---------------+------------+---------+-------+-------+-------+

1 row in set (0.00 sec)

mysql> explain partitions select *  from baby_user_change_log_partition where app_id=7;

+----+-------------+-----------------------------------------+------------+------+---------------+------------+---------+-------+------+-------+

| id | select_type | table                                   | partitions | type | possible_keys | key        | key_len | ref   | rows | Extra |

+----+-------------+-----------------------------------------+------------+------+---------------+------------+---------+-------+------+-------+

|  1 | SIMPLE      | baby_user_change_log_partition | p4         | ref  | idx_app_id    | idx_app_id | 4       | const |  276 |       |

+----+-------------+-----------------------------------------+------------+------+---------------+------------+---------+-------+------+-------+

1 row in set (0.00 sec)

mysql> explain partitions select *  from baby_user_change_log_partition;

+----+-------------+-----------------------------------------+----------------+------+---------------+------+---------+------+-------+-------+

| id | select_type | table                                   | partitions     | type | possible_keys | key  | key_len | ref  | rows  | Extra |

+----+-------------+-----------------------------------------+----------------+------+---------------+------+---------+------+-------+-------+

|  1 | SIMPLE      | baby_user_change_log_partition | p0,p1,p2,p3,p4 | ALL  | NULL          | NULL | NULL    | NULL | 56269 |       |

+----+-------------+-----------------------------------------+----------------+------+---------------+------+---------+------+-------+-------+

1 row in set (0.00 sec)

四、 分区表的原理及优缺点

1)分区表是什么？分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，

存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎)，分区表的索引只是在各个

底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是

一个普通表还是一个分区表的一部分。

2)在分区表上的操作按照下面的操作逻辑进行：

1.select查询：

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，

然后再调用对应的存储引擎接口访问各个分区的数据

2.insert操作：

当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表

3.delete操作：

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

4.update操作：

当更新一条数据时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，

再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作

3)虽然每个操作都会打开并锁住所有的底层表，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，

如：innodb，则会在分区层释放对应的表锁，这个加锁和解锁过程与普通Innodb上的查询类似。

4)分区表适用的场景

1.表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他都是历史数据

2.分区表的数据更容易维护，如：想批量删除大量数据可以使用清除整个分区的方式。另外，

还可以对一个独立分区进行优化、检查、修复等操作

3.分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

4.可以使用分区表来避免某些特殊的瓶颈，如：innodb的单个索引的互斥访问，ext3文件系统的inode锁竞争等

5.如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好

6.优化查询，在where字句中包含分区列时，可以只使用必要的分区来提高查询效率，

同时在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，

最终只需要汇总所有分区得到的结果。

5)分区表的限制

1.一个表最多只能有1024个分区,包含子分区(mysql5.6之后支持8192个分区)

2.在mysql5.1中分区表达式必须是整数，或者是返回整数的表达式，

在5.5之后某些场景可以直接使用字符串列和日期类型列来进行分区

使用varchar字符串类型列时，一般还是字符串的日期作为分区。

3.如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，

如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引

4.分区表中无法使用外键约束

5.mysql数据库支持的分区类型为水平分区，并不支持垂直分区，

因此mysql数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，

而全局分区是指的数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中

6.目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引

五、分区表的分区类型

1)分区表根据数据类型的特征适用不同的分区类型主要的类型有：

1.RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

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

3.HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。

这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

4.KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。

必须有一列或多列包含整数值。

分区表官方文档的解释与说明：

http://dev.mysql.com/doc/refman/5.5/en/partitioning.html