형변환과 인덱스

형변환과 인덱스(Index)

1. 형변환: ORACLE 설명

• 명시적 형변환: 형변환 함수 사용
  • TO_NUMBER(문자열)
  • TO_CHAR(숫자 혹은 날짜, [FORMAT])
  • TO_DATE(문자열, FORMAT)
    -암시적 형변환: DBMS가 자동으로 형변환

    SELECT
      *
    FROM
      EMP
    WHERE
      EMPNO = '100'
    ;

  • EMPNO가 숫자형 데이터 타입이라고 가정
  • 비교값을 작은 따옴표로 묶었기 때문에 '100'은 문자형임
  • 이 경우 EMPNO를 ORACLE이 자동으로 TO_CHAR(EMPNO)로 변환해 데이터 타입을 일치시킴
• 암시적 형변환과 인덱스
  • 인덱스: 데이터를 빠르게 조회하기 위해 인덱스 키를 기준으로 정렬해 놓은 데이터
  • 인덱스는 기본적으로 변형이 발생하면 사용할 수 없음
    • 예외도 있음
  • 따라서 인덱스가 있어도 인덱스 컬럼에 형변환이 발생하면 인덱스를 사용할 수 없다.

     SELECT
       *
     FROM
       EMP
     WHERE
       EMPNO = '100'
     ;

  • 만약 EMPNO가 숫자형 데이터 타입이고 기본키라면 이 경우 자동적으로 암시적 형변환이 되므로 인덱스를 사용할 수 없게 됨
    • 아래와 같이 해결 가능

        SELECT
          *
        FROM
          EMP
        WHERE
          EMPNO = TO_NUMBER('100')
        ;

2. 인덱스(Index)

• 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하는 대신 데이터의 읽기 속도를 높여 테이블의 동작 속도(조회)를 높여주는 자료구조
  • 책 뒷편 '찾아보기'가 인덱스의 역할과 동일함
• 인덱스가 없더라도 데이터베이스를 작동하는 건 문제 없지만 데이터베이스의 크기가 억대 단위로 크면 클수록 인덱스가 반드시 필요해짐
  • 인덱스는 데이터베이스의 성능(속도)를 크게 좌우하는 요소이기 때문
• 그러나 인덱스 자체 역시 하나의 데이터 덩어리이기 때문에 잘못 사용할 경우 성능이 오히려 크게 떨어질 수 있음
  • 데이터 변경 작업 (INSERT, UPDATE, DELETE)시 성능이 나빠질 수 있음
  • 특히 INSERT에 큰 영향

Index를 타지 않는 쿼리

인덱스 컬럼을 변형하는 경우

• SQL문을 사용할 때 인덱스 컬럼을 변형시키면 데이터베이스가 인덱스를 사용하지 않는다.

SELECT
  *
FROM
  users
WHERE
  LOWER('first_name') = 'hong'
;

SELECT
  *
FROM
  table
WHERE
  ages + 10 = 30 -- 인덱스 사용 불가
;

SELECT
  *
FROM
  table
WHERE
  age = 20 + 10 -- 인덱스 사용 가능
;

NULL 조건의 사용

• NULL 조건을 사용하면 Table Full Scan이 발생한다.

SELECT
  *
FROM
  users
WHERE
  ages IS NULL -- 인덱스 사용 불가
;

SELECT
  *
FROM
  users
WHERE
  ages IS NOT NULL -- 인덱스 사용 불가
;

SELECT
  *
FROM
  users
WHERE
  age > 0 -- 인덱스 사용 가능
;

SELECT
  *
FROM
  users
WHERE
  ages > ' ' -- 인덱스 사용 가능
;

부정형의 사용

• NOT일 경우 무조건 인덱스를 안 타는 건 아님
  • 일반적으로 NOT에 사용된 값이 아닌 데이터 비율이 높은 경우가 많이 때문에 인덱스를 타지 않음

SELECT
  *
FROM
  users
WHERE
  age != 31 -- 인덱스 사용 불가
;

SELECT
  *
FROM
  users
WHERE
  ages < 31
  AND ages > 31 -- 인덱스 사용 가능
;

전체 범위를 설정하고 LIKE문을 사용할 때

• '%'가 앞에 붙을 때에는 인덱스를 타지 않음

SELECT
  *
FROM
  users
WHERE
  first_name
  LIKE '%hong%' -- 인덱스 사용 불가
;

SELECT
  *
FROM
  users
WHERE
  first name
  LIKE 'hong%' -- 인덱스 사용 가능 
;

IN 연산자 사용할 경우

• IN의 경우 항상 인덱스를 타지 않는 것은 아님
  • IN에 포함된 데이터들의 비율이 매우 높아 Table Full Scan을 하는 것이 낫다고 데이터베이스가 판단하면 인덱스를 타지 않음
• MySQL 5.7 버전 이상부터는 range_optimizer_max_mem_size 값이 0으로 되어 있음녀 인덱스를 타고, 값이 있으면 해당 값보다 메모리를 더 사용하면 Table Full Scane 또는 이외의 인덱스를 탐

SELECT
  *
FROM
  users
WHERE
  ages
  IN (20, 21, 22)
;

복합 인덱스에서 첫 인덱스가 첫 조건으로 적용하지 않았을 경우

• 복합 인덱스에서 인덱스의 순서가 name, age인 경우 name → age의 순서로 조건을 걸어야 인덱스를 탈 수 있다.

SELECT
  *
FROM
  users
WHERE
  name = 'hong'
  AND age = 30 -- 인덱스 사용 가능
;

SELECT
  *
FROM
  users
WHERE
  age = 30
  AND name = 'hong'
;

인덱스 컬럼의 내부적인 데이터 변환

• 문자값 데이터타입을 갖는 컬럼에 '값'
  → 값을 하지 말고 정확한 데이터 타입을 넣어야 인덱스를 탈 수 있다.

SELECT
  *
FROM
  users
WHERE
  age = '30' -- 인덱스 사용 불가
;

SELECT
  *
FROM
  users
WHERE
  age = TO_NUMBER('30') -- 인덱스 사용 가능
;