查询截取分析

半塘 2023/8/27 数据库MySQL

# 1、分析慢SQL步骤

项目出现性能问题，需要分析慢SQL，找出问题SQL

分析步骤

观察，至少跑1天，看看生产的慢SQL情况
开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来
explain + 慢SQL分析
show Profile
运维经理 or DBA，进行MySQL数据库服务器的参数调优

总结（大纲）

慢查询的开启并捕获
explain + 慢SQL分析
show Profile查询SQL在MySQL数据库中的执行细节和生命周期情况
MySQL数据库服务器的参数调优

# 2、查询优化

# 2.1、小表驱动大表

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

例子

/**
* 举个例子：可以使用嵌套的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++){
        
    }
}

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

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

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

EXISTS

语法：SELECT...FROM tab WHERE EXISTS(subquery);

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

注意点：

EXISTS(subquery) 子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别
EXISTS(subquery) 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题
EXISTS(subquery) 子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析

# 2.2、ORDER BY优化

MySQL排序算法

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

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

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

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

单路排序算法问题： 如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法

单路复用算法的优化策略：

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

优化验证

数据准备

CREATE TABLE talA (
  id 		integer primary key auto_increment,
  age 	INT,
  birth TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO talA(age) VALUES(18);
INSERT INTO talA(age) VALUES(19);
INSERT INTO talA(age) VALUES(20);
INSERT INTO talA(age) VALUES(21);
INSERT INTO talA(age) VALUES(22);
INSERT INTO talA(age) VALUES(23);
INSERT INTO talA(age) VALUES(24);
INSERT INTO talA(age) VALUES(25);

-- 创建索引
CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);

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

验证过程

/* 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;

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

结论

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

ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序
MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低

结论2: ORDER BY满足两情况，会使用Index方式排序

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

结论3: 提高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的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率

# 2.3、GORUP BY优化

GORUP BY优化跟ORDER BY优化一样，只有下面几点需要注意下

GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能
WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了

# 2.4、总结

排序使用索引

MySQL两种排序方式：Using filesort和Using Index扫描有序索引排序
MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询

/* 创建a b c三个字段的索引 */
idx_table_a_b_c(a, b, c)

/* 1.ORDER BY 能使用索引最左前缀 */
ORDER BY a;
ORDER BY a, b;
ORDER BY a, b, c;
ORDER BY a DESC, b DESC, c DESC;

/* 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 */
WHERE a = 'Ringo' ORDER BY b, c;
WHERE a = 'Ringo' AND b = 'Tangs' ORDER BY c;
WHERE a = 'Ringo' AND b > 2000 ORDER BY b, c;

/* 3.不能使用索引进行排序 */
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;  /* 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询 */

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

# 3、慢查询日志分析

# 3.1、什么是慢查询日志？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中
long_query_time 的默认值为10，意思是运行10秒以上的语句
由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前 explain 进行全面分析

# 3.2、如何开启慢查询日志？

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';
开启慢查询日志：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

1
2
3
4
5
6
7

开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？

这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒
MySQL中查看long_query_time的时间SHOW VARIABLES LIKE 'long_query_time%';

修改long_query_time的时间，需要在my.cnf修改配置文件

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

1
2
3

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

# 3.3、如何查看慢查询日志？

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

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

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

打开cat /var/lib/mysql/slow.log

/usr/sbin/mysqld, Version: 5.7.34 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
use db01;
SET timestamp=1635343413;
/* ApplicationName=IntelliJ IDEA 2023.8.27 */ select sleep(4);

1
2
3
4
5
6

# 4、批量数据插入

需要模拟大数据量时，可以使用批量插入，涉及到数据库函数和存储过程知识点

# 4.1、准备

建表

/* 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

由于开启过慢查询日志，开启了bin-log，我们就必须为function指定一个参数，否则使用函数会报错，需要bin-log信任函数创建，函数创建的内容才能够被bin-log记录下来

# 在mysql中设置 
# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

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

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

[mysqld]
log_bin_trust_function_creators=ON

1
2

# 4.2、创建函数

随机函数，就是随机返回一个字符串

# 1、函数：随机产生字符串
DELIMITER $$       # 修改结束符，之前是;(分号)，现在修改为$$，不然函数中的;就会被认为结束符，函数就无法执行了
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)    # rand_string：函数名，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  # 当i<n时，执行循环体
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  # 返回值赋值
    SET i = i + 1;  # 变量自增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 $$

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

# 4.3、创建存储过程

# 1、函数：向dept表批量插入
DELIMITER $$  # 修改结束符，之前是;(分号)，现在修改为$$，不然函数中的;就会被认为结束符，函数就无法执行了
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) # 创建一个存储过程，存储过程名：insert_dept，参数：START，max_num
BEGIN
    DECLARE i INT DEFAULT 0;  # 定义一个变量，默认值为0
    SET autocommit = 0;   # 自动提交关闭
    REPEAT # 循环
    SET i = i + 1;  # 变量自增1
    INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8)); # 循环中执行插入语句
    UNTIL i = max_num # 结束循环，当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 $$

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 4.4、调用存储过程

# 1、调用存储过程向dept表插入10个部门。
DELIMITER ; # 修改结束符为;，不然存储过程中的$$会被认为是结束符，存储过程就无法执行了
CALL insert_dept(100,10);

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

1
2
3
4
5
6
7

# 5、Show Profile

Show Profile：MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤

是否支持，看看当前的MySQL版本是否支持

# 查看Show Profile功能是否开启
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

开启Show Profile功能，默认是关闭的，使用前需要开启SET profiling=ON;

# 开启Show Profile功能
mysql> SET profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

1
2
3

运行SQL

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

SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;

1
2
3

查看结果，执行SHOW PROFILES; Duration：持续时间

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling'                   |
|        2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5   |
+----------+------------+---------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10

诊断SQL，SHOW PROFILE cpu,block io FOR QUERY Query_ID;

# 这里的3是第四步中的Query_ID
# 可以在SHOW PROFILE中看到一条SQL中完整的生命周期
mysql> SHOW PROFILE cpu,block io FOR QUERY 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000097 | 0.000090 |   0.000002 |            0 |             0 |
| checking permissions | 0.000010 | 0.000009 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000039 | 0.000058 |   0.000000 |            0 |             0 |
| init                 | 0.000046 | 0.000046 |   0.000000 |            0 |             0 |
| System lock          | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000023 | 0.000037 |   0.000000 |            0 |             0 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000041 | 0.000053 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.520620 | 0.516267 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000060 | 0.000051 |   0.000000 |            0 |             0 |
| end                  | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000032 | 0.000064 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Show Profile查询参数备注

ALL：显示所有的开销信息
BLOCK IO：显示块IO相关开销（通用）
CONTEXT SWITCHES：上下文切换相关开销
CPU：显示CPU相关开销信息（通用）
IPC：显示发送和接收相关开销信息
MEMORY：显示内存相关开销信息
PAGE FAULTS：显示页面错误相关开销信息
SOURCE：显示和Source_function
SWAPS：显示交换次数相关开销的信息

Show Profile查询列表，日常开发需要注意的结论

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

1
2
3
4
5
6
7
8

在命令配置

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

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

1
2
3
4
5

索引优化分析 MySQL锁机制

星云-全栈知识体系

Choose mode