[Database] 인덱스를 타지 않는 쿼리 10가지 경우 정리

인덱스(Index)는 B-Tree 구조 또는 기타 정렬된 구조를 기반으로 빠르게 데이터를 검색하는 도구입니다. 하지만 특정 조건이나 쿼리 작성 방식에 따라 인덱스가 무시되고 Full Table Scan이 발생합니다. 이러한 상황을 정확히 이해하고, 이를 피하는 것이 데이터베이스 최적화의 핵심입니다.

1. 함수나 연산자를 사용하는 경우

인덱스가 설정된 컬럼에 함수나 연산자를 사용하면 인덱스를 타지 않고 전체 스캔(Full Scan)이 일어납니다.

• 예제 코드

-- 함수 사용
SELECT * FROM ExampleTable WHERE UPPER(Name) = 'JOHN';

-- 연산자 사용
SELECT * FROM ExampleTable WHERE Age + 1 = 30;

1.1 Full Scan이 일어나는 이유

• 인덱스는 정렬된 키 값을 기반으로 효율적으로 탐색합니다.
• 하지만 함수나 연산이 사용되면 컬럼의 값이 변형되므로 인덱스에 저장된 원본 값과 일치하지 않게 됩니다.
• 데이터베이스는 변형된 값을 인덱스에서 찾을 수 없기 때문에 전체 데이터를 스캔하게 됩니다.

1.2 해결 방법

• 컬럼에 변형을 가하지 말고 조건을 변경합니다.

SELECT * FROM ExampleTable WHERE Name = LOWER('john'); -- 원본 컬럼 그대로 사용

• 컬럼에 인덱스를 걸 때 함수 기반의 함수 인덱스(Function-based Index)를 사용할 수도 있습니다.

2. LIKE 연산자에서 %가 앞쪽에 사용된 경우

LIKE연산에서 %가 문자열 앞쪽에 사용되면 B-Tree 구조를 활용할 수 없어 Full Scan이 발생합니다.

• 예제 코드

-- 인덱스 사용 가능
SELECT * FROM ExampleTable WHERE Name LIKE 'A%';

-- 인덱스 사용 불가능
SELECT * FROM ExampleTable WHERE Name LIKE '%A';
SELECT * FROM ExampleTable WHERE Name LIKE '%A%';

2.1 Full Scan이 일어나는 이유

• 인덱스는 B-Tree 구조로 데이터를 오름차순 또는 내림차순으로 정렬합니다.
• %가 앞쪽에 있으면 문자열의 시작점을 찾을 수 없어 인덱스를 사용할 수 없습니다.

2.2 해결 방법

• %를 뒤쪽에만 사용하도록 검색 패턴을 조정합니다.
• FULL TEXT INDEX를 사용하면 앞쪽에 %가 있는 패턴도 최적화할 수 있습니다.

3. OR 조건을 사용하는 경우

OR 조건을 사용하면 인덱스 최적화가 어려워 Full Table Scan이 발생할 수 있습니다.

• 예제 코드

SELECT * FROM ExampleTable WHERE Name = 'John' OR Age = 30;

3.1 Full Scan이 일어나는 이유

• OR 조건은 각 조건의 결과를 모두 검사한 후 병합해야 하므로 여러 인덱스를 동시에 활용하기 어렵습니다.
• 옵티마이저가 단일 인덱스를 선택할 수 없고, 결국 Full Table Scan을 선택할 수 있습니다.

3.2 해결 방법

• UNION ALL을 사용하여 인덱스를 타도록 쿼리를 분리합니다.

SELECT * FROM ExampleTable WHERE Name = 'John'
UNION ALL
SELECT * FROM ExampleTable WHERE Age = 30;

4. NULL 또는 NOT NULL을 조건으로 사용하는 경우

IS NULL 또는 IS NOT NULL 조건은 인덱스가 NULL 값을 저장하지 않는 경우가 있어 Full Scan이 발생할 수 있습니다.

• 예제 코드

SELECT * FROM ExampleTable WHERE Name IS NULL;
SELECT * FROM ExampleTable WHERE Age IS NOT NULL;

4.1 Full Scan이 일어나는 이유

• NULL 값은 인덱스에 저장되지 않는 경우가 많습니다. (데이터베이스 엔진에 따라 다름)
• 결과적으로 NULL을 찾거나 제외하기 위해서는 모든 데이터를 확인해야 합니다.

4.2 해결 방법

• NULL 대신 특정 기본값을 사용하도록 테이블을 설계합니다.

CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) DEFAULT 'N/A'
);

5. 컬럼의 자료형이 다른 경우 (암시적 형 변환)

컬럼과 조건 값의 자료형이 다를 때 데이터베이스는 형 변환을 수행하게 되고 Full Table Scan이 발생합니다.

• 예제 코드

-- 자료형이 다른 경우
SELECT * FROM ExampleTable WHERE Age = '30'; -- Age 컬럼은 INT 타입

5.1 Full Scan이 일어나는 이유

• 인덱스는 자료형에 맞는 정렬된 값을 기반으로 작동합니다.
• 형 변환을 수행하면 데이터베이스는 인덱스를 무시하고 값을 일일이 비교해야 합니다.

5.2 해결 방법

• 조건 값의 자료형을 컬럼의 자료형과 일치시킵니다.

SELECT * FROM ExampleTable WHERE Age = 30;

6. 부정형 조건 사용 (NOT, !=)

!= 또는 NOT과 같은 부정형 조건을 사용하면 인덱스를 활용하기 어렵습니다.

• 예제 코드

SELECT * FROM ExampleTable WHERE Status != 'Active';
SELECT * FROM ExampleTable WHERE NOT Status = 'Inactive';

6.1 Full Scan이 일어나는 이유

• 인덱스는 특정 범위를 효율적으로 탐색합니다.
• 하지만 부정형 조건은 나머지 값을 모두 확인해야 하므로 인덱스를 활용하지 못합니다.

6.2 해결 방법

• 부정형 조건을 긍정형 조건으로 변환합니다.

SELECT * FROM ExampleTable WHERE Status = 'Inactive';

7. 복합 인덱스의 순서를 정확히 사용하지 않은 경우

복합 인덱스는 컬럼 순서대로 조건이 사용되어야 인덱스를 활용할 수 있습니다.

• 예제 코드

CREATE INDEX idx_example ON ExampleTable (Age, Category);

-- 인덱스 사용 불가능
SELECT * FROM ExampleTable WHERE Category = 'A' AND Age = 25;

-- 인덱스 사용 가능
SELECT * FROM ExampleTable WHERE Age = 25 AND Category = 'A';

7.1 Full Scan이 일어나는 이유

• 복합 인덱스는 왼쪽 필드부터 순서대로 조건을 충족해야 최적화됩니다.
• 인덱스의 선두 컬럼이 조건에 없으면 인덱스가 무시됩니다.

7.2 해결 방법

• 조건에 인덱스의 선두 컬럼부터 사용합니다.

8. IN 연산자에서 항목이 너무 많은 경우

IN 절에 항목이 너무 많으면 인덱스 대신 Full Scan이 발생할 수 있습니다.

• 예제 코드

SELECT * FROM ExampleTable WHERE ID IN (1, 2, 3, ..., 1000);

8.1 Full Scan이 일어나는 이유

• IN절의 항목이 많으면 여러 번의 인덱스 스캔이 필요하고, 비용이 Full Scan보다 높아질 수 있습니다.

8.2 해결 방법

• JOIN으로 대체하거나 값을 나눠서 쿼리합니다.

SELECT *
FROM ExampleTable
JOIN (SELECT 1 AS ID UNION ALL SELECT 2 AS ID) AS ids
ON ExampleTable.ID = ids.ID;

9. 테이블 전체 레코드를 반환하는 경우

SELECT *로 테이블 전체 레코드를 반환하는 쿼리는 인덱스를 사용할 필요가 없기 때문에 데이터베이스는 Full Table Scan을 수행합니다.

9.1 Full Scan이 일어나는 이유

• 인덱스는 특정 조건에 따라 데이터를 빠르게 탐색하기 위한 구조입니다.
• 하지만 SELECT *는 조건 없이 모든 레코드를 반환해야 하므로 인덱스를 사용할 필요가 없습니다.
• 인덱스를 사용해도 모든 데이터를 접근해야 하므로 오히려 불필요한 비용이 추가될 수 있습니다.
• 따라서 데이터베이스 옵티마이저는 인덱스 대신 Full Table Scan을 수행합니다.

9.2 해결 방법

1. 불필요한 컬럼 조회를 줄이기
   • 필요한 컬럼만 명시적으로 조회하면 성능이 개선될 수 있습니다.

SELECT ID, Name FROM ExampleTable;

2. LIMIT 절 사용
   • 레코드 수를 제한하는 LIMIT를 사용하면 Full Table Scan 범위를 줄일 수 있습니다.

SELECT * FROM ExampleTable LIMIT 10;

3. WHERE 조건 추가
   • 특정 조건을 추가하면 인덱스를 활용할 수 있습니다.

SELECT * FROM ExampleTable WHERE Status = 'Active';

9.3 추가 설명

• 대용량 테이블에서 SELECT *를 자주 사용하면 성능 저하뿐만 아니라 네트워크 트래픽도 증가하게 됩니다.
• 항상 목적에 맞는 컬럼만 선택적으로 조회하는 습관을 가져야 합니다.

10. 인덱스가 여러 개 존재할 때 (AND/OR 조건)

여러 인덱스가 존재하는 경우 AND와 OR 조건이 복합적으로 사용되면 데이터베이스 옵티마이저가 최적의 인덱스를 선택하기 어려워 Full Table Scan이 발생할 수 있습니다.

• AND 조건 예제 코드

-- 두 컬럼에 인덱스가 존재함
CREATE INDEX idx_name ON ExampleTable (Name);
CREATE INDEX idx_userid ON ExampleTable (UserID);

-- AND 조건
SELECT * FROM ExampleTable WHERE Name = 'John' AND UserID = 'elky';

• OR 조건 예제 코드

-- 두 컬럼에 인덱스가 존재함
SELECT * FROM ExampleTable WHERE Name = 'John' OR UserID = 'elky';

10.1 Full Scan이 일어나는 이유

1. AND 조건의 경우
   • 인덱스는 각 컬럼에 대해 개별적으로 적용되기 때문에 단일 인덱스만 선택됩니다.
   • 옵티마이저는 각 인덱스의 비용을 비교한 후 최적의 하나만 선택하거나, 두 인덱스를 결합해 사용하려 하지만 이 과정이 비효율적일 수 있습니다.
   • 결국 옵티마이저가 Full Table Scan을 선택할 수도 있습니다.
2. OR 조건의 경우
   • OR 조건은 여러 컬럼에 걸쳐 조건이 적용되기 때문에 여러 인덱스를 동시에 사용할 수 없습니다.
   • 데이터베이스는 OR 조건을 최적화하기 어렵기 때문에 전체 테이블을 스캔할 가능성이 높습니다.

10.2 해결 방법

1. 복합 인덱스 사용

• 여러 컬럼을 포함하는 복합 인덱스를 생성하면 인덱스 탐색을 최적화할 수 있습니다.

-- 복합 인덱스 생성 (Name, UserID 순서로)
CREATE INDEX idx_combined ON ExampleTable (Name, UserID);

-- 복합 인덱스를 활용하는 쿼리
SELECT * FROM ExampleTable WHERE Name = 'John' AND UserID = 'elky';

• 주의사항
  • 복합 인덱스의 컬럼 순서가 중요합니다.
  • 인덱스를 생성할 때 자주 사용되는 조건 컬럼을 왼쪽에 배치합니다.

2. OR 조건 최적화 (UNION ALL 사용)
   • OR 조건을 분리해서 UNION ALL로 쿼리를 나누면 각 조건이 인덱스를 활용할 수 있습니다.

-- OR 조건을 나누어 인덱스 활용
SELECT * FROM ExampleTable WHERE Name = 'John'
UNION ALL
SELECT * FROM ExampleTable WHERE UserID = 'elky';

3. 옵티마이저 힌트 사용
   • 특정 인덱스를 강제로 사용하도록 옵티마이저 힌트를 제공할 수 있습니다.

-- 특정 인덱스를 강제로 사용하도록 설정
SELECT * FROM ExampleTable FORCE INDEX (idx_name)
WHERE Name = 'John' AND UserID = 'elky';

11. 추가 설명

• AND 조건의 경우
  • 복합 인덱스를 사용하면 두 컬럼의 조건을 동시에 최적화할 수 있습니다.
  • 단일 인덱스만 사용할 때보다 성능이 크게 향상됩니다.
• OR 조건의 경우
  • OR는 인덱스 최적화가 어렵기 때문에 상황에 따라 UNION ALL로 분리하는 것이 효율적입니다.
  • 옵티마이저가 여러 인덱스를 병합하는 비용보다 Full Table Scan이 저렴하다고 판단할 수 있습니다.
• 복합 인덱스 주의사항
  • 복합 인덱스는 순서를 지켜야만 최적화가 됩니다.
  • 예를 들어 (Name, UserID) 순서의 인덱스는 WHERE Name = ? AND UserID = ?는 최적화되지만, WHERE UserID = ?만 사용하면 최적화되지 않습니다.

12. 결론

인덱스를 타지 않는 쿼리는 주로 함수 사용, 형 변환, 부정형 조건, 복합 인덱스 순서 오류와 같이 데이터베이스의 B-Tree 구조와 인덱스 특성을 무시하는 경우 발생합니다. 이를 방지하기 위해서는

1. 인덱스 컬럼에는 함수나 연산을 피한다.
2. LIKE 검색 시 %는 뒤에만 사용한다.
3. 형 변환을 최소화하고, 조건 값의 자료형을 맞춘다.
4. SELECT * 대신 필요한 컬럼만 조회합니다.
5. 복합 인덱스를 활용하고 컬럼 순서를 최적화합니다.
6. OR 조건 대신 UNION ALL을 사용하여 인덱스를 강제로 활용합니다.
7. 필요할 경우 옵티마이저 힌트를 사용하여 인덱스 선택을 강제합니다.

이런 방법들을 적용하면 Full Table Scan을 줄이고 데이터베이스의 성능을 극대화할 수 있습니다.