关于mysql:第38期MySQL-时间类分区具体实现

44次阅读

共计 8632 个字符，预计需要花费 22 分钟才能阅读完成。

实用分区或者说分表最多的场景仍然是针对工夫字段做拆分，这节咱们具体讲讲如何更好的基于工夫字段来拆分。别离依照年、月、日几个维度的实现办法以及一些细节注意事项。

第一，以年为维度做拆分

日期字段拆分粒度的抉择跟业务检索申请密切相关。比方保留 10 年数据，每次查问基于某个具体年份做为过滤条件，那依照年拆分必定最好。例如上面 SQL：

select * from ytt_pt1 where log_date >='2018-01-01' and log_date < '2019-01-01';

那咱们来看下依照年独自拆分的理论例子：表 ytt_pt1，蕴含 1000W 条记录，以年为粒度建设分区表。

mysql> create table ytt_pt1(id bigint, log_date date);
Query OK, 0 rows affected (0.18 sec)

mysql> insert into ytt_pt1 select id,log_date from ytt_p1 limit 10000000;
Query OK, 10000000 rows affected (3 min 49.53 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE ytt_pt1 PARTITION BY RANGE (year(log_date))
    -> (-> PARTITION p0001 VALUES LESS THAN (2012),
    -> PARTITION p0002 VALUES LESS THAN (2013),
    -> PARTITION p0003 VALUES LESS THAN (2014),
    -> PARTITION p0004 VALUES LESS THAN (2015),
    -> PARTITION p0005 VALUES LESS THAN (2016),
    -> PARTITION p0006 VALUES LESS THAN (2017),
    -> PARTITION p0007 VALUES LESS THAN (2018),
    -> PARTITION p0008 VALUES LESS THAN (2019),
    -> PARTITION p0009 VALUES LESS THAN (2020),
    -> PARTITION p0010 VALUES LESS THAN (2021),
    -> PARTITION p_max VALUES LESS THAN (maxvalue)
    -> );
Query OK, 10000000 rows affected (2 min 33.31 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

看下按年为粒度的查问成果：以下 SQL 间接走分区 p0008，查问工夫 0.91 秒，这个工夫不算短，前期能够减少过滤条件来缩小查问工夫。

mysql> select count(*) from ytt_pt1 where log_date >='2018-01-01' and log_date < '2019-01-01';
+----------+
| count(*) |
+----------+
|  1000204 |
+----------+
1 row in set (0.91 sec)

mysql> explain  select count(*) from ytt_pt1 where log_date >='2018-01-01' and log_date < '2019-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ytt_pt1
   partitions: p0008
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 998002
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

须要留神：查问只能基于字段来间接过滤，如果基于字段表达式来过滤，MySQL 不确定走哪个分区，会扫描所有分区，解决办法和单表查问一样。比方语句：

select count(*) from ytt_pt1 where year(log_date) = '2018' ;

看下执行状况: MySQL 扫描所有分区，查问执行工夫 9 秒多。

mysql> select count(*) from ytt_pt1 where year(log_date) = '2018' ;
+----------+
| count(*) |
+----------+
|  1000204 |
+----------+
1 row in set (9.19 sec)

mysql> explain select count(*) from ytt_pt1 where year(log_date) = '2018' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ytt_pt1
   partitions: p0001,p0002,p0003,p0004,p0005,p0006,p0007,p0008,p0009,p0010,p_max
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9982648
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

如果非保持这种写法，能够给优化器一个提醒，具体到指定分区去检索数据，或者是基于字段表达式建一个虚构列：

mysql> select count(*) from ytt_pt1 partition(p0008) where year(log_date) = '2018' ;
+----------+
| count(*) |
+----------+
|  1000204 |
+----------+
1 row in set (0.84 sec)

如果查问依照月作为维度过滤比拟频繁，那必定是依照月来拆最好；比方须要检索 2020 年当月的某些记录来做后续数据处理，大抵 SQL 如下：

select * from ytt_pt1_按月拆分表 where log_date in ('2020-01-01'，'2020-01-02'，...)
第二，以月为维度做拆分
依照月来拆分，有以下两种写法：

第一种：间接依照月来拆 12 个分区: 上面表 ytt_pt1_month1 分区类型为 LIST，基于函数 month 间接计算。

mysql> show create table ytt_pt1_month1\G
*************************** 1. row ***************************
       Table: ytt_pt1_month1
Create Table: CREATE TABLE `ytt_pt1_month1` (
  `id` bigint DEFAULT NULL,
  `log_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (month(`log_date`))
(PARTITION p0001 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p0002 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p0003 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p0004 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p0005 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p0006 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p0007 VALUES IN (7) ENGINE = InnoDB,
 PARTITION p0008 VALUES IN (8) ENGINE = InnoDB,
 PARTITION p0009 VALUES IN (9) ENGINE = InnoDB,
 PARTITION p0010 VALUES IN (10) ENGINE = InnoDB,
 PARTITION p0011 VALUES IN (11) ENGINE = InnoDB,
 PARTITION p0012 VALUES IN (12) ENGINE = InnoDB) */
1 row in set (0.00 sec)

比方要查问 2020 年前半个月的记录：查问限定在分区 p0001 里，然而工夫不太现实，得 0.66 秒。

mysql> select count(*) from ytt_pt1_month1 where log_date in ('2020-01-01','2020-01-02','2020-01-03','2020-01-04','2020-01-05','2020-01-06','2020-01-07','2020-01-08','2020-01-09','2020-01-10','2020-01-11','2020-01-12','2020-01-13','2020-01-14','2020-01-15');
+----------+
| count(*) |
+----------+
|    41540 |
+----------+
1 row in set (0.66 sec)

mysql> explain select count(*) from ytt_pt1_month1 where log_date in ('2020-01-01','2020-01-02','2020-01-03','2020-01-04','2020-01-05','2020-01-06','2020-01-07','2020-01-08','2020-01-09','2020-01-10','2020-01-11','2020-01-12','2020-01-13','2020-01-14','2020-01-15')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ytt_pt1_month1
   partitions: p0001
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 848224
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

第二种：对于每年的数据，独自划分 12 个分区，也就是依照年月联结维度来分区，一共有 144 个分区，每个分区对应具体某一年某一月数据。

增加这个分区略微麻烦些，克隆表 ytt_pt1_month1 为 ytt_pt1_month2，这里写个存储过程来增加分区信息：

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_add_partition_ytt_pt1_month2`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_add_partition_ytt_pt1_month2`()
BEGIN
    DECLARE i,j INT UNSIGNED DEFAULT 1;
    DECLARE v_tmp_date DATE;
    SET @stmt = '';
    SET @stmt_begin = 'ALTER TABLE ytt_pt1_month2 PARTITION BY RANGE COLUMNS (log_date)(';
        SET i = 2010;        
        WHILE i <= 2020 DO
          SET j = 1;
          WHILE j <= 12 DO
            SET v_tmp_date = CONCAT(i,'-01-01');
            SET @stmt = CONCAT(@stmt,'PARTITION p',i,'_',LPAD(j,2,"0"),'VALUES LESS THAN (''',DATE_ADD(v_tmp_date,INTERVAL j MONTH),'''),');
            SET j = j + 1;
          END WHILE;
          SET i = i + 1;
        END WHILE;    
    SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
        SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
        PREPARE s1 FROM @stmt;
        EXECUTE s1;
        DROP PREPARE s1;

        SET @stmt = NULL;
        SET @stmt_begin = NULL;
        SET @stmt_end = NULL;    
    END$$

DELIMITER ;

mysql> call sp_add_partition_ytt_pt1_month2;
Query OK, 0 rows affected (2 min 20.48 sec)

后果相似这样：

PARTITION p2010_01 VALUES LESS THAN ('2010-02-01') ENGINE = InnoDB,
 ...
 PARTITION p2010_12 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
 PARTITION p2011_01 VALUES LESS THAN ('2011-02-01') ENGINE = InnoDB,
...
 PARTITION p2011_12 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
 ...
 PARTITION p2020_12 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB,
 PARTITION p_max VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

加好分区后，来察看下方才那个获取 2020 年前半个月记录的查问：

mysql> select count(*) from ytt_pt1_month2 where log_date in ('2020-01-01','2020-01-02','2020-01-03','2020-01-04','2020-01-05','2020-01-06','2020-01-07','2020-01-08','2020-01-09','2020-01-10','2020-01-11','2020-01-12','2020-01-13','2020-01-14','2020-01-15');
+----------+
| count(*) |
+----------+
|    41540 |
+----------+
1 row in set (0.06 sec)

mysql> explain   select count(*) from ytt_pt1_month2 where log_date in ('2020-01-01','2020-01-02','2020-01-03','2020-01-04','2020-01-05','2020-01-06','2020-01-07','2020-01-08','2020-01-09','2020-01-10','2020-01-11','2020-01-12','2020-01-13','2020-01-14','2020-01-15')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ytt_pt1_month2
   partitions: p2020_01
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 85498
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

查问工夫为 0.06 秒，比第一种拆分办法要快 10 倍。

那是不是说依照年月联结来分区肯定比依照单月来分区更加优化？也不肯定，如果查问语句过滤条件蕴含分区的固定月，比方每年的 12 月份都要查，这时候间接按月来分区必定要优于依照年月来分区。

第三，以天为维度做拆分

依照天为维度过滤比拟频繁的查问，以天来拆分最好。比方只想查问 2020 年 1 月 1 日当天的数据，大抵 SQL 如下：

select * from ytt_pt1 where log_date = '2020-01-01'

相似依照年月联结维度，写个脚本或者存储过程来增加分区，这里惟一要留神的一点就是 MySQL 分区表数量有限度，最大为 8192 个，所以如果依照天来分区，寄存 10 年数据，分区数量为 3650 个，也在限度之内。

批改下之前的存储过程，代码如下：

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_add_partition_ytt_pt1_day`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_add_partition_ytt_pt1_day`(
IN f_year_start YEAR,
IN f_year_end YEAR
)
BEGIN
    DECLARE v_days INT UNSIGNED DEFAULT 365;
    DECLARE v_year DATE DEFAULT '2010-01-01';
    DECLARE v_partition_name VARCHAR(64) DEFAULT '';
    DECLARE v_log_date DATE;
    DECLARE i,j INT UNSIGNED DEFAULT 1;
    SET @stmt = '';
    SET @stmt_begin = 'ALTER TABLE ytt_pt1_day PARTITION BY RANGE COLUMNS (log_date)(';
    SET i = f_year_start;
    WHILE i <= f_year_end DO 
      SET v_year = CONCAT(i,'-01-01');
      SET v_days = DATEDIFF(DATE_ADD(v_year,INTERVAL 1 YEAR),v_year);            
      SET j = 1;
      WHILE j <= v_days DO
        SET v_log_date = DATE_ADD(v_year,INTERVAL j DAY);
       SET v_partition_name = CONCAT('p',i,'_',LPAD(j,3,'0'));
       SET @stmt = CONCAT(@stmt,'PARTITION',v_partition_name,'VALUES LESS THAN (''',v_log_date,'''),');
       SET j = j + 1;        
      END WHILE;
      SET i = i + 1;    
    END WHILE;
    SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue))';
    SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    DROP PREPARE s1;
    SELECT NULL,NULL,NULL INTO @stmt,@stmt_begin,@stmt_end;
END$$

DELIMITER ;

mysql> CALL sp_add_partition_ytt_pt1_day('2010','2020');
Query OK, 1 row affected (14 min 13.69 sec)

接下来，以天来查问工夫肯定是最短的，只有 0.01 秒。

mysql> select count(*) from ytt_pt1_day where log_date = '2020-01-01';
+----------+
| count(*) |
+----------+
|     2675 |
+----------+
1 row in set (0.01 sec)

此时这样的查问要是基于年或者月性能必定不是最优。

mysql> select count(*) from ytt_pt1 where log_date = '2020-01-01';
+----------+
| count(*) |
+----------+
|     2675 |
+----------+
1 row in set (0.68 sec)

mysql> select count(*) from ytt_pt1_month1 where log_date = '2020-01-01';
+----------+
| count(*) |
+----------+
|     2675 |
+----------+
1 row in set (0.87 sec)

mysql> select count(*) from ytt_pt1_month2 where log_date = '2020-01-01';
+----------+
| count(*) |
+----------+
|     2675 |
+----------+
1 row in set (0.09 sec)

能够看到，此类查问基于其余办法分区工夫显著比按天来的长。

总结下：

本篇次要讲述日常工作中最罕用的数据拆分形式：按工夫来拆分。工夫字段又常依照年，月，日三种不同的维度来具体实现，并且具体讲述了具体的实现形式以及实用场景。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完
mysql
发表至： mysql
2021-12-27
 0
关于mysql:MySQL-用户管理之修改用户锁定用户
关于mysql:Java8-lambda表达式及自定义函数式接口入门
关于mysql:MySQL写sql的21个好习惯让你的工作效率翻倍
Linux和MySQL添加用户和授予权限