mysql索引和MVVC

目录

• 什么是索引?
  • 从上图可知innodb使用的索引结构有如下特点:
• 为什么选择类b+树结构?
• 索引的类型?
• 局部性原理,磁盘预读 和分页?
  • 什么是mysql局部性原理?
• 什么是聚集索引, 什么是非聚集索引?
• 非/ 聚集索引的结构和查询顺序
• 聚集索引的主键设置规则
• 为什么要拆分出聚集索引和非聚集索引两种结构?
• 回表
• 不同存储 引擎中索引结构的区别?
  • 注意事项:
• Mysql 索引和全表扫描查找数据的方式分别是什么 ?
• 最左前缀原则
• 索引的优缺点
  • 索引的优点
  • 索引的缺点
• ACID的定义和实现原理
  • ACID简述
  • Innodb 实现 ACD 特性
  • Undo log 实现事务回滚
  • 隔离级别与隔离级别解决的问题
    • 查看当前隔离级别
    • 隔离级别
• Mysql 日志
  • 日志类型
  • 查看日志信息
  • Redo log 循环写入
  • 什么是版本链?
  • 查看当前事务id
  • 判断版本链行记录对当前事务可见
    • ReadView 字段以及含义
    • 事务查询版本链原则
    • 举例
    • 总结
    • 在不同隔离级别下判断对当前事务可见的行记录
    • 读已提交和可重复读都是根据 ReadView 确定当前可读的事务号, 区别是什么, 具体是如何工作的?
      • 举例
      • 总结
• 一致性非锁定读和一致性锁定读

什么是索引?

索引是数据库中一种用于快速查询数据的数据结构 , innodb中通常选用类b+树构建一个索引

从上图可知innodb使用的索引结构有如下特点:

1. 不是二叉树, 而是基于m阶的b+树的结构 , 一个节点内允许存放多个值
2. 非叶节点数据都会冗余在叶节点上
3. 叶节点都是有序的
4. 叶节点上有前继指针指向上一个节点
5. 非叶节点有双向指针分别指向前后节点

为什么选择类b+树结构?

1. 相比于二叉搜索树高度小, 树的高度跟查询的次数相关, 树越矮查询次数越少, 提高查询效率
2. 相比于b+ 树, 类b+树子节点之间分别有指针指向前一个子节点和后一个子节点, 提高查询效率
3. B+Tree 的高度一般都在2_{4层。InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作

索引的类型?

按功能分类: 主键索引、普通索引、唯一索引、组合索引、全文索引

按结构分类 : 聚集索聚、非聚集索引

局部性原理,磁盘预读 和分页?

什么是mysql局部性原理?

局部性原理定义: 指CPU访问存储器时，无论是存取指令还是存取数据，所访问的存储单元都趋于聚集在一个较小的连续区域中

为了尽量减少I/O操作，计算机系统一般采取预读的方式，预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k）

主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

局部性类型:

1. 时间局部性：如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。
2. 空间局部性：在最近的将来将用到的信息很可能与正在使用的信息在空间地址上是临近的。
3. 顺序局部性 : 在典型程序中，除转移类指令外，大部分指令是顺序进行的

在mysql中的应用 : 当读取一个字节时, 实际上取了一页(page), 假如下一次要用到紧跟其后的数据无需再访问磁盘, 节约了一次IO时间

操作系统的页为4kb , mysql的页默认为16kb, 分页大小可以修改但必须是4kb的整数倍

查innoDB分页大小 :

SELECT @@innodb_page_size;
或
show global variables like '%page_size%'; # 16384 (16Kb)

什么是聚集索引, 什么是非聚集索引?

聚集索引 : 指索引项的排序方式和表中数据记录排序方式一致的索引 ;特点是: 叶节点data域保存了完整的数据记录, 用户数据和索引数据存在一起

非聚集索引 : 指索引顺序与物理存储顺序不同的索引 , 特点是: 叶节点是一个指向对应的数据块的指针, 用户数据和索引数据存在一起

聚集索引和非聚集索引是两种不同结构的索引方式, 当我们在 innodb 表中创建一个索引的时候, 这个索引要么是聚集索引, 要么是非聚集索引;

当一个表中有存在主键索引时, 主键索引就是聚集索引的一种情况; 存在主键的情况下创建一个普通索引, 该索引就是一个非聚集索引

非/ 聚集索引的结构和查询顺序

假设创建的索引是单个字段的索引, 数据库会创建一个类b+树结构, 叶节点按顺序存放索引字段的值; 在上一层节点存放每个叶节点的首个值, 直到根节点结束(非聚集索引); 另外数据库会找当前表的主键, 按照主键规则选择合适的字段创建另一个类b+树结构, 非叶节点的值为主键值, 同样也是经过排序 -> 向上建立索引的方式, 区别是叶节点存放了整个行记录(聚集索引)

查询顺序分为三种情况:

1. 精确查询
2. 范围查询
3. 全字段查询

聚集索引的主键设置规则

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）

但是当一个表没有主键，或者没有一个索引，Innodb会如何处理呢。请看如下规则：

​ 1.如果一个主键被定义了，那么这个主键就是作为聚集索引

​ 2. 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引

​ 3. 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

为什么要拆分出聚集索引和非聚集索引两种结构?

道聚集索引用类b+树存储, 叶节点存放的是行记录; 假如每次创建普通索引时都使用聚集索引的结构, 必然产生大量的数据冗余, 并且因为一个分页里存放的行记录要比存放一个字段占用更多空间, 导致单页能存放的有效数据减少, 查询次数增加

需要的数据在一个非聚集索引中可以获得而无需回表, 这种情况下使用非聚集索引效率是非常高的, 该索引又被成为覆盖索引;

回表

使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，利用主键值找到节点上对应的数据行的数据

不同存储 引擎中索引结构的区别?

引擎 : innodb && myisam

两种引擎索引的区别 :

1. myisam 叶节点的data域存放的是数据记录的地址

2. innodb :

   1. 聚集索引 : 叶节点data域保存了完整的数据记录, 特点集索引的结构和查询顺序: 用户数据和索引数据存在一起
   2. 非聚集索引 : 叶节点是一个指向对应的数据块的指针 特点: 只存放索引数据
   3. 一张表里必须要有主键

注意事项:

1. 一般来说, 不应该修改聚集索引 : 自增主键会把数据自动向后插入，避免了插入过程中的聚集索引排序问题。聚集索引的排序，必然会带来大范围的数据的物理移动，这里面带来的磁盘IO性能损耗是非常大的。 而如果聚集索引上的值可以改动的话，那么也会触发物理磁盘1上的移动，于是就可能出现page分裂，表碎片横生。所以不应该修改聚集索引。
2. 索引所在的字段尽量避免出现大量重复数据, 否则可能导致索引失效
3. 创建索引要根据实际场景设计, 注意最左前缀原则
4. 非聚集索引中存在的数据在行记录里有一个完整且重复的数据, 索引越多占用空间越大 , 尽量避免超过5个索引在同一张表中
5. 因为非聚集索引本身占用空间, 所以要尽可能避免创建重复或冗余的索引
6. mysql 内部有分析器, 会在查询之前预估本次查询的消耗, 所以不是每次认为有索引的字段查询都会走索引, 如果分析器认为全表查询更快会放弃索引

Mysql 索引和全表扫描查找数据的方式分别是什么 ?

1. 索引是按照根节点到叶节点的顺序, 利用二分查找的方式快速定位到条件指定的行记录集, 也就是从上到下检索
2. 全表扫描只用到叶节点, 从第一个叶节点开始一个个往后走, 没有建立索引意味着没有排序, 所以会扫描全部叶节点

最左前缀原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

使用通配符%开头的模糊查询会导致索引失效, 原因是违背了索引的最左前缀原则

最左前缀原则和模糊查询通配符问题, 本质是同一个原因导致的

1. 联合查询的查询, 假设字段 fieldA , fieldB , fieldC 按顺序组成联合索引 index ( fieldA , fieldB , fieldC ), 我在使用索引检索数据时要先通过A字段的值确定下一个节点的位置, 如果A字段不确定的就无法找到下一个节点, 导致索引失效
2. 使用通配符查询, 假设查询内容是 "hello%", 字符串会按照顺序排列, 索引先确定了h的位置再确定e的位置, 直到查询完成 ; 假设查询内容是 "%hello", 索引无法找到%的位置, 索引失效 , 进而选择全表扫描

索引的优缺点

索引的优点

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

1. 需要占用物理空间 : 每个索引都有额外在磁盘创建b+树, 聚集索引占用的更多

2. 创建索引和维护索引要耗费时间,消耗cpu和内存资源

3. 建立了索引不利于变更数据的场景 : 每次对数据增加、删除和修改都要维护新的排序, 查询变快修改变慢

---

ACID的定义和实现原理

ACID简述

A: 原子性 : 整个事务的所有操作, 要么全部完成, 要么全部不完成

C : 一致性 : 指系统从一个正确的状态(满足约束的状态),迁移到另一个正确的状态, AID都是数据库的实现, 应用层依赖AID来保证事务的一致性

I : 隔离性 : 重点是解决不同进程访问同一数据的处理方案, 出现并发问题, 在mysql中与隔离级别相关

D : 持久性 : 目的是提供一个安全的地方存储数据，而不用担心丢失; 数据一旦被存储, 不会因为数据库故障而丢失或修改

Innodb 实现 ACD 特性

1. 原子性
   使用 undo log 实现事务回滚

2. 隔离性
   通过MVVC实现不同级别的事务隔离

3. 持久性

   通过两类储存引擎实现磁盘存储

   1. 日志结构存储引擎(LSM树)
   2. 面向页面存储引擎(B树)

4. 一致性

   一致性是基于ADI+应用层逻辑实现, ADI是数据库层面的实现, 符合ADI的逻辑并不能保证一致性

   这里我们举个大家都在说的财务系统的例子.

   A要向B支付100元,而A的账户中只有90元,并且我们给定账户余额这一列的约束是,不能小于0.那么很明显这条事务执行会失败,因为90-100=-10,小于我们给定的约束了.

   这个例子里,支付之前我们数据库里的数据都是符合约束的,但是如果事务执行成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证.然后我们再看个例子

   A要向B支付100元,而A的账户中只有90元,我们的账户余额列没有任何约束.但是我们业务上不允许账户余额小于0.因此支付完成后我们会检查A的账户余额,发现余额小于0了,于是我们进行了事务的回滚.

   这个例子里,如果事务执行成功,虽然没有破坏数据库的约束,但是破坏了我们应用层的约束.而事务的回滚保证了我们的约束,因此也可以说事务提供了一致性保证(ps:事实上,是我们应用层利用事务回滚保证了我们的约束不被破坏).最后我们再看个例子

   A要向B支付100元,而A的账户中只有90元,我们的账户余额列没有任何约束.然后支付成功了.

   这里,如果按照很多人的理解,事务不是保证一致性么?直观上账户余额为什么能为负呢.但这里事务执行前和执行后,我们的系统没有任何的约束被破坏.一直都是保持正确的状态.

Undo log 实现事务回滚

undo log主要记录了数据的逻辑变化，每次对数据修改都会追加一个记录并用指针指向前一次操作，这样在发生错误时，就能根据记录的操作回滚到事务之前的数据状态。

隔离级别与隔离级别解决的问题

查看当前隔离级别

select @@session.transaction_isolation	

隔离级别

隔离级别	说明
读未提交	一个事务还没提交时，它做的变更就能被别的事务看到
读已提交	一个事务提交之后，它做的变更才会被其他事务看到
可重复读	一个事务中，对同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。 InnoDB默认级别 。
串行化	事务串行化执行，每次读都需要获得表级共享锁，读写相互都会阻塞，隔离级别最高，牺牲系统并发性。

不同的隔离级别是为了解决不同的问题。也就是脏读、幻读、不可重复读。

隔离级别	脏读	不可重复读	幻读
读未提交	可以出现	可以出现	可以出现
读已提交	不允许出现	可以出现	可以出现
可重复读	不允许出现	不允许出现	可以出现
序列化	不允许出现	不允许出现	不允许出现

---

Mysql 日志

日志类型

1. 重做日志（redo log）
2. 回滚日志（undo log）
3. 二进制日志（bin log）
4. 错误日志（error log）
5. 慢查询日志（slow query log）
6. 一般查询日志（general log）
7. 中继日志（relay log）

日志类型	默认文件	功能	说明
redo log	ib_logfile0 , ib_logfile1 , ... 允许的最大日志文件数为 100	用于在崩溃恢复期间纠正不完整事务写入的数据,保障已提交事务的持久性	基于磁盘的数据结构,循环写入
undo log	ibdata	实现事务回滚,保障未提交事务的原子性 mvvc事务隔离	InnoDB最多支持 128 个回滚段
bin log	mysql-bin.index + mysql-bin.000006	主从复制 数据恢复	二进制日志 格式: ROW,Statement,MiXED
error log	VM-0-6-centos.err	记录错误信息
slow query log	mysql-slow.log	记录执行或查询时间长的 SQL 全表扫描的 SQL 没有使用索引的 SQL 分析定位慢查询
general query log	VM-0-6-centos.log	记录mysql服务器客户端连接 / 断开连接 / 接收的SQL语句的操作
relay log	VM-0-6-centos-relay-bin.index	记录主主复制 / 主从复制操作产生的日志	与bin log格式相同

查看日志信息

SHOW  GLOBAL VARIABLES LIKE '%log%';

Redo log 循环写入

redo log实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志; 在下图中，write pos表示redo log当前记录的LSN(逻辑序列号)位置，check point表示数据页更改记录刷盘后对应redo log所处的LSN(逻辑序列号)位置。

write pos到check point之间的部分是redo log空着的部分，用于记录新的记录；check point到write pos之间是redo log待落盘的数据页更改记录。

当check pos追上write point时，表示数据页都已经记录到磁盘。

当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。

什么是版本链?

innodb 使用undo log 实现版本链

版本链上存储的内容是一行记录的初始值以及基于时间的未提交事务链, 使用了链表结构

一个节点包含一行记录的值和事务版本id, 以及指向上回滚事务的指针

版本链中行记录的存储格式，有一些额外的字段： DATA_TRX_ID和DATA_ROLL_PTR 。

• DATA_TRX_ID ：数据行版本号。用来标识最近对本行记录做修改的事务 id。
• DATA_ROLL_PTR ：指向该行回滚段的指针。该行记录上所有旧版本，在 undo log 中都通过链表的形式组织。

查看当前事务id

set autocommit=OFF;
begin;
select * from table;
SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();  # 329068689653112
commit;

判断版本链行记录对当前事务可见

ReadView 字段以及含义

1. m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表
2. min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值
3. max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值
4. creator_trx_id：表示生成该ReadView事务的事务id

事务查询版本链原则

1. 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
2. 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
3. 如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
4. 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

举例

m_ids可理解为一个数组, 当m_ids的值为 [1,2,4] , 当前事务id = 3时, 说明3不是活跃的, 可以访问 ; 如果当前事务id =4 ,则这个事务还没有被提交, 不允许被访问 。可以看到, m_ids不一定是连续的数组, 所以在判断范围的同时还需要判断当前事务号是否在 m_ids 数组中

总结

当trx_id在m_ids中，或者大于m_ids列表中最大的事务id的时候，这个版本就不能被访问

在不同隔离级别下判断对当前事务可见的行记录

1. 读未提交 : 读版本链新的记录, 即事务编号83号记录
2. 读已提交 / 可重复读 : 利用 ReadView 读版本链中对当前事务可见的最新记录
3. 串行化 : 所有事务在完成前都会对数据加锁, 不存在读不一致的问题, 因此无需实现多版本并发控制

读已提交和可重复读都是根据 ReadView 确定当前可读的事务号, 区别是什么, 具体是如何工作的?

需求: 根据定义, 读已提交下重复读取同一数据会读到已提交的最新结果, 可重复读在当前事务只能读到第一次读取的值

举例

读已提交 :

假设80号事务已提交, 81号, 82号按顺序开启事务, 但未提交

1. 81号事务把name值修改为小李
2. 82号事务随后读取id=1的数据, 此时生成一行 ReadView 记录, 82号按版本链编号从大到小遍历, 81号在活跃事务组中, 所以80号的记录为当前事务可见的数据 ; 查询结果 name = 小赵
3. 81号事务提交, 82号事务再次查询;
4. 82号事务生成另一个 ReadView 记录, 81号事务不在活跃事务组, 81号数据对82号事务可见 ; 查询结果 name = 小李

第一次查询	m_ids	min_trx_id	max_trx_id	creator_trx_id
	[81]	81	81	82
第二次查询	m_ids	min_trx_id	max_trx_id	creator_trx_id
	[]	-	-	82

不可重复读 :

1. 81号事务把name值修改为小李
2. 82号事务随后读取id=1的数据, 此时生成一行 ReadView 记录, 82号按版本链编号从大到小遍历, 81号在活跃事务组中, 所以80号的记录为当前事务可见的数据 ; 查询结果 name = 小赵
3. 81号事务提交, 82号事务再次查询;
4. 82号事务重用上一次 ReadView 记录, 81号在活跃事务组中, 80号数据对82号事务可见 ; 查询结果 name = 小赵

总结

读已提交和可重复读的区别 : 读已提交在每次读取数据时都生成新的 ReadView 记录, 所以每次都在查数据的时刻判断最新的不活跃事务; 可重复读仅在事务开始第一次读数据生成新的 ReadView 记录, 每次都用首次查询时刻的状态判断, 取到的是相同值

一致性非锁定读和一致性锁定读

一致性非锁定读(快照读) :一条记录被事务A加了X锁而事务B仍然可以读而不被阻塞, 通过 undo log + mvvc 实现读取版本快照

一致性锁定读(当前读) : 为了保证特定场景数据一致性，读取时对数据加锁

SELECT ... FOR UPDATE; # 读取加排他锁
SELECT ... LOCK IN SHARE MODE; # 读取加共享锁

一致性非锁定读的优点: 读不阻塞, 重用undo log,无额外开销

inndb 默认使用一致性非锁定读