SQL性能分析

SQL性能分析

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

#查看慢查询日志参数
show variables like 'slow_query_log';
#修改MYSQL的配置信息（linux系统）
vi /etc/my.cnf

#添加一下配置信息
#开启MYSQL慢查询的日志开关
slow_query_log = 1
#设置慢查询日志的时间为2秒，SQL语句的执行时间超过2秒时视为慢查询语句，记录慢查询日志。
login_query_time = 2

##配置完成后重启数据库
service  mysql restart
systemctl restart mysql

此时在mysql目录下（datadir的配置目录下）会生成
localhost-slow.log 文件

cat localhost-slow.log 查看SQL日志

tail -f localhost-slow.log 实时查看SQL日志的尾部

• SQL的执行频率
  MYSQL客户端连接成功之后可以通过 show[ session|global ] status 命令查看服务器的状态信息。

#查看服务器 insert，update，delete，select的访问次数
show global status like 'com_______';
#七个下划线，代表七个字符

• profile详情
  show profiles 能够在做SQL优化时帮助我们了解时间的耗费。
  可以通过 have_profiling 参数。
  需要查看MYSQL版本是否支持
  profile操作

SELECT @@have_profiling ;
##默认profiling是关闭的，可以通过set语句在session/gloal级别开启profiling:
SET profiling =1 ;
##可以通过语句查询是否开启
SELECT @@@profiling;
1 是开启
2 是关闭

查看每一条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 语句执行的过程中表是如何连接以及连接的顺序。
语法：

#直接在sql语句之前加上关键字 explain/desc
explain select 字段名 from 表名 where 条件;

explain执行计划各个字段的含义

id
  select 查询的序列号，表示查询中执行select子句或者操作表的顺序
  id相同，执行顺序从上到下,
  id不相同，值越大，越先执行。

select_type
表示select 的类型，常见的取值有
SLMPLE(简单表，即不适用表连接或者子查询)
PRIMARY (主查询，即外层的查询)
UNION(UNION中的第二个或者后面的查询语句)
SUBQUERY(select/where 之后包含的子查询)

type（重要）
表示连接类型，性能由好到差的连接类型为
NULL 、system 、const、 eq_ref、ref 、range、index、all。
唯一索引会出现 const
非唯一索引出现 ref
index 有索引，但是遍历所有的索引

possible_key
显示可能用到的这张表上的索引，一个或者多个。

key
实际使用的索引，如果为 null ，则没有使用索引

filtered
表示返回结果行数占需要读取行数的百分比，filtered 的值越大越好。