织梦网站地图模板下载/北京培训机构

一、引言

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化SQL，更重要的是得先找到需要优化的SQL语句。

性能优化的思路

1. 首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句
2. 其次使用explain命令去查询由问题的SQL的执行计划
3. 最后可以使用show profile[s] 查看由问题的SQL的性能使用情况
4. 优化SQL语句

二、慢查询分析

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化SQL，更重要的是得先找到需要优化的SQL语句。

2.1、SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

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

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

SHOW GLOBAL STATUS LIKE 'Com_______';

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

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

2.2、查看 SQL 执行成本：SHOW PROFILE

show profiles 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量,show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

select @@have_profiling;

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：

select @@profiling;

或者

show variables like 'profiling';

set profiling=1;

或者：

set profiling = 'ON';

通过profile，我们能够更清楚地了解SQL执行的过程。

首先，我们可以执行一系列的操作，如下所示：

show databases;

select * from tb_seller;

select count(*) from tb_seller;

select count(0) from tb_sku;

执行完上述命令之后，执行show profiles 指令， 来查看SQL语句执行的耗时：

show profiles;

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

show profile for query  5;

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults

等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

show profile cpu for query  5;

show profile的常用查询参数：

• ① ALL：显示所有的开销信息。
• ② BLOCK IO：显示块IO开销。
• ③ CONTEXT SWITCHES：上下文切换开销。
• ④ CPU：显示CPU开销信息。
• ⑤ IPC：显示发送和接收开销信息。
• ⑥ MEMORY：显示内存开销信息。
• ⑦ PAGE FAULTS：显示页面错误开销信息。
• ⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
• ⑨ SWAPS：显示交换次数开销信息。

2.3、定位执行慢的 SQL：慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。

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

show variables like 'slow_query_log';

2.3.1 开启慢查询日志

查看下慢查询日志是否开启，以及慢查询日志文件的位置：

show variables like '%slow_query_log%';

set global slow_query_log='ON';

show variables like '%slow_query_log%';

2.3.2、修改long_query_time阈值

show variables like '%long_query_time%';

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

• 测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句

set global long_query_time = 1;

set long_query_time=1;

show variables like '%long_query_time%';

2.3.3、开启慢查询日志（永久）

我们上述的的设置的开启慢查询的方式属于临时的，如果重启MySQL的话会失效。

如果要永久开启慢查询日志，需要在MySQL的配置文（/etc/mysql/my.cnf）中配置如下信息，我们为了不影响当前的测试环境，我们重新拉一台MySQl服务器作为演示：

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

show variables like '%slow_query_log%';

show variables like '%long_query_time%';

2.3.4、案例的演示

select * from tb_sku where id = '100000030074'\G

由于该语句执行时间很短，为0s ， 所以不会记录在慢查询日志中。

select * from tb_sku where name like '%HuaWei手机Meta87384 Pro%';

该SQL语句 ， 执行时长为 14.30s ，超过10s ， 所以会记录在慢查询日志文件中。

查询当前系统中有多少条慢查询记录:

show status like 'slow_queries';

2.3.5、看慢查询日志文件

2.3.5.1、直接通过cat

cat  /var/lib/mysql/b3d475fcbe56-slow.log

2.3.5.2、慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

1. -a: 不将数字抽象成N，字符串抽象成S
2. -s: 是表示按照何种方式排序：
   • c: 访问次数
   • l: 锁定时间
   • r: 返回记录
   • t: 查询时间
   • al:平均锁定时间
   • ar:平均返回记录数
   • at:平均查询时间 （默认方式）
   • ac:平均查询次数
3. -t: 即为返回前面多少条的数据；
4. -g: 后边搭配一个正则匹配模式，大小写不敏感的；

举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

mysqldumpslow -s t -t 5 /var/lib/mysql/b3d475fcbe56-slow.log

常用参考：

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

2.4、统计SQL的查询成本：last_query_cost

如果我们想要查询 id=9999024的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

select * from tb_sku where id =9999024;

运行结果（1 条记录，运行时间为 0.01s ）

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

SHOW STATUS LIKE 'last_query_cost';

如果我们想要查询 id 在 9999024到 9999730之间的记录呢？

select * from tb_sku where id between 9999024 and 9999730;

运行结果（707 条记录，运行时间为 0.01s ）：

然后再看下查询优化器的成本，这时我们大概需要进行 270 个页的查询。

总结：

能看到页的数量是刚才的 270 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

三、开发环境准备

https://github.com/shixiaochuangjob/markdownfile/tree/main/20240816

准备tb_sku表, 导入数据 - 数据1000w

CREATE DATABASE test  CHARACTER SET utf8mb4   COLLATE utf8mb4_general_ci;

use test;

CREATE TABLE `tb_sku` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',`sn` varchar(100) NOT NULL COMMENT '商品条码',`name` varchar(200) NOT NULL COMMENT 'SKU名称',`price` int(20) NOT NULL COMMENT '价格（分）',`num` int(10) NOT NULL COMMENT '库存数量',`alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量',`image` varchar(200) DEFAULT NULL COMMENT '商品图片',`images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',`weight` int(11) DEFAULT NULL COMMENT '重量（克）',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`update_time` datetime DEFAULT NULL COMMENT '更新时间',`category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',`brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',`spec` varchar(200) DEFAULT NULL COMMENT '规格',`sale_num` int(11) DEFAULT '0' COMMENT '销量',`comment_num` int(11) DEFAULT '0' COMMENT '评论数',`status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

由于1000w的数据量较大 , 如果直接加载1000w , 会非常耗费CPU及内存 ; 已经拆分为5个部分 , 每一个部分为

200w数据 , load 5次即可 ;文件上传至 /opt/mysql-data

mkdir  -p  /opt/mysql-data

load data local infile '/opt/mysql-data/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

load data local infile '/opt/mysql-data/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

load data local infile '/opt/mysql-data/tb_sku3.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

load data local infile '/opt/mysql-data/tb_sku4.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

load data local infile '/opt/mysql-data/tb_sku5.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

select count(0) from tb_sku;

https://mp.weixin.qq.com/s?__biz=MzkwOTczNzUxMQ==&mid=2247483991&idx=1&sn=d556f73d4ee09487dc93b28764ef5148&chksm=c137691ff640e009a8798c481d12dd1ad8973008374e64a8152e2bf10b881f41bc4dda06a9e8#rd