查询截取分析
查询截取分析
- 慢查询的开启并捕获
- explain和慢sql分析
- show profile 查询sql在mysql服务器里面的执行细节和生命周期情况。
- sql数据库服务器的参数调优
慢查询日志
介绍
Mysql提供的一种日志记录,用来记录mysql中响应时间超过阈值的语句,具体指运行时间超过Long_query_time值的sql语句,会被记录到慢查询日志中。
Long_query_time默认为10s。借此观察哪些语句超出了忍耐值。
使用
默认情况下,mysql数据库没有开启慢查询日志,需要手动开启并设置。如果不需要,一般不建议启动该参数,多少会对性能有影响。
查看慢查询日志
show variables like '%slow_query_log%';
开启慢查询日志
set global slow_query_log =1;
只对此次连接有效,重启后失效。
查看long_query_time
show variables like 'long_query_time%';
设置long_query_time
set global long_query_time = 3;
设置后没有效果,需要重新启动或新开一个对话才有效果。
重新连接后:
long_query_time通过判断运行时间大于此值的情况,不是大于等于!
查看慢日志
cat 慢查询日志地址
查询当前系统中有多少条慢查询记录
show global status like '%slow_queries%';
Mysqldumpslow
Mysql的日志分析工具
查看使用帮助
Mysqldumpslow --help
s:按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
#得到返回记录集最多的10个sql
mysqldumpslow -s -r -t 10 /var/run/mysqld/mysqld-slow.log
#得到访问次数最多的10个sql
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
#得到按照时间排序的前10条里含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/run/mysqld/mysqld-slow.log
最好结合more使用
批量插入数据脚本
建表
CREATE TABLE games(
id int PRIMARY KEY auto_increment,
name VARCHAR(20),
author_id INT
);
CREATE TABLE authorss(
id int PRIMARY KEY auto_increment,
name VARCHAR(20),
age int,
sex varchar(10)
);
创建函数
#随机产生名
delimiter $
CREATE FUNCTION rand_name(charstr VARCHAR(20),n int) RETURNs VARCHAR(100)
BEGIN
DECLARE restr VARCHAR(100) DEFAULT '';
DECLARE i INT DEFAULT 0 ;
WHILE i < n DO
SET restr = CONCAT(charstr,i+1);
SET i = i+1;
END WHILE;
RETURN restr;
END $
创建存储过程
#插入游戏名
delimiter $
create PROCEDURE insert_game(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 games(name,author_id) VALUES(rand_name('rim',max_num),(START+i));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $
#插入作者
create PROCEDURE insert_atr(IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i+1;
INSERT INTO authorss(name,age,sex) VALUES(rand_name('xh',max_num),(18+i),'女');
UNTIL i = max_num
END REPEAT;
COMMIT;
END $
call insert_game(5,10)$
CALL insert_atr(10)$
仅为案例,结果有点问题。
创建函数时如果报错
如果报错:This function has none of DETERMINISTIC…
由于开启过慢查询日志,开启了bin_log,必须为函数指定一个参数
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1;
不能跨重启,永久方法:
Show profile
介绍
可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql调优的测量.
默认情况下,参数处于关闭状态,保存最近15次的运行结果。
分析步骤
查看当前mysql版本是否支持
show variables like 'profiling';
开启
set profiling = on ;
运行sql语句
查看结果
show profiles;
诊断sql
show profile cpu,block io for query 语句编号;
注意事项
- Converting HEAP to MyISAM 查询结果太大,内存不够用,使用磁盘。
- Creating tmp table 拷贝数据到临时表/用完再删除
- Copying to tmp table on disk 把内存中临时表复制到磁盘 危险
- Locked
全局查询日志
不要在生产环境启用!
配置启用
编码启用
set global general_log =1;
set global log_output = 'TABLE';
此后,你所编写的sql语句,将会记录到mysql库里的general_log表
#查看general_log表
select * from mysql.general_log;