mysql 加索引范围 mysql中添加索引

转载

Python数据分析 2024-07-30 11:42:48

文章标签 mysql 加索引范围 java mysql 数据库 oracle 文章分类 MySQL 数据库

mysql 添加索引索引

Bob忘记了Oracle和NULL

可怜的Bob忘记了（或不知道） Oracle没有在“普通”索引中放入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索引的一部分。不幸的是，爱丽丝的查询看起来更像这样（简化版本）：

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

即使我们DATE '1980-01-01'值不在索引中，我们仍然必须检查整个表以查看date_of_birth列中是否包含单个NULL值。因为，如果有一个NULL值，在NOT IN艾丽丝的查询谓词永远不会产生TRUE或FALSE ，但NULL 。

爱丽丝可以使用NOT EXISTS解决此问题

Alice可以自己轻松解决问题，方法是用NOT IN NOT EXISTS代替NOT IN ，这是不受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可以再次成为朋友。

要点：如何找到“坏”列？

这很容易。以下有用的查询列出了其中至少具有一个可空列的所有索引。

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的模式运行时，上面的查询产生：

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

现在在您自己的模式上使用此查询，并仔细检查结果，仔细评估是否确实需要将该列保持可空状态。在50％的情况下，您没有。通过添加NOT NULL约束，您可以极大地加快应用程序的速度！