mysql 分區(qū)實(shí)踐之按月份分區(qū)，定時(shí)增加分區(qū)和刪除分區(qū)

按月份分區(qū)，這樣再使用分區(qū)字段時(shí)間來查詢數(shù)據(jù)將會(huì)很快，因?yàn)檫@樣只需要掃描指定的分區(qū)。

1、創(chuàng)建表，并使用RANGE COLUMNS分區(qū)。按創(chuàng)建時(shí)間create_time字段分區(qū);分區(qū)名使用p0、p1、p2、p3 .. 的形式；create_time字段小于2019-01-01的數(shù)據(jù)將進(jìn)入p0 分區(qū)，依次類推。。

CREATE TABLE "box_fenqu" (
  "id" bigint(36) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  "create_by" varchar(50) DEFAULT NULL COMMENT '創(chuàng)建人',
  "create_time" datetime NOT NULL COMMENT '創(chuàng)建日期',
  "update_by" varchar(50) DEFAULT NULL COMMENT '更新人',
  "update_time" datetime DEFAULT NULL COMMENT '更新日期',
  "sys_org_code" varchar(64) DEFAULT NULL COMMENT '所屬部門',
  "status" int(10) DEFAULT '0' COMMENT '狀態(tài)',
  "number" varchar(32) DEFAULT NULL COMMENT '編號(hào)',
  "zi_number" varchar(32) DEFAULT NULL COMMENT '自編號(hào)',
  "house_address" varchar(32) DEFAULT NULL COMMENT '倉(cāng)庫地址',
  "sb_number" varchar(32) DEFAULT NULL COMMENT '設(shè)備id',
  "point_id" varchar(32) DEFAULT NULL COMMENT '投放點(diǎn)id',
  "point" varchar(32) DEFAULT NULL COMMENT '投放點(diǎn)',
  "confirm" int(32) DEFAULT '0' COMMENT '商戶/企業(yè)用戶確認(rèn)入庫，默認(rèn)為0（未確認(rèn)）1是已確認(rèn)',
  "last_point" varchar(32) DEFAULT NULL COMMENT '最近一次投放點(diǎn)名',
  PRIMARY KEY ("id","create_time") USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2120001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p0 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2019-02-01') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('2019-03-01') ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN ('2019-04-01') ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN ('2019-05-01') ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN ('2019-06-01') ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN ('2019-07-01') ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN ('2019-08-01') ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN ('2019-09-01') ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN ('2019-10-01') ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN ('2019-11-01') ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN ('2019-12-01') ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

2、創(chuàng)建存儲(chǔ)過程proc_create_partition，用它來直接對(duì)box_fenqu表進(jìn)行分區(qū)。其實(shí)就是將分區(qū)名加1，如p2加1就是p3;指定的日期范圍就是往后面進(jìn)一個(gè)月，保證最新的分區(qū)總是MAXVALUES的 ,如20200401就變成20200401和20200501，其中20200401指定范圍，20200501為MAXVALUES

CREATE DEFINER="root"@"%" PROCEDURE "proc_create_partition"(in_tbname VARCHAR(64))
BEGIN

SELECT DATABASE() INTO @dbname;

SET @tbname = in_tbname;

#查詢表的最近一次分區(qū)的名字，這里按自然數(shù)遞增的。比如0,1,2,4。去掉 p
SELECT
    REPLACE (partition_name, 'p', '') INTO @PMAX
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = @dbname
AND table_name = @tbname
ORDER BY
    partition_ordinal_position DESC
LIMIT 1;

#查詢表的最近一次分區(qū)的指定時(shí)間,比如最近時(shí)間的分區(qū)時(shí) 2020.04.01
SELECT
REPLACE(partition_description, '\'', '') INTO @DNAME
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = @dbname
AND table_name = @tbname
ORDER BY
    partition_ordinal_position DESC
LIMIT 1, 1;


SET @t=CONCAT('alter table `',@dbname,'`.',@tbname,' reorganize partition p',@PMAX,
                          ' into(partition p',@PMAX,' values less than (''',date(DATE_ADD(@DNAME,INTERVAL 1 MONTH)),'''),',
                            'partition p',@PMAX+1,' values less than MAXVALUE)');

SELECT @t;
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

COMMIT;
END

這樣傳入box_fenqu表名即可進(jìn)行增加分區(qū)了： CALL proc_create_partition('box_fenqu');

3、創(chuàng)建mysql事件，定時(shí)調(diào)度分區(qū)；下面的事件到的意思就是從2020-05-03 00:00:00開始，每過一個(gè)月就執(zhí)行下CALL proc_create_partition('box_fenqu')

CREATE DEFINER=`root`@`%` EVENT `e_create_partition` 
ON SCHEDULE EVERY 1 MONTH STARTS '2020-05-03 00:00:00' 
ON COMPLETION PRESERVE DISABLE 
DO CALL proc_create_partition('box_fenqu')

4、如果有多個(gè)表需要都需要定時(shí)的動(dòng)態(tài)增加分區(qū)的話，就可以再寫個(gè)存儲(chǔ)如下，這個(gè)存儲(chǔ)過程將對(duì)當(dāng)前庫下所有分區(qū)表都進(jìn)行遍歷，然后增加分區(qū)

CREATE DEFINER="root"@"%" PROCEDURE "proc_create_partition_all"()
BEGIN

  DECLARE tbname varchar(32);
  DECLARE tmpSql varchar(256);
  DECLARE done INT DEFAULT FALSE ;

#查詢已手動(dòng)分區(qū)的表
    DECLARE part_cursor CURSOR FOR (SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = DATABASE() AND partition_expression IS NOT NULL AND table_name NOT LIKE '%bak');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

#循環(huán)對(duì)表添加分區(qū)
    OPEN part_cursor;
        myLoop: LOOP
            FETCH part_cursor INTO tbname;
            IF done THEN 
                LEAVE myLoop;
            END IF;
            #調(diào)用分區(qū)存儲(chǔ)過程
            CALL proc_create_partition(tbname);

            COMMIT;
        END LOOP myLoop;
    CLOSE part_cursor;
    
END

5、mysql允許創(chuàng)建的分區(qū)數(shù)量有限，因此我們可以根據(jù)業(yè)務(wù)的情況。定期刪除已經(jīng)不需要的分區(qū)

6、查詢有數(shù)據(jù)的最新日期/月份

select REPLACE(partition_description, '\'', '') date from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='box_fenqu' AND table_rows != 0
ORDER BY REPLACE(partition_description, '\'', '') desc limit 1

查詢有數(shù)據(jù)的數(shù)據(jù)條數(shù)

select table_rows from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='box_fenqu' AND table_rows != 0
ORDER BY REPLACE(partition_description, '\'', '') desc limit 1

7、插入數(shù)據(jù)測(cè)試，編寫存儲(chǔ)過程如下

CREATE DEFINER="root"@"localhost" PROCEDURE "insert_box_fq"(in max_num int(10),in sDate datetime,in eDate datetime)
begin
 declare i int default 0;
 /*把a(bǔ)utocommit設(shè)置成0*/
 set autocommit= 0;
 repeat
 set i=i+1;
  INSERT INTO `test`.`box_fenqu`(`create_by`, `create_time`, `update_by`, `update_time`, `sys_org_code`, `status`, `number`, `zi_number`, `house_address`, `sb_number`, `point_id`, `point`, `confirm`, `last_point`) VALUES (rand_string(3), getDateTime(sDate,eDate), rand_string(3), now(), 'A03', 0, rand_string(3), 'A001', '倉(cāng)庫1', rand_string(3), rand_string(3), NULL, 1, rand_string(3));


 until i=max_num end repeat;
commit;
end

插入10萬條 call insert_box_fq(100000,'2019-01-01 00:00:00',now())

8、查詢各個(gè)分區(qū)數(shù)據(jù)情況

SELECT
    PARTITION_NAME AS '分區(qū)名',
    TABLE_ROWS AS '記錄數(shù)' ,
        PARTITION_DESCRIPTION '范圍'
FROM
    information_schema.PARTITIONS 
WHERE
    table_schema = 'test' 
    AND table_name = 'box_fenqu';

思考分區(qū)結(jié)合分頁

既然是按日期分區(qū)，那么我們?cè)诜猪摬樵儠r(shí)就可以使用日期。如下我查詢最新的10條數(shù)據(jù)。效率非常低，因?yàn)樾枰獟呙杷械姆謪^(qū)。但是事實(shí)上我們只需要10條而已，只需要在最新的分區(qū)中查找。若最新的分區(qū)記錄數(shù)大于等于10那么就需要前移動(dòng)一個(gè)月份，另外掃描一個(gè)分區(qū)。依次類推。我想這樣應(yīng)該查詢效率很快了吧

SELECT *  FROM box_fenqu ORDER BY create_time DESC LIMIT 10

這樣需要寫一個(gè)存儲(chǔ)過程來查詢，將limit 掃描的分區(qū)只鎖定在需要的分區(qū)里

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容