注:本文是以windos mysql v8.0.25、Linux mysql v8.0.26 进行讲解
SQL性能分析主要优化的是select查询语句,在查询优化方面,索引占主导部分
即查看当前数据库是以插入为主、还是以删除为主、还是以更新为主、还是以查询为主;
如果查看后是以增删改为主,那么优化的比率就可以减轻了;但如果查看后是以查询为主,那么得需要大大优化
MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信
息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次
注意:
‘Com_______’;–注意:后面是7个字符,一个下划线代表一个字符;
主要看Com_delete:删除次数Com_insert: 插入次数;Com_select: 查询次数;Com_update: 更新次数这三个即可;
– session 是查看当前会话 ;
– global 是查询全局数据 ;
wps的官网下载的方法在哪里
3.1概念
现在假如我们已经知道是select查询用的过多,那么下一步需要再知道哪些select sql语句用到的更多,需要用到慢查询日志;
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有
SQL语句的日志;
3.2查看慢查询日志变量、文件、时间
MySQL的慢查询日志默认没有开启,我们可以查看一下系统变量 slow_query_log
3.3.开启慢查询日志
开启MySQL慢日志查询开关
3.4设置慢查询时间
设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
3.5查看慢查询日志文件
3.6使用慢查询日志
在select * from tb_user;查询后,发现日志文件仍然没有变化,原因是这条SQL执行效率比较高, 执行耗时 0.00secwps官网下载地方是多少
wps的的官网的下载的入口
select count(*) from tb_sku; – 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时
13.35sec
打开慢查询日志后,会发现日志文件已经发生变化
3.7检查慢查询日志
最终我们发现,在慢查询日志中,只会记录执行时间超多我们预设时间(2s)的SQL,执行较快的SQL是不会记录的;
那这样,通过慢查询日志,就可以定位出执行效率比较低的SQL,从而有针对性的进行优化
4.1profile
假如说现在有一些sql语句执行查询时间1.9秒、1.8秒、1.95秒,这类的sql是不会被记录在慢日志查询里的,因为低于2秒;
通过proflie是可以查看每一条sql语句它的耗时是多少,能够在做SQL优化时帮助我们了解耗时时间都耗费到哪里去
4.2查看是否支持profile
通过have_profiling参数,能够看到当前MySQL是否支持profile操作;
可以看到,当前MySQL是支持 profile操作的
4.3查看profile开关
当前MySQL是支持 profile操作的,但是开关是关闭的
4.4开启profiling
虽然开关是关闭的,可以通过set语句在session/global开启profiling:
4.5使用profile
4.5.1查看每条SQL耗时
4.5.2查看指定SQL各阶段耗时
starting:开始执行
ExecutingWPS office官网最新的下载地址是什么 hook on transaction :开启对应事务
checking permissions :检查权限
Opening tables :打开表
init :进行初始化的操作
optimizing :优化的操作
statistics :统计的操作
executing :执行的操作
4.5.3查看指定SQL各阶段CPU使用情况
5.1使用explainwps 的官网最新下载网址(wps office下载官网最新免费下载)
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序;
对于单表查询来说,explain后查看的执行select可能是一行
5.2explain字段含义
Explain 执行计划中各个字段的含义:
id:
select查询的序列号,表示查询中执行select子句或者是操作表的顺序
(id相同,执行顺序从上到下;id不同,值越大,越先执行)
注:对于单表查询来说,执行select可能是一行,但是对于多表查询来说select语句对应的执行计划可能是多条记录
select_type:
表示 当前sqlSELECT查询 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接
或者子查询)、PRIMARY(主查询,即外层的查询)、
UNION(UNION 中的第二个或者后面的查询语句)、
SUBQUERY(SELECT/WHERE之后包含了子查询)等
type:
表示连接类型,性能由好到差的连接类型为NULL、system、const、
eq_ref、ref、range、 index、all
NULL性能是最好的, all性能最差(all代表的是全表扫描),我们在优化的时候,尽量往NULL优化,但是在一般的业务中不太可能优化为NULL ,只有当查询语句不访问任何表的时候才会出现NULL;
访问系统表的时候才会出现system;
根据主键和唯一索引访问一般出现const,比如 select * from tb_user where id =1;;
如果使用非唯一性索引进行查询,会出现ref
index是指扫面整个索引树,也非常慢
possible_key:
显示可能应用在这张表上的索引,一个或多个
key:
实际使用的索引,如果为NULL,则没有使用索引。
key_len:
表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长
度,在不损失精确性的前提下, 长度越短越好 。
rows:
MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,
可能并不总是准确的。
filtered :
表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好
5.3id字段(id相同情况)
创建一个多对多表,一个学生对应多个课程;
一个课程供多个学生选择;
多对多需要中间有个中间表来维护
需求1:查询所有学生的选课情况
这需要设计三张表的联查,三张表的查询需要个条件来消除无效的笛卡尔积
我们发现这个id都为1,如果id相同,则代表表结构的顺序是从往下,也就代表着先执行的是s即student,然后是中间表,然后是course表;
原因是:student表与课程表没有直接关系,必须通过中间表连接起来
5.4id字段(id不同情况)
需求2:查询选修了查询MySQL课程的学生(通过子查询来实现):
方式1:
通过3个语句来查询
通过子查询来进行查询
id相同,执行顺序从上到下;id不同,值越大,越先执行:
*先执行id=3即course表:selECt id from course c where c.name =‘MySQL’);
*再执行id=2即student_course 表:selECt studentid from student_course sc where sc.courseid;
*再执行id=1即subquery:(selECt studentid from student_course sc where sc.courseid =(selECt id from course c where c.name =‘MySQL’));
*再执行id=1即 student表:select * from student s where s.id in
注意:subquery是指子查询的意思
5.5type
只有当查询语句不访问任何表的时候才会出现NULL
5.6possible_key、key、key_len、rows
我们根据name字段进行查询,可能用到的是name字段的索引;
实际要用到的也是name索引即key=name索引名字;
索引长度=202,这与name字段里存储的值相关