基于索引的SQL优化(下)

怼烎@ 2022-05-31 00:36 128阅读 0赞

![这里写图片描述][SouthEast]

今天继续我们的sql优化.上次讲了7条,今天再补充几条.

## 8.like子句尽量前端匹配 ##

因为like参数使用的非常频繁，因此如果能够对like子句使用索引，将很高的提高查询的效率。  
例6：select \* from city where name like ‘%S%’  
以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），如果能够修改为：  
select \* from city where name like ‘S%’  
那么查询的执行计划将会变成（INDEX RANGE SCAN），成功的利用了name字段的索引。这意味着Oracle SQL优化器会识别出用于索引的like子句，只要该查询的匹配端是具体值。因此我们在做like查询时，应该尽量使查询的匹配端是具体值，即使用like ‘S%’。

## 9.用Case语句合并多重扫描 ##

我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询：  
例8：1）select count(\*) from emp where sal<1000;  
2）select count(\*) from emp where sal between 1000 and 5000;  
3）select count(\*) from emp where sal>5000;  
这样我们需要进行三次全表查询，但是如果我们使用case语句：  
select  
count (sale when sal <1000  
then 1 else null end) count\_poor,  
count (sale when between 1000 and 5000  
then 1 else null end) count\_blue\_collar,  
count (sale when sal >5000  
then 1 else null end) count\_poor  
from emp;  
这样查询的结果一样，但是执行计划只进行了一次全表查询。

## 10.使用基于函数的索引 ##

前面谈到任何对列的操作都可能导致全表扫描，例如：  
select \* from emp where substr(ename,1,2)=’SM’;  
但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的基于函数的索引，  
create index emp\_ename\_substr on eemp ( substr(ename,1,2) );

这样在执行上面的查询语句时，这个基于函数的索引将排上用场，执行计划将是（INDEX RANGE SCAN）。

## 11.基于函数的索引要求等式匹配 ##

上面的例子中，我们创建了基于函数的索引，但是如果执行下面的查询：  
select \* from emp where substr(ename,1,1)=’S’  
得到的执行计划将还是（TABLE ACCESS FULL），因为只有当数据列能够等式匹配时，基于函数的索引才能生效，这样对于这种索引的计划和维护的要求都很高。请注意，向表中添加索引是非常危险的操作，因为这将导致许多查询执行计划的变更。然而，如果我们使用基于函数的索引就不会产生这样的问题，因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引。

## 12.使用分区索引 ##

在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。  
例如，假设你已经定义了一个分区索引，并且某个SQL语句需要在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。

## 13.使用位图索引 ##

位图索引可以从本质上提高使用了小于1000个唯一数据值的数据列的查询速度，因为在位图索引中进行的检索是在RAM中完成的，而且也总是比传统的B树索引的速度要快。对于那些少于1000个唯一数据值的数据列建立位图索引，可以使执行效率更快。

# 最后总结 #

我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10％的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40％时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：  
1）对于原始排序的表 仅读取少于表记录数40％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。  
2）对于未排序的表 仅读取少于表记录数7％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。

[SouthEast]: /images/20220531/5cad82f6227d4afd8903337e6ea02503.png