|
诺禾指导你一文看懂mysql数据库分区表概念、类型、适用场景、优缺点及原理 最近对项目上部分表按时间做了分区，所以顺便整理下mysql分区表的一些内容，仅供参考。 一、分区表概念分区是将一个表的数据按照某种方式，比如按照时间上的月份，分成多个较小的，更容易管理的部分，但是逻辑上仍是一个表。 分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象碧昂城一些小对象。 MySQL分区既可以对数据进行分区也可以对索引进行分区。 二、分区表类型MySQL支持如下几种类型的分区：
无论创建何种类型的分区，如果表中存在主键或唯一索引的列，则分区列必须是主键或唯一索引的一部分。索引列可以是null值。在没有主键和唯一索引的表中可以指定任意列为索引列。表中只能最多有一个唯一索引，即primary key 和unique key不能同时存在，primary key包含在unique key中时除外。 三、分区适用场景为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率，MYSQL数据库分区更适合以下场景： 1、表非常大无法全部都放在内存中，或者只是在表的最后部分有热点数据，其他均是历史数据。 2、分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。 3、分区表的数据分布在不同的物理设备，从而高效的利用多个硬件设备。 4、使用分区表避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等、 5、备份和恢复独立的分区，这在非常大的数据集的场景下更高效。 四、分区表优点及限制1、优点 1）与单个磁盘或文件系统分区相比，可以存储更多的数据。 2）对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。 3）一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。 4）涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。 5）通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。 2、限制 1）因为需要根据分区列来确定数据所在分区，所以分区列必须作为查询条件， 如果不使用分区列的查询条件，那么就无法进行分区过滤，Mysql最终会扫描所有分区。 2）所有分区都必须使用相同的存储引擎。 3）某些存储引擎不支持分区（MERGE、CSV、FEDERATED） 4）一张表最多只能有1024个分区 5）分区表中无法对非分区列建立唯一索引（Unique Index） 6）分区表中无法使用外键 7) 打开并锁住所有底层表的成本可能很高 当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。这一点对一些本身操作非常快的查询，比如根据主键朝找单行，会带来明显的额外开销。可以用批量操作的方式来降低单个操作的此类开销，例如使用批量插入或者load data infile、一次删除多行数据，等等。当然同时还是需要限制分区的个数。 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分区不能进行子分区。 五、mysql分区表的原理分区表由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样，所有的底层表都必须使用相同的存储引擎，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。 分区表上的操作按照下面的操作逻辑进行： 1、select 当查询一个分区表的时候，分区层先打开并锁定住所有的底层表，优化器先判断是否可以过滤部分分区，然后在调用对应的存储引擎接口访问各个分区的数据。 2、insert 当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定那个分区接收这条记录，在将记录写入对应底层表。 3、delete 当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。 4、update 当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，在判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。 有些操作是支持过滤的。例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉。这对update语句同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。MySQL先确定这条记录属于哪个分区，在将记录写入对应的底层分区表，无需对任何其他分区进行操作。虽然每个操作都会先打开并锁住所有的底层表，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，如InnoDB，则会在分区层释放对应的表锁。 |
|
|
