非DBA人员从零到一，MySQL InnoDB数据库调优之路(三)-分区表与分库分表

最新推荐文章于 2022-09-13 14:56:54 发布

kiranet

最新推荐文章于 2022-09-13 14:56:54 发布

阅读量493

点赞数 1

分类专栏： Mysql 数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/kiranet/article/details/123940689

版权

数据库同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

Mysql

5 篇文章 1 订阅

订阅专栏

我发现我在工作中分区场景没有特别的多，更多时候我们会去讨论进行分库分表要怎么分，所以本篇博客我把分区和分库分表合并在一起。

1 分区表

1.1 什么是分区表

MySQL在5.1版本添加了对分区的支持

CREATE TABLE `PARTITION_TESt` (
  `id` int(32) NOT NULL,
  `year` datetime NOT NULL,
   PRIMARY KEY(`id`,`year`)
) partition by range columns(year)
(PARTITION `1990` VALUES LESS THAN (1990),
PARTITION `1991` VALUES LESS THAN (1991),
PARTITION `1992` VALUES LESS THAN (1992)
);
insert into t values(1,'1990-01-01'),(2,'1991-01-01');

物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

但是就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引

1.2 分区表的执行逻辑

分区表的引擎层行为

session A

session B

begin;

select * from PARTITION_TESt where year='1990-12-25' for update;

insert into PARTITION_TESt values(1,'1990-12-29');

(block)

insert into PARTITION_TESt values(1,'1991-01-02');

(success)

在非分区的情况下在拥有1990-01-01和1991-12-30下上面的执行结果两个insert语句会堵塞，但是在使用分区表后，因为表被分割成两个物理区间，因此只有1990下的insert语句会堵塞。

分区表的server行为

session A

session B

begin;

select * from PARTITION_TESt where year='1990-12-25';

alter table PARTITION_TESt truncate partition 1990

(block)

server 层看的话，一个分区表就只是一个表,所有分区共用同一个 MDL 锁。

1.3 InnoDB数据库分区表的限制

MySQL 要求分区表中的主键必须包含分区字段
分区无法使用外键
InnoDB Cluster不支持分区表

1.4 InnoDB数据库分区表的缺点

MySQL 在第一次打开分区表的时候，需要访问所有的分区

分区表不能建立太多的分区,分区表分区过多导致的主从延迟问题

在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁，

虽然物理上分有多个ibd组成，但是实际上在做不同分区的DML操作和DDL、DML操作的时候会出现MDL锁

1.5 分区性能就提升?

分区表不是用于提升MySQL的性能，而是在于方便数据管理

通过非DBA人员从零到一，MySQL InnoDB数据库调优之路(一)-建表可知道InnoDB的存储量与B+树高度的关系，虽然使用分区表使我们数据分散到多个物理分区，但是并没有多大的提升，举个例子把三层2000万整表数据分区成2000个2层10000数据的分区表，在访问数据时候也只是减少了1次IO，实际上没有对性能有显著的提升。