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: don't do it unless you have to.
    • 1.2.2. Your SQL statement is no longer declarative (declarative)
    • 1.2.3. You incur a lot of network delays.
    • 1.2.4. Your time zone. Many of the powerful capabilities of SQL
    • 1.2.5. MySQL does not have an API to ensure asynchronous query sequence work
    • 1.2.6. Summary

MySQL Sub-Library sub-table scheme

Translate a StackOverflow answer, about the shortcomings of the sub-database, the original link: https://stackoverflow.com/questions/5541421/mysql-sharding-approaches

Problem:

What is the best way to slice MySQL tables? What I think of is:

1. Application layer slicing?
2. MySQL proxy layer slicing?
3. Provide a central lookup shard service?

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. It's only when you need it that you start to optimize latency, increase throughput,

The reason you slice a database is simply because the database reads or writes: The database writes: The write operation exceeds 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 memory, and most read operations start by hitting the disk instead of reading the data from memory.

Only then do you need to do database slicing.

When you start slicing, you begin to pay the price in one of several 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 it 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 A-node table and the B-node table, and even some of the tables have data that is larger than one node, joins the data in the A-node and B-nodes, and aggregates the data from the B-node and the C-node join. You start writing a one-sided hash application to solve the problem (or you can recreate the MySQL cluster), which means you get a whole bunch of non-declarative SQL, and let it work 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 knows the consumption time on the local disk, which can be minimized to solve this query problem.

In a distributed environment, a query statement must have a KV map to access multiple network nodes (hopefully, bulk access, not every key round trip), or place conditions on the node they will be executing on.

But even in the best case, it can be more complex to involve many network round trips. In particular, the MySQL optimizer is completely unaware of the network latency situation.

Your time zone has many of the powerful capabilities of SQL

Well, it might not be that important, but external check constraints, other SQL mechanisms that guarantee data integrity, are powerless for cross-node multiple nodes.

MySQL does not have API to ensure asynchronous query order work

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

The premise is that there is an API that requires asynchronous communication, which does not exist in MySQL's very good working state. The alternative is to add a lot of forking and connections to the child process,

Summarize

When you start slicing, data structures and network topologies significantly affect the performance of your application. In order to run well, your application needs to be wary of these things, which suggests that only the application layer's segmentation is meaningful.

If you need to auto-slice, the problem will be more (such as determining the node's column as the hash primary key), or you want to manually slice, XYZ users go to the main library, ABC goes to the main library with def.

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. The discount allows transparency 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 slicing on business functions is that it does not allow the data bloat of a single table to be too large, which requires the designer's active attention.

The benefit of business feature segmentation is that it is relatively simple for a codebase that doesn't change much. It has been done a few times in the past few years and has been able to work well.

MySQL Sub-Library sub-table scheme