MySQL counters, such as site hits, how to achieve high-performance concurrency counter function

MySQL counters, such as the number of site hits, how to achieve high-performance concurrency counter function clicks:5338Date: the-Geneva- in  at: -: thePower by Li Xuan lanetagmysql counter High performance now there are a lot of projects, the implementation of the counter is very arbitrary, such as in the implementation of the website article hits, is so designed data table, such as: "article_id, menu_id, Article_ Name, Article_content, Article_author, Article_view ... Record the number of views of the article in Article_view. There seems to be no problem with a bluff. For a small station, such as this blog, is to do so, because the side of the blog will be involved in the concurrency problem?      The answer is clear, there is not much IP in a day, and it will not be big. To the point, the article information-based projects, when browsing a page not only to carry out a large number of checks (query the record above, already belong to the classification of the name, hot article Information comments, tags, etc.), but also to write operations (update number of hits).      Put the details of the article and the counter in a table although the development is very convenient, but it will cause the pressure of the database is too large (otherwise, why large projects are divided into the library table it). So, is it okay to have two tables in store? One table saves the details of the article, and the other table saves the counter separately. CREATE TABLE ' Article_view ' (' article_id 'int( One) Not NULL, ' view 'int( One) 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) not NULL, ' pond ' tinyint (4) Not NULL COMMENT'the pool is used for random use.', ' View 'int( One) 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- -As pond, how the pond exists is updated view+1, otherwise insert, 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 () * -,1) on DUPLICATE KEY UPDATE ' view ' = ' view ' +1gets the total number of visits for the specified article: SELECT SUM (' View ') from ' Article_view ' WHERE ' article_id '='123'Ps: Everything is a double-edged sword. We usually sacrifice something for a faster reading. In reading more tables to speed up the reading speed, in writing more tables to speed up writing. Weigh each other. At the speed of reading, we sacrifice not only the performance of writing, but also the development cost, the more complicated development, the maintenance cost and so on. So the faster you read the better, the more you need to find a balance. Note: Here is only the MySQL aspect, some people will say high concurrency under you this is directly read and write MySQL, Project bottleneck is in the database ... In fact... This is just about how the MySQL table is designed. You can use the queue to write the table in this place, you can also save the counter in memory, keep accumulating, 1 hours to persist once. You can also use Redis, which is known to read and write 100,000 times per second. 

MySQL counters, such as site hits, how to achieve high-performance concurrency counter function