What is Mysql table sharding?

Mysql table sharding is a key technology in Mysql databases. The concepts and usage of Mysql table sharding are described in detail below. If you are interested in this, take a look.

First, we need to know why Mysql table sharding is required. What are the advantages of Mysql table sharding. Let's take a look at the SQL Execution Process in the next database:
Receive SQL --> put in SQL Execution queue --> Use analyzer to break down SQL --> extract or modify data according to analysis results --> return processing results

Of course, this flowchart is not necessarily correct, but I think it is my own subjective consciousness. So what are the most common problems in this process? That is to say, if the previous SQL statement is not completed, the subsequent SQL statement will not be executed, because to ensure data integrity, the data table file must be locked, two types of locks are available: Shared locks and exclusive locks. During the lock period, other threads can access the data file, but the modification operation is not allowed. Correspondingly, the exclusive lock means that the entire file is owned by one thread, other threads cannot access this data file. In general, MyISAM, the fastest storage engine in MySQL, is locked based on tables. That is to say, if one is locked, the entire data file cannot be accessed from outside. After the previous operation is completed, to receive the next operation, the previous operation is not completed, and the latter operation is not executed in the queue. Blocking is usually called a "lock table ".

What are the consequences of locking a table directly? That is, a large number of SQL statements cannot be executed immediately. You must wait until all the SQL statements in front of the queue are executed. This unexecutable SQL will result in no results, or the delay is serious, affecting the user experience.

Especially for tables that are frequently used, such as the user information table in the SNS system and the post table in the forum system, these tables are all tables with a large access volume, to ensure quick data extraction and return to users, you must use some processing methods to solve this problem. This is the table sharding technology I will talk about today.

As the name suggests, Mysql table sharding technology divides several tables that store the same type of data into several table sharding tables. When extracting data, different users access different tables without conflict, reduce the chance of locking a table. For example, currently there are two tables for saving user sub-tables, one for user_1 and the other for user_2. The two tables store different user information, and user_1 stores the first 0.1 million user information, user_2 stores the information of the last 0.1 million users. If you query the users heiyeluren1 and heiyeluren2 at the same time, the Mysql sub-tables are extracted from different tables to reduce the possibility of locking the table.
 

Internal lock of MySQL Server

Difference between 25 mysql and SQL server

Provides you with an in-depth understanding of Mysql temporary tables.

How to clear a table in Mysql

Command for viewing the Mysql table structure