这个临界值，并不能一概而论，它与硬件能力、具体业务有关。
虽然在很多 MySQL 运维规范里，都建议单表不超过 500w、1000w。
但实际上，我在生产环境，也见过大小超过 2T，记录数过亿的表，同时，业务不受影响。
单表过大时，业务通常会考虑两种拆分方案：水平切分和垂直切分。
水平切分，拆分的维度是行，一般会根据某种规则或算法将表中的记录拆分到多张表中。
拆分后的表既可在一个实例，也可在多个不同实例中。如果是后者，又会涉及到分布式事务。
垂直切分，拆分的维度是列，一般是将列拆分到多个业务模块中。这种拆分更多的是上层业务的拆分。
从改造的复杂程度来说，前者小于后者。
所以，在单表数据量过大时，业界用得较多的还是水平拆分。
常见的水平拆分方案有：分库分表、分区表。
虽然分库分表是一个比较彻底的水平拆分方案，但一方面，它的改造需要一定的时间；另一方面，它对开发的能力也有一定的要求。相对来说，分区表就比较简单，也无需业务改造。
很多人可能会认为 MySQL 的优势在于 OLTP 应用，对于 OLAP 应用就不太适合，所以，也不太推荐分区表这种偏 OLAP 的特性。
但实际上，对于某些业务类型，还是比较适合使用分区表的，尤其是那些有明显冷热数据之分，且数据的冷热与时间相关的业务。
下面，我们看看分区表的优点：
遗憾的是，MySQL 分区表不支持并行查询。理论上，当一个查询涉及到多个分区时，分区与分区之间应进行并行查询，这样才能充分利用多核 CPU 资源。
但 MySQL 并不支持，包括早期的官方文档，也提到了这个问题，也将这个功能的实现放到了优先级列表中。
在 MySQL 5.7 中，对于分区表，有个很重大的更新，即 InnoDB 存储引擎原生支持了分区，无需再通过 ha_partition 接口来实现。
所以，在 MySQL 5.7 中，如果要创建基于 MyISAM 存储引擎的分区表，会提示 warning 。
而在 MySQL 8.0 中，则更为彻底，server 层移除了 ha_partition 接口代码。
如果要使用分区表，只能使用支持原生分区的存储引擎。在 MySQL 8.0 中，就只有 InnoDB。
这就意味着，在 MySQL 8.0 中，如果要创建 MyISAM 分区表，基本上就不可能了。
这也从另外一个角度说明了为什么生产上不建议使用 MyISAM 表。
在使用分区表时，大家常常会碰到下面这个报错。
即分区键必须是主键的一部分。
上面的 opr 是一张 *** 作流水表。其中，opr_no 是 *** 作流水号，一般都会被设置为主键，opr_date 是 *** 作时间。基于 *** 作时间来进行分区，是一个常见的分区场景。
为了突破这个限制，可将 opr_date 作为主键的一部分。
但是这么创建，又会带来一个新的问题，即对于同一个 opr_no ，可插入到不同分区中。如下所示：
这实际上违背了业务对于 opr_no 的唯一性要求。
既然这样，有的童鞋会建议给 opr_no 添加个唯一索引，But，现实是残酷的。
即便是添加唯一索引，分区键也必须包含在唯一索引中。
总而言之，对于 MySQL 分区表，无法从数据库层面保证非分区列在表级别的唯一性，只能确保其在分区内的唯一性。
这也是 MySQL 分区表所为人诟病的地方之一。
但实际上，这个锅让 MySQL 背并不合适，对于 Oracle 索引组织表（ InnoDB 即是索引组织表），同样也有这个限制。
Oracle 官方文档（ http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514），在谈到索引组织表（Index-Organized Table，简称 IOT）的特性时，就明确提到了 “分区键必须是主键的一部分”。
下面，我们看看刚开始的建表 SQL ，在 Oracle 中的执行效果。
同样报错。
注意，这里指定了 ORGANIZATION INDEX ，创建的是索引组织表。
看来，分区键必须是主键的一部分并不是 MySQL 的限制，而是索引组织表的限制。
之所以对索引组织表有这样的限制，个人认为，还是基于性能考虑。
假设分区键和主键是两个不同的列，在进行插入 *** 作时，虽然也指定了分区键，但还是需要扫描所有分区才能判断插入的主键值是否违反了唯一性约束。这样的话，效率会比较低下，违背了分区表的初衷。
而对于堆表则没有这样的限制。
在堆表中，主键和表中的数据是分开存储的，在判断插入的主键值是否违反唯一性约束时，只需利用到主键索引。
但与 MySQL 不一样的是，Oracle 实现了全局索引，所以针对上面的，同一个 opr_no，允许插入到不同分区中的问题，可通过全局唯一索引来规避。
但 MySQL 却无能为力，之所以会这样，是因为 MySQL 分区表只实现了本地分区索引（Local Partitioned Index），而没有实现 Oracle 中的全局索引（Global Index）。
本地分区索引和全局索引的原理图如下所示：
结合原理图，我们来看看两种索引之间的区别：
1. MySQL 分区表关于“分区键必须是唯一键（主键和唯一索引）的一部分”的限制，本质上是索引组织表的限制。
2. MySQL 分区表只实现了本地分区索引，没有实现全局索引，所以无法保证非分区列的全局唯一。
如果要保证非分区列的全局唯一，只能依赖业务实现了。
3. 不推荐使用 MyISAM 分区表。当然，任何场景都不推荐使用 MyISAM 表。
一、背景
话说风和日丽的一天，为提高随着业务增长的大表（3510449行吧）的访问效率，于是决定对表分区，记录如下。
二、实 ***
结合业务，若干条记录会集中在一个日期，查询时也往往只查询一个日期内的数据，于是选取分区字段为时间。
创建分区 比如
CREATE TABLE message_all (
id int(10) NOT NULL AUTO_INCREMENT,
......
createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
PRIMARY KEY ( id , createtime )
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(createtime))
(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
不过我们表已经有了当然不能这么建，除非你想导一次数据。
如下 *** 作 ：
1、
ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))
(
PARTITION p2015 VALUES LESS THAN (to_days('2016-01-01')),
PARTITION p2016 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p2017 VALUES LESS THAN (to_days('2018-01-01')),
PARTITION p2018 VALUES LESS THAN MAXVALUE
)
或者
2、ALTER TABLE message_all PARTITION BY RANGE (YEAR(createtime))
(
PARTITION p2015 VALUES LESS THAN (YEAR('2016-01-01'))
)
然后追加。
ALTER TABLE message_all ADD PARTITION
(
PARTITION p2016 VALUES LESS THAN (YEAR('2017-01-01')),
PARTITION p2017 VALUES LESS THAN (YEAR('2018-01-01')),
PARTITION p2018 VALUES LESS THAN MAXVALUE
)
这里会有几种错误情况：
1、ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))
[Err] 1492 - For RANGE partitions each partition must be defined
解释：必须指定至少一个分区。
2、[Err] 1492 - A PRIMARY KEY must include all columns in the table's partitioning function
解释：分区字段必须是主键之一。
3、[Err] 1492 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
解释：分区字段为timestamp，换成datetime。
4、[Err] 1526 - Table has no partition for value xxxx
解释：用追加方式第一次必须覆盖目前所有数据。
总结：
1、创建时必须指定至少一个分区。
2、key必须为主键之一。
3、RANGE处必须为INT型，时间字段用函数转——YEAR()、YEARWEEK()、TO_DAYS()。
4、THAN处必须为INT型，时间字段用函数转——TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()。
5、它就是以两个INT比大小划分的文件。
6、所有ENGINE必须一样。
7、范围分区添加只能在最大值后面追加。
8、分区是有上限的貌似1024个。
用到的其他 *** 作
1、删除分区（直接扔掉分区文件，数据也没了）
ALTER TABLE message_all DROP PARTITION p2016
2、清空分区数据
ALTER TABLE message_all TRUNCATE PARTITION p2017
3、重定义（可实现：分区拆分、合并、重命名）
ALTER TABLE message_all REORGANIZE PARTITION p201601,p201602,p201603,p201604 INTO
(
PARTITION p2016012 VALUES less than(TO_DAYS('2016-03-01')),
PARTITION p2016034 VALUES less than(TO_DAYS('2016-05-01'))
)
检查/查看你的分区
1、SHOW TABLE STATUS LIKE 'message_all'
2、SELECT * FROM information_schema.partitions WHERE table_name='message_all'
3、SHOW CREATE TABLE message_all
4、EXPLAIN SELECT COUNT(1) FROM message_all WHERE createtime>= '2016-01-01' AND createtime <'2016-12-30'如果用到了分区partitions里会有显示。
5、指定分区查
SELECT COUNT(1) FROM message_all PARTITION (p2016) 表别名 WHERE ......
到这里就结束啦，土豆白。
一些概念
水平分区Partition有以下几种模式
我们的业务只存近一段时间的数据，因此有大量表需要清理 历史 数据，目前使用的delete清理数据，存在以下问题。为避免同时支持大量delete，我们的清理任务只在低峰期串行执行，导致任务过多时需要排队，甚至失败的情况；数据清理使用delete语句，表数据量较大时，对数据库造成很大压力；即使我们删除了旧数据，已删除的数据仍占据存储空间，底层数据文件并没有立刻变小，以至于形成数据空洞。
查看MySQL官方文档时，发现了分区表，因此基于官方文档总结一下。
MySQL逻辑上为一个表，物理上存储在多个文件中，这是 MySQL 支持的功能（5.1 开始）, 8.0 版本只 InnoDB 和 NDB 支持分区表。
优点：
缺点：
根据分区表键值的范围把数据存储到表的不同分区中，适用于以时间或日期作为分区类型，方便数据清理。
小提示：
1.当插入数据分区不存在时会报错：Table has no partition for value xxx
2.Range类型分区字段必须是数值，时间类型可用函数转换为数值；
3.分区字段列值可以为null，所有为null的数据将存在最小的分区中；
按分区键取值的列表进行分区，每一行数据须找到对应的分区列表，否则数据插入失败
小提示：
根据指定分区表达式的整数值以及分区数进行数据划分(mod函数)
小提示：
按键分区类似于按哈希分区，只是哈希分区使用用户定义的表达式，用于键分区的哈希函数由 MySQL 服务器提供。NDB 集群为此使用 MD5() 对于使用其他存储引擎的表，服务器使用自己的内部哈希函数。
小提示：
子分区(subpartitioning)也称为复合分区(composite partitioning) ，是已分区表中每个分区的进一步划分
小提示：
小提示：
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）