sql优化

一、定位慢查询                                                                                

      我们要对sql语句进行优化，第一步肯定是找到执行速度较慢的语句，那么怎么在一个项目里面定位这些执行速度较慢的sql语句呢？下面就介绍一种定位慢查询的方法。 

1.1、数据库准备

     首先创建一个数据库表：

1 2 3 4 5 6 7 8 9 10

CREATE  TABLE  emp (empno  MEDIUMINT UNSIGNED   NOT  NULL   DEFAULT  0 COMMENT  '编号' , 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=InnoDB  DEFAULT  CHARSET=utf8;

　　然后我们构建一个存储函数，这个存储函数会返回一个长度为参数n的随机字符串：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 ;

　   接下来我们再创建一个存储函数，该存储函数会返回一个随机int值：

1 2 3 4 5 6 7 8 9 10 11

delimiter $$   create  function  rand_num( ) returns  int (5) begin   declare  i  int  default  0;   set  i = floor(10+rand()*500); return  i;    end  $$   delimiter ;

      然后我们利用刚刚创建的两个存储函数创建一个存储过程，该存储过程包含一个参数，该参数表示插入数据表emp的数据条数：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

delimiter $$   create  procedure  insert_emp( in  max_num  int (10)) begin declare  i  int  default  0;   set  autocommit = 0;    repeat   set  i = i + 1;   insert  into  emp  values  (i ,rand_string(6), 'SALESMAN' ,0001,curdate(),2000,400,rand_num());    until i = max_num   end  repeat;     commit ;   end  $$   delimiter ;

      最后，我们调用改改创建的存储过程，对emp表插入1000w条数据：

1	call insert_emp(10000000);

1.2、查看慢查询

      我们可以用以下命令查看慢查询次数：

1	show status  like  'slow_queries' ;

      现在在mysql中敲入该命令，可以看到value为1，这个慢查询就是由刚刚批量插入1000w条数据产生。

      使用该命令只能查看慢查询次数，但是我们没有办法知道是哪些查询产生了慢查询，如果想要知道是哪些查询导致的慢查询，那么我们必须修改mysql的配置文件。打开mysql的配置文件（windows系统是my.ini,linux系统是my.cnf），在[mysqld]下面加上以下代码：

1 2	log-slow-queries=mysql_slow.log long_query_time=1

　　此时我们在mysql中运行以下命令，可以看到slow_query_log是ON状态，log_file也是我们指定的文件：

1 2 3 4 5 6 7 8

mysql> show variables  like  'slow_query%' ;  + ---------------------+------------------------------+ | Variable_name       | Value                        | + ---------------------+------------------------------+ | slow_query_log      |  ON                            | | slow_query_log_file | mysql_slow.log | + ---------------------+------------------------------+ 2  rows  in  set  (0.00 sec)

　　运行以下命令我们可以看到我们设定的慢查询时间也生效了，此时只要查询时间大于1s，查询语句都将存入日志文件。

1 2 3 4 5 6 7

mysql> show variables  like  'long_query_time' ;  + -----------------+----------+ | Variable_name   | Value    | + -----------------+----------+ | long_query_time | 1.000000 | + -----------------+----------+ 1 row  in  set  (0.00 sec)

　　现在我们运行一个查询时间超过1s的查询语句：　　

1 2 3 4 5 6 7

mysql>  select  *  from  emp  where  empno=413345; + --------+--------+----------+-----+------------+---------+--------+--------+ | empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno | + --------+--------+----------+-----+------------+---------+--------+--------+ | 413345 | vvOHUB | SALESMAN |   1 | 2014-10-26 | 2000.00 | 400.00 |     11 | + --------+--------+----------+-----+------------+---------+--------+--------+ 1 row  in  set  (6.55 sec)

　　然后查看mysql安装目录下的data目录，该目录会产生一个慢查询日志文件：mysql_slow.log，该文件内容如下：

1 2 3 4 5 6 7 8 9

/usr/ local /mysql/bin/mysqld, Version: 5.1.73-log (MySQL Community Server (GPL)). started  with : Tcp port: 3306  Unix socket: /tmp/mysql.sock Time                  Id Command    Argument #  Time : 141026 23:24:08 #  User @Host: root[root] @ localhost [] # Query_time: 6.547536  Lock_time: 0.002936 Rows_sent: 1  Rows_examined: 10000000 use  temp ; SET  timestamp =1414337048; select  *  from  emp  where  empno=413345;

　　在该日志文件中，我们可以知道慢查询产生的时间，最终产生了几行结果，测试了几行结果，以及运行语句是什么。在这里我们可以看到，这条语句产生一个结果，但是检测了1000w行记录，是一个全表扫描。

二、Explain执行计划                                                                            

     慢查询日志可以帮助我们把所有查询时间过长的sql语句记录下来，在优化这些语句之前，我们应该使用explain命令查看mysql的执行计划，寻找其中的可优化点。

      explain命令的使用十分简单，只需要"explain + sql语句"即可，如下命令就是对我们刚刚的慢查询语句使用explain之后的结果：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> explain  select  *  from  emp  where  empno=413345\G; *************************** 1. row ***************************             id: 1    select_type: SIMPLE          table : emp           type:  ALL possible_keys:  NULL            key :  NULL        key_len:  NULL            ref:  NULL           rows : 10000351          Extra: Using  where 1 row  in  set  (0.00 sec)   ERROR: No  query specified

　　可以看到，explain命令的结果一共有以下几列：id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra，这些列分别代表以下意思：

      1、id：SELECT识别符。这是SELECT的查询序列号；

      2、select_type：查询类型，主要有PRIMARY(子查询中最外层查询）、SUBQUERY（子查询内层第一个SELECT）、UNION（UNION语句中第二个SELECT开始后面所有SELECT）、SIMPLE（除了子查询或者union之外的其他查询）；

      3、table：所访问的数据库表明；

      4、type：对表的访问方式，包括以下类型all(全表扫描)，index（全索引扫描），rang（索引范围扫描），ref（join语句中被驱动表索引引用查询），eq_ref（通过主键或唯一索引访问，最多只会有一条结果），const（读常量，只需读一次），system（系统表。表中只有一条数据），null（速度最快）。

      5、possible_keys：查询可能使用到的索引；

      6、key：最后选用的索引；

      7、key_len：使用索引的最大长度；

      8、ref：列出某个表的某个字段过滤；

      9、rows：估算出的结果行数；

      10、extra：查询细节信息，可能是以下值：distinct、using filesort（order by操作）、using index（所查数据只需要在index中即可获取）、using temporary（使用临时表）、using where（如果包含where，且不是仅通过索引即可获取内容，就会包含此信息）。

      这样，通过"explain select * from emp where empno=413345\G"命令的输出，我们就可以清楚的看到，这条查询语句是一个全表扫描语句，查询时没有用到任何索引，所以它的查询时间肯定会很慢。

三、Profiling 的使用                                                                      

      mysql除了提供explain命令用于查看命令执行计划外，还提供了profiling工具用于查看语句查询过程中的资源消耗情况。首先我们要使用以下命令开启Profiling功能：

1	set  profiling = 1;

　　接下来我们执行一条查询命令：

1 2 3 4 5 6 7

mysql>  select  *  from  emp  where  empno=413345; + --------+--------+----------+-----+------------+---------+--------+--------+ | empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno | + --------+--------+----------+-----+------------+---------+--------+--------+ | 413345 | vvOHUB | SALESMAN |   1 | 2014-10-26 | 2000.00 | 400.00 |     11 | + --------+--------+----------+-----+------------+---------+--------+--------+ 1 row  in  set  (6.44 sec)

　　在开启了Query Profiler功能之后,MySQL就会自动记录所有执行的Query的profile信息了。 然后我们通过以下命令获取系统中保存的所有 Query 的 profile 概要信息：

1 2 3 4 5 6 7 8 9 10

mysql> show profiles; + ----------+------------+--------------------------------------+ | Query_ID | Duration   | Query                                | + ----------+------------+--------------------------------------+ |        1 | 0.00053000 | show tables                          | |        2 | 0.07412700 |  select  *  from  dept                   | |        3 | 0.06743300 |  select  *  from  salgrade               | |        4 | 6.44056000 |  select  *  from  emp  where  empno=413345 | + ----------+------------+--------------------------------------+ 4  rows  in  set  (0.00 sec)

　　然后我们可以通过以下命令查看具体的某一次查询的profile信息：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> show profile cpu, block io  for  query 4; + --------------------+----------+----------+------------+--------------+---------------+ | Status             | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | + --------------------+----------+----------+------------+--------------+---------------+ | starting           | 0.000107 | 0.000072 |   0.000025 |            0 |             0 | | Opening tables     | 0.000021 | 0.000018 |   0.000003 |            0 |             0 | | System lock        | 0.000006 | 0.000004 |   0.000001 |            0 |             0 | |  Table  lock         | 0.000009 | 0.000008 |   0.000001 |            0 |             0 | | init               | 0.000034 | 0.000033 |   0.000002 |            0 |             0 | | optimizing         | 0.000012 | 0.000011 |   0.000001 |            0 |             0 | |  statistics          | 0.000014 | 0.000012 |   0.000001 |            0 |             0 | | preparing          | 0.000013 | 0.000012 |   0.000002 |            0 |             0 | | executing          | 0.000005 | 0.000005 |   0.000016 |            0 |             0 | | Sending data       | 6.440260 | 7.818553 |   0.178155 |            0 |             0 | |  end                 | 0.000008 | 0.000006 |   0.000011 |            0 |             0 | | query  end           | 0.000002 | 0.000002 |   0.000003 |            0 |             0 | | freeing items      | 0.000030 | 0.000013 |   0.000017 |            0 |             0 | | logging slow query | 0.000001 | 0.000000 |   0.000001 |            0 |             0 | | logging slow query | 0.000035 | 0.000020 |   0.000015 |            0 |             0 | | cleaning up        | 0.000003 | 0.000003 |   0.000000 |            0 |             0 | + --------------------+----------+----------+------------+--------------+---------------+ 16  rows  in  set  (0.00 sec)

　　该profile显示了每一步操作的耗时以及cpu和Block IO的消耗，这样我们就可以更有针对性的优化查询语句了。可以看到，由于这是一次全表扫描，这里耗时最大是在sending data上。除了这种情况，以下几种情况也可能耗费大量时间：converting HEAP to MyISAM(查询结果太大时,把结果放在磁盘)、create tmp table(创建临时表,如group时储存中间结果)、Copying to tmp table on disk(把内存临时表复制到磁盘)、locked(被其他查询锁住) 、logging slow query(记录慢查询)。