MySQL高级 - 查询和慢查询日志分析

发表于

「这是我参与11月更文挑战的第28天，活动详情查看：2021最后一次更文挑战」

SQL性能下降的原因

在日常的运维过程中，经常会遇到DBA将一些执行效率较低的SQL发过来找开发人员分析，当我们拿到这个SQL语句之后，在对这些SQL进行分析之前，需要明确可能导致SQL执行性能下降的原因进行分析，执行性能下降可以体现在以下两个方面：

  • 等待时间长
1
text复制代码1.锁表导致查询一直处于等待状态，后续我们从MySQL锁的机制去分析SQL执行的原理
  • 执行时间长
1
2
3
4
text复制代码1.查询语句写的烂
2.索引失效
3.关联查询太多join
4.服务器调优及各个参数的设置

需要遵守的优化原则

查询优化是一个复杂的工程，涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句的执行顺序、索引以及统计信息的采集等等方面

下面给大家介绍几个编写SQL的关键原则，可以帮助我们编写出更加高效的SQL查询

  • 第一条：只返回需要的结果
    • 一定要为查询语句指定WHERE条件，过滤掉不需要的数据行
    • 避免使用select * from, 因为它表示查询表中的所有字段
  • 第二条：确保查询使用了正确的索引
    • 经常出现在WHERE条件中的字段建立索引，可以避免全表扫描
    • 将ORDER BY排序的字段加入到索引中，可以避免额外的排序操作
    • 多表连接查询的关联字段建立索引，可以提高连接查询的性能
    • 将GROUP BY分组操作字段加入到索引中，可以利用索引完成分组
  • 第三条：避免让索引失效
    • 在WHERE子句中对索引字段进行表达式运算或者使用函数都会导致索引失效
    • 使用LIKE匹配时，如果通配符出现在左侧无法使用索引
    • 如果WHERE条件中的字段上创建了索引，尽量设置为NOT NULL

SQL的执行顺序

  • 程序员编写的SQL

image.png

  • MySQL执行的SQL

image.png

  1. FORM子句：左右两个表的笛卡尔积
  2. ON：筛选满足条件的数据
  3. JOIN：如果是inner join那就正常，如果是outer join则会添加回来上面一步过滤掉的一些行
  4. WHERE：对不满足条件的行进行移除，并且不能恢复
  5. GROUP BY：分组后只能得到每组的第一行数据，或者聚合函数的数值
  6. HAVING：对分组后的数据进行筛选
  7. SELECT：执行select操作，获取需要的列
  8. DISTINCT：去重
  9. ORDER BY：排序
  10. LIMIT：取出指定行的记录，并将结果返回
  • 查看下面的SQL分析执行顺序
1
2
3
4
5
6
7
8
9
mysql复制代码select
    id,
    sex,
    count(*) AS num
from
    employee
where name is not null
group by sex
order by id
  • 上面的SQL执行执行顺序如下
  1. 首先执行 FROM 子句，从employee表组装数据源的数据
  2. 执行WHERE子句，筛选employee表中所有name不为NULL的数据
  3. 执行GROUP BY子句，按 “性别” 列进行分组
  4. 执行select操作，获取需要的列
  5. 最后执行order by，对最终的结果进行排序

JOIN查询的七种方式

  • 7种JOIN，可以分为四类：内连接 、左连接 、右连接、 全连接

image.png

JOIN查询SQL编写

创建表插入数据

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
29
30
mysql复制代码---部门表
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
    `id` varchar(40) NOT NULL,
    `name` varchar(40) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---员工表
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
    `id` varchar(40) NOT NULL,
    `name` varchar(40) DEFAULT NULL,
    `age` int(3) DEFAULT NULL,
    `deptid` varchar(40) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `deptid` (`deptid`),
    CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `t_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入部门数据
INSERT INTO `t_dept` VALUES ('1', '研发部');
INSERT INTO `t_dept` VALUES ('2', '人事部');
INSERT INTO `t_dept` VALUES ('3', '财务部');

--插入员工数据
INSERT INTO `t_emp` VALUES ('1', '赵四', 23, '1');
INSERT INTO `t_emp` VALUES ('2', '刘能', 25, '2');
INSERT INTO `t_emp` VALUES ('3', '广坤', 27, '1');
INSERT INTO `t_emp` VALUES ('4', '玉田', 43, NULL);

内连接

image.png

1
mysql复制代码SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptid = d.id

左连接

image.png

1
mysql复制代码SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id

左连接去重叠部分

image.png

1
2
mysql复制代码SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL;

右连接

image.png

1
mysql复制代码SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id

右连接去重叠部分

image.png

1
2
mysql复制代码SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL;

全连接

image.png

1
2
3
mysql复制代码SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id

MySQL UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。多个SELECT语句会删除重复的数据。

各自独有

image.png

1
2
3
4
5
6
7
mysql复制代码SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL

UNION

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL

慢查询日志分析

慢查询介绍

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。

默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件和数据库表。

慢查询参数

  1. 执行下面的语句
1
mysql复制代码SHOW VARIABLES LIKE "%query%" ;
  1. MySQL 慢查询的相关参数解释：
  • slow_query_log：是否开启慢查询日志， 1表示开启， 0表示关闭。
  • slow-query-log-fifile：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。
  • long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录日志。

慢查询配置方式

  1. 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的
1
2
3
4
5
6
7
mysql复制代码mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
  1. 可以通过设置slow_query_log的值来开启
1
2
3
4
5
6
7
8
9
mysql复制代码mysql> set global slow_query_log=1;

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
  1. 使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
lua复制代码-- 编辑配置
vim /etc/my.cnf

-- 添加如下内容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/lagou-slow.log

-- 重启MySQL
service mysqld restart

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/lagou-slow.log |
+---------------------+-------------------------------+
  1. 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql复制代码mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

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

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
  1. 我修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？注意：使用命令set global long_query_time=1修改后，需要重新连接或新开一个会话才能看到修改值。
1
2
3
4
5
6
mysql复制代码mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
  1. log_output参数是指定日志的存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。 log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
1
2
3
4
5
6
mysql复制代码mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件

  1. 系统变量log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql复制代码mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+

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

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

慢查询测试

  1. 执行test_index.sql脚本，导入测试表
  2. 执行下面的SQL，执行超时 (超过1秒) 我们去查看慢查询日志
1
2
mysql复制代码SELECT * FROM test_index WHERE
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931';
  1. 日志内容

我们得到慢查询日志后，最重要的一步就是去分析这个日志。我们先来看下慢日志里到底记录了哪些内容。

如下是慢日志里其中一条SQL的记录内容，可以看到有时间戳，用户，查询时长及具体的SQL等

1
2
3
4
5
6
7
mysql复制代码==> lagou-slow.log <==
# User@Host: root[root] @ [192.168.52.1] Id: 4
# Query_time: 1.681371 Lock_time: 0.000089 Rows_sent: 3 Rows_examined: 5000000
SET timestamp=1604307746;
select * from test_index where
hobby = '20009951' or hobby = '10009931' or hobby = '30009931' LIMIT 0, 1000;
# Time: 2020-11-02T09:02:26.052231Z

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%