详解MySQL 慢查询

查询mysql的操作信息

show status -- 显示全部mysql操作信息

show status like "com_insert%"; -- 获得mysql的插入次数;

show status like "com_delete%"; -- 获得mysql的删除次数;

show status like "com_select%"; -- 获得mysql的查询次数;

show status like "uptime"; -- 获得mysql服务器运行时间

show status like 'connections'; -- 获得mysql连接次数

查询mysql的操作信息show [session|global] status like .... 如果你不写 [session|global] 默认是session 会话,只取出当前窗口的执行,如果你想看所有(从mysql 启动到现在,则应该 global)

通过查询mysql的读写比例,可以做相应的配置优化;

慢查询

当Mysql性能下降时,通过开启慢查询来获得哪条SQL语句造成的响应过慢,进行分析处理。当然开启慢查询会带来CPU损耗与日志记录的IO开销,所以我们要间断性的打开慢查询日志来查看Mysql运行状态。

慢查询能记录下所有执行超过long_query_time时间的SQL语句, 用于找到执行慢的SQL, 方便我们对这些SQL进行优化.

show variables like "%slow%";-- 是否开启慢查询;
show status like "%slow%"; -- 查询慢查询SQL状况;
show variables like "long_query_time"; -- 慢查询时间

慢查询开启设置

mysql> show variables like 'long_query_time'; -- 默认情况下,mysql认为10秒才是一个慢查询
+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

mysql> set long_query_time=1; -- 修改慢查询时间,只能当前会话有效;
mysql> set global slow_query_log='ON';-- 启用慢查询 ,加上global,不然会报错的;

也可以在配置文件中更改
修改mysql配置文件my.ini[windows]/my.cnf[Linux]加入,注意必须在[mysqld]后面加入

slow_query_log =>
mysql> select sleep(3);

mysql> show status like '%slow%';
+---------------------+-------+
| Variable_name    | Value |
+---------------------+-------+
| Slow_launch_threads | 0   |
| Slow_queries    | 1   |
+---------------------+-------+
-- Slow_queries 一共有一条慢查询

Example2:

利用存储过程构建一个大的数据库来进行测试;

数据准备

CREATE TABLE dept(
deptno MEDIUMINT  UNSIGNED NOT NULL DEFAULT 0 comment '编号', 
dname VARCHAR(20) NOT NULL DEFAULT "" comment '名称', 
loc VARCHAR(13) NOT NULL DEFAULT "" comment '地点'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '部门表' ;

CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, 
ename VARCHAR(20) NOT NULL DEFAULT "" comment '名字', 
job VARCHAR(9) NOT NULL DEFAULT "" comment '工作',
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '上级编号',
hiredate DATE NOT NULL comment '入职时间',
sal DECIMAL(7,2) NOT NULL comment '薪水',
comm DECIMAL(7,2) NOT NULL comment '红利',
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '部门编号' 
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '雇员表';

CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '等级',
losal DECIMAL(17,2) NOT NULL comment '最低工资',
hisal DECIMAL(17,2) NOT NULL comment '最高工资'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '工资级别表';

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $
create function rand_num() 
returns tinyint(6) READS SQL DATA 
begin 
 declare return_num tinyint(6) default 0;
 set return_num = floor(1+rand()*30);
 return return_num;
end $

delimiter $
create function rand_string(n INT) 
returns varchar(255) READS SQL DATA 
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 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 values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 commit;
end $

call insert_emp(1,4000000);
SELECT * FROM `emp` where ename like '%mQspyv%'; -- 1.163s

# Time: 150530 15:30:58 -- 该查询发生在2015-5-30 15:30:58
# User@Host: root[root] @ localhost [127.0.0.1] -- 是谁,在什么主机上发生的查询
# Query_time: 1.134065 Lock_time: 0.000000 Rows_sent: 8 Rows_examined: 4000000 
-- Query_time: 查询总共用了多少时间,Lock_time: 在查询时锁定表的时间,Rows_sent: 返回多少rows数据,Rows_examined: 表扫描了400W行数据才得到的结果;
SET timestamp=1432971058; -- 发生慢查询时的时间戳;
SELECT * FROM `emp` where ename like '%mQspyv%';

详解MySQL 慢查询

扫一扫手机访问