On the structure optimization of database

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

The optimization of database has always been an important problem to be addressed in the operation of many large websites. For example, at the end of March 2012, I have participated in the development of a province's provincial government information public release system, after 4 months of functional development and testing, the system is officially online, because the system uses the regional substation mode, according to the "province-city-county-town-village" site model, by the government-led, The system soon began to be used in all areas of a province, with the release of daily government information gradually increased, the results 2 months later, found that the system has been slow query problems. According to the preliminary calculation, a province a total of more than 15,000 administrative villages, 1200 towns, 100 counties and 9 municipalities, assuming each village, town, county, city, province, daily release of 10, then the daily amount of information will reach (15000+1200+100+9+1) *10=163100 data , then 1 months of data will reach 163100*30=4893000︾5000000, then two months will reach 10 million, a year will reach hundreds of billions of data. Faced with such a large amount of data, query efficiency will also become a performance bottleneck of the system.

In a two-month period, the number of information bars that the database reaches tens is a large amount of data. Especially in the first 1-2 months of the process, we can also in 5 seconds or so within the time range of information inquiries, but with the increasing amount of information, now every time the summary and report will have to wait for a long time, up to more than 20 seconds. Check the original selection of the server performance configuration is no problem, then we only through the optimization of the database structure to improve the efficiency of the query data.

There are many ways to optimize database structure, one is to use stored procedures to replace common SQL query statements, the other is to use partitioned table method in database management system. The optimization method of using stored procedures has the advantage of fast execution, but it is not conducive to debugging, no way to use the database caching mechanism, and so on, so in the case of higher system security and performance requirements, it is recommended to use partitioned table method. But note: It is not as long as the amount of data required to use the database partition table to improve query efficiency, but to the data is segmented, we need to consider whether the partition table needs to be used. Obviously, combined with the above example, the Government information public release system, the release of information is based on the time period to query, so we can do database table partitioning.

There are two ways to table partitions of a database, horizontal table partitioning and vertical table partitioning.

The purpose of a horizontal partition is to divide a table into multiple tables. Each table contains the same number of columns (table fields), but the number of records (data rows) is reduced. For example, we could divide a database table containing 100 million rows into 12 small tables, each representing the data from January to December, respectively, in a horizontal partition. In this way, any query that needs to query for a particular month's data can only query the table of the corresponding month and avoid querying from data from all the months stored in the 1 large tables. Depending on the execution efficiency of the SQL statement, there is no doubt that the query efficiency in the small table is much higher than the query efficiency from the large table. Vertical partitioning is the opposite of horizontal partitioning, from portrait to partition, dividing an original table into multiple tables that contain only a few columns. In everyday applications, horizontal partitioning can be said to be the most commonly used partitioning method, and all of the following are described in horizontal partitioning.

In the simplest terms, a database partition table divides a large table into many small tables. Here, we take the government information public table as an example, the table records all the time period of the issue of government information, then we can do horizontal partitioning, the large information table divided into several small tables, assuming that this is divided into 10 small tables. 2012, 2013, 3721.html ">2014 year ... 2021. Then, if you want to query which year records, you can go to the corresponding table to retrieve, because the number of records in each of the tables is much less than a large table record number, and its query efficiency will be significantly improved.

But we should also note that if you divide a large table into smaller tables, it will add to the burden on the software developers. For example, with record additions or modifications, the above 10 tables are separate 10 tables, and programmers need to use different SQL statements when recording additions or modifications at different times. For example, when adding records in 2012, programmers add records to the 2012 table; When adding records in 2013, programmers add records to the 2013 table. In this way, the programmer's workload increases and the likelihood of a program error increases. So how do we do horizontal partitioning in this situation?

The use of partitioned tables can be a good solution to the above problems. Partitioned tables can be physically divided into small tables from a large table, but logically, a large table. A partitioned table can divide an information record table into several physical small tables. But for programmers, they're still facing a big table, whether it's 2012 or 2013, and it's not for programmers to think about, he just inserts records into the Information log table ( In the logical sense of the big table) in the line.

Corresponding to the operation of the database partition table, different databases have their own way to create database partitions, such as Microsoft's database SQL Server can implement the database partition table operation, through the relevant settings can be implemented after the database partition operation. The steps to create a partitioned table using SQL Server can be done using SQL commands, or you can use the wizard from the database itself to complete the database partition operation.

This article by the psychologist http://www.psybook.com feeds, reprint please indicate the source!