SQL优化分析 过程　

　　1.观察，至少跑一天。看看生产的慢SQL情况

　　2.开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来

　　3.explain + 慢SQL分析

　　4.show profile

　　5.运维经理 or DBA ，进行数据库调优

总结：

　　1.慢查询的开启并捕获

　　2.explain + 慢查询分析

　　3.show profile 查询SQL在MySQL服务器里面的执行细节和生命周期情况

　　4.SQL数据库服务器参数调优

 

慢查询日志  （当某条SQL的执行时间大于阙值，会被记录到日志文件中 ）

　　1.查看与开启慢查询日志

　　　　SHOW  VARIABLES  LIKE  '%show_query_log%';

　　　　set  global  slow_query_log = 1;

　　2.查看与开启阙值时间(需重新连接)　　

　　　　SHOW  VARIABLES  LIKE  '%long_query_time%';

　　　　set  global  long_query_time = 1;

　　3.查询当前系统有多少条慢查询记录

　　　　show global status like '%slow_queries%';

 

日志分析工具  mysqldumpslow

　　查看 mysqldumpslow的帮助信息

　　　　s:按何种方式排序

　　　　c:访问次数

　　　　t：查询时间

　　　　......

mysqldumpslow -s c -t 10 日志文件    得到指定的慢查询日志中的访问次数最多的10个 慢 SQL

 

批量数据脚本

　　

#1.建表dept

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=gbk;

#2.建表emp

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,

comm decimal(7,2) not null,

deptno mediumint unsigned not null default 0

) engine=innodb default charset=gbk;

#3.设置参数 'log_bin_trust_function_creators';

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators = 1;

#4.创建函数（随机产生字符串）

delimiter $$

create function rand_string(n int) returns varchar(255)

begin

declare chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM';

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

create function rand_num()

returns int (5)

begin

declare i int default 0;

set i = floor(100 + rand()*10);

return i;

end $$

#5.创建存储过程

#(往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,hiredate,sal,comm,deptno) values ((start + i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());

until i = max_num

end repeat;

commit;

end $$

#(往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 $$

#6.调用存储过程(先插部门表，再插员工表)

delimiter ;

call insert_dept(100,10);

call insert_emp(100001,500000);

 

函数和存储过程的区别：

函数有返回值，存储过程没有返回值