MySQL中对MVCC的理解总结

Posted on by

一、MVCC简介

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

二、MVCC原理

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

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

MYSQL中的MVCC原理

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

这几项介绍如下：

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

事务隔离级别的区别：

  • 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之ICP、MRR、BKA、BNL

Posted on by

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 indexcondition提示

相关参数

optimizer_switch="index_condition_pushdown=on”;

适用场景

#辅助索引INDEX (zipcode, lastname, firstname).

SELECT * FROM people WHERE zipcode='95054'AND lastname LIKE '%etrunia%'AND address LIKE '%Main Street%';

People表有个二级索引INDEX (zipcode, lastname, firstname),用户只知道某用户的zipcode,和大概的lastname、address,此时想查询相关信息。

若不使用ICP：则是通过二级索引中zipcode的值去基表取出所有zipcode=’95054’的数据，然后server层再对lastname LIKE ‘%etrunia%’AND address LIKE ‘%Main Street%’;进行过滤

若使用ICP：则lastname LIKE ‘%etrunia%’AND address LIKE ‘%Main Street%’的过滤操作在二级索引中完成，然后再去基表取相关数据

使用限制

l  只支持 select 语句

l  5.6 中只支持 MyISAM与InnoDB引擎

l  5.6中不支持分区表的ICP;从MySQL 5.7.3开始支持分区表的ICP

l  ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法；

l  不支持主建索引的ICP（对于Innodb的聚集索引，完整的记录已经被读取到Innodb Buffer，此时使用ICP并不能降低IO操作）

l  当 SQL 使用覆盖索引时但只检索部分数据时，ICP 无法使用

l  ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

Multi-Range Read (MRR)

MRR 的全称是 Multi-Range Read Optimization，是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段。

MRR原理

select non_key_column from tb where ey_column=x;

在没有使用MRR特性时，MySQL执行查询的伪代码

第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合，结果集为rest。

select key_column, pk_column from tb where key_column=x order by key_column

第二步 通过第一步获取的主键来获取对应的值。

for each pk_column value in rest do:

select non_key_column from tb where pk_column=val

使用MRR特性时，MySQL执行查询的伪代码

第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合，结果集为rest

select key_column, pk_column from tb where key_column = x order by key_column

第二步 将结果集rest放在buffer里面(read_rnd_buffer_size 大小直到buffer满了)，然后对结果集rest按照pk_column排序，得到结果集是rest_sort

第三步 利用已经排序过的结果集，访问表中的数据，此时是顺序IO.

       select non_key_column fromtb where pk_column in (rest_sort)

综上

在不使用 MRR 时，优化器需要根据二级索引返回的记录来进行“回表”，这个过程一般会有较多的随机IO, 使用MRR时，SQL语句的执行过程是这样的：

1)   优化器将二级索引查询到的记录放到一块缓冲区中

2)   如果二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序

3)   用户线程调用MRR接口取cluster index，然后根据cluster index 取行数据

4)   当根据缓冲区中的 cluster index取完数据，则继续调用过程 2) 3)，直至扫描结束

通过上述过程，优化器将二级索引随机的 IO 进行排序，转化为主键的有序排列，从而实现了随机 IO 到顺序 IO 的转化，提升性能

此外MRR还可以将某些范围查询，拆分为键值对，来进行批量的数据查询，如下：

SELECT * FROM tWHERE key_part1 >= 1000 AND key_part1 <
2000AND key_part2 = 10000;

表t上有二级索引(key_part1, key_part2)，索引根据key_part1,key_part2的顺序排序。

若不使用MRR：索引扫描会将key_part1在1000到2000的索引元组，而不管key_part2的值，这样对key_part2不等于10000的索引元组也做了额外的扫描。此时扫描的范围是：

[{1000, 10000}, {2000, MIN_INT}]此间隔可能包含key_part2不等于10000的部分

若使用MRR：扫描则分为多个范围，对于每一个Key_part1(1000,1001…,1999)单个值的扫描只需要扫描索引中key_part2为10000的元组。如果索引中包含很多key_part2不为10000的元组，最终MRR的效果越好。MRR扫描的范围是多个单点间隔[{1000, 10000}],
…, [{1999, 10000}] 此间隔只包含key_part2=10000的部分。

MRR标识

当使用ICP优化时，执行计划的Extra列显示Using MRR提示

相关参数

用optimizer_switch 的标记来控制是否使用MRR.设置mrr=on时，表示启用MRR优化。

mrr_cost_based表示是否通过cost base的方式来启用MRR.

当mrr=on,mrr_cost_based=on,则表示cost base的方式还选择启用MRR优化,当发现优化后的代价过高时就会不使用该项优化

当mrr=on,mrr_cost_based=off,则表示总是开启MRR优化

SET  @@optimizer_switch='mrr=on,mrr_cost_based=on';

参数read_rnd_buffer_size 用来控制键值缓冲区的大小。二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序

适用场景

#辅助索引key_part1，查询key_part1在1000到2000范围内的数据

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000

不使用MRR：先通过二级索引的key_part1字段取出满足条件的key_part1,pk_col order by key_part1.然后通过pk_col去表中取出满足条件的数据，此时，因为取出的pk_col是乱序的，而表又是pk_col存放数据的，当去表中取数据时，则会产生大量的随机IO

使用MRR：先通过二级索引的key_part1字段取出满足条件的key_part1,pk_col order by key_part1.放到缓存中（read_rnd_buffer_size），当对应的缓冲满了以后，将这部分key值按照pk_col排序，最后再按照排序后的reset去取表中数据，此时pk_col1是顺序的，将随机IO转化为顺序IO，多页数据记录可一次性读入或根据此次的主键范围分次读入，以减少IO操作，提高查询效率

使用限制

MRR 适用于range、ref、eq_ref的查询

Batched Key Access (BKA)和Block Nested-Loop(BNL)

Batched Key Access (BKA)– 提高表join性能的算法。

当被join的表能够使用索引时，就先排好顺序，然后再去检索被join的表，听起来和MRR类似，实际上MRR也可以想象成二级索引和primary key的join

如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)

BKA原理

对于多表join语句，当MySQL使用索引访问第二个join表的时候，使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后，批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的（mrr目的是较为顺序）.这样，MRR使得查询更有效率。

大致的过程如下:

1 BKA使用join buffer保存由join的第一个操作产生的符合条件的数据

2 然后BKA算法构建key来访问被连接的表，并批量使用MRR接口提交keys到数据库存储引擎去查找查找。

3 提交keys之后，MRR使用最佳的方式来获取行并反馈给BKA

BNL和BKA都是批量的提交一部分行给被join的表，从而减少访问的次数，那么它们有什么区别呢？

 第一 BNL比BKA出现的早，BKA直到5.6才出现，而NBL至少在5.1里面就存在。

 第二 BNL主要用于当被join的表上无索引

 第三 BKA主要是指在被join表上有索引可以利用，那么就在行提交给被join的表之前，对这些行按照索引字段进行排序，因此减少了随机IO，排序这才是两者最大的区别，但是如果被join的表没用索引呢？那就使用NBL了。

BKA和BNL标识

Using join buffer (Batched Key Access)和Using join buffer (Block Nested Loop)

相关参数

BAK使用了MRR，要想使用BAK必须打开MRR功能，而MRR基于mrr_cost_based的成本估算并不能保证总是使用MRR，官方推荐设置mrr_cost_based=off来总是开启MRR功能。打开BAK功能(BAK默认OFF)：

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

BKA使用join buffer size来确定buffer的大小，buffer越大，访问被join的表/内部表就越顺序。

BNL默认是开启的，设置BNL相关参数：

SET optimizer_switch=’block_nested_loop’

适用场景

支持inner join, outer join, semi-join operations,including nested outer joins

BKA主要适用于join的表上有索引可利用，无索引只能使用BNL了

简单总结

以下部分来源：http://www.cnblogs.com/zhoujinyi/p/4746483.html

 ICP优化（Index
Condition Pushdown）

Index Condition Pushdown (ICP)是MySQL用索引去表里取数据的一种优化。禁用ICP(MySQL5.6之前)，引擎层会利用索引在基表中寻找数据行，然后返回给MySQL
Server层，再去为这些数据行进行WHERE后的条件的过滤（回表）。启用ICP(MySQL5.6之后)，如果部分WHERE条件能使用索引中的字段，MySQL会把这部分下推到引擎层。存储引擎通过使用索引把满足的行从表中读取出。ICP减少了引擎层访问基表的次数和MySQL
Server 访问存储引擎的次数。总之是 ICP的优化在引擎层就能够过滤掉大量的数据，减少io次数，提高查询语句性能


MRR优化（Multi-Range Read）

 

Multi-Range Read 多范围读(MRR) 它的作用是基于辅助/第二索引的查询，减少随机IO，并且将随机IO转化为顺序IO，提高查询效率。在没有MRR之前(MySQL5.6之前)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再通过主键来获取对应的值。辅助索引获取的主键来访问表中的数据会导致随机的IO(辅助索引的存储顺序并非与主键的顺序一致)，不同主键不在同一个page里面时必然导致多次IO 和随机读。使用MRR优化(MySQL5.6之后)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再将结果集放在buffer里面(read_rnd_buffer_size 大小直到buffer满了)，然后对结果集按照pk_column排序，得到有序的结果集rest_sort。最后利用已经排序过的结果集，访问表中的数据，此时是顺序IO。即MySQL 将根据辅助索引获取的结果集根据主键进行排序，将无序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，以减少IO操作，提高查询效率。


Nested Loop Join算法：

将驱动表/外部表的结果集作为循环基础数据，然后循环该结果集，每次获取一条数据作为下一个表的过滤条件查询数据，然后合并结果，获取结果集返回给客户端。Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次，效率非常差。


Block Nested-Loop Join算法：

将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。主要用于当被join的表上无索引。


Batched Key Access算法：

当被join的表能够使用索引时，就先排好顺序，然后再去检索被join的表。对这些行按照索引字段进行排序，因此减少了随机IO。如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK
Nested-loop)。

 

参考：

http://dev.mysql.com/doc/refman/5.7/en/select-optimization.html

http://blog.itpub.net/22664653

http://www.kancloud.cn/taobaomysql/monthly/117959

http://www.kancloud.cn/taobaomysql/monthly/67181

http://www.cnblogs.com/zhoujinyi/p/4746483.html

转：https://blog.csdn.net/caomiao2006/article/details/52205177

IO多路复用机制详解（转）

Posted on by

服务器端编程经常需要构造高性能的IO模型，常见的IO模型有四种：

（1）同步阻塞IO（Blocking IO）：即传统的IO模型。

（2）同步非阻塞IO（Non-blocking IO）：默认创建的socket都是阻塞的，非阻塞IO要求socket被设置为NONBLOCK。注意这里所说的NIO并非Java的NIO（New IO）库。

（3）IO多路复用（IO Multiplexing）：即经典的Reactor设计模式，有时也称为异步阻塞IO，Java中的Selector和Linux中的epoll都是这种模型。高性能并发服务程序使用IO多路复用模型+多线程任务处理的架构。

（4）异步IO（Asynchronous IO）：即经典的Proactor设计模式，也称为异步非阻塞IO。不经常用。

高性能I/O设计模式Reactor和Proactor：https://blog.csdn.net/xiongping_/article/details/45152333

转自：https://blog.csdn.net/baixiaoshi/article/details/48708347

[译]Go里面的unsafe包详解

Posted on by

unsafe包位置: src/unsafe/unsafe.go

https://gocn.vip/question/371

指针类型：
*类型：普通指针，用于传递对象地址，不能进行指针运算。
unsafe.Pointer：通用指针，用于转换不同类型的指针，不能进行指针运算。
uintptr：用于指针运算，GC 不把 uintptr 当指针，uintptr 无法持有对象。uintptr 类型的目标会被回收。

unsafe.Pointer 可以和 普通指针 进行相互转换。
unsafe.Pointer 可以和 uintptr 进行相互转换。

也就是说 unsafe.Pointer 是桥梁，可以让任意类型的指针实现相互转换，也可以将任意类型的指针转换为 uintptr 进行指针运算。

一般使用流程：
第一步：将结构体->通用指针unsafe.Pointer(struct)->uintptr(通用指针)获取内存段的起始位置start_pos，并记录下来，第二步使用。
第二步：使用start_pos + unsafe.Offsetof(s.b)->将地址转为能用指针unsafe.Pointer(new_pos)->转为普通指针np = (*byte)(p)->赋值 *np = 20
第三步：打印结构体，发现内容发生了更改。

参考：https://www.cnblogs.com/golove/p/5909968.html

golang中slice切片理解总结

Posted on by

首先我们对切片有一个大概的理解，先看一下slice的内部结构，共分三部分，一个是指向底层数组的时候，一个是长度len，另一个就是slice的容量cap了。如cap不足以放在新值的时候，会产生新的内存地址申请。

先看代码

package main

import "fmt"

func main() {
	// 创建一个切片，长度为9，容量为10
	fmt.Println("----- 1.测试切片变量append的影响（未申请新的内存空间）-----")
	a := make([]int, 9, 10)
	fmt.Printf("%p len=%d cap=%d %v\n", a, len(a), cap(a), a)

	// 切片进行append操作，由于原来len(a)长度为9,而cap(a)容量为10，未达到扩展内存的要求，此时新创建的切片变量还指向原来的底层数组，只是数组的后面添加一个新值
	// 此时一共两个切片变量，一个是a，另一个是s4。但共指向的一个内存地址
	s4 := append(a, 4)
	fmt.Printf("%p len=%d cap=%d %v\n\n", s4, len(s4), cap(s4), s4)

	// 测试上面提到的切片变量a和s4共指向同一个内存地址, 发现切片数组的第一个值都为7，而唯一不同的是len的长度，而cap仍为10
	fmt.Println("----- 2.测试切片变量共用一个底层数组（内存地址一样）-----")
	a[0] = 7
	fmt.Printf("%p len=%d cap=%d %v\n", a, len(a), cap(a), a)
	fmt.Printf("%p len=%d cap=%d %v\n\n", s4, len(s4), cap(s4), s4)

	// 切片进行append操作后，发现原来的cap(a)的长度已用完了(因为a和s4共用一个底层数组，你也可以理解为cap(s4))，此时系统需要重新申请原cap*2大小的内存空间,所以cap值为10*2=20，将把原来底层数组的值复制到新的内存地址
	// 此时有两个底层数组，一个是切片变量a和s4指向的数组，另一个就是新的切片变量s4
	fmt.Println("----- 3.测试切片变量append的影响（申请了新的内存空间，内存地址不一样了）-----")
	s4 = append(s4, 5)
	fmt.Printf("%p len=%d cap=%d %v\n\n", s4, len(s4), cap(s4), s4)

	// 注意：原切片未发生任何变化，(打印a[0]=7是因为上面第3段落代码已把默认的0值改为了7）
	fmt.Println("----- 4.测试原切片变量a未发生变化-----")
	fmt.Printf("%p len=%d cap=%d %v\n\n", a, len(a), cap(a), a)
}

运行结果：

----- 1.测试切片变量append的影响（未申请新的内存空间）-----
0x10450030 len=9 cap=10 [0 0 0 0 0 0 0 0 0]
0x10450030 len=10 cap=10 [0 0 0 0 0 0 0 0 0 4]

----- 2.测试切片变量共用一个底层数组-----
0x10450030 len=9 cap=10 [7 0 0 0 0 0 0 0 0]
0x10450030 len=10 cap=10 [7 0 0 0 0 0 0 0 0 4]

----- 3.测试切片变量append的影响（申请了新的内存空间）-----
0x10458050 len=11 cap=20 [7 0 0 0 0 0 0 0 0 4 5]

----- 4.测试原切片变量a未发生变化-----
0x10450030 len=9 cap=10 [7 0 0 0 0 0 0 0 0]

在线运行代码：https://play.golang.org/p/pfxZa8T0H1_g

当对slice进行append的时候，会出现以下情况：
1.如果cap超出了原slice的cap长度，则申请原来cap*2的内存空间，再把原来切片所指的底层数组的值复制一份存储到新申请的内存空间里，这样是两块内存地址了，所有原来slice指的底层数组内容没有变化的。此时两个切片，两个底层数组了，每个切片有自己对应的底层数组。
2.如果cap没超出原slice的cap的话，底层对应的数组是没有变化的，但会产生一个新的slice变量，仍然会指向到原来的底层数组（这个新slice变量有自己的内存地址，我们不用关心它，只需要关心他对应的底层数组就可以了）。这时一共两个切片，但共用一个底层数组。

总结：
1.当对slice进行append的时候，如果原slice可以存放下新增加的值，则不会申请新的内存空间，否则会申请cap*2大小基准申请内存空间。而当cap>=1024的时候，会以cap*1.25倍的大小空间基准申请内存空间。
2.每次切片进行append操作，都会返回一个新的切片变量，这个变量有自己的内存地址。 这点开发者知道这点就可以了，一般不用不用关心这点。
3.当切片进行append后，原切片对应的底层数组是不会变化的，这点见第4点。

请参考golang开发源码包 src/runtime/slice.go 中的growslice函数，可以看到对于cap的增长处理逻辑。