Mysql数据按天分区,定期删除，及分区索引

通过SQL直接增删分区

 

分区的原因：

   1、可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。

   2、方便维护，通过删除分区来删除老的数据。

   3、分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。

 

MySQL可以建立四种分区类型的分区：

    1、RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

    2、LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。  www.jb51.net 

    3、HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

    4、KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

一般用得多的是range分区和list分区。

 

需求:

　　1.报表（消耗日志）需要按天分区

　　2.只保留一个月数据

　　3.这里是新建空表，创建分区，然后导入数据，再每天新增，删除分区

      4.如需在原表上新建分区，并每天新增删除分区的，请移步：https://www.cnblogs.com/garfieldcgf/p/10143367.html

 

方案: 1

　　1.创建一个空表，并创建30天分区

      2.通过定时任务，每天删除分区，新建分区，保持分区总量不变即可

      3.导入数据或定时生成数据即可

 

注意：

　　1.分区索引问题，全局索引未必生效，需Explain看一下

      2.新建分区索引，不会

      3.强制使用唯一索引，直接爆炸，原800万左右的数据，explain后rows 大于1.4个亿

      4.分区字段必须包含在主键中

      5.注意使用drop、truncate等操作分区后，据说全局索引会失效，就是我们建表的索引，需要我们手动

 

这里以range分区为例，如下：

 

1、新建表

CREATE TABLE `ug_stats_material_daily` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dt` bigint(20) NOT NULL COMMENT '日期，如20200512',
  `hr` int(10) NOT NULL COMMENT '小时，0-23',
  `cdt` bigint(20) NOT NULL DEFAULT '0' COMMENT '素材创建日期',
  `mid` int(10) NOT NULL COMMENT '媒体id 1-广点通 2-头条 3-快手 ',
  `pid` bigint(20) NOT NULL DEFAULT '0' COMMENT '项目ID',
  `account_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '账户ID',
  `ctype` int(10) NOT NULL DEFAULT '0' COMMENT '创意类型，0:未知，1图片，2视频，3文案',
  `material_dna` varchar(255) NOT NULL DEFAULT '',
  `material_dna_id` bigint(20) NOT NULL,
  `material_id` int(11) NOT NULL DEFAULT '0',
  `imp` int(2) unsigned NOT NULL DEFAULT '0' COMMENT '素材曝光',
  `mimp` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '素材曝光，仅快手',
  `clk` bigint(20) unsigned NOT NULL DEFAULT '0',
  `landing_clk` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '行为点击',
  `act` bigint(20) unsigned NOT NULL DEFAULT '0',
  `waken` bigint(20) unsigned NOT NULL DEFAULT '0',
  `reg` bigint(20) unsigned NOT NULL DEFAULT '0',
  `active` int(11) DEFAULT NULL COMMENT '次留',
  `pay` bigint(20) unsigned NOT NULL DEFAULT '0',
  `price` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '渠道消耗，单位分',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`dt`),
  UNIQUE KEY `dt_mid_ctype_pid_accountid_mdnaid` (`dt`,`mid`, `ctype`,`pid`, `account_id`,`material_dna_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (dt)
(
 PARTITION p_20220601 VALUES LESS THAN (20220602) ENGINE = InnoDB,
 PARTITION p_20220602 VALUES LESS THAN (20220603) ENGINE = InnoDB,
 PARTITION p_20220603 VALUES LESS THAN (20220604) ENGINE = InnoDB,
 PARTITION p_20220604 VALUES LESS THAN (20220605) ENGINE = InnoDB,
 PARTITION p_20220605 VALUES LESS THAN (20220606) ENGINE = InnoDB,
 PARTITION p_20220606 VALUES LESS THAN (20220607) ENGINE = InnoDB) */;
 
库名:km
表名：ug_stats_material_daily

 

2、查看分区

select partition_name part, partition_expression expr, partition_description descr, table_rows from  INFORMATION_SCHEMA.partitions  where TABLE_SCHEMA="km" AND TABLE_NAME="ug_stats_material_daily";

 

3、手动添加分区

ALTER TABLE km.ug_stats_material_daily ADD PARTITION (PARTITION 'p_20220607' VALUES LESS THAN (20220608));

 

4、删除分区数据（分区仍在，但会释放内存，与delete有区别）

 alter table 上述表名 truncate partition p_20220601;  

 

5、直接删除分区及数据

ALTER TABLE 表名 DROP PARTITION p_20220601;

 

6、分区的合并

下面的SQL，将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3

ALTER TABLE 表名 REORGANIZE PARTITION p201001,p201002,p201003,p201004,p201005,p201006,p201007,p201008,p201009 INTO

(

PARTITION p2010Q1 VALUES LESS THAN (201004),  

PARTITION p2010Q2 VALUES LESS THAN (201007),

PARTITION p2010Q3 VALUES LESS THAN (201010)

);

 

7、分区的拆分

下面的SQL，将p2010Q1 分区，拆分为s2009 与s2010 两个分区

ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (
 PARTITION s2009 VALUES LESS THAN (201001),
 PARTITION s2010 VALUES LESS THAN (201004)
);

 

分区索引的局限：
1，所有分区都要使用同样的引擎。
2，分区表的每一个唯一索引必须包含由分区函数引用的列。
3，mysql能避免查询所有的分区，但仍然锁定了所有分区。
4，分区函数能使用的函数和表达式有限，
5，分区不支持外键。 
6，不能使用LOAD INDEX INTO CACHE
7，分区并不能总是改善性能，要进行性能评测。
例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据：

mysql> explain partitions select * from fenqubiao where day<'2011-09-12';
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | partitions    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | fenqubiao | p_2010,p_2011 | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

 

 

 

解存储过程创建分区（建议略过）

以下为手动建立分区的存储过程demo，用于了解存储过程

-- SELECT DATE_FORMAT(DATE_SUB(curdate(),INTERVAL + 14 DAY),"p_%Y%m%d");
-- SELECT DATE_FORMAT(DATE_SUB(curdate(),INTERVAL + 12 DAY),"%Y%m%d");

-- 有一张空白表，也可以是有数据的  
DELIMITER $$
USE `mydatebase`$$
DROP PROCEDURE IF EXISTS `create_partition_today`$$
CREATE PROCEDURE `create_partition_today`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
  BEGIN
    DECLARE DAYS_ENDTIME INT;
    DECLARE PARTITIONNAME VARCHAR(16);
    SET PARTITIONNAME = DATE_FORMAT(DATE_SUB(curdate(),INTERVAL +13 DAY),"p_%Y%m%d");
    SET DAYS_ENDTIME = DATE_FORMAT(DATE_SUB(curdate(),INTERVAL +12 DAY),"%Y%m%d");
    SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                        ' PARTITION BY RANGE (dt)
    (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAYS_ENDTIME, '))');
    PREPARE STMT FROM @SQL;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
  END$$
DELIMITER ;


CALL create_partition_today('mydatebase', 'test_ug_stats_material_hourly'); -- 分区后还需处理历史数据，由于是空表，所以未处理，如有需要，参看上文：方法一

ALTER TABLE test_ug_stats_material_hourly ADD PARTITION (PARTITION p_20211115 VALUES LESS THAN (20211116));
ALTER TABLE test_ug_stats_material_hourly DROP PARTITION p_20211101;

SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name='test_ug_stats_material_hourly';

 

分区索引：

对索引进行分区有两种方法：
1) 随表对索引完成相应的分区：这也称为局部分区索引（locally partitioned index）。
每个表分区都有一个索引分区，而且只索引该表分区。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。

2)按区间对索引分区：这也称为全局分区索引（globally partitioned index）。
索引按区间分区或者按散列(10g之后)分区，一个索引分区可能指向任何（和所有）表分区。
由于全局索引只按区间或散列分区，如果希望有一个列表或组合分区索引，就必须使用局部索引。而局部索引会使用底层表相同的机制分区。

使用LOCAL关键字创建局部分区索引。
局部分区索引使用和基表相同的分区来保存索引。
如果使用一个局部索引来保证惟一性约束（PRIMARY KEY或者UNIQUE），那么分区键必须包括在约束本身中。

--创建分区表和分区索引
创建表：
create table test(
c1 int,
c2 varchar2(16),
c3 varchar2(64),
c4 int
constraint pk_ta_c1 primary key(c1)
)partition by range(c1)(
partition p1 values less than(10000000),
partition p2 values less than(20000000),
partition p3 values less than(30000000),
partition p4 values less than(maxvalue)
);
建立分区索引：
create index idx_test_c2 on test(c2) local (partition p1,partition p2,partition p3,parition p4);
或者create index idx_test_c2 on test(c2) local;

删除分区索引：https://blog.csdn.net/kaertiger/article/details/6927657