分区表历史

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使用过程中的简单稳定可靠,且通过堆服务器+分库分表更能有处理数据量爆炸式增长带来的性能问题。