参考文档
https://blog.csdn.net/selectdb/article/details/82286812
https://www.cnblogs.com/songxuan/p/9469098.html
1.max_execution_time
mysql5.6可以使用pt-kill
https://www.percona.com/doc/percona-toolkit/LATEST/pt-kill.html
mysql 5.6 及以后,有语句执行超时时间变量,用于在服务端对 select 语句进行超时时间限制;
mysql 5.6 中,名为: max_statement_time (毫秒)
mysql 5.7 以后,改成: max_execution_time (毫秒)
2.设置
默认是0,没有超时时间
查询
show variables like 'max_execution_time';
set global max_execution_time=30;
重新登陆客户端
show variables like 'max_execution_time';
查询验证
mysql> select count(*) from user where name like '%b%';
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
还原为0
set global max_execution_time=0;
mysql> select count(*) from user where name like '%b%';
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
重新登陆,再次执行
mysql> select count(*) from user where name like '%b%';
+----------+
| count(*) |
+----------+
| 32017 |
+----------+
1 row in set (0.06 sec)
3.快速新增100万数据
新建表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`addr` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
--创建rand_str函数
DELIMITER $$
DROP FUNCTION IF EXISTS rand_str;
-- 如果存在就删除
create FUNCTION rand_str(strlen SMALLINT )
-- 创建函数名 rand_str 参数为返回的长度
RETURNS VARCHAR(255)
-- 返回值
BEGIN
DECLARE randStr VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
-- 声明的字符串
DECLARE i SMALLINT DEFAULT 0;
-- 声明 i 循环变量
DECLARE resultStr VARCHAR(255) DEFAULT '';
-- 声明返回变量
WHILE i<strlen DO
SET resultStr=CONCAT(SUBSTR(randStr,FLOOR(RAND()*LENGTH(randStr))+1,1),resultStr);
SET i=i+1;
END WHILE;
RETURN resultStr;
END $$
DELIMITER ;
--创建add_user存储函数
DROP PROCEDURE IF EXISTS `add_user`;
DELIMITER $$
CREATE PROCEDURE `add_user`(IN n int)
BEGIN
DECLARE i int unsigned DEFAULT 0;
WHILE i < n DO
INSERT INTO `user`(name,age) VALUES (rand_str(15),rand_str(15));
SET i = i+1;
END WHILE;
END $$
DELIMITER ;
--调用存储过程
CALL add_user(100000);