Oracle SQL 解析阻塞导致的一次生产异常

2024.3.1
某模块excel批量导入数据进行处理,没有限制行数,导致生成的 select 有1万多个绑定变量,Oracle 解析该 select 一直没完成,阻塞了该表其他 SQL 的解析(当时生产被阻塞的是一个高频 insert)。

该SQL大致如下,in 列表是根据导入数据动态生成的:

select count(*) from t_table t where (t.col1, t.col2, t.col3) in (
(:1, :2, :3),
(:4, :5, :6)
)

DBA提示:
1、Oracle 19C in 列表超过1000会报错,11G不会报错(我们用的是 11G)。
2、太长的 SQL 解析时可能会遇到一些内存争用。
3、DBA还提示,该 sql 效率不高,in 列表每增加一个值,执行计划就相当于多一次范围扫描,执行计划会很长。

继续阅读

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:是否有一个已经存在或计划添加的索引,包含了所有本地谓词对应的列?在表连接中指包含了涉及的所有表的本地谓词。

合并扫描连接和哈希连接

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

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

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

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

继续阅读