mysql表分区（partition）

一、为什么不用视图（view）视图的优点：1.第一个显著优点就是它简化了操作。此时我们完全不用关心视图是怎么处理数据的，我们只需要知道如何使用这个结果集即可，视图相当于一个中间层。2.第二个显著优点就是它更加安全。比如我们可以让用户有权去访问某个视图，但是不能访问原表，这样就可以起到保护原表中某些数据的作用。3.我们之后会接触到管理权限，权限是无法细致到某一个列的，通过视图，则很容易实现。4.第三

rakunjo

6672人浏览 · 2022-03-14 11:03:56

rakunjo · 2022-03-14 11:03:56 发布

6、指定用来分区的字段，必须是主键或者唯一索引，否则会报错。

7、删除分区（drop）会删除该分区的数据，要特别小心

8、当分区表有最大分区的时候，不能直接添加分区

9、当删除分区表分区的时候，drop 和truncate都会导致global索引失效，需要注意。

参考：MySql从一窍不通到入门（五）Sharding：分表、分库、分片和分区_Invoker's Tower-CSDN博客

一、为什么不用视图（view）

视图的优点：

1.第一个显著优点就是它简化了操作。此时我们完全不用关心视图是怎么处理数据的，我们只需要知道如何使用这个结果集即可，视图相当于一个中间层。

2.第二个显著优点就是它更加安全。比如我们可以让用户有权去访问某个视图，但是不能访问原表，这样就可以起到保护原表中某些数据的作用。

3.我们之后会接触到管理权限，权限是无法细致到某一个列的，通过视图，则很容易实现。

4.第三个显著优点就是降低耦合。假如我们以后要修改原表的结构，那么我们可以通过修改视图的定义即可，而不用修改应用程序，对访问者是不会造成影响的，一般来说，这样代价会更小。

视图的缺点：

1.性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。

2.表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

二、为什么不用merge分表

转载：Mysql通过Merge引擎进行分表 - 南哥的天下 - 博客园

参考：
https://www.cnblogs.com/easonscx/p/12598092.html

Mysql各种引擎原理实战对比_dhmkjv0619的博客-CSDN博客

MERGE分表的优点：

1. 适用于存储日志数据。例如，可以将不同月份的数据存入不同的表，然后使用myisampack工具压缩数据，最后通过一张MERGE表来查询这些数据。

2. 可以获得更快的速度。可以根据某种指标，将一张只读的大表分割成若干张小表，然后将这些小表分别放在不同的磁盘上存储。当需要读取数据时，MERGE表可以将这些小表的数据组织起来，就好像使用先前的大表一样，但是速度会快很多。

3. 可以提高搜索效率。可以根据某种指标将一张只读的大数据表分割为若干个小表，然后根据不同的查询维度，可以得到若干种小表的组合，然后再为这些组合分别创建不同的MERGE表。例如，有一张只读的大数据表T，分割为T1、T2、T3、T4，共4张小表，有两种查询维度A和B，A可以得到小表组合T1、T2和T3，B可以得到小表组合T2、T3和T4，分别为A和B创建两个MERGE表，也就是M1和M2，这两个MERGE表分别关联的小表是存在交叠的。

4. 可以更加有效的修复表。修复单个的小表要比修复大数据表更加容易。

5. 多个子表映射至一个总表的速度极快。因为MERGE表本身不会存储和维护任何索引，索引都是由各个关联的子表存储和维护的，所以创建和重新映射MERGE表的速度非常快。

6. 不受操作系统的文件大小限制。单个表会受到文件大小的限制，但是拆分成多个表，则可以无限扩容。

7. MERGE表还可以用来给单个表创建别名，并且几乎不会影响性能。

MERGE分表的问题：

1. 总表（MERGE表）必须使用MRG_MyISAM存储引擎，子表必须使用MyISAM存储引擎，不可避免会受到MyISAM存储引擎的限制。

2. MERGE表不能使用某些MyISAM特性。例如，虽然可以为子表创建全文索引，但是却不能使用全文索引，通过MERGE表查询数据。

3. MERGE表会使用更多的文件描述符。如果有10个客户端使用1张MERGE表，那么就需要消耗(10×10)+10个文件描述符（其中，10个客户端分别有10个数据文件描述符，并且会共享使用10个索引文件描述符）。

4. 若使用ALTER TABLE语句修改总表的存储引擎，那么会立即丢失总表和子表的映射关系，并且会将所有子表的数据拷贝至修改后的新表。

5. 总表和子表的主键都不能使用自动增长（auto increment）。

6. 子表之间不能保证唯一键约束，只能保证单个子表内部的唯一性约束。

7. 由于不能保证唯一键约束，导致REPLACE语句的行为会不可预期，INSERT ... ON DUPLICATE KEY UPDATE语句也有类似问题。因此，只能使用路由策略，对子表使用这些语句，而不能对总表使用。

8. 子表不支持分区（Partition）。

9. 当正在使用总表时，不能对任何子表执行ANALYZE TABLE、REPAIR TABLE、OPTIMIZE TABLE、ALTER TABLE、DROP TABLE、DELETE或TRUNCATE TABLE语句，否则会导致不可预期的结果。

10. 总表和子表的表结构必须完全一致。

11. 总表可以映射的所有子表的总行数上限为 2的64次方行。

12. 不支持INSERT DELAYED语句。

补充：

13. MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。正是由于MyISAM引擎的缘故，即使MySQL支持事务已经很长时间了，在很多人的概念中MySQL还是非事务型数据库。

三、水平分区的模式：

转载：深入解析MySQL分区（Partition）功能 - 龙福 - 博客园

Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。
Hash（哈希） – 这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。
Key（键值） – Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。
Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

四、表分区的注意

转载：数据表分区策略及实现（一）_云水之路的专栏-CSDN博客

1、引擎的统一

在对同一个表进行分区时，必须保证数据表的引擎相同，比如：不能对一个分区的表为InnoDB，而另一个分区的引擎为MySIAM。

2、分区关联性

在对数据表分区时，不能只对数据进行分区，需要连同其对应的索引等属性一同分区动作，某种程度上可以保持数据属性的完整。

3、分区的级别

对表进行分区之后，如果某个分区中的数据量依然很大或是增长迅速，那么你同样可以再进行子分区操作，将该数据再分区到其它分区中。另外，如果在一个分区中使用了子分区，那么其它的子分区也必须定义。

4、LIST分区

LIST分区没有类似如“VALUESLESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

5、Linear线性

分区策略KEY和HASH都支持使用线性LINEAR的算法，也就是分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

补充：Range模式

6、指定用来分区的字段，必须是主键或者唯一索引，否则会报错。

7、删除分区（drop）会删除该分区的数据，要特别小心

8、当分区表有最大分区的时候，不能直接添加分区

需要用到split partition（但是mysql好像没有split命令）

9、当删除分区表分区的时候，drop 和truncate都会导致global索引失效，需要注意。

可以加参数 update global indexes 在11g的时候，这个参数会影响数据库性能，所以我们一般都是之后手动rebuild索引。但是在12c及以后，这个参数更加智能，会把工作安排到数据库空闲状态进行，但是我们一般还是选择把删除分区这种操作在空闲时候执行，然后手动重建索引。对于本地索引，删除分区表的时候不会影响到本地索引的状态。（不知道11g、12c什么意思。没有实际验证）

五、分区的管理

转载：深入解析MySQL分区（Partition）功能 - 龙福 - 博客园

1、新增分区

#新增一个RANGE分区
ALTER TABLE mytest ADD PARTITION (PARTITION p20220313 VALUES LESS THAN (TO_DAYS ('2022-03-13')));
#新增默认分区

ALTER TABLE mytest ADD PARTITION (PARTITION pmax VALUES LESS THAN (maxvalue))

转载：mysql 分区_Mysql分区表maxvalue分区重组_weixin_39535125的博客-CSDN博客

mysql在使用了maxvalue作为最后分区以后，是无法直接创建新分区的，他认为一旦使用了maxvalue就是最后一个分区了。还好我们还有一个命令REORGANIZE可以将最后一个分区进行重组，从而达到创建新分区的目的。

ALTER TABLE og_register_log REORGANIZE PARTITION pcatchall INTO ( PARTITION p202011 VALUES less than ( 202012 ), PARTITION pcatchall VALUES LESS THAN MAXVALUE ENGINE = MyISAM )

我们将最后一个分区重组以后，pcatchall就变成2个分区了。并且原先落到pcatchall分区的数据也会重新分配到新的p202011分区。

2、重建分区

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));  #将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

3、删除分区

ALTER TABLE users DROP PARTITION p0; #删除分区 p0

4、给已有的表加上分区

alter table results partition by RANGE (month(ttime)) 
(
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2) , 
PARTITION p2 VALUES LESS THAN (3) ,
PARTITION p3 VALUES LESS THAN (4) , 
PARTITION p4 VALUES LESS THAN (5) ,
PARTITION p5 VALUES LESS THAN (6) , 
PARTITION p6 VALUES LESS THAN (7) ,
PARTITION p7 VALUES LESS THAN (8) , 
PARTITION p8 VALUES LESS THAN (9) ,
PARTITION p9 VALUES LESS THAN (10) , 
PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN (13) 
);

5、新建分区

CREATE TABLE `mytest` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
PRIMARY KEY (`id`,`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci ROW_FORMAT=DYNAMIC COMMENT='测试表'
/*!50100 PARTITION BY RANGE (TO_DAYS(create_time))
(PARTITION p20220312 VALUES LESS THAN (to_days('2022-03-12')) ENGINE = InnoDB,
PARTITION p20220612 VALUES LESS THAN (to_days('2022-06-12')) ENGINE = InnoDB) */;

六、添加过程

参考：MySQL 存储过程 | 菜鸟教程

每3个月自动添加分区。

DELIMITER $$

DROP PROCEDURE IF EXISTS `add_partition_mytest`$$

CREATE PROCEDURE `add_partition_mytest`()
BEGIN
    START TRANSACTION;

/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
    SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE table_name='mytest' ORDER BY partition_ordinal_position DESC LIMIT 1;
     SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 3 MONTH))+0;
/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
    SET @s1=CONCAT('ALTER TABLE mytest 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 ;

其中DELIMITER 定好结束符为"$$", 然后最后又定义为";", MYSQL的默认结束符为";"。

默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。
因为mysql一遇到分号，它就要自动执行。
即，在语句RETURN '';时，mysql解释器就要执行了。
这种情况下，就需要事先把delimiter换成其它符号，如//或$$。

七、添加事件

每3个月自动添加分区。

DELIMITER ||
CREATE EVENT add_event_mytest
ON SCHEDULE
    EVERY 3 month STARTS '2022-03-10 03:00:00'
    DO
        BEGIN
            CALL add_partition_mytest();
        END ||
DELIMITER ;

查询mysql事件调度器是否开启

/* 查看定时器开关情况 */
show VARIABLES like 'event_scheduler';
/* 开启定时器开关 */
set GLOBAL event_scheduler = 1;

八、查看分区情况

SELECT partition_name, partition_ordinal_position, TABLE_Rows, table_schema
FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_name = 'tl_alarm_data'
ORDER BY partition_ordinal_position DESC

九、分区表能不能提升性能

压测时，发现表分区还不如不分区（加索引）性能更好。测试的结果是这样的，当数据是几十万级别时，分区表要3s，不分区只要200ms。当数据是200w级别时，分区表也是3点几s，不分区要6点几秒（查看分页的最后几页）。跟预想的相差很大。即使是自己分表，也比这性能更好。

网上收了一下，各种声音都有，没有找到说的很明白的。

折腾了一个晚上，终于找到原因所在：分区字段必须加入主键，所以就会形成至少两个字段的复合主键。通常id是第一主键，分区字段是第二主键。

参考：复合索引和效率_Lyndon1115的博客-CSDN博客

感谢博主。

摘出重要部分：

从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。

如果两个索引idx1（单一）,idx2（复合）都存在
并不是 where col1='A'用idx1；where col1=A and col2=B 用idx2。
其查询优化器使用其中一个以前常用索引。要么都用idx1,要么都用idx2.

（这就是为什么我给分区字段单独建了索引时，性能没有改善了。）

解决方案：

很明显，只要把分区字段改为第一主键就可以了

（id不能再用自增了，改成雪花算法：@TableId(type = IdType.ASSIGN_ID)）。

测试了一下，效果非常不错。

结论：

表分区再数据量少时基本和加了索引的不分区表差不多（略逊，可接受）

数据量大时，查询聊率提升明显（前提时分区字段一定要作为检索条件，限制每次查询只覆盖两个分区） <- 纠正：提升不是很明显，略优。分区和不分区加索引性能差不多。

十、查询总是有第一个分区

参考：MySQL Bugs: #49754: Partitioning by RANGE with TO_DAYS always includes first partition when pruning 因为分区字段为null，或者是无效时间是，都归属于第一个分区。所以每次查询都会有第一个分区。可以加一个p0分区，PARTITION p0 VALUES LESS THAN (0)（测试了一下，加不加性能没影响。）