Enables high-performance, highly concurrent counter functions

There are many scenarios in the project that need to be applied to the counter, we often need to add some statistical fields that need to be updated frequently, such as: User's points, download number of files, number of likes, number of comments, number of views, etc. and when the frequency of these data updates is more frequent, The pressure on the database has also increased considerably.

Usually in the implementation of the website article hits the time, is so design data table, such as: "article_id, menu_id, Article_name, Article_content, Article_author, Article_view ...
Record the amount of this article in Article_view, which is only a trial of a scenario with a smaller number of visits. How should the counters be designed for sites with large traffic?

MySQL counter: Multi-line Parallel update

To the article information class-based site, in a page when browsing not only to do a lot of check (query the record above, already belong to the classification of the name, hot article Information comments, tag, etc.), but also to write operations (update the number of hits). Put the details of the article and the counter in a table although the development is very convenient, but will cause the database pressure too much. So, two tables to store it? One table saves the details of the article, and the other table saves the counter separately.

CREATE TABLE Article_view (
article_id Int (one) is not NULL,
View Int (one) is not NULL,
PRIMARY KEY (article_id)
) Engine=innodb;

This way, although sharing the pressure of the article table, but whenever a process request updates, will produce a global mutex, only serial, not parallel. There is a long wait time under high concurrency.

Another good way is that the counter for each article is not a row, but more than one line, for example, 100 rows. Each time one of the rows is updated randomly, the number of views for that article is the and of all lines.

CREATE TABLE Article_view (
article_id Int (one) is not NULL,
Pond tinyint (4) Not NULL COMMENT ' pond, which is used for random use ',
View Int (one) is not NULL,
PRIMARY KEY (article_id, pond)
) Engine=innodb;

A small number of random pool 100 is definitely more, 35 is enough. Each time a visit, a random number (1-100) as pond, how the pond exists is updated view+1, otherwise inserted, view=1. With duplicate KEY, otherwise in the program is implemented in the first select, judge the insert or update.

INSERT into Article_view (article_id, pond, view) VALUES (123, RAND () *100, 1) on DUPLICATE KEY UPDATE view=view+1

To get the total number of visits for the specified article:

SELECT SUM (view) from Article_view WHERE article_id= ' 123 '

MySQL Counter: Delayed update

The deferred update feature means that we can set a delay time for the update of the statistics field, in which all updates are cached cumulatively and then the database is updated on a regular basis. This is more appropriate for a field that often needs to be incremented or decremented, and is less stringent for real-time requirements.

Take Thinkphp's deferred update method as an example: Http://document.thinkphp.cn/manual_3_2.html#update_data
Starting with the 3.2.3 version, the Setinc and Setdec methods support deferred updates, using the following:
$Article = M ("article"); Instantiating a Article Object
$Article->where (' id=5 ')->setinc (' View ', 1); Article reading plus 1
$Article->where (' id=5 ')->setinc (' View ', 1,60); Article read count plus 1, and delay 60 seconds Update (write) /thinkphp/library/think/model.class.php

/** field value grows @access public @param string $field field name @param integer $step growth value @param integer $lazyTime delay time (s) @return Boolea N/Public Function setinc ($field, $step =1, $lazyTime =0) {if ($lazyTime >0) {//delay write $condition = $this->options[' whe Re ']; $guid = MD5 ($this->name. " $field. " Serialize ($condition)); $step = $this->lazywrite ($guid, $step, $lazyTime); if (false = = = $step) return true; Wait for the next write} return $this->setfield ($field, Array (' exp ', $field. ' + '. $step)); }

The

/** delay update check returns false to indicate that a delay is required or the actual write value is returned @access public @param string $guid write the identity @param integer $step write the stepping value @param integer $lazyTime delay Time (s) @return False|integer */protected function lazywrite ($guid, $step, $lazyTime) {if (false!== ($value = s) ($GUID))) {//existence of cache write data if (Now_time > S ($guid. ' time ') + $lazyTime) {//delay update is up, delete cached data and actually write to database S ($guid, NULL); S ($guid. ' _time ', NULL); return $value + $step; }else{//Append data to Cache S ($guid, $value + $step); return false;}} else{//No cache data S ($guid, $step);//Timing start S ($guid. ' Time ', now_time); return false;}} /thinkphp/common/functions.php

/** Cache Management @param mixed $name cache name, if the array represents cache settings @param mixed $value cached values @param mixed $options cache parameters @return Mixed */functio N S ($name, $value = ", $options =null) {static $cache ="; Is_array ($options)) {//cache operation is initialized at the same time $type = Isset ($options [' Typ E '])? $options [' type ']: '; $cache = Think\cache::getinstance ($type, $options); }elseif (Is_array ($name)) {//cache initialization $type = isset ($name [' type '])? $name [' type ']: '; $cache = Think\cache::getinstance ($ Type, $name); return $cache; }elseif (Empty ($cache)) {//auto-Initialize $cache = Think\cache::getinstance ();} if (' = = = = $value) {//Get cache return $cache->get ($ name); }elseif (Is_null ($value)) {//delete cache return $cache->rm ($name);} else {//cache data if (Is_array ($options)) {$expire = Isset ($options [' expire '])? $options [' Expire ']:null;} else{$expire = Is_numeric ($options)? $options: NULL;} return $cache->set ($name, $value, $expire); } }

Mysql+memcache Counter: Deferred update

In conjunction with the above diagram, the following flowchart is parsed:

1. Resource views, such as blog details page views = MySQL (data sheet views) + memcache (PageView) each time the guest visits the blog details page, the amount of browsing will be +1, using the browser delay update, only update the memcache in the number of views, and the browser cache key hash into the array (this is very important), when the memcache in the number of views reached a certain value, such as 100, to do an update MySQL data browsing, and immediately set the memcache of the number of views to 0.

2. Because the resource browse amount is saved in Memcache, restart Memcache, or other reason, the browsing amount will be lost, need to develop an additional scheduled task to update the cache's browsing amount to MySQL;

3. The figure of the PageView timer task (such as 3 o'clock in the morning) update memcache cache to the database, at this time (the 1th hash key is particularly important), the timing task becomes a traversal hash array, each hash of value is a set of Browse volume cache key collection, Then traverse the browse volume key to get the browse amount of a resource, update to MySQL, set the PageView cache to 0 in time before the update so that the new PageView updates to the cache is not affected by the scheduled task.

Https://github.com/JingwenTian/CodeLibrary/tree/master/backend/Server/memcache

Redis counters

$r = new Redis ();
$r->connect ("127.0.0.1", "6379");

$URL = $SERVER ["Script_uri"];
$UA = $_server["Httpuser_agent"];
$d = Date ("Ymd");

$userkey _ua = "Stats:". $d. ": UA:". MD5 ($URL);
$userkey _url = "Stats:". $d. ": url:". MD5 ($URL);
$userkey _glob = "Stats:". $d;

$r->sadd ($userkey _ua, MD5 ($UA));
$r->incr ($userkey _url);
$r->incr ($userkey _glob);

Optionally set expire hours from now one,
To be sure'll be available until tomorrow.
$r->expire ($userkey _ua, 3600 * 25);
$r->expire ($userkey _url, 3600 * 25);
We want $userkey _glob to expire in
$r->expire ($userkey _glob, 3600 * 24 * 32);

...
Somewhere at the end of the page ...

Echo sprintf (
"This page is visited%d times today, with%d different browsers!",
$r->get ($userkey _url),
$r->scard ($userkey _ua)
);

Reference

Visitor Tracking with Redis and php http://www.ebrueggeman.com/blog/redis-visitor-tracking
Simple Realtime Web counter http://redis4you.com/code.php?id=009

/http Edagarli.logdown.com/posts/306223/performance-counters-for-high-concurrency-features-such-as-the-article-hits