继续昨天的协程初探, 我们继续来编写数据库等操作。
首先我们准备好数据库账号及表, 数据:
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';
FLUSH PRIVILEGES;
我们建立了一个mysql的账号test, 并设置host为%即不限制客户端连接, 然后赋予操作当前服务器所有数据库的所有权限。
现在来建立数据库和表:
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4; -- 如果已经有了test数据库, 请忽略
USE test;
-- 建表 users
CREATE TABLE `users`(
`user_id` INT UNSIGNED AUTO_INCREMENT,
`user_name` varchar(20) NOT NULL,
`create_time` INT NOT NULL,
PRIMARY KEY(`user_id`)
) ENGINE=InnoDB COMMENT '用户表';
-- 创建生成随机字符串的函数
DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS varchar(255)
BEGIN
DECLARE chars varchar(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE rt varchar(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET rt = CONCAT(rt, SUBSTRING(chars, FLOOR(rand() * 62 + 1), 1));
SET i = i+1;
END WHILE;
RETURN rt;
END $$
DELIMITER ;
-- 如果创建函数时报错Error Code: 14181, 请执行: set global log_bin_trust_function_creators=TRUE;
-- 编写存储过程, 实现批量插入数据功能
DROP PROCEDURE IF EXISTS mysp_batch_insert_users;
DELIMITER $$
CREATE PROCEDURE mysp_batch_insert_users(IN n INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;-- 关闭默认的自动提交, 可极大的提高插入速度
WHILE i < n DO
SET i = i + 1;
INSERT INTO `users`(user_name, create_time)
VALUES(rand_string(5), UNIX_TIMESTAMP(now()));
END WHILE;
COMMIT;
END $$
DELIMITER ;
-- 执行存储过程, 插入10k数据
CALL mysp_batch_insert_users(10000);
注意上面在大批量插入数据时, 使用了
SET autocommit = 0
设置, 我们可以控制何时commit, 在填充测试数时很有用, 比每次循环插入多次INSRET INTO tb()VALUES(),...
都要快很多很多, 普通ssd一分钟可以生成60w+数据
ok, 数据已经准备好,是时候让php上场了(其实它才是主角?)
use Swoole\Runtime;
use Swoole\Coroutine;
use function Swoole\Coroutine\run;
//开启协程. 自动将文件操作,sleep,mysqli,pdo,streams等都变成异步IO
Runtime::enableCoroutine();
$time_start = microtime(true);
//Swoole\Coroutine\run()
run(function(){
//5k PDO read
echo 'PDO'.PHP_EOL;
for($n = 50; $n --;){
Coroutine::create(function(){
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test;charset=utf8', 'test', 'test');
$statement = $pdo->prepare('SELECT * FROM `users` LIMIT 10');
for($n2 = 100; $n2 --;){
$statement->execute();
assert(count($statement->fetchAll()) > 0); //如果获取到的数据个数为0, 则终止(不会抛出"Warning")
}
});
echo ($n+1) . "\t";
}
//5k MySQLi read
echo PHP_EOL.'mysqli'.PHP_EOL;
for($n = 50; $n--;){
Coroutine::create(function(){
$mysqli = new mysqli('127.0.0.1', 'test', 'test', 'test');
$statement = $mysqli->prepare('SELECT user_id FROM `users` LIMIT 10');
for($n2 = 100; $n2 --;){
$statement->bind_result($user_id);
$statement->execute();
$statement->fetch();
assert($user_id > 0);//如果user_id<=0, 则抛出"Warning"
}
});
echo ($n+1) . "\t";
}
});
$time_end = microtime(true);
echo 'Time elapsed: '.($time_end - $time_start).' s'.PHP_EOL;
在上面的php代码实际执行过程中, 因为表中有1w数据, 如果不做limit, 时间在40s以上, 做了limit 10或者limit 1, 时间都是在零点几秒.
我们以不使用协程的做比较, 代码如下:
$time_start = microtime(true);
//5k PDO read
echo 'PDO'.PHP_EOL;
for($n = 50; $n --;){
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test;charset=utf8', 'test', 'test');
$statement = $pdo->prepare('SELECT * FROM `users` LIMIT 10');
for($n2 = 100; $n2 --;){
$statement->execute();
assert(count($statement->fetchAll()) > 0); //如果获取到的数据个数为0, 则抛出"Warning"
}
echo ($n+1) . "\t";
}
//5k MySQLi read
echo PHP_EOL.'mysqli'.PHP_EOL;
for($n = 50; $n--;){
$mysqli = new mysqli('127.0.0.1', 'test', 'test', 'test');
$statement = $mysqli->prepare('SELECT user_id FROM `users` LIMIT 10');
for($n2 = 100; $n2 --;){
$statement->bind_result($user_id);
$statement->execute();
$statement->fetch();
assert($user_id > 0);//如果user_id<=0, 则抛出"Warning"
}
echo ($n+1) . "\t";
}
$time_end = microtime(true);
echo 'Time elapsed: '.($time_end - $time_start).' s'.PHP_EOL;
下图是使用协程和同步处理的执行时间比较: