How to optimize the Voting problem in mysql with High concurrency
for example, ten thousand people vote for one person
what I do is to read the number of votes first and then get the votes + 1
, but if ten thousand people vote at the same time, the number of votes and updates must be incorrect
.
The
code is as follows: phpcms is used to ask God how to optimize the code. The simple structure is that there are more voters
.
//ID
$r = $db->get_one(array("id" => intval($_GET["id"])));
//
$num = $r["num"] + 1;
$sql = array("num" => $num);
$db->update($sql, array("id" => intval($_GET["id"])));
cordially invited.
if you don't want or don't want to introduce redis into phpcms.
I haven't redeveloped phpcms, but you can add it directly in sql instead of using code to add it
update student set score=score+1 where id = 1
this avoids inaccurate voting
if you have time, you can use redis or queue to implement this function.
< H2 > I have a two-step plan for this requirement < / H2 >
1. Put the total number of users clicking or canceling votes in the cache. Redis,memcache is fine. It is recommended that redis;
2. Cache whether a user votes or not, as above;
3. The logic of the data displayed and judged by the user is directly cached, and then the Synchronize data is sent to the mysql server through a timing script.
4. If, for the sake of safety, locks can be added when updating or writing, to ensure the consistency of the data, the prerequisites should be based on logic, and there will be no deadlocks.
if you just ensure that the data is normal, you can do it by adding an exclusive lock to the transaction.
it is supposed to be saved in redis first, and then written to the database only after voting is over.
simply lock the MySQL directly, but the performance is much lower.
can be read from redis and redis,. Update is also an update in redis (incr operation).
opens a task to write the number of votes in the cache to MySQL when the voting is over.
this vote I suggest you vote with redis and update to mysql regularly because update may cause locking tables, and you need select to read the number of votes. In the case of large concurrency, it may directly lead to mysql gone away
this is actually an application of queue control and locking. That is, when a user writes, lock it up first and do not give others control over the data until one modification is completed, and then go to the next person to modify it.
you can learn about related articles under data locks or data queue lookups. The specific implementation can be sorted through the temporary table and processed one by one. Of course, if dealing with highly concurrent data, it is generally better to use databases such as redis for master-slave data processing.
look at your concurrency and server performance.
in a nutshell, there are a few things you can do to deal with your problems.
- uses the
redis list to record everyone's vote and process the database asynchronously.
- the database is locked, but it depends on faith when there is high concurrency (for example, only one low-end server + 10,000 people
3s can access it at the same time).
- optimize your table design to keep this table as simple as possible and choose the appropriate transaction isolation level.
- the physical machine of the server is replaced with a hard disk with better read and write performance.
as long as the table is not deadlocked and high concurrency is controlled, there is basically no problem, and there is no need to lock the table. Do a good job of asynchronous processing
