MySQL 针对千万或亿级别数据量的表格维护,可以考虑使用表格分区进行优化

2023年 11月 27日 51.3k 0

MySQL 表格分区

什么情况下,考虑 表格分区?

项目中,经常会遇到数据量比较大的表格(百万、千万、亿),这个时候就要根据业务,考虑表格分区的设计思维了。
通过将表格的行分成多个逻辑分区,可以更快地执行涉及这些分区的查询,并降低对大量数据的访问需求。

MySQL 分区步骤

MySQL 表格分区是一种将一个表格分成多个较小的、独立的区域,以提高查询性能和管理方便性的技术。下面是操作 MySQL 表格分区的一般步骤:

  • 确保你的 MySQL 版本支持表格分区。MySQL 5.6 及以上版本都支持表格分区。
  • 创建表格时指定分区。在 CREATE TABLE 语句中,使用 PARTITION BY 子句指定分区方法。常见的分区方法包括:
    • RANGE:根据列的值的范围进行分区。
    • LIST:根据列的值的列表进行分区。
    • HASH:根据列的哈希值进行分区。
    • KEY:根据列的索引进行分区。
  • 设计表格时,考虑好分区一般为:RANGE + LIST 、 HASH + KEY
    例如,创建一个按年份分区的顾客表格:

    -- 第一种:RANGE分区 根据年份将数据进行分区处理。
    CREATE TABLE customers (
       id INT,
       name VARCHAR(50),
       year INT,
       partitioned_column VARCHAR(50)
    ) PARTITION BY RANGE (year) (
        PARTITION p0 VALUES LESS THAN (2000),
        PARTITION p1 VALUES LESS THAN (2005),
        PARTITION p2 VALUES LESS THAN (2010),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );
    
    -- 第二种:HASH分区 根据月份将数据进行分区处理
    -- 此处,是划分了12个分区。
    CREATE TABLE customers (
       id INT,
       name VARCHAR(50),
       crt_date date,
       partitioned_column VARCHAR(50)
    ) PARTITION BY HASH(MONTH(crt_date)) 
    PARTITIONS 12;
    
    -- 第三种:LIST分区 根据顾客状态进行分区处理
    CREATE TABLE customers (
       id INT,
       name VARCHAR(50),
       status char(1),
       crt_date date,
       partitioned_column VARCHAR(50)
    ) PARTITION BY list(status)
    PARTITIONS 12;
    
    -- 第四种:KEY分区 根据相同KEY写入同一个分区
    CREATE TABLE customers (
       id INT,
       name VARCHAR(50),
       status char(1),
       crt_date date,
       partitioned_column VARCHAR(50)
    ) PARTITION BY key(id)
    PARTITIONS 12
    
    -- 通过下面的命令,可以将原来的12个分区改为6个分区
    ALTER TABLE customers COALESCE PARTITION 6;
    
  • 插入数据时,MySQL 会自动将数据分配到相应的分区。你可以像插入普通表格一样插入数据。例如:
  • INSERT INTO customers (id, name, year, partitioned_column) VALUES (1, 'John Doe', 2008, 'some value');
    
  • 查询数据时,MySQL 会自动从相应的分区中获取数据。你可以像查询普通表格一样查询数据。例如:
  • SELECT * FROM customers WHERE year = 2008;
    
  • 调整分区。你可以根据需要添加、删除或合并分区。例如,为顾客表格添加一个新的分区:
  • ALTER TABLE customers ADD PARTITION (PARTITION p4 VALUES LESS THAN (2015));
    
  • 注意监控和维护分区。
  • 使用SHOW TABLE STATUS 命令:查看表格的状态和分区信息。

    # 查看所有表格的状态和分区信息
    show table status;
    

    使用** OPTIMIZE TABLE命令:是优化表格的命令。也可以优化和修复分区。
    它的作用主要是重建表索引,并压缩表碎片,使得数据物理排列更加紧密,从而加快查询速度。此外,它还可以更新统计信息和缓存,从而减少数据读取次数和IO操作,大大提高数据库效率。
    场景:优化删除大量数据后的表,或者对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行很多更改后的优化。一般针对特定的表格,进行周期运行。

    optimize table customers;
    

    使用 ALTER TABLE ... REORGANIZE PARTITION命令:可以合并相邻的分区。

    -- 分解分区  将p0拆分为s0和s1
    ALTER TABLE customers REORGANIZE PARTITION p0 INTO (
        PARTITION s0 VALUES LESS THAN (3),
        PARTITION s1 VALUES LESS THAN (5)
    );
    
    -- 合并分区  将s0和s1合并为p0
    ALTER TABLE customers REORGANIZE PARTITION s0,s1 INTO (
        PARTITION p0 VALUES LESS THAN (5)
    );
    
    -- 合并分区  将s0和s1合并为 r0 r1
    ALTER TABLE customers REORGANIZE PARTITION s0,s1 INTO (
        PARTITION s0 VALUES LESS THAN (2),
        PARTITION p0 VALUES LESS THAN (5)
    );
    

    使用 ALTER TABLE ... ADD PARTITION命令:可以添加新的分区。

    alter table customers add partition (partition p5 VALUES LESS THAN (2020))
    

    使用 ALTER TABLE ... COALESCE PARTITION命令:修改分区数量。

    -- 通过下面的命令,将原来的12个分区改为6个分区
    ALTER TABLE customers COALESCE PARTITION 6;
    

    什么场景下,具体使用什么分区?

  • Key分区:这种分区方式主要用于将具有相同Key的消息写入同一个分区,以保证消息的顺序性。例如,在订单系统中,每个订单都有一个唯一的订单号作为Key,那么具有相同订单号的消息将被写入同一个分区,保证了订单的顺序。
  • Hash分区:Hash分区主要用于数据结构中,主要是为了提高查询效率。在密码学中,hash算法的作用主要是用于消息摘要和签名,对整个消息的完整性进行校验。
  • List分区:List分区特别适合于枚举值列的分区,例如根据性别分区。这种分区方式能够快速定位到特定的数据分区,从而提高查询效率。
  • Range分区:Range分区最适合的两种场景是 1.当我们需要删除过期或某些一类数据时,可以通过ALTER TABLE命令直接删除特定分区的数;2.当我们查询数据时,通过合理设计可以减少全表扫描,从而提高查询效率。例如,SELECT * from t_user_main where f_id > 12可以直接扫描p2区扫描,查询数据。
  • RANGE COLUMNS 用法

    RANGE COLUMNS是RANGE分区的一种特殊类型,它与RANGE分区的区别如下:

  • RANGE COLUMNS不接受表达式,只能是列名。而RANGE分区则要求分区的对象是整数。
  • RANGE COLUMNS允许多个列,在底层实现上,它比较的是元祖(多个列值组成的列表),而RANGE比较的是标量,即数值的大小。
  • RANGE COLUMNS不限于整数对象,date,datetime,string都可作为分区列。
  • CREATE TABLE rcx (
        a INT,
        b INT,
        c CHAR(3),
        d INT
    )
    PARTITION BY RANGE COLUMNS(a,d,c) (
        PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
        PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
        PARTITION p2 VALUES LESS THAN (15,30,'sss'),
        PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    );
    

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论