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

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

How to divide the database and table in SQL

Option 1:

Improving data processing capabilities by improving the hardware capabilities of the server, such as increasing storage capacity, CPU, etc., is expensive, and if the bottleneck is

MySQL itself is also very good at improving the hardware.

Option 2:

The data is scattered in different databases to reduce the amount of data in a single database to alleviate the performance problems of a single database, so as to achieve the purpose of improving the performance of the database, as shown in the following figure: the e-commerce database is divided into several independent databases, and the large table is also divided into several small tables, through this method of database splitting to solve the performance problem of the database.

In order to solve the problem of database performance degradation caused by too much data, the original independent database is divided into several databases, and the large data table is divided into several data tables. make the amount of data of a single database and a single data table smaller, so as to achieve the purpose of improving the performance of the database.

The way of dividing the database and table

Sub-library sub-table includes sub-library and sub-table two parts, in production usually includes: vertical sub-library, horizontal sub-table, vertical sub-table, horizontal sub-table four ways.

Vertical subtable

The following table is divided vertically through a case of commodity query:

Usually, the product details are displayed in the product list.

When browsing the list of goods, users will view the detailed description of the product only if they are interested in it. Therefore, the access frequency of commodity description field in commodity information is lower, and the storage space of this field is larger, and the IO time of accessing a single data is longer; the access frequency of other fields in commodity information, such as commodity name, commodity picture, commodity price and so on, is higher.

Because of the different characteristics of the two types of data, he considered splitting the commodity information table as follows:

The commodity description information with low access frequency is stored in a separate table, and the basic product information with high access frequency is placed in a separate table.

The following sql can be used for the list of items:

SELECTp.*,r. [geographical area name], s. [shop name], s. [reputation] FROM [commodity information] p

LEFTJOIN [geographical area] rONp. [place of origin] = r. [geographical area coding] LEFTJOIN [store information] sONp.id=s. [owned store] WHERE...ORDERBY...LIMIT...

When you need to obtain a product description, you can obtain it through the following sql:

SELECT*

FROM [Product description] WHERE [Commodity ID] =?

This step of optimization carried out by Xiaoming is called vertical sub-table.

Vertical split table definition: a table is divided into multiple tables according to fields, and each table stores some of the fields.

The improvements it brings are:

1. In order to avoid IO competition and reduce the chance of locking the table, users who view details and browsing product information do not affect each other.

two。 Give full play to the operational efficiency of popular data, the high efficiency of the operation of commodity information will not be dragged down by the inefficiency of commodity description.

Note:

Why is the large field IO inefficient:

The first is that because of the large amount of data itself, it takes longer to read.

The second is the spread, the page is the database storage unit, a lot of search and positioning operations are based on the page, the more data rows in a single page, the better the overall performance of the database, while large fields take up more space, less rows are stored in a single page, so the efficiency of IO is low.

Third, the database loads the data into memory in behavior units, so that the length of the fields in the table is shorter and the access frequency is higher, the memory can load more data, the hit rate is higher, and the disk IO is reduced, thus improving the performance of the database.

Generally speaking, the access frequency of each data item in a business entity is different, and some of the data items may be BLOB or TEXT that take up more storage space. For example, the product description in the above example. Therefore, when there is a large amount of data in the table, the table can be cut by field, and the hot fields and unpopular fields can be placed in different libraries, which can be placed on different storage settings to avoid IO competition. The performance improvement brought about by vertical sharding is mainly focused on the operational efficiency of hot data, and disk contention is reduced.

Usually we split vertically according to the following principles:

1. Put the less commonly used fields on a separate table

2. Separate the large fields such as text,blob and put them in the schedule

3. The columns of frequently combined queries are placed in a table

The above content is how to realize the sub-database and sub-table in SQL. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.