mysql 分表后怎么汇总查询

原创

mob64ca12f37e8a 2024-08-30 07:35:10 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12f37e8a的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 分表后如何汇总查询

在实际项目中，随着数据的不断增长，单一表的性能可能变得难以承受。分表（Sharding）是一种常用的解决方案，它将数据拆分到多个表中，以便提高查询效率。然而，分表后汇总查询往往成了一大挑战。有些开发者在这一过程中容易陷入困境，本篇文章将通过示例来解决这一问题。

实际问题描述

考虑一个电商平台的用户订单数据，原始的订单表 orders 包含字段：id, user_id, product_id, amount, created_at。随着用户和订单数量的剧增，我们决定按照用户 ID 对订单表进行分表。于是，我们创建了如下的分表结构：

orders_0
orders_1
orders_2
orders_3

每个子表负责一部分用户的订单数据，具体划分可以为用户 ID 除以 4 的余数来决定。

表结构示例

以下是每个分表的结构：

CREATE TABLE orders_0 (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    amount DECIMAL(10, 2),
    created_at DATETIME
);

CREATE TABLE orders_1 (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    amount DECIMAL(10, 2),
    created_at DATETIME
);

CREATE TABLE orders_2 (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    amount DECIMAL(10, 2),
    created_at DATETIME
);

CREATE TABLE orders_3 (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    amount DECIMAL(10, 2),
    created_at DATETIME
);

汇总查询的实现

假设我们需要统计某个时间范围内，所有订单的总金额。因为数据分布在四个表中，直接查询会很麻烦。因此，我们可以利用 SQL 语句的 UNION ALL 操作符来完成汇总查询。

以下是实现示例：

SELECT SUM(amount) AS total_amount
FROM (
    SELECT amount FROM orders_0 WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31'
    UNION ALL
    SELECT amount FROM orders_1 WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31'
    UNION ALL
    SELECT amount FROM orders_2 WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31'
    UNION ALL
    SELECT amount FROM orders_3 WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31'
) AS combined_orders;

在这个查询中，我们分别从每个分表中提取在指定时间范围内的订单金额，然后使用 UNION ALL 合并这些结果，最后进行总和计算。

代码结构类图

为了更清晰地理解代码结构，我们用类图进行可视化展示：

classDiagram
    class Order {
        +int id
        +int user_id
        +int product_id
        +decimal amount
        +datetime created_at
    }
    class OrdersDB {
        +void createOrder(Order order)
        +decimal getTotalAmount(string startDate, string endDate)
    }