Some time ago, Hellohello-Tom left his job. Due to personal reasons, after a period of rest, he re-entered a new company. Orientation on the first day of Tom elder brother just experienced a production accident, ops students online MYSQL load pressure warning said, is the main library MYSQL directly collapsed first day (this is not a good one million head), emergency operations classmates master-slave switch, after the fact to find the cause of accidents, trying to is the main reason for the slow query result in MYSQL avalanche, After exporting the SQL of slow query, the project manager directly said that the mysql optimization function should be handed over to the new brother Tom. Brother Tom quickly opened the jump-board to check, and was startled to see what he did not know

The amount of data in a single table has reached 500 million levels! “, this must be caused by the accumulation of historical problems until now, ah, the project manager directly dumped this pit to Brother Tom, brother Tom thought, I can not pass the trial period?

But Tom elder brother well, hurry up with the project manager and communicate with all of his old workmates, understand the business scenario, only to find that lead to the situation now is such, Tom elder brother company is mainly do IM social system, the 500 million levels of data table is to focus on table, table is also commonly known as fans and in some big V, or is similar to web celebrity, Millions of followers are very common. A record will be generated after A pays attention to B, and B will also produce A record when B pays attention to A. It takes A long time to reach today’s data scale. The project manager said to Brother Tom in A slow way, there is no need to worry about this optimization, come up with A plan first!

According to Tom’s previous experience, when the data of a single table reaches about 500W, it should be considered to split the table. The common split table scheme is nothing more than hash modulo or range partition, but the difficulty of the data split table and migration process lies in two aspects:

1. Excessive data smoothing, gradually migrating single table data without downtime (boss said: dare to lose thousands of pieces per minute when downtime, KPI will be directly negative for you)

2, data partition, hash or range? (Temporarily unable to use some sub-database sub-table middleware, helpless.)

Let’s start with hash

User_id =128; user_id=257; user_id=128; Then two people can easily fill the table, and when other users come in, user_attention_1 will become a large table, which is a typical hot spot problem. This method can be passed, some students say that user_id and fans_id can be combined to allocate data. Tom brother also considered this problem, although this way the data distribution is uniform, but there will be a fatal problem is the query problem (because there is no high performance query DB like mongodb and DB2, and there is no data synchronization, considering the workload or query the existing sub-table data), For example, a common query in a business scenario is I follow those people, and those people follow me, so our query code might look something like this

Select * from user_attention where user_id = #{userId} select * from user_attention where fans_id = #{userId}Copy the code

After we hash user_id and fans_id, if I want to query who I follow and who follows me, I will have to retrieve 128 tables to get the result, which is disgusting and definitely not desirable. In addition, considering that at least half of the data will be affected by expansion in the future, it is really not good to use this scheme PASS.

I sorted out some information, and friends in need can click to get it directly.

25 Java Interview Topics

Learning routes and materials from 0 to 1Java

Java core knowledge set

Left program cloud algorithm

And then range

For example, if user id=128, then the user_attention_0 table in the Range [0,10000] is used to insert the data directly into the table. However, this can also cause hot data problems. It seems that simple horizontal partition can not be satisfied, this scheme can also pass, or to find him by ah.

After working day and night, Tom came up with three solutions

Range + consistent hash ring combination (hash link point 10000)

The main reason for wanting to adopt this scheme is

1. Capacity expansion is simple and affects only a single node in the Hash ring

2. Data migration is simple. Each capacity expansion only requires data interaction between the newly added nodes and the post-installed nodes

3. The query scope is small, and some table partitions are retrieved according to the range and hash relationship

We will divide the range by user_id first, but after range I will probably not be a table, but a hash ring

Each range corresponds to its own set of rings. We can expand according to the actual situation. For example, there are only two large V’s in the range [1,10000], so we can divide it into three tables and reserve 15 million data capacity. [10001,20000] there are 4 Internet celebrities and big vs in the hash ring. There are actually 4 tables. Our user id can be located in the first physical table clockwise, and the data can be stored in the database.

Everything has advantages and disadvantages, the program also should be combined with working hours, the actual feasibility and technical review after the ability to decide, malpractice ZA also want to list out

1. The design is complex, and the relationship between range area and Hash ring needs to be added

2, the system modification affected more, query relationship is complex, more than a layer of routing table concept, although try to allocate user data to a region, but want to query who care about me, and I care about who logic is still complex.

Range +hash mod 300

This is actually easier to understand, which is a simple form of range+hash modulo combination. After range reaches a certain range, hash modulo will be used to find the corresponding table for storage. This scheme is simpler than scheme 1, but it also has the problems existing in scheme 1. And he also has the problem of expanding the scope of the data. However, the implementation is quite simple. From the perspective of query, the size range of the module can be controlled according to different scenarios. The hash module of each partition adopts different values according to the actual situation.

The last option is the Range userId partition

This is the most reliable and implementable option Tom thinks possible. It looks like the second option, but is a little more specific. First we define an intermediate relation table user_attention_routing

We will make a relationship between the user range and which table to route to, search according to the range, combine with the existing data when a big V or Internet celebrity has a large amount of data, we will route him into his own table and the data will look something like this

User_id = 256 is a big V, for example, they took him to ask for him to become a table alone, at the time of query range priority to check whether there is a separate corresponding routing table, and all the other bits and pieces, users still routed to a unified in the table, when some classmates said like this is not the data and uneven, Tom elder brother was also think so, But basic is unlikely to be assigned to the absolute evenly, can only be relative, as far as possible put some big V points out, do not take up public resources, when someone suddenly become after the big V, in the person be separate points out, evolving this process, ensure the balance of the data, and after that deal with a lot of original associated query actually changes little, As long as all the original user_attention is dynamically modified after data migration (using a Mybatis interceptor can be done) PS: In fact, most of the data concerned in analyzing actual business scenarios still come from those frags of users.

The sub-table scheme is set first, then another problem is the query problem, the above said that a lot of business query is nothing more than who cares about me, I care about who such a scenario, if continue to use before

Select * from user_attention where user_id = #{userId} select * from user_attention where fans_id = #{userId}Copy the code

Such a scheme, when what I want to check my fans, this tragedy, I still want to retrieve a full table according to fansid find all of my fans, because the records in a table only I who focused on such data, considering this problem, Tom decided to redesign data storage form, using the thinking of space, in time, User A writes two columns to user B while user A is paying attention to user B

After user 1 follows user 2, two data will be generated, state(1 means I followed user, 0 means I was followed user, and 2 means we are related to each other). After using such data storage mode, all my queries can be started from user_id, instead of the reverse calculation of fans_id, in the design of database index, User_id, fans_id, state, user_id, state

With the table and query issues resolved, it’s time to consider the data migration process, which is also very important. You may lose your KPI (step by step)

The most important issue to consider in data migration is timeliness, migration procedures are essential, how to normal production environment running, migration script offline running data do not affect each other? Answer is classic routines written data pairs, because of the old data have not suddenly moved to a new table, now and user_attention data, you have to remain in the produce of class data at the same time, according to the routing rules, directly to a new table, offline migration program slowly ran a few additional service bai, but should control the amount of data, Do not occupy full IO affect the production environment, offline simulation and drilling is also essential, who can not guarantee that there will be no problem. Create a unique index for user_id and fans_id in the migration script and online. In extreme cases, data may be written to a new table, but the old table may not be updated.

When the new table data and the old table are fully synchronized we can put all the system affecting the old table query statements are changed to the new table query, verify that there is no problem, if there is no problem can finally be happy

truncate table user_attention;
Copy the code

Finally killing the 500 million data time bomb.