Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve sub-database and sub-table in MySQL, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First, background introduction

1. A large amount of data storage requires a lot of resources.

2. The continuous growth of the amount of data requires database storage to be scalable.

3. In the case of ensuring a large amount of data, it is necessary to ensure quality requirements such as performance and high availability.

4. The storage problem of large amount of data has not been completely solved in the existing framework.

5. Mass storage solutions such as those are expensive, and MySQL is used to split the database and table to save the cost of IT.

II. Feasibility analysis

1. Risk assessment

1) Resource and specification requirements for DBA database management

two。 The impact of the size and changes of business data volume

1) for medium or above data scale that can be planned in advance, the use of single database sub-table (one database instance, divided into multiple tables), read-write separation, or multi-database and multi-table (multiple database instances, multiple tables) can meet business requirements. And the corresponding design and implementation is relatively simple, not easy to make mistakes.

2) the initial data scale can not be accurately predicted, but with the development of the business, the data storage is required to be scalable. This scalability is solved by sub-library and sub-table, which requires strong scalability in routing, which is also the difficulty of sub-library and sub-table. this scheme proposes a step-by-step implementation route to solve this problem step by step.

3. Technology accumulation

1) the company has a simple scheme of sub-database and sub-table.

2) this scheme lacks scalability

3) this scheme will put forward a scheme to achieve certain scalability in the short term and high scalability in the medium and long term.

4. Open source or product

1) Commercial database Sharding:MySQL Proxy, which provides MySQL protocol interface (non-JDBC), master-slave structure, load balancing, read-write separation, failover, etc., lua syntax is complex, does not support large amount of data sub-database and sub-table.

2) Amoeba, which supports sub-database instances, and each table with the same data does not support transactions. Similar to MySQL Proxy, it is easier to abandon lua in design.

3) the open source CobarClient of Ali Group Research Institute is mainly for small-scale database sharding cluster access, and based on ibatis, it needs to plan the data scale and lacks expansibility; in addition, there is Cobar, a complete DAL layer within Ali Group, which implements a complete JDBC proxy.

4) the sharding provided by HibernateShards,Hibernate, which supports sub-database instances, is complex, and the data scale is planned in advance, which is inconsistent with the framework.

5) guzz, multi-database (virtual database, the routing rules of the actual database are still customized), table slicing, read-write separation, and transparent distributed transaction support between multiple databases, the design goal is to support large-scale online production applications; the need to completely replace ibatis; is completely inconsistent with the framework.

6) TDDL, the DAL of Taobao, has a strong ability to divide databases and tables, so it still needs to plan the amount of data, and the dynamic expansion is limited.

7) some of the above products can meet our needs to some extent, but can not completely solve the problem that we can expand a large amount of data.

III. Performance indicators

1. The maximum delay of each operation is compared with that without the introduction of subdatabase and table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.