MYSQL学习笔记——sql语句优化工具

MYSQL学习笔记——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 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 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 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 |

+---------------------+------------------------------+

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

          keyNULL

      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 |

+----------+------------+--------------------------------------+

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(记录慢查询)。

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页