MySQL性能优化与索引之：`MySQL`的大表优化：分库分表、归档和分区表的选择。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 大表优化：分库分表、归档和分区表的选择

大家好，今天我们来聊聊 MySQL 大表优化。随着业务的发展，数据量会不断增长，单表的数据量达到百万甚至千万级别时，查询效率会显著下降，甚至会影响整个系统的性能。因此，我们需要对大表进行优化。常见的优化手段包括分库分表、数据归档和分区表。那么，面对不同的场景，我们应该如何选择合适的方案呢？

一、了解大表带来的问题

首先，我们需要明确大表会带来哪些问题：

查询效率降低： 扫描大量数据，导致查询速度慢。
索引失效： 索引维护成本高，索引效果下降。
锁竞争激烈： 并发读写操作争夺锁资源，导致性能瓶颈。
备份恢复困难： 备份和恢复时间过长，影响业务连续性。
硬件资源消耗： 占用大量的磁盘空间和内存资源。

二、分库分表

分库分表是将一个大表的数据分散到多个数据库或多个表中，从而降低单表的数据量，提高查询效率。

1. 水平分表 (Sharding):

将一个大表的数据按照某种规则分散到多个结构相同的表中。例如，按照用户ID进行哈希取模，将用户数据分散到不同的用户表中。

优点：
- 降低单表数据量，提高查询效率。
- 缓解锁竞争，提高并发能力。
- 更容易进行备份和恢复。
缺点：
- 增加数据管理的复杂度。
- 跨分片查询需要进行聚合操作。
- 需要考虑数据迁移和扩容的问题。

2. 垂直分表:

将一个大表中不常用的字段拆分到另外一张表中。例如，将用户基本信息和用户详细信息分别存储在不同的表中。

优点：
- 减少单表字段数量，提高查询效率。
- 更容易进行数据维护。
缺点：
- 增加表连接操作，可能影响查询效率。
- 需要保证数据一致性。

3. 分库:

将多个表分散到不同的数据库中。

优点：
- 提高数据库的并发能力。
- 缓解数据库的压力。
缺点：
- 增加数据管理的复杂度。
- 跨库查询需要进行分布式事务管理。

选择分库分表策略的考虑因素：

数据量： 如果数据量非常大，建议采用水平分表或分库。
查询模式： 如果查询主要集中在某个特定的字段上，可以考虑按照该字段进行分表。
业务需求： 根据业务需求选择合适的分表策略。
技术复杂度： 分库分表会增加系统的复杂度，需要评估技术团队的能力。

示例：水平分表 (基于用户ID的哈希取模)

假设我们有一个用户表 users，数据量非常大，我们可以按照用户ID进行哈希取模，将用户数据分散到 16 个表中，表名为 users_00 到 users_15。

-- 创建 users_00 到 users_15 表
CREATE TABLE users_00 (
    id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    ...
);

CREATE TABLE users_01 LIKE users_00;
...
CREATE TABLE users_15 LIKE users_00;

-- 查询用户数据
-- 假设用户ID为 123456789
-- 计算分表索引
SET @shard_index = 123456789 % 16;

-- 构造查询语句
SET @sql = CONCAT('SELECT * FROM users_', LPAD(@shard_index, 2, '0'), ' WHERE id = 123456789');

-- 执行查询语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

代码解释：

CREATE TABLE users_00 ...：创建表 users_00，定义了表的结构，包括 id、username、email 等字段。
CREATE TABLE users_01 LIKE users_00; ... CREATE TABLE users_15 LIKE users_00;：使用 LIKE 关键字，创建了与 users_00 结构相同的表 users_01 到 users_15。
SET @shard_index = 123456789 % 16;：计算用户ID 123456789 对 16 取模的结果，得到分表索引。
SET @sql = CONCAT(...);：构造动态 SQL 语句，根据分表索引拼接表名。
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;：预编译 SQL 语句，执行查询，并释放预编译语句。

更优雅的方案：使用存储过程简化分表逻辑

DELIMITER //

CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
    DECLARE shard_index INT;
    DECLARE sql_stmt TEXT;
    DECLARE table_name VARCHAR(20);

    SET shard_index = user_id % 16;
    SET table_name = CONCAT('users_', LPAD(shard_index, 2, '0'));
    SET sql_stmt = CONCAT('SELECT * FROM ', table_name, ' WHERE id = ', user_id);

    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 调用存储过程
CALL get_user_by_id(123456789);

代码解释：

DELIMITER //：修改语句分隔符，避免与存储过程内部的 ; 冲突。
CREATE PROCEDURE get_user_by_id(IN user_id INT)：创建存储过程 get_user_by_id，接收用户ID作为输入参数。
DECLARE ...：声明局部变量，用于存储分表索引、SQL 语句和表名。
SET shard_index = user_id % 16;：计算分表索引。
SET table_name = CONCAT(...);：构造表名。
SET sql_stmt = CONCAT(...);：构造动态 SQL 语句。
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;：预编译 SQL 语句，执行查询，并释放预编译语句。
DELIMITER ;：恢复语句分隔符。
CALL get_user_by_id(123456789);：调用存储过程，传入用户ID。

总结：分库分表适合数据量巨大，且需要高并发读写的场景。需要仔细设计分片策略，并考虑数据迁移和扩容的方案。

三、数据归档

数据归档是将历史数据从主表中转移到归档表中，从而减少主表的数据量，提高查询效率。

优点：
- 降低主表的数据量，提高查询效率。
- 减少主表的存储空间。
缺点：
- 需要定期进行数据归档。
- 查询历史数据需要访问归档表。

选择数据归档策略的考虑因素：

数据访问频率： 如果历史数据很少被访问，可以考虑进行归档。
数据保留时间： 根据业务需求确定数据的保留时间。
归档频率： 根据数据增长速度和访问频率确定归档频率。

示例：基于时间的数据归档

假设我们有一个订单表 orders，我们需要将超过一年的订单数据归档到 orders_archive 表中。

-- 创建归档表
CREATE TABLE orders_archive LIKE orders;

-- 归档数据
INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- 删除主表中的数据
DELETE FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

代码解释：

CREATE TABLE orders_archive LIKE orders;：创建与 orders 表结构相同的 orders_archive 表，用于存储归档数据。
INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);：将 orders 表中 order_date 小于一年前的数据插入到 orders_archive 表中。
DELETE FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);：从 orders 表中删除 order_date 小于一年前的数据。

需要注意的是，归档操作需要考虑数据一致性，避免在归档过程中发生数据丢失或错误。

更安全的归档方案：使用事务

START TRANSACTION;

INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

DELETE FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

COMMIT;

代码解释：

START TRANSACTION;：开始事务，确保归档操作的原子性。
INSERT INTO orders_archive ...：将数据插入到归档表。
DELETE FROM orders ...：从主表中删除数据。
COMMIT;：提交事务，确保数据一致性。

如果在执行过程中发生错误，可以使用 ROLLBACK; 回滚事务，撤销之前的操作。

总结：数据归档适合历史数据访问频率较低的场景，可以有效减少主表的数据量，提高查询效率。

四、分区表

分区表是将一个大表的数据按照某种规则划分成多个逻辑分区，每个分区可以独立存储，但对外仍然表现为一个表。

优点：
- 提高查询效率：查询时可以只扫描相关的分区。
- 方便数据管理：可以独立管理每个分区，例如备份、恢复、删除等。
缺点：
- 分区数量过多会影响性能。
- 分区策略需要根据业务需求进行选择。

常见的分区类型：

RANGE 分区： 按照范围进行分区，例如按照时间范围或数值范围。
LIST 分区： 按照枚举值进行分区，例如按照地区或状态。
HASH 分区： 按照哈希值进行分区，例如按照用户ID。
KEY 分区： 类似于 HASH 分区，但由 MySQL 自动管理哈希值。

选择分区策略的考虑因素：

查询模式： 根据查询模式选择合适的分区策略，例如经常按照时间范围查询，可以选择 RANGE 分区。
数据分布： 考虑数据的分布情况，避免数据倾斜。
分区数量： 避免分区数量过多，一般建议不超过 100 个。

示例：基于时间范围的 RANGE 分区

假设我们有一个订单表 orders，我们需要按照月份进行分区。

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION p202304 VALUES LESS THAN (202305),
    PARTITION p202305 VALUES LESS THAN (202306),
    PARTITION p202306 VALUES LESS THAN (202307),
    PARTITION p202307 VALUES LESS THAN (202308),
    PARTITION p202308 VALUES LESS THAN (202309),
    PARTITION p202309 VALUES LESS THAN (202310),
    PARTITION p202310 VALUES LESS THAN (202311),
    PARTITION p202311 VALUES LESS THAN (202312),
    PARTITION p202312 VALUES LESS THAN (202401)
);

-- 查询 2023 年 1 月份的订单数据
SELECT * FROM orders PARTITION (p202301);

-- 添加新的分区 (例如 2024 年 1 月份)
ALTER TABLE orders ADD PARTITION (PARTITION p202401 VALUES LESS THAN (202402));

代码解释：

PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date))：指定按照 order_date 的年份和月份进行 RANGE 分区。
PARTITION p202301 VALUES LESS THAN (202302)：创建分区 p202301，存储 order_date 在 2023 年 1 月份的数据。
SELECT * FROM orders PARTITION (p202301);：查询 p202301 分区的数据。
ALTER TABLE orders ADD PARTITION (PARTITION p202401 VALUES LESS THAN (202402));：添加新的分区。

更方便的分区管理：使用存储过程自动创建分区

DELIMITER //

CREATE PROCEDURE create_monthly_partitions(IN table_name VARCHAR(255), IN start_year INT, IN start_month INT, IN num_months INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE current_year INT;
    DECLARE current_month INT;
    DECLARE partition_name VARCHAR(255);
    DECLARE partition_value INT;
    DECLARE next_partition_value INT;
    DECLARE sql_stmt TEXT;

    SET current_year = start_year;
    SET current_month = start_month;

    WHILE i < num_months DO
        SET partition_name = CONCAT('p', current_year, LPAD(current_month, 2, '0'));
        SET partition_value = current_year * 100 + current_month;

        SET current_month = current_month + 1;
        IF current_month > 12 THEN
            SET current_month = 1;
            SET current_year = current_year + 1;
        END IF;

        SET next_partition_value = current_year * 100 + current_month;
        SET sql_stmt = CONCAT('ALTER TABLE ', table_name, ' ADD PARTITION (PARTITION ', partition_name, ' VALUES LESS THAN (', next_partition_value, '))');

        SET @sql = sql_stmt;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

-- 调用存储过程，创建 2024 年 1 月份到 2024 年 12 月份的分区
CALL create_monthly_partitions('orders', 2024, 1, 12);

代码解释：

CREATE PROCEDURE create_monthly_partitions(...)：创建存储过程 create_monthly_partitions，接收表名、起始年份、起始月份和分区数量作为输入参数。
DECLARE ...：声明局部变量。
WHILE i < num_months DO ... END WHILE;：循环创建分区。
SET partition_name = CONCAT(...);：构造分区名。
SET partition_value = current_year * 100 + current_month;：计算分区值。
SET next_partition_value = ...;：计算下一个分区的值。
SET sql_stmt = CONCAT(...);：构造动态 SQL 语句。
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;：预编译 SQL 语句，执行添加分区操作，并释放预编译语句。
CALL create_monthly_partitions('orders', 2024, 1, 12);：调用存储过程，创建分区。

总结：分区表适合需要按照特定规则进行查询和管理的场景。选择合适的分区策略可以提高查询效率，方便数据管理。

五、如何选择合适的优化方案

选择合适的优化方案需要综合考虑以下因素：

方案	适用场景	优点	缺点
分库分表	数据量巨大，高并发读写，需要水平扩展	降低单表数据量，提高查询效率，缓解锁竞争，更容易备份和恢复	增加数据管理的复杂度，跨分片查询需要聚合操作，需要考虑数据迁移和扩容
数据归档	历史数据访问频率较低，需要减少主表数据量	降低主表数据量，提高查询效率，减少主表的存储空间	需要定期进行数据归档，查询历史数据需要访问归档表
分区表	需要按照特定规则进行查询和管理，例如按照时间范围查询	提高查询效率，方便数据管理，可以独立管理每个分区	分区数量过多会影响性能，分区策略需要根据业务需求进行选择

一些建议：

优先考虑分区表： 如果业务需求允许，优先考虑分区表，因为它的实现相对简单，对应用程序的侵入性也较小。
谨慎使用分库分表： 分库分表会增加系统的复杂度，需要仔细设计分片策略，并考虑数据迁移和扩容的方案。
结合使用多种方案： 在实际应用中，可以结合使用多种优化方案，例如先使用分区表，再对历史数据进行归档。

六、针对数据量持续增长，优化策略也需要持续调整

需要根据业务发展和数据增长情况，不断调整优化策略。例如，如果分区表的分区数量过多，可以考虑进行分库分表。

总之，MySQL 大表优化是一个持续的过程，需要根据实际情况进行选择和调整，才能达到最佳的性能。

MySQL 大表优化：分库分表、归档和分区表的选择

一、 了解大表带来的问题

二、 分库分表

三、 数据归档

四、 分区表

五、 如何选择合适的优化方案

六、 针对数据量持续增长，优化策略也需要持续调整

发表回复 取消回复

一、了解大表带来的问题

二、分库分表

三、数据归档

四、分区表

五、如何选择合适的优化方案

六、针对数据量持续增长，优化策略也需要持续调整

发表回复取消回复