首页 > 数据库 >MySQL实战宝典 索引调优篇 14 分区表:哪些场景不建议用分区表

MySQL实战宝典 索引调优篇 14 分区表:哪些场景不建议用分区表

时间:2023-01-18 14:13:05浏览次数:59  
标签:01 14 分区 PARTITION 索引 分区表 VALUES MySQL

MySQL实战宝典 索引调优篇 14 分区表:哪些场景不建议用分区表

 

前面几节,通过索引的原理,索引覆盖的使用,结合复杂SQL的调优,学习了索引设计的各个方面,接下来聊聊分区表的设计,用来对数据进行物理分区。

分区表即涉及表结构设计,也涉及了索引的设计,以及一个数据库上的哲学问题:是否要使用分区表?

分区表的使用

简单来说,分区表就是把物理表结构相同的几张表,通过一定算法,组成一张逻辑大表。这种算法叫“分区函数”,当前MySQL数据库支持的分区函数类型有RANGE、LIST、HASH、KEY、COLUMNS。

无论选择哪种分区函数,都要指定相关列成为分区算法的输入条件,这些列就叫“分区列”。另外,在MySQL分区表中,主键也必须是分区列的一部分,不然创建分区表会失败,比如:

CREATE TABLE t (
    a INT,
    b INT,
    c DATETIME(6),
    d VARCHAR(32),
    e INT,
    PRIMARY KEY (a,b)
)
partition by range columns(c) (
    PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
    PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
    PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
    PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

上面创建了表 t,主键是复合索引,由列 a、b 组成。表 t 创建分区表的意图是根据列 c(时间列)拆分数据,把不同时间数据存放到不同分区中。

而我们可以从错误的提示中看到:分区表的主键一定要包含分区函数的列。所以,要创建基于列c 的数据分片的分区表,主键必须包含列 c,比如下面的建表语句:

CREATE TABLE t (
    a INT,
    b INT,
    c DATETIME,
    d VARCHAR(32),
    e INT,
    PRIMARY KEY (a,b,c),
    KEY idx_e (e)
)
partition by range columns(c) (
    PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
    PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
    PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
    PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);

创建完表后,在物理存储上会看到四个分区所对应 ibd 文件,也就是把数据根据时间列 c 存储到对应的 4 个文件中:

t#p#p0000.ibd  t#p#p2019.ibd  t#p#p2020.ibd  t#p#p9999.ibd

所以,你要理解的是:MySQL 中的分区表是把一张大表拆成了多张表,每张表有自己的索引,从逻辑上看是一张表,但物理上存储在不同文件中。

分区表注意事项:唯一索引

在MySQL数据库中,分区表的索引都是局部而非全局的。也就是说,索引在每个分区文件中都是独立的,所以分区表上的唯一索引必须包含分区列信息,否则创建会报错:

ALTER TABLE t ADD UNIQUE KEY idx_d(d);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).

你可以看到错误提示: 唯一索引必须包含分区函数中所有列。而下面的创建才能成功:

ALTER TABLE t ADD UNIQUE KEY idx_d(d,c);

但是,正因为唯一索引包含了分区列,唯一索引也就变成仅在当前分区唯一,而不是全局唯一了。那么对于上面的表 t,插入下面这两条记录都是可以的:

INSERT INTO t VALUES 
(1,1,'2021-01-01','aaa',1),
(1,1,'2020-01-01','aaa',1);
SELECT * FROM t;
+---+---+---------------------+------+------+
| a | b | c                   | d    | e    |
+---+---+---------------------+------+------+
| 1 | 1 | 2020-01-01 00:00:00 |aaa   |    1 |
| 1 | 1 | 2021-01-01 00:00:00 |aaa   |    1 |
+---+---+---------------------+------+------+

你可以看到,列 d 都是字符串‘aaa’,但依然可以插入。这样带来的影响是列 d 并不是唯一的,所以你要由当前分区唯一实现全局唯一。

那如何实现全局唯一索引呢? 和之前表结构设计时一样,唯一索引使用全局唯一的字符串(如类似 UUID 的实现),这样就能避免局部唯一的问题。

分区表的误区:性能提升

很多同学会认为,分区表是把一张大表拆分成多张小表,所以这样MySQL数据库的性能就会大幅提升。这是错误的认识!

分区表技术并不能提升MySQL数据库的性能,而是用来进行方面管理数据的。

分区表也可能会引入新的性能问题,比如说非分区列的查询。即使分区列上已经创建的索引,但因为索引是每个分区文件对应的本地索引,所以需要查询每个分区。可以看如下SQL及其执行计划:

mysql> EXPLAIN SELECT * FROM t WHERE d = 'aaa';
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions              | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | p0000,p2019,p2020,p9999 | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

通过执行计划我们可以看到:上述 SQL 需要访问 4 个分区,假设每个分区需要 3 次 I/O,则这条 SQL 总共要 12 次 I/O。但是,如果使用普通表,记录数再多,也就 4 次的 I/O 的时间。

所以,分区表设计时,务必明白你的查询条件都带有分区字段,否则会扫描所有分区的数据或索引。所以,分区表设计不解决性能问题,更多的是解决数据迁移和备份的问题。

而为了让你更好理解分区表的使用,我们继续看一个真实业务的分区表设计。

分区表在业务上的设计

以电商中的订单表orders为例,如果在类似淘宝的海量互联网业务中,orders表的数据量会巨大,假设每天产生5000万条的订单,那么一年表orders就有仅18亿的记录。

所以对于订单表,在数据库中通常只保存最近一年甚至更短时间的数据,而历史订单数据会如历史库,除非存在1年以上退款的订单,大部分订单一旦完成,这些数据从业务角度就没有用了。

那么如果你想方便管理订单表中的数据,可以对表orders按年创建分区表,如:

CREATE TABLE `orders` (
  `o_orderkey` int NOT NULL,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_CLERK` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`o_orderkey`,`O_ORDERDATE`),
  KEY `orders_fk1` (`O_CUSTKEY`),
  KEY `idx_orderdate` (`O_ORDERDATE`)
)
PARTITION BY RANGE  COLUMNS(o_orderdate)
(
  PARTITION p0000 VALUES LESS THAN ('1992-01-01') ENGINE = InnoDB,
  PARTITION p1992 VALUES LESS THAN ('1993-01-01') ENGINE = InnoDB,
  PARTITION p1993 VALUES LESS THAN ('1994-01-01') ENGINE = InnoDB,
  PARTITION p1994 VALUES LESS THAN ('1995-01-01') ENGINE = InnoDB,
  PARTITION p1995 VALUES LESS THAN ('1996-01-01') ENGINE = InnoDB,
  PARTITION p1996 VALUES LESS THAN ('1997-01-01') ENGINE = InnoDB,
  PARTITION p1997 VALUES LESS THAN ('1998-01-01') ENGINE = InnoDB,
  PARTITION p1998 VALUES LESS THAN ('1999-01-01') ENGINE = InnoDB,
  PARTITION p9999 VALUES LESS THAN (MAXVALUE)
)

你可以看到,这时 Orders 表的主键修改为了(o_orderkey,O_ORDERDATE),数据按照年进行分区存储。那么如果要删除 1 年前的数据,比如删除 1998 年的数据,之前需要使用下面的 SQL,比如:

DELETE FROM orders WHERE o_orderdate >= '1998-01-01' AND o_orderdate <= '1999-01-01';

可这条 SQL 的执行相当慢,产生大量二进制日志,在生产系统上,也会导致数据库主从延迟的问题。而使用分区表的话,对于数据的管理就容易多了,你直接使用清空分区的命令就行:

ALTER TABLE orders_par TRUNCATE PARTITION p1998;

上述 SQL 执行速度非常快,因为实际执行过程是把分区文件删除和重建。另外产生的日志也只有一条 DDL 日志,也不会导致主从复制延迟问题。

# at 425
#210328 12:10:12 server id 8888  end_log_pos 549        Query   thread_id=9     exec_time=0     error_code=0    Xid = 10
SET TIMESTAMP=1619583012/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
ALTER TABLE orders TRUNCATE PARTITION p1998
/*!*/;

总结

  • 当前MySQL的分区表支持RANGE、LIST、HASH、KEY、COLUMNS的分区算法;
  • 分区表的创建需要主键包含在分区列中;
  • 在分区表表中,唯一索引仅在当前分区文件中唯一,全局不唯一;
  • 分区表唯一索引推荐使用类似UUID的全局唯一实现;
  • 分区表不解决性能问题,如果使用非分区列查询,性能反而会更差;
  • 推荐分区表用于数据管理,速度快,日志少;

再次强调:分区表并不是用于提升性能的手段,而是方便数据管理的一种方式

标签:01,14,分区,PARTITION,索引,分区表,VALUES,MySQL
From: https://www.cnblogs.com/yaoyangding/p/17059679.html

相关文章

  • linux安装mysql8后的登陆问题
    通过宝塔面板快速安装了mysql8,然后到了要登陆mysql的时候想起来不知道密码,这时候就要强制重置mysql密码了。修改/etc/my.cnf中的mysql配置,添加一句话来跳过密码验证然......
  • Azure ARM (27) 自定义Role,只能创建MySQL Flexible Server资源
    《WindowsAzurePlatform系列文章目录》 用户这里有个新的需求,自定义Role,只能创建MySQLFlexibleServer资源和PrivateDNSZone,不能创建资源资源。可以......
  • MySQL性能优化浅析及线上案例
    作者:京东健康孟飞1、数据库性能优化的意义业务发展初期,数据库中量一般都不高,也不太容易出一些性能问题或者出的问题也不大,但是当数据库的量级达到一定规模之后,如果缺失有效......
  • Docker下安装MySQL8与开启root远程访问
    1.dockersearchmysql#查询mysql版本2.dockerpullmysql:8.0.15#拉取mysql3.创建mysql容器dockerstopmysql_8dockerrmmysql_8dockerrun-d-eTZ=“Asia/Shan......
  • mysql5.7彻底解决sql_mode=only_full_group_by
    打开配置文件windows在安装目录下的my.ini文件Linux在/etc/my.cnf分别在[mysqld]下面添加这段:sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FO......
  • mysql查询怎么区分大小写
    mysql中查询默认是区分大小写的,但是在mysql中默认不区分大小写。解决办法:mysql可以在SQL语句中加入binary来区分大小写。BINARY不是函数,是类型转换运算符,它用来强制它后......
  • 这项评测,华为云GaussDB(for MySQL)顺利通过
    摘要:近日,中国信息通信研究院(简称“中国信通院”)公布了第十五批“可信数据库”评测结果。华为云GaussDB(forMySQL)凭借过硬的技术实力顺利通过“HTAP数据库基础能力评测”......
  • 这项评测,华为云GaussDB(for MySQL)顺利通过
    摘要:近日,中国信息通信研究院(简称“中国信通院”)公布了第十五批“可信数据库”评测结果。华为云GaussDB(forMySQL)凭借过硬的技术实力顺利通过“HTAP数据库基础能力评测”。......
  • MySQL必知必会第十二章-汇总数据
    汇总数据聚集函数聚集函数(aggregatefunction)运行在行组上,计算和返回单个值的函数。函数说明AVG()返回某列的平均值COUNT()返回某列的行数MAX()返......
  • MySQL事务的隔离级别
     令人惊讶的是,大部分数据库系统都没有提供真正的隔离性,最初或许是因为系统实现者并没有真正理解这些问题。如今这些问题已经弄清楚了,但是数据库实现者在正确性和性能之间......