mysql进阶（六）

╰半夏微凉° 2022-05-18 05:24 262阅读 0赞

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

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

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

![70][]

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

生成随机数函数：

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 ;

生成随机数字函数：注意 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 ;

创建存储过程：

-- 创建存储过程
    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 ;

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 ;

### 2.show profiles ###

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

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

**①查看是否支持profile**

show variables like '%profiling%';

![70 1][]

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

![70 2][]

![70 3][]

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

![70 4][]

![70 5][]

![70 6][]

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

![70 7][]

![70 8][]

### 3.全局查询日志 ###

![70 9][]

配置启用：

![70 10][]

编码配置：

![70 11][]

[70]: /images/20220518/5902c2dede5545a1b7742806f3a81e5d.png
[70 1]: /images/20220518/e774520ee46445afb6979d389a237668.png
[70 2]: /images/20220518/419f364f7cd7412ca8439aa81f68d268.png
[70 3]: /images/20220518/dd28c75b2bb04b4d81f4edf2493d2982.png
[70 4]: /images/20220518/e592babeb1934b4aab02e05c3ed9abf4.png
[70 5]: /images/20220518/2c08ffd06b934fd493e44d9feae9aa04.png
[70 6]: /images/20220518/9cc9380b2e704ac0beb362a138963dde.png
[70 7]: /images/20220518/b3e473409f3e4b3a9aff5bdc73bd301d.png
[70 8]: /images/20220518/df247be853f44b3f9c5227bdb14fb6d5.png
[70 9]: /images/20220518/d247d33acf1446b18cd32144be2c37b3.png
[70 10]: /images/20220518/44b9e18014b74822b7afa5fe2b0836e2.png
[70 11]: /images/20220518/acb27844a269466781f5888557146e1a.png