知识点 | MySQL分区到底能不能用？

Point: 理解的最好形式是有效发问？

要理解MySQL分区到底能不能用，就需要知道为何会有分区这个东东。

为什么需要分区？
TP类事务中，业务对巨大数据表全表扫描的时间变得无法容忍。这时候你会想起要建索引，但依赖索引，会导致大量的碎片和低聚集度的数据，会导致查询的时候有上千次的随机 I/O 访问而致查询响应时间无法接受。这种情况下一般只会使用1-2个索引，而不会更多。

可以说此时索引意义不大，且维护索引（磁盘空间，I/O 操作）的代价同样很高。

索引本质是拿空间来换取时间的一种方式，但这种方式并非一直有效/万能的。

怎么办？

有两个可行的选项：

1. 查询必须从数据表的指定的部分顺序查找；
2. 期望的部分数据及其索引与服务器的内存匹配（局部性原则）。

分区能够解决上述问题。

原因为：
分区是索引的一个初级形式，负荷低且能够让从临近的数据中获取结果；可以依次扫描相邻的数据或者是将临近的数据加载到内存进行检索。
分区之所以负荷低是因它并没有指针指向对应的数据行，也不需要被更新。
分区并不精确地将数据按行划分，也没有涉及到所谓的数据结构。

实际上，分区的本质是对数据进行了分类划分。

再进一步解释：
MySQL的分区表的原理是由多个相关的底层表实现，这些底层表由句柄对象表示，所以可直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

涉及到具体操作是：
分区是将一个表的数据按照某种方式，逻辑上仍是一个表，也就是所谓的分区表。分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成一些小对象，从而实现对数据的分化管理。作为MySQL数据库中的一个重要机制，MySQL分区表优点和限制也是一目了然的，然而又能够同时实现共存。

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

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

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

MySQL 5.7版本引入本地分区策略，并标记弃用通用分区策略。

通用分区策略到底有啥问题而被弃用。

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

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

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分区表优点：

1.分区表对业务透明，只需要维护一个表的数据结构。
2.DML操作加锁仅影响操作的分区，不会影响未访问分区。
3.通过分区交换快速将数据换入和换出分区表。
4.通过TRUNCATE操作快速清理特定分区数据。
5.通过强制分区仅访问特定分区数据，减少操作影响。
6.通过大数据量分区能有效降低索引层数，提高查询性能。
7.与单个磁盘或文件系统分区相比，可以存储更多的数据。
8.对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
9.一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
10.涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
11.通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

说完了MySQL分区表优点，再来聊一聊MySQL分区表限制。

1.因需要根据分区列来确定数据所在分区，所以分区列必须作为查询条件，如果不使用分区列的查询条件，无法进行分区过滤，Mysql最终会扫描所有分区。
2.所有分区都必须使用相同的存储引擎。
3.某些存储引擎不支持分区(MERGE、CSV、FEDERATED)。
4.一张表最多只能有1024个分区。
5.分区表中无法对非分区列建立唯一索引(Unique Index)。
6.分区表中无法使用外键。
7.打开并锁住所有底层表的成本可能很高。
当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。

8、维护分区的成本可能很高
某些分区维护操作的速度会非常快，例如新增或者删除分区。而有些操作，例如充足分区或类似alter语句的操作；这类操作需要赋值数据。重组分区的原理与alter类似，先创建一个临时的分区，然后将数据赋值到其中，然后在删除原分区。

9、分区的字段，必须是表上所有的唯一索引(或者主键索引)包含的字段的子集。一个表上有一个或者多个唯一索引的情况下，分区的字段必须被包含在所有的主键或者唯一索引字段中。

10、分区键非NULL约束
如分区键所在列没有notnull约束，如是range分区表，那么null行将被保存在范围最小的分区。如果是list分区表，那么null行将被保存到list为0的分区。
在按HASH和KEY分区的情况下，任何产生NULL值的表达式mysql都视同它的返回值为0。为了避免这种情况的产生，建议分区键设置成NOT NULL。

11、分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键( BLOB or TEXT 列除外)。

12、只有RANG和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

【参考】
https://blog.youkuaiyun.com/weixin_33694391/article/details/113557678

文章结束。

以下是个人微信公众号，欢迎关注：