电商网站设计多少钱,python网站开发演示,linux配置wordpress,如何找到网站管理员一、引言
数据库查询快慢是影响项目性能的一大因素#xff0c;对于数据库#xff0c;我们除了要优化SQL#xff0c;更重要的是得先找到需要优化的SQL语句。
性能优化的思路
首先需要使用慢查询功能#xff0c;去获取所有查询时间比较长的SQL语句其次使用explain命令去查…一、引言
数据库查询快慢是影响项目性能的一大因素对于数据库我们除了要优化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 profiling1;或者
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_functionSource_fileSource_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_logON;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_time1;show variables like %long_query_time%;2.3.3、开启慢查询日志永久
我们上述的的设置的开启慢查询的方式属于临时的如果重启MySQL的话会失效。
如果要永久开启慢查询日志需要在MySQL的配置文/etc/mysql/my.cnf中配置如下信息我们为了不影响当前的测试环境我们重新拉一台MySQl服务器作为演示 # 开启MySQL慢日志查询开关
slow_query_log1
# 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志
long_query_time2show 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.log2.3.5.2、慢查询日志分析工具mysqldumpslow
在生产环境中如果要手工分析日志查找、分析SQL显然是个体力活MySQL提供了日志分析工具mysqldumpslow 。 查看mysqldumpslow的帮助信息
mysqldumpslow --helpmysqldumpslow 命令的具体参数如下
-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 | more2.4、统计SQL的查询成本last_query_cost
如果我们想要查询 id9999024的记录然后看下查询成本我们可以直接在聚簇索引上进行查找
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
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表;由于1000w的数据量较大 , 如果直接加载1000w , 会非常耗费CPU及内存 ; 已经拆分为5个部分 , 每一个部分为
200w数据 , load 5次即可 ;文件上传至 /opt/mysql-data mkdir -p /opt/mysql-dataload 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?__bizMzkwOTczNzUxMQmid2247483991idx1snd556f73d4ee09487dc93b28764ef5148chksmc137691ff640e009a8798c481d12dd1ad8973008374e64a8152e2bf10b881f41bc4dda06a9e8#rd
