教你区分 多块读、单块读、散列读、顺序读、索引扫描

理解多块读、单块读、散列读、顺序读、索引扫描对性能调优是很有好处的，下面给大家分享下数据块之间不同读会有怎样的区别?

一次IO能够读取的Block的个数，是以下三者的最小值

db_file_multiblock_read_count

extent（block都只能在一个区，不能跨区）

os limit（linux好像不超过1M）

多块读的场景

Full Table Scan --全表扫描

Index Fast Full Scans --索引快速全扫描

单块读的场景

Rowid Scans --直接通过Rowid获取

Index Unique Scans --索引唯一扫描

Index Range Scans --索引局部扫描

Index Skip Scans --索引跳跃扫描

Index Full Scans --索引全扫描

访问某张表的数据不可能同时发生多块读又发生单块读

因Index Fast Full Scans就是读取索引以获取数据，不需要再返回表获取数据

所以只有其他模式的索引扫描再通过ROWID来获取数据，其他模式的索引扫描是单块读，ROWID也是单块读。

为什么多块读比单块读快，即为什么有时全表扫描比索引扫描还快

因为逻辑读物理读单位是次块（一次IO读取相同或不同块数情况下，看读取了多少次）,数据总块数一样的情况下，多块读的话，读取次数就少，逻辑读或物理读就少了，而全表扫描就是多块读。

可以这样理解：你一次读一个快算一次IO，一次读多个块也是算一次IO，那哪种性能会比较好。

再比如一秒内要读完10个块，单块读的话1次读一个块，需要10次，多块读的话假如1次读10个块，需要1次。虽然两者产生的IO吞吐量都是一样的，但是前者的IOPS是10，后者的IOPS是1，而一次IO的开启和结束是要消耗操作系统很多资源的。

db file scattered read

This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.

The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called 'db file scattered read'. multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'.

此事件表示用户进程正在将缓冲区读入SGA缓冲区高速缓存并正在等待物理I/O调用返回。 一个db文件分散读取发出一个将数据读入多个不连续的内存位置的分散读取。 分散读取通常是多块读取。 除了全表扫描之外，它还可能发生快速全索引扫描。

db文件分散读等待事件标识正在进行全表扫描或快速索引全扫描。 当对缓冲区高速缓存执行全表扫描或快速索引全扫描时，读取的块被读取到彼此物理上不相邻的内存单元。 这样的读取被称为分散读取调用，因为这些块被分散在整个内存中。这就是为什么相应的等待事件被称为'db文件分散读取'的原因。 由于全表扫描或快速索引全扫描到缓冲区缓存中，多块（最多DB_FILE_MULTIBLOCK_READ_COUNT个块）读取显示为等待“db文件分散读取”。

db file sequential read

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call because of extent boundaries, or buffers present in the buffer cache. These waits would also show up as db file sequential read.

此事件表示用户进程正在将缓冲区读入SGA缓冲区高速缓存并正在等待物理I/O调用返回。 顺序读取是单块读取。

单块I / O通常是使用索引的结果。很罕见的是，由于缓冲区边界或缓冲区缓存中存在缓冲区，全表扫描调用可能会被截断为单个块调用。 这些等待也将显示为db文件顺序读取。

Full Table Scans

This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. The high water mark indicates the amount of used space, or space that had been formatted to receive data. Each row is examined to determine whether it satisfies the statement's WHERE clause.

When Oracle Database performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, the database can make I/O calls larger than a single block to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads, the database can perform a full table scan very efficiently. The database reads each block only once.

这种扫描类型从表中读取所有行，并过滤掉不符合选择条件的行。 在全表扫描期间，扫描处于高水位标记下的表中的所有块。高水位标记表示已使用空间的数量，或已格式化为接收数据的空间。 检查每一行以确定它是否满足语句的WHERE子句。当Oracle数据库执行全表扫描时，顺序读取块。 因为这些块是相邻的，所以数据库可以使I / O调用大于单个块来加快进程。 读取呼叫的大小范围从一个块到由初始化参数DB_FILE_MULTIBLOCK_READ_COUNT指示的块数。 使用多块读取，数据库可以非常有效地执行全表扫描。 数据库读取每个块只有一次。

Rowid Scans

The rowid of a row specifies the data file and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.

To access a table by rowid, Oracle Database first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes. Oracle Database then locates each selected row in the table based on its rowid.

This is generally the second step after retrieving the rowid from an index. The table access might be required for any columns in the statement not present in the index.

一行的rowid指定包含该行中的行和该行的位置的数据文件和数据块。 通过指定其rowid来定位行是检索单个行的最快方法，因为指定了数据库中行的确切位置。要通过rowid访问表，Oracle数据库首先从语句的WHERE子句或通过对表的一个或多个索引的索引扫描获取所选行的rowid。 然后，Oracle数据库将根据其rowid在表中定位每个选定的行。

这通常是从索引中检索rowid后的第二步。 索引列以外的其他列需要通过rowid返回表访问。

select直接使用条件rowid='AAAX7bAAEAAAo1VAAM'则执行计划显示为TABLE ACCESS BY USER ROWID

Index Unique Scans

This scan returns, at most, a single rowid. Oracle Database performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.

此扫描最多返回一个rowid。 如果语句包含UNIQUE或PRIMARY KEY约束，则Oracle数据库将执行唯一的扫描，以确保仅访问单个行。

Index Range Scans

An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.

If you require the data to be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.

索引范围扫描是访问选择性数据的常用操作。 它可以是有限的（在两边限制）或无界（在一侧或两侧）。 以索引列的升序返回数据。 具有相同值的多个行按照rowid的升序排序。

如果您要求数据按顺序排序，请使用ORDER BY子句，而不依赖于索引。 如果索引可以满足ORDER BY子句，则优化器将使用此选项并避免排序。

Index Skip Scans

Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.

Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The database determines the number of logical subindexes by the number of distinct values in the initial column. Skip scanning is advantageous when there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate.

在跳过扫描中，复合索引的初始列未在查询中指定。 换句话说，它被跳过。

当复合索引的前导列中只有少量几个不同的值而索引的非前导列中有大量的不同的值时，跳过扫描是有利的。

当查询谓词中未指定组合索引的前导列时，数据库可以选择索引跳过扫描。

Index Fast Full Scans

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. The database cannot use this scan to eliminate a sort operation because the data is not ordered by the index key. The database reads the entire index using multiblock reads, unlike a full index scan, and can scan in parallel.

A fast full scan is faster than a normal full index scan because it can use multiblock I/O and can run in parallel just like a table scan.

当索引包含查询所需的所有列时，快速全索引扫描可替代全表扫描，并且索引键中至少有一列具有NOT NULL约束。 快速全索引扫描访问索引本身的数据，而无需访问表。 数据库无法使用此扫描来消除排序操作，因为数据不是由索引键排序的。 数据库使用多块读来读取整个索引，与完整索引扫描不同，可以并行扫描。

快速全索引扫描比正常全索引扫描更快，因为它可以使用多块I/O和并行，就行全表扫描一样。

Index Full Scans

A full index scan eliminates a sort operation, because the data is ordered by the index key. It reads the blocks singly. Oracle Database may use a full scan in any of the following situations:

An ORDER BY clause that meets the following requirements is present in the query:

--All of the columns in the ORDER BY clause must be in the index.

--The order of the columns in the ORDER BY clause must match the order of the leading index columns.

--The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.

The query requires a sort merge join. The database can perform a full index scan instead of doing a full table scan followed by a sort when the query meets the following requirements:

--All of the columns referenced in the query must be in the index.

--The order of the columns referenced in the query must match the order of the leading index columns.

--The query can contain all of the columns in the index or a subset of the columns in the index.

A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index. The columns do not need to be in the same order in the index and the GROUP BY clause. The GROUP BY clause can contain all of the columns in the index or a subset of the columns in the index.

全索引扫描消除了排序操作，因为数据按索引键排序。它单独读取块即单块读。Oracle数据库可能会在以下任何情况下使用全面扫描：

查询中存在满足以下要求的ORDER BY子句：

--ORDER BY子句中的所有列都必须在索引中。

--ORDER BY子句中的列的顺序必须与前导索引列的顺序相匹配。

--ORDER BY子句可以包含索引中的所有列或索引中的列的子集。

查询需要排序合并连接。 当查询满足以下要求时，数据库可以执行完整索引扫描，而不是进行全表扫描，然后进行排序。

--查询中引用的所有列必须在索引中。

--查询中引用的列的顺序必须与前导索引列的顺序相匹配。

--查询可以包含索引中的所有列或索引中的列的子集。

查询中存在GROUP BY子句，GROUP BY子句中的列存在于索引中。 这些列在索引和GROUP BY子句中不需要是相同的顺序。 GROUP BY子句可以包含索引中的所有列或索引中的列的子集。

上面的只是大概讲了下多块读、单块读、散列读、顺序读、索引扫描之间的一些区别和概念，大家有空的话可以做下实验，深入体会下不同数量级的数据，在走不同的索引时不同数据块读的区别。 觉得有用的可以收藏起来，以后也可以学习，每天定期推文，大家觉得还可以就走一波关注哦~