MySQL 高级 (3) 查询截取分析

在对慢 SQL 进行分析时，一般需要以下几步

1. 开启慢查询日志，设置阈值 ( 比如超过 5s 的就是慢 SQL )，并抓取慢 SQL
2. explain + 慢 SQL 分析。分析诸如 SQL 写的不好，或者索引失效等原因
3. 分析 show profile， show profile 是比 explain 更近一步的执行细节，可以查询到每一个 SQL 都干了什么，并且这些 SQL 都花了多少秒
4. 找 DBA 或者运维对 MySQL 或服务器进行参数调优

小表驱动大表

假设有两段循环如下

for(int i=0; i<5; i++){
    for(int j=0; j<10000; j++){
        //...
    }
}
for(int i=0; i<10000; i++){
    for(int j=0; j<5; j++){
        //...
    }
}

从结果上来说，两段循环会执行同样的次数

但是对于 MySQL 而言，第二段循环则是做了 1000 次连接，然后每个连接只查询了5次；第一段循环则是做了 5 次连接，然而每次连接做了 10000 次查询

数据库连接是非常消耗系统资源的，在 Java 甚至是一种重量级的对象。因此小表驱动大表显得就十分重要

在 MySQL 中，in 和 exists 能实现类似于嵌套循环的效果

in 和 exists

in

select d.department_id
from departments d
where d.department_id
in (select e.department_id from employees e);

其中，departments 表的记录数为 27，employees 表的记录数为 107

这样写就相当于

for select d_id from e
    for select d_id from ds d and d.d_id = e.d_id

所以，in 子查询下，in 中的子查询应该选取小表，因为其充当的是外层循环

exists

exists 将主查询的数据放到子查询中做条件验证，根据验证结果 ( true or false ) 来决定主查询的数据是否保留

SELECT d.department_id
FROM departments d
WHERE EXISTS 
(SELECT e.`department_id` FROM 
    employees e 
     WHERE d.department_id = e.department_id);

这段使用 exists 的子查询，等价于

for select d.d_id from d
    for select e.d_id from e where d.d_id = e.d_id

所以，exists 子查询下，exists 中的子查询应该选取大表，因为其充当了内层循环

order by 优化

首先创建测试用表并插入数据，最后创建索引

create table if not exists tblA(
    age int,
    birth timestamp not null
);

insert into tblA(age, birth)
values(22, now());
insert into tblA(age, birth)
values(23, now());
insert into tblA(age, birth)
values(24, now());

create index idx_a_ageBirth on tblA(age, birth);

MySQL 对排序有两种标准，一种是 filesort，另一种是 index，index 指的是 MySQL 通过扫描索引本身就可以完成排序，效率远大于 filesort

对排序的优化，其重点就在于优化其可能出现的 filesort 文件排序

出现 filesrot 的情况

第一种情况：当排序索引顺序与索引创建顺序不一致时，且都是默认排序方式时，会出现文件排序

EXPLAIN 
SELECT * FROM tblA
WHERE age>20 ORDER BY birth, age;

第二种情况：排序索引顺序与索引创建顺序一致，但是排序方式不同时，会出现文件排序

EXPLAIN 
SELECT * FROM tblA
ORDER BY age ASC, birth DESC;

第三种情况：排序字段中出现了没有索引的字段

出现 using index 的情况

1. order by 语句准守了最左匹配原则
2. 使用 where 子句与 order by 子句中的字段组合满足最左匹配原则 ( 前提是 where 子句中没有中间索引或最左索引用于范围条件 )

filesort 的两种算法

filesort 是通过相应的排序算法，将取得的数据在内存中进行排序，MySQL 将数据在内存中进行排序，所使用的内存区域大小是通过 sort_buffer_size 系统变量设置的

这个排序区是每个线程独享的，即同一时刻 MySQL 中可能存在多个 sort buffer 排序区

MySQL 的 filesort 的实现算法有两种

1. 双路排序：MySQL 4.1 之前使用的是双路排序，双路排序需要对磁盘进行两次扫描，才能得到最终数据
   首先根据相应的条件取出相应的排序字段，以及可以直接定位到行记录的行指针信息，然后在 sort buffer 中进行排序，排序后再把查询字段依照行指针取出，需要两次磁盘 IO (第一次是取出排序字段，第二次是取出其它字段)
   为了减少磁盘 IO，出现了改进的单路排序算法

2. 单路排序：从磁盘读取出查询所需要的所有列，按照 order by 字段在 sort buffer 对他们进行排序，然后扫描排序后的表进行输出，只需要一次磁盘 IO，但是使用了更大的内存空间，因为该算法将所有的行都保存在了内存中

单路算法在总体性能上是优于双路算法的，但在一定情况下单路会出现问题 ( 其实双路和单路都会出现下面这个问题，但是单路由于对内存需求更大，因此出现问题的可能性就更大 )

比如：单路排序因为使用了 sort buffer，将所有字段对应的行都取出来放入其中，如果 sort buffer 的容量不足以容下所有的数据，就会导致每次只能取 sort_buffer_size 大小的数据进行排序 ( 创建中间文件，最后多路合并 )，排完后再区 sort_buffer_size 大小的数据进行排序 … 以此类推，可能会出现比双路排序更多的 IO 次数

针对这种情况，可以增大 sort_buffer_size 参数的大小和增大 max_length_for_sort_data 参数的大小

总结

想要提高 order by 排序效率，需要遵守以下几点

1. 尽可能遵循最左匹配原则，从而只依靠索引就完成排序

2. order by 时，select * 是大忌，尽量遵守只查询需要的字段这一原则。需要准守这一原则的原因如下

   • 当查询字段的大小总和小于 max_length_for_sort_data 并且排序字段不是 text || blob 类型时，会采用单路排序，反之采用多路。因此尽量避免查询字段大小总和大于 max_length_for_data_sort
   • 两种算法的数据都有可能超出 sort_buffer 的容量，超出后都会创建临时文件最后进行归并，导致多次 IO。但是由于单路会把所有字段涉及的行都读取到 sort buffer，因此如果 select * 的话，风险非常之大，同时也有很大的可能浪费内存空间 (选取了不必要的字段)
3. 尝试提高 sort_buffer_size：不论使用何种算法，增大该参数都会提高效率，但是注意 sort buffer 是线程独享的，因此要根据系统能力合理设置
4. 尝试提高 max_length_for_sort_data：增大该参数，可以增加使用单路排序算法的概率，但是如果设置的太大了，数据总容量超出 sort_buffer_size 的概率就会增大，显著特征是高磁盘 IO 和 低 CPU 使用率

慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，用来记录在 MySQL 中运行时间超过阈值的语句，阈值通过 long_query_time 参数来指定，单位为 s

默认情况下 ，MySQL 没有开启慢查询日志，需要手动开启

查看是否开启

SHOW VARIABLES LIKE "%slow_query_log%";

开启

set global slow_query_log=1;

设置的全局变量在 MySQL 重启后会失效，如果需要永久生效，需要修改文件 my.cnf，在 [mysqld] 下增加参数

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/test-slow.log

查看默认阈值

show variables like "long_query_time";

该值可以使用命令修改，也可以在 my.cnf 中设置参数修改

当一段 SQL 的运行时间正好等于 long_query_time 时，并不会被记录下来

设置阈值

set global long_query_time = 3;
set long_query_time = 3;

在设置阈值之后，对当前连接不会生效，需要新建一个连接

测试是否能够记录慢 SQL

在开启慢查询日志，并且设置阈值为 3 后，执行下面命令或其它的

select sleep(4);

然后根据命令

SHOW VARIABLES LIKE "%slow_query_log%";

找到对应的慢查询日志并查看

可以看见慢查询日志中，显示了具体的慢 SQL 指令，以及具体的执行时间

同时，系统会记录慢 SQL 的具体条数，查看当前系统中有多少条慢 SQL 可以使用命令

SHOW GLOBAL STATUS LIKE "%Slow_queries%";

日志分析工具 mysqldumpslow

在生产环境中，手工分析慢查询日志效率是很低下的，因此 MySQL 提供了日志分析工具 mysqldumpslow

因为在安装时我使用了 docker 进行安装，因此我需要先进入容器内部，然后执行下面命令

mysqldumpslow --help

各种参数信息如下

• s：表示按照何种方式排序
• c：访问次数
• l：锁定时间
• r：返回记录
• t：查询时间
• al：平均锁定时间
• ar：平均返回记录数
• at：平均查询时间
• -t NUM：返回前面多少条数据
• -g PATTERN：后边搭配一个正则匹配模式，大小写不敏感

#返回日志中查询所返回的记录集最多的前 10 条 sql
mysqldumpslow -s r -t 10 /var/lib/mysql/443d9573445f-slow.log

#得到访问次数最多的 10 条 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/443d9573445f-slow.log

#得到按照时间排序的前 10 条 SQL 中含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/443d9573445f-slow.log

在使用命令时，可以通过管道流和 more 一起使用，避免刷屏

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/443d9573445f-slow.log | more

批量插入数据

创建表

create table if not exists 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 ""
);

create table if not exists 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
);

开始批量数据插入前，需要先设置

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

想要永久生效则在配置文件中增加 log_bin_trust_function_creators=1 配置

创建函数

要求：有两个函数，分别能随机产生字符串和随机产生部门编号

#随机产生字符串
delimiter $
create function getRondomStr(len int) returns varchar(255)
begin
    declare re varchar(255) default "";
    set re = SUBSTRING(MD5(RAND()), 1, len);
    return re;
end $

#随机产生整数
delimiter $
create function getRondomInt() returns int
begin
    #随机生成 5 位整数
    return (rand()*900000+100000);
end$

创建存储过程并执行

使用存储过程向 emp 中一次插入 50w 条数据

delimiter $
create procedure insertEmp(in startNum int, in endNum int)
begin
    declare i int default 0;

    #设置为 0，不让 insert 自动提交，不然的话要提交 50w 次。而是应该将 50w 条数据一次性提交
    set autocommit = 0;
    while i<endNum
    DO
        insert into emp
        (empno, ename, job, mgr, hiredate, sal, comm, deptno)
        values
        (startNum+i, getRondomStr(6), 
         "SALESMAN", 0001, CURDATE(), 2000, 4000,
         getRondomInt()
        );
        set i = i+1;
    end while;
    commit;
end $ 

CALL insertEmp(0, 500000);

向 dept 中一次插入 50w 条数据

delimiter $
create procedure insertDept(in startNum int, in endNum int)
begin
    declare i int default 0;

    set autocommit = 0;
    while i<endNum
    do
        insert into dept(deptno, dname, loc)
        values
        (startNum+i, getRondomStr(10), 2);
        set i = i+1;
    end while;
    commit;
end $

call insertDept(0, 500000);

Show Profile

show profile 是 MySQL 提供的可以用来分析当前会话中指定 SQL 语句执行时资源消耗情况的工具，可用于 SQL 调优测量，是比 explain 细粒度更高的信息清单。默认情况下为关闭状态，并保存最近 15 次的运行结果

查看当前状态

show variables like "profiling";

设置开启

SET profiling=1;

使用

在批量插入数据的基础上进行使用

运行以下两段 SQL

SELECT id%10
FROM emp 
GROUP BY id%10 LIMIT 150000;

EXPLAIN SELECT id%20
FROM emp
GROUP BY id%20 ORDER BY 1;

然后执行

show profiles\G

可以发现，show profiles 查询出了从当前会话开始到目前为止所有执行过的查询以及其执行时间

知道了每段 SQL 的执行时间后，我们就可以对其进行分析

分析命令为

show profile cpu, block io for query + query_id;

show profile 的可选参数类型有

• all：显示所有开销信息
• block io：显示与 IO 相关的开销信息
• context switches：显示与上下文切换相关的开销信息
• cpu：显示与 cpu 相关的开销信息
• ipc：显示与发送和接收数据相关的开销信息
• memory：显示与内存相关的开销信息
• page faults：显示与页面错误时相关的开销信息
• source：显示与 source_function，source_file，source_line 相关的开销信息
• swaps：显示与交换次数相关的开销信息

show profile cpu, block io for query [, 更多参数类型] + query_id;

例如，我们分析 query_id = 1 的 SQL 的 cpu 开销和 IO 开销

show profile cpu,block io for query 1;

可以发现大部分时间都花在了 sending data 这一步上，sending data 即是数据传输和接收所花费的时间，至少包含了

• InnoDB 层数据的定位返回给 MySQL 层
• InnoDB 层数据的查询返回给 MySQL 层
• InnoDB 层数据的修改
• InnoDB 层加锁以及等待
• 等待进入 InnoDB 层 ( innodb_thread_concurrency 参数)
• MySQL 层发送数据给客户端

值得注意的是，不同的查询语句其使用 show profile 显示的 步骤 ( status ) 并不是完全一样的，但是如果出现以下 status 则表示 SQL 可以进行优化了

• converting HEAP to MyISAM：在 MyISAM 存储引擎下表示查询结果太大，内存不够用了，需要使用到磁盘作为中介
• creating tmp table：使用到了临时表。先把数据拷贝到临时表，用完后再删除
• copying to tmp table on disk：将内存中临时表复制到磁盘
• locked

Performance Schema

在 MySQL 官方文档中，明确表示了 SHOW PROFILE 和 SHOW PROFILES 在未来版本将被取消，建议更换为 Performance Schema 进行查询分析

https://dev.mysql.com/doc/refman/5.7/en/show-profiles.html

机翻文档

https://www.deituicms.com/mysql8cn/cn/performance-schema.html#performance-schema-runtime-configuration

首先查看 Performance Schema 是否启动

SHOW VARIABLES LIKE 'performance_schema';

在 MySQL5.7 中，Performance Schema 是默认启动的

性能模式基于存储引擎，通过命令查看存储引擎是否支持性能模式

SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='PERFORMANCE_SCHEMA';

由于我不是专业的 DBA，只是一介草民，所以具体的介绍在这里就略过了，详情可以参考这篇博客

https://www.cnblogs.com/zhoujinyi/p/5236705.html

这里就简单说一下，Performance Schema 做到和 show profile 一样的具体分析某条 SQL，以及该 SQL 在不同阶段的时间消耗

1. 查看 setup_actors 历史事件收集，并在后续操作中将其设置为本地用户

   SELECT * FROM performance_schema.setup_actors;
   

1. 更新 setup_actors 表中默认行，禁用前台线程的步骤收集

   UPDATE performance_schema.setup_actors
       SET ENABLED = 'NO', HISTORY = 'NO'
       WHERE HOST = '%' AND USER = '%';
   

然后插入新的记录，为本地用户启用监视和步骤收集

INSERT INTO performance_schema.setup_actors
       (HOST,USER,ROLE,ENABLED,HISTORY)
   VALUES('localhost','test_user','%','YES','YES');

然后再次查询 setup_actors

SELECT * FROM performance_schema.setup_actors;

1. 启用 setup_instruments，确保能够保存到表中 ```mysql UPDATE performance_schema.setup_instruments

   SET ENABLED = 'YES', TIMED = 'YES'
   WHERE NAME LIKE '%statement/%';
   

UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES’, TIMED = ‘YES’ WHERE NAME LIKE ‘%stage/%’;

4. 
确保启用 events_statements_* 和 events_stage_*
```mysql
UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_statements_%';

UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_stages_%';

1. 执行要分析的语句

   SELECT id%10
   FROM emp 
   GROUP BY id%10 LIMIT 150000;
   

1. 通过 events_statement_history_long 中的 sql_text 字段模糊查询出执行的语句 ( 也可以通过 events_statement_history 表查询，二者区别为可存储的记录数不同 )

   select event_id, sql_text
   from events_statements_history
   where sql_text like "%limit 150000%";
   

1. 通过 events_stages_history_long 中的 nesting_event_id 字段与查询出来的 even_id 等值查询，即可查出要分析的 SQL 的步骤

   SELECT NESTING_EVENT_ID, event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
   FROM events_stages_history_long WHERE NESTING_EVENT_ID=3972;
   

全局查询日志

在生产环境中不要开启此功能

在 MySQL 的配置文件中，增加如下配置

general_log = 1
#日志文件路径
general_log_file = /var/lib/mysql/443d9573445f.log
#输出格式
log_outpt = file

或者通过命令设置

set global general_log = 1;
set global log_output = "TABLE";
#然后，所执行的 sql 语句就会被记录到 mysql 库的 general_log 表中
select * from mysql.`general_log`;