Mysql knowledge extension-database/table sharding

Mysql knowledge expansion-database/table sharding MySQL Internet Oracle Enterprise Application Data Structure. database Replication can solve the access problem and cannot solve the large-scale concurrent writing problem. To solve this problem, we need to consider that mysql Data is split into www.2cto.com data segmentation, as the name suggests, that is, data dispersion, data on a single host is distributed among multiple hosts to reduce the load pressure on a single host. There are two sharding Methods: Database sharding, that is, multiple databases are divided according to the business module, the tables in each database are different. Another type is table sharding. Data is split to different hosts according to certain business rules or logic. The tables on each host are the same, this is a bit similar to Oracle table partitions. Database sharding is also called vertical partitioning. This method is simple to implement. It is important to refine the business. When database sharding is performed, you need to understand the interaction between services of each module, avoid excessive cross-database operations when writing programs in the future.
A table Shard is also called a horizontal partition. This method is more complicated than a vertical partition, but it can solve the problem that vertical partitions cannot solve, that is, a single table is frequently accessed and written, in this case, table sharding can be performed based on certain business rules (PS: for example, the membership level concept of Internet BBS Forum: Table sharding Based on the membership level) to reduce the pressure on a single table, it can also solve the problem of frequent interaction between modules. The advantages of www.2cto.com database sharding are that it is easy to implement, and the database and database are clearly defined to facilitate maintenance. The disadvantage is that it is not conducive to frequent cross-database operations and the problem of large data volumes in a single table cannot be solved. The advantages of table sharding are that it can solve the disadvantages of database sharding, but its disadvantages are precisely the advantages of database sharding. The implementation of table sharding is complicated, especially the division of table sharding rules and programming, and later maintenance of database splitting and transplantation.
In practical applications, Internet enterprises generally adopt database sharding and table sharding. The two are used in combination to complement each other. This gives full play to the biggest advantage of mysql expansion, but the disadvantage is that the architecture is large and complex, the compilation of applications is also complicated. The above are some concepts of mysql Data splitting. After data is cut off, what we need to do now is how to integrate it to facilitate external access, because there is always only one entry for program access, A common solution is to centrally control all data sources through the intermediate proxy layer. Common proxy layer solutions: 1. mysql proxy is similar to the connection pool, and all connections are forwarded through it. 2. Amoeba is a development proxy layer framework with a corresponding version of Amoeba for mysql. It supports data splitting rules, Connection Control, and read/write splitting routing. 3. HiveDB only supports horizontal splitting. The underlying layer is implemented by hibernate shards. 4. other shortcomings of www.2cto.com HSCALE, Spock Proxy (ROR), and Pyshards (Python) are worth noting:
There is a lack of good distributed transaction solutions. Currently, only Innodb provides solutions, and the performance is questionable.
Federated does not provide support for cross-node SQL solutions similar to DBLink. However, Federated does not support table structure changes. These shortcomings can only be solved through the application layer. However, if all the shortcomings are solved, Oracle will not sell as expensive.