`
deepinmind
  • 浏览: 450817 次
  • 性别: Icon_minigender_1
  • 来自: 北京
博客专栏
1dc14e59-7bdf-33ab-841a-02d087aed982
Java函数式编程
浏览量:41552
社区版块
存档分类
最新评论

为什么你加的索引不管用?

阅读更多

最近在办公室里,听见这么一段对话:

Bob:  Alice,我看了下你昨天告诉我的那个慢查询,我已经把你想要的那个索引给加上去。现在肯定OK了。

Alice:谢谢你,Bob。我马上确认一下…不对啊,还是很慢,看起来没起作用啊

Bob:还真是。看起来Oracle没有用上这个索引,你那个查询我加了/*+INDEX(...)*/索引提示也不行。真是不知道怎么回事了。

然后,问题仍然没有解决。Alice很头疼,因为她要加的特性没有按时发现,Bob也很发愁,因为他觉得Oracle居然没有正常工作。

这是个真事。

Bob忘了Oralce和NULL值的问题了

可怜的Bob忘了,Oralce是不会把NULL放到普通索引里的。你想一下这种情况:

CREATE TABLE person (
  id            NUMBER(38)   NOT NULL PRIMARY KEY,
  first_name    VARCHAR2(50) NOT NULL,
  last_name     VARCHAR2(50) NOT NULL,
  date_of_birth DATE             NULL
);
 
CREATE INDEX i_person_dob ON person(date_of_birth);
 




现在Bob认为有了这个索引什么事都解决了,因为他用这个查询验证了下,这个索引确实是好使的:


SELECT *
FROM   person
WHERE  date_of_birth > DATE '1980-01-01’;


(当然了,你不应该使用select *)

这个查询的执行计划看起来很正常:



----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON       |
|*  2 |   INDEX RANGE SCAN          | I_PERSON_DOB |
----------------------------------------------------



这是因为Bob的查询并不需要NULL作为I_PERSON_DOB索引的一部分。不幸的是,Alice的查询看起来大概是这样的:


SELECT 1 
FROM   dual
WHERE  DATE '1980-01-01' NOT IN (
  SELECT date_of_birth FROM person
);



实际上,Alice的查询是判断是不是有人是这天生日的。她的执行计划看起来会是这样的:


-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|*  1 |  FILTER            |        |
|   2 |   FAST DUAL        |        |
|*  3 |   TABLE ACCESS FULL| PERSON |
-------------------------------------



可以看到,她的查询进行了一个TABLE ACCESS FULL操作,索引被忽略了。为什么呢?很简单:


- Oracle不会把NULL值放到索引里。
- NOT IN(a, b, NULL, c, d)的结果是NULL。

不管你的这个日期’1980-01-01’在没在索引里,我们都得查看整个表来确认date_of_birth列中是否饮食一个NULL值。因为如果存在NULL值的话,Alice查询中这个NOT IN谓词的结果不是TRUE或FALSE,而是NULL


Alice可以用NOT EXISTS来解决这个问题

这个问题其实Alice自己就可以很容易搞定,她只需要把NOT IN换成NOT EXISTS就好了,这个谓词能够绕过SQL的特殊的三值逻辑


SELECT 1
FROM   dual
WHERE  NOT EXISTS (
  SELECT 1
  FROM   person
  WHERE  date_of_birth = DATE '1980-01-01'
);



现在新的查询语句的确能够得到一个最优的执行计划:


------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | SELECT STATEMENT  |              |
|*  1 |  FILTER           |              |
|   2 |   FAST DUAL       |              |
|*  3 |   INDEX RANGE SCAN| I_PERSON_DOB |
------------------------------------------



但问题仍然存在,因为该来的迟早还是会来的。Alice必须在写每条查询说一句的时候都时刻谨记这次教训。

对于Bob只需把这列设置成NOT NULL就好了

最佳的解决方案,其实就是把这列设置成NOT NULL就好了:


ALTER TABLE person 
MODIFY date_of_birth DATE NOT NULL;



有了这个约束后,NOT IN查询就和NOT EXISTS查询是一样的了,Bob和Alice又可以一起快乐地玩耍了。

如何找出这些捣乱的列?

很简单。下面这个查询可以列出所有存在一个NULL值的索引列。


SELECT
  i.table_name,
  i.index_name,
  LISTAGG(
    LPAD(i.column_position,  2) || ': ' || 
    RPAD(i.column_name    , 30) || ' '  ||
    DECODE(t.nullable, 'Y', '(NULL)', '(NOT NULL)'), 
    ', '
  ) WITHIN GROUP (ORDER BY i.column_position) 
    AS "NULLABLE columns in indexes"
FROM user_ind_columns i
JOIN user_tab_cols t
ON (t.table_name, t.column_name) = 
  ((i.table_name, i.column_name))
WHERE EXISTS (
  SELECT 1
  FROM user_tab_cols t
  WHERE (t.table_name, t.column_name, t.nullable) = 
       ((i.table_name, i.column_name, 'Y'       ))
)
GROUP BY i.table_name, i.index_name
ORDER BY i.index_name ASC;



你现在再用Bob和Alice的schema来执行下,上述查询的结果是:



TABLE_NAME | INDEX_NAME   | NULLABLE columns in indexes
-----------+--------------+----------------------------
PERSON     | I_PERSON_DOB | 1: DATE_OF_BIRTH (NULL)



现在你可以在你自己的schema上运行下这条查询语句,仔细地看一下结果中的那些列有没有必要允许NULL值的出现。应该有半数的情况下是不该出现NULL值的。加上一个NOT NULL约束后,你的程序的性能可能会得到质的提升!


原创文章转载请注明出处:http://it.deepinmind.com
英文原文链接 
2
0
分享到:
评论

相关推荐

    分区索引,本地索引,全局索引的区别

    以创建有前缀的本地索引为例: ```sql CREATE TABLE test (id NUMBER, data VARCHAR2(100)) PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (1000) TABLESPACE p1, PARTITION p2 VALUES LESS THAN ...

    mysql 索引与执行计划

    2. **唯一索引**:确保索引列中的值是唯一的,但允许值为空。 3. **复合索引**:包含多个列的索引,可用于同时基于多个字段进行查询。 4. **聚簇索引(聚集索引)**:这是一种特殊的数据存储方式,通常与主键相关联...

    一个有用的自动索引加色的JS下拉插件

    标题中的“一个有用的自动索引加色的JS下拉插件”揭示了这是一个JavaScript插件,主要用于下拉框(dropdown)的功能增强,特别是涉及到搜索和高亮显示匹配内容的特性。这种插件在网页交互设计中很常见,可以提高用户...

    [Oracle]如何在亿级记录表中创建索引

    **解释**:通过设置`set timing on`,可以在执行查询后显示执行时间,这对于估算索引创建的时间成本非常有用。 #### 三、创建索引的策略与步骤 基于上述信息,我们可以制定出创建索引的具体策略。 1. **确定索引列...

    InDesignCC2021 中文索引插件

    四级索引意味着可以为每个条目设置四个层次的分类,这种深度分类对于组织大量信息非常有用。例如,在一本科技类图书中,可以将一级索引设为大类别(如“计算机科学”),二级索引为子类别(如“编程语言”),三级...

    android 左右滑动加索引

    在Android开发中,实现左右滑动以及添加索引效果通常是为了提供用户友好的界面体验,让用户可以方便地浏览大量数据。在这个场景下,`Gallery`组件是一个很好的选择,它允许用户通过水平滚动来查看一系列的项目,同时...

    SQL Server 2000数据库中如何重建索引

    这在你想要改变索引的填充因子或者有其他特定需求时非常有用。 重建索引的过程不仅考虑了性能提升,还涉及数据的排序顺序。使用`DROP_EXISTING`或`DBCC DBREINDEX`时,可以保留现有的数据排序,从而避免了重新排序...

    ArcGIS教程:修改格网索引要素图层

    - **工具介绍**:首先使用添加字段工具为格网索引要素类添加一个新的字段(如UTM_Zone),然后使用计算UTM带工具填充该字段,以确定每个地图页面对应的UTM带号。 - **步骤**: - 打开添加字段工具,选择输入表...

    索引分词查看工具

    对于那些涉及复杂索引结构和高级搜索需求的项目来说,这一特性尤为有用。 总的来说,Luke 6.0作为一款强大的索引查看工具,它不仅简化了对Lucene索引的理解,而且提供了丰富的分析功能,对于优化搜索性能、调试索引...

    c# 索引器三

    - 索引器在类中定义为公共成员,并且通常具有get和set访问器,用于读取和修改值。 - 索引器的关键字是`this`,它作为返回类型前的修饰符。 2. 索引器的语法: ```csharp public 数据类型 this[参数列表] { get...

    oracle,数据库索引

    聚簇索引是按照数据的实际存储顺序排列的,而非聚簇索引则不遵循此规则。聚簇索引对于多行检索有优势,而非聚簇索引在单行查询时表现出色。 创建索引的主要好处包括: 1. **唯一性保证**:通过唯一性索引确保表中每...

    Sql2008_全文索引详解

    SQL Server 2008 全文索引是一种高级的搜索技术,专为在大量文本数据中快速执行复杂的自然语言搜索而设计。全文索引与传统的B-tree索引有着本质的区别,后者主要用于数字和短文本字段,而全文索引则是为了解决在长...

    sql server创建索引

    - 对于频繁查询但不经常更改的数据列非常有用。 ### 二、创建索引的语法 创建索引的基本语法如下: ```sql CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table_name (column_name) [WITH ...

    Oracle索引优化相关

    - **解决方案**:通过调整WHERE子句中的条件表达式,使得不直接对索引列使用函数。 ```sql SELECT empno, ename, deptno FROM emp WHERE hiredate (TO_DATE('01-MAY-81') + 0.9999); ``` 4. **比较不匹配的数据...

    数据库索引

    1. **为什么我的SQL慢**:常见的原因包括没有合适的索引、查询条件中使用了函数、全表扫描等。 2. **如何让自己的SQL写得更快**: - 创建有效的索引,特别是针对频繁查询的列。 - 避免在查询条件中使用函数。 - ...

    Oracle索引优化

    在Oracle数据库中,索引的种类繁多,包括B*Tree索引、反向索引、降序索引、位图索引和函数索引等。理解并正确使用这些索引类型对于提升查询性能至关重要。 首先,B*Tree索引是最常见的索引类型,适用于大部分常规...

    基于Jakarta Lucene的LIUS索引框架---下载不扣分,回帖加1分,欢迎下载,童叟无欺

    下载不扣分,回帖加1分,欢迎下载,童叟无欺 下面是LIUS的介绍: LIUS是一个基于Jakarta Lucene项目的索引框架。LIUS为Lucene添加了对许多文件格式的进行索引功能如: Ms Word,Ms Excel,Ms PowerPoint,RTF,PDF,XML,...

    关于数据库中的索引原理

    - **物理顺序**:由于聚簇索引确定了表中数据的物理顺序,因此对于频繁排序和范围查询非常有用。 ##### 2.2 聚簇索引的工作原理 假设我们有一个名为“员工”的表,并且以“姓名”作为聚簇索引键。那么这个表中的...

    数据库 索引及优化

    - **非聚簇索引(Nonclustered Index)**:非聚簇索引不改变数据的实际存储位置,而是通过索引表间接指向实际数据行。这种方式对于单行查询非常高效,因为它只需找到特定键值即可返回结果。 ##### 2.2 B树结构 B树是...

    Oralce如何选择合适的索引类型

    - **处理NULL值**:在查询涉及NULL值时,位图索引特别有用。标准索引通常会忽略NULL值,而位图索引则会记录这些值,使得查询“所有没有身份证号码”的员工信息等类似场景变得高效。 #### 建议二:创建索引时的限制...

Global site tag (gtag.js) - Google Analytics