[大数据]MySQL高级3：查询截取分析

查询截取分析

如何分析

1. 观察，至少跑1天，看看生产的慢SQL情况。

2. 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。

3. explain + 慢SQL分析。

4. show Profile。

5. 运维经理 OR DBA，进行MySQL数据库服务器的参数调优。

总结（大纲）

1. 慢查询的开启并捕获。

2. explain + 慢SQL分析。

3. show Profile查询SQL在MySQL数据库中的执行细节和生命周期情况。

4. MySQL数据库服务器的参数调优。

一、查询优化

1 小表驱动大表

优化原则：对于MySQL数据库而言，永远都是小表驱动大表。

类似于嵌套循环Nested Loop

例子

可以使用嵌套的for循环来理解小表驱动大表。

以下两个循环结果都是一样的，但是对于MySQL来说不一样，

第一种可以理解为，和MySQL建立5次连接每次查询1000次。

第二种可以理解为，和MySQL建立1000次连接每次查询5次。

for(int i = 1; i <= 5; i ++){
    for(int j = 1; j <= 1000; j++){
        
    }
}
// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
for(int i = 1; i <= 1000; i ++){
    for(int j = 1; j <= 5; j++){
        
    }
}

IN 和 EXISTS

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

#IN适合B表比A表数据小的情况
SELECT * FROM A WHERE id IN (SELECT id FROM B)
#等价于
for SELECT id FROM B
for SELECT * FROM A WHERE A.id = B.id

#EXISTS适合B表比A表数据大的情况
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
#等价于
for SELECT * FROM A 
for SELECT * FROM B WHERE B.id = A.id

EXISTS：

• 语法：SELECT...FROM tab WHERE EXISTS(subquery);该语法可以理解为：

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

提示：

• EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。

• EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。

• EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

总结：A小于B（子查询的表）使用exists，A大于B（子查询的表）使用in

2. ORDER BY优化

如果你要听懂，最好看一下符合索引B树的排序原理

数据准备

create table tblA(
	#id int primary key not null auto_increment,
	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);

select * from tblA;

练习

1.使用索引进行排序了 不会产生Using filesort 
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;

2.使用索引进行排序了 不会产生Using filesort
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;

3.没有使用索引进行排序 产生了Using filesort 
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;

4.没有使用索引进行排序 产生了Using filesort 
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;

5.没有使用索引进行排序 产生了Using filesort 
EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;

6.没有使用索引进行排序 产生了Using filesort 
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;

 7.使用索引进行排序了 不会产生Using filesort 
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;

8.没有使用索引进行排序 产生了Using filesort 
EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;

结论

• ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。

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

• ORDER BY满足两情况，会使用Index方式排序

  • ORDER BY语句使用索引最左前列。
  • 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列。

总结：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

如果不在索引列上，File Sort有两种算法：MySQL就要启动双路排序算法和单路排序算法

双路排序算法和单路排序算法

双路排序

MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

即，从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。

单路排序

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

由于单路排序算法是后出的，总体而言效率好过双路排序算法。

但是单路排序算法有问题：在sort_buffer中，方法B（单路排序）比方法A（双路排序）要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取出sort_buffer容量大小的数据，进行排序（创建tmp文件，夺路合并），排完再取出sort_buffer容量大小，再排……从而多次I/O。

导致，本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失

优化策略（提高Order By的速度）

• 不要使用select *
  • 当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT/BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序
  • 两种算法的数据都有可能超过sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是使用单路排序算法风险会更大一些，所以要提高sort_buffer_size

• 增大sort_buffer_size参数的设置。
  • 不管用那个算法，提高这个参数都会提高效率。当然，要根据系统能力去提高，因为这个参数是针对每个进程的
• 增大max_length_for_sort_data参数的设置。
  • 提高这个参数，会增加用改进算法的概率，如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

小总结

• Mysql两种排序方式：文件排序或烧苗有序索引排序
• MySQL能为排序与查询使用相同的索引

KEY a_b_c(a,b,c)

/*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 and b > const order by b,c

/*不能使用索引进行排序*/
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  /*对于排序来说，多个相等条件也是范围查询*/

3. GROUP BY 优化

• GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。

• 当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。

• WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。

二、慢查询日志

1. 是什么

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。

• long_query_time 的默认值为10，意思是运行10秒以上的语句。

• 由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前 explain 进行全面分析。

2. 怎么用

说明

**默认情况下，MySQL数据库没有开启慢查询日志，**需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看是否开启及如何开启

• 查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。 默认为OFF

• 开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

如果要使慢查询日志永久开启，需要修改my.cnf文件，重启服务器，在[mysqld]下增加修改参数。

# my.cnf
[mysqld]
# 1.这个是开启慢查询。注意ON需要大写
slow_query_log=ON  

# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建
slow_query_log_file=/var/lib/mysql/slow.log

什么样的SQL会记录到慢查询日子

这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。

MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。

需要大于long_query_time才会被记录下来，小于等于都不会

参数设置

使用set global long_query_time=3;修改

修改long_query_time的时间，也可以在my.cnf修改配置文件

[mysqld]
# 这个是设置慢查询的时间，我设置的为1秒
long_query_time=1

修改后需要重新连接或新开一个会话才能看到修改值

或者使用SHOW VARIABLES LIKE 'long_query_time%';命令查看;

查看慢查询日志的总记录条数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';。

日志

配置版

[mysqld]
# 1.这个是开启慢查询.
slow_query_log=1  
# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建
slow_query_log_file=/var/lib/mysql/slow.log
# 3.这个是设置慢查询的时间，我设置的为1秒
long_query_time=1
log_output=file

3. 日志分析工具

日志分析工具 mysqldumpslow ：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow。

mysqldumpslow --help 来查看mysqldumpslow的帮助信息

# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息
root@1dcb5644392c:/usr/bin# 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  # 返回前面多少条记录
  -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/slow.log 
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log 
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
# 另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more

参数参考

-s：按照何种方式排序(al, at, ar, c, l, r, t)

c：访问次数

l：锁定时间

r: 返回记录

t： 查询时间

al：平均锁定时间

ar：平均返回记录数

at：平均查询时间

-t NUM ：返回前面多少条记录

-g PATTERN：后边搭配一个正则匹配模式

4. 批量数据脚本

往表里插入1000W数据

建立测试表

/* 1.dept表 */
CREATE TABLE `dept` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  `dname` varchar(20) NOT NULL DEFAULT '' COMMENT '部门名字',
  `loc` varchar(13) NOT NULL DEFAULT '' COMMENT '部门地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'

/* 2.emp表 */
CREATE TABLE `emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `empno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工编号',
  `ename` varchar(20) NOT NULL DEFAULT '' COMMENT '员工名字',
  `job` varchar(9) NOT NULL DEFAULT '' COMMENT '职位',
  `mgr` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',
  `hiredata` date NOT NULL COMMENT '入职时间',
  `sal` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '分红',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'

1. 设置log_bin_trust_function_creators

由于开启过慢查询日志，开启了bin-log，我们就必须为function指定一个参数，否则使用函数会报错。

# log_bin_trust_function_creators 默认是关闭的 需要手动开启
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

SET GLOBAL log_bin_trust_function_creators=1;

上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效。

[mysqld]
log_bin_trust_function_creators=1

2. 创建函数，保证每条数据都不同

# 1、函数：随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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 $$

# 2、函数：随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

3. 创建存储过程

# 1、函数：向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 $$

# 2、函数：向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,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    
    COMMIT;
END $$

4. 调用存储过程

# 1、调用存储过程向dept表插入10个部门。
DELIMITER ;
CALL insert_dept(100,10);

# 2、调用存储过程向emp表插入50万条数据。
DELIMITER ;
CALL insert_emp(100001,500000);

5. Show Profile

MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。

可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

1. 查看是否支持

SHOW VARIABLES LIKE 'profiling';

2. 开启功能，默认关闭

SET profiling=ON;

3. 运行sql

/*mysql5.7*/
SELECT *,id FROM `emp` GROUP BY `id`%10 LIMIT 150000;
SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;

/*5.7之后*/
SELECT * FROM `emp` GROUP BY deptno LIMIT 15000;
SELECT * FROM `emp` GROUP BY deptno ORDER BY 5;

4. 查询结果

SHOW PROFILES;

5. 诊断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。

• SWAPS：显示交换次数相关开销的信息。

6. 日常开发需要注意的结论

• converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。

• Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。

• Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！

• locked：死锁。

6. 全局查询日志

只在测试环境下才可以用

配置文件方式开启

在mysql的my.cnf中，设置如下
#开启
general_log=1

#记录日志文件的路径
general_log_file=/path/logfile

#输出格式
log_output=FILE

命令方式开启

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

#此后编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;

相关视频：尚硅谷MySQL数据库高级，mysql优化，数据库优化