之前,在上一篇博客《项目性能优化实践和SQL优化总结》中,我提到了可优化sql之一就是没有正确使用复合索引的sql.
这篇博客就总结一下复合索引和单列索引的相关内容.
复合索引(a,b,c)在作用上约等于(a),(a,b)(a,c)(a,b,c)合一.即查询条件中有首字段存在才会使用该索引. 除非所有记录a字段的值只有很少的几种,数据库才可能会在实质上默认b字段为首字段,跳过a字段进行扫描. 但是一定要避免出现这种情况,这样会令人对复合索引的生效条件产生疑惑,而且为了效率和查询性能考虑,我们应该将能过滤掉最多数据即值最多的字段作为首字段.当然,前提是该字段通常在查询条件中是必须存在的.
复合索引(a,b,c)和单列索引(a)在查询条件中只有a字段时都会被调用,但是存在较小的性能差异.主要性能差异取决于IO性能. 因为索引实质上就是将该条记录的唯一标识rowid和索引字段记录下来.所以一个最小的存储物理块block存储单列索引(a)可能可以存储100条数据,而复合索引(a,b,c)可能只能存储30条,那么在使用索引时,复合索引需要IO到的block更多,自然就会存在性能差异.
复合索引(a,b,c)和单列索引(a)单列索引(b)单列索引(c)在查询条件中abc字段都有时都会被调用,但是存在性能差异. 因为通过多个条件可以从索引中过滤得到更少的记录条数,也就减少了需要回表扫描的次数,甚至可以直接在联合索引中得到所查的所有结果,则不再需要回表.
但是在表空间的使用上,复合索引的占用空间并不等于所涉及到的列的所有单列索引的总和,而是远远小于,经实测4个字段的复合索引占用空间为3072KB,而4个单列索引占用的空间均为2048KB,即4个单列索引总和为8192KB.主要原因是每个索引中都必须包含rowid,而rowid是比较大的,通常情况下占用空间远大于数据.
同时,使用复合索引(a,b,c)时,sql语句中查询条件中的字段顺序也有微小的性能差异.最优时自然是和索引字段顺序相同,其中原理类似于查询时*和字段名和的差别.
如果多种单列索引和复合索引同时存在时,数据库会自动选择最优的执行计划. 但是我们建议不要建立过多的索引.原因有2个:1.占用表空间.2.影响DML的性能,因为DML时要维护索引.
oracle提供了压缩索引,压缩索引能够降低存储的表空间,并且减少IO的block,但是解压时需要占用CPU资源,因此在索引字段值重复率较高且CPU资源较空闲时可以考虑使用.
