MySql counters, such as the number of clicks on a website, how to implement high-performance and high-concurrency counters-PHP source code

Source: Internet
Author: User
MySql counters, such as the number of clicks on a website, how to implement high-performance and high-concurrency counters MySql counters, such as the number of clicks on a website, and how to implement high-performance and high-concurrency counters

Clicks: 5338 Date: 2014-03-29 23:30:42 Power By Li Xuan Lane

High-performance TagMysql counters

There are many projects that implement counters at will. for example, when implementing the number of clicks on a website article, we design data tables like: "article_id, menu_id, article_name, article_content, article_author, article_view ...... record the page views of this article in article_view. Fraud seems to be okay. This is what we do for a small website, such as this blog. Is it because a small dish blog involves concurrency issues? The answer is obvious: there are not many IP addresses in a day, and they will not be very large in the future.

Let's get down to the truth. for articles and news projects, when browsing a page, we not only need to perform a lot of queries (query the above Records, name of a category, comment on popular articles, TAG, etc., and write operations (update the number of views ). Putting the detailed content and counter of the article in a table is very convenient for development, but it will cause too much pressure on the database (otherwise, why do large projects require database and table sharding ).

So, can we store two tables? Save the document details in one table, and save the counter in another form.

CREATE TABLE `article_view`(    `article_id` int(11) NOT NULL,    `view` int(11) NOT NULL,    PRIMARY KEY (`article_id`))ENGINE=InnoDB;

In this way, although the pressure on the article table is shared, every time a process requests an update, a global mutex lock is generated, which can only be serialized and cannot be parallel. There will be a long wait time in high concurrency.

Another better way is to counter each article in multiple rows rather than one row, for example, row one hundred. Each time a row is randomly updated, the number of views in this article is the sum of all rows.

Create table 'article _ view' ('article _ id' int (11) not null, 'pond' tinyint (4) not null comment' pool, which is used for random use ', 'View' int (11) not null, primary key ('article _ id', 'pond') ENGINE = InnoDB;

There must be more than 100 random pools with small traffic. During each access, a random number (1-100) is used as the pond. if the pond exists, view + 1 is updated; otherwise, view = 1 is inserted. Use duplicate key. otherwise, SELECT must be implemented in the program, and then INSERT or UPDATE.

Insert into 'article _ view' ('article _ id', 'Pond', 'view') VALUES ('20140901', RAND () * 123, 1) on duplicate key update 'View' = 'View' + 1

When obtaining the total access volume of a specified article:

Select sum ('View') FROM 'article _ View' WHERE 'article _ id' = '20140901'

Ps: everything is a double-edged sword. We usually sacrifice something for faster reading. Reading a large number of tables must speed up reading, while writing a large number of tables must speed up writing. Trade-offs. To speed up reading, we sacrifice not only the write performance, but also the development cost, the complexity of development and the maintenance cost. So it is not the faster the reading speed, the better. you need to find a balance point.

Note: this is only Mysql. some people will say that you can directly read and write Mysql at high concurrency. the bottleneck of the project is in the database... Actually... Here we just talk about how to design Mysql tables. You can use a queue to write tables in this place. you can also save the counters in the memory and accumulate them all the time. the counters are persisted once an hour. You can also use Redis, which claims to read and write 100,000 times per second.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.