MySQL之分区表介绍

本文大部分参考自《MySQL技术内幕:InnoDB存储引擎》

概述

对于InnoDB,分区就是把一个数据表的物理存储拆分为多个ibd文件(相当于独立表空间再拆分成了多个表空间)。分区后的MyISAM,则是每个分区有一个myi文件(表的索引信息)和一个myd文件(表的数据信息)。每一个分区,在MySQL看来都是一张独立的表。

分区类型

水平分区

这种形式分区是对表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。

垂直分区

这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应的行。

MySQL在5.1时添加了对水平分区的支持。并不支持垂直分区。

MySQL数据库的分区是局部分区索引,一个分区中既存了数据,又放了索引。也就是说,每个区的聚集索引和非聚集索引都放在各自区的(不同的物理文件)。目前MySQL数据库还不支持全局分区(数据存放在各个分区中,但是所有数据的索引放在一个对象中)。

分区算法

当前MySQL支持以下几种类型的分区:

  • RANGE分区:行数据基于属于一个给定连续区间的列值被放入分区,MySQL5.5开始支持。
  • LIST分区:和RANGE类似,只是LIST分区面向的是离散的值。MySQL5.5开始支持。
  • HASH分区:根据用户自定义的表达式的返回值来进行分区,返回值不能为负数。
  • KEY分区:根据MySQL数据库提供的哈希函数来进行分区。

无论哪种类型的分区,如果表中存在主键或唯一索引时,分区列(用来判断该分往哪个分区)必须是唯一索引的一个组成部分。

RANGE分区

示例

CREATE TABLE tbl_new(
    id INT NOT NULL PRIMARY KEY,
    title VARCHAR(20) NOT NULL DEFAULT ''
)
PARTITION BY RANGE(id)(
    PARTITION t0 VALUES LESS THAN(10),
    PARTITION t1 VALUES LESS THAN(20),
    PARTITION t2 VALUES LESS THAN(MAXVALUE)
);

这个表会把以id列作为分区依据,当id:
+ 0~10 放在 t0
+ 10~20 放在 t1
+ >20 放在 t2

当插入一个不在分区范围内的值的时候,MySQL会报异常。

LIST分区

示例

create table user (
    uid int not null,
    userName varchar(20),
    gender tinyint
)
partition by list(gender) (
    partition male values in (1),
    partition female values in (2),
    partition unknown values in (3)
);

这个表会把以gender列(性别)作为分区依据,当gender:
+ = 1 放在 male
+ = 2 放在 female
+ = 3 放在 unknown

当插入一个不在分区范围内的值的时候,MySQL同样会报异常。

当一条Insert语句插入多个值的时候,而其中有一些值是在分区范围内的,MyISAM引擎会把第一个不满足范围的数据前面的数据全部插入,而后面的不处理。而InnoDB则把它当成一个事务,都不会处理。

HASH分区

示例:

CREATE TABLE t_hash(
    a INT,
    b DATETIME
) ENGINE=InnoDB
PARTITION BY HASH(YEAR(b))
PARTITIONS 4;

用户给数据库提供分区的依据,YEAR(b),必须是一个正整数,然后需要通过PARTITIONS num告知数据库分区的数量,然后数据库自动根据YEAR(b)将数据均匀分到这些分区中。如果没有PARTITIONS num语句,分区的数量默认为1。

KEY分区

KEY分区和HASH分区相似,不同之处在于HASH分区使用用户定义的函数进行分区,KEY分区使用MySQL数据库提供的函数进行分区。对于大多数存储引擎,MySQL数据库使用其内部的哈希函数,这些函数基于与PASSWORD()一样的运算法则。

示例:

CREATE TABLE t_key(
    a INT,
    b DATETIME
) ENGINE=InnoDB
PARTITION BY KEY (b)
PARTITIONS 4;

其他

COLUMNS分区、子分区

分区的应用场景

  1. 表非常大以至于无法全部放在内存中,或者只在表的最后部分有热点数据,其他均为历史数据(可以将热点数据提取出来,加快热点数据的查询、访问效率)
  2. 分区表的数据更容易维护。(批量删除数据->清除整个分区)
  3. 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备
  4. 可以使用分区表来避免某些特殊的瓶颈。比如 InnoDB 的单个索引的互斥访问,ext3 文件系统的 inode 锁竞争。
  5. 还可以备份和恢复独立的分区

分区的限制

  1. 一个表最多只能有 1024 个分区
  2. 如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分。
  3. 分区表中无法使用外键索引

分区的陷阱

NULL值会使分区过滤无效

分区的表达式的值可以是 NULL;第一个分区是一个特殊分区,如果表达式的值为 NULL 或非法值,记录都会被存放到第一个分区。WHERE 查询时即使看起来可以过滤到只有一个分区,但实际会检查两个分区,即第一个分区。最好是设置分区的列为NOT NULL。

分区列和索引列不匹配

如果定义的索引列和分区列不匹配,会导致索引无法进行分区过滤。

假设在列 a 上定义了索引,而在列 b 上进行分区。因为每个分区都有其独立的索引,所以扫描 b 上的索引就需要扫描每一个分区内对应的索引。

选择分区的成本可能很高

尤其是范围分区,对于回答“这一行属于哪个分区”、“这些符合查询条件的行在哪些分区”这样的问题的成本可能会非常高。其他的分区类型,比如KEY分区和HASH分区,就没有这样的问题。在批量插入时问题尤其严重。

原创文章,作者:彭晨涛,如若转载,请注明出处:https://www.codetool.top/article/mysql%e4%b9%8b%e5%88%86%e5%8c%ba%e8%a1%a8%e4%bb%8b%e7%bb%8d/

发表评论

电子邮件地址不会被公开。