SoFunction
Updated on 2025-04-14

Summary of the application of MySQL partition and library partitioning strategy

MySQL partitioning and library partitioning strategies

In application scenarios with large data volume, complex queries and high concurrency, a single database often finds difficult to meet the performance and scalability requirements. To solve these problems, MySQL provides two common horizontal splitting strategies: Partitioning and Sharding. This article will introduce the basic concepts, implementation methods, advantages and disadvantages of these two strategies in detail, and demonstrate how to apply them in a project through practical cases.

1. Background of database horizontal splitting

With the increasing volume of business and data, a single database may face the following challenges:

  • Performance bottleneck: Too many single library read and write requests lead to a prolonged response time.
  • Storage pressure: The storage and maintenance cost of massive data on a server is high.
  • Poor scalability: It is difficult to meet the growing business needs through hardware upgrades.

To solve these problems, horizontal splitting technology can disperse data into multiple databases or tables, thereby improving overall system performance and scaling capabilities.

2. MySQL partitioning policy

2.1 Partition concept

Partitions are partitioning a single logical table into multiple physical segments according to some rules, and these partitions still belong to the same database instance. During query, MySQL automatically selects relevant partitions according to the partition key for scanning, thereby reducing the amount of data in a single scan and improving query performance.

2.2 Common partition types

  • RANGE Partition: Divide partitions according to the value or date range of a certain field. For example, partition an order by month or year.
  • LIST Partition: Partition based on enumeration values, such as partitioning data of finite sets such as region and state.
  • HASH partition: Hash the field value and get the remaining partition, suitable for scenarios where data is evenly distributed.
  • KEY Partition: Similar to HASH partition, but does not require user-defined partition expressions, and is automatically calculated by MySQL.

2.3 Pros and cons of partition

advantage:

  • Improve query efficiency: Only relevant partitions are scanned during query to reduce full table scanning.
  • Easy to manage: Historical data can be archived, backed up or maintained independently.
  • Optimize maintenance operations: When deleting or archiving data, just operate on the corresponding partition.

shortcoming:

  • Single library limitations: All partitions are still on the same database instance, and it is difficult to solve the hardware resource bottleneck problem.
  • Management Complexity: Partitioning strategies need to be carefully designed, and later adjustment of partitions may involve data migration.

2.4 Partition Example

Assuming that the order table needs to be partitioned by year, the following statement can be used:

CREATE TABLE orders (
    order_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pMax VALUES LESS THAN MAXVALUE
);

In this example, the order table is divided into multiple partitions by order year, so that when querying data for a specific year, you only need to scan the corresponding partition.

3. Library and table division strategy

3.1 Library and table concept

A sub-database splitting is to split the data into multiple independent database instances (sub-databases) or multiple tables (sub-databases) in the same database according to certain rules. This strategy can effectively reduce the load of a single library and improve the overall concurrency performance and scalability of the system.

3.2 Implementation of library and tables

  • Vertical split: Split different tables into different databases according to business modules or data types to reduce the number of single-base tables. For example, user data, order data, and log data are stored in different database instances respectively.
  • Horizontal split: Split the data in a single table into multiple subtables according to the value range or hash value of a certain field (such as user ID, order ID). For example, split the user table into 10 subtables by the hash value of the user ID.

3.3 Pros and cons of sub-store and table

advantage:

  • Improve performance: By distributing data across multiple nodes, concurrent processing capabilities can be greatly improved.
  • Enhanced scalability: The data volume and request pressure of a single database instance are reduced, making it convenient to scale horizontally.
  • Reduce the risk of single point failure: The data is distributed on multiple nodes, and even if some nodes fail, it will not cause the entire system to crash.

shortcoming:

  • Cross-library query complexity: Multi-base data aggregation and conjunction table query require middleware or distributed query engine to increase system complexity.
  • Transaction consistency: Cross-store transaction management is difficult and additional distributed transaction mechanisms are required.
  • Increased operation and maintenance costs: Data is distributed on multiple database instances, and backup, recovery and monitoring management are more complex.

3.4 Example of sub-store representation

Suppose the order table is split horizontally into 4 sub-tables by customer ID:

-- Sub-table orders_0
CREATE TABLE orders_0 (
    order_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id)
);
-- Sub-table orders_1
CREATE TABLE orders_1 LIKE orders_0;
-- Sub-table orders_2
CREATE TABLE orders_2 LIKE orders_0;
-- Sub-table orders_3
CREATE TABLE orders_3 LIKE orders_0;

Data routing rules: Assign the result of moduloing the customer ID to 4 as a suffix to the corresponding subtable, for example:

INSERT INTO orders_((customer_id % 4)) VALUES (...);

The business layer or middleware needs to automatically select the correct subtable according to the customer ID for query and update operations.

4. Comprehensive application of partitioning and library division

In actual projects, partitioning can be used in combination with library and tables:

  • Partition: Used to manage a large amount of data inside a single table, such as partitioning by date and status, to facilitate data maintenance and query optimization.
  • Library and table: Used to solve the overall concurrency and storage bottleneck of databases, split the data level on multiple nodes, thereby achieving the purpose of high availability and high scalability.

This combination strategy can not only use partitioning technology to reduce the amount of data scanned in a single time, but also reduce the pressure on each node through library and table division to achieve overall performance optimization of the system.

5. Summary

  • Partition policy: Suitable for the management of large tables in a single library, dividing data into multiple physical segments by scope, hashing, etc., improving query efficiency and flexibility in data maintenance.
  • Database and table division strategy: Suitable for scenarios with huge data volume and high concurrency, it can achieve load balancing and scale-out by splitting data into multiple database instances or subtables.
  • Comprehensive application: According to business needs, reasonably combine partitioning and library division and table division strategies, and find the best balance between performance, scalability and maintenance.

Understanding and applying these strategies will not only improve the performance and response speed of the database, but also lay a solid foundation for future system expansion. I hope this article provides valuable reference and guidance for you when designing and optimizing the MySQL data storage architecture!

This is the article about MySQL partitioning and library division and table division strategy. For more related contents of mysql partitioning and library division and table division, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!