索引总结(四)

本文深入讲解了复合索引的概念、建立原则及优化方法。复合索引由两个或多个列组成,能够有效缩小搜索范围,提高查询效率。文章还探讨了复合索引的排序优化原理及其在实际查询中的应用。

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

6. 什么是复合索引

6.1 复合索引定义

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。

利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。

所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。

如:建立 姓名、年龄、性别的复合索引。

起作用的复合索引查询:

55.png

6.2 复合索引的建立原则:

如果您很可能仅对一个列多次执行搜索,则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索,则应该创建另一个仅包含第二列的索引。

如上图所示,如果查询中需要对年龄和性别做查询,则应当再新建一个包含年龄和性别的复合索引。

包含多个列的主键始终会自动以复合索引的形式创建索引,其列的顺序是它们在表定义中出现的顺序,而不是在主键定义中指定的顺序。在考虑将来通过主键执行的搜索,确定哪一列应该排在最前面。

请注意,创建复合索引应当包含少数几个列,并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值,其后果是内存溢出和性能降低。

6.3 复合索引对排序的优化:

复合索引只对和索引中排序相同或相反的order by 语句优化。

在创建复合索引时,每一列都定义了升序或者是降序。如定义一个复合索引:

CREATE INDEX idx_example
ON table1 (col1 ASC, col2 DESC, col3 ASC)

其中 有三列分别是:col1 升序,col2 降序, col3 升序。现在如果我们执行两个查询

1Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC 和索引顺序相同

2Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC 和索引顺序相反

查询12 都可以别复合索引优化。

如果查询为:

Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC 排序结果和索引完全不同时,此时的查询不会被复合索引优化。

查询优化器在在where查询中的作用:

1. 如果一个多列索引存在于 Col1 Col2 上,
则以下语句:Select * from table where col1=val1 AND col2=val2 查询优化器会试图通过决定哪个索引将找到更少的行。之后用得到的索引去取值。

1. 如果存在一个多列索引,任何最左面的索引前缀能被优化器使用。所以联合索引的顺序不同,影响索引的选择,尽量将值少的放在前面。
如:一个多列索引为 (col1 col2 col3)
那么在索引在列 (col1) (col1 col2) (col1 col2 col3) 的搜索会有作用。
SELECT * FROM tb WHERE col1 = val1

SELECT * FROM tb WHERE col1 = val1 and col2 = val2

SELECT * FROM tb WHERE col1 = val1 and col2 = val2 AND col3 = val3

2. 如果列不构成索引的最左面前缀,则建立的索引将不起作用。
如:
SELECT * FROM tb WHERE col3 = val3

SELECT * FROM tb WHERE col2 = val2

SELECT * FROM tb WHERE col2 = val2 and col3=val3

3. 如果一个 Like 语句的查询条件不以通配符起始则使用索引。
如:% %% 不使用索引。
% 使用索引。

索引的缺点:

1. 占用磁盘空间。

2. 增加了插入和删除的操作时间。一个表拥有的索引越多,插入和删除的速度越慢。如 要求快速录入的系统不宜建过多索引。

7. 下面是一些常见的索引限制问题

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

下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描

select * from dept where staff_num <> 1000;

但是开发中的确需要这样的查询,难道没有解决问题的办法了吗?

有!

通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。

select * from dept shere staff_num < 1000 or dept_id > 1000;

2、使用 is null is not null

使用 is null is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。

解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)

3、使用函数

如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引:

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

4、比较不匹配的数据类型

比较不匹配的数据类型也是难于发现的性能问题之一。

下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。

SQL语句改为如下形式就可以使用索引

select * from dept where dept_id = '900198';

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22934571/viewspace-1042719/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/22934571/viewspace-1042719/

<think>嗯,用户让我介绍一下Oracle中的索引总结。首先,我需要回忆一下Oracle数据库索引的相关知识,确保信息准确。索引数据库中非常重要,能显著提高查询速度,但不同类型的索引适用场景不同,这点需要详细说明。 用户可能想了解索引的基本概念,比如B树索引、位图索引、函数索引等。我应该先介绍每种索引的结构和原理,然后说明它们的优缺点以及适用情况。比如,B树索引适合高基数列,而位图索引适合低基数列,像性别这样的字段。 还要注意索引的创建和管理,比如如何创建、维护,以及索引的存储参数。用户可能关心如何选择合适的索引策略,所以需要给出一些实际例子,比如在OLTP和OLAP环境中的不同选择。 另外,索引的维护也是重点,比如重建索引、监控索引使用情况。这部分用户可能容易忽略,但实际应用中很重要,避免索引过多导致性能下降。 可能需要提到组合索引的使用技巧,比如最左前缀原则,以及索引覆盖查询的优势。还有函数索引的使用场景,比如对字段进行函数操作后的查询优化。 用户可能有疑问为什么有时候索引不生效,可以简要说明索引失效的常见原因,比如隐式类型转换、使用函数导致索引无效等。 最后,总结一下如何根据实际业务需求选择合适的索引类型,并给出一些最佳实践,比如定期分析索引使用情况,删除无用索引等。确保回答结构清晰,分点说明,方便用户理解。</think>好的,我来总结一下 Oracle 数据库中的索引核心知识点,分以下几个部分: --- ### 一、索引基础概念 **作用**:加速数据检索的数据库对象(类似书籍目录) **代价**:占用存储空间,降低DML操作速度(需维护索引结构) --- ### 二、常用索引类型 #### 1. B-Tree 索引(平衡树索引) - **结构**:根节点 → 分支节点 → 叶子节点(存储ROWID和键值) - **特点**: - 适合高基数(唯一值多)的列 - 支持精确查找和范围查询 - **语法**: ```sql CREATE INDEX idx_name ON table(column); ``` #### 2. 位图索引(Bitmap Index) - **结构**:用位图存储键值与ROWID的映射 - **特点**: - 适合低基数(重复值多)的列(如性别、状态字段) - 适合OLAP场景 - 锁粒度大,不适合高并发DML - **语法**: ```sql CREATE BITMAP INDEX idx_name ON table(column); ``` #### 3. 函数索引 - **特点**:对列应用函数后的结果建立索引 - **典型场景**:`WHERE UPPER(name) = 'JOHN'` - **语法**: ```sql CREATE INDEX idx_name ON table(UPPER(column)); ``` #### 4. 组合索引(复合索引) - **规则**: - 遵循最左前缀原则(查询必须使用最左侧列) - 列顺序影响查询效率(高频查询列放左侧) - **语法**: ```sql CREATE INDEX idx_name ON table(col1, col2); ``` --- ### 三、特殊索引类型 1. **反向键索引(Reverse Key)** - 反转键值,缓解热块争用(如序列主键的索引) - 语法:`CREATE INDEX idx_name ON table(column) REVERSE;` 2. **降序索引** - 支持`ORDER BY column DESC`优化 - 语法:`CREATE INDEX idx_name ON table(column DESC);` 3. **索引组织表(IOT)** - 表数据按索引结构存储,适用于主键频繁访问的场景 --- ### 索引使用策略 #### 1. 创建原则 - 在WHERE/JOIN/ORDER BY涉及的列上创建索引 - 小表(数据量 < 总行数5%)通常不需要索引 - 避免在频繁更新的列上过多建索引 #### 2. 索引失效场景 - 使用`IS NULL`/`IS NOT NULL` - 列参与函数运算(如`TO_CHAR(create_time)=...`) - 隐式类型转换(如字符串列使用数字查询) - 使用`LIKE '%value%`前导通配符 #### 3. 索引维护 - **重建索引**: ```sql ALTER INDEX idx_name REBUILD; ``` - **监控使用情况**: ```sql SELECT * FROM v$object_usage WHERE index_name = 'IDX_NAME'; ``` --- ### 五、性能优化要点 1. **索引覆盖查询**:SELECT只查询索引列时,无需回表 2. **索引跳跃扫描**(Oracle 9i+):组合索引跳过前导列查询 3. **索引压缩**:减少存储空间,语法:`CREATE INDEX ... COMPRESS` --- ### 六、实践建议 1. 定期使用`ANALYZE INDEX ... VALIDATE STRUCTURE`检查索引健康度 2. OLTP系统避免过多位图索引 3. 使用`DBMS_STATS`收集统计信息优化执行计划 需要具体案例分析或某个知识点的详细解释,可以随时告诉我! 📊
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值