Database optimization-Horizontal slicing-and application in real-world projects

Database horizontal partitioning, relatively vertical partitioning, need to do more work and things to do, but for some of the rows of data particularly many tables, it is necessary.

In my continuous optimization of the BDC project, the following several commonly used database horizontal segmentation methods are summarized:

1. Table partitioning;

2. Table splitting;

3. Table sub-Library;

Table partitioning

Table partitioning is a very powerful feature in Oracle and the new version of MySQL database. Well worth learning.

However, if the table partition is not used properly, performance will decrease. I remember one of our other project groups, and they tried to use table partitioning, so we arranged for a developer with no relevant experience to study, study for a few days, and then discover that it was slower, so the schedule for table partitioning ended. This is an off-topic.

In the selected table partition this scheme, the first thing to do, that is to build confidence, table partition in the processing of large table data, it is certain to have effect, if the effect is not obvious or even the effect of poor, then first of all to consider is not to use the place is not quite right.

First of all, the actual situation of our project, the core telephone table size is 120 million, we choose the scheme is table partition, then the next step is to according to the data situation, the different dimensions of these data to differentiate, so that it better for business services.

One of the key elements of our table is the region; each telephone message corresponds to a region, which is a fundamental feature of our nationwide telecommunications data.

So what is the difference between the provincial level and the city level? We have the data of the provincial administrative district is 32, the city is more than 300. Partitions can not be too fragmented, unless there is a definite need, or later operation is a very headache. Then we are on the provincial level zoning as a condition.

Partition design, if the SQL is to be reflected, if the partition is not reflected, it is possible to reduce efficiency.

The use of partitioning is simple, one is directly explicit partitioning, such as

SELECT * FROM Tel PARTITION (310000), where PARTITION (310000) refers to data partitioning in Shanghai, where 310000 is the corresponding national standard Code of Shanghai;

This is a kind of writing, I personally more inclined to another kind of writing:

select* from Tel t where T.prov_code = 310000

Where the label of the partition is created on the Prov_code field, Oracle automatically calls the partition's attributes.

As a result, after a similar partition transformation, peak throughput from the previous day 4 million data, the database will be hung off, up to the day to deal with 12 million of data, the database seems to have spare capacity, the effect is good.

Table partitioning involves a lot of concepts and functions, and is free to collate the content.

Table splitting

Table splitting is an ancient technology, but also very practical. We used to do the telecommunications yellow pages, when the table partition is not mature, we deal with big table The most common technology is the table split.

Table splitting is logically straightforward, and we have a 90 million-data-Book table containing 31 provincial company data, which is maintained by 31 provincial companies.

90 million of the data, whether retrieved, modified, deleted, has a high performance impact. Then just follow the provinces and divide them. Create 31 tables Instead of this one, and then create a nationwide view for the group to use.

Table Sub-Library

We mentioned a project team next door in the table partition, trying to use the table partition was unsuccessful, but the living person could not be suffocated by the urine, they also found their suitable method, this approach is the table sub-Library.

What is a table sub-library? In fact, our database is the main preparation mechanism, is a table sub-library of the embryonic. How did they do it, first prepare the hardware, buy a set of database hardware, then copy the existing database to the new hardware, clean up the business data, keep the constant data, and then make a simple adjustment to the system, the south of the Yangtze River into the old system, access to the old database, the north of the Yangtze River into the new system, Access the new database.

In addition to spending a little, the risk of transformation is quite small. Of course, not all systems can be divided into such libraries.

To sum up, when the database reaches a certain scale, vertical segmentation and horizontal segmentation are the project leader must consider the issue, regardless of the use of the method, you need to understand the business, based on the actual situation of the system, choose the most appropriate method.

Database optimization-Horizontal slicing-and application in real-world projects