分析海量数据

悠悠 2023-10-06 13:08 102阅读 0赞

#### 文章目录 ####

*  模拟海量数据
 *  分析海量数据

## 模拟海量数据 ##

通过 存储过程（无return）/存储函数（有return）来模拟。

create database testdata ;
    use testdata
    
    create table dept
    (
    dno int(5) primary key default 0,
    dname varchar(20) not null default '',
    loc varchar(30) default ''
    )engine=innodb default charset=utf8;
    
    
    create table emp
    (
    eid int(5) primary key,
    ename varchar(20) not null default '',
    job varchar(20) not null default '',
    deptno int(5) not null default 0
    )engine=innodb default charset=utf8;

通过存储函数 插入海量数据：  
创建存储函数：  
randstring(6) ->aXiayx 用于模拟员工名称

delimiter $ 
    	create function randstring(n int)   returns varchar(255) 
    	begin
    		declare  all_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(all_str,   FLOOR(1+rand()*52)   ,1)       );
    			set i=i+1 ;
    		end while ;
    		return return_str;
    		
    	end $

注意：  
  
1、如果报错：You have an error in your SQL syntax，说明SQL语句语法有错，需要修改SQL语句；  
  
  
2、 如果报错This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log\_bin\_trust\_function\_creators variable)  
是因为 存储过程/存储函数在创建时 与之前的 开启慢查询日志冲突了

解决：  
①临时解决( 开启log\_bin\_trust\_function\_creators )

show variables like '%log_bin_trust_function_creators%';
    set global log_bin_trust_function_creators = 1;

②永久解决：

vi /etc/my.cnf 
    	[mysqld]
    	log_bin_trust_function_creators = 1

--------------------

**产生随机整数:**

create function ran_num() returns int(5)
    	begin
    		declare i int default 0;
    		set i =floor( rand()*100 ) ;
    		return i ;
    
    	end $

--------------------

**通过存储过程插入海量数据：emp表**

create procedure insert_emp( in eid_start int(10),in data_times int(10))
    	begin 
    		declare i int default 0;
    		set autocommit = 0 ;
    		
    		repeat
    			
    			insert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ;
    			set i=i+1 ;
    			until i=data_times
    		end repeat ;
    		commit ;
    	end $

**通过存储过程插入海量数据：dept表**

create procedure insert_dept(in dno_start int(10) ,in data_times int(10))
    		begin
    			declare i int default 0;
    			set autocommit = 0 ;
    			repeat
    			
    				insert into dept values(dno_start+i ,randstring(6),randstring(8)) ;
    				set i=i+1 ;
    				until i=data_times
    			end repeat ;
    		commit ;
    			
    
    		end$

--------------------

**插入数据：**

delimiter ; 
    		call insert_emp(1000,800000) ;
    		call insert_dept(10,30) ;

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5_size_16_color_FFFFFF_t_70 1]

## 分析海量数据 ##

**（1）profiles**

show profiles ; --默认关闭
    	show variables like '%profiling%';
    	set profiling = on ;

show profiles ：会记录所有profiling打开之后的 全部SQL查询语句所花费的时间。缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu io ）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5_size_16_color_FFFFFF_t_70 2]  
**(2)–精确分析:sql诊断**

show profile all for query 上一步查询的的Query_Id
    
    show profile cpu,block io for query 上一步查询的的Query_Id

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

**(3)全局查询日志 ：记录开启之后的 全部SQL语句。**

这次全局的记录操作 仅仅在调优、开发过程中打开即可，在最终的部署实施时 一定关闭

show variables like '%general_log%';--查看状态

--执行的所有SQL记录在表中
    set global general_log = 1 ;--开启全局日志
    set global log_output='table' ; --设置 将全部的SQL 记录在表中

--执行的所有SQL记录在文件中
    		set global log_output='file' ;
    		set global general_log = on ;
    		set global general_log_file='/tmp/general.log' ;

**开启后，会记录所有SQL ： 会被记录 mysql.general\_log表中。**

select * from  mysql.general_log ;

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200716144340822.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200716144356520.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200716144632268.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200716144814399.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200716145123990.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxMDQwNTU5,size_16,color_FFFFFF,t_70