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

920 阅读7分钟

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

SQL性能下降的原因

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

  • 等待时间长
1.锁表导致查询一直处于等待状态,后续我们从MySQL锁的机制去分析SQL执行的原理
  • 执行时间长
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分析执行顺序
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编写

创建表插入数据

---部门表
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

SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptid = d.id

左连接

image.png

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id

左连接去重叠部分

image.png

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

右连接

image.png

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

右连接去重叠部分

image.png

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

全连接

image.png

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

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. 执行下面的语句
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,表示慢查询日志是禁用的
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的值来开启
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(其它系统变量也是如此)
-- 编辑配置
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秒
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修改后,需要重新连接或新开一个会话才能看到修改值。
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表中。
mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+

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

  1. 系统变量log-queries-not-using-indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。如果调优的话,建议开启这个选项。
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秒) 我们去查看慢查询日志

SELECT * FROM test_index WHERE
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931';
  1. 日志内容

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

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

==> 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