mysql表分区的使用与底层原理详解

作者:java叶新东老师 时间:2024-01-24 06:24:25 

目录
  • 什么是分区表

  • 分区表应用场景

  • 分区表的限制

  • 分区类型

  • 分区表的使用

    • 1、范围分区

    • 2、列表分区(list分区)

    • 3、列分区

    • 4、hash分区

    • 5、秘钥分区(key分区)

    • 6、子分区

  • 添加分区

    • 分区表原理

      • 如何使用分区表

        • 注意事项

          • 总结

            什么是分区表

            MySQL从5.1版本开始支持分区功能,分区是将一个表的数据按照某种方式,比如按照时间上的月份,分成多个较小的,更容易管理的部分,但是逻辑上仍是一个表。

            还没出现分区表的时候,所有的数据都是存放在一个文件里面的,如果数据量太大,查询数据时总是避免不了需要大量io操作;使用分区表后,每个分区存放不同的数据。这样不但可以减少io。还可以加快数据的访问;

            为了保证MySQL的性能,我们都建议mysql单表不要太大,建议是:单表小于2G,记录数小于1千万,十库百表。如果但行记录数非常小,那么记录数可以再偏大些,反之,可能记录数到百万级别就开始变慢了。

            那么,业务量在增长,数据到瓶颈了怎么办呢,除了使用分布式数据库,我们也可以自行分库分表,或者利用mysql的分区功能实现。

            分区表的出现是为了分而治之的概念,分区表的用处非常大,只是现在还有很多人都不知道;
            将一个表设置为分区表后,会在数据文件.idb的文件名加上#号,代表这是一个分区表;

            分区表应用场景

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

            2. 分区表的数据更容易维护,,能批量删除大量数据

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

            4. 分区表的数据可以分布在不同的设备上,从未高效的利用多个硬件设备

            5. 可以备份和恢复独立的分区;

            分区表的限制

            1. 一个表最多能有1024个分区,在5.7版本及以上可以有8196个分区

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

            3. 分区表无法使用外检约束

            4. 最好不要去修改分区列

            5. 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来;就像这样:


            -- 创建分区必须包含所有主键
            create table user_11(
             id bigint(20) not null ,
             name varchar(20) ,
             age int(3),
            PRIMARY KEY (`id`,`age`)
            )
            -- 创建分区
            partition by range columns(id,age)(
             partition p00 values less than(6,30), -- 小于6的值在P0分区
             partition p11 values less than(11,40), -- 小于11的值在p1分区
             partition p22 values less than(16,50), -- 小于16的值在p2分区
             partition p33 values less than (9999,9999) -- 大于21的值在p3分区,或者用一个更大的值
            );

            -- 创建分区必须包含所有唯一键
            create table user_22(
             id bigint(20)  not null,
             name varchar(20) ,
             age int(3) not null ,
            unique key only_one_1(age,id )
            )
            -- 创建分区
            partition by range columns(id,age)(
             partition p000 values less than(6,30), -- 小于6的值在P0分区
             partition p111 values less than(11,40), -- 小于11的值在p1分区
             partition p222 values less than(16,50), -- 小于16的值在p2分区
             partition p333 values less than (9999,9999) -- 大于21的值在p3分区,或者用一个更大的值
            );

            分区类型

            1. 范围分区

            2. 列表分区

            3. 列分区

            4. hash分区

            5. 秘钥分区

            6. 子分区

            分区表的使用

            1、范围分区

            下面示例中将年龄进行分区,


            create table employees(
            id bigint(20) not null,
            age int(3) not null,
            name varchar(20)
            )
            -- 创建分区
            partition by range (age)(
             partition p0 values less than(6), -- 小于6的值在P0分区
             partition p1 values less than(11), -- 小于11的值在p1分区
             partition p2 values less than(16), -- 小于16的值在p2分区
             partition p3 values less than(21) -- 小于21的值在p3分区
            );

            创建好之后,就可以看到在数据文件夹中的分区文件了


            [root@VM_0_5_centos test]# pwd
            /var/lib/mysql/test
            [root@VM_0_5_centos test]# ll
            总用量 8741504
            -rw-rw---- 1 mysql mysql         61 10月 31 2018 db.opt
            -rw-rw---- 1 mysql mysql       8614 8月   1 21:30 employees.frm
            -rw-rw---- 1 mysql mysql         32 8月   1 21:30 employees.par
            -rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p0.ibd
            -rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p1.ibd
            -rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p2.ibd
            -rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p3.ibd

            因为age字段最大只能插入21以下的数字,如果插入21的数字则会报错,


            mysql> insert employees (id,name,age) values(1,'yexindong',21);
            ERROR 1526 (HY000): Table has no partition for value 21

            所以,为了解决这个问题,在建表的时候可以这么干,将最大的值使用maxvalue,据说maxvalue的值为28个9,也就是9999999999999999999999999999


            create table employees(
            id bigint(20) not null,
            age int(3) not null,
            name varchar(20)
            )
            -- 创建分区
            partition by range (age)(
             partition p0 values less than(6), -- 小于6的值在P0分区
             partition p1 values less than(11), -- 小于11的值在p1分区
             partition p2 values less than(16), -- 小于16的值在p2分区
             partition p3 values less than maxvalue -- 大于16的值在p3分区,或者用一个更大的值
            );

            时间范围分区


            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 RANGE ( YEAR(separated) ) (
               PARTITION p0 VALUES LESS THAN (1991), -- 1991年之前的数据在P0分区
               PARTITION p1 VALUES LESS THAN (1996),-- 1996年之前的数据在P1分区
               PARTITION p2 VALUES LESS THAN (2001),-- 2001年之前的数据在P2分区
               PARTITION p3 VALUES LESS THAN MAXVALUE -- 2001年制后的数据在P3分区
            );

            CREATE TABLE members (
               firstname VARCHAR(25) NOT NULL,
               lastname VARCHAR(25) NOT NULL,
               username VARCHAR(16) NOT NULL,
               email VARCHAR(35),
               joined DATE NOT NULL
            )
            PARTITION BY RANGE COLUMNS(joined) (
               PARTITION p0 VALUES LESS THAN ('1960-01-01'),
               PARTITION p1 VALUES LESS THAN ('1970-01-01'),
               PARTITION p2 VALUES LESS THAN ('1980-01-01'),
               PARTITION p3 VALUES LESS THAN ('1990-01-01'),
               PARTITION p4 VALUES LESS THAN MAXVALUE
            );

            2、列表分区(list分区)

            列表分区和范围分区最大的区别就是列表是等值的,而范围分区是在某个范围内的;


            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), -- 3,5,6,9,17的值放在pNorth分区
               PARTITION pEast VALUES IN (1,2,10,11,19,20),-- 1,2,10,11,19,20的值放在pEast分区
               PARTITION pWest VALUES IN (4,12,13,14,18),-- 4,12,13,14,18的值放在pWest分区
               PARTITION pCentral VALUES IN (7,8,15,16)-- 7,8,15,16的值放在pCentral分区
            );

            3、列分区

            列分区是范围分区和列表分区的变体,也就是说列分区就是由范围分区和列表分区封装得来的,唯一的不同的是,列分区没有数据类型的限制,换句话说,范围分区和列表分区就是列分区;

            4、hash分区

            hash分区不需要指定范围或者列表,而是根据插入的值动态分配来决定插入到哪个分区,和hashMap的原理很像,不同的是hashMap会通过扰动函数来解决hash碰撞问题,但是mysql的hash分区是直接取模运算得出结果;然后插入指定位置的分区;


            -- 普通字段的分区
            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 5;--创建5个分区,分别是0,1,2,3,4

            -- 创建时间类型的分区
            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( YEAR(hired) )
            PARTITIONS 4; -- 创建四个分区,分别为0,1,2,3

            5、秘钥分区(key分区)

            key分区用的比较少


            -- 以主键进行分区
            CREATE TABLE k1 (
               id INT NOT NULL PRIMARY KEY,
               name VARCHAR(20)
            )
            PARTITION BY KEY()
            PARTITIONS 2; -- 创建2个分区分别为P0和P1,这里是hash分区的变种,存储方式和hash分区一样

            -- 以唯一键进行分区
            CREATE TABLE k1 (
               id INT NOT NULL,
               name VARCHAR(20),
               UNIQUE KEY (id)
            )
            PARTITION BY KEY()
            PARTITIONS 3;-- 创建三个分区,分别是p0,p1,p2

            -- 指定主键字段进行分区
            CREATE TABLE tm1 (
               s1 CHAR(32) PRIMARY KEY
            )
            PARTITION BY KEY(s1)
            PARTITIONS 10; -- 创建10个分区

            6、子分区

            子分区这么理解就行了:在分区的基础上在分区;举个例子吧,如果一张表分成三个分区,而每个分区又有三个子分区,所以一共有3 * 3 = 9个分区;


            -- 表中有3个分区,每个分区上有2个子分区,所以加起来一共有6个分区
            CREATE TABLE ts (id INT, purchased DATE)
               PARTITION BY RANGE( YEAR(purchased) )
               SUBPARTITION BY HASH( TO_DAYS(purchased) )
               SUBPARTITIONS 2 (
                   PARTITION p0 VALUES LESS THAN (1990),
                   PARTITION p1 VALUES LESS THAN (2000),
                   PARTITION p2 VALUES LESS THAN MAXVALUE
               );

            进入mysql的数据文件中就可以看到有6个文件,顾名思义,生成了6个分区


            -rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p0#SP#p0sp0.ibd
            -rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p0#SP#p0sp1.ibd
            -rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p1#SP#p1sp0.ibd
            -rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p1#SP#p1sp1.ibd
            -rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p2#SP#p2sp0.ibd
            -rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p2#SP#p2sp1.ibd

            添加分区


            -- 添加列表分区
            alter table titles add partition(partition p7 values in('CEO'));

            分区表原理

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

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

            select查询

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

            insert操作

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

            delete操作

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

            update操作

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

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

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

            如何使用分区表

            1. 日志系统可以用分区,一般日志数量都是比较多的,按年或者月份来分区,一般来说都需要在日志系统中查询出某一段时间的历史记录,因为数据量巨大,肯定不能走全表扫描,全表扫描会引发大量的随机io,当数据量超大的时候,索引也无法起作用;此时应该考虑用分区进行解决;

            2. 并不是数据量大才需要用分区,数据量小的时候也可以用分区,怎样的场景下数据量小呢?答案是你每次查询的数据都是某一个批次的时候就可以用分区,比如说字典,业务的字典和用户类型的字典一般都是存放在同一张表里面的,且你每次查询的时候不是差一个业务或者一个用户类型,而是查询整个业务或者用户类型,这就是一个批次,此时也可以用分区来实现;

            3. 使用分区后,就可以不用索引了,因为一般使用分区的话都是范围查询,范围查询也就没必要使用索引了;已经将数据分布在不同的分区中了;

            4. 要使用索引的话,也可以,但是要分离热数据和冷数据,热数据就是经常要查询的数据,在热数据的表上加索引来加快访问速度;

            注意事项

            1. null值会使分区过滤无效;分区是需要制定列名的,需要确保这个列名不会出现null值;

            2. 如果分区列和索引列不是同一列的话,会导致查询无法进行分区过滤,比如说你的id和age字段都加了索引,那么分区的时候最好把这2个列设为分区列:干PARTITION BY RANGE COLUMNS(id,age)

            3. 对分区表增删改的成本很高,每次对表进行次增删改的时候会打开并锁住所有的底层表,只要有一个锁住了,其他的操作就无法进行;

            4. 维护分区的时候,成本可能很高,特别是需要修改分区的时候,成本是最高的,

            来源:https://blog.csdn.net/qq_27184497/article/details/119336142

            标签:mysql,表分区,底层
            0
            投稿

            猜你喜欢

          • 使用虚拟机在VirtualBox+openEuler上安装部署openGauss数据库

            2024-01-21 22:45:53
          • python中子类与父类的关系基础知识点

            2022-11-26 23:45:12
          • Bootstrap Paginator分页插件使用方法详解

            2023-09-05 23:08:42
          • 页面中横排布局的思考

            2008-01-18 12:56:00
          • PHP使用redis实现分布式锁的示例详解

            2023-06-01 16:32:19
          • Golang协程池gopool设计与实现

            2024-05-28 15:23:01
          • python面向对象之类的继承详解

            2022-03-20 18:22:22
          • Python映射类型之dict详解

            2023-08-03 13:19:19
          • python 服务器运行代码报错ModuleNotFoundError的解决办法

            2021-03-23 00:39:08
          • 在MySQL中使用LIMIT进行分页的方法

            2024-01-20 22:27:58
          • Innodb表select查询顺序

            2024-01-16 03:32:40
          • Python基础之数据类型相关知识总结

            2021-08-05 07:04:43
          • PHP基于非递归算法实现先序、中序及后序遍历二叉树操作示例

            2023-08-16 04:46:47
          • Python-OpenCV实现图像缺陷检测的实例

            2023-02-16 19:38:46
          • Python3.5字符串常用操作实例详解

            2023-08-31 00:25:53
          • Python实现命令行通讯录实例教程

            2023-10-18 01:51:28
          • python basemap 画出经纬度并标定的实例

            2023-08-23 23:26:40
          • MySQL 字符串模式匹配 扩展正则表达式模式匹配

            2024-01-20 14:47:41
          • SQL联合查询inner join、outer join和cross join的区别详解

            2024-01-14 21:33:41
          • sql server 2005用户权限设置深入分析

            2024-01-19 12:08:21
          • asp之家 网络编程 m.aspxhome.com