Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Preface

The terms "sharding" and "database splitting" are often mentioned in the database field, and usually refer to similar concepts, but they differ in details. Sharding usually refers to distributing the data in a large database into multiple smaller databases to improve performance and manageability. Database splitting refers more broadly to breaking a database system into smaller parts, which may include sharding, but may also involve other types of splitting, such as sharding tables.

Here is the difference between sharding and database splitting:

Database sharding example

-- 分库示例：将订单信息按照用户ID的哈希值存储到不同的数据库
-- 数据库1
CREATE DATABASE orders_db_1;
USE orders_db_1;
 
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10, 2),
    order_date DATE
);
 
-- 数据库2
CREATE DATABASE orders_db_2;
USE orders_db_2;
 
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10, 2),
    order_date DATE
);

Database Splitting Example

-- 垂直拆分示例：将用户信息表和订单信息表分离到不同的数据库
-- 主数据库
CREATE DATABASE main_database;
USE main_database;
 
-- 用户信息表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);
 
-- 订单信息表
CREATE DATABASE orders_database;
USE orders_database;
 
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10, 2),
    order_date DATE
);

Database sharding and database splitting are used in different scenarios to meet specific business needs and technical challenges:

      Although both database sharding and database splitting can be used to solve the above problems, they focus on different aspects. Database sharding is more inclined to solve the storage and processing of large-scale data, as well as improving the scalability and disaster recovery capabilities of the system. Database splitting, on the other hand, focuses more on optimizing the organization and access mode of data, as well as simplifying the management of the database.