我们都知道,在Mysql 中,如果数据量过大的话,就有可能在查询过程中会出现各种超时的情况,毕竟如果一个表的数据量过大的时候,一个简单的单表查询都会有点慢,所以,就有了各种中间件的存在,比如说 MyCat,ShardingJDBC 等分库工具,但是今天了不起不说这个,我们来说说这个Mysql自己的分区,我们不做分库操作。
MySQL 的数据分区(Partitioning)是一个数据库功能,允许将一个表物理地分成多个独立的、更小的、更易于管理的片段,这些片段被称为分区。每个分区在逻辑上都是表的一部分,但在物理存储上,每个分区可以有自己的存储引擎、文件、索引等。
为什么要使用分区?
MySQL 支持多种分区方法,包括:
我们来详细说说这个分区的区别
RANGE分区
LIST分区
HASH分区
KEY分区
COLUMNS分区
我们来看一下个示例,
假设我们有一个名为 sales 的表,它记录了销售数据,并且我们想要基于 sale_date 列进行 RANGE 分区。
CREATE TABLE sales (
sale_id VARCHAR(100) NOT NULL,
sale_name VARCHAR(100) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
dsYear VARCHAR(20) NOT NULL
)
PARTITION BY RANGE COLUMNS(dsYear) (
PARTITION p0 VALUES LESS THAN ('2021'),
PARTITION p1 VALUES LESS THAN ('2022'),
PARTITION p2 VALUES LESS THAN ('2023'),
PARTITION p3 VALUES LESS THAN ('2024')
);
在这个例子中,我们根据 dsYear 列的年份值将数据分成多个分区。注意 MAXVALUE 的使用,它表示最大的可能的值。
如果我们进行了分区,那么就要在查询中适当的去增加分区查询条件,和分库是一样的,查询的时候需要命中分库规则,这样的话,就不会进行全表的扫描。
然后我们创建一大笔数据,这样去查询一下,我们先来造数据,直接用存储过程来造数据,如果你使用的Navicat16,那你可以使用他自带的造数据的功能。
脚本如下:
CREATE DEFINER=`root`@`%` PROCEDURE `CreateTestData`()
BEGIN
DECLARE v_counter INT DEFAULT 1;
WHILE v_counter <= 1000000 DO
INSERT INTO sales(sale_id, sale_name, amount,dsYear)
VALUES(UUID_SHORT(), CONCAT('Data', v_counter), v_counter,'2021');
SET v_counter = v_counter + 1;
END WHILE;
END
我们这时候就可以通过查询是否命中分区来看一下查询速度了。
不过这种创建数据的方式那可是非常的耗时间的。
我们来对比一下查询分区和不涉及分区的情况
这个速度的差距明显就显现出来了,一共200w的数据,所以,利用好分区是非常有必要的。
不是所有的存储引擎都支持分区。例如,MyISAM 和 InnoDB 支持分区,但 MEMORY 存储引擎不支持。
分区键必须是表的一个列或表达式的组合,且必须是整数类型、返回整数值的表达式或 DATE/DATETIME 列。
分区表可能有一些限制和注意事项,例如,某些类型的索引可能不支持,或者某些查询优化可能不适用于分区表。因此,在决定使用分区之前,最好先详细了解这些限制和注意事项。
所以,你对Mysql 的分区了解了么?