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

This article mainly explains "what is the stage of database optimization". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. let's follow the editor's train of thought to study and learn "what is the stage of database optimization"!

0x01:SQL optimization and application optimization

A system is always from small to big, so when the system is still a small system. First of all, we should pay attention to the design of the table, make rational use of primary key, foreign key and index, and select the appropriate data type and length of the field. Use SQL to consider when an index is valid and when it is invalid, avoid using index-invalidated SQL.

In addition, it is also important to optimize the program. For example, if you can execute SQL in batches, do not cycle through SQL in the program. It is also important to use caching in various frameworks, for example, if you use MyBatis, you can make rational use of MyBatis's primary cache and secondary cache; when using Hibernate, you can make rational use of Hibernate's primary cache and secondary cache, and so on.

0x02: database read and write classification

When the volume of business reaches a certain degree, the read-write separation scheme of the database can be considered to separate the read-write business. However, we must consider how to solve the problem of data consistency between write library and read library. At present, there are many open source read-write separation services and frameworks. It is mainly based on the following two schemes:

The application program judges according to the business logic, the write operation commands such as additions, deletions and modifications are sent to the write library, and the query commands are sent to the reading library.

Using middleware as a proxy, it is responsible for identifying whether to read or write to the request of the database, and distribute it to different databases.

0x03: introducing cached database

NoSQL databases are also very popular at present, especially Redis databases. For example, using a Redis cache server, you can cache some commonly used and infrequently changed data into memory. The speed of reading data in memory is many times faster than that on hard disk. The introduction of cache database not only increases the complexity of the system, but also considers the data consistency between relational database and NoSQL database.

0x04: split vertically

A database is composed of many tables, and each table corresponds to a different business. Vertical sharding means that tables are classified and distributed to different databases according to business, so that the data or pressure is shared to different databases, as shown below:

Advantages:

After the split, the business is clear and the split rules are clear.

It is easy to integrate or expand between systems

Data maintenance is simple

Disadvantages:

Some business tables can not be join, so they can only be solved by interface, which increases the complexity of the system.

Due to the different limitations of each business, there is a performance bottleneck of a single database, so it is not easy to expand data and improve performance.

Transaction processing is complex

0x05: split horizontally

If you encounter a stand-alone bottleneck after vertical split, you can use horizontal split. The difference from vertical split is that vertical split splits different tables into different databases, while horizontal split splits the same table into different databases.

Compared with vertical split, horizontal split is not to classify the data of a table, but to spread it into multiple libraries according to some rules of a field, and each table contains part of the data. To put it simply, we can understand the horizontal segmentation of data as the segmentation of data rows, that is, some rows in the table are split into a database, while other rows are split into other databases. There are two modes: sub-table and sub-database. As shown below:

Sub-library mode

Sub-table mode

Advantages:

There is no performance bottleneck of single library big data and high concurrency.

Transparent to the application, less modification on the application side

Split according to reasonable split rules, join operation basically avoids cross-database

The stability and load capacity of the system are improved.

Disadvantages:

Split rules are difficult to abstract

The consistency of fragment transaction is difficult to solve.

It is difficult to expand the data many times and has a great amount of maintenance.

Poor performance of cross-library join

Thank you for your reading. the above is the content of "what is the stage of database optimization". After the study of this article, I believe you have a deeper understanding of what the stage of database optimization is. Specific use also needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.