Mysql database to deal with the large traffic site of the 3 kinds of Schema expansion method Introduction _mysql

The database extension is probably divided into the following steps:

1, read and write separation: When the database access is not very large, we can appropriately increase the server, the database master-slave copy of the way to read and write separation;

2. Vertical partition: When the write operation once increased, then the master-slave database will spend more time on the data synchronization, this time the server is overwhelmed; Then there is the vertical partition of the data, the vertical partitioning of the data is the data table, such as the user table _ User, or Order form _orders, then we can separate the two tables, on a different server, if the two tables and other tables have a table query, then you can only split the original SQL statement, first query a table, in the query another, although said this will consume more performance, But the burden has been reduced by a lot of data synchronization.

3, Horizontal division: But often things are not satisfactory, may take vertical zoning can support a period of time, because the site is too hot, the number of visits daily 100w, suddenly jumped to 1000w, this time can take the data separation, we can according to user ID different distribution, If you take the form of%2, or%10 form, of course, this form of the future expansion has a lot of restrictions, when I from 10 partitions to 20, all the data have to repartition, then will be a very large amount of computation; Here are some common algorithms:
Hash algorithm: Is the way to use user_id%;
Range: can be divided according to user_id character value range, such as 1-1000 for one area, 1001-2000 for another;
Mapping relationship: That is, the corresponding partition of the user_id exists in the database to save, when the user operation first to query the partition, then the operation;

For the above methods, read-write separation is mainly operational expansion, vertical partitioning is mainly to write more frequent data table separation, the horizontal partition is mainly data separation;