索引是SQL优化使用频度最高的优化武器，甚至可以说你对索引有了深刻的理解，你基本可以优化身边60%以上的SQL。下面从五个方面来说明索引有关的知识：
1.索引的结构
索引由ROOT（根块），Branch（茎块），Leaf（叶子块）三部分组成，其中Leaf(叶子块）主要存储key column value（索引列具体值），以及能具体定位到数据库所在位置的rowid。
2.索引的特性
根据索引的结构，可提炼出索引的三大特性：
a.索引高度较低
b .索引存储列值
c.索引本身有序
3.索引三大特性在优化中的应用
3.1.索引高度较低
一般表的索引高度都比较低，大部分都在3到5层，很少有超过六层（见过一个90亿的表，也就六层）。索引高度低，这就有利于使用索引范围扫描，而且随着记录的增加，相对于全表访问，索引访问的优势也越来越明显。
3.2索引存储列值
（1）优化count
在未建索引的情况下，直接用count去计算表的记录数，一般都会走全表扫，如果在表上建有索引，将会大大减少逻辑读，极大地提高SQL的性能。
（2）优化avg和sum
在未建索引的情况下，直接用avg和sum，一般都会走全表扫，如果在对应列上建有索引，将会大大减少逻辑读，极大地提高SQL的性能。
3.3索引本身有序
（1）优化order by
当sql语句中有order by语句的时候，必然会产生排序，排序是一个非常耗费资源的操作。新增索引后，Oracle会利用索引本身有序的特点，来避免排序。
（2）优化max和min
利用索引有序的特性，在叶子块的最左边或最右边找到最大值和最小值。
4.组合索引的选用
组合索引适用于单列查询返回多，组合查询返回少的场景；组合查询的组合顺序，要考虑单独的前缀查询；仅等值无范围查询时，组合的顺序不影响性能；组合索引最佳顺序一般是将等值查询的列置前。
5.索引的不利之处
索引对于查询语句性能的提升是显而易见的，但是索引的缺点也很明显，由于索引本身是有序的，在更新数据的过程中也要更新索引，更新完后还要保持索引的有序性，这就需要很大的开销。索引的不足之处从索引的开销和容易失效两个方面来讨论:
(1)索引的各种开销
a.热块竞争
索引最新的数据一般在最右边，而我们访问数据时正常来说也是访问比较新的数据，历史数据很少有人关注，这就容易产生热块的竞争。
b.回表开销
索引存储索引列和rowid，当要访问表中其它列时，需要通过rowid来定位回到表中，其实这个开销也是很大的。索引查询尽可能要避免回表，如果不可避免，则需要关注聚合因子是否过大。
c.更新开销
索引的有序性是一个非常重要的特性，这个特性能够消除排序等开销，但是索引要保持有序性，也是非常不容易的一件事。比较索引列的数据是随机插入的，为了保证这个顺序，索引需要做很多事，比如索引块分裂，而索引列的增删改的开销是很大的。在没有建索引的情况下，数据量不会影响插入的速度，但是当建有索引的时候，数量量越大，插入会越来越慢。
d.建立开销
我们在建索引的过程中，首先要把索引列的数据排序提取出来，再插入到块中形成索引块，这时如果数据不断地插入，就会影响索引的建立，所以还必须锁表，这就是一个极大的开销（一般工作中会noline建索引，可以避免这个）。
（2）索引的使用失效
索引的使用失效包括逻辑失效和物理失效两种。逻辑失效指的是索引本身没有失效，只是由于写法的问题导致索引用不到。物理失效就是索引真的失效，比如被人误设了unable动作，或者是一些类似分区表的不规范操作导致索引失效。
a.索引逻辑失效
（1）用索引反而代价变得更高
（2）发生索引列的类型转换
b.索引物理失效
（1）long列调整导致索引失效
（2）move操作会导致索引失效
（3）分区表的操作导致索引失效
分区表的truncate，drop，split，exchange等操作，都会导致全局索引失效，当操作时加上update global indexes，则全局索引不会失效。
网友评论