High performance mysql (version 3)_ Website click counter

Time:2021-6-16

Website visits statistics, their own ideas:

Whenever a user visits a page, the front-end initialization method automatically calls the back-end interface for access statistics. A counter table can be created separately. Using an independent table can help avoid query cache failure.

udpate hit_counter set cnt=cnt+1 ;

But when a large number of users visit the website at the same time, the modification of CNT field may cause short-term blocking.

(the problem is that for any transaction that wants to update this row, there is a global mutex on this record. This makes these transactions only serially executed. To achieve higher concurrent update performance, you can also save the counter in multiple rows and randomly select one row at a time to update.)

In reading this book, it provides an idea.

Method 1

If you need to get the total number of hits (regardless of the date), you can create a table field.

create table hit_counter (slot tinyint unsigned not null, cnt int unsigned not null) ENGINE=INNODB

Add 100 rows of data to this table in advance. Now select a random slot to update

update hit_counter set cnt=cnt+1 where slot =rand()*100

To get statistical results, you need to use the following aggregate query

select sum(cnt) from hit_counter;

(for personal understanding, you don’t need to insert 100 rows in advance. Set the slot as the primary key. When inserting the slot field, use rand() * 100 and the following on duplicate key update. It’s just that you can insert 100 rows here to understand the following example.)

When you need to start a new counter every day or every month (every other period of time), design the table like this

create table daily_hit_counter(
day date not null,
slot tinyint unsigned not null,
cnt int unsigned not null,
primary key(day,slot)
) engine =innodb;

There’s no need for Mr. Yu to make a trip here

on duplicate key update

replace

insert into  daily_hit_counter(day,slot,cnt) values(current_date,rand()*100,1) on duplicate key update cnt=cnt+1;

Here, day and slot form a joint primary key to count the number of hits on that day.

If you want to reduce the number of rows in the table to avoid the table becoming too large, you can write a cycle task, merge all structures into slot 0, and delete all other slots.

UPDATE dayily_hit_counter AS c
INNER JOIN ( SELECT DAY, sum( cnt ) AS cnt , min( slot ) AS mslot FROM daily_hit_counter GROUP BY DAY ) AS x USING ( DAY ) 
SET c.cnt =
IF
    ( c.slot = x.mslot, x.cnt, 0 ),
    c.slot =
IF
    ( c.slot = x.mslot, 0, c.slot );
    delete from daily_hit_counter where slot <> 0 and cnt=0;

Why is the same rand () result different.

This work adoptsCC agreementReprint must indicate the author and the link of this article

love ,live in the moment.

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]