【MySQL进阶】SQL性能分析

落日映苍穹つ 2023-10-15 20:12 37阅读 0赞

## 一、**SQL性能分析**  ##

### **1.SQL执行频率** ###

MySQL  客户端连接成功后，通过  show \[session|global\] status  命令可以提供服务器状态信

息。通过如下指令，可以查看当前数据库的 INSERT 、 UPDATE 、 DELETE 、 SELECT 的访问频次：

-- session 是查看当前会话 ;
    -- global 是查询全局数据 ;
    SHOW GLOBAL STATUS LIKE 'Com_______';

![58cbd86458484da8969bc85d20f28bec.png][]

Com\_delete:  删除次数  Com\_insert: 插入次数

Com\_select:  查询次数 Com\_update: 更新次数

我们可以在当前数据库再执行几次查询操作，然后再次查看执行频次，看看  Com\_select  参数会不会变化。

![c1dbb3bd404542ef9ec17d7c5c0d5305.png][]

> 通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？ 次数我们可以借助于慢查询日志。

接下来，我们就来介绍一下 MySQL 中的慢查询日志。

###  ###

### 2.**慢查询日志** ###

慢查询日志记录了所有执行时间超过指定参数（ long\_query\_time ，单位：秒，默认 10 秒）的所有

SQL 语句的日志。

MySQL 的慢查询日志默认没有开启，我们可以查看一下系统变量  slow\_query\_log 。

SHOW VARIABLES LIKE 'slow_query_log';

我的之前配置过，所以显示的是ON（off为关，on为开）:

![4fb5a97652b74d17ab648ea8d7b2003b.png][]

如果要开启慢查询日志，需要在 MySQL 的配置文件（C:\\ProgramData\\MySQL\\MySQL Server 8.0）中配置如下信息：

![8ce83282cfd3481198579183f7e538e4.png][]

# 开启MySQL慢日志查询开关
    slow_query_log=1
    # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
    long_query_time=2

![e8490c214e8f44b689a71bf9b2fbf60f.png][]

> 小知识：
> 
> **my.ini和my.cnf的区别**
> 
> MySQL的配置文件在windows系统为 my.ini  
> 而在linux或者MacOs是 my.cnf
> 
> linux或者MacOs配置文件所在位置
> 
>   
> 安装MySQL方式不同配置文件所在位置也不同，如果是安装版一般默认在  
> /usr/local/mysql-\[版本号\]/  
> 而免安装版一般都都在 MySQL安装目录的根路径下，如果找不到MySQL的安装目录，那么Linux和MacOS是可以通过命令搜索的，**一般会放在/etc/my.cnf，/etc/mysql/my.cnf。如果找不到，可以用find命令查找。**  
> 
> 
> **Windows的配置文件所在位置**  
> 无论安装版和免安装版，最好的办法是通过环境变量的方式查找，方式有了，我就不在这里赘述了。  
>

**慢查询日志：**

![741587b260284137b6fd99d5fdad18a8.png][]

**测试【linux版本】：**

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

/var/lib/mysql/localhost-slow.log 。

systemctl restart mysqld

![6a1917773e5e413590c686b1e49057a8.png][]

然后，再次查看开关情况，慢查询日志就已经打开了。

**A. 执行如下SQL语句 ：**

select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
    select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时
    13.35sec

![7ac7707aa47d42c8a94fe6ce74d5d95f.png][]

**B. 检查慢查询日志 ：**

最终我们发现，在慢查询日志中，只会记录执行时间超多我们预设时间（ 2s ）的 SQL ，执行较快的 SQL是不会记录的。

![4cff115ece1d46db9651231b6f6d78fe.png][]

那这样，通过慢查询日志，就可以定位出执行效率比较低的 SQL ，从而有针对性的进行优化。

### 3.**profile详情** ###

show profiles  能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过 have\_profiling

参数，能够看到当前 MySQL 是否支持 profile 操作：

SELECT @@have_profiling ;

![d2674b7975894ca7b9e13c4ece469ede.png][]

可以看到，当前MySQL是支持 profile操作的，如果开关是关闭的。

可以通过set语句在 session/global级别开启profiling：

SET profiling = 1;

SELECT @@profiling ;

![982abf822e0240eba44be304d5c5d4ab.png][]

开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：

select * from tb_user;
    select * from tb_user where id = 1;
    select * from tb_user where name = '白起';
    select count(*) from tb_user;

执行一系列的业务 SQL 的操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况
    show profiles;
    -- 查看指定query_id的SQL语句各个阶段的耗时情况
    show profile for query query_id;
    -- 查看指定query_id的SQL语句CPU的使用情况
    show profile cpu for query query_id;

**查看每一条SQL的耗时情况:**

![e061295f110f4762aecc32447d7e731e.png][]

**查看指定SQL各个阶段的耗时情况 :**

show profile for query 97;

![2ff673b8966a4172aa9f57efd05c1845.png][]

### 4.**explain** ###

EXPLAIN  或者  DESC 命令获取  MySQL  如何执行  SELECT  语句的信息，包括在  SELECT  语句执行过程中表如何连接和连接的顺序。

-- 直接在select语句之前加上关键字 explain / desc
    EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

![00c8a178945d422b9b864cf26353ebe9.png][]

Explain 执行计划中各个字段的含义:

![07ff752055b04d55a9e46d098f93a1d4.png][]

## 二、好书推荐 ##

![95d36c1494f04a4e87e80ca09471b318.png][]

清华社【秋日阅读企划】领券立享优惠

IT好书 5折叠加10元 无门槛优惠券：[https://u.jd.com/Yqsd9wj][https_u.jd.com_Yqsd9wj]

活动时间：9月4日-9月17日，先到先得，快快来抢

![fab8f69731ad4cfc814ebc27c427ba84.png][]

**迫不及待的小伙伴点击链接查看：**[Spring Cloud 微服务快速上手][Spring Cloud]

> 本次送书 2 本 评论区抽2位小伙伴送书  
> 活动时间：截止到 2023-09-17 20:00:00
> 
> 抽奖方式：利用程序进行抽奖。
> 
> 参与方式：关注博主、点赞、收藏，评论区评论 "人生苦短，我用Java！"

[58cbd86458484da8969bc85d20f28bec.png]: https://img-blog.csdnimg.cn/58cbd86458484da8969bc85d20f28bec.png
[c1dbb3bd404542ef9ec17d7c5c0d5305.png]: https://img-blog.csdnimg.cn/c1dbb3bd404542ef9ec17d7c5c0d5305.png
[4fb5a97652b74d17ab648ea8d7b2003b.png]: https://img-blog.csdnimg.cn/4fb5a97652b74d17ab648ea8d7b2003b.png
[8ce83282cfd3481198579183f7e538e4.png]: https://img-blog.csdnimg.cn/8ce83282cfd3481198579183f7e538e4.png
[e8490c214e8f44b689a71bf9b2fbf60f.png]: https://img-blog.csdnimg.cn/e8490c214e8f44b689a71bf9b2fbf60f.png
[741587b260284137b6fd99d5fdad18a8.png]: https://img-blog.csdnimg.cn/741587b260284137b6fd99d5fdad18a8.png
[6a1917773e5e413590c686b1e49057a8.png]: https://img-blog.csdnimg.cn/6a1917773e5e413590c686b1e49057a8.png
[7ac7707aa47d42c8a94fe6ce74d5d95f.png]: https://img-blog.csdnimg.cn/7ac7707aa47d42c8a94fe6ce74d5d95f.png
[4cff115ece1d46db9651231b6f6d78fe.png]: https://img-blog.csdnimg.cn/4cff115ece1d46db9651231b6f6d78fe.png
[d2674b7975894ca7b9e13c4ece469ede.png]: https://img-blog.csdnimg.cn/d2674b7975894ca7b9e13c4ece469ede.png
[982abf822e0240eba44be304d5c5d4ab.png]: https://img-blog.csdnimg.cn/982abf822e0240eba44be304d5c5d4ab.png
[e061295f110f4762aecc32447d7e731e.png]: https://img-blog.csdnimg.cn/e061295f110f4762aecc32447d7e731e.png
[2ff673b8966a4172aa9f57efd05c1845.png]: https://img-blog.csdnimg.cn/2ff673b8966a4172aa9f57efd05c1845.png
[00c8a178945d422b9b864cf26353ebe9.png]: https://img-blog.csdnimg.cn/00c8a178945d422b9b864cf26353ebe9.png
[07ff752055b04d55a9e46d098f93a1d4.png]: https://img-blog.csdnimg.cn/07ff752055b04d55a9e46d098f93a1d4.png
[95d36c1494f04a4e87e80ca09471b318.png]: https://img-blog.csdnimg.cn/95d36c1494f04a4e87e80ca09471b318.png
[https_u.jd.com_Yqsd9wj]: https://u.jd.com/Yqsd9wj
[fab8f69731ad4cfc814ebc27c427ba84.png]: https://img-blog.csdnimg.cn/fab8f69731ad4cfc814ebc27c427ba84.png
[Spring Cloud]: https://item.jd.com/13251159.html