MySQL利用 INFORMATION_SCHEMA.PROFILING 分析SQL性能

MySQL5.7中有一个系统默认库 information_schema , 里面有些表如 PROFILING,、OPTIMIZER_TRACE、 PROCESSLIST、INNODB_TRX等,其中 PROFILE 对于我们分析sql有很大的帮助,在此以前我们需要使用 SHOW PROFILE 命令,不过此命令以后将被废弃。下面我们就介绍一下如何使用此表。

从 MySQL8.0开始, 这个表也开始被废弃了,以后分析性能问题直接使用另一个系统库 performance_schema 里的相关表(setup_actors)就可以了。到时候 show profiles 和show profile两个命令也不能用了。

1.在使用此表前,我们需要开户性能检测功能。

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

默认情况下是 OFF/0 状态。在我们分析完,最好关闭以减少服务器压力。

相关查询命令

show VARIABLES like 'profil%'
-------------------------------
profiling	ON
profiling_history_size	15

2. 了解 information_schema.profiling 表的常用字段

官方文档:https://dev.mysql.com/doc/refman/8.0/en/profiling-table.html

1QUERY_ID 查询ID, 用于标记不同的查询
2SEQ 一个查询内部执行的步骤 , 从2开始
3STATE 步骤的状态
4DURATION 持续时间
5CPU_USER 用户空间的cpu 使用量
6CPU_SYSTEM 内核空间的cpu 使用量
7CONTEXT_VOLUNTARY上下文主动切换
8CONTEXT_INVOLUNTARY上下文被动切换
9BLOCK_OPS_IN阻塞输入操作
10BLOCK_OPS_OUT阻塞输出操作
11MESSAGES_SENT消息发送
12MESSAGES_RECEIVED消息接受
13PAGE_FAULTS_MAJOR主分页错误
14PAGE_FAULTS_MINOR次分页错误
15SWAPS swap 发生的次数
16SOURCE_FUNCTIONMySQL源码执行函数
17SOURCE_FILE源码文件
18SOURCE_LINE源码行数

以下是我执行了一个join语句的输出,从结果中我们可以分析出哪个步骤执行的时间最长,进行相应的优化即可。

我们可以根据DURATION 列的值来分析哪一个模块消耗的时间多来进行相应的优化。

推荐使用 OPTIMIZER_TRACER 来分析 SQL 执行过程
https://dev.mysql.com/doc/internals/en/optimizer-tracing.html ,每种参数用法可参考:
https://www.cnblogs.com/DataArt/p/10232831.html

MySQL中group_concat函数详解

函数语法:

group_concat( [DISTINCT]  要连接的字段   [Order BY 排序字段 ASC/DESC]   [Separator ‘分隔符’] )


下面举例说明:

select * from goods;  

+——+——+
| id| price|
+——+——+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+——+——+
6 rows in set (0.00 sec)


  1. 以id分组,把price字段的值在同一行打印出来,逗号分隔(默认)

select id, group_concat(price) from goods group by id;  

+——+——————–+
| id| group_concat(price) |
+——+——————–+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+——+——————–+
3 rows in set (0.00 sec)


2. 以id分组,把price字段的值在一行打印出来,分号分隔 
select id,group_concat(price separator ‘;’) from goods group by id;  

+——+———————————-+
| id| group_concat(price separator ‘;’) |
+——+———————————-+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+——+———————————-+
3 rows in set (0.00 sec)


3. 以id分组,把去除重复冗余的price字段的值打印在一行,逗号分隔
select id,group_concat(distinct price) from goods group by id;  

+——+—————————–+
| id| group_concat(distinct price) |
+——+—————————–+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+——+—————————–+
3 rows in set (0.00 sec)


4. 以id分组,把price字段的值打印在一行,逗号分隔,按照price倒序排列
select id,group_concat(price order by price desc) from goods group by id;  

+——+—————————————+
| id| group_concat(price order by price desc) |
+——+—————————————+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+——+—————————————+
3 rows in set (0.00 sec)

MySQL中order by 排序必知

在开发过程时,我们经常会遇到 order by 排序操作,那么你知道什么时候MySQL才会进行排序操作,什么时候不需要时间排序操作?,下面我们就从一个很小的例子中了解一下排序场景。

表结构如下:

CREATE TABLE t (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
city varchar(16) NOT NULL,
name varchar(16) NOT NULL,
age int(11) NOT NULL,
addr varchar(128) DEFAULT NULL,
PRIMARY KEY (id),
KEY city (city) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里只有一个索引,我们执行一条SQL语句:

SELECT city, name  FROM t WHERE city=’杭州’ ORDER BY name LIMIT 1000;

通过Explain命令查看执行情况

发现Extra字段里有”Using filesort”,说明使用了排序,而排序必用到了sort_buffer, 这是由数据库为了专门进行排序操作而分配的一块内存。
这里的Using index conditon是指ICP特性,请参考:https://blog.haohtml.com/archives/18201

下面我们专门来看一下这条语句的执行流程是如何的?在此以前我们需要了解一个概念,就是索引的特性是有序的,系统搜索时,一旦找到最后一条满足条件的记录后就立即停止,后面的记录则不再进行扫描,这样就可以用来解决全表扫描的性能问题。

Continue reading

MySQL中的半同步复制

MySQL当前存在的三种复制模式有:异步模式、半同步模式和组复制模式。注意:MySQL复制模式没有“同步复制”这一项的,文章中只是为了读者方便理解半同步复制的概念才介绍了同步复制概念https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html

从MySQL5.5开始,MySQL以插件的形式支持半同步复制。

1. 异步复制(Asynchronous replication)

MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理,这样就会有一个问题,主如果crash掉了,此时主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。

异步复制是MySQL最早的也是当前使用最多的复制模式,异步复制提供了一种简单的主-从复制方法,包含一个主库(master)和备库(一个,或者多个) 之间,主库执行并提交了事务,在这之后(因此才称之为异步),这些事务才在从库上重新执行一遍(基于statement)或者变更数据内容(基于 row),主库不检测其从库上的同步情况。在服务器负载高、服务压力大的情况下主从产生延迟一直是其诟病。工作流程简图如下:

而同步复制(Fully synchronous replication,MySQL中没有此复制概念)指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。

2. 半同步复制(Semisynchronous replication)

介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。

MySQL5.5 的版本在异步同步的基础之上,以插件的形式实现了一个变种的同步方案,称之为半同步复制(semi-sync replication)。这个插件在原生的异步复制上,添加了一个同步的过程:当从库接收到了主库的变更(即事务)时,会通知主库。主库上的操作有两种:接收到这个通知以后才去commit事务接受到之后释放session。这两种方式是由主库上的具体配置决定的。当主库收不到从库的变更通知超时时,由半同步复制自动切换到异步同步,这样就极大了保证了数据的一致性(至少一个从库),但是在性能上有所下降,特别是在网络不稳定的情况下,半同步和同步之间来回切换,对正常的业务是有影响的。其工作流程简图如下:

Continue reading

MySQL中对MVCC的理解总结

一、MVCC简介

MVCC (Multiversion Concurrency Control),即多版本并发控制技术。InnoDB数据库的事务隔离级别就是通过UNDO和MVCC来实现的(ACID特性),旧数据存储在UNDO中,再通过DB_ROLL_PTR 回溯查找历史版本。

二、MVCC原理

1、通过DB_ROLL_PT 回溯查找数据历史版本
2、通过read view判断行记录是否可见

理解这一块之前,我们必须先了解一下row的内部存储格式

字段说明:

  • DB_ROW_ID:长度6个字节。此值当由InnoDB自动生成,聚集索引时使用。如果用户未显式指定表主键时,表使用DB_ROW_ID的值作为主键ID,聚集索引会使用此值。如果指定了表主键的话,则聚集索引使用指定的值。
  • DB_TRX_ID:6个字节的事务ID。标记了最后更新此记录的事务ID,每开起一个新事务,其值自动+1
  • DB_ROLL_PTR:7字节的回滚指针。指向当前记录项的undo log记录,找之前版本的数据需通过此指针。

MySQL中的MVCC原理

新insert记录的DB_ROLL_PTR指针为NULL。修改新值后,记录的 DB_ROLL_PTR 回滚指针指向原始值在Undo Log 日志的位置,也就是说将原值在Unde Log的物理位置存储到原记录的 DB_POLL_PTR 字段。如果事务回滚的话,则从Undo Log 中把原始值读取出来再放到记录中去。如果直接commit的话,则直接保存即可。

– InnoDB Undo Log的日志类型
MySQL数据库InnoDB存储引擎的undo log采用了逻辑的日志
InnoDB undo log的格式可以概括为:<操作类型>+<Table ID>+<数据>.

  A. 从表中删除一行记录
TRX_UNDO_DEL_MARK_REC (将主键记入日志)
在删除一条记录时,并不是真正的将数据从数据库中删除,只是标记为已删除.这样做的好处是Undo Log中不用记录整行的信息.在undo时操作也变得很简单.
  B. 向表中插入一行记录
TRX_UNDO_INSERT_REC (仅将主键记入日志)
TRX_UNDO_UPD_DEL_REC (将主键记入日志) 当表中有一条被标记为删除的记录和要插入的数据主键相同时, 实际的操作是更新这个被标记为删除的记录。
  C. 更新表中的一条记录
TRX_UNDO_UPD_EXIST_REC (将主键被更新了的字段内容记入日志)
TRX_UNDO_DEL_MARK_REC 和 TRX_UNDO_INSERT_REC,当更新主键字段时,实际执行的过程是删除旧的记录然后,再插入一条新的记录。

事务隔离级别的区别:

  • RR隔离级别下,在每个事务开始的时候,会将当前系统中的所有的活跃事务拷贝到一个列表中(read view)。
  • RC隔离级别下,在事务中的每个语句开始(select)时,会将当前系统中的所有的活跃事务拷贝到一个列表中(read view)
  • 然后按照以下逻辑判断事务的可见性

MVCC解决了什么问题

  • MVCC使得数据库读不会对数据加锁,普通的SELECT请求不会加锁,提高了数据库的并发处理能力;
  • 借助MVCC,数据库可以实现RC,RR等隔离级别,用户可以查看当前数据的前一个或者前几个历史版本。保证了ACID中的I特性(隔离性)。

查看当前数据库中的活跃事务,可执行

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

参考:
https://mp.weixin.qq.com/s/tNA_-_MoYt1fJT0icyKbMg

MySQL数据库InnoDB存储引擎Log漫游(2)

MySQL多版本并发控制分析 

MySQL之ICP、MRR、BKA、BNL

Index Condition Pushdown(ICP)

Index Condition Pushdown (ICP)mysql使用索引从表中检索行数据的一种优化方式。

ICP原理

禁用ICP,存储引擎会通过遍历索引定位基表中的行,然后返回给MySQL Server层,再去为这些数据行进行WHERE后的条件的过滤。

开启ICP,如果部分WHERE条件能使用索引中的字段,MySQL Server 会把这部分下推到存储引擎层,存储引擎通过索引过滤,把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。

ICP的目标是减少从基表中全纪录读取操作的数量,从而降低IO操作

对于InnoDB表,ICP只适用于辅助索引。

ICP标识

当使用ICP优化时,执行计划的Extra列显示 Using index condition 提示

Continue reading