Index Full Scans

    A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is also available when there is no predicate, if both the following conditions are met:All of the columns in the table referenced in the query are included in the index.At least one of the index columns is not null.

    A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.

Fast Full Index 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. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

    Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

    A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

建议继续学习：

1. 由浅入深探究mysql索引结构原理、性能分析与优化    (阅读：15773)
2. 浅谈MySQL索引背后的数据结构及算法    (阅读：10689)
3. 由浅入深理解索引的实现(2)    (阅读：7097)
4. HBase二级索引与Join    (阅读：6405)
5. 如何建立合适的索引？    (阅读：6110)
6. mysql查询中利用索引的机制    (阅读：5926)
7. InnODB和MyISAM索引统计集合    (阅读：5817)
8. Innodb 表和索引结构    (阅读：5586)
9. MySQL索引背后的数据结构及算法原理    (阅读：5174)
10. mysql索引浅析    (阅读：4749)