Mysql查询截取分析（2）

这是我参与8月更文挑战的第6天，活动详情查看：8月更文挑战

3、批量插入数据脚本

建表

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `dname` varchar(40) NOT NULL DEFAULT '',
  `loc` varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '编号',
  `enmae` varchar(20) NOT NULL DEFAULT '',
  `job` varchar(20) NOT NULL DEFAULT '' COMMENT '工作',
  `mar` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '领导编号',
  `hiredate` date NOT NULL COMMENT '入职时间',
  `sal` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '红利',
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '部门编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

先开启 bin_log，重启MySQL后失效

# 查看bin_log
SHOW VARIABLES LIKE "%log_bin_trust_function_creators%"
# 开启bin_log
SET GLOBAL log_bin_trust_function_creators = 1;

创建函数，保证每条数据不同

随机产生字符串的函数

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE char_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	SET return_str = CONCAT(return_str,SUBSTRING(char_str,FLOOR(1+RAND()*52),1));
	SET i = i+1;
	END WHILE;
	RETURN return_str;
END $$

随机产生部门编号

DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(100+RAND()*10);
	RETURN i;
END $$

创建存储过程

往emp表插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;#把自动提交关闭
	REPEAT
	SET i = i + 1;
	INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$

往dept表插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO dept (deptno,dname,loc) VALUES((START+i),rand_string(10), rand_string(8));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$

调用存储过程

# 向dept表插入10条数据
CALL insert_dept(100,10);
# 向emp表插入50w数据
CALL insert_emp(100001,500000);

4、使用 Show Profile 进行 SQL 分析

profiling操作步骤

# 查看profiling状态
SHOW VARIABLES LIKE "%profiling%"

# 开启profiling
SET profiling = on;

# 查看执行SQL的记录
SHOW PROFILES;

# 诊断SQL，还有更多参数可以添加或删除，这里使用的是常用的参数。
SHOW PROFILE cpu,block io for query 上一步查询记录中的id

出现以下字段需要注意