MySQL分区表性能对比案例：创建两张表，分别插入100万条记录，一个表分区，一个表不分区...-CSDN博客

为了创建一张表，插入100万条记录，并基于创建时间字段进行分区，我们可以遵循以下步骤：

步骤 1: 创建表

首先，我们需要创建一个表，其中包含一个创建时间字段。假设我们的表名为 sales，包含以下字段：

id: 主键，自增
product: 产品名称
amount: 销售数量
created_at: 创建时间，用于分区

创建表的 SQL 语句如下：

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50) DEFAULT 'testingpartitions',
    amount INT DEFAULT 1,
    created_at DATE NOT NULL
)
PARTITION BY RANGE(YEAR(created_at))
(
    PARTITION p0 VALUES LESS THAN (1995),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (1997),
    PARTITION p3 VALUES LESS THAN (1998),
    PARTITION p4 VALUES LESS THAN (1999),
    PARTITION p5 VALUES LESS THAN (2000),
    PARTITION p6 VALUES LESS THAN (2001),
    PARTITION p7 VALUES LESS THAN (2002),
    PARTITION p8 VALUES LESS THAN (2003),
    PARTITION p9 VALUES LESS THAN (2004),
    PARTITION p10 VALUES LESS THAN (2010),
    PARTITION p11 VALUES LESS THAN MAXVALUE
);

上述SQL中，表的主键没有包含所有用于分区的列，会报错：

调整后的SQL语句如下——

使用复合主键来解决：

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    product VARCHAR(50) DEFAULT 'testingpartitions',
    amount INT DEFAULT 1,
    created_at DATE NOT NULL,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE(YEAR(created_at))
(
    PARTITION p0 VALUES LESS THAN (1995),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (1997),
    PARTITION p3 VALUES LESS THAN (1998),
    PARTITION p4 VALUES LESS THAN (1999),
    PARTITION p5 VALUES LESS THAN (2000),
    PARTITION p6 VALUES LESS THAN (2001),
    PARTITION p7 VALUES LESS THAN (2002),
    PARTITION p8 VALUES LESS THAN (2003),
    PARTITION p9 VALUES LESS THAN (2004),
    PARTITION p10 VALUES LESS THAN (2010),
    PARTITION p11 VALUES LESS THAN MAXVALUE
);

其他解决方案可参考：

步骤 2: 插入100万条记录

接下来，我们需要向表中插入100万条记录。为了提高插入性能，我们可以使用存储过程或者批量插入。这里我们将使用存储过程来逐条插入数据。

创建存储过程

DELIMITER $$

CREATE PROCEDURE load_data()
BEGIN
    DECLARE v INT DEFAULT 0;
    DECLARE start_date DATE DEFAULT '1995-01-01';

    WHILE v < 1000000 DO
        INSERT INTO sales (product, amount, created_at)
        VALUES ('testingpartitions', 1, ADDDATE(start_date, FLOOR((RAND(v) * 36520) % 3652)));
        SET v = v + 1;
    END WHILE;
END $$

DELIMITER ;

调用存储过程

步骤 3: 性能对比测试

为了进行性能对比测试，我们需要创建一个未分区的表，并插入同样的100万条记录。然后，我们将比较查询性能。

创建未分区的表

CREATE TABLE sales_unpartitioned (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50) DEFAULT 'testingpartitions',
    amount INT DEFAULT 1,
    created_at DATE NOT NULL
);

插入数据

DELIMITER $$

CREATE PROCEDURE load_data_unpartitioned()
BEGIN
    DECLARE v INT DEFAULT 0;
    DECLARE start_date DATE DEFAULT '1995-01-01';

    WHILE v < 1000000 DO
        INSERT INTO sales_unpartitioned (product, amount, created_at)
        VALUES ('testingpartitions', 1, ADDDATE(start_date, FLOOR((RAND(v) * 36520) % 3652)));
        SET v = v + 1;
    END WHILE;
END $$

DELIMITER ;

调用存储过程

步骤 4: 查询性能对比

为了比较查询性能，我们将执行一些查询并记录执行时间。我们将使用 EXPLAIN 和 EXPLAIN EXTENDED 来查看查询计划和实际执行情况。

查询示例

查询2000年的数据：

SELECT COUNT(*) FROM sales WHERE YEAR(created_at) = 2000;
SELECT COUNT(*) FROM sales_unpartitioned WHERE YEAR(created_at) = 2000;

测试方法

记录查询时间：使用 EXPLAIN 或 EXPLAIN EXTENDED 记录查询计划。
使用 BENCHMARK：MySQL 提供了一个内置函数 BENCHMARK()，可以用来重复执行一个表达式，这对于测试查询执行时间非常有用。

使用 `BENCHMARK` 测试查询性能

SELECT BENCHMARK(1000, (SELECT COUNT(*) FROM sales WHERE YEAR(created_at) = 2000)) AS partitioned;

SELECT BENCHMARK(1000, (SELECT COUNT(*) FROM sales_unpartitioned WHERE YEAR(created_at) = 2000)) AS unpartitioned;

这里，BENCHMARK(1000, ...) 表示执行查询1000次，并返回执行次数。通过比较两个查询的执行时间，我们可以得出性能差异。