10.慢查询、函数、存储过程

慢查询日志

1.MySQL的慢查询日志MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,
具体指运行时间超过long_query_time值得SQL,则会被记录到慢查询日志中：

- 具体指运行时间超过long_query_time值得SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。
- 由他来查看那些SQL超出了我们的最大忍耐时间值,比如一条SQL执行超过5秒钟,我们就算是慢SQL,希望能收集超过5秒的SQL,结合之前explain进行全面分析

2.默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数
- 如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。
- 慢查询日志支持将日志记录写入文件

3.查看慢查询是否开启命令
show variables like 'slow_query%';

4.开启慢查询的方法
方法1：
set global slow_query_log='ON'; 
set global slow_query_log_file='/usr/local/mysql/data/slow.log';

方法2：
修改配置文件my.cnf，在[mysqld]下的下方加入下面的内容后，重启mysql服务
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
#long_query_time参数比较重要

5.参数说明
mysqldumpslow --help可显示其参数的使用

经常使用的参数：
-s   order的顺序
al   平均锁定时间
ar   平均返回记录时间
at   平均查询时间（默认）
c    计数
l    锁定时间
r    返回记录
t    查询时间
-t   是top n的意思，即为返回前面多少条的数据
-g   后边可以写一个正则匹配模式，大小写不敏感的

得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

注：这个需要有mysqldumpslow这个命令

函数和存储过程

创建函数语法：
CREATE
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
[characteristic ...] routine_body
create function 存储函数的关键字
sp_name存储函数的名称
func_parameter存储过程的参数列表type-----函数返回的数据类型
格式：
[ IN | OUT | INOUT ] param_name type
介绍：
in----输入，out----输出，inout----可以输入也可以输出
param_name 参数名称
type 参数的类型（MySQL的任意类型）
routine_body sql的代码
characteristic存储过程的特性

部门表

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

员工表

create table emp(
            id int primary key auto_increment,
            empno mediumint not null,   
            ename varchar(20) not null, 
            job varchar(9) not null, 
            mgr mediumint not null,  
            hiredate DATE not null,  
            sal decimal(7,2) not null, 
            comm decimal(7,2) not  null, 
            deptno mediumint not null   
)engine=innodb default charset=gbk;

创建函数

创建函数，假如报错：this function has none of DETERMINISTIC… 查看参数

set global log_bin_trust_function_creators=1;

随机产生字符串

DELIMITER $
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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 $

创建存储过程

创建存储过程语法：
CREATE
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

create procedure：存储的关键字
sp_name：存储过程的名字 
proc_parameter：存储过程的参数列表characteristic存储过程的特性
格式：
[ IN | OUT | INOUT ] param_name type
介绍：
in----输入，out----输出，inout----可以输入也可以输出
param_name：参数名称
type：参数的类型（MySQL的任意类型）
routine_body sql的代码

插入数据

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 $

插入数据

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 ; 
call insert_dept(100,10);

delimiter ; 
call insert_emp(100001,500000);

相关命令

调用存储过程：call 存储过程名字（[存储过程的参数[,……]])
查看存储过程和状态：show {procedure|function}status[like’存储过程或存储函数的名称’]
删除存储过程：DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
调用函数：select 存储函数名字（[存储过程的参数[,……]])
删除函数：DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name