诺禾:mysql数据库分区表概念、类型、适用场景

概述

最近对项目上部分表按时间做了分区,所以顺便整理下mysql分区表的一些内容,仅供参考。

诺禾:mysql数据库分区表概念、类型、适用场景

 

 


一、分区表概念

分区是将一个表的数据按照某种方式,比如按照时间上的月份,分成多个较小的,更容易管理的部分,但是逻辑上仍是一个表。

分区有利于管理非常大的表,它采用分而治之的逻辑,分区引入了分区键的概念,分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集,让数据根据规则分布在不同的分区中,让一个大对象碧昂城一些小对象。

MySQL分区既可以对数据进行分区也可以对索引进行分区。


二、分区表类型

MySQL支持如下几种类型的分区:

  • RANGE分区:行数据基于一个给定连续范围分区。不好理解,看例子吧。5.5开始支持RANGE COLUMNS分区。
  • LIST分区:同RANGE,区别在于给定的不是连续范围,是离散的值。5.5开始支持LIST COLUMNS分区。
  • HASH分区:根据用户自定义的表达式的返回值进行分区,返回值不能是负数。
  • KEY分区:根据MySQL内部提供的哈希函数进行分区。
  • COLUMNS分区:5.5开始支持,可以直接使用非整形的数据进行分区,分区根据类型直接比较而得,不需要转换为整形。

无论创建何种类型的分区,如果表中存在主键或唯一索引的列,则分区列必须是主键或唯一索引的一部分。索引列可以是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,则会在分区层释放对应的表锁。

诺禾:mysql数据库分区表概念、类型、适用场景

上一篇:诺禾!秒懂!图解MySQL的各种 JOIN


下一篇:Elasticsearch基础知识分享