SQL 优化的关键：单块读、多块读与索引维护

1.单块读与多块读

单块读与多块读这两个概念对于掌握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次数少，谁的性能就好。

2.有时候索引扫描比全表扫描更慢

假设一个表有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次，而不是1000次。

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

3.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完成之后再重建索引。