Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具,可用于sql调优的测量。默认情况下处于关闭状态,并保存最近15次的运行结果。 通过show profiles查看sql语句的耗时时间,然后通过show profile命令对耗时时间长的sql语句进行诊断 。注意show profile诊断结果中出现相关字段的含义,判断是否需要优化sql语句
SQL批量插入
1、建库建表
1 | CREATE TABLE dept( |
2、设置参数
创建函数,假如报错: This function has none of DETERMINIST…, 由于开启过慢查询日志,因为我们开启了bin-log,我们就必须为我们的function指定一个参数。
但是这样设置会导致的问题是:如果MySQL重启,上述参数又会丢失,所以到达到永久配置的效果,需要修改配置文件,在/etc/my.cnf[mysqld]
下加上 global log_bin_trust_function_creators=1;
3、创建函数,保证数据的随机性
随机产生字符串
1 | DELIMITER $$ |
随机产生数字编号
1 | DELIMITER $$ |
4、创建存储过程
向emp表存储数据的存储过程
1 | /* 建立存储过程(插入数据emp)*/ |
向dept表存储数据的存储过程
1 | /* 向dept表存储数据的存储过程 */ |
5、调用存储过程
由于定义函数的时候是以$$
这个符号作为结束符,现在要更换为普通语句:
1 | DELIMITER; |
下面开始调用:
1 | CALL insert_dept(100, 10); |
试试向emp表添加50万条数据:
1 | CALL insert_emp(100001, 500000); |
哈哈,虚拟机还可以,OK 妥妥的50万条数据!
Show profiles
1、Show profiles是什么
Show profiles是什么:是mysql提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优测量
这就好比去超市买东西,买什么东西花了多少钱都是有明确的记录的,Show profiles也是一样,记录SQL执行步骤耗时,每一步都做了记录。默认情况下,参数处于关闭状态,并保存最近15次的运行结果
2、Show profiles分析步骤
① 查看当前版本是否支持
② 开启功能,默认是关闭,使用前需要开启
1 | show variables like 'profiling%' |
把之前的测试数据放过来
1 | create table tbl_dept( |
然后开启Show profile,进行了几条查询再show profile
下面开始选取一条开始分析。
3、分析执行过程
现在假设分析的是语句3 ,也就是select * from tbl_dept:
从上面可以看出,通过show profile 的分析,完整的呈现了一条SQL执行的全流程,配合着MySQL架构模型,其实很容易看出,先进行权限检查,打开表,初始化,优化器优化等等一系列的执行流程…
只能查看CPU和IO吗?当然不是,下面给出了常用的查询字段:
①ALL:显示所有的开销信息。
②BLOCK IO:显示块IO开销。
③CONTEXT SWITCHES:上下文切换开销。
④CPU:显示CPU开销信息。
⑤IPC:显示发送和接收开销信息。
⑥MEMORY:显示内存开销信息。
⑦PAGE FAULTS:显示页面错误开销信息。
⑧SOURCE:显示和Source_function,Source_file,Source_line相关的开销信息。
⑨SWAPS:显示交换次数开销信息
4、日常开发需要注意的结论
Status里面出现的字段:
① converting HEAP to MyISAM 查询结果太大,内存都不够用了往磁盘上搬了。
② creating tmp table 创建临时表:说说创建临时表为什么这么费事呢?首先需要新建临时表,然后需要拷贝数据到临时表,数据推送后需要删除数据,这也就是为什么创建临时表非常损耗性能的原因
③ copying to tmp table on disk 把内存中临时表复制到磁盘,危险!!!这说明临时表都存不下了,只能往磁盘丢
④ locked 锁定了
如果在show profile诊断结果中出现了以上4条结果中的任何一条,则sql语句需要优化。
下面看看临时表的处理过程:
可以看出,拷贝数据到临时表是非常消耗时间的!
- 本文作者: Tim
- 本文链接: https://zouchanglin.cn/2019/10/23/1560112675.html
- 版权声明: 本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 许可协议。转载请注明出处!