MySQL sub-database sub-table method

MySQL sub-database sub-table method

For sub-library sub-table, the concrete in two ways: 垂直拆分和水平拆分. The vertical split is mainly about the refinement and independence of the business, which is closely related to the business. So this article only discusses more general horizontal splits.

Why sub-database and sub-table

  1. Reduce the performance of stand-alone MySQL
  2. Reduce the amount of data in a single table or a single database, and reduce the query pressure of the database
  3. Breaking through the capacity limit of a single machine, the way to sub-database and sub-table
  4. 范围区分(range): Shard by month\by district\by other special attribute dimensions
  5. 预定义范围: Estimate how much data capacity there is, and allocate the range of data, 0-100->A 101-200->B
  6. 取模 Hash: Perform modulo operation on the specified field to match the corresponding library and table.

Problems caused by sub-database and sub-table

  1. Data maintenance costs are high
  2. Cross-database business join
  3. Low performance of distributed transactions
  4. Self-incrementing id generation problem
  5. The waste of polling for non-fragmented field queries
  6. Multi-node sorting problem

Middleware for sub-database and sub-table

There are many middleware for sub-database and sub-table, Shardingsphere, Tddl, MyCat, cobar. In terms of architecture, there are two main types: JDBC应用方式and Proxy模式.

JDBC应用模式It is fragmentation based on the client, and the client Sqldetermines the specific SQL server to be executed according to the rules and rules. Representative hasShardingsphere,Tddl

Advantages of JDBC application mode:

  1. Good performance
  2. Support cross-database (mysql oralce mssq)

Disadvantages:

  1. Cannot cross language
  2. Not friendly enough to development, increasing the difficulty of development

Proxy模式Agent model is based on MySQLdoing one forwarding agent, the agent is to distribute specific to the SQL server according to the rules.

advantage:

  1. Cross language
  2. Development without perception

Disadvantages:

  1. Poor performance, increased network requests
  2. Does not support cross-database
Reference: https://cloud.tencent.com/developer/article/1780886 MySQL sub-database sub-table method-Cloud + Community-Tencent Cloud