복붙노트

[SQL] 어떻게 NULL 값은 데이터베이스 검색의 성능에 영향을합니까?

SQL

어떻게 NULL 값은 데이터베이스 검색의 성능에 영향을합니까?

우리의 제품에서 우리는 일반적인 검색 엔진을 가지고 있고, 검색 성능을 optimze하려고합니다. 쿼리에 사용 된 테이블의 많은 널 (null) 값을 허용합니다. 우리는 최적화 여부는 null 값을 허용하지 않도록 우리의 테이블을 재 설계해야 하는가?

우리의 제품은 오라클과 MS SQL 서버 모두에서 실행됩니다.

해결법

  1. ==============================

    1.오라클에서 NULL 값은 내가 인덱싱되지 않습니다. 이자형. 이 쿼리 :

    오라클에서 NULL 값은 내가 인덱싱되지 않습니다. 이자형. 이 쿼리 :

    SELECT  *
FROM    table
WHERE   column IS NULL

    항상 당신이 필요로하는 값을 포함하지 않는 인덱스 때문에 전체 테이블 스캔을 사용합니다.

    그 이상이 쿼리 :

    SELECT  column
FROM    table
ORDER BY
        column

    또한 전체 테이블 스캔을 사용하여 정렬 같은 이유로됩니다.

    당신의 가치는 본질적으로 NULL의를 허용하지 않는 경우, NOT NULL로 컬럼을 표시합니다.

  2. ==============================

    2.여분의 대답은 Quassnoi의 허용 대답에 데이비드 앨 드리지의 코멘트에 몇 가지 여분의 관심을 그립니다.

    여분의 대답은 Quassnoi의 허용 대답에 데이비드 앨 드리지의 코멘트에 몇 가지 여분의 관심을 그립니다.

    문 :

    사실이 아니다. 여기 리터럴 값 인덱스를 사용하여 이의 예이다 :

    SQL> create table mytable (mycolumn)
  2  as
  3   select nullif(level,10000)
  4     from dual
  5  connect by level <= 10000
  6  /

Table created.

SQL> create index i1 on mytable(mycolumn,1)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)

PL/SQL procedure successfully completed.

SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ *
  2    from mytable
  3   where mycolumn is null
  4  /

  MYCOLUMN
----------


1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  daxdqjwaww1gr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ *   from mytable  where mycolumn
is null

Plan hash value: 1816312439

-----------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE SCAN| I1   |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MYCOLUMN" IS NULL)


19 rows selected.

    당신이 볼 수 있듯이, 인덱스를 사용하고 있습니다.

    문안 인사, 롭.

  3. ==============================

    3.짧은 대답 : 예, 조건부!

    짧은 대답 : 예, 조건부!

    널 (null) 값과 성능의 주요 문제는 앞으로 조회와 함께 할 것입니다.

    당신이 널 (null) 값으로 테이블에 행을 삽입 할 경우,이 속한 자연 페이지에 배치됩니다. 그 기록을 찾고 모든 쿼리는 적절한 장소에 그것을 발견 할 것이다. 쉬운 지금까지 ....

    ...하지만하자의 페이지가 가득, 지금은 그 행이 다른 행 사이에 안기고 말한다. 여전히 잘 진행 ...

    ... 행이 업데이트되고 널 (null) 값은 지금 뭔가를 포함 할 때까지. DB를 엔진은 그것에 대해 뭔가를 가지고 있도록 행의 크기는 그것을 사용할 수있는 공간 이상으로 증가했다.

    할 수있는 서버에 대한 빠른 것은 다른에 해당 페이지 오프 행을 이동하고, 앞으로 포인터를 행의 항목을 대체하는 것입니다. 현재 위치를 찾기 위해 하나 하나가 행의 자연적인 위치를 찾기 위해, 그리고 : 불행하게도,이 쿼리가 수행되는 추가 검색이 필요합니다.

    따라서, 귀하의 질문에 짧은 대답은 해당 필드 nullable이 아닌 의지의 도움말 검색 성능을 만드는 예입니다. 종종 당신이 검색 기록에 널 필드가 null로 업데이트되어 발생하는 경우에 특히 그러하다.

    물론, 다른 처벌이 있습니다 (특히 I / O, 비록 작은 범위 지수 깊이) 또 다른 문제입니다 당신이 개념을 요구하는 분야에 널 (null)을 허용하지와 응용 프로그램 문제를 가지고 있지만, 헤이 다음 큰 데이터 세트와 연관 ​​:)

  4. ==============================

    4.당신의 열이 널 (null)을 포함하지 않는 경우이 열에 NOT NULL을 선언하는 것이 가장 좋습니다, 최적화는보다 효율적인 경로를 취할 수 있습니다.

    당신의 열이 널 (null)을 포함하지 않는 경우이 열에 NOT NULL을 선언하는 것이 가장 좋습니다, 최적화는보다 효율적인 경로를 취할 수 있습니다.

    당신이 당신의 열에서 널 (NULL)이있는 경우 그러나, 당신은 선택의 여지를 (그것이 해결보다 null 이외의 디폴트 값은 더 많은 문제를 만들 수 있음)가 없습니다.

    Quassnoi가 했나요으로, 널 (NULL)이 또는 모든 인덱스 열이 NULL이 수단있는 경우, 행이 인덱싱되지 않습니다 더 정확하게하기 위해, 오라클에 인덱싱되지 않습니다 :

    다음 스크립트는 인덱스 NULL 값에 대한 방법을 보여줍니다 :

    CREATE TABLE TEST AS 
SELECT CASE
          WHEN MOD(ROWNUM, 100) != 0 THEN
           object_id
          ELSE
           NULL
       END object_id
  FROM all_objects;

CREATE INDEX idx_null ON test(object_id, 1);

SET AUTOTRACE ON EXPLAIN

SELECT COUNT(*) FROM TEST WHERE object_id IS NULL;
  5. ==============================

    5.그 시험이 필요 말을하지만 다른 사람의 경험을 알고 좋은 것입니다. MS SQL 서버 내 경험에 의하면, 널 (null)이 대규모 성능 문제 (차이) 원인 할 수 있습니다. NOT NULL이이 설정되지 않은 경우 문 이상 25 분 만든 테이블의 관련 분야에 설정되었을 때 아주 간단한 테스트에서 지금 45 초 쿼리 수익을 보았다 (I 기다리고 포기하고 단지에서 피크를했다 예상 쿼리 계획).

    그 시험이 필요 말을하지만 다른 사람의 경험을 알고 좋은 것입니다. MS SQL 서버 내 경험에 의하면, 널 (null)이 대규모 성능 문제 (차이) 원인 할 수 있습니다. NOT NULL이이 설정되지 않은 경우 문 이상 25 분 만든 테이블의 관련 분야에 설정되었을 때 아주 간단한 테스트에서 지금 45 초 쿼리 수익을 보았다 (I 기다리고 포기하고 단지에서 피크를했다 예상 쿼리 계획).

    테스트 데이터는 1 개 백만 행이 윈도우 8.1에 8기가바이트 RAM (2GB를 사용하여 SQL 서버) / SQL 서버 2012 Enterprise Edition을 i5-3320 일반 HD에 62 개 임의 소문자 알파벳 문자로 구성되어 20 열 x와입니다. 그것은 현실적인 "나쁜"케이스를 테스트하기 위해 임의의 데이터 / 불규칙한 데이터를 사용하는 것이 중요합니다. 두 경우 모두 테이블에서 다시 한 이미 여유 공간의 적절한 양을했다 데이터베이스 파일에 30초 걸렸다 임의의 데이터로 다시로드.

    select count(field0) from myTable where field0 
                     not in (select field1 from myTable) 1000000

CREATE TABLE [dbo].[myTable]([Field0] [nvarchar](64) , ...

 vs

CREATE TABLE [dbo].[myTable]([Field0] [nvarchar](64) not null,

    성능상의 이유로 모두 테이블 옵션 DATA_COMPRESSION = 페이지 집합이 있고 다른 모든 기본값했다. 어떤 인덱스 없습니다.

    alter table myTable rebuild partition = all with (data_compression = page);

    널 (null)을 가지고하지 않으면 메모리 최적화 테이블에있는 내가 특별히 분명 빠른이 특정의 경우 데이터에 널 (null)을 갖고,에 NOT NULL을 사용하지 찬성 대규모로 나타나는 것입니다 무엇을 할 것인가 그러나 SQL 서버를 사용하고 있지 않다위한 요구 사항입니다 테이블을 만들 수 있습니다.

    이초이 테이블 반환에 같은 형태의 후속 쿼리는 내가 표준 기본 통계를 가정하고 아마도 잘 작동하는 메모리로 (1.3GB) 테이블 적합을 가진 것 때문에. 즉

    select count(field19) from myTable where field19 
                       not in (select field18 from myTable) 1000000

    null의 경우를 다루는 갖는 따로 가지고 있지 널 (null)과하지에 또한 쿼리 훨씬 더 간단, 경향이 매우 일반적으로 빠르고 짧은 덜 오류가 있습니다. 가능한 모든에서 가장 적어도 MS SQL 서버에 일반적으로 널 (null)을 방지하기 위해 경우에 그들은 명시 적으로 요구되는 합리적 해결책 밖으로 일을하지 않는 한.

    새 테이블에서 시작하여 10m 행이 최대 크기 조정 / 13기가바이트 같은 쿼리는 하드웨어와 사용없이 인덱스를 고려 매우 존경 12 분 정도 걸립니다. 정보 쿼리가 완전히이었다 들어 IO는 IO가 / s의 60메가바이트 / s의 20메가바이트 사이에 마우스를 함께 바인딩됩니다. 동일한 쿼리의 반복 구 분했다.

  6. ==============================

    6.쿼리 "NOT IN"할 때 Null 허용 필드는 성능에 큰 영향을 미칠 수 있습니다. null로 설정된 모든 인덱스 필드 행은 B-tree 인덱스에서 인덱스되지 않기 때문에, 오라클은 인덱스가 존재하는 경우에도, 널 entires를 확인하기 위해 전체 테이블 스캔을 수행해야합니다.

    쿼리 "NOT IN"할 때 Null 허용 필드는 성능에 큰 영향을 미칠 수 있습니다. null로 설정된 모든 인덱스 필드 행은 B-tree 인덱스에서 인덱스되지 않기 때문에, 오라클은 인덱스가 존재하는 경우에도, 널 entires를 확인하기 위해 전체 테이블 스캔을 수행해야합니다.

    예를 들면 :

    create table t1 as select rownum rn from all_objects;

create table t2 as select rownum rn from all_objects;

create unique index t1_idx on t1(rn);

create unique index t2_idx on t2(rn);

delete from t2 where rn = 3;

explain plan for
select *
  from t1
 where rn not in ( select rn
                     from t2 );

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50173 |   636K|  3162   (1)| 00:00:38 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 50205 |   637K|    24   (5)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   | 45404 |   576K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

    쿼리는 T1의 각 행에 대해 (T2)의 전체 테이블 스캔을 할 수있다, 그래서 널 (null) 값을 확인한다.

    우리는 필드에 널 (NULL) 입력이 불가능 할 경우 지금,이 인덱스를 사용할 수 있습니다.

    alter table t1 modify rn not null;

alter table t2 modify rn not null;

explain plan for
select *
  from t1
 where rn not in ( select rn
                     from t2 );

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  2412 | 62712 |    24   (9)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |        |  2412 | 62712 |    24   (9)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | T1_IDX | 50205 |   637K|    21   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| T2_IDX | 45498 |   577K|     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
  7. ==============================

    7.그들은 성능에 영향을 미칠 수 있기 때문에 널 (null) 사용 여부의 문제는 분산 데이터베이스 설계의 역할을 그 중 하나입니다. 당신은 성능에 대한 비즈니스 요구의 균형을해야합니다.

    그들은 성능에 영향을 미칠 수 있기 때문에 널 (null) 사용 여부의 문제는 분산 데이터베이스 설계의 역할을 그 중 하나입니다. 당신은 성능에 대한 비즈니스 요구의 균형을해야합니다.

    필요할 경우 널 (null)이 사용되어야한다. 예를 들어, 당신은이 날짜와 테이블에 종료 날짜를 시작할 수 있습니다. 당신은 종종 레코드가 생성 된 시점에서 종료 날짜를 모르는 것입니다. 그들은 성능에 영향을 미칠 수 있는지 따라서 당신은 데이터에 넣어 단순히 거기인지로 널 (null)을 허용해야합니다. 그러나, 데이터 필수가, 비즈니스 규칙, 기록이 작성 될 때이있을 경우, 당신은 허용해서는 안 널 (null). 이 개선 것입니다 성능은 간단 비트 코딩하고 있는지 데이터 무결성 보존되어 있는지 확인합니다.

    당신은 당신이 더 이상 허용 널 (null)로 변경하고자하는 데이터를 기존의 경우, 당신은 그 변화의 영향을 고려해야합니다. 첫째, 당신은 무엇을 당신이 현재 null의 기록에 투입해야 할 가치를 알 수 있습니까? 둘째, 당신은 (당신이 그들을 더 이상 필요 확인하지하는 경우 그래서, 당신은 코드를 변경해야합니다, 이러한 것들을 성능 저하)를 업데이트해야 ISNULL 또는 병합을 사용하는 코드를 많이해야합니까? DO 당신은 기본값을해야합니까? 당신은 정말 하나를 할당 할 수 있습니까? 그렇지 않다면은 삽입 또는 갱신 코드 휴식의 일부는이 분야가 더 이상 null이 될 수 있다는 것을 고려하지되지 않습니다. 때때로 사람들은 그들이 널 (null)을 제거 할 수 있도록 잘못된 정보를 넣어 것입니다. 그래서 지금 가격 필드의 요구는 '알 수없는'와 같은 진수 값과 일을 포함하기 때문에 제대로 소수점 데이터 유형이 될 수 없습니다 그리고 당신은 계산을하기 위해 길이의 모든 종류의로 이동합니다. 이것은 종종 잘못되었거나 만든 널보다 더 같은 성능 문제를 만듭니다. 게다가 당신은 모든 코드 어디 이제까지 당신이 제기되고 널 (null)에 refernce를 사용하거나없는 널 (null)을 통해 이동해야합니다, 당신은 제외 다시 작성해야하거나 데이터가 허용되지 않습니다 변경할 수 없기에 넣어 가능한 나쁜 값 누군가를 기반으로 포함 null이 될 수 있습니다.

    나는 클라이언트 데이터에서 데이터 가져 오기를 많이 할 모든 시간을 우리는 널 (null)을 허용해야 할 몇 가지 필드, 우리는 우리가 우리의 시스템에 가져 오기 전에 요구 사항이 정리 될 수 있음을 쓰레기 데이터를 얻을하지 않는 파일을 얻을. 이메일은 다음 중 하나입니다. 종종 데이터 입력이 값을 알고되지 않고 사용자가 여기에 아무 것도 입력 할 수 있습니다 그것은 일반적으로 문자열 데이터의 몇 가지 유형입니다. 우리는 "나도 몰라"전자 메일을 가져옵니다 물건을 찾아 이동합니다. 힘든 사실 "나도 몰라"으로 이메일을 보내려고합니다. 시스템이 @ 기호, 우리는 " 'I@dont.know을 얻을 것의 실존과 같은에 대한 유효한 이메일 주소와 검사가 필요하기 어떻게 이런 쓰레기 데이터는 데이터의 사용자에게 유용합니다?

    널 (null)로 성능 문제의 일부는 nonsargable 쿼리를 작성의 결과이다. 때로는 where 절을 정리보다는 성능을 향상시킬 수 있습니다 필요한 널 (null)을 제거.

  8. ==============================

    8.내 경험에 NULL은 유효한 값이며 일반적으로 "모른다"를 의미한다. 당신이 다음 모르는 경우 정말 열의 일부 기본값을 구성하거나 일부 NOT NULL 제약 조건을 적용하려고 무의미하다. NULL은 특정의 경우가 발생합니다.

    내 경험에 NULL은 유효한 값이며 일반적으로 "모른다"를 의미한다. 당신이 다음 모르는 경우 정말 열의 일부 기본값을 구성하거나 일부 NOT NULL 제약 조건을 적용하려고 무의미하다. NULL은 특정의 경우가 발생합니다.

    NULL을위한 진정한 도전은 조금 검색 복잡하다. 예를 들어 당신은 WHERE COLUMN_NAME IN (NULL, '값 1', '값 2')을 말할 수 없습니다.

    당신이 당신의 열을 많이 찾거나 특정 컬럼이 널 (NULL)이 많이 포함되어 개인적으로 만약 당신이 당신의 데이터 모델을 다시 방문 할 수 있습니다 생각합니다. 어쩌면 그 널 열은 자식 테이블에 넣을 수있다? 예를 들면 : 그것의 이름, homePhone를, 휴대 전화, faxno, worknumber, emergencynumber 등이 ... 당신 만 채우기 하나 또는 그 두 가지와 더 나은를 정상화 할 수 있습니다 전화 번호가있는 테이블.

    당신이해야 할 것은 단계 뒤로하고 데이터가 액세스되는 방법을 참조하십시오. 이 값을 가지고 있어야 열 수 있습니까? 이것은 어떤 경우에 값이있는 경우에만하는 열인가? 이 많은 쿼리 할 것이다 열인가?

    9. from https://stackoverflow.com/questions/1017239/how-do-null-values-affect-performance-in-a-database-search by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 안드로이드에서 준비된 문으로 쿼리?  (0) 2020.06.03
[SQL] 오라클에 삽입하여 생성 된 시퀀스 ID를 입수  (0) 2020.06.03
[SQL] 다른 테이블의 데이터를 하나 개의 테이블에서 업데이트 된 행이 각각 동일하고 하나의 열에 기초  (0) 2020.06.03
[SQL] PL / SQL 블록 문제 : 없음 데이터 오류를 발견  (0) 2020.06.03
[SQL] 어떻게 SQL 단지 하나의 필드에 중복없이 레코드를 선택하려면?  (0) 2020.06.03
,

티스토리툴바