分区是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件。MySQL在5.1时添加了对水平分区的支持。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。要知道MySQL是面向OLTP的数据，它不像TIDB等其他DB。那么对于分区的使用应该非常小心，如果不清楚如何使用分区可能会对性能产生负面的影响。

　　MySQL数据库的分区是局部分区索引，一个分区中既存了数据，又放了索引。也就是说，每个区的聚集索引和非聚集索引都放在各自区的（不同的物理文件）。目前MySQL数据库还不支持全局分区。

　　无论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

目前MySQL支持一下几种类型的分区，RANGE分区，LIST分区，HASH分区，KEY分区。如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。

不使用NDB存储引擎的话，在MySQL5.6.7版本之前，一个表的最大分区数量是1024；在MySQL5.6.7版本以后，一个表的分区数量的上限是8192。这里提到的分区也包括二级分区。

  使用NDB存储引擎的话，这个上限受到MySQL集群软件的版本、数据节点的数量、还有一些其它因素的影响。        

  在创建一个有大量分区的表的时候，有可能会收到这样的错误信息：Got error … from storage engine: Out of resources when opening file。此时可以尝试调高open_files_limit的值。然而，这个还要看操作系统是否支持。此外考虑到其它的因素，在某些情况下，使用大量的（数百个）分区并不能得到更好的性能提升。

目前mysql不支持自动分区。需要手动分区。

例如：
CREATE TABLE `m_test_db`.`Order` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `partition_key` INT NOT NULL,

  `amt` DECIMAL(5) NULL,

  PRIMARY KEY (`id`, `partition_key`)) PARTITION BY RANGE(partition_key) PARTITIONS 5( PARTITION part0 VALUES LESS THAN (201901),  PARTITION part1 VALUES LESS THAN (201902),  PARTITION part2 VALUES LESS THAN (201903),  PARTITION part3 VALUES LESS THAN (201904),  PARTITION part4 VALUES LESS THAN (201905)) ;
需要写入手动写入 part0-part4 的分区名称 分区范围也需要手动数据。

如何才能自动让表分区呢？如果分区过多怎么才能让mysql自动删除过期的分区呢。

设置mysql自动分区

既然mysql 不支持自动分区，为何说自动分区呢。

Mysql 提供了手动创建分区命令

ALTER TABLE partition_test.domain_data ADD PARTITION (PARTITION 'PARTITIONNAME' VALUES LESS THAN (TO_DAYS(now())));

Mysql 提供了手动删除分区命令

alter table partition_test.domain_data drop partition 'PARTITIONNAME';

Mysql 提供了手动清空分区数据命令

alter table partition_test.domain_data truncate partition 'PARTITIONNAME';

这样我们可以使用触发器和事件相互配合完成我们的目标了。

准备

1、创建数据库

create  database  partition_test;

use partition_test

2、创建分区表

create table domain_data
(
        id bigint(11) NOT NULL AUTO_INCREMENT,
        t_domain varchar(200) DEFAULT NULL COMMENT '',
        url varchar(500) DEFAULT NULL COMMENT '',
        ip  varchar(15) DEFAULT NULL COMMENT '',
        ip_address varchar(200) DEFAULT NULL COMMENT '',
        IS_ZONE varchar(200) DEFAULT NULL COMMENT '',
        TITLE varchar(200) DEFAULT NULL COMMENT '',
        CONTENT blob DEFAULT NULL COMMENT '',
        SZ_TYPE int(5) default null COMMENT '',
        SZ_NAME varchar(200) DEFAULT NULL COMMENT '',
        SZ_PRODUCT_TYPE int(5) default null COMMENT '',
        SZ_PRODUCT_NAME varchar(200) DEFAULT NULL COMMENT '',
        create_date date not null COMMENT '',
        CHECK_STATUS int(1) default null COMMENT '',
        PRIMARY KEY (id,create_date),
        UNIQUE KEY id_UNIQUE (id,create_date)
)
ENGINE=InnoDB  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='表'

创建触发器

1、将表转化为分区表,并将历史数据归集到该分区,未来数据则按天放置

DROP PROCEDURE IF EXISTS partition_test.create_partition_today;

DELIMITER $$
$$
CREATE DEFINER=`root`@`%` PROCEDURE `partition_test`.`create_partition_today`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
     DECLARE BEGINTIME TIMESTAMP;
    DECLARE ENDTIME TIMESTAMP;
    DECLARE DAYS_ENDTIME INT;
    DECLARE PARTITIONNAME VARCHAR(16);
    SET BEGINTIME = NOW();
    SET ENDTIME = BEGINTIME + INTERVAL 1 DAY;
    SET PARTITIONNAME = DATE_FORMAT(BEGINTIME, 'p%Y%m%d');
    SET DAYS_ENDTIME = TO_DAYS(ENDTIME);
    SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                        ' PARTITION BY RANGE (to_days(create_date))
    (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAYS_ENDTIME, '))');
    PREPARE STMT FROM @SQL; 
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
END$$
DELIMITER ;


触发器 需要传输2个参数。一个数据库名称，一个表名称；分区名称自动生成  p%Y%m%d格式；

分区字段create_date。

2.按天构造分区的存储过程create_partition_by_day:

DROP PROCEDURE IF EXISTS partition_test.create_partition_by_day;

DELIMITER $$
$$
CREATE DEFINER=`root`@`%` PROCEDURE `partition_test`.`create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
	DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE BEGINTIME TIMESTAMP;
    DECLARE ENDTIME TIMESTAMP;
    DECLARE DAYS_ENDTIME INT;
    DECLARE PARTITIONNAME VARCHAR(16);
    SET BEGINTIME = NOW() + INTERVAL 1 DAY;
    SET PARTITIONNAME = DATE_FORMAT(BEGINTIME, 'p%Y%m%d');
    SET ENDTIME = BEGINTIME + INTERVAL 1 DAY;
    SET DAYS_ENDTIME = TO_DAYS(ENDTIME);
    SELECT COUNT(*)
    INTO ROWS_CNT
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0
    THEN
      SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                        ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAYS_ENDTIME, '))');
      PREPARE STMT FROM @SQL;
      EXECUTE STMT;
      DEALLOCATE PREPARE STMT;
    ELSE
      SELECT CONCAT("partition `", PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME,
                    "` already exists") AS result;
    END IF;
END $$
DELIMITER ;

3.按天清除数据的存储过程clear_partition_by_day

DROP PROCEDURE IF EXISTS partition_test.clear_partition_by_day;

DELIMITER $$
$$
CREATE DEFINER=`root`@`%` PROCEDURE `partition_test`.`clear_partition_by_day`()
BEGIN
	DECLARE NOWDAYS INT;
    DECLARE Done INT;
    DECLARE part VARCHAR(64);
    DECLARE descr INT;
    DECLARE rs CURSOR FOR SELECT
                            partition_name        part,
                            partition_description descr
                          FROM information_schema.partitions
                          WHERE
                            table_schema = IN_SCHEMANAME
                            AND table_name = IN_TABLENAME;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
    OPEN rs;
    SET NOWDAYS = TO_DAYS(NOW());
    FETCH rs into part,descr;
    while Done is null DO
      IF NOWDAYS - descr > 90 
        THEN
          select descr AS cc;
          SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                            ' DROP PARTITION ', part);
          select descr AS aa;
          SELECT @SQL AS result;
          select descr AS bb;
          PREPARE STMT FROM @SQL;
          EXECUTE STMT;
          DEALLOCATE PREPARE STMT;
        END IF;
      FETCH rs into part,descr;
    end WHILE;
    CLOSE rs;
END$$
DELIMITER ;

注意:以上过程有可能报错,这里记录两个错误:

1.分区字段必须包含主键:

这里采用的是事件字段作为分区字段,当然不可能是主键,所以再mysql中会报错,查阅资料大多说是因为分区表中不能保证数据唯一,需要将分区健纳入主键才可,不清楚原理.如果不愿意这样做也可以考虑,删除已有主键,无主键的表可以用任意字段作为分区字段

2.时间字段不允许作为分区字段:

这是因为 DATETIME 会受到时间区的影响,mysql里面分区的时间字段需要用CURRENT_TIMESTAMP

ALTER TABLE partition_test MODIFY create_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

4、继续往下,打开事件变量,这里直接修改

SET GLOBAL event_scheduler = 'ON'; #打开
SET GLOBAL event_scheduler=ON

重启后会被重置,如果需要永久修改,在my.cnf（mysql配置文件）中[mysqld]部分中添加下面内容，重启MYSQL

5、调用存储过程创建当天分区:

CALL create_partition_today('partition_test','domain_data');

6、创建生成分区事件

#定时 没小时执行一次。
USE partition_test;
ALTER EVENT e_part_manage
ON SCHEDULE EVERY 1 HOUR
STARTS '2022-01-14 16:00:00.000'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions' 
DO BEGIN 
		CALL create_partition_today('partition_test','domain_data');		
END;

#定时每天执行一次
USE partition_test;
ALTER EVENT clear_data
ON SCHEDULE EVERY 1 DAY
STARTS '2022-01-14 00:00:00.000'
ON COMPLETION PRESERVE
ENABLE  
COMMENT 'clearing data'
DO BEGIN 
	CALL clear_partition_by_day('partition_test','domain_data');
END;

如果想两个表同时使用 需要修改事件。

#定时 没小时执行一次。
USE partition_test;
ALTER EVENT e_part_manage
ON SCHEDULE EVERY 1 HOUR
STARTS '2022-01-14 16:00:00.000'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions' 
DO BEGIN 
		CALL create_partition_today('partition_test','domain_data');	
		CALL create_partition_today('partition_test','domain_data1');
		CALL create_partition_today('partition_test','domain_data2');
		CALL create_partition_today('partition_test','domain_data3');
		CALL create_partition_today('partition_test','domain_data4');	
END;

#定时每天执行一次
USE partition_test;
ALTER EVENT clear_data
ON SCHEDULE EVERY 1 DAY
STARTS '2022-01-14 00:00:00.000'
ON COMPLETION PRESERVE
ENABLE  
COMMENT 'clearing data'
DO BEGIN 
	CALL clear_partition_by_day('partition_test','domain_data');
    CALL clear_partition_by_day('partition_test','domain_data1');
	CALL clear_partition_by_day('partition_test','domain_data2');
	CALL clear_partition_by_day('partition_test','domain_data3');
	CALL clear_partition_by_day('partition_test','domain_data4');
END;


7、查看事件和事件开关
show events;
SHOW VARIABLES LIKE 'event_scheduler';

# 数据库开发 # 数据库架构 # java
Logo
魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

cover

【计算机视觉】Pixel逐像素分类&Mask掩码分类理解摘要

avatar 魔乐社区

计算机视觉（opencv）实战三十二——CascadeClassifier 人脸微笑检测（摄像头）

本文从原理到实现，详细介绍了基于 OpenCV Haar 分类器的人脸与微笑检测：讲解了 Haar 特征和级联检测原理。对代码逐行拆解并解释参数含义。画出完整流程图，帮助理解执行过程。给出了常见问题和优化建议，甚至扩展到深度学习方法。这种方法简单、轻量、实时性好，非常适合入门和小型应用项目。但如果需要更高准确率和更强鲁棒性，建议使用深度学习检测器替代 Haar 分类器。

avatar 魔乐社区
cover

小杰机器学习（seven）——贝叶斯分类

avatar 魔乐社区