MySQL5.5的分区表

234123

变更普通表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