SQL Request Optimization--View count, SQL write operation dilution

Introduction

A few days ago to do such a thing: a game has an activity page, the Activity page has a product, the product below to show the actual number of views, it is equivalent to the user every request for the total number of visits will be added once, the problem is simple, each time you browse to the database to do "+1" operation. But when I do, I can dilute the SQL request and reduce the number of "+1" requests due to the fact that the total number of real-time requests is not so high, which reduces the resources wasted by "write" when interacting with the database.

Implement

This special environment of the SQL request is this, each time to perform the browse number of "+1" operation, we produce a 1~n random number r, if r%n = = 0 for "+n" operation, N can be based on the total number of views of the real-time level of the appropriate adjustment, the higher the real-time, n the smaller, The greater the chance that the write operation will hit, or vice versa, look at the following:

Here are some of the code (just the test simulation, the description meaning):

1 classaddtimes{2     /**3 * Assuming this is a DB object4      */5     Private Static $db;6 7     /**8 * Test "browse times" dilution9 * int $hit dilution magnification, average $hit for one recordTen      */ One      Public Static functionDoadd ($hit){ A         $hit=intval($hit); -         $r=Rand(1,$hit); -         $r%$hit= = 0 && self::$db->query ("Update testtimes set times=times+{$hit}"); the     } -}

This reduces the write operation to a certain extent and merges the N write operations into one at a time.

Summary

This is a small optimization, but his limitations are very large, use the time to pay attention to the following points:

1. Before using the data to consider the real-time requirements, real-time requirements of high or not use.
2. This expected total number of times must be greater than n, preferably many times, because it is a random number, if the total number of times too little there is a large error.
3. If you can use the cache, it will be better, such as storing the number of times in memchached, Redis, APC and other caches, each time the "+1" operation, so that the high real-time can be achieved, the number of hits logic is still there, when hit with data persistence (write-back db), At this time this write-back n can be properly adjusted to large.

This article copyright belongs to the author ([email protected]) and the blog garden in total, without the author's consent to prohibit any form of reprint, reproduced article must be in the article page obvious location to the author and the original text connection, otherwise reserves the right to pursue legal responsibility.

SQL Request Optimization--View count, SQL write operation dilution