mysql分区表吐血总结

最新推荐文章于 2025-07-28 14:20:58 发布

原创最新推荐文章于 2025-07-28 14:20:58 发布 · 225 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

20 篇文章

订阅专栏

分区表的原理

分区表的底层原理：

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

分区表的操作按照以下的操作逻辑进行：

**select查询**

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

**insert操作**

当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表

**delete操作**

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

**update操作**

当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

有些操作时支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作

虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。

分区表的应用场景

1、表非常大以至于无法全部放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据

2、分区表的数据更容易维护

1）批量删除大量数据可以使用清除整个分区的方式

2）对一个独立分区进行优化、检查、修复等操作

3、分区表的数据可以分布在不同的物理设备上，从而高效利用多个存储设备

4、可以使用分区表来避免某些特殊的瓶颈

1）innodb的单个索引互斥访问

2)ext3文件系统的innode锁竞争

5、可以备份和恢复独立分区

分区表的限制

1、一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区

2、在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区

3、如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

4、分区表无法使用外键约束

分区表的类型

1、范围分区

根据列值在给定范围内将行分配给分区

详情可以看我文章三分钟了解mysql范围分区--多一分钟都不要

2、列表分区

类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

mysql> DROP TABLE IF EXISTS employees;
Query OK, 0 rows affected (1.12 sec)

mysql> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT,
    ->     store_id INT
    -> )
    ->
    -> PARTITION BY LIST(store_id) (
    ->     PARTITION pNorth VALUES IN (3,5,6,9,17),
    ->     PARTITION pEast VALUES IN (1,2,10,11,19,20),
    ->     PARTITION pWest VALUES IN (4,12,13,14,18),
    ->     PARTITION pCentral VALUES IN (7,8,15,16)
    -> );
Query OK, 0 rows affected (1.18 sec)

3、列分区

mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

mysql>  CREATE TABLE `list_c` (
    ->  `c1` int(11) DEFAULT NULL,
    ->  `c2` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    ->  PARTITION BY RANGE COLUMNS(c1)
    -> (PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
    ->  PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) ;
Query OK, 0 rows affected (0.54 sec)

mysql> DROP TABLE IF EXISTS list_c;
Query OK, 0 rows affected (0.76 sec)

mysql>  CREATE TABLE `list_c` (
    ->  `c1` int(11) DEFAULT NULL,
    ->  `c2` int(11) DEFAULT NULL,
    ->  `c3` char(20) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> PARTITION BY RANGE COLUMNS(c1,c3)(
    -> PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
    -> PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB
    -> );
Query OK, 0 rows affected (0.48 sec)

mysql>  DROP TABLE IF EXISTS list_c;
Query OK, 0 rows affected (0.29 sec)

mysql>  CREATE TABLE `list_c` (
    ->  `c1` int(11) DEFAULT NULL,
    ->  `c2` int(11) DEFAULT NULL,
    ->  `c3` char(20) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> PARTITION BY LIST COLUMNS(c3)(
    -> PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
    -> PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB
    -> );
Query OK, 0 rows affected (0.45 sec)

4、hash分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含myql中有效的、产生非负整数值的任何表达式

mysql> DROP TABLE IF EXISTS employees;
Query OK, 0 rows affected (0.64 sec)

mysql> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT,
    ->     store_id INT
    -> )
    -> PARTITION BY HASH(store_id)
    -> PARTITIONS 4;
Query OK, 0 rows affected (1.24 sec)

mysql> DROP TABLE IF EXISTS employees;
Query OK, 0 rows affected (0.43 sec)

mysql> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT,
    ->     store_id INT
    -> )
    -> PARTITION BY LINEAR HASH(YEAR(hired))
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.91 sec)