Mysql 表分区操作

1、什么是表分区？

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

2、表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

3、表分区有什么好处？

（1）、与单个磁盘或文件系统分区相比，可以存储更多的数据。

（2）、对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

（3）、一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

（4）、涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

5）、通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

4、表分区注意事项

1）、一个表最多只能有1024个分区。

（2）、 MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

（3）、如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

（4）、分区表中无法使用外键约束。

（5）、MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

5、如何判断当前MySQL是否支持分区？

mysql从5.1开始支持分区功能

-- mysql5.6以下方法
show plugins like '%partiotion%';

--have_partintioning 的值为YES，表示支持分区。

-- mysql5.6及以上方法
show plugins;

--上面的查询方法会显示所有插件，如果有红色部分（如下）的话，表示支持分区。
partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL 

 

RANGE COLUMNS范围分区

创建测试表

CREATE TABLE `t_test` (
    `id` INT (11),
    `dates` DATETIME 
);
ALTER TABLE t_test ADD PRIMARY KEY (id);
ALTER TABLE t_test MODIFY id INT AUTO_INCREMENT;


-- 创建一个生成测试数据的存储过程
DELIMITER //
CREATE PROCEDURE `add_vote_memory`(n INT)
BEGIN  
  DECLARE i INT DEFAULT 1;
  DECLARE datess VARCHAR(256);
    WHILE (i <= n ) DO
    -- 随机时间 这里生成的都是2018年的
    SELECT   CONCAT(2018,'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)) INTO datess;
      INSERT INTO t_test(dates ) VALUES (datess);
            SET i=i+1;
    END WHILE;
END //

-- 生成测试数据
CALL add_vote_memory(500);

创建分区表

CREATE TABLE t_range_test (
    id INT (11),
    dates DATETIME
)PARTITION BY RANGE COLUMNS ( dates ) (
    -- 第一组 小于2018-01-01的
    PARTITION p1 VALUES LESS THAN  ( '20180101' ),
    -- 大于等于2018-01-01 小于2018-02-01的
    PARTITION p2 VALUES LESS THAN  ( '20180201' ),
    PARTITION p3 VALUES LESS THAN  ( '20180301' ),
    PARTITION p4 VALUES LESS THAN  ( '20180401' ),
    PARTITION p5 VALUES LESS THAN  ( '20180501' ),
    PARTITION p6 VALUES LESS THAN  ( '20180601' ),
    PARTITION p7 VALUES LESS THAN  ( '20180701' ),
    PARTITION p8 VALUES LESS THAN  ( '20180801' ),
    PARTITION p9 VALUES LESS THAN  ( '20180901' ),
    PARTITION p10 VALUES LESS THAN ( '20181001' ),
    PARTITION p11 VALUES LESS THAN ( '20181101' ),
    PARTITION p12 VALUES LESS THAN ( '20181201' ),
    -- 大于等于2018-12-01 小于2019-01-01的
    PARTITION p13 VALUES LESS THAN ( '20190101' ),
    -- 最后一组 大于上面的
    PARTITION p2022 VALUES LESS THAN ( MAXVALUE )
);

-- 把上面测试表的数据插入到分区表中
INSERT INTO t_range_test SELECT * FROM t_test;

查看执行计划、结果看PARTITIONS列

EXPLAIN PARTITIONS SELECT * FROM t_range_test WHERE dates >= '20181107124503' AND dates<='20181210111230';

查看表分区状况 TABLE_SCHEMA:库名;TABLE_NAME:表名.  ps: TABLE_ROWS 数据量可能有偏差，官方给的解释是数据仅供参考。

SELECT t.`PARTITION_NAME`,t.`SUBPARTITION_NAME`,t.`TABLE_ROWS` FROM information_schema.`PARTITIONS` t WHERE t.`TABLE_SCHEMA` = SCHEMA() AND t.`TABLE_NAME` = 't_range_test';

以下显示则成功

取消分区

ALTER TABLE t_range_test REMOVE PARTITIONING;

重新定义表分区（为现有表定义表分区）

ALTER TABLE t_range_test PARTITION BY RANGE COLUMNS ( dates ) (
    -- 第一组 小于2018-01-01的
    PARTITION p1 VALUES LESS THAN  ( '20180101' ),
    -- 大于等于2018-01-01 小于2018-02-01的
    PARTITION p2 VALUES LESS THAN  ( '20180201' ),
    PARTITION p3 VALUES LESS THAN  ( '20180301' ),
    PARTITION p4 VALUES LESS THAN  ( '20180401' ),
    PARTITION p5 VALUES LESS THAN  ( '20180501' ),
    PARTITION p6 VALUES LESS THAN  ( '20180601' ),
    PARTITION p7 VALUES LESS THAN  ( '20180701' ),
    PARTITION p8 VALUES LESS THAN  ( '20180801' ),
    PARTITION p9 VALUES LESS THAN  ( '20180901' ),
    PARTITION p10 VALUES LESS THAN ( '20181001' ),
    PARTITION p11 VALUES LESS THAN ( '20181101' ),
    PARTITION p12 VALUES LESS THAN ( '20181201' ),
    PARTITION p13 VALUES LESS THAN ( '20190101' ),
    -- 大于等于2019-01-01 小于2020-01-01的
    PARTITION p13 VALUES LESS THAN ( '20200101' ),
    -- 最后一组 大于上面的
    PARTITION p2022 VALUES LESS THAN ( MAXVALUE )
);


-- 若为现有带主键(索引)的表分区需要进行以下操作，再进行重新定义表分区操作即可

-- 移除现有主键，以及自增功能
ALTER TABLE t_test MODIFY COLUMN `id` int(11) NOT NULL COMMENT '主键ID' FIRST;
ALTER TABLE t_test DROP PRIMARY KEY;

-- 创建复合主键(主键(索引)和分区字段)
ALTER TABLE t_test ADD PRIMARY KEY (id, dates);

-- id重新设为自增主键
ALTER TABLE t_test MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID' FIRST;

OK.