mysql自动建表分区

落日映苍穹つ 2022-06-06 22:55 200阅读 0赞

\*\*摘要：\*\*数据太多，就得建分区，查询速度就快，我就提醒一下，建分区的时候最好建的有规律一点，尽量避免查询的时候跨多个分区查询，这样就降低了效率，还有分区建好之后，你表中那个时间的字段不能超过分区那里的显示的时间

1.新建表

CREATE TABLE `test_partition` (
      `name` varchar(20) DEFAULT NULL,
      `time_test` datetime NOT NULL
    )

注意：我这种方式前提是这张已存在分区，我们先新建2个分区

alter table test_partition partition by range (to_days(time_test)) (
    partition p20170902 values less than (to_days("2017-09-02")),
    partition p20170909 values less than (to_days("2017-09-09"))
    );

2.新建表分区的存储过程

DELIMITER $$
    
    USE `test`$$
    
    DROP PROCEDURE IF EXISTS `create_partition_test`$$
    /*这里复制的时候注意自己的登录用户*/
    CREATE DEFINER=`root`@`localhost` PROCEDURE `create_partition_test`(IN tableName VARCHAR(20),IN interval_ INT)
    BEGIN
    /* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的 */
        DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
        START TRANSACTION;
      
     /* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
        SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS 
        WHERE table_name=tableName ORDER BY partition_ordinal_position DESC LIMIT 1;
          SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL interval_ DAY))+0;
     /* 修改表，在最大分区的后面增加一个分区，时间范围加 interval_ 天 */
         SET @s1=CONCAT('ALTER TABLE ' ,tableName ,' ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
        /* 输出查看增加分区语句*/
         SELECT @s1;
       PREPARE stmt2 FROM @s1;
         EXECUTE stmt2;
        DEALLOCATE PREPARE stmt2;
     /* 提交 */
         COMMIT ;
      END$$
    
    DELIMITER ;

3.调用存储过程，第一个参数写表名，第二个是分区之间的天数，我这里是7天  
自己写好定时器调用即可

call  create_partition_test('test_partition',7);

4.查询某个表的所有分区

SELECT
      partition_name part,
      partition_expression expr,
      partition_description descr,
      FROM_DAYS(partition_description) lessthan_sendtime,
      table_rows
    FROM
      INFORMATION_SCHEMA.partitions
    WHERE
      TABLE_SCHEMA = SCHEMA() AND TABLE_NAME='test_partition';

效果如下图

![这里写图片描述][format_png]

5.删除某一个分区，包括里面的数据

ALTER TABLE test_partition DROP PARTITION p20170926;

6.查询某个表分区下的数据

SELECT * FROM test_partition PARTITION (p20170909);

说明：存储过程是参考网上自己改的，亲测！！！！！！！发现问题了，望留

[format_png]: /images/20220607/eb284d7756cc4ba6b974598ad87a9ca2.png