一、引言
数据库查询快慢是影响项目性能的一大因素,对于数据库,我们除了要优化SQL,更重要的是得先找到需要优化的SQL语句。
性能优化的思路
- 首先需要使用慢查询功能,去获取所有查询时间比较长的SQL语句
- 其次使用explain命令去查询由问题的SQL的执行计划
- 最后可以使用show profile[s] 查看由问题的SQL的性能使用情况
- 优化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 命令的具体参数如下:
- -a: 不将数字抽象成N,字符串抽象成S
- -s: 是表示按照何种方式排序:
- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间 (默认方式)
- ac:平均查询次数
- -t: 即为返回前面多少条的数据;
- -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