MySQL分区表，为什么分区键必须是主键的一部分？

　　随着业务的不断发展，数据库中的数据会越来越多，相应地，单表的数据量也会越到越大，大到一个临界值，单表的查询性能就会下降。
　　这个临界值，并不能一概而论，它与硬件能力、具体业务有关。
　　虽然在很多MySQL运维规范里，都建议单表不超过500w、1000w。
　　但实际上，我在生产环境，也见过大小超过2T，记录数过亿的表，同时，业务不受影响。
　　单表过大时，业务通常会考虑两种拆分方案：水平切分和垂直切分。水平拆分VS垂直拆分
　　水平切分，拆分的维度是行，一般会根据某种规则或算法将表中的记录拆分到多张表中。
　　拆分后的表既可在一个实例，也可在多个不同实例中。如果是后者，又会涉及到分布式事务。
　　垂直切分，拆分的维度是列，一般是将列拆分到多个业务模块中。这种拆分更多的是上层业务的拆分。
　　从改造的复杂程度来说，前者小于后者。
　　所以，在单表数据量过大时，业界用得较多的还是水平拆分。
　　常见的水平拆分方案有：分库分表、分区表。
　　虽然分库分表是一个比较彻底的水平拆分方案，但一方面，它的改造需要一定的时间；另一方面，它对开发的能力也有一定的要求。相对来说，分区表就比较简单，也无需业务改造。分区表
　　很多人可能会认为MySQL的优势在于OLTP应用，对于OLAP应用就不太适合，所以，也不太推荐分区表这种偏OLAP的特性。
　　但实际上，对于某些业务类型，还是比较适合使用分区表的，尤其是那些有明显冷热数据之分，且数据的冷热与时间相关的业务。
　　下面，我们看看分区表的优点：提升查询性能对于分区表的查询操作，如果查询条件中包含分区键，则这个查询操作就只会被下推到符合条件的分区内进行，无关分区将自动过滤掉。在数据量比较大的情况下，能提升查询速度。对业务透明将表从一个非分区表转换为分区表，业务端无需做任何改造。管理方便在对单个分区进行删除、迁移和维护时，不会影响到其它分区。尤其是针对单个分区的删除（DROP）操作，避免了针对这个分区所有记录的DELETE操作。
　　遗憾的是，MySQL分区表不支持并行查询。理论上，当一个查询涉及到多个分区时，分区与分区之间应进行并行查询，这样才能充分利用多核CPU资源。
　　但MySQL并不支持，包括早期的官方文档，也提到了这个问题，也将这个功能的实现放到了优先级列表中。ThesefeaturesarenotcurrentlyimplementedinMySQLPartitioning，butarehighonourlistofpriorities。QueriesinvolvingaggregatefunctionssuchasSUM（）andCOUNT（）caneasilybeparallelized。AsimpleexampleofsuchaquerymightbeSELECTsalespersonid，COUNT（orders）asordertotalFROMsalesGROUPBYsalespersonid；。Byparallelized，wemeanthatthequerycanberunsimultaneouslyoneachpartition，andthefinalresultobtainedmerelybysummingtheresultsobtainedforallpartitions。Achievinggreaterquerythroughputinvirtueofspreadingdataseeksovermultipledisks。MySQL8。0中分区表的变化
　　在MySQL5。7中，对于分区表，有个很重大的更新，即InnoDB存储引擎原生支持了分区，无需再通过hapartition接口来实现。
　　所以，在MySQL5。7中，如果要创建基于MyISAM存储引擎的分区表，会提示warning。Thepartitionengine，usedbytablesbtest。trange，isdeprecatedandwillberemovedinafuturerelease。Pleaseusenativepartitioninginstead。
　　而在MySQL8。0中，则更为彻底，server层移除了hapartition接口代码。
　　如果要使用分区表，只能使用支持原生分区的存储引擎。在MySQL8。0中，就只有InnoDB。
　　这就意味着，在MySQL8。0中，如果要创建MyISAM分区表，基本上就不可能了。
　　这也从另外一个角度说明了为什么生产上不建议使用MyISAM表。mysqlCREATETABLEtrange（idINT，nameVARCHAR（10））ENGINEMyISAMPARTITIONBYRANGE（id）（PARTITIONp0VALUESLESSTHAN（5），PARTITIONp1VALUESLESSTHAN（10））；ERROR1178（42000）：Thestorageengineforthetabledoesntsupportnativepartitioning为什么分区键必须是主键的一部分？
　　在使用分区表时，大家常常会碰到下面这个报错。mysqlCREATETABLEopr（oprnoINT，oprdateDATETIME，descriptionVARCHAR（30），PRIMARYKEY（oprno））PARTITIONBYRANGECOLUMNS（oprdate）（PARTITIONp0VALUESLESSTHAN（20210101），PARTITIONp1VALUESLESSTHAN（20210102），PARTITIONp2VALUESLESSTHANMAXVALUE）；ERROR1503（HY000）：APRIMARYKEYmustincludeallcolumnsinthetablespartitioningfunction（prefixedcolumnsarenotconsidered）。
　　即分区键必须是主键的一部分。
　　上面的opr是一张操作流水表。其中，oprno是操作流水号，一般都会被设置为主键，oprdate是操作时间。基于操作时间来进行分区，是一个常见的分区场景。
　　为了突破这个限制，可将oprdate作为主键的一部分。mysqlCREATETABLEopr（oprnoINT，oprdateDATETIME，descriptionVARCHAR（30），PRIMARYKEY（oprno，oprdate））PARTITIONBYRANGECOLUMNS（oprdate）（PARTITIONp0VALUESLESSTHAN（20210101），PARTITIONp1VALUESLESSTHAN（20210102），PARTITIONp2VALUESLESSTHANMAXVALUE）；QueryOK，0rowsaffected（0。04sec）
　　但是这么创建，又会带来一个新的问题，即对于同一个oprno，可插入到不同分区中。如下所示：mysqlinsertintooprvalues（1，2020123100：00：01，abc）；QueryOK，1rowaffected（0。00sec）mysqlinsertintooprvalues（1，2021010100：00：01，abc）；QueryOK，1rowaffected（0。00sec）mysqlselectfromoprpartition（p0）；oprnooprdatedescription12020123100：00：01abc1rowinset（0。00sec）mysqlselectfromoprpartition（p1）；oprnooprdatedescription12021010100：00：01abc1rowinset（0。00sec）
　　这实际上违背了业务对于oprno的唯一性要求。
　　既然这样，有的童鞋会建议给oprno添加个唯一索引，But，现实是残酷的。mysqlcreateuniqueindexukoprnoonopr（oprno）；ERROR1503（HY000）：AUNIQUEINDEXmustincludeallcolumnsinthetablespartitioningfunction（prefixedcolumnsarenotconsidered）
　　即便是添加唯一索引，分区键也必须包含在唯一索引中。
　　总而言之，对于MySQL分区表，无法从数据库层面保证非分区列在表级别的唯一性，只能确保其在分区内的唯一性。
　　这也是MySQL分区表所为人诟病的地方之一。
　　但实际上，这个锅让MySQL背并不合适，对于Oracle索引组织表（InnoDB即是索引组织表），同样也有这个限制。
　　Oracle官方文档（http：docs。oracle。comcdE1188201server。112e40540schemaob。htmCNCPT1514），在谈到索引组织表（IndexOrganizedTable，简称IOT）的特性时，就明确提到了分区键必须是主键的一部分。NotethefollowingcharacteristicsofpartitionedIOTs：Partitioncolumnsmustbeasubsetofprimarykeycolumns。Secondaryindexescanbepartitionedlocallyandglobally。OVERFLOWdatasegmentsarealwaysequipartitionedwiththetablepartitions。
　　下面，我们看看刚开始的建表SQL，在Oracle中的执行效果。SQLCREATETABLEoproracle（2oprnoNUMBER，3oprdateDATE，4descriptionVARCHAR2（30），5PRIMARYKEY（oprno）6）7ORGANIZATIONINDEX8PARTITIONBYRANGE（oprdate）（9PARTITIONp0VALUESLESSTHAN（TODATE（20170713，yyyymmdd）），10PARTITIONp1VALUESLESSTHAN（TODATE（20170714，yyyymmdd）），11PARTITIONp2VALUESLESSTHAN（MAXVALUE）12）；PARTITIONBYRANGE（oprdate）（ERRORatline8：ORA25199：partitioningkeyofaindexorganizedtablemustbeasubsetoftheprimarykey
　　同样报错。
　　注意，这里指定了ORGANIZATIONINDEX，创建的是索引组织表。
　　看来，分区键必须是主键的一部分并不是MySQL的限制，而是索引组织表的限制。
　　之所以对索引组织表有这样的限制，个人认为，还是基于性能考虑。
　　假设分区键和主键是两个不同的列，在进行插入操作时，虽然也指定了分区键，但还是需要扫描所有分区才能判断插入的主键值是否违反了唯一性约束。这样的话，效率会比较低下，违背了分区表的初衷。
　　而对于堆表则没有这样的限制。
　　在堆表中，主键和表中的数据是分开存储的，在判断插入的主键值是否违反唯一性约束时，只需利用到主键索引。
　　但与MySQL不一样的是，Oracle实现了全局索引，所以针对上面的，同一个oprno，允许插入到不同分区中的问题，可通过全局唯一索引来规避。SQLCREATETABLEoproracle（2oprnoNUMBER，3oprdateDATE，4descriptionVARCHAR2（30），5PRIMARYKEY（oprno，oprdate）6）7ORGANIZATIONINDEX8PARTITIONBYRANGE（oprdate）（9PARTITIONp0VALUESLESSTHAN（TODATE（20170713，yyyymmdd）），10PARTITIONp1VALUESLESSTHAN（TODATE（20170714，yyyymmdd）），11PARTITIONp2VALUESLESSTHAN（MAXVALUE）12）；Tablecreated。SQLcreateuniqueindexukoprnoonoproracle（oprno）；Indexcreated。SQLinsertintooproraclevalues（1，todate（2020123100：00：01，yyyymmddhh24：mi：ss），abc）；1rowcreated。SQLinsertintooproraclevalues（1，todate（2020123100：00：01，yyyymmddhh24：mi：ss），abc）；insertintooproraclevalues（1，todate（2020123100：00：01，yyyymmddhh24：mi：ss），abc）ERRORatline1：ORA00001：uniqueconstraint（SCOTT。SYSIOTTOP87350）violated
　　但MySQL却无能为力，之所以会这样，是因为MySQL分区表只实现了本地分区索引（LocalPartitionedIndex），而没有实现Oracle中的全局索引（GlobalIndex）。本地分区索引VS全局索引
　　本地分区索引和全局索引的原理图如下所示：
　　结合原理图，我们来看看两种索引之间的区别：本地分区索引同时也是分区索引，分区索引和表分区之间是一一对应的。而全局索引，既可以是分区的，也可以是不分区的。如果是全局分区索引，一个分区索引可对应多个表分区，同样，一个表分区也可对应多个分区索引。对本地分区索引的管理操作只会影响到单个分区，不会影响到其它分区。而对全局分区索引的管理操作会造成整个索引的失效，当然，这一点可通过UPDATEINDEXES子句加以规避。本地分区索引只能保证分区内的唯一性，无法保证表级别的唯一性，但全局分区可以。在Oracle中，无论是索引组织表还是堆表，如果要创建本地唯一索引，同样也要求分区键必须是唯一键的一部分。SQLcreateuniqueindexukoprnolocalonoproracle（oprno）local；createuniqueindexukoprnolocalonoproracle（oprno）localERRORatline1：ORA14039：partitioningcolumnsmustformasubsetofkeycolumnsofaUNIQUEindex总结
　　1。MySQL分区表关于分区键必须是唯一键（主键和唯一索引）的一部分的限制，本质上是索引组织表的限制。
　　2。MySQL分区表只实现了本地分区索引，没有实现全局索引，所以无法保证非分区列的全局唯一。
　　如果要保证非分区列的全局唯一，只能依赖业务实现了。
　　3。不推荐使用MyISAM分区表。当然，任何场景都不推荐使用MyISAM表。