龙空技术网

大表查询是不是索引越多越好?

五月看海 229

前言:

现在咱们对“oracle索引添加过多”大概比较看重,各位老铁们都想要了解一些“oracle索引添加过多”的相关资讯。那么小编同时在网摘上搜集了一些有关“oracle索引添加过多””的相关文章,希望大家能喜欢,各位老铁们快快来了解一下吧!

在我负责项目期间,我们设有巡检团队,定期对系统进行"望闻问切"式的检查。他们的职责是确保系统运行良好,查看日志中的异常信息,并提出优化查询的建议。原本,这样的巡检能够弥补系统的不足,提升其性能。然而,随着巡检次数的增多和人员水平的起伏,巡检逐渐沦为形式主义。巡检团队的建议也变得千篇一律,如SQL查询慢了,就建议对A字段加索引。

起初,我们也按照这些建议进行修改。但有一天,一个批量处理业务竟然耗时近两小时才完成。我们开始深入分析,最终发现是因为索引过多导致数据插入效率低下。这次事件让我深思:数据库索引应该如何使用才能恰到好处?以下是我对数据库索引的一些理解和知识。

1.数据库索引特点

索引结构

数据库中常见的索引类型包括B树索引、哈希索引、位图索引等。不同的数据库系统支持的索引类型也不同,需要根据具体的数据库系统来确定使用何种类型的索引。

Oracle数据库使用B-trees存储索引,来加速数据访问。若没有索引,你必须顺序扫描数据来找到所需的记录,这可能需要很长时间。B-trees是一种自平衡的树形数据结构,它可以在O(log n)的时间内查找、插入和删除记录。

B树是一种多路搜索树,它的每个节点可以有多个子节点,而且子节点的键值从小到大排序。B树的特点有:

B树的查询次数与磁盘I/O次数相同,因此B树适合用于外存存储。 B树的非叶子节点只存储索引,不存储数据,因此B树的查询速度非常快。 B树的叶子节点使用链表相连,因此B树支持范围查询。

B+树是B树的一种变体,它在B树的基础上进行了改进,使得每个节点可以存储更多的关键字和数据,从而提高了查询效率。B+树的特点有:

B+树的内部节点不存储数据,只存储索引信息,叶子节点存储数据,且叶子节点使用链表相连。这样的结构使得B+树更适合于磁盘存储,因为磁盘IO是相对较慢的,使用B+树可以减少磁盘IO次数,提高查询效率。 B+树支持范围查询和排序操作。 B+树的结构对并发访问有很好的支持。

2.索引建立该如何选择

选择给表建立索引的方法因数据库而异,但是以下是一些常见的原则:

选择频繁查询的字段:一张表的字段总会有冷热之分,很明显那些频繁使用的字段更适合为它创建索引。 在where和on条件出现的字段优先创建索引:在where和on条件中出现的字段是最常用的查询条件,因此这些字段应该优先创建索引。 选择唯一性索引:唯一性索引的值是唯一的,可以更快速地通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快地确定某个学生的信息。 避免过多的索引:过多的索引会导致查询性能下降,因为每次查询时都需要扫描所有的索引。索引在适合较多数据时不如全表扫描高效,比如:

表中记录为10万条

一个BLOCK中在100条数据,则表大小为1000个BLOCK,多块读一次读16个BLOCK,则需要60多个IO。

如果满足索引的记录数为1000条,占记录数的1%,如果要通过rowid回到表中取其

他字段的值,则发生1000次IO,由于IO是瓶颈,显然不如全表扫描高效!

但是基于规则的优化器RBO(rule based optimizer),当表中存在索引时,将不管不顾,就用索引,哪怕全表扫描更合适!这就是为什么数据库抛弃RBO,目前只支持CBO(COST based optimizer)的原因,CBO会计算谁更划算!更合理和高效!

对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。对于那些定义为blob数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

3.不走索引的情况

在SQL中有很多陷阱会使一些索引无法使用,下面讨论一些常见的问题:

(1)使用不等于操作符(<>、!=)

下面的查询即使在cust_rating列有一个索引,查询语句仍然执行一次全表扫描。

select cust_Id,cust_name from customers where cust_rating <>'aa';

把上面的语句改成如下的查询语句,这样,在采用基于规则的优化器而不是基于代价的优化器(更智能)时,将会使用索引。

select cust_Id,cust_name from customers where cust _rating <'aa' or cust_rating > 'aa';

特别注意:通过把不等于操作符改成OR条件,就可以使用索引,以避免全表扫描。

(2) 使用IS NULL 或IS NOT NULL

使用IS NULL或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时,把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值,就不会使用这个索引(除非索引是一个位图索引)。

(3)使用函数

如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,

使优化器忽略掉这些索引。 下面的查询不会使用索引(只要它不是基于函数的索引)

select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81',

把上面的语句改成下面的语句,这样就可以通过索引进行直找。

select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);

(4)比较不匹配的数据类型(隐式转换)

也是比较难于发现的性能问题之一。 注意下面查询的例子,account_number是一个VARCHAR2类型,在account_number字段上有索引。

下面的语句将执行全表扫描:

select bank_name,address,city,state,zip from banks where account_number = 990354;

Oracle可以自动把where子句变成to_number(account_number)=990354,这样就限制了索引的使用,改成下面的查询就可以使用索引:

select bank_name,address,city,state,zip from banks where account_number ='990354';

特别注意:不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描"

4.索引过多的弊端

当然,正如文章开头所提到的,一个表的索引过多会产生很多弊端。

建立索引的字段越多,数据量大的时候,文件就会越大,查找数据就会变慢。 一个索引会在 update 或 insert 时增加一次 I/O,对于操作系统底层来说是非常损耗性能的。 索引过多一定情况下会导致索引文件过大(指数增长),系统在寻址时查询时间增长。 索引过多会增加维护成本和复杂性,因为需要维护和管理更多的索引。 索引过多可能会导致查询优化器无法选择最优的查询计划,从而影响查询效率。

总的来说,我们需要根据实际的业务需求和数据量,以及数据库的性能特性,来合理设计和调整索引。

标签: #oracle索引添加过多 #位图索引和b树索引的区别