MySQL之SQL性能（4）——查询截取分析（2）——批量插入数据脚本、show profile、全局查询日志

目录

1.批量插入数据脚本

2.show profile

（1）是什么

（2）分析步骤

3.全局查询日志

（1）配置启用

（2）编码启用

---

1.批量插入数据脚本

往表里插200万的数据

（1）建表

 CREATE TABLE dept(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, 	
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #部门编号
	dname VARCHAR(20) NOT NULL DEFAULT "",#部门名字
	loc VARCHAR(13) NOT NULL DEFAULT "" #部门位置
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE dept(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	emptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,#员工编号
	ename VARCHAR(20) NOT NULL DEFAULT "",#名字
	job VARCHAR(9) NOT NULL DEFAULT "",#工作
	mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,#上级编号
	hiredate DATE NOT NULL,#入职时间
	sal DECIMAL(7,2)NOT NULL,#薪水
	comm DECIMAL(7,2) NOT NULL,#红利
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 #部门编号
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

设置参数log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETERMINISTIC......

由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数

永久修改，如下，修改配置文件中/etc/my.cnf，添加如下

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

函数1：随机返回字符串

 DELIMITER $
 CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
 BEGIN
	DECLARE chars_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(chars_str,FLOOR(1+RAND()*52),1));
	SET i=i+1;
	END WHILE;
	RETURN return_str;
 END $

函数2：随机返回部门编号

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

创建存储过程

存储过程1：往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,hirdate,sal,comm,deptno)
 VALUES(START+i,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
 UNTIL i =max_num
 END REPEAT;
 COMMIT;
 END $

 

存储过程2：往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条记录

向emp表中插入200 0000条记录

此处根据根据个人电脑的性能的差别会执行很长时间

2.show profile

（1）是什么

show profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL语句的调优的测量

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

（2）分析步骤

1）查看当前的mysql版本是否支持

show variables like 'profiling';

2）开启profiling

3）运行SQL

下面的一句执行3次

 

.......

......

4）使用show profiles;查看执行过的SQL

可以看到各个sql语句的执行时间情况

5）诊断SQL

show profile cpu,block io for query 上一步前面问题SQL数字号码;

示例

诊断上述查询语句3：select * from class inner join book on class.card=book.card;

此处由于我之前查询过，所以此处直接在缓存上找到了结果返回

诊断上述查询语句8：select * from emp group by id%10 limit 1500000;

可以看到使用上述show profile命令可以查看到一条查询语句执行的完整的生命周期

其实不只可以查看cpu和block io，可以查看的参数类型如下：

5）对于show profile的结果分析

以下4个状态是危险的4个状态：

• convertion HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬
• Creating tmp table：创建临时表，拷贝临时表，用完再删除临时表
• Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！
• locked

只要你写的SQL中出现上述4个状态中的任何一个，这条SQL必须得优化

 

3.全局查询日志

全局查询日志只允许在测试环境用，永远不要在生产环境开启这个功能

（1）配置启用

在/etc/my.cnf配置文件中，设置如下：

//开启
general_log=1

//记录日志文件的路径
general_log_file=/path/logfile

//输出格式
log_output=FILE

（2）编码启用

//开启
set global general_log=1;


//输出格式
set global log_output='TABLE';

此后，你所编写的所有SQL语句，将会记录在mysql库里的general_log表，可以用以下命令查看：

select * from mysql.general_log;

以下是我执行了一些语句后的示例：