彻底搞清分库分表（垂直分库，垂直分表，水平分库，水平分表）

云游道人 2025-02-02 513 阅读 0评论

分库分表是数据库优化中常见的技术，目的是提高数据库的性能、可扩展性和管理性。通过合理的分库分表策略，可以有效避免单一数据库或单一表的性能瓶颈。

下面我们将深入讨论四种常见的分库分表策略，并通过具体的 SQL 示例来分析它们的工作原理和应用场景。

1. 垂直分库（Vertical Sharding）

概念

垂直分库是指将一个数据库拆分成多个数据库，每个数据库存储不同的业务模块或表。常见的做法是将一些不相关或访问频率不同的表分到不同的数据库中。

应用场景

适用于不同模块的数据访问频率差异较大、业务逻辑完全分离的情况。例如，将用户管理相关的表和订单管理相关的表分别存放到不同的数据库中。

SQL 示例

假设我们有一个电商系统，包含以下两张表：

user：存储用户信息
order：存储订单信息

1.1 创建用户数据库 `user_db`：

CREATE DATABASE user_db;

USE user_db;

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

1.2 创建订单数据库 `order_db`：

CREATE DATABASE order_db;

USE order_db;

CREATE TABLE order (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    status VARCHAR(50)
);

在应用中，user 表和 order 表分别放在不同的数据库中，访问时通过不同的数据库连接来操作不同的业务模块。

2. 垂直分表（Vertical Partitioning）

概念

垂直分表是指将一张表根据列的不同将其拆分成多张表。例如，将一张表中的经常一起查询的列放在一张表中，其他不常查询的列放在另一张表中。

应用场景

适用于一张表有很多列，其中有些列很少访问，而其他列又经常访问的情况。通过垂直分表，可以将常用的列和不常用的列分开存储，减少 IO 和内存消耗。

SQL 示例

假设我们有一个用户表 user，包含多个列，其中有一些列（如用户的基本信息）比较常用，而其他列（如用户的日志、详细信息）较少访问。

2.1 创建 `user_basic` 表：

CREATE TABLE user_basic (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20)
);

2.2 创建 `user_details` 表：

CREATE TABLE user_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    address VARCHAR(255),
    birthday DATE,
    bio TEXT
);

在应用中，常用的 name、email 等信息存放在 user_basic 表中，而较少访问的 address、birthday 等信息存放在 user_details 表中。

3. 水平分库（Horizontal Sharding）

概念

水平分库是指将一张表的数据根据某个字段（如用户 ID、时间等）分散到多个数据库中。每个数据库包含表的不同数据行。常见的做法是根据某些字段的哈希值或者范围进行分库。

应用场景

适用于数据量大且不断增长的场景。通过将数据分散到多个数据库中，可以减少单一数据库的存储压力和访问压力。

SQL 示例

假设我们有一个订单表 order，数据量非常大，可以根据 user_id 或 order_date 等字段进行水平分库。

3.1 创建数据库 `order_db_1` 和 `order_db_2`：

CREATE DATABASE order_db_1;
CREATE DATABASE order_db_2;

3.2 创建 `order` 表（在两个数据库中）：

在 order_db_1 中：

CREATE TABLE order_db_1.order (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    status VARCHAR(50)
);

在 order_db_2 中：

CREATE TABLE order_db_2.order (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    status VARCHAR(50)
);

3.3 数据分库策略

order_db_1 存储 user_id 范围在 1-1000 的数据。
order_db_2 存储 user_id 范围在 1001-2000 的数据。

这种分库策略通常需要在应用层实现路由逻辑，根据 user_id 或其他字段来选择访问哪个数据库。

-- 如果查询 user_id 为 150 的订单
SELECT * FROM order_db_2.order WHERE user_id = 150;

4. 水平分表（Horizontal Partitioning）

概念

水平分表是指将一张表的数据按照某个条件（如时间、ID 等）拆分成多张表，通常是按行的方式进行拆分，每张表存储数据的某个范围。

应用场景

适用于数据量庞大的表，尤其是数据量增长迅速的场景。通过水平分表可以将数据分散到多张表中，从而减少每张表的负担，提高查询性能。

SQL 示例

假设我们有一个订单表 order，数据量很大，可以按时间将表拆分成多个分表，每个分表存储不同时间段的数据。

4.1 创建 `order_2024_01` 和 `order_2024_02` 表：

CREATE TABLE order_2024_01 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    status VARCHAR(50),
    order_date DATE
);

CREATE TABLE order_2024_02 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    status VARCHAR(50),
    order_date DATE
);

4.2 数据分表策略

order_2024_01 表存储 2024 年 1 月份的订单数据。
order_2024_02 表存储 2024 年 2 月份的订单数据。

-- 查询 2024 年 1 月份的订单
SELECT * FROM order_2024_01 WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

-- 查询 2024 年 2 月份的订单
SELECT * FROM order_2024_02 WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29';