点击蓝色“潭时录”关注我丫
每天和小潭一起快乐的学习~
你好，我是在学mysql的小潭。在上一期08-mysql高级篇-千万级数据批量插入脚本推文中我们学习了向数据库批量插入千万级别数据的方法。今天，我们将继续对查询截取分析中的Show Profile分析工具做一下认识。
对于sql的优化和分析，一般顺序是先在开发环境做问题重现再做问题的分析和解决。在先前的文章中我们学习了使用慢查询日志以及explain工具对慢SQL进行分析的方法，而更细粒度的分析则需要用到Show Profile工具。
一、是什么
Show Profile工具是MySQL提供用来分析当前会话中语句执行的资源(链接-服务-引擎-存储)消耗情况。可以用于SQL的调优测量。
对应资料：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
默认情况下，该参数处于关闭状态，并保存最近15次的运行结果。
二、怎么用
Show Profile工具的使用步骤可分为以下6步。
1.查看当前MySQL版本是否支持
show variables like 'profiling';或show variables like 'profiling%';
2.开启功能
默认是关闭，使用前需要开启。
set profiling=on;
3.运行需要分析的SQL
使用上一期推文中建立的数据库和表进行示例：
select * from emp group by id%10 limit 15000;select * from emp group by id%20 order by 5;
说明：
示例SQL运行时可能会出现如下报错
原因：MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。也就是说对于GROUP BY的聚合操作，如果select中的列没有在GROUP BY中出现，那么该SQL不合法，在设置ONLY_FULL_GROUP_BY该参数时，必须使用MAX()，SUM()，ANT_VALUE()这种聚合函数才能完成GROUP BY的聚合操作。
我们可通过以下方式修改sql_mode参数来解决此报错。
方式1：会话配置
a. select @@global.sql_modeb. 去除其中的ONLY_FULL_GROUP_BY参数重新设置sql_modec. 重新连接mysql
方式2：配置文件
a. vim /etc/mysql目录/my.cnfb. 在mysqld下添加如下内容sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONc. 重启mysql服务service mysql restartd. 重新连接mysql
使用方式1配置sql_mode后的示例SQL运行结果：
4.查看分析结果
show profiles;
5.诊断SQL
show profile cpu,block io for query 上一步骤中问题SQL的数字号码;type参数说明：ALL :显示所有的开销信息BLOCK IO :显示块IO相关的开销CONTEXT SWITCHES :上下文切换相关开销CPU :显示CPU相关开销信息IPC :显示发送和接收相关的开销信息MEMORY :显示内存相关的开销信息PAGE FAULTS :显示页面错误相关的开销信息SOURCE :显示和Source_function, Source_file, Source_line相关的开销信息SWAPS :显示交换次数相关的开销信息
6.日常开发需要注意或检查的点(耗时过程)
1) converting HEAP to MyISAM 查询结果太长，内存不足会往磁盘上搬2) Creating tmp table 创建临时表a.拷贝数据到临时表b.用完再删除3) Copying to tmp table on disk 把内存中的临时表复制到磁盘(危险动作)4) locked
补充
最后我们再补充一个SQL分析的辅助工具，即全局查询日志。该功能仅在测试环境辅助我们对SQL进行定量分析(开启后会记录所有sql，从而有助于我们对记录的日志进行分时段的分析)，但永远不要在生产环境开启这个功能！
方式1.启用配置
在mysql的配置文件my.cnf中设置：# 开启general_log=1# 记录日志文件的路径general_log_file=/path/logfile# 输出格式log_output=FILE
方式2.编码启用
set global general_log=1;set global log_output='TABLE'; # 以表的形式记录之后我们所编写的SQL语句将会被记录到MySQL库里的general_log表中查看命令：select * from mysql.general_log;
下期预告：MySQL锁机制
