MySQL调优之性能分析

目录

1.SQL执行频率

2.SQL性能分析

慢查询日志

profile详情 

开启profile

使用profile

explain

---

1.SQL执行频率

MySQL客户端连接成功后，通过 show[session|global] status 命令可以提供服务器状态信息。

global查看全局的状态信息，session查看当前会话的状态信息

通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

SQL语句：SHOW GLOBAL STATUS LIKE 'Com_______';

效果图：

注意：'Com_______'中含7个下划线，29代表进行了29次select查询。

2.SQL性能分析

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认为10秒）的所有SQL语句的日志。

查询是否开启慢查询日志： SQL指令：show variables like 'slow_query_log';

 ON表示开启状态，如何开启呢？

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件my.ini（Linux是my.cnf）中配置如下信息：

  #开启MySQL慢日志查询开关

   show_query_log=1

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

    long_query_time=2

 配置完毕后，需要重启MySQL服务器，查看慢日志文件中的记录的信息

如何实时监测慢查询日志？

 my.ini中标明了慢查询日志生成文件的名称

 

windows系统下，进入该文件目录并打开cmd 输入如下指令：tail -f  ‪xxx.log

 在sql命令窗口输入sql指令 如：select count(*) from tableName；

如果该查询耗时超过2秒，则会在cmd中监测到信息：

 包括执行时间、主机、端口、查询时间、使用的数据库、实行的sql语句等信息

profile详情 

    通过前面的学习我们会发现，通过慢查询日志检测到的数据都是执行时间超过long_query_time的sql语句。那么当执行时间为0.8、0.9s，它的性能同样也不算好时，我们怎么办呢？这时就可以用profile了。

profile作用：可以帮助我们查看每条sql指令的运行时间

开启profile

查看当前MySQL是否支持profile：select @@have_profiling;

查询是否开启了profile：select @@profiling;

 1代表开启；0代表关闭。

 开启profile：set profiling = 1;

使用profile

 #查询每一条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后加sql语句

 如 explain select * from user;

EXPLAIN各字段含义：

 id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺  序从上到下；id不同，值越大，越先执行）
 select_type：表示select的类型
 type：表示连接类型，性能由好到差的连接类型为：NULL、system、const、eq_ref、ref、         range、index、all。
 possible_key:显示可能应用在这张表上的索引，一个或多个
 key：实际用到的索引，没有则为NULL
 key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度
 rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可并不总是准确的（该属性可仅作参考）
 filtered：表示返回结果的行数占需读取行数的百分比，他的值越大越好