引言

MYSQL 可以查看一个 SQL 大概性能的命令往往都是使用 expalin , 该命令个人理解中该命令更偏向于给出 sql 在组织数据方面的操作情况, 从中大致可以看出使用什么索引(Index)的什么字段(keys), 大致扫描数据量(rows)和实际返回数据量占扫描数据量比(filter), 数据组织形式(extra)等等.

但是在 MYSQL 中, 一个SQL的执行是从优化器优化后, 到缓存, 加载索引, 扫描索引, 组织数据, 发送数据等直到返回结果, 如果仅纠结于explain解决问题有时候会出现sql走了索引情况下, 在程序从发出sql请求到接受结果这个过程中, 依然很慢的诡异情况。

实际的业务场景中, 可能会遇到更诡异的情况就是同样的SQL在不同时间不同流量下的执行情况不同导致的数据库压力过高, 但是由于时效性的问题, 始终无法还原场景。

所以需要一种可以反映出 SQL 在执行过程中的不同阶段的消耗情况, 以求可以更准确快速定位问题。

show profiles

使用 show profiles 查询是需要有前后执行顺序的，先给结果，以下是整套操作顺序：

 show variables like '%profil%';
 show PROFILES ;
 show profile all for query 241;

是否开启 PROFILING

MYSQL 提供的查询执行命令的情况的命令，命令执行本身需要确认 profiling 参数是否开启, 只有开启后才可以使用该命令查看最晚执行的若干条sql记录，开启命令（SET profiling = 1）查询 profiling 是否开启命令如下：

show variables like '%profil%';

其中：profiling 代表是否开启，profiling_history_size 代表记录最新的15条sql情况

查询具体SQL执行参数

由于是记录最新的15条sql，所以想要准确找到自己想要查询的 SQL，最好是先执行SQL后再执行命令 ，具体情况如下：

 select count(*) from comment_info;
 show PROFILES;

此时可以看到收集到了对应的SQL,此时可以看到实际消耗的时间以及查询id（QUERY_ID），可以执行以下命令查询对应查询id的具体执行情况，如下：

 show profile all for query 537;
 ## 命令中的all可以替换成自己适合参数,可选参数有all, cpu等

其中，可以从 Status 查看具体的操作流程，不同的 SQL 自然存在不同的流程，在一些比较慢的 SQL 里面自然会存在一些明显花费时间比较长的流程，部分比较耗时的流程如下：

 System lock
     确认是由于哪个锁引起的, 通常是因为MySQL或InnoDB内核级的锁引起的。建议：如果耗时较大再关注即可, 一般情况下都还好
 ​
 Sending data
     解释：从server端发送数据到客户端, 也有可能是接收存储引擎层返回的数据, 再发送给客户端, 数据量很大时尤其经常能看见。
     备注：Sending Data不是网络发送, 是从硬盘读取, 发送到网络是Writing to net。
     建议：通过索引或加上LIMIT, 减少需要扫描并且发送给客户端的数据量
 ​
 Sorting result
     正在对结果进行排序, 类似Creating sort index, 不过是正常表, 而不是在内存表中进行排序。
     建议：创建适当的索引
 ​
 Table lock
     表级锁, 要么是因为MyISAM引擎表级锁, 要么是其他情况显式锁表
 ​
 create sort index
     当前的SELECT中需要用到临时表在进行ORDER BY排序
     建议：创建适当的索引
 ​
 Creating tmp table
     创建临时表。先拷贝数据到临时表, 用完后再删除临时表。消耗内存, 数据来回拷贝删除, 消耗时间
     建议：优化索引
 ​
 converting HEAP to MyISAM
     查询结果太大, 内存不够, 数据往磁盘上搬了。
     建议：优化索引, 可以调整 max_heap_table_size 
 ​
 Copying to tmp table on disk
     把内存中临时表复制到磁盘上, 危险！！！
     建议：优化索引, 可以调整 tmp_table_siz 参数, 增大内存临时表大小

至此，从研发层面来说，基本上可以定位到一个sql在mysql里面最耗时的位置了，可以看下是因为 MySQL 内部参数导致临时表或读取索引次数过多导致的性能问题，还是因为参数导致临时表大小问题。这将益于针对性的提出解决方法。

扩展

MYSQL 优化参数有哪些？

 ##innodb_buffer_pool_size   innodb数据库的内存缓存, 主要缓存数据块和索引块
     SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; ## 查看当前数据库的 innodb_buffer_pool_size
     SET GLOBAL innodb_buffer_pool_size = 1073741824; ##命令修改缓存的大小, 但是命令修改只是暂时的, 重启后会丢失且重启可能导致数据的错误所以一般使用配置文件修改
     [mysqld]  
     innodb_buffer_pool_size = 1G  # 或者其他你希望的值，例如 2G, 4G 等
 ​
 ##innodb_max_dirty_pages_pct        脏页占总比, 缓存页超过这个阈值就会刷新到磁盘
     SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct'; ##查看
     SET GLOBAL innodb_max_dirty_pages_pct = 75;    ##临时设置
     ##永久设置, 编辑你的 MySQL 配置文件（通常是 my.cnf 在 Linux/Unix 系统中，或者 my.ini 在 Windows 系统中），然后在 [mysqld] 部分添加或修改以下行
     [mysqld] 
     innodb_max_dirty_pages_pct = 75
 ​
 ##innodb_io_capacity                一定程度内代表磁盘刷新的io能力,设置为100代表数据能力，代表每秒磁盘完成io的次数100次，一些读写能力低的可以设置低一点，高的可以设置高一点   
     SHOW VARIABLES LIKE 'innodb_io_capacity'; ##查看
     SET GLOBAL innodb_io_capacity = 2000;  ##临时设置
     ##用具设置
     [mysqld]  
     innodb_io_capacity = 2000
 ​
 ## tmp_table_size 
     SHOW VARIABLES LIKE 'tmp_table_size'; ##查看
     SET SESSION tmp_table_size = <size>;  ## 使用set session设置会话级别的命令，会话结束后，修改结束
     ## 永久配置
     [mysqld]  
      tmp_table_size = <size>  
     
 ​
 ## max_heap_table_size
     SHOW VARIABLES LIKE 'max_heap_table_size'; ##查看
     SET SESSION max_heap_table_size = <size>; ##临时配置
     ##永久配置
     [mysqld]  
      max_heap_table_size = <size>

show processList

用于查看 MySQL 当前执行中的线程。使用之前需要先看当前账号是否有权限且是否开启收集日志，一般情况下，都是使用的root权限的账号查看，该方法用于解决死锁，执行时间长等紧急情况命令时候，有着很大的帮助作用。

 show full PROCESSLIST; ##显示所有的执行中线程
 kill id; ## 强制杀死某个id的线程

其中从 command 可以看到线程的状态， time代表执行的时间，正常情况下time不会太长。

 System lock：等待获取一个内部锁。
 Query：正在执行查询。
 Sleep：线程正在等待客户端发送新的查询。
 Locked：线程正在等待表锁。这通常发生在 MyISAM 存储引擎中，因为 MyISAM 支持表级锁定。
 Sending data：线程正在处理 SELECT 查询的结果集，并将数据发送给客户端。
 System：线程正在等待系统调用完成。
 User lock：线程正在等待用户锁（例如，GET_LOCK()）。
 Freeing items：线程正在清理使用过的项目，并执行释放内存的操作。
 Waiting for tables：线程正在等待表被解锁，以便它可以继续操作。
 Waiting for table flush：线程正在等待表刷新操作完成。
 Opening tables：线程正在尝试打开一个表。
 System sleep：线程正在等待某个资源变得可用。
 Closing tables：线程正在关闭表。
 Killed：线程已被杀死，但清理过程仍在进行。
 Query end：查询已经结束，但清理过程仍在进行。
 Delayed insert：线程正在等待 INSERT DELAYED 队列中的表被插入。
 Logging slow query：线程正在记录一个慢查询。
 Analyzing or optimizing：线程正在分析或优化表。
 Waiting for table metadata lock：线程正在等待元数据锁。
 Waiting for global read lock：线程正在等待全局读锁。
 Waiting for committed transaction to be flushed：对于二进制日志，线程正在等待提交的事务被刷新到磁盘。