MySQL-慢查询

最新推荐文章于 2024-04-30 16:27:04 发布

智博的自留地

最新推荐文章于 2024-04-30 16:27:04 发布

阅读量1.1k

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/XZB119211/article/details/127651432

版权

MySQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

一、创建一个100万条数据的表

1、首先将以下建表的sql放入一个sql文件中，然后建数据库执行。

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、插入100万条数据

直接进入mysql命令行，新建过程，然后执行

delimiter $$
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=187635267;
SET ageVal=100;
SET i=1;
WHILE i < 1000000 DO
		INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+i)%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END $$

3、执行过程

在命令行执行即可，此过程比较慢，可能要几个小时，可以查看数据库插入了多少调数据。

delimiter ;
call proc_batch_insert();

二、排查慢查询的sql

我们需要打开mysql的慢查询日志，在这个日志里会记录我们慢查询的sql，当然这个慢查询日志中的慢查询是有时间限定的，我们可以更改慢查询的记录时间。

1、查看数据库慢查询日志是否开启

show variables like 'slow_query_log';

在这里插入图片描述
默认的慢查询日志是你关闭的，我们选择打开

2、打开慢查询日志

set global slow_query_log=on;

在这里插入图片描述

3、查看慢查询的阈值（超过这个时间会被记录在日志中）

show variables like 'long_query_time’;

在这里插入图片描述

4、更改阈值，方便我们进行实验（一般的查询不会超过10s）

set long_query_time=0.3;

在这里插入图片描述

5、查看慢查询日志的位置

show variables like 'slow_query_log_file';

在这里插入图片描述
这个文件存储在C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下。

6、测试慢查询用例

（1）、用例一

select * from t_user;

在这里插入图片描述

（2）、用例二

SELECT
	id,NAME,
	age 
FROM
	t_user 
where name = '187795367@qq.com';

在这里插入图片描述

7、查看慢查询日志

打开C:\ProgramData\MySQL\MySQL Server 8.0\Data下的WIN-M7UTQG0KFSK-slow.log文件。
在这里插入图片描述
这里显示出我们慢查询的两条sql 数据同时显示出了慢sql的执行语句以及慢sql的执行时间（超过我们设置的0.3s的才记录下来）同时还显示出了查询的行数;找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

小结：

mysql判断sql语句是不是慢查询，是根据语句的执行时间来衡量的，mysql会用语句的执行时间和long_query_time这个系统参数做比较，如果语句执行时间大于long_query_time，都会把这个语句记录到慢查询日志里面。long_query_time的默认值是10s，一般生产环境不会设置这么大的值，一般设置1秒。

三、慢查询优化

我们前面已经知道两个用例的sql在我们定义下属于慢查询，下面我们通过explain命令来查看一下SQL语句的执行计划，查看其索引的使用情况、有没有全表扫描等内容。

0、mysqldumpslow工具

以MySQL为例，一般使用mysqldumpslow工具分析慢查询日志，使用命令查询慢SQL语句。此工具在mysql安装目录的bin目录下，

1、查询sql的执行计划

在这里插入图片描述为了直观展示查询结果，这里使用navicat执行sql。
主要看type那列，ALL标识全文检索，所以这条sql查询很慢。
解决：最简单有效的方法就是：加索引。

2、加索引

alter table 表名 add index nameIndex (列名);

在这里插入图片描述
此时已经不是全盘扫描了，重新进行查询

比较：索引前后的查询时间变化很大，没有索引的时候查询时间1.62s，有索引之后，查询一瞬间。

3、小结

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。