optimization of SQL database for millions of loads

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

When considering a website that carries millions or tens of thousands of PV, we tend to think in two directions: static and distributed. Of course, for Sina and other portal types of Web sites, static + read and write separation and distribution, can solve most of the problems. But we are faced with more, may be large data + high concurrency + real-time reading, and static immobilization is difficult to solve this problem.

For example, a short sesame recently received an E-commerce Web site, traffic, large amount of data, at the same time require 8206.html "> real-time updates, because the home page and content pages are always changing every moment." This is when we have to query the database, and we need to make some specific optimizations for the database.

Optimization Recommendations:

1) Table structure optimization

By optimizing the table structure, the pressure of the database can be reduced directly. Common methods such as indexing, disassembling, and flattening of appropriate redundancy. The most direct role of the index, through the B + tree easy to achieve similar to a table divided into hundreds of tables of the effect, in the query, often linked list query needs 100,000 times to query the data, add the index may only need four times to be able to solve.

Of course, do not unduly increase the index, because it will increase the burden of the database to some extent. At the same time to say a very interesting question, how to implement the ID card query system? We often know that the first few of the ID card determines the location of the person, the date of birth and other visible information, and then a few random numbers. And should have found a problem, that is only in the account location can register ID card, this is a checklist query. The public security system only needs to inquire the local database to have the birthday and the random number whether to have the duplication, can determine whether has the duplicate identity card in the country.

The most commonly used method is that the active users within one months to set up a separate table, so that each user login, in fact, just query the month's users, can effectively improve the efficiency of the query.

2 Optimization of SQL statements

SQL statement optimization is often a lot more straightforward, that is, as much as possible to reduce the like query, associated query and other resource consumption is larger, query volume than a large SQL statement, you can, sorted out a few as follows:

1. Reduce unnecessary like query, as far as possible through the establishment of keyword table to achieve like query effect

2. Reduce the associated query, as far as possible through the left link to the right link to implement the query

3. Complex query statements are resolved using stored procedures.

4. Reduce unnecessary queries.

5. Avoid using is null and are NOT NULL on indexed columns

6. Avoid the use of resource-consuming operations: SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine

7. Do not use SELECT *, try to indicate the column to be displayed

8. Do not use inserts to import large numbers of data please do not do this unless it is necessary. Use UTS or bcp so you can have both flexibility and speed.

3 The optimization of the program section is particularly important

The program section is optimized according to different user requirements, especially important.

The most common case is that when you query a list, you can solve the problem by querying only the title name and the profile. When the user opens the article, it will query the content of the article, comments, published information, and other related tables. This can greatly reduce the burden of the program.

As far as possible through Ajax, if necessary, background query, rather than open the page when the relevant query.

In addition, the most important point is to try to achieve static.

Generally for CMS-type Web sites, static can solve most of the problems. Then need to query the database is nothing but search, user member Center related operations.

And the search can be read and write to separate asynchronous operations, which can effectively reduce the pressure on the server.

Of course, there are more and more effective methods, just a sudden rise today to tidy up, there are problems with the short sesame to communicate oh ~