MySQL模拟插入百万级数据和SQL分析

阳光穿透心脏的1/2处 2022-03-29 14:07 771阅读 0赞

## 一、引言 ##

测试环境中的数据，有时候需要我们自己来构造，为了模拟大数据量，我们总不能一条一条SQL的往数据库中插入，我们可以使用函数和存储过程来实现，这就需要对MySQL的函数和存储过程所有掌握，下面就来介绍一下如何模拟百万数据或者千万数据的插入。（数据模拟完成后，还可以用这些数据来测试MySQL的访问性能哦）

## 二、MySQL模拟插入百万级数据： ##

###### 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 ""
    );

create table emp(
    	id int unsigned primary key auto_increment,
    	empno 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,
    	comn decimal(7,2) not null,
    	deptno mediumint unsigned not null default 0
    );

###### 2、设置参数 log\_bin\_trust\_function\_creators； ######

前提说明：如果我们在创建函数时，报错：this function has none of deterministic…，这是由于开启过慢查询日志，因为开启了bin-log（二进制日志），我们就必须为我们的function指定一个参数。

简单介绍一下，当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限，如果变量设置为1，MySQL不会对创建存储函数实施这些限制。

那么为什么MySQL有这样的限制呢？ 因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，参数log\_bin\_trust\_function\_creators就会生效，限制存储函数的创建、修改、调用。

（1）、查看该参数：

show varibales like ‘log_bin_trust_function_creators’;

（2）、设置该参数：

set global log_bin_trust_function_creators = 1;

（3）、这样添加了参数后，如果mysqld重启，上述参数又会消失，永久的办法是：

a.	在Windows系统下的配置文件中 my.ini[mysqld] 加上 log_bin_trust_function_creators = 1;
    b.	在Linux下 /etc/my.cnf下my.cnf[mysqld] 加上 log_bin_trust_function_creators = 1；

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

###### （1）、随机产生字符串 ######

DELIMITER $$ 
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN
    	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    	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）、随机产生部门编号 ######

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

###### 4、创建存储过程 ######

###### （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 把autocommit设置成0，把默认提交关闭
    	SET autocommit = 0;
    	REPEAT
    	SET i = i + 1;
    	INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comn,deptno) VALUES ((START+I),rand_string(6),'SALEMAN',0001,now(),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 $$

###### 5、调用方法存储过程 ######

调用方法和存储过程:

delimiter ;				//结束$$的定义，变为";"
    call insert_dept(10000,100000);

## 三、show profile ##

show profile 是MySQL提供用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。默认情况下，该参数处于关闭状态，并保持最近15次的运行结果。

###### 1、show profile分析步骤： ######

（1）、看看当前的mysql版本是否支持（mysql5.5之后）

show varibles like ‘profiling’;

（2）、开启功能，默认是关闭，使用前需要开启。或者在配置文件中设置该值

set profiling = on; //开启

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70]  
（3）、运行SQL；  
（4）、使用该命令可以查看第三步运行的所有SQL结果：

show profiles;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 1]  
（5）、从上图中，可以看出14号SQL执行时间用了37.58秒，这属于严重的SQL问题，想想并发情况下，这一条SQL所要话费的时间就是37秒，用户该怎么办（这里只是模仿SQL的慢查询）。这就需要对该SQL进行诊断，我们使用如下命令：

show profile cpu,block io for query 问题SQL数字号码；

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 2]

这样我们就可以知道该条SQL执行的每一个细节，就可以对SQL进行优化了。

###### 2、show profile的type参数介绍： ######

show profile的type参数

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>意义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>all</td> 
   <td>显示所有的开销信息</td> 
  </tr> 
  <tr> 
   <td>cpu</td> 
   <td>显示CPU相关开销信息</td> 
  </tr> 
  <tr> 
   <td>Block io</td> 
   <td>显示块IO相关开销</td> 
  </tr> 
  <tr> 
   <td>ipc</td> 
   <td>显示发送和接收相关开销信息</td> 
  </tr> 
  <tr> 
   <td>memory</td> 
   <td>显示内存相关开销信息</td> 
  </tr> 
  <tr> 
   <td>Page faults</td> 
   <td>显示页面错误相关开销信息</td> 
  </tr> 
  <tr> 
   <td>swaps</td> 
   <td>显示交换次数相关开销的信息</td> 
  </tr> 
  <tr> 
   <td>source</td> 
   <td>显示和source_funciton、source_file、source_line相关的开销信息</td> 
  </tr> 
  <tr> 
   <td>Context switches</td> 
   <td>上下文切换相关开销</td> 
  </tr> 
 </tbody> 
</table>

如果在 Status 字段中出现以下描述，则需要注意：

（1）converting HEAP to MYISAM：查询结果太大，内存都不够用了，往磁盘上搬了；

（2）Creating tmp table ：创建临时表（拷贝数据到临时表，用完再删除）；

（3）Copying to tmp table on disk：把内存中临时表复制到磁盘（很危险！！！）

（4）Locked

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 3]

## 四、全局查询日志 ##

###### 1、注意作用： ######

全局查询日志用于保存所有的SQL执行记录，该功能主要用于测试环境，在生产环境中永远不要开启该功能。

（1）、在配置文件中启用，在mysql的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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 4]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 5]  
注意：永远不要再生产环境开启这个功能

## 五、总结： ##

1、通过命令方式开启的功能，重启mysql后都会失效；

2、全局查询日志只用在测试环境，切记生产环境中永远不要开启该功能；

3、show profile能更细粒度的分析SQL的执行过程，要区别于explain的使用。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70]: /images/20220329/921a577833934215afc395f05dfa7fd6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 1]: /images/20220329/5f0bb5913ad948a58025b205ca0cb060.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 2]: /images/20220329/8b7558cccd744d53bb7bcbd3b6237eb8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 3]: /images/20220329/56eb7fa81909485b8f093476769d1691.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 4]: /images/20220329/9759dd945b9e4a599327eab12dc56587.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4ZDE0MzU1MTM3NzU_size_16_color_FFFFFF_t_70 5]: /images/20220329/a6bc01e22aa94736a305fb5840083981.png