如何从一个大项目中，迅速定位执行速度慢的SQL语句 —— 定位慢查询

有些sql语句写的效率高，有些sql语句写的效率不高，老鸟和菜鸟写的sql语句一般会有细微差别。

优化sql语句，定位慢查询很重要！

1、MySQL运行状态

首先了解一些mysql当前的运行状态：show status; (有300多个状态指令)

固定语法：show [session|global] status like ... 

session 表示当前(本次)连接的统计结果

global 表示MySQL从上次启动至今的统计结果

如果不写 [session|global] 默认是session会话

当前mysql运行的时间（运行时间久了会有碎片等产生）：show status like 'uptime';

一共执行了多少次select/update/delete：

show status like 'com_select';

show status like 'com_update';

show status like 'com_delete';

（注意：上述指令，执行错误的sql语句也会包含在内）

通过这几个参数，可以容易了解到当前数据的应用是以插入更新为主还是以查询操作为主，以及各类SQL大致的执行比例是多少

当前连接次数：show status like 'connections'; (不用加global)

connections并不是代表目前有多少个链接，它只代表试图连接mysql的次数。本意是：成功连接成功退出的次数！换句话说，目前的链接数99.99% 小于conncetions。

为什么不是100%？因为有异常链接（Aborted_connects）！

connections只记录正常连接正常退出的链接次数。所以异常链接没有记录。

实际上这个是先驱做法，你链接一次，这个就加一，关闭的时候就不加了，如果在关闭的时候+1 就不是官方的说法了。

显示慢查询数量：show status like 'slow_queries';

默认情况下，mysql认为10秒才是一个慢查询

显示慢查询时间：show variables like 'long_query_time';

修改mysql慢查询：set long_query_time=1;

2、模拟慢查询操作

需要构建大表，大表中要有记录，否则测试效果和真实效果的相差大

首先创建三张表(下面的代码可以直接粘贴到mysql中)

#部门表

CREATE TABLE dept(

deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/

dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名称*/

loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

#创建表EMP雇员

CREATE TABLE emp

(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=MyISAM DEFAULT CHARSET=utf8 ;

#工资级别表

CREATE TABLE salgrade(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2)  NOT NULL,

hisal DECIMAL(17,2)  NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

插入数据：

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

#删除自定的函数

drop  function rand_string$$

#创建 rand_string(n INT) 函数，该函数接收一个整数，生成一个随机字符串

create function rand_string(n INT) 

returns varchar(255) #该函数会返回一个字符串

begin 

#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

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

测试上面的函数：

select rand_string(6) from dual$$

dual 是亚元表，专门用来测试，并不真实存在

#自定一个函数，生成一个随机的数字

create function rand_num( )

returns int(5)

begin 

 declare i int default 0;

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

return i;

  end $$

#创建一个存储过程

#start表示雇员的编号从哪里开始，max_num 表示一共增加多少雇员

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

#调用刚刚写好的函数, 1800000条记录,从100001号开始

call insert_emp(100001,4000000);

这时我们如果出现一条语句执行时间超过1秒钟，就会统计到. 

例如：select * from emp where empno=518682