首页 > 代码库 > MySQL5.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
本文出自 “影子骑士” 博客，请务必保留此出处http://andylhz2009.blog.51cto.com/728703/1866007
MySQL5.5的分区表