单块读与多块读

单块读与多块读这两个概念对于掌握 SQL 优化非常重要，更准确地说是单块读的物理 I/O 次数和多块读的物理 I/O 次数对于掌握 SQL 优化非常重要。

从磁盘 1 次读取 1 个块到 buffer cache 就叫单块读，从磁盘 1 次读取多个块到 buffer cache 就叫多块读。如果数据块都已经缓存在 buffer cache 中，那就不需要物理 I/O 了，没有物理 I/O 也就不存在单块读与多块读。

绝大多数的平台，一次 I/O 最多只能读取或者写入 1MB 数据，Oracle 的块大小默认是 8k，那么一次 I/O 最多只能写入 128 个块到磁盘，最多只能读取 128 个块到 buffer cache。在判断哪个访问路径性能好的时候，通常是估算每个访问路径的 I/O 次数，谁的 I/O 次数少，谁的性能就好。在估算 I/O 次数的时候，我们只需要算个大概就可以了，没必要很精确。

为什么有时候索引扫描比全表扫描更慢

假设一个表有 100 万行数据，表的段大小为 1GB。如果对表进行全表扫描，最理想的情况下，每次 I/O 都读取 1MB 数据（128 个块），将 1GB 的表从磁盘读入 buffer cache 需要 1 024 次 I/O。在实际情况中，表的段前 16 个 extent，每个 extent 都只有 8 个块，每次 I/O 只能读取 8 个块，而不是 128 个块，表中有部分块会被缓存在 buffer cache 中，会引起 I/O 中断，那么将 1GB 的表从磁盘读入 buffer cache 可能需要耗费 1 500 次物理 I/O。

从表中查询 5 万行数据，走索引。假设一个索引叶子块能存储 100 行数据，那么 5 万行数据需要扫描 500 个叶子块（单块读），也就是需要 500 次物理 I/O，然后有 5 万条数据需要回表，假设索引的集群因子很小（接近表的块数），假设每个数据块存储 50 行数据，那么回表需要耗费 1 000 次物理 I/O（单块读），也就是说从表中查询 5 万行数据，如果走索引，一共需要耗费大概 1 500 次物理 I/O。如果索引的集群因子较大（接近表的总行数），那么回表要耗费更多的物理 I/O，可能是 3 000 次，而不是 1 000 次。

根据上述理论我们知道，走索引返回的数据越多，需要耗费的 I/O 次数也就越多，因此，返回大量数据应该走全表扫描或者是 INDEX FAST FULL SCAN，返回少量数据才走索引扫描。根据上述理论，我们一般建议返回表中总行数 5% 以内的数据，走索引扫描，超过 5% 走全表扫描。请注意，5% 只是一个参考值，适用于绝大多数场景，如有特殊情况，具体问题具体分析。

DML 对于索引维护的影响

在 OLTP 高并发 INSERT 环境中，递增列（时间，使用序列的主键列）的索引很容易引起索引热点块争用。递增列的索引会不断地往索引「最右边」的叶子块插入最新数据（因为索引默认升序排序），在高并发 INSERT 的时候，一次只能由一个 SESSION 进行 INSERT，其余 SESSION 会处于等待状态，这样就引起了索引热点块争用。对于递增的主键列索引，我们可以对这个索引进行反转（reverse），这样在高并发 INSERT 的时候，就不会同时插入索引「最右边」的叶子块，而是会均衡地插入到各个不同的索引叶子块中，这样就解决了主键列索引的热点块问题。将索引进行反转之后，索引的集群因子会变得很大（基本上接近于表的总行数），此时索引范围扫描回表会有严重的性能问题。但是一般情况下，主键列都是等值访问，索引走的是索引唯一扫描（INDEX UNIQUE SCAN），不受集群因子的影响，所以对主键列索引进行反转没有任何问题。对于递增的时间列索引，我们不能对这个索引进行反转，因为经常会对时间字段进行范围查找，对时间字段的索引反转之后，索引的集群因子会变得很大，严重影响回表性能。遇到这种情况，我们应该考虑对表根据时间进行范围分区，利用分区裁剪来提升查询性能而不是在时间字段建立索引来提升性能。

在 OLTP 高并发 INSERT 环境中，非递增列索引（比如电话号码）一般不会引起索引热点块争用。非递增列的数据都是随机的（电话号码），在高并发 INSERT 的时候，会随机地插入到索引的各个叶子块中，因此非递增列索引不会引起索引热点块问题，但是如果索引太多会严重影响高并发 INSERT 的性能。

当只有 1 个会话进行 INSERT 时，表中会有 1 个块发生变化，有多少个索引，就会有多少个索引叶子块发生变化（不考虑索引分裂的情况），假设有 10 个索引，那么就有 10 个索引叶子块发生变化。如果有 10 个会话同时进行 INSERT，这时表中最多有 10 个块会发生变化，索引中最多有 100 个块会发生变化（10 个 SESSION 与 10 个索引相乘）。在高并发的 INSERT 环境中，表中的索引越多，INSERT 速度越慢。对于高并发 INSERT，我们一般是采用分库分表、读写分离和消息队列等技术来解决。

在 OLAP 环境中，没有高并发 INSERT 的情况，一般是单进程做批量 INSERT。单进程做批量 INSERT，可以在递增列上建立索引。因为是单进程，没有并发，不会有索引热点块争用，数据也是一直插入的索引中「最右边」的叶子块，所以递增列索引对批量 INSERT 影响不会太大。单进程做批量 INSERT，不能在非递增列建立索引。因为批量 INSERT 几乎会更新索引中所有的叶子块，所以非递增列索引对批量 INSERT 影响很大。在 OLAP 环境中，事实（FACT）表没有主键，时间列一般也是分区字段，所以递增列上面一般是没有索引的，而电话号码等非递增列往往需要索引，为了提高批量 INSERT 的效率，我们可以在 INSERT 之前先禁止索引，等 INSERT 完成之后再重建索引。

数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。

OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

• OLTP 系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
• OLAP 系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。