oracle表加索引反而查询慢的原因

本文探讨了数据库索引的设计原则及其对查询性能的影响。通过分析索引的工作原理,指出并非所有情况下增加索引都能提升效率,特别是当数据选择性不高时。建议在创建索引时考虑字段的独特性和重复率,以实现最佳性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

     这两天在大数据库的查询,有几个存储过程查询下来用了好几分钟。一看表,好像索引比较少,就使劲加了些,发现加了反而查询速度更慢了。其实我们只要懂得索引的原理便可以知道索引不是想随便加就加。下面看一下索引的一些原理,我们就可以知道或许你也犯了同样的加了索引反而速度更慢的错:

    当用户从数据表中查询数据时,Oracle数据库提供了两种查询的方式。一是从表中读取每一行,就是大家常说的全表扫描;二是通过ROWID一此读取一行。当表中记录比较多的时候,很明显第二种方式能够更快的定位记录内容。而索引其实就是建立在这个查询原理之上的。如现在某个表中有300多万条记录,而现在用户可能只需要了解其中的10条记录信息。此时如果使用索引标识读取的块,则可以执行比较少的I/O,数据库系统会很快找到用户所需要的内容。而如果没有使用索引的话,则需要读取表中所有的块。

    如果在这个表中加入了索引,那么到底对数据库的性能影响有多大呢?这个就不好说了,因为其跟很多因素相关。如跟数据选择性直接相关。如果用户的数据非常具有选择性,则表中家功能只有很少的行匹配索引值,则Oracle将能够快速查询匹配所引值得ROWID的索引,并且可以快速查询少量的相关表快。如还是上面这个表中,其如果存储有某个市的所有常住人口信息,其中身份证号码肯定是少不了的。如此时用户想根据身份证号码来查询某个人的信息时,那么数据库能够在很短的时间内给出响应。这主要是因为用户提供的数据非常具有选择性,基本上跟数据库中的索引值是一一对应的。而如果用户想通过出身年月信息来查询信息的话,则其数据库反映的速度就会比较慢了。

    可见索引对数据库性能的影响直接跟数据的选择性挂钩。这对于数据库管理员设计索引时很有启发性。如数据库管理员在设计索引时,最好能够选择哪些具有唯一性的字段或者重复性比较少的字段。如此的话,索引对于数据库性能来说才有比较大的价值。

### Oracle 数据库索引的创建、使用与优化 #### 1. 索引概述 Oracle 数据库中的索引是一种数据结构,用于速数据检索操作。通过减少需要扫描的数据量来提高查询性能[^1]。 #### 2. 创建索引 ##### (1) 多列索引 可以针对多个列创建复合索引。例如,在 `cstable` 上的 `(id, sex)` 列创建索引: ```sql CREATE INDEX cs_index2 ON cstable(id, sex); ``` 此索引适用于按 `id` 和 `sex` 排序或过滤的操作[^1]。 ##### (2) 基于函数的索引 为了支持特定类型的查询(如忽略大小写的搜索),可以在达式上创建索引。例如: ```sql CREATE INDEX upper_index3 ON cstable(UPPER(name)); SELECT * FROM cstable WHERE UPPER(name) = 'LJY'; ``` 这种索引能够显著提升涉及复杂计算的查询效率。 ##### (3) 唯一索引 唯一索引确保指定列的值重复。可以通过以下方式创建: ```sql CREATE UNIQUE INDEX idx_unique_name ON table_name(column_name); ``` ##### (4) 普通索引 对于需要强制唯一的场景,可创建普通索引: ```sql CREATE INDEX index_name ON table_name(column_name) TABLESPACE IDXTB; ``` 这里指定了存储索引空间为 `IDXTB`[^3]。 #### 3. 删除与维护索引 ##### (1) 删除索引 再需要某个索引时,可通过以下命令删除: ```sql DROP INDEX index_name; ``` ##### (2) 重建索引 频繁更新索引所在的列可能导致索引碎片化,影响性能。此时应考虑重建索引: ```sql ALTER INDEX index_name REBUILD; ``` #### 4. 查询优化器的工作原理 Oracle查询优化器负责生成最有效的执行计划。它有两种模式:基于规则的优化器(RBO)和基于成本的优化器(CBO)。自 Oracle 10g 版本起,RBO 已被废弃[^2]。 CBO 根据统计信息决定是否使用索引。因此,保持统计信息最新至关重要。可以使用以下命令收集统计信息: ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name'); ``` #### 5. 索引失效的原因及解决方法 某些情况下,即使存在索引也可能无法利用它们。常见的原因包括: - **IS NULL / IS NOT NULL 条件** 当条件为 `IS NULL` 或 `IS NOT NULL` 时,可能会导致索引失效[^4]。 - **在索引列上应用函数** 如下述 SQL 中的应用了 `SUBSTR` 函数,则会触发索引: ```sql SELECT col FROM tbl WHERE SUBSTR(name, 1, 3) = 'ABC'; ``` 解决方案是改用基于函数的索引[^4]。 - **LIKE 运算符的模糊匹配** 如果模式以 `%` 开头(如 `WHERE name LIKE '%ABC%'`),通常会导致全表扫描;但如果模式以固定字符串开头(如 `WHERE name LIKE 'ABC%'`),则可能命中索引[^4]。 #### 6. 性能调优案例分析 尽管索引有助于查询速度,但在实际环境中需注意潜在问题。例如,用户提到的一个例子显示,创建索引后反而使查询。这可能是由于以下几个因素造成的: - 查询优化器选择了次优路径; - 统计信息未及时更新; - 索引维护开销过高。 建议检查当前执行计划并调整 CBO 参数或重新收集统计信息[^2]。 --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值