MySQL 事务隔离及实现

1. 事务的 ACID 特性

  • Actomic,原子性:一个事务中的所有操作,要么全部完成,要么全部失败。

  • Consistency:一致性:事务执行前和执行后数据库都处于一致的状态。

  • Isolation:隔离性:同时进行中的事务不会看到其他事务的中间状态。

  • Durability,持久性:事务提交成功后,对数据库所做的变更都是持久的。

2. SQL 标准的事务隔离级别

  • 读未提交, read uncommitted:一个事务还没提交,它做的变更就能被别的事务看到。问题:会产生”脏读”,读到的数据可能是不一致的。

  • 读已提交, read committed:一个事务提交后,它做的变更才会被别的事务看到。问题:不可重复读,在一个事务里,前后两次执行同一个 SQL 看到的数据是不一致的,产生的原因有:1. 数据被修改或删除(称为”不可重复读”),2. 有新插入的数据(称为”幻读”)。

  • 可重复读, repeatable read:一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。解决了”不可重复读”的问题,但没有解决”幻读”问题。

  • 串行化, serializable:对于同一行记录,“写”会加写锁,“读”会加读锁。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。解决了”幻读”的问题,但效率低。

继续阅读

MySQL InnoDB MRR 优化

MRR 是 Multi-Range Read 的简写,目的是减少磁盘随机访问,将随机访问转化为较为顺序的访问。适用于 range/ref/eq_ref 类型的查询。

实现原理:

  1. 在二级索引查找后,根据得到的主键到聚簇索引找出需要的数据。
  2. 二级索引查找得到的主键的顺序是不确定的,因为二级索引的顺序与聚簇索引的顺序不一定一致;
  3. 如果没有 MRR,那么在聚簇索引查找时就可能出现乱序读取数据页,这对于机械硬盘是及其不友好的。
  4. MRR 的优化方式:

    • 将查找到的二级索引键值放在一个缓存中;
    • 将缓存中的键值按照 主键 进行排序;
    • 根据排序后的主键去聚簇索引访问实际的数据文件。
  5. 当优化器使用了 MRR 时,执行计划的 Extra 列会出现 “Using MRR” 。

  6. 如果查询使用的二级索引的顺序本身与结果集的顺序一致,那么使用 MRR 后需要对得到的结果集进行排序。

继续阅读

MySQL ICP 索引条件下推优化

ICP 优化的全称是 Index Condition Pushdown Optimization 。

ICP 优化适用于 MySQL 利用索引从表里检索数据的场景。

ICP 适用的场景

  • ICP 用于访问方法是 range/ref/eq_ref/ref_or_null,且需要访问表的完整行记录。
  • ICP适用于 InnoDB 和 MyISAM 的表,包括分区的表。
  • 对于 InnoDB 表,ICP只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。对于 InnoDB 的聚簇索引,完整的记录已经读进 InnoDB 的缓存,使用 ICP 不能减少 I/O 。
  • ICP 不支持建立在虚拟列上的二级索引。InnoDB 支持在虚拟列上建立二级索引。
  • 引用子查询、存储函数的条件没法下推。
  • Triggered conditions 也没法下推。

继续阅读

Oracle rownum 与 offset

主要来自:
On ROWNUM and Limiting Results
The result offset and fetch first clauses

rownum

rownum 主要有两类用处

  • 处理 top N ;
  • 分页查询;

rownum 的工作机制

rownum 是查询中的伪列,从 1 开始计数。

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned .

rownum 的值是在行记录通过了查询的过滤阶段、在排序或聚合之前被赋值。rownum 只有在被赋值之后才会递增。select * from t where ROWNUM > 1; 是永远查不到记录的。

查询的处理步骤大概如下:
1. from/where 首先执行;
2. rownum 被递增、赋值给 from/where 子句输出的每一行;
3. 执行 select 子句;
4. 执行 group by 子句;
5. 执行 having ;
6. 执行 order by 。

继续阅读

《MySQL 实战45讲》–笔记–锁

根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁是对整个数据库实例加锁。MySQL 提供的一个加全局锁的命令: Flush tables with read lock (FTWRL),让整个库处于只读状态。

全局锁的典型使用场景是做全库逻辑备份。

逻辑备份工具 mysqldump 使用参数 -single-transaction 的时候在导数据之前就会启动一个事务,来确保拿到一致性视图。

一致性读的前提是引擎要支持这个隔离级别的事务。

set global readonly=true 也可以让全库进入只读状态。但存在两个风险: readonly 的值可能被用来做其他逻辑,比如判等一个库是主库还是备库;设置 readonly 之后,如果客户端发生异常,则数据库会一直保持 readonly 状态。

在 slave 上,如果用户有超级权限的话,全库只读 readonly=true 是失效的。

FTWRL 执行完后如果客户端异常断开,MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。

继续阅读

《MySQL 实战45讲》–笔记–order by 实现

用 explain 命令查看执行计划的时候,Extra 列的值含有 “using filesort” 表示需要排序,可能是在内存里排序、也可能是磁盘文件排序。MySQL 给每个线程分配一块内存用于排序,称为 sort_buffer ,可以通过参数 sort_buffer_size 调整这个内存的大小。

如果要排序的数据都能放进 sort_buffer 则直接在内存里排序,否则需要借助磁盘临时文件进行辅助排序。

由于 InnoDB 是索引组织表,聚簇索引就是主键索引,下面的描述就用 rowid 替代主键值。

走索引全字段排序

  1. 初始化 sort_buffer,确定要放入 sort_buffer 的目标字段;
  2. 根据选择的索引查找满足条件的 rowid;
  3. 通过rowid到聚簇索引树获取整行数据,取目标字段放入sort_buffer;
  4. 从选择的索引取下一个记录的rowid;
  5. 重复步骤 3、4直到不满足条件;
  6. 对 sort_buffer 中的数据按照排序列进行排序。
  7. 遍历排序结果,把目标数据返回给客户端。

继续阅读

InnoDB Sorted Index Build

Sorted Index Build

InnoDB 在创建或重建索引时采用 批量加载而不是一次插入一个索引记录的方式。这种索引创建方法也称为 Sorted Index Build ,该方式不支持空间索引。

索引创建有三个阶段:

  1. 第一阶段,扫描聚簇索引,生成索引条目并添加到排序缓存(sort buffer)。当排序缓存满了后,条目被排序、写入临时的中间文件。这个过程也称为 run 。

  2. 第二阶段,一个或多个 run 写入临时中间文件,对文件里的所有条目执行合并排序。

  3. 在第三阶段,排序后的条目被插入到 B+tree 上。

Sorted Index build 采用自底向上方式来构建索引,采用这种方式,在 B+Tree 的每一层,持有对最右叶子页的引用。位于B+Tree需要的深度的最右叶子页被分配,条目按它们排序后的顺序插入。一旦叶子页满了,一个节点指针追加到父页上,同层叶子页被分配用于接下来的插入。这个过程持续至所有条目插入完成,这可能导致插入一直到顶层。

白话解读:最底层的最右叶子页插入索引记录,如果当前最右叶子页满了,生成一个新的最右叶子页作为接下来的插入位置,在新旧最右叶子页之间建立指针链接,然后把指向旧最右叶子页的指针追加到上一层的最右叶子页,触发上一层的最右叶子页插入,逻辑跟前面是一样的,这是个递归的过程,直至根页。这是一个自底向上、递归构建的过程

最终生成的索引结构如下图(图片来自MySQL内核解析:Innodb页面存储结构-1):
InnoDB索引结构

为后续插入保留空间

可以使用 innodb_fill_factor 配置选项来指定在 B+Tree 页空间保留的空闲百分比,用于后续在这些位置插入索引条目。

这个设置应用于索引树的叶子和非叶子页。不适用于外部页如用于 TEXT/CLOB 条目的。

这个配置是一个提示而非强制约束。

MySQL InnoDB 二级索引的排序

排序问题

最近看了极客时间上 《MySQL实战45讲》,纠正了一直以来对 InnoDB 二级索引的一个理解不到位,正好把相关内容总结下。PS:本文的所有测试基于 MySQL 8.0.13 。

先把问题抛出来,下面的 SQL 所创建的表,有两个查询语句,哪个索引是非必须的?

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

作者给的答案是索引 c 和 ca 的数据模型是一样的,因此 ca 是多余的。为啥??

我们知道,二级索引里存放的不是行的位置,而是主键的值,也知道索引是有序的。

如果 c 与 ca 的数据模型一样,那么就要求二级索引的叶子节点不仅是按索引列排序、而且还按关联的主键值进行排序

我以前的理解是 二级索引只按索引列进行排序,主键值是不排序的。

问了专栏作者,得到的答复是:索引 c 就是按照 cab 这样排序,(二级索引))有保证主键算进去、还是有序的。(PS:非原话,前后问了三次得到)。

本着 先问是不是,再问为什么 的思路,进行一番探究。

继续阅读

《数据库索引设计与优化》笔记二

第八章 为表连接设计索引

基本问题BQ:是否存在或者计划设计一个索引,使它包含所有被 where 子句引用的列。BQ 背后的原理是:保证只有知道 是必须被访问的表行时,才去访问该表。

基本连接问题 BJQ:是否有一个已经存在或计划添加的索引,包含了所有本地谓词对应的列?在表连接中指包含了涉及的所有表的本地谓词。

合并扫描连接和哈希连接

合并扫描连接执行过程如下:

  • 执行表或索引扫描以找出满足本地谓词的所有行。
  • 随后可能会进行排序,如果这些扫描未按所要求的顺序提供结果集。
  • 对前两步生成的结果集进行合并。

在以下情况中,合并扫描连接会比嵌套循环连接快:

  • 用于连接的字段上没有可用的索引。这种情况下,使用嵌套循环,内层表可能需要被扫描很多次。
  • 结果表很大。这种情况下使用嵌套循环会导致相同的页被不断重复访问。
  • 连接查询中不止一张表的本地谓词的过滤因子很低。嵌套循环可能导致对内层表(或者内层表索引)的大量随机访问。

继续阅读

《数据库索引设计与优化》笔记一

第二章 表和索引结构

B-Tree-index

索引页和表页

目前页的大小一般是 32K64K 的。页的大小决定了一个页可以存储多少个索引行、表行,以及一共需要多少个页来存储表或者索引。每个页都会预留一定比例的空闲空间,以满足向其添加新的行。缓冲池和 I/O 活动都是基于页的

索引行

对于唯一索引,一个索引行等同于叶子页中的一个索引条目。字段值没表中复制到索引上,并加上一个指向表中记录的指针。

对于非唯一索引,一个索引值后带着多个指针,指针指向下一层非叶子页或叶子页,叶子页的指针指向表中的记录。

B 树索引结构

非叶子页包含着一个键值,以及一个指向下一层级页的指针,该键值是下一层级页中的最大键值。多个索引层级按照这一方式逐层建立,直到根页。

(对于非唯一索引)通过这种方式来查找任何一条索引记录都需要访问相同数量的非叶子页。

第三章 SQL 处理过程

谓词是索引设计的主要入手点。

第四章 为 select 语句创建理想的索引

三星索引评定:

  • 第一颗星:与查询相关的索引行是相邻的,或者至少相距足够靠近。最小化了必须扫描的索引片的宽度。
  • 第二颗星:索引行的顺序与查询语句的需求一致。排除了排序操作。
  • 第三颗星:索引行包含查询语句中的所有列。避免了回表。

宽索引:款索引是指一个至少满足了第三颗星的索引。该索引包含了 select 语句所涉及的所有列,因此能够使得查询只需访问索引而无须回表。

继续阅读