How Cao Zheng reduces SQL requests

First, in order to prevent certain spider professionals from playing without limits, we should first declare several prerequisites.

1.

2: There are many branches to optimize database queries. Reducing SQL requests is only one of the fields. Other branches are not covered in this article.

3: In some scenarios, you even need to add SQL statements to solve such problems as distribution or other issues. This article does not cover these issues.

4: This article does not cover O & M optimization and other optimization methods.

5: This article does not cover product business logic optimization.

6: For other content not mentioned in this article, you are welcome to join us. If you are technically superior, ignore this article.

First query request analysis and cropping

In the online system, when there are many requests and there is a lot of data pressure to optimize indexes in place), I will ask programmers to output query requests for a period of time. Generally, database operations have encapsulated objects and logs can be directly recorded. It is recommended to write/dev/shm to reduce the I/o pressure. If the request frequency is very high, a certain proportion of logs can be written ), then, it is based on log analysis.

1. The number of identical query requests and the average number of such queries per second.

For example, normally, all pages are loaded with System Information select * from systeminfo;

2. The average number of queries per second based on the same primary key of the same data table

For example, select name from userinfo where uid = 10134; select email from userinfo where uid = 10134;

These two types of requests can be optimized by establishing a cache mechanism,

This analysis will provide a good data recognition.

The number of query requests processed by the current database per second, the number of redundant requests that can be optimized, and the number of requests that can be reduced if a cache is set up. How much is the system support improved?

For some open-source systems that are not particularly good, we can find that the number of query requests that can be tailored is huge.

Second, update request analysis and cropping

Update requests can also be optimized,

We usually use mysql to unbind the binlog file, restore it to a text file, and then analyze

Based on the same data table, the number of update requests for the same primary key and the average number of such requests per time period

Example 1:

Update user set lastacttime =... where uid = 10314; update the last active time frequently.

Example 2:

Update posts set views = views + 1 where pid = 10004211; update the number displayed in the same post

If there are many such requests, you can establish a specific queue and regularly update the queue asynchronously.

During asynchronous update

Example 1 multiple requests only need to remember the last one of the same primary key;

Example 2 multiple requests can be merged in the program, and only one database operation is performed.

In this way, the frequency of updating requests is greatly reduced.

If you have real-time requirements online, you can keep a memory data synchronously updated online.

The method is actually very simple, but very effective

Summary

First, keep abreast of the frequency of read/write requests.

Second, read/write requests with the same primary key for the same data table within a certain period of time can be optimized and cropped. However, the system load composition, request frequency, and impact degree should also be considered, you can solve the main problems.

In this way, for other aspects, see the prerequisite description.

Edit recommendations]

1. Restore SQL Server simple mode by mistake Delete heap table records
2. Microsoft SQL Server Data Engine and Analysis Service
3. SQL Server Data Mining Rules for product Recommendation 1
4. SQL Server advanced content: subqueries and table links
5. Data Compression in SQL Server 2008