Table sharding design ideas and implementation

I. OverviewTable sharding is a popular concept, especially in the case of large loads. Table sharding is a good way to distribute database pressure. First, you need to understand why Table sharding is required. What are the benefits of 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.To ensure data integrity, Data Table files must be locked, including shared 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 implies, the table sharding technology divides several tables that store the same type of data into several table sharding stores. When extracting data, different users access different tables and do not conflict with each other, 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 sub-tables are extracted from different tables to reduce the possibility of locking the table. The two table sharding methods I will discuss below have not been tested by myself and cannot be used accurately. They just provide a design idea. The following table sharding example is based on a paste bar system. (If you haven't posted any posts, Google them) Ii. Basic table-based table shardingThe basic table-based table sharding method is: A main table stores all the basic information. If a project needs to find the table it stores, it must find the corresponding table name and other items from the basic table, you can directly access this table. If you think this basic table is not fast enough, you can save the entire basic table in the cache or memory for convenient and effective query.Based on the post, we construct the following three tables: 1. clipboard forum table: Save the information of the Forum in the clipboard. 2. paste bar topic table: Save the topic information in the paste bar for browsing 3. paste the reply table: Save the original content of the topic and the reply content "paste the forum table" contains the following fields: Forum ID board_id int (10) Forum name board_name char (50) the subtable ID table_id smallint (5) generation time created datetime "Post Bar topic table" contains the following fields: topic ID topic_id int (10) topic name topic_name char (255) forum ID board_id int (10) the field of the created time created datetime "post it reply table" is as follows: reply ID reply_id int (10) reply content reply_text text topic ID topic_id int (10) section ID board_id int (10) creation time created datetime. Therefore, the table structure information in the whole bar is saved above. The relationship between the three tables is: forum --> multiple topic --> Multiple replies, that is, the relationship between the table file size is: Forum table file <topic table file <reply table FileSo You can basically determine that you need to split the topic table and the reply table, and the speed and performance of our data retrieval and query changes have been increased.After reading the table structure above, we can see that a "table_id" field is saved in the "forum table, this field is used to save the topic corresponding to a forum and the table in which the reply table is saved. For example, we have a post called "PHP". If board_id is 1 and the sub-Table ID is also 1, the record is: board_id | board_name | table_id | created1 | PHP | 1 | 00:30:12. If you need to extract all topics in "PHP, then, you must combine a table name that stores the topic according to the table_id saved in the table. For example, the prefix of the topic table is "topic _", the topic table corresponding to the "PHP" should be "topic_1". Then we will execute: SELECT * FROM topic_1 WHERE board_id = 1 order by topic_id desc limit 10 to obtain the reply list under this topic, which is convenient for us to view. If you need to view the reply under a topic, we can continue to use the table_id saved in the forum table for query. For example, if the prefix of the reply table is "reply _", you can combine the reply of the topic with the ID of 1 in "PHP: SELECT * FROM reply_1 WHERE topic_id = 1 order by reply_id desc limit 10 here, we can clearly see that we actually use the basic table here, the basic table is our forum table. Then, we will certainly say: How can we ensure the speed and efficiency of a base table when the data volume is large?Of course, we This basic table must maintain the best speed and performance. For example, you can use the MySQL memory table for storage or store it in the memory, such as the Memcache memory cache, it can be adjusted according to the actual situation.Generally, the basic table-based table sharding mechanism is a good solution for SNS, dating, forums, and other Web2.0 websites. In these websites, you can use a single table to save the relationship between the basic identifier and the target table. The advantage of using tables to save the corresponding relationship is that it is very convenient to expand in the future. You only need to add a table record. 【 Advantages] It is very convenient to add and delete nodes, which makes it very convenient for later upgrade and maintenance 【 Disadvantage] If you want to add a table or operate a table, you still cannot leave the database, which may cause a bottleneck. Iii. Based on Hash Algorithm table shardingWe know that a Hash table is a value calculated using a special Hash algorithm. This value must be unique and can be used to find the desired value, this is called a hash table. The hash algorithm in the table is similar to this idea: Calculate the table name of the data storage table by the ID or name of the original target using a certain hash algorithm, and then access the corresponding table.Continue to take the above post for example. Each post has a forum name and a forum ID. The two values are fixed and unique, then we can consider performing some operations on one of these two values to obtain the name of a target table. Now, if we assume that the system allows a maximum of 0.1 billion data records for each table, and we want to save 1 million records for each table, the system will not have more than 100 tables. According to this standard, we assume that hash is performed on the Forum ID of the clipboard to obtain a key value. This value is our table name and then access the corresponding table. We construct a simple hash algorithm: function get_hash ($ id) {$ str = bin2hex ($ id); $ hash = substr ($ str, 0, 4 ); if (strlen ($ hash) <4) {$ hash = str_pad ($ hash, 4, "0") ;}return $ hash ;} the algorithm is generally to pass in a forum ID value, and then the function returns a four-character string. If the string length is not enough, use 0 to complete. For example: get_hash (1), the output result is "3100", input: get_hash (23819), and the result is: 3233, then we use a simple combination with the table prefix, you can access this table. When we need to access the content with ID 1, the combined table will be: topic_3100,Reply_3100, then you can directly access the target table. Of course, after the hash algorithm is used, some data may be in the same table. This is different from the hash table. The hash table tries its best to solve the conflict. We do not need it here, of course, the name of the table that may be stored in the Prediction and Analysis tables. If more data needs to be stored, you can perform the hash operation on the Forum name. For example, the preceding binary conversion is also in hexadecimal format, because Chinese characters are much more likely to be repeated than numbers and letters, but more tables may be combined. Therefore, some other problems must be considered. In the final analysis, if you use the hash method, You must select a good hash algorithm to generate more tables. However, data query is faster. 【 The hash algorithm directly obtains the target table name, which is highly efficient.] Through 【 Disadvantage] Poor scalability. A hash algorithm is selected to define the amount of data. In the future, the system can only run on the data volume, but cannot exceed the data volume. The scalability is slightly lower. 4. Other problems 1. Search Problems Now that we have already performed table sharding, we cannot directly search for tables, because you cannot search for dozens or hundreds of tables that may already exist in the system, therefore, search must be performed by using third-party components. For example, Lucene and Sphinx are good choices for intra-site search engines. 2. Table file ProblemsWe know that MySQL MyISAM engine generates three files for each table, namely, *. frm, *. MYD, and *. myi. Table shards are used to store table structures, table data, and table indexes. In Linux, the number of files in each directory should not exceed 1000. Otherwise, data retrieval will be slower, so each table will generate three files. If the number of sub-tables exceeds 300, therefore, the retrieval is very slow, so at this time the score must be performed again, for example, the database is separated. With the basic table, we can add a new field to save the data stored in the table. In hash mode, we must take the nth digit of the hash value as the database name. In this way, the problem is solved in good condition. V. SummaryIn large-load applications, databases have always been an important bottleneck and must be broken through. This article describes two table sharding methods, hoping to inspire many people. Of course, the code and ideas in this article have not been tested by any code, so it cannot be ensured that the design is completely accurate and practical. The readers must carefully analyze and implement the design during use.