MySQL索引调优实际操作(二)

Posted by 好记性不如烂笔头 on 04-09,2020

前言

说完explain关键字后,再来说说另一种检查sql执行情况的方法。


show profile

profile是mysql提供用来分析当前回话中语句执行的资源消耗情况。可以对单独的sql语句进行诊断,结果相当于一份化验单。

打开profile功能

profile功能默认是关闭的,登录mysql后输入show variables like 'profiling'即可查看

默认关闭的profiling

使用命令set profiling=on打开该功能(该功能一般在测试环境打开)

打开后的profiling

再重启以下mysql服务service mysql restart

使用profile功能

输入show profiles即可查看历史sql的执行情况

历史sql的执行情况

Query_ID字段是sql的标识id,Duration字段是执行时间,Query字段是sql语句。从每条sql的执行时间可以初步筛选出慢sql,一般可以定一个标准,比如说2秒,只要超过这个值就视为慢sql语句。

我们可以使用show profile (字段) for query (ID号)来进一步分析sql语句的执行情况。其中字段可选项有:

字段含义
ALL显示所有开销信息
BLOCK IO显示块io相关开销
CONTENT SWITCHES上下文切换相关开销
CPUCPU开销
IPC发送和接受相关开销
MEMORY内存开销
PAGE FAULTS页面错误开销
SWAP交换次数相关开销

其中最常用的就是CPU和BLOCK IO这两个参数。

比如输入show profile CPU, BLOCK IO for query 7

分析一条sql

Status字段表示出sql执行的阶段,后面有每个阶段的执行时间,十分详细。

注意,当Status出现下列信息时,表示sql语句有某些问题:

  1. converting HEAP to MyISAM :查询结果太大,内存不够了,往磁盘上搬运了
  2. creatin tmp table :创建临时表,会拖慢执行时间
  3. copying to tmp table on disk : 把内存中临时表复制到磁盘,危险!!
  4. locked : 锁住