通过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