「这是我参与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
- MySQL执行的SQL
- FORM子句:左右两个表的笛卡尔积
- ON:筛选满足条件的数据
- JOIN:如果是inner join那就正常,如果是outer join则会添加回来上面一步过滤掉的一些行
- WHERE:对不满足条件的行进行移除,并且不能恢复
- GROUP BY:分组后只能得到每组的第一行数据,或者聚合函数的数值
- HAVING:对分组后的数据进行筛选
- SELECT:执行select操作,获取需要的列
- DISTINCT:去重
- ORDER BY:排序
- LIMIT:取出指定行的记录,并将结果返回
- 查看下面的SQL分析执行顺序
select
id,
sex,
count(*) AS num
from
employee
where name is not null
group by sex
order by id
- 上面的SQL执行执行顺序如下
- 首先执行 FROM 子句,从employee表组装数据源的数据
- 执行WHERE子句,筛选employee表中所有name不为NULL的数据
- 执行GROUP BY子句,按 "性别" 列进行分组
- 执行select操作,获取需要的列
- 最后执行order by,对最终的结果进行排序
JOIN查询的七种方式
- 7种JOIN,可以分为四类:内连接 、左连接 、右连接、 全连接
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);
内连接
SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptid = d.id
左连接
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
左连接去重叠部分
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL;
右连接
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
右连接去重叠部分
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL;
全连接
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语句会删除重复的数据。
各自独有
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数据库并不启动慢查询日志,需要手动来设置这个参数。
如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件和数据库表。
慢查询参数
- 执行下面的语句
SHOW VARIABLES LIKE "%query%" ;
- MySQL 慢查询的相关参数解释:
- slow_query_log:是否开启慢查询日志, 1表示开启, 0表示关闭。
- slow-query-log-fifile:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。
- long_query_time: 慢查询阈值,当查询时间多于设定的阈值时,记录日志。
慢查询配置方式
- 默认情况下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 |
+---------------------+-----------------------------------+
- 可以通过设置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 |
+---------------------+-----------------------------------+
- 使用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 |
+---------------------+-------------------------------+
- 那么开启了慢查询日志后,什么样的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 |
+-----------------+-----------+
- 我修改了变量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 |
+-----------------+----------+
- 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'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件
- 系统变量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)
慢查询测试
-
执行test_index.sql脚本,导入测试表
-
执行下面的SQL,执行超时 (超过1秒) 我们去查看慢查询日志
SELECT * FROM test_index WHERE
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931';
- 日志内容
我们得到慢查询日志后,最重要的一步就是去分析这个日志。我们先来看下慢日志里到底记录了哪些内容。
如下是慢日志里其中一条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