MySQL table sharding implements batch query design mode for distribution and storage of millions of records on millions _ MySQL

MySQL table sharding implements batch query design mode for distribution and storage of millions of records on millions

We know that a large MySQL table with massive records can be divided into several tables based on the primary key, time field, and condition field, or even stored on several servers.
The only problem is that cross-server batch query is troublesome and can only be solved through applications. Let's talk about the solutions in Java. The principles of other languages are similar.
The table Sharding is not a partition of MySQL 5.1, but a table is separated by several tables or different servers.
1. application-level implementation
See

ElectThreadManager table sharding Data Query Manager
It creates a thread pool for each database or server in a table shard.
AddTask ()-add a task
StopTask ()-stop a task
GetResult ()-get execution result
Fastest execution time = time consumed by querying the slowest MySQL node
Slowest execution time = timeout
Process when a ThreadPool is busy
1. if ThreadPoolN is very busy (it also means db n is very busy );
2. the new query task comes, addTask (), and a thread of the new task is added to the ThreadPoolN task queue.
3. the outer application has obtained the results returned by other threads and continues to wait.
4. when the wait time of the outer application is reached, call stopTask () to set the stop flag in all threads of the task, and the outer application returns.
5. after several times, ThreadPoolN receives the queue Thread. because the stop bit is set, the Thread runs directly.
2. JDBC layer implementation
Pack a JDBC Driver and intercept the executeQuery () of PreparedStatement and Statement ()
Then the SelectThreadManager is called.
3. MySQL partition
The partition function of MySQL 5.1 also has the preceding problems in batch query because the data in a single table is cross-file. However, it is implemented in MySQL and does not need to be concerned by external callers. Its query implementation principle should be roughly similar.
However, partition only solves the I/O bottleneck and cannot solve the CPU computing bottleneck. Therefore, it cannot replace the traditional manual table sharding method.

BitsCN.com