当前位置：首页 > news >正文

MySQL-SQL性能分析

news 2024/10/30 21:38:17

SQL执行频率

Mysql客户端连接成功后，通过 show [ session | global ] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 insert ，update ， delete，select 的访问频次。
show global status like 'Com_______'

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

#开启MySQL慢查询日志开关

slow_query_log = 1

#设置慢查询日志的时间为 2 秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志。

long_query_time = 2

查询慢查询日志的开关状态
show variables like 'slow_query_log'

修改/etc/my.cnf 【注意这里是linux下的mysql路径】

修改完成后，重启mysql,通过如下指令来查找慢日志存储的位置

show variables like 'slow_query_log_file';

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作
查询当前数据库是否支持profile操作
语句：select @@have_profiling;

查询当前profiling状态

select @@profiling;

修改profiling

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling
语句：set profiling = 1；

默认情况下，不加session/global ，是session级别

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

#查看每一条SQL的耗时基本情况
show profiles；

#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain执行计划

EXPLAIN 或者 DESC 命令获取MySQL如何执行SELECT 语句的信息，包含在SELECT语句执行过程中表如何连接和连接的顺序。
语法：
#直接在SELECT语句之前加上关键字 EXPLAIN / DESC
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

EXPLAIN执行计划各字段含义:

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下，id不同，值越大，越先执行）。
select_type：表示select的类型，常见的取值有：
- SIMPLE：简单表，既不使用表连接或者子查询
- PRIMARY：主查询，即外层的查询
- UNION：UNION中的第二个或后面的查询语句
- SUBQUERY：SELECT/WHERE 之后包含了子查询等
table：表名
partitions：5.6版本开始EXPLAIN新增字段，可以评估你查询时是否有效的利用了表的分区策略。
type：表示连接类型，性能由好到差的连接类型为NULL，system，const，eq_ref，ref，range，index，all。
- NULL：如果优化器可以确定不需要访问表来返回结果，就会使用这个值。例如：如果查询本身就是个常量。
- system：当表只有一行数据（系统表），并且该表是InnoDB存储引擎时可能出现，这实际上是非常罕见的。
- const：用于主键或唯一索引与常数比较的情况。在这种情况下，最多只会匹配一行，因此整个查询实际上是一个常量。
  - 案例：select * from user where id = 1;
- eq_ref：出现在对于每个前面的表的组合，从当前表中读取一行的时候。当所有使用的部分被定义为primary key 或 unique index 时，会用到这个类型。
  - 常用于多表连接且连接条件基于主键或唯一非空索引时。
- ref：类似于eq_ref，但不是所有使用的部分都被定义为主键或唯一索引。通常出现在使用非唯一索引的情况下：
  - 案例：select * from t1,t2 where t1.col1 = t2.col2;
- range：在使用索引选择行的范围时使用，比如BETWEEN，> ，<等操作符。
  - 案例：select * from user where index_col between xxx and xxx;
- index：全索引扫描，即遍历索引树以找到行。比全表扫描快，因为索引文件通常小于数据文件。
  - 适用于覆盖索引的情况，即查询的所有列都在同一个索引中。
- ALL：全表扫描，意味着必须从头到尾地读取整张表来找出符合搜索条件的行。
  - 这是最慢的访问方法之一，特别是在打表上。
possible_key：显示可能应用在这张表上的索引，一个或多个。
key：实际使用的索引，如果为NULL，则没有使用索引。
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
ref：表示索引查找使用了非唯一索引（也可以是唯一索引的非唯一前缀部分），即通过索引列的值与某个引用值进行比较来检索数据。
row：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。