在我以前还在从事开发工作的时候,我经常会疑惑,我的sql语句涉及的表上明明有索引,可为什么偏偏就是不走索引而是选择全表扫描呢?然后一顿百度之后,让我的sql强制走上了索引,可为什么走上索引之后比全表扫描还要慢呢?现在回过头来看看确实很好笑,不过简单归简单,今天还是来探究一下其中涉及到的知识点。
当一张表上有索引,那么sql在执行的时候却不走索引的原因有很多很多,例如:
- 隐式转换
- 索引列上使用了函数
- 索引列选择度太差
- 条件列使用了is null或者is not null
- 条件列使用模糊查询并且前面带%
- 条件列使用了<>或者!=
- 表的统计信息与实际值相差很多
- 索引不满足当前的业务场景
- 索引列分布不均匀,且该列上存在直方图
- 查询条件没有使用索引的前导列
- 对索引列进行了运算
- 等值和范围索引不会被合并使用
- 索引相关的参数设置
- 索引的clustering factor值太大
......
今天我们就来看看最后两项是如何影响索引使用的。
和索引的使用有关的参数大家应该都很熟悉:
optimizer_index_cost_adj:优化器计算通过索引扫描访问表数据的cost开销,可以通过这个参数进行调整。参数可用值的范围为1到10000。默认值为100,超过100后越大则越会使索引扫描的COST开销越高(计算的),从而导致查询优化器更加倾向于使用全表扫描。相反,值越小于100,计算出来的索引扫描的开销就越低。
optimizer_index_caching:用于在执行in-list遍历和嵌套循环连接时,优化器评估已经存在于buffer cache中的索引块的数量(以百分比的方式)。参数的取值范围是0到100,默认值为0,取值越大就越减少优化器在评估In-list和嵌套循环连接的索引扫描的开销COST。