PHP结合MySQL或Redis简单设计的秒杀系统，在高并发情况下下单，防止商品库存超发的情况。
环境
1、PHP5.6
2、Redis4.0.2
3、MySQL5.7
MySQL表格
创建产品表product：
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) NOT NULL DEFAULT '',
`num` int(11) NOT NULL DEFAULT '0',
`version` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
创建订单表bill：
CREATE TABLE `bill` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bill_no` varchar(60) NOT NULL DEFAULT '',
`user_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`count` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `userId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
实例
//concurrency.php
<?php
try {
$conn = get_mysql_conn();
$userId = rand(1, 10000);
$bill_no = 'bill' . (int)(microtime(true) * 1000) . $userId;
$buy_num = 1;
miaosha_x($conn, $userId, $bill_no, $buy_num);
//miaosha_redis($conn, $userId, $bill_no, $buy_num);
//miaosha_redis($conn, $userId, $bill_no, $buy_num);
} catch (Exception $e) {
exit($e->getMessage());
}
/**
* @desc: 连接mysql
* @return \mysqli
* @throws \Exception
*/
function get_mysql_conn()
{
$conf = array(
'host' => '192.168.1.40',
'db' => 'test',
'user' => 'jason',
'pwd' => '123456',
'port' => 8888
);
$conn = new mysqli($conf['host'],$conf['user'],$conf['pwd'],$conf['db'],$conf['port']);
if (mysqli_connect_errno()) {
throw new Exception(mysqli_connect_error());
}
return $conn;
}
/**
* @desc: 连接redis
* @return \Redis
* @throws \Exception
*/
function get_redis_conn()
{
try {
$redis = new Redis();
$redis->pconnect("127.0.0.1", 6379); //采用localhost连接会很慢
$redis->auth("beecloud");
return $redis;
} catch (Exception $e) {
throw new Exception("Redis: " . $e->getMessage());
}
}
/**
* @desc: 利用数据库的悲观锁（排它锁X）
*
* @param $conn
* @param $userId
* @param $bill_no
* @param $buy_num
*/
function miaosha_x($conn, $userId, $bill_no, $buy_num)
{
$conn->query('BEGIN');
$sql = "select * from product where id = 1 for update";
$rs = $conn->query($sql);
$row = $rs->fetch_assoc();
if (!empty($row)) {
$num = $row['num'];
$id = $row['id'];
if ($num > 0) {
$sql = "update product set num = num - $buy_num where id = " . $id;
$conn->query($sql);
if (mysqli_affected_rows($conn)) {
$sql = "insert into bill(`user_id`, `product_id`, `bill_no`, `count`)
values($userId, $id, '$bill_no', $buy_num)";
$conn->query($sql);
if (!mysqli_affected_rows($conn)) {
$conn->query('ROLLBACK');
}
}
}
}
$conn->query('COMMIT');
$conn->close();
}
/**
* @desc: 利用数据库的乐观锁（共享锁S）
*
* @param $conn
* @param $userId
* @param $bill_no
* @param $buy_num
*/
function miaosha_s($conn, $userId, $bill_no, $buy_num)
{
$conn->query('BEGIN');
$sql = "select * from product where id = 1";
$rs = $conn->query($sql);
$row = $rs->fetch_assoc();
if (!empty($row)) {
$version = $row['version'];
$num = $row['num'];
$id = $row['id'];
if ($num > 0) {
//$sql = "update product set num = num - 1 where id = " . $id . " and num = $num";
$sql = "update product set num = num - $buy_num, version = version + 1 where id = " .
$id . " and version = $version";
$conn->query($sql);
if (mysqli_affected_rows($conn)) {
$sql = "insert into bill(`user_id`, `product_id`, `bill_no`, `count`)
values($userId, $id, '$bill_no', $buy_num)";
$conn->query($sql);
if (!mysqli_affected_rows($conn)) {
$conn->query('ROLLBACK');
}
}
}
}
$conn->query('COMMIT');
$conn->close();
}
/**
* @desc: 利用redis实现商品秒杀下单
*
* @param $conn
* @param $userId
* @param $bill_no
* @param $buy_num
*
* @throws \Exception
*/
function miaosha_redis($conn, $userId, $bill_no, $buy_num)
{
$conn->query('BEGIN');
$sql = "select * from product where id = 1";
$rs = $conn->query($sql);
$row = $rs->fetch_assoc();
if (!empty($row)) {
$num = $row['num'];
$id = $row['id'];
$redis = get_redis_conn();
$good_amount_key = 'product_' . $id;
//商品总数
if (!$redis->exists($good_amount_key)) {
$redis->setnx($good_amount_key, $num);
}
$good_amount = $redis->get($good_amount_key);
$key = 'product_num_' . $id;
if (!($redis->incrBy($key, $buy_num) > $good_amount)) {
$sql = "update product set num = num - $buy_num where id = " . $id;
$conn->query($sql);
if (mysqli_affected_rows($conn)) {
$sql = "insert into bill(`user_id`, `product_id`, `bill_no`, `count`)
values($userId, $id, '$bill_no', $buy_num)";
$conn->query($sql);
if (!mysqli_affected_rows($conn)) {
$conn->query('ROLLBACK');
}
}
}
//关闭redis连接
$redis->close();
$conn->query('COMMIT');
$conn->close();
}
}
ab测试
测试命令：
ab -c 110 -n 1000 http://localhost/concurrency.php
参数描述：
-n 表示请求数
-c 表示并发数
-t 表示多少s内并发和请求
利用数据库的悲观锁（排它锁X）测试结果：
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests
Server Software: nginx
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /concurrent.php
Document Length: 0 bytes
Concurrency Level: 110 # 并发请求数
Time taken for tests: 4.185 seconds # 整个测试持续的时间
Complete requests: 1000 # 完成的请求数
Failed requests: 0 # 失败的请求数
Total transferred: 130000 bytes # 所有请求的响应数据长度总和，包括每个http响应数据的头信息
和正文数据的长度。注意这里不包括http请求数据的长度，从Web服务器流向用户PC的应用层数据总长度
HTML transferred: 0 bytes # 所有请求的响应数据中正文数据的总和，也就是减去了Total transferred
中http响应数据中头信息的长度。
Requests per second: 238.94 [#/sec] (mean) # 最重要的指标之一:吞吐率，表示当前机器的整体性能，
值越大越好。即：Complete requests / Time taken for tests， mean表示这是一个平均值
Time per request: 460.375 [ms] (mean) # 最重要的指标之二: 用户平均请求等待时间，
即：Time taken for tests / (Complete requests /Concurrency Level)
Time per request: 4.185 [ms] (mean, across all concurrent requests) # 服务器平均请求处理
时间，即：Time taken for tests / Complete requests
Transfer rate: 30.33 [Kbytes/sec] received # 这些请求在单位时间内从服务器获取的数据长度，
即：Total transferred / Time taken for tests
# 花费在连接Connect，处理Processing，等待Waiting的时间的最小min，平均值mean，标准差[+/-sd]，
# 中值median，最大表max的一个表。
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 1 0.9 0 4
Processing: 35 435 102.9 418 805
Waiting: 35 435 102.9 418 805
Total: 39 435 102.7 419 807
WARNING: The median and mean for the initial connection time are not within a normal deviation
These results are probably not that reliable.
Percentage of the requests served within a certain time (ms)
50% 419 # 50%请求的响应时间在419ms内
66% 443 # ...以此类推
75% 455
80% 463
90% 549
95% 667
98% 748
99% 773
100% 807 (longest request)
