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

     

     本文转自andylhz 51CTO博客,原文链接:http://blog.51cto.com/andylhz2009/1866007,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章