Shulou(Shulou.com)06/01 Report--

Types of sub-database and sub-table:

The sub-database and sub-table here refers to physically splitting the data in the database into multiple instances or servers, rather than MySQL's native Partitioining.

The official Partitioning of MySQL can store the database of a table as multiple files separately. If you follow the partition rules when writing SQL, you can transform the work that originally needs to traverse the whole table into the work of traversing only one or part of the partition in the table, thus reducing the pressure on the server for query. But no matter how partitioned it is, all the data is on one server, and there is no way to spread the pressure by horizontally expanding physical services.

Split vertically:

When considering database split, first consider vertical split, then consider horizontal split. Vertical splitting can be understood as the separated database table structure is independent of each other.

1) if there are multiple businesses and there is little correlation between them, different businesses can be divided into separate instances, libraries or tables.

2) if there are multiple databases on an instance, you can split each database into a separate instance.

3) if there are multiple tables in a library, you can split each table into different instances.

4) if there is a table, but there are too many fields in the table, when the table is too large, you can split each or several fields into a table.

Split horizontally:

Horizontal split is for a table, after vertical split, if the database of the table is still too large, for example, the number of registered users is more than 1 billion, then it has to be split horizontally through some algorithm. The result of the split is multiple tables with the same table structure, with part of the data stored in each table. The method of splitting is based on many things, for example, by taking mold 100,100,100, 2014 and so on.

Principles of sub-database and sub-table:

Principle zero: if you can't divide, you can't divide.

If you can upgrade the system to improve the performance of the database, such as upgrading hard disk, cpu, memory, network, database version, read-write separation, load balancing and other aspects to solve problems, do not do sub-table sub-database. That is to say, the premise of doing sub-table and sub-database is that these have been done.

Principle 1: the amount of data is too large, normal operation and maintenance affect normal business access.

1) backing up a database, such as a single table that is too large, requires a lot of disk IO or network IO resources.

2) the modification of the data table, such as the table database is too large, the table will be locked when doing DDL, this time will be very long.

3) the hot data of the whole table, such as the user_last_login field of a table, frequently perform update operations, resulting in excessive pressure on the table.

Principle 2: the table design is unreasonable and some fields are split vertically

1) the number of users has soared from 1 million to 1 billion, and the user_last_login field is constantly being update. The best way is to split the field vertically.

2) the person_info form of the user table is originally useless, but some users will divide their personal information into perfect ones, and what is worse is that the product manager is on a whim. In order to open the field, everyone else can access it, and the type of the field is text, which must be split.

Principle 3: there has been an endless increase in some data

For example, the chat record of the chat system, the recharge record of the recharge system, and so on.

Principle 4: security and availability considerations

Don't put all your eggs in one basket, we don't want something wrong with the database, or we don't want 100% of our users to be affected. Such as the user, inventory, orders and other originally unified resources to be split.

Principle 5: business coupling considerations

The train ticket business has nothing to do with Roast Lamb Leg's business and can be split into different databases.

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.