MySQL怎样优化大表查询 分区表/物化视图/查询重写方案

MySQL大表查询优化是数据库运维和开发中的常见需求，当单表数据量超过千万级后，即使有合适的索引，查询响应时间也可能无法满足业务要求，此时需要从数据存储结构、查询执行逻辑等层面进行优化。

一、分区表优化方案

分区表是将一张大表按照指定规则拆分成多个小的物理存储单元，查询时MySQL会自动只扫描符合条件的分区，减少数据扫描量。常见的分区类型有范围分区、列表分区、哈希分区等，其中范围分区最适合按时间维度存储的大表。

1. 范围分区表创建示例

假设有一张存储用户操作日志的大表，按照操作时间按年分区：

-- 创建分区表，按操作时间范围分区
CREATE TABLE user_operation_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    operation_type VARCHAR(20) NOT NULL,
    operation_time DATETIME NOT NULL,
    operation_content TEXT
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(operation_time)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 插入测试数据
INSERT INTO user_operation_log (user_id, operation_type, operation_time, operation_content)
VALUES (1001, 'login', '2023-05-10 14:30:00', '用户登录操作');

-- 查询2023年的操作日志，只会扫描p2023分区
SELECT * FROM user_operation_log WHERE operation_time BETWEEN '2023-01-01' AND '2023-12-31';

2. 分区表注意事项

• 分区键必须是主键或者唯一索引的一部分，否则创建分区表会失败
• 范围分区适合查询条件中经常包含分区键范围的场景，否则无法触发分区裁剪
• 分区数量不宜过多，一般建议单表分区数不超过100个，避免元数据管理开销过大

二、物化视图优化方案

MySQL本身不支持原生物化视图，但可以通过定时任务维护汇总表来模拟物化视图的效果。物化视图适合存储预计算的聚合结果，比如按天汇总的用户操作次数，查询时直接读取汇总表，避免对大表进行全表聚合。

1. 模拟物化视图实现示例

基于上面的用户操作日志表，创建按天汇总的物化视图表：

-- 创建物化视图汇总表
CREATE TABLE user_operation_daily_summary (
    summary_date DATE PRIMARY KEY,
    user_id INT,
    total_operations INT,
    UNIQUE KEY idx_user_date (user_id, summary_date)
) ENGINE=InnoDB;

-- 初始化全量数据
INSERT INTO user_operation_daily_summary (summary_date, user_id, total_operations)
SELECT DATE(operation_time) AS summary_date, user_id, COUNT(*) AS total_operations
FROM user_operation_log
GROUP BY DATE(operation_time), user_id;

-- 创建定时事件，每天凌晨更新前一天的汇总数据
DELIMITER //
CREATE EVENT update_daily_summary
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO
BEGIN
    DECLARE yesterday DATE;
    SET yesterday = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
    -- 删除前一天的旧数据
    DELETE FROM user_operation_daily_summary WHERE summary_date = yesterday;
    -- 插入前一天的新汇总数据
    INSERT INTO user_operation_daily_summary (summary_date, user_id, total_operations)
    SELECT DATE(operation_time) AS summary_date, user_id, COUNT(*) AS total_operations
    FROM user_operation_log
    WHERE DATE(operation_time) = yesterday
    GROUP BY DATE(operation_time), user_id;
END //
DELIMITER ;

-- 查询用户1001在2023年5月的每日操作次数，直接读汇总表
SELECT summary_date, total_operations
FROM user_operation_daily_summary
WHERE user_id = 1001 AND summary_date BETWEEN '2023-05-01' AND '2023-05-31';

2. 物化视图适用场景

• 查询以聚合类需求为主，比如求和、计数、平均值等
• 数据更新频率不高，或者可以接受一定的数据延迟
• 汇总表的查询频率远高于原始大表的查询频率

三、查询重写优化方案

查询重写是通过调整SQL语句的写法，或者利用MySQL的查询重写插件，改变查询的执行逻辑，避免低效的操作。常见的重写方式包括避免SELECT *、优化子查询、利用覆盖索引等。

1. 常见查询重写示例

以下是几个典型的大表查询重写场景：

-- 原查询：子查询效率低
SELECT * FROM user_operation_log
WHERE user_id IN (SELECT user_id FROM user_info WHERE user_level = 'VIP');

-- 重写后：使用JOIN替代子查询，效率更高
SELECT l.* FROM user_operation_log l
INNER JOIN user_info u ON l.user_id = u.user_id
WHERE u.user_level = 'VIP';

-- 原查询：SELECT * 导致回表，效率低
SELECT * FROM user_operation_log WHERE user_id = 1001 AND operation_type = 'login';

-- 重写后：使用覆盖索引，避免回表
-- 先创建覆盖索引
CREATE INDEX idx_user_type ON user_operation_log (user_id, operation_type, operation_time);
-- 只查询索引包含的字段，触发覆盖索引
SELECT user_id, operation_type, operation_time FROM user_operation_log
WHERE user_id = 1001 AND operation_type = 'login';

2. 查询重写插件使用

MySQL 5.7及以上版本支持查询重写插件，可以自动将符合规则的查询重写为更优的写法：

-- 安装查询重写插件
INSTALL PLUGIN rewrite_example SONAME 'rewrite_example.so';

-- 添加重写规则：将查询SELECT id FROM user_operation_log WHERE user_id=?重写为使用覆盖索引的查询
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, pattern_database)
VALUES (
    'SELECT id FROM user_operation_log WHERE user_id = ?',
    'SELECT id FROM user_operation_log USE INDEX (idx_user_type) WHERE user_id = ?',
    'test_db'
);

-- 刷新重写规则使其生效
CALL query_rewrite.flush_rewrite_rules();

四、三种方案对比

不同优化方案的适用场景和特点差异较大，可通过下表快速选择：

实际优化过程中， often可以组合使用多种方案，比如先对大表做分区，再针对高频聚合查询创建物化视图，同时对剩余的点查进行查询重写，这样能最大化提升查询效率。

MySQL分区表物化视图查询重写大表查询优化修改时间：2026-07-21 04:21:34