MySQL高级知识点(七)
文章目录
截取查询分析
1、慢日志查询
概念
- MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中
- 具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为 10,意思是运行10秒以上的语句
- 由他来查看哪些SQL超出了我们的最大忍耐时间值,比如一条sql执行超过5秒钟,我们就算慢SQL,希望能 收集超过5秒的sql,结合之前explain进行全面分析
使用
默认情况下,MySQL 数据库没有开启慢查询日志,需要我们手动来设置这个参数
如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件
SQL 语句 | 描述 | 备注 |
---|---|---|
SHOW VARIABLES LIKE ‘%slow_query_log%’ | 查看慢查询日志是否开启 | 默认情况下 slow_query_log 的值为 OFF |
set global slow_query_log=1 | 开启慢查询日志 | |
SHOW VARIABLES LIKE ‘long_query_time%’ | 查看慢查询设定阈值 | 单位:秒 |
set long_query_time=1 | 设定慢查询阈值 | 单位:秒 |
运行查询时间长的 sql,可以打开慢查询日志查看
2、批量数据脚本
建表语句
--dept 部门表
CREATE TABLE `dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, ceo INT NULL , PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- emp 员工表
CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `empno` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
设置参数
在执行创建函数之前,首先请保证 log_bin_trust_function_creators 参数为 1,即 on 开启状态。 否则会报错
--查询
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
--设置
SET GLOBAL log_bin_trust_function_creators=1;
编写随机函数
随机产生字符串
--DELIMITER 是用于改变结束的标志的,一般以分号结尾,但这里改为了以 $$ 结尾
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 $$
如果要删除函数,则执行:
DROP FUNCTION rand_string;
随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$
如果要删除函数,则执行:
drop function rand_num;
创建存储过程
创建往 emp 表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把 autocommit 设置成 0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
--删除
-- DELIMITER ;
-- drop PROCEDURE insert_emp;
创建往 dept 表中插入数据的存储过程
--执行存储过程,往 dept 表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
--删除
-- DELIMITER ;
-- drop PROCEDURE insert_dept;
调用存储过程
添加数据到部门表
--执行存储过程,往 dept 表添加 1 万条数据
DELIMITER ;
CALL insert_dept(10000);
添加数据到员工表
--执行存储过程,往 emp 表添加 50 万条数据
DELIMITER ;
CALL insert_emp(100000,500000);
批量删除某个表上的所有索引
删除索引的存储过程
DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE
table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index ",_index," on ",tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END$$
执行存储过程
CALL proc_drop_index("dbname","tablename");