Horizontal splitting of the MySQL5.5 table

One: Business background

Second: The first level of business split programme

III: Second level of business split programme

1.1 As a result of the rapid increase in the volume of business, a table of data is increasingly large, and now assume that the situation for a company's peer-to site, due to the earlier propaganda is better, in the last three months of the business volume increased sharply, making the data of the Water Record Table (trade) More and more large, More than 3 million lines of records are expected to record more than 10 million lines during the year, this time you need to consider the level of the table to be split

1.2 assume that as the volume of business increases, the number of users increases, up from the previous thousands of users to 1 million, but also want to split it horizontally

2.1 Assuming that the year reached 10 million lines of records, coupled with subsequent considerations, the water table (trade) is split into 10 tables, respectively, Trade_0, Trade_1, trade_2, trade_3 ..., trade_10

2.2 The fields in the water table assume the following fields

Serial_number Serial number user_id User ID request_message request information

2.3 because fetching water is based on the USER_ID (user ID) to get their own water situation, so here to user_id to do the sub-table

2.4 user_id%10 Use the user_id modulo 10, equal to a few will be equal to this user_id record inserted into the trade_x (x equals 0 to ten)

2.5 Specific Steps

2.5.1 Check how many records are in the current trade table so that you can check if the records are lost after the final split is complete

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/B1/wKiom1WlI53SoaqDAABzowiB6B0755.jpg "title=" 1.png " alt= "Wkiom1wli53soaqdaabzowib6b0755.jpg"/>

2.5.2 Insert the user_id%10=0 record into the Trade_0,trade_1...trade_9 table

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B1/wKiom1WlI8Hiw8xoAAB3Vx-r6-E977.jpg "style=" float: none; "title=" 2.png "alt=" Wkiom1wli8hiw8xoaab3vx-r6-e977.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/AE/wKioL1WlJZriMt3-AAB96mtyJrg473.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1wljzrimt3-aab96mtyjrg473.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/AE/wKioL1WlJZqx2BGQAACFRWU0yAk806.jpg "style=" float: none; "title=" 4.png "alt=" Wkiol1wljzqx2bgqaacfrwu0yak806.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/B1/wKiom1WlI8GjXl1VAACHoUaoeSQ508.jpg "style=" float: none; "title=" 5.png "alt=" Wkiom1wli8gjxl1vaachouaoesq508.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/B1/wKiom1WlI8GQAnWMAACIFiXUWK0840.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1wli8gqanwmaacifixuwk0840.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/AE/wKioL1WlJaywibY1AACH-dBNs70572.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1wljaywiby1aach-dbns70572.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/B1/wKiom1WlI9OQVIhTAACA04I7mMc785.jpg "style=" float: none; "title=" 8.png "alt=" Wkiom1wli9oqvihtaaca04i7mmc785.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/AE/wKioL1WlJaziBzNrAACNK2Q1YtA224.jpg "style=" float: none; "title=" 9.png "alt=" Wkiol1wljazibznraacnk2q1yta224.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B1/wKiom1WlI9OjzXtDAACHzYuxw5w086.jpg "style=" float: none; "title=" 10.png "alt=" Wkiom1wli9ojzxtdaachzyuxw5w086.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/AE/wKioL1WlJayxRkbbAACJmNuMg6Y491.jpg "style=" float: none; "title=" 11.png "alt=" Wkiol1wljayxrkbbaacjmnumg6y491.jpg "/>

2.5.3 add up the records of the TRADE_0...TRADE_10 table to see if it is the total number of records in the trade table

2.5.4 The above is to split the existing data into more than one table, then the query and insert how to do it? Because the flow of water is related to the user's operation, so when querying and inserting, you can use stitching string to insert the data, such as the above split method can be spliced

int suffix = user_id%10; String table_name = "Trade_" + Suffix;insert into table_name (...) values (...); or select * FROM table_name WHERE ...;

2.5.5 Summary, because the above split table is based on the existing user ID split, the first user, and then a record of running water, so this business to do the split is relatively easy

3.1 due to the increase in user volume, the user table is also to do data splitting, splitting scheme and the same, split into User_main_0...user_main_5

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/AE/wKioL1WlJguATu0ZAAGzUA2SjxQ421.jpg "title=" 12.png "alt=" Wkiol1wljguatu0zaagzua2sjxq421.jpg "/>

3.2 Now the problem is that when the user inserts, we don't know what the user ID is, and that means we don't know where to insert the data into the User_main_0...user_main_5 table .

3.3 in order to solve the above problems, we can execute the SQL statement first

Mysql> SELECT auto_increment Frominformation_schema. TABLES WHERE table_name= ' User_main ';

It returns the value of the next self-growth ID of the current table

3.4 The following SQL statement has already obtained the next ID value for the current User_main table, creating the User_main_uuid table below

Mysql> CREATE tableuser_main_uuid (user_main_next_id INT UNSIGNED PRIMARY KEY auto_increment);

3.5 then insert the value returned by the "3.3 SQL statement" into the User_main_uuid

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/B1/wKiom1WlJJagVUE_AABxd4J9l8U280.jpg "title=" 13.png "alt=" Wkiom1wljjagvue_aabxd4j9l8u280.jpg "/>

3.6 Then when the user registers, submits the user to fill in the information, enters to the controll time, after checking the user information, first checks the User_main_uuid table the user_mian_next_id field returns the value, then according to this value% 5, equal to a few, it is inserted into the user_main_x

3.7 There is also a problem, after the user registration is completed, the value of the user_main_next_id should be automatically added 1, otherwise, the next user registration when the ID will be repeated so that the data can not be successfully inserted, so after the successful insertion of the data into the corresponding user_main_x table, Execute the following SQL statement again

Mysql> INSERT into User_main_uuidvalues (NULL);

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/B1/wKiom1WlJMLDs7ceAADgmukbMNg783.jpg "title=" 14.png "alt=" Wkiom1wljmlds7ceaadgmukbmng783.jpg "/>

Since the ID is increased by inserting a record, the maximum value for the user_mian_next_id field in "3.6" should be obtained Max (USER_MIAN_NEXT_ID)

3.8 In addition to the values of the above update user_mian_next_id, you can also do this in the following ways

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/AE/wKioL1WlJs3DXZ3EAAEQBoTqz8U690.jpg "title=" 15.png "alt=" Wkiol1wljs3dxz3eaaeqbotqz8u690.jpg "/>

In this way, there is always only one record in the User_main_uuid table, so you do not have to use the max () function.

This article is from the "Everyman" blog, please make sure to keep this source http://caoyt.blog.51cto.com/9978141/1674267

Horizontal splitting of the MySQL5.5 table