Three architectural extensions to the mysql database for high traffic websites are presented

Database expansion is roughly divided into the following steps:

1, read and write separation: when the database access is not very big, we can appropriately increase the server, database master and slave replication will read and write separation;

2. Vertical partitioning: When write operation 1 increases, master/slave database will spend more time on data synchronization, and the server will be overwhelmed. Then there is vertical partition of the data, the data of vertical zoning idea is to write more frequently data table, such as the user table _user, or _orders order table, then we can to separate the two tables, and placed on a different server, if the two league table query table and other tables, so can only give the original sql statements to break up, to query a table, the query the other one, although this will consume more performance, but rather than that of large amounts of data synchronization, burden or reduce a lot;

3, horizontal partitioning: but often unsatisfactory things, may take vertical partition can hold 1 time, thanks to the fire site, traffic and daily 100 w, 1 following jumped to 1000 w, can adopt the separation of data at this time, we can according to user Id different allocation, such as taking the form of % 2, % or the form of 10, of course this form have made a lot of restrictions on the propagation of the future, when I increased from 10 partitions to 20, all data have to partition, then will be one of the very large amount of calculation; Several common algorithms are provided below:
Hash algorithm: is to use user_id%;
Range: It can be partitioned according to the range of user_id character value. For example, 1-1000 is 1 zone, 1001-2000 is another zone, etc.
Mapping relationship: the corresponding partition existing in user_id is saved in the database. When the user operates, he/she first goes to query the partition and then carries out the operation.

For the above several expansion methods, read and write separation is mainly the operation of the extension, vertical partition is mainly to write more frequent data table separation, horizontal partition is mainly data separation;