mysql进阶（六）

1.插入1000w数据

涉及到的表：

create table dept(
	id int unsigned primary key  auto_increment,
	deptno mediumint not null default 0,
	deptname varchar(20) not null default "",
	loc varchar(13) not null default""
	) engine=innodb default charset=gbk;


 create table emp(
	id int unsigned primary key  auto_increment,
	empno mediumint 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,
	deptno mediumint unsigned not null default 0
	) engine=innodb default charset=gbk;

        

          
        
        
        

          

          AI 代码解读
        
      
      

 创建函数：当创建函数时报错：this function has none of deterministic ……

由于开启过慢查询日志，因为 bin-log,必须为function指定一个参数。

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

生成随机数函数：

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 $$
delimiter ;

        

          
        
        
        

          

          AI 代码解读
        
      
      

 生成随机数字函数：注意 rand)num( )括号中空格

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

        

          
        
        
        

          

          AI 代码解读
        
      
      

创建存储过程：

-- 创建存储过程
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,comm,deptno) values ((start+i),rand_string(6),'SALEMAN',001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

        

          
        
        
        

          

          AI 代码解读
        
      
      

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 $$
delimiter ;

        

          
        
        
        

          

          AI 代码解读
        
      
      

2.show profiles

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

参数默认关闭，并保存最近15次的运行结果

①查看是否支持profile

show variables like '%profiling%';

        

          
        
        
        

          

          AI 代码解读
        
      
      

②开启功能，默认是关闭的，使用前需要开启

select * from emp group by id%10 limit 150000;

        

          
        
        
        

          

          AI 代码解读
        
      
      

诊断SQL的命令演示 ：show profile ,cpu,block io for query query_id;

3.全局查询日志

配置启用：

编码配置：