分区表历史

1、MySQL 5.1版本开始支持基于整数列的分区表，
2、MySQL 5.5版本开始支持RANGE和LIST分区，支持TRUNCATE分区，新增COLUMNS关键词简化分区定义。
3、MySQL 5.6版本开始支持分区交换，支持显式分区查询，支持最大8182个分区或子分区。
4、MySQL 5.7版本引入本地分区策略，并标记弃用通用分区策略。

 

分区策略

按照管理打开分区的行为可以将分区策略分为两类：
1、通用分区策略(Generic Partitioning), 由MySQL Server层负责控制访问分区。
2、本地分区策略(Native Partitioning),由存储引擎层负责控制访问分区。

在MySQL开始支持分区表时，将分区表访问控制操作放在MySQL Server层实现，由于在文件管理/表管理等方面实现较为粗糙，存在严重性能问题。而不同存储引擎层使用不同存储机制/索引结构/访问控制(锁)，可以通过特殊设计来提升或优化特定的操作，将分区访问控制策略放置在存储引擎中实现更好。


通用分区策略问题：
1、当分区表第一次被访问时，无论该次访问需要操作多少个分区，都需要访问该分区表上所有分区，导致性能问题。当分区表上分区数量较大时，可能会因为打开文件数量超过参数open_file_limit限制而出错。
2、在对分区表进行维护时，需要同时维护原分区文件和新分区文件，如将分区表由100分区扩展至101分区时，需要2*100+2*101=402个文件描述符。


在MySQL 5.7.9版本中，InnoDB引入本地分区策略，由InnoDB存储引擎层内部管理访问分区表行为。
在MySQL 5.7.17版本中，MySQL将通用分区策略标记为弃用
在MySQL 8.0版本，不再允许MyISAM引擎使用分区表，因为MyISAM引擎不支持本地分区策略。
目前仅有InnoDB和NDB两种存储引擎支持本地分区策略。

 

MySQL 5.7分区增强

MySQL 5.7分区增强：
1、MySQL 5.7.1开始支持HANDLER语句(非标准SQL语句，不支持DML操作，通过指定索引来访问数据，降低优化器解析和优化SQL的开销，提升查询性能。)
2、MySQL 5.7.2开始子分区支持ANALYZE/CHECK/OPTIMIZE/REPAIR/TRUNCATE操作
3、MySQL5.7.3支持index condition pushdown(ICP)特性
4、MySQL 5.7.4为InnoDB表分区支持FLUSH TABLES FOR EXPORT选项
5、支持使用缓存来提升Load data的性能，每个分区使用130KB缓冲区
6、支持使用CACHE INDEX和LOAD INDEX INTO CACHE语句对分区的MyISAM表支持索引缓存

 

分区表优点

在MySQL Server层分区表为一个表，而在MySQL存储引擎层分区表是多个表，因此有如下特点：
1、分区表对业务透明，只需要维护一个表的数据结构。
2、DML操作加锁仅影响操作的分区，不会影响未访问分区。
3、通过分区交换快速将数据换入和换出分区表。
4、通过TRUNCATE操作快速清理特定分区数据。
5、通过强制分区仅访问特定分区数据，减少操作影响。
6、通过大数据量分区能有效降低索引层数，提高查询性能。

 

分区表缺点

由于分区表在MySQL Server层为一个表，因此：
1、DDL操作需要锁定所有分区，导致所有分区上操作都被阻塞。
2、当表数据量较小时，分区表和非分区表性能相近，分区表效果有限。
3、当表数据量较大时，对分区表进行DDL或其他运维操作难度大风险高。
4、分区表使用较少，存在未知风险多，BUG多BUG多BUG多，MySQL社区版本免费，横向扩展成本低，分库分表实现简单且中间件完善。
5、当单台服务器性能无法满足时，对分区表进行分拆的成本较高，而分库分表能很容易实现横向分拆。
6、当分区表操作不当导致访问所有分区时，会导致严重的性能问题，而分库分表操作不当仅影响访问的表。
7、使用分库分表可以有效运维降低运维操作影响，对1亿数据量表做DDL操作需要谨慎评估，而对10万数据量表做DDL操作可以默认其很快完成。
8、使用分库分表可以有效减小宕机或其他故障影响，将数据分库分表到10套群集上，一套群集发生故障仅影响业务的一成。

 

个人见解

对于SQL Server和Oracle这些商业数据库，由于商业授权导致横向扩展成本较高，且分区表功能稳定，因此通过硬件扩展和分区来承担大数据量带来的负载，而对于MySQL，互联网企业有资源有能力将很多需求迁移到数据库外部实现，因此更追求MySQL使用过程中的简单稳定可靠，且通过堆服务器+分库分表更能有处理数据量爆炸式增长带来的性能问题。