文档章节

开发人员MySQL调优-实战篇3-profile日志和锁

特拉仔
 特拉仔
发布于 2018/06/14 09:41
字数 3261
阅读 84
收藏 5

profile日志分析

​ 通过使用explain命令查看执行计划,并对SQL调优后,如果还想对SQL执行过程更详细的了解,查找慢更底层的原因,可以使用profile分析。

打开日志记录

先查看profile配置

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set

在当前会话中打开profile配置

mysql> set profiling=on;
Query OK, 0 rows affected

执行几个SQL

select count(1) from tb_v_user;
select count(1) from tb_v_user;
select user_id,count(1) from tb_vote group by user_id;

看profile信息

mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 |  0.2433095 | select count(1) from tb_v_user                        |
|        2 | 0.00085075 | unlock tables                                         |
|        3 |   0.114828 | select count(1) from tb_v_user                        |
|        4 |  0.1181025 | select count(1) from tb_v_user                        |
|        5 | 0.11777725 | select count(1) from tb_v_user                        |
|        6 |   4.482654 | select user_id,count(1) from tb_vote group by user_id |
+----------+------------+-------------------------------------------------------+
6 rows in set

Query_ID:收集到的执行SQL的序列号,后面指定要分析那一条SQL时候需要用到这个值

Duration:执行SQL耗时

Query:SQL

分析日志记录

先使用如下命令参数。不知道为什么这个命令在navicat中不能识别,我是登录到虚拟机中打开mysql客户端执行的

mysql> ? show profile
Name: 'SHOW PROFILE'
Description:
Syntax:
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]
​
type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS

type列出来了很多,但我一般就看看CPU和BLOCK IO,如果通过这两项看不出什么问题,可以再挨个查看一下

如果你想看所有的信息,那么可以这样写

show profile all for query 1

查看上面日志收集到的第一条sql的所有执行过程信息(步骤、耗时),返回的列会非常多,我就不贴出来了。

查看一下执行慢的SQL(ID=6)比如:

show profile CPU,BLOCK IO for query 6
mysql> show profile CPU,BLOCK IO for query 6;
+---------------------------+----------+----------+------------+--------------+---------------+
| Status                    | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+---------------------------+----------+----------+------------+--------------+---------------+
| starting                  | 0.000101 | NULL     | NULL       | NULL         | NULL          |
| checking permissions      | 6E-6     | NULL     | NULL       | NULL         | NULL          |
| Opening tables            | 0.004756 | NULL     | NULL       | NULL         | NULL          |
| init                      | 3.9E-5   | NULL     | NULL       | NULL         | NULL          |
| System lock               | 7E-6     | NULL     | NULL       | NULL         | NULL          |
| optimizing                | 3E-6     | NULL     | NULL       | NULL         | NULL          |
| statistics                | 4.7E-5   | NULL     | NULL       | NULL         | NULL          |
| preparing                 | 2.6E-5   | NULL     | NULL       | NULL         | NULL          |
| Creating tmp table        | 6.2E-5   | NULL     | NULL       | NULL         | NULL          |
| Sorting result            | 3E-6     | NULL     | NULL       | NULL         | NULL          |
| executing                 | 1E-6     | NULL     | NULL       | NULL         | NULL          |
| Sending data              | 0.797651 | NULL     | NULL       | NULL         | NULL          |
| converting HEAP to MyISAM | 0.762739 | NULL     | NULL       | NULL         | NULL          |
| Sending data              | 1.574018 | NULL     | NULL       | NULL         | NULL          |
| Creating sort index       | 1.333006 | NULL     | NULL       | NULL         | NULL          |
| end                       | 7E-6     | NULL     | NULL       | NULL         | NULL          |
| removing tmp table        | 0.009357 | NULL     | NULL       | NULL         | NULL          |
| end                       | 8E-6     | NULL     | NULL       | NULL         | NULL          |
| query end                 | 6E-6     | NULL     | NULL       | NULL         | NULL          |
| closing tables            | 1E-5     | NULL     | NULL       | NULL         | NULL          |
| freeing items             | 0.000784 | NULL     | NULL       | NULL         | NULL          |
| cleaning up               | 1.9E-5   | NULL     | NULL       | NULL         | NULL          |
+---------------------------+----------+----------+------------+--------------+---------------+
22 rows in set

下图是它的执行计划(说明一下:这个SQL只是为了测试慢,实际情况下除非脑袋短路才会对unique的列做group by )

执行计划告诉我:使用了临时表和文件排序

那么对于上面的profile都是些是什么喃?他们就是下面截取这一段

| Creating tmp table        | 6.2E-5   | NULL     | NULL       | NULL         | NULL          |
| Sorting result            | 3E-6     | NULL     | NULL       | NULL         | NULL          |
| executing                 | 1E-6     | NULL     | NULL       | NULL         | NULL          |
| Sending data              | 0.797651 | NULL     | NULL       | NULL         | NULL          |
| converting HEAP to MyISAM | 0.762739 | NULL     | NULL       | NULL         | NULL          |
| Sending data              | 1.574018 | NULL     | NULL       | NULL         | NULL          |
| Creating sort index       | 1.333006 | NULL     | NULL       | NULL         | NULL          |
| end                       | 7E-6     | NULL     | NULL       | NULL         | NULL          |
| removing tmp table        | 0.009357 | NULL     | NULL       | NULL         | NULL          |

尝试着对这不合常规的SQL做优化

create index idx_tb_vote_user_id on tb_vote(user_id);
mysql> explain select user_id,count(1) from tb_vote group by user_id;
+----+-------------+---------+-------+---------------------+---------------------+---------+------+--------+-------------+
| id | select_type | table   | type  | possible_keys       | key                 | key_len | ref  | rows   | Extra       |
+----+-------------+---------+-------+---------------------+---------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | tb_vote | index | idx_tb_vote_user_id | idx_tb_vote_user_id | 62      | NULL | 398784 | Using index |
+----+-------------+---------+-------+---------------------+---------------------+---------+------+--------+-------------+
1 row in set

没有再使用临时表和文件排序,虽然依然取了398784行,但取的是索引的,而不是表的,IO次数会明显减小,特别是那种列非常多的字段效果更明显

表级锁

先看看lock命令的语法,在主机后台登录mysql

[hadoop@hadoop00 /home/hadoop]$ mysql -u root -p
Enter password: 
​
mysql> ? lock
Name: 'LOCK'
Description:
Syntax:
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
​
lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

通过命令可以看出可以在表上面添加读锁和写锁,下面分别对读锁和写锁做测试

更全面的信息可以参考https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html,视自己的MySQL版本选择对应帮助文档版本

读锁

在会话1给表添加读锁

lock table tb_v_s_user read;

创建两个会话(打开两个sql窗口),在会话1中对tb_v_s_user表加读锁

操作 会话1 会话2
select user_name from tb_v_s_user limit 1; 可以 可以
select user_id from tb_vote limit 1; 不可以 可以
update tb_v_s_user set user_name = '1F7sJ' where user_name = '1F7sJ'; 不可以,直接报错 阻塞-获得锁-执行
lock table tb_v_s_user read; 可以,而且无论执行多少次,一个会话只能对一个表加一个读锁,证明它是可重入锁 可以,表上的读锁加1
unlock tables 释放该会话所有的锁 释放该会话所有的锁

写锁

在操作之前先将两个会话中的所有读锁释放掉

unlock tables;

在会话1中添加写锁

show open tables where In_use > 0;

比较两个会话在不同情况下的结果

操作 会话1 会话2
select user_name from tb_v_s_user limit 1; 可以 阻塞-获得锁-执行
select user_id from tb_vote limit 1; 不可以 可以
update tb_v_s_user set user_name = '1F7sJ' where user_name = '1F7sJ'; 可以 阻塞-获得锁-执行
lock table tb_v_s_user read; 可以,一旦执行之前的写锁就变成了读锁,如果没有别的会话在表上有锁,还是变回写锁 阻塞-获得锁-执行
unlock tables 释放该会话所有的锁 释放该会话所有的锁

行级锁

前面说表级锁会导致吞吐量很低,锁竞争严重,为此出现了行级锁,行级锁高并发下锁竞争小、吞吐量大。使用行级锁可以实现事务的ACID属性,避免因为并发出现的数据更新丢失、脏读、不可重复读、幻读的情况。

扩展:

ACID即为事务的原子性、一致性、隔离性、持久性:

原子性(Atomicity):执行的SQL要么全部成功,要么全部失败,不可被拆分

一致性(Consistent):就是数据在事务开始之前的状态会同一变为事务结束后的状态,不能部分被改变

隔离性(Isolation):不同会话执行SQL对结果互不影响,比如会话1写的数据在未提交之前是不能被会话2看到的

持久性(Durable):数据一旦被提交,再被别的操作覆盖之前永久被保存着

 

没有行锁支持的事务导致的并发问题:

更新丢失(Lost Update):会话1中执行的更新操作未提交之前被会话2中执行的更新操作覆盖

脏读(Dirty Reads):会话1中执行的更新操作未提交就被会话2看到了

不可重复读(Non-Repeatable Reads):会话1读取的数据在未提交之前不能重复读

幻读(Phantom Reads):会话1中执行的新增操作未提交就被会话2看到了

 

事务隔离级别:

使用命令查看默认隔离级别

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

 

比较两个会话在不同情况下的结果

测试之前先关闭掉每个会话的自动提交功能

mysql> set autocommit=0;
Query OK, 0 rows affected

拿一条数据做测试,测试之前数据是这样的:

mysql> select * from tb_v_user where user_name = '01Vo5';
+----------------------+-----------+-----+--------+
| user_id              | user_name | age | gendor |
+----------------------+-----------+-----+--------+
| 01Vo53QWYUqgSsuUWN0s | 01Vo5     |  45 |      2 |
+----------------------+-----------+-----+--------+
1 row in set

 

操作 会话1 会话2
会话1中:update tb_v_user set age = 44 where user_name = '01Vo5'; 查询结果:age=44 查询结果:age=45
会话1中:做commit; 查询结果:age=44 查询结果:age=45(因为会话2还没有commit)
会话2中:做commit; 查询结果:age=44 查询结果:age=44
会话1中:update tb_v_user set age = 46 where user_name = '01Vo5'; 查询结果:age=46 执行同样的update语句,会阻塞等待直到会话1的commit之后,拿到锁才会成功
会话1中:select * from tb_v_user where user_name = '01Vo5'; 查询结果:age=46 执行update tb_v_user set age = 47 where user_name = '01Vo5';
会话1\2中:select * from tb_v_user where user_name = '01Vo5'; 查询结果:age=46 查询结果:age=47

行锁(MySQL默认事务隔离级别:REPEATABLE-READ)特点总结一下:只要在行上做写操作,行锁就会排斥其他写操作,其他读是可以继续的,而且不会读写了未提交的数据

 

注:在会话中执行ddl(create table ,drop table,create index ,drop index等等)操作,会触发commit操作

始料未及的表锁灾难

先准备一点数据,将tb_v_user中的数据插入20份到tb_vv_user中。虽然里面数据重复了,在索引的情况下,根据索引字段更新还是行级锁

在tb_vv_user的user_name字段上添加索引

create index idx_tb_vv_user_un on tb_vv_user(user_name);

更新语句走全表扫描

1.有索引,但是更新时未走索引扫描

为了测试这一点,我得先将user_name = '01Vo5'行的user_name更新为全数字

update tb_vv_user set user_name = '12345' where user_name = '01Vo5';

测试步骤:

会话1中按照走索引方式更新数据

会话2中按照不走索引方式更新数据,同时会话2中执行其他行的更新

会话1profiles:

会话2profiles:

可以看出会话1按照不走索引方式更新时,导致了会话2的更新被阻塞

有兴趣的同学还可以再深入查看一下每个sql的执行过程

show profile for query id

2.无索引

既然上面没有走索引就会导致整张表被锁,那如果表上面没有索引,做更新是不是也会这样喃?测试一下

#会话1
drop index idx_tb_vv_user_un on tb_vv_user;
update tb_vv_user set age = 13 where user_name = '12345';
#会话2
update tb_vv_user set age = 13 where user_name = '0343W'
​
update tb_vv_user set age = 13 where user_name = '0343W'

测试步骤:删除表上的索引,在会话1中执行更新操作的同时,在会话2中执行更新操作。为了验证会话2的更新操作确实被会话1的更新阻塞,在前面的操作都执行完后再次在会话2中执行更新操作,通过比较时间来判断。下面看看profiles

会话1:

会话2:

从执行耗时上看,是符合预期的。结论:没有索引做更新时,会触发表级锁,我认为更宽泛的说法应该是“只要DML操作走全表扫描都会触发表锁”。我认为原因是这样的:因为全表扫描过程很慢,在当前会话还没更新完数据的时候,其他会话更新了数据就违反了事务的隔离性,所以必须加表级写锁

删除语句走全表扫描

有了上面的理论,所以删除肯定也是表级写锁了

会话1:

会话2:

我在这里只测试了无索引走全表扫描的情况,结果符合预期,两个会话同时执行的时候出现了阻塞

有兴趣的同学还可以测试有索引,但是使用varchar的自动转换做删除是否会发生阻塞

创建索引

#会话1执行
create index idx_tb_vv_user_un on tb_vv_user(user_name);
#会话2执行
update tb_vv_user set age = 10 where user_name = '0343W';

查看profiles情况

会话1:

创建索引耗时31秒

会话2:创建索引时执行的更新操作

更新耗时接近7秒,为什么这么耗时?因为在创建索引的时候是表锁,任何更新操作都会被阻塞

会话2:索引创建完后执行更新操作

更新耗时毫秒级

批量插入导致索引重建

我就不测试了,和上面的区别是手工创建索引与人工创建索引

间隙锁

会话1执行的DML操作是范围扫描,而会话2执行的DML操作又在会话1的范围内,那么会话2的DML操作就会被阻塞,比如:

会话1在UPDATE条件为ID>1 AND ID<5,那么会话2在会话1还未提交时对ID在1到5之间的数据做DML操作就会阻塞

 

总结一下:

从开发人员角度出发,需要从以下几点去避免问题和优化SQL

1.开发时在测试环境多使用explain和profile检查自己写的SQL,比如有没有走索引,索引使用是否恰当,用小表驱动大表,避免大表的全表扫描

2.上线后跟踪系统运行情况,比如打开慢日志查询,跟踪优化SQL,不断的迭代

3.数据批量操作时避免出现表锁和间隙锁,使用show open tables 查看表上锁的情况。比如插入与删除数据、重建索引等

4.开发时如何关闭了自动提交功能,要时刻注意手动提交与关闭连接或者回收连接

5.不要写超长的SQL

6.批量操作尽量放在系统负载低的时候去做

7.对需求发布时的数据库脚本做认真的验证,做好数据备份以备发布失败回退

© 著作权归作者所有

特拉仔
粉丝 62
博文 266
码字总数 259663
作品 0
渝中
部门经理
私信 提问
PHP 性能分析第三篇: 性能调优实战

注意:本文是我们的 PHP 性能分析系列的第三篇,点此阅读 PHP 性能分析第一篇: XHProf & XHGui 介绍 ,或 PHP 性能分析第二篇: 深入研究 XHGui 。 在本系列的 第一篇 中,我们介绍了 XHProf 。...

OneAPM蓝海讯通
2015/10/23
38
0
更新简历后,来找我的公司都是外包?什么情况!

  你是否有过这么一段经历,一直做着OA,CRM管理系统等传统项目,简历技能开头第一行,基本都是“Java基础扎实”,然后下面接着就是熟悉XXX。然后不断列名词,举例一下:   Java基础扎实...

java进阶架构师
01/13
0
0
Go开发实战

写这本书主要是灵感来自于: https://github.com/thekarangoel/Projects 然后我就想到了当初做PHP的时候,也有类似的项目,觉得golang也可以实现一个类似的书籍,暂且把书名定为《Go实战开发...

astaxie
2013/08/05
7.3K
0
SQL 常用优化手段总结 - 分析 SQL 语句的一般步骤

数据库的性能调优是一个很大的话题。但是对于开发人员来讲,掌握一些常用的 SQL 优化手段却不是什么难事。 从本章节开始,将连载总结常用的适合于开发人员的 SQL 优化手段与大家分享。 要想解...

给你添麻烦了
2018/01/09
0
0
The based of tuning

调优目的: 1、 提高资源利用率 2、 找出性能瓶颈并缓解 3、 通过性能管理实现合理的资源分配,提升硬件性价比 调优分层及效率问题: 业务级调优 eg:1)网站使用的Apache—>业务架构 2)将原...

卡子火
2017/05/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

关于docker0: iptables: No chain/target/match by that name的问题解决

由于Docker 0默认网桥的iptables策略冲突问题,将导致一些web server启动时出现如下错误: docker: Error response from daemon: driver failed programming external connectivity on endpo......

王焱君
今天
87
0
js 下载 canvas 兼容移动端

很蛋疼的问题PC上好好的, 移动端下载不了 , 貌似前端 js 生成的时 base64 格式的 图片数据,移动端无法直接下载, 但是chrome 移动端和pc端都没问题, 国产的几个浏览器全部挂了 之前的下载方式...

阿豪boy
昨天
76
0
微信小程序获取用户OpenId

1.在小程序中获得UserCode: wx.login({success: function (res) {thisApp.setData({userCode: res.code})}}) 2.在小程序中获得UserInfo: wx.getUserInfo({success...

kaition
昨天
54
0
代码生成器技术乱弹二十三,未来之野望,未实现的功能SQLTypes

在原先的Java类型系统的基础上新增SQL类型系统,有校验确保SQL类型系统兼容于Java类型系统。在生成数据库脚本时候使用SQL类型系统。

火箭船
昨天
48
0
Mybatis在插入自增字段时,会对当前对象未赋值的自增字段进行赋值

Mybatis在插入自增字段时,会对当前对象未赋值的自增字段进行赋值,如下即自动完成对id的赋值 OrderItem orderItem = new OrderItem(); System.out.println("==...

Gotcha_
昨天
42
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部