MySQL优化3之查询截取分析

1、sql优化

1.1、sql调优的过程

步骤	描述
Step1	查看生产环境中满sql的情况
Step2	开启慢查询日志，设置阈值。比如超过5秒钟的就是慢SQL，并将它抓取出来
Step3	explain + 慢sql
Step4	show profile，展示SQL语句的资源使用情况，查询SQL在MySQL服务器里面的执行细节和生命周期情况
Step5	进行SQL数据库服务器的参数调优

1.2、sql优化1：小表驱动大表（in和exists）

select * from A where id in (select id from B); # A表与B表的ID字段应建立索引
等价于:
for select id from B {
	for select * from A where A.id = B.id
}

当B表的数据量必须小于A表的数据量时，用in优于exists。

当A表的数据量小于B表的数据量时，用exists优于in。

select * from A where exists (select 1 from B where B.id = A.id)  # A表与B表的ID字段应建立索引，select 1  在执行时会被忽略
等价于：
for select * from A {
	for select * from B where B.id = A.id
}

SELECT ...FROM table WHERE EXISTS (subquery)

理解：将主查询的数据，放到子查询中做条件验证，根据验证结果（true/false）来决定主查询的数据结果是否得以保留。

优化原则：小表驱动大表，即小的数据集驱动大的数据集。

1.3、sql优化2：order by子句优化

order by子句中尽量使用index方式排序，避免出现filesort方式排序。

create index idx_A_ageBirth on tblA(age, birth)

mysql> EXPLAIN SELECT * FROM tblA where age > 20 order by age;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth,age;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA order by birth;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA WHERE birth > '2016-01-28 00:00:00' order by birth;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA WHERE birth > '2016-01-28 00:00:00' order by age;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA order by age, birth desc;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

MySQL支持filesort和index两种方式的排序。index效率高，它指MySQL扫描索引本身完成排序；filesort方式效率较低。

order by满足以下两情况，会使用Index方式排序：

• order by语句使用索引最左前列（最佳左前缀原则）。
• 使用where子句与order by子句条件列组合满足索引最左前列。

filesort有两种算法

• 双路排序
• 单路排序

1.3.1、双路排序

两次扫描磁盘，最终得到数据。读取行指针和OrderBy列，对他们进行排序，然后扫描已经排序好的列表，从中读出对应数据。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行了两次扫描，I\O耗时很高，mysql4.1之后改进算法——单路排序。

1.3.2、单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序好的列表进行输出。它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

单路总体而言好过双路，但单路也有缺陷。

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据进行排序，排完再取，从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

• 增大sort_buffer_size参数的设置
• 增大max_length_for_sort_data参数的设置

为什么设置sort_buffer_size、max_length_for_sort_data参数能优化排序？

可以提高Order By的速度。Order by时select * 是一个大忌，只查询需要的字段，这点非常重要。因为当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用单路排序，否则用多路排序。
两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer__size。
提高sort_buffer_size，不管对于哪种算法都会提高效率。
提高max_length_for_sort_data，会增加单路排序的使用概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

MySQL能为排序与查询使用相同的索引
假设有一复合索引 idx (a, b, c)

• order by子句能使用索引的情况

  • order by能使用索引最左前缀

    • order by a
    • order by a, b
    • order by a, b, c
    • order by a DESC, b DESC, c DESC // 同升或者同降

  • WHERE使用索引的最左前缀定义为常量，则order by能使用索引

    • where a = const order by b,c
    • where a = const AND b = const order by c
    • where a = const order by b, c
    • where a = const AND b > const order by b, c

• order by子句不能使用索引进行排序

  • order by a ASC, b DESC, c DESC //排序不—致
  • where g = const order by b, c //产丢失a索引
  • where a = const order by c //产丢失b索引
  • where a = const order by a, d //d不是素引的一部分
  • where a in (…) order by b, c //对于排序来说,多个相等条件也是范围查询

1.4、sql优化3：group by子句优化

group by实质是先排序后进行分组，遵照索引建的最佳左前缀。优化方式和order by子句类似。

当无法使用索引时，增大max_length_for_sort_data参数和sort_buffer_size参数的值。

where高于having，能写在where限定的条件就不要去having限定了。

2、慢查询日志

作用：用来记录运行时间超过long_query_time值的SQL的日志文件。long_query_time的默认值为10。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动开启。如果不是调优需要的话，一般不建议启动慢查询日志，因为开启慢查询日志会带来性能影响。

查看慢查询日志是否开启：

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| slow_query_log      | OFF                                             |#off:关闭
| slow_query_log_file | /var/lib/mysql/iZbp1dm5fdkrqk5zoa0y07Z-slow.log |#日志位置
+---------------------+-------------------------------------------------+
2 rows in set (0.01 sec)

开启慢查询日志功能

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| slow_query_log      | ON                                              |
| slow_query_log_file | /var/lib/mysql/iZbp1dm5fdkrqk5zoa0y07Z-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.00 sec)

只对当前数据库生效，如果MySQL重启后慢查询日志功能又会关闭。、

查看long_query_time值

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

设置long_query_time值

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

设置后long_query_time值还是10？
需要重新连接或新开一个会话才能看到修改值。

查询当前系统中有多少条慢查询记录

mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     | # 有1条慢查询记录
+---------------+-------+
1 row in set (0.01 sec)

3、日志分析工具mysqldumpslow

作用：分析日志，查找分析sql

root@iZbp1dm5fdkrqk5zoa0y07Z:/var/lib/mysql# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time   # 按平均锁定时间排序
                ar: average rows sent   # 按平均返回记录排序
                at: average query time  # 按平均查询时间排序
                 c: count               # 按访问次数排序
                 l: lock time           # 按锁定时间排序
                 r: rows sent           # 按返回记录排序
                 t: query time          # 按查询时间排序
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries   # 返回前n条的数据
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string  
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

常用命令：

• 得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

• 得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

• 得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

4、 show profile

作用：MySQL中用来分析sql语句执行的资源消耗情况。可以用于SQL的调优的测量。

默认情况下show profile功能是关闭的

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

开启show profile功能

mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.01 sec)

案例：新建dept表和emp表，向emp表中添加50万数据，再用show profile分析查询sql

Step1、建库建表

create database bigData;
use bigData;

CREATE TABLE dept(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
	dname VARCHAR(20)NOT NULL DEFAULT "",
	loc VARCHAR(13) NOT NULL DEFAULT ""
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE emp(
    id int unsigned primary key auto_increment,
    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=INNODB DEFAULT CHARSET=utf8;

Step2、创建函数，随机生成字符串

delimiter $$  # 两个 $$ 表示结束
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    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 function rand_num() returns int(5)
begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
end $$

如果创建函数时出现错位：ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

解决办法

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

Step3、创建存储过程

创建往emp表中插入数据的存储过程

delimiter $$
create procedure insert_emp(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 emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
        until i=max_num
        end repeat;
    commit;
end $$

创建往dept表中插入数据的存储过程

delimiter $$
create procedure insert_dept(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 dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
        until i=max_num
        end repeat;
    commit;
end $$

Step4、调用存储过程

往dept表中插入数据

mysql> DELIMITER ;
mysql> CALL insert_dept(100, 10);
Query OK, 0 rows affected (0.02 sec)

往emp表中插入50万数据

mysql> CALL insert_emp(100001, 500000);  # 这里可能卡一会的情况，很正常
Query OK, 0 rows affected (1 min 15.76 sec)

查看执行的sql

mysql> show profiles;
+----------+------------+-----------------------------------------------------+
| Query_ID | Duration   | Query                                               |
+----------+------------+-----------------------------------------------------+
|        1 | 0.00030625 | select * from dept                                  |
|        2 | 0.00030350 | select * from emp where name like 'a%' limit 10000  |
|        3 | 0.00218700 | desc emp                                            |
|        4 | 0.13977450 | select * from emp where ename like 'a%' limit 10000 |
|        5 | 0.00007800 | show proflie                                        |
|        6 | 0.00334675 | show variables like 'profiling'                     |
|        7 | 0.15375675 | select * from emp where ename like 'a%' limit 10000 |
+----------+------------+-----------------------------------------------------+

step5、诊断sql

mysql>  show profile cpu,block io for query 2;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000132 | 0.000080 |   0.000048 |            0 |             0 |
| Executing hook on transaction  | 0.000008 | 0.000005 |   0.000002 |            0 |             0 |
| starting                       | 0.000010 | 0.000006 |   0.000004 |            0 |             0 |
| checking permissions           | 0.000007 | 0.000005 |   0.000003 |            0 |             0 |
| Opening tables                 | 0.000056 | 0.000035 |   0.000021 |            0 |             0 |
| end                            | 0.000004 | 0.000002 |   0.000001 |            0 |             0 |
| query end                      | 0.000006 | 0.000004 |   0.000003 |            0 |             0 |
| closing tables                 | 0.000010 | 0.000006 |   0.000003 |            0 |             0 |
| freeing items                  | 0.000047 | 0.000029 |   0.000017 |            0 |             0 |
| cleaning up                    | 0.000025 | 0.000016 |   0.000010 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
10 rows in set, 1 warning (0.00 sec)

参数备注

参数	介绍
ALL	显示所有的开销信息
BLOCK IO	显示块lO相关开销
CONTEXT SWITCHES	上下文切换相关开销
CPU	显示CPU相关开销信息
IPC	显示发送和接收相关开销信息
MEMORY	显示内存相关开销信息
PAGE FAULTS	显示页面错误相关开销信息息
SOURCE	显示和Source_function，Source_file，Source_line相关的开销信息
SWAPS	显示交换次数相关开销的信息

日常开发需要注意的事项

• converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
• Creating tmp table 创建临时表，拷贝数据到临时表，用完再删除
• Copying to tmp table on disk 把内存中临时表复制到磁盘，危险!
• locked

5、全局查询日志（生产环境不要开）

启用全局查询日志

set global general_log=1;
set global log_output='TABLE'

查看执行的sql

select * from mysql.general_log;