MySQL Sub-Library sub-table scheme

• 1. mysql Sub-database sub-table scheme
  • 1.1. Question:
  • 1.2. Answer:
    • 1.2.1. The best way to divide MySQL is to not do it unless it is a last resort.
    • 1.2.2. Your SQL statement is no longer declarative (declarative)
    • 1.2.3. You incur a lot of network delays.
    • 1.2.4. You lost a lot of the power of SQL
    • 1.2.5. MySQL does not have an API to ensure that asynchronous queries return sequential results
    • 1.2.6. Summary

MySQL Sub-Library sub-table scheme

Translate a question and answer on a stackoverflow, on the shortcomings of the sub-database, the original link:
MySQL sharding approaches?

Problem:

What is the best MySQL sub-Library sub-table scheme? What I think of is:

1. Application layer slicing?
2. MySQL proxy layer slicing?
3. Provide a central service for locating database shards?

Do you know any interesting items or tools in this area?

Answer: The best way to divide MySQL is: don't do it unless you have to.

When you write an app, you usually want the fastest development speed. Only when necessary, you start to optimize latency, improve throughput,

The reason you slice a database is simply because the database reads or writes :

• Database Write
  Write operations exceed the server's disk load permanently, and too many writes cause the replica synchronization to lag behind forever.
• Database read
  The amount of data read is too large to burst into memory, and most read operations start by hitting the disk rather than reading the data from memory.

Only then do you need to consider database slicing.

When you start slicing, you begin to pay the price in the following ways:

Your SQL statement is no longer declarative (declarative)

In general, you use SQL statements to tell the database what data you want, and then let the optimizer optimize SQL and convert SQL into a data acquisition program.
It's great because it's very flexible and it's boring to write these conversions, which seriously affects the speed of development.

In a distributed environment, you join the table of Node A and the table of B nodes, and even some tables have data that is larger than one node.
The A and B nodes join the data and then aggregate the data after the B node and the C node join.
You start writing a one-sided hash application to solve this problem (or you can recreate the MySQL cluster),
This means you get a whole bunch of non-declarative SQL, and let the program start working in a process-oriented way .

You incur a lot of network delays.

In general, a SQL query statement can be resolved locally and the optimizer is able to resolve this query problem with minimal time-consuming.

In a distributed environment, a query statement must have a KV map to access multiple network nodes (in the hope of bulk access,
Instead of each key to one network round trip), or place the Where condition on the node they will be executing.

But even in the best case, multiple network accesses can be more complex. In particular, the MySQL optimizer is completely unaware of the network latency situation.

You lost a lot of the power of SQL

Well, this may not be so important, but foreign key constraints, other SQL mechanisms that guarantee data integrity, are powerless for cross-node multiple nodes.

MySQL does not have an API to ensure that asynchronous queries return sequential results

When the same type of data is stored on multiple nodes (for example, the user data resides on the A,B,C node), the horizontal query requires access to all nodes,
Data access time is directly increased linearly with the number of nodes. Unless multiple nodes are accessed in parallel, they are then aggregated in a map-reduce manner.

The premise is that you need to provide an API for asynchronous communication, but this does not exist in the functionality provided by MySQL. An optional scenario is to add a lot of forking and connections to the child process.

Summarize

When you start doing database sub-library tables, data structures and network topologies significantly affect the performance of your application.
In order to run well, your application needs to be careful with these things, so only the application layer's segmentation is meaningful.

If auto-segmentation is required, the problem will be more (such as determining the column of that node as the hash primary key), or
You want to manually slice, XYZ users go to the main library, ABC goes and DEF goes to the main library.

There are some benefits to segmenting business functions, and if done right, this is transparent to most developers. Because all the related tables are stored locally.
This allows the transparency of the program to benefit as much as possible from declarative SQL and has less network latency because network access across nodes is kept to a minimum.

The disadvantage of segmentation on business functions is that it does not allow the data bloat of a single table to be too large , which requires special attention from the designer.

The benefit of business feature segmentation is that it is relatively simple for a codebase that doesn't change much.
It has been doing a few business functions in the past few years, and has been working very well.

MySQL Sub-Library sub-table scheme