Database Design and Construction : Optimizing Index query performance (2/2)

---

Contents

1️⃣ 인덱스 동작 리뷰 (Review index behavior)
2️⃣ 인덱스 컬럼 선정 (Selecting index columns)
3️⃣ 인덱스 컬럼 결정 (Determine the index column)

---

쿼리 성능 개선의 핵심은 인덱스를 효율적으로 활용하여 최대한 적은 범위의 행들을 가져오는 것에 있다. 이번 파트에서는 어떤 전략을 사용해서 쿼리가 인덱스를 먼저 사용하여 보다 적은 범위의 데이터를 스캔하고, 결과적으로 더 빠르고 효율적인 데이터 조회가 가능하게 하는지 배워볼 예정이다.

1️⃣ 인덱스 동작 리뷰 (Review index behavior)

💡Keyword: 인덱스 사용을 최적화하고 피해야 할 쿼리 패턴을 이해하는 것 (Optimizing index usage and understanding query patterns to avoid).

생각해보기 - 쿼리 예제 ⬇️

💡중요포인트: 함수 사용 여부와 LIKE 절의 패턴이 인덱스 사용 여부에 큰 영향을 미친다.

4가지 쿼리에 대해 인덱스 사용 여부를 살펴보자.

1. SELECT * FROM employees;

   • 인덱스 사용 안함: 조건 없이 모든 데이터를 가져오는 쿼리이다. 테이블의 모든 행을 읽어야 하므로 인덱스가 사용되지 않고, Full Table Scan이 이루어진다.

2. SELECT * FROM employees WHERE employee_id = 200;

   • 인덱스 사용: employee_id 컬럼에 인덱스가 설치되어 있다고 가정했을 때, 이 쿼리는 인덱스를 사용하여 빠르게 특정 ID의 데이터를 조회할 수 있다. Index Range Scan 또는 Unique Scan이 사용된다.

3. SELECT * FROM employees WHERE email LIKE 'SV%';

   • 인덱스 사용: email 컬럼에 인덱스가 설치되어 있고, **LIKE 'SV%'**는 앞부분의 문자열이 고정되어 있어 인덱스를 사용할 수 있는 조건이다. 인덱스를 사용하여 Range Scan이 가능하다.

4. SELECT * FROM employees WHERE substr(email, 1, 2) = 'SV';

   • 인덱스 사용 안함: 함수(substr())가 사용된 경우 인덱스가 작동하지 않는다. 인덱스는 직접적으로 해당 컬럼의 값을 비교할 때만 사용되기 때문에, Full Table Scan이 이루어질 가능성이 크다.

정리:

• 인덱스 사용됨: 2번, 3번 쿼리 /인덱스 사용 안됨: 1번, 4번 쿼리

---

✅생각해보기 - 쿼리만 보고 인덱스가 사용되었는지 아닌지 어떻게 알수 있을까? ⬇️

쿼리만 보고 인덱스가 사용되었는지 아닌지를 판단하는 방법은 기본적으로 몇 가지 규칙을 기반으로 추측한다. 하지만, 실제로는 **실행 계획 (Execution Plan)**을 확인하는 것이 가장 정확한 방법이다. 쿼리만 보고 인덱스 사용 여부를 추측할 수 있는 몇 가지 기준을 살펴보자.

1. 조건절에 있는 컬럼이 인덱스에 포함되어 있는지 확인

• 인덱스가 설치된 컬럼에 대해 WHERE 조건이 있으면, 인덱스를 사용할 가능성이 높다

• 예: employee_id, email 등의 컬럼에 인덱스가 설치되어 있다고 가정하면, 이를 이용한 조건문은 인덱스를 사용하게 된다.

• 예시: WHERE employee_id = 200 → 인덱스 사용 가능

2. 함수나 연산이 사용된 경우 인덱스가 사용되지 않을 가능성

• 함수 또는 연산이 WHERE 절에 사용되면, 인덱스가 비활성화될 수 있다. 이는 인덱스가 컬럼의 원본 값을 기준으로 동작하기 때문이다.

• 예: SUBSTR(email, 1, 2) = 'SV' → 함수 사용으로 인덱스 사용 안 함

• 예시: WHERE salary + 1000 > 3000 → 연산이 있으므로 인덱스 사용 불가

3. LIKE 조건에서의 패턴

• LIKE 조건이 사용될 때, 패턴의 시작이 고정된 문자열로 시작하면 인덱스를 사용할 수 있다.

• 예: LIKE 'SV%' → 고정된 문자열로 시작하므로 인덱스 사용 가능

• 반면, 패턴이 %로 시작하면 인덱스를 사용할 수 없다.

• 예: LIKE '%SV' → 패턴이 뒤에 있기 때문에 인덱스 사용 불가

4. 모든 데이터를 조회하는 경우 (Full Table Scan)

WHERE 절 없이 모든 데이터를 조회하는 경우 인덱스가 사용되지 않고 Full Table Scan이 발생한다.

• 예: SELECT * FROM employees → 인덱스 사용 안 함

5. 범위 검색 (Range Scan)

• 범위 조건을 사용하는 쿼리에서는 인덱스를 사용할 가능성이 높다.

• 예: WHERE employee_id BETWEEN 100 AND 200 → 인덱스 사용 가능

6. JOIN, ORDER BY, GROUP BY 절에서 인덱스 사용 여부**

• JOIN, ORDER BY, GROUP BY 절에서 사용된 컬럼이 인덱스에 포함되어 있으면 인덱스를 사용할 가능성이 있다.

• 예: ORDER BY employee_id → employee_id에 인덱스가 있다면 사용 가능

실행 계획(Execution Plan) 확인 방법

위 기준들을 바탕으로 추측할 수는 있지만, 정확한 인덱스 사용 여부는 실행 계획을 확인하는 것이 가장 확실하다. 데이터베이스에서 실행 계획을 조회하는 명령어를 사용하면, 해당 쿼리가 어떤 방식으로 실행되는지, 인덱스가 사용되는지를 명확히 알 수 있게 된다.

---

생각해보기 - 스캔방식: 테이블 스캔(Table Scan) ⬇️

1번 쿼리: SELECT * FROM employees;

• 조건이 없기 때문에 FULL 테이블 스캔이 발생한다.

• 이는 테이블의 모든 데이터를 조회하는 방식이다.

• 이는 인덱스가 사용되지 않았음을 보여준다.

---

생각해보기 - 스캔방식: 인덱스 유니크 스캔(Index Unique Scan) ⬇️

2번 쿼리: SELECT * FROM employees WHERE employee_id = 200;

• 이 쿼리는 employee_id에 대한 **인덱스 유니크 스캔 (Index Unique Scan)**을 사용하고 있다. 인덱스를 통해 정확히 한 건의 데이터를 찾았고, FULL 테이블 스캔이 발생하지 않아 성능이 최적화되었다.

• UNIQUE SCAN: employee_id가 프라이머리 키이기 때문에 이 쿼리는 유니크 인덱스를 사용하여 정확히 한 행만을 빠르게 조회할 수 있다.

• Access Predicate: EMP_EMP_ID_PK 인덱스를 사용하여 employee_id = 200인 데이터를 조회하였다.

---

생각해보기 - 스캔방식: 인덱스 레인지 스캔(Index Range Scan)⬇️

3번 쿼리: SELECT * FROM employees WHERE email like 'SV%';

• RANGE SCAN: email 컬럼에 대해 LIKE 조건이 붙어 있어, 인덱스 레인지 스캔이 사용되었다. 이 스캔 방식은 범위 검색에 적합하다.

• Access Predicate와 Filter Predicate: EMP_EMAIL_UK라는 인덱스를 사용하여 email이 'SV'로 시작하는 모든 행을 조회하며, 이 조건을 만족하는 범위 내의 데이터를 가져온다.

---

생각해보기 - 스캔방식: 테이블 스캔(Table Scan) ⬇️

4번 쿼리: SELECT * FROM employees WHERE substr(email, 1, 2) = 'SV';

• 테이블 스캔 방식으로, SUBSTR 함수로 이메일의 첫 두 문자를 추출하고 그것이 'SV'와 일치하는지를 찾는다.

• SUBSTR 함수는 인덱스를 사용할 수 없으므로, 전체 테이블을 스캔하게 된다.

• 즉, 모든 행을 조회하면서 각 행의 이메일 첫 두 자리를 확인하기 때문에 인덱스가 적용되지 않게 된다. 함수를 사용한 조건절은 인덱스를 무력화시킨다.

---

✅생각해보기 - 어떤 스캔 방법을 사용할까? ⬇️

💡요약: 쿼리가 실행될 때 어떤 스캔 방법을 사용할지는 일반적으로 DBMS(데이터베이스 관리 시스템)가 결정한다. DBMS는 쿼리를 분석하고, 최적화기(Optimizer)를 사용하여 가장 효율적인 실행 계획을 선택하게 된다. 이 실행 계획에 따라 테이블 스캔이나 인덱스 스캔 등의 방법이 자동으로 결정되는 것이다. 또한 사용자가 인덱스를 적절히 관리하고 쿼리를 최적화함으로써 DBMS가 더 나은 선택을 하도록 유도할 수도 있다.

구체적인 과정:

1. 쿼리 작성: 사용자가 SQL 쿼리를 작성한다.

2. 최적화기(Optimizer): DBMS의 최적화기가 쿼리를 분석하고, 데이터 분포, 인덱스, 통계 정보 등을 기반으로 최적의 실행 계획을 결정한다.

3. 실행 계획 선택: 최적화기는 가능한 여러 실행 계획(테이블 스캔, 인덱스 스캔 등)을 평가하고 **비용(Cost)**이 가장 낮은 방법을 선택하게 된다.

4. 실행: 최적화기가 선택한 실행 계획에 따라 DBMS가 쿼리를 실행한다.

하지만 사람이 고려할 수 있는 부분:

• 인덱스 생성: 사람이 미리 어떤 컬럼에 인덱스를 생성할지 결정하여 성능을 높일 수 있다.

• 쿼리 최적화: 사용자가 특정한 방법으로 쿼리를 작성하거나 리팩토링(예: 함수 사용을 피하는 등)해서 인덱스가 더 잘 활용되도록 할 수 있다.

---

**인덱스 사용이 불가능한 쿼리 정리(**Queries to avoid are as follows) ⬇️

✅함수를 사용하는 질의(Queries using functions): SUBSTR 또는 기타 함수가 컬럼에 적용될 경우 인덱스가 작동하지 않으므로, 이러한 질의를 피해야 한다.

✅가공된 조건절을 사용하는 질의(Queries using processed conditions): 컬럼의 일부분만을 조건으로 사용하거나(email LIKE 'SV%'), 특정 패턴 매칭을 위해 조건을 변형하면 인덱스가 적용되지 않게된다.

따라서, 쿼리 성능을 최적화하려면 컬럼의 전체 값을 조건절로 사용하고, 함수나 가공된 값을 피하는 것이 좋다.

---

**인덱스 사용이 불가능한 쿼리: 인덱스 컬럼을 조건절에서 가공하는 경우 인덱스 사용 불가 #1 (**Queries to avoid are as follows: When an indexed column is processed in the condition clause, the index cannot be used.)⬇️

💡요약: 인덱스가 컬럼 전체 값을 기반으로만 작동하기 때문에, 함수나 가공된 값을 사용하면 인덱스가 적용되지 않게 된다.

계획 설명:

• TABLE ACCESS FULL: 쿼리에서 SUBSTR 함수를 사용하여 email 컬럼의 일부분을 조건절로 사용했기 때문에, 인덱스를 사용할 수 없다. 대신 테이블 전체를 스캔하는 풀 테이블 스캔이 이루어졌다.

결과 요약:

• 인덱스를 활용하지 못해, 전체 테이블을 조회하게 되며 성능이 떨어지게 된다. 특히 큰 데이터셋일 경우 효율성이 매우 낮아질 것이다.

---

**인덱스 사용이 불가능한 쿼리: 부정형 비교를 사용하는 경우 인덱스 사용 불가 #2 (**Queries to avoid are as follows: Index cannot be used when using negative comparison)⬇️

💡 요약: 부정형 비교 (NOT BETWEEN, <>)를 사용하면 인덱스가 작동하지 않으므로 성능이 저하될 수 있으며, 쿼리 성능 개선을 위해서는 이러한 비교 연산을 피하는 것이 좋다.

• 부정형 비교 연산자 (Negative Comparison Operator): NOT BETWEEN, <>, NOT IN과 같은 부정형 비교 연산자를 사용할 때는 인덱스가 적용되지 않게 된다.

• 예를 들어, employee_id가 150에서 200 사이가 아닌 값을 찾는 쿼리는 인덱스를 사용하지 않고 전체 테이블을 스캔하게 된다.

• 이처럼 **부정형 조건 (Negative Condition)**은 **인덱스가 아닌 테이블 전체 스캔 (Full Table Scan)**을 유발하여 성능이 저하되게 된다.

✅ not between 150 and 200 대신 긍정비교 연산자 사용(Positive Comparison Operator) : 부정형 비교 연산자인 NOT BETWEEN 대신, 긍정형 비교 연산자를 사용하는 방식으로 쿼리를 재구성해야 한다. 이를 위해 두 개의 범위 조건을 조합하여 사용하면 된다.

SELECT * FROM employees WHERE employee_id < 150 OR employee_id > 200;

위의 쿼리는 150보다 작은 또는 200보다 큰 직원 아이디를 가진 행을 검색하게 된다. 이 방식은 인덱스를 효과적으로 활용할 수 있으며, 성능 개선에 도움이 될 수 있을 것이다.

---

인덱스 사용이 불가능한 쿼리: 인덱스 쿼리 개선 사례 #1 ⬇️

💡요약: 인덱스 컬럼을 가공하는 쿼리는 인덱스를 무력화하므로 조건절에서 인덱스가 적용될 수 있도록, 인덱스 컬럼에 직접적인 연산을 피해야 한다.

• 인덱스 생성:

  • 쿼리를 효율적으로 처리하기 위해 salary 컬럼에 인덱스를 생성한다.

  • SQL: CREATE INDEX idx_salary ON employees(salary);

• 잘못된 조건절:

  • salary * 12 = 57600와 같은 조건은 인덱스를 사용할 수 없다. 이유는 salary 컬럼을 가공하여 비교하기 때문에 인덱스가 무시되기 때문이다.

  • SQL: SELECT * FROM employees WHERE salary * 12 = 57600;

• 개선된 조건절:

  • salary = 57600 / 12와 같이 인덱스 컬럼을 가공하지 않는 형태로 조건을 변경하면 인덱스를 활용할 수 있게 된다.

  • SQL: SELECT * FROM employees WHERE salary = 57600 / 12;

---

인덱스 사용이 불가능한 쿼리: 인덱스 쿼리 개선 사례 #2 ⬇️

💡요약: 컬럼을 결합하면 인덱스가 적용되지 않으므로, 컬럼을 각각 비교해야 성능을 높일 수 있게 된다. 컬럼을 결합하는 연산을 피하고, 각각 비교하는 방식으로 성능을 개선 할 수 있다.

• 잘못된 쿼리:

  • first_name || last_name = 'StevenKing'과 같이 두 컬럼을 결합하여 하나의 문자열로 비교하면 인덱스를 사용할 수 없게된다.

  • SQL: SELECT * FROM employees WHERE first_name || last_name = 'StevenKing';

  • 이유: 인덱스가 있는 컬럼을 결합하여 하나의 문자열로 만들면 인덱스가 무시된다.

• 개선된 쿼리:

  • first_name = 'Steven' AND last_name = 'King'처럼 컬럼을 각각 비교하면 인덱스를 사용할 수 있게 된다.

  • SQL: SELECT * FROM employees WHERE first_name = 'Steven' AND last_name = 'King';

  • 이 방식은 인덱스를 제대로 활용할 수 있어서 더 빠르고 효율적이다.

---

2️⃣ 인덱스 컬럼 선정 (Selecting index columns)

💡Keyword: 자주 조회되는, 정렬 또는 조인에 사용되는, 갱신이 적은 컬럼을 신중하게 선택하여 성능을 최적화하는 것이 중요하다.

✅생각해보기: 어떤 것을 컬럼으로 선정하는 것이 유리할까? 전체 컬럼을 다 인덱스로 선정하면 어떻게 될까? 물론 검색하는 것은 빠르지만 비효율적일 것이다. 데이터가 삽입/삭제될때마다 전체 인덱스를 관리해야하기 때문에 고비용이 들기 때문이다. 이번 차시에서는 인덱스 컬럼을 선정하는 것에 대해서 배워볼 예정이다.

---

인덱스 컬럼 선정 기준 - 인덱스 대상 후보 컬럼 선정을 위한 질문 ⬇️

위의 이미지는 **인덱스 컬럼 선정 기준(Selecting index columns)**에 대해 다루고 있다. 인덱스를 설정할 때 고려해야 할 중요한 요소들을 제시하고 있는데 크게 다섯 가지 질문을 통해 방향을 잡을 수 있다. 이 질문들은 보편적인 기준을 제시하고 있으며, 실제 현업에서는 데이터 크기, 서버 성능, 전체 사용자의 수, 쿼리 사용 비용 등 여러 가지 요인들을 종합적으로 고려해 인덱스를 생성하게 된다는 점을 알아두자.

핵심 요약:

• 분포도가 좋은 컬럼인가?

• 갱신이 자주 발생하지 않는 컬럼인가?

• 조건절에서 자주 사용되는 컬럼인가?

• 조인의 연결고리에 사용되는 컬럼인가?

• 정렬이 필요한 컬럼인가?

이런 질문을 바탕으로 적절한 인덱스를 설정할 수 있다.

---

인덱스 컬럼 선정 기준 - 분포도가 좋은 컬럼인가? #1 ⬇️

✅ 분포도(Distribution)란? 전체 레코드에서 식별 가능한 수에 대한 백분율을 나타낸다.

💡요약: 분포도가 높을수록 인덱스를 설정할 때 유리한 컬럼이다.

• 성별(Gender) 컬럼은 보통 남성/여성 두 가지 값으로 식별 가능하며, 이 경우 식별 가능한 수가 2이고, 총 레코드 수에 따라 분포도가 결정된다. 예를 들어, 성별 컬럼에서 식별 가능한 수가 20이면 분포도(distribution)는 50%이다.

• 고객 아이디(Customer ID)와 같은 고유한 값이 있는 컬럼은 분포도가 매우 높다. 식별 가능한 수가 N이면, 분포도(distribution)는 1/N * 100 %로 계산된다.

---

인덱스 컬럼 선정 기준 - 분포도가 좋은 컬럼인가? #2⬇️

✅분포도가 좋은 컬럼(Column with Good Distribution)을 선정할 때 주의해야 할 점이다. 분포도(Distribution)가 좋은 컬럼, 즉 분포도가 1% 미만인 컬럼을 선택하는 것이 일반적으로 좋지만, 분포도만으로 판단하는 것은 위험(It is risky to judge based only on distribution)하다점을 강조한다.

💡요약: 분포도가 좋은 컬럼을 선택하되, 분포도만으로 판단하지 말고 실질적인 쿼리 사용 빈도를 고려하는 것이 중요하다.

• 생년월일 컬럼(Birthdate column)의 분포도는 매우 좋습니다 (365일 * 100년), 하지만 실제로 이 컬럼을 자주 사용하는 경우는 많지 않다.

• 배송 여부 컬럼(Delivery status Y/N)의 경우 식별 가능한 값이 2개뿐이므로 분포도가 좋지 않다고 생각될 수 있지만, 실제로 많은 레코드가 'Y' 값을 갖고 있을 때 'N' 값만을 찾는 쿼리가 자주 발생할 수 있다.

  • 아래 쿼리 처럼 분포도가 낮은 컬럼이라도, 예를 들어 'N'처럼 특정 값을 자주 조회하는 경우가 있다면, 인덱스를 생성하여 성능을 개선할 수 있다.

      SELECT * FROM 주문 WHERE 배송여부 = 'N';
    

---

인덱스 컬럼 선정 기준 - 업데이트가 자주 발생하지 않는 컬럼인가?⬇️

✅ 컬럼이 자주 업데이트되는 경우 인덱스를 생성하는 데 신중해야 한다는 점을 강조한다.

💡요약: 업데이트가 빈번하게 발생하는 컬럼에 인덱스를 설정하면 인덱스 성능이 저하되므로 주의가 필요하다.

• 인덱스 컬럼이 자주 업데이트되면, 인덱스 밸런싱이 깨짐 (If the index column is frequently updated, the index balancing breaks): 컬럼이 자주 업데이트되면, 인덱스의 구조가 불균형해져 성능 저하를 일으킬 수 있다.

• INSERT, DELETE, UPDATE 시 인덱스도 함께 변경되므로 비용이 발생 (When INSERT, DELETE, UPDATE occur, the index changes as well, leading to additional costs): 데이터의 삽입, 삭제, 또는 수정이 발생할 때마다 인덱스도 함께 갱신되기 때문에 성능 저하가 일어난다.

• 예로 든 컬럼들(수정일자, 승인일자, 상태코드 등)은 자주 업데이트되는 컬럼은 인덱스 컬럼으로 선정할 때 신중해야 한다. 결합인덱스의 후행 컬럼으로 사용하는 것이 더 좋을수 있다. (Frequent update columns should be carefully considered for index columns)

---

인덱스 컬럼 선정 기준 - 조건절에서 자주 사용되는 컬럼인가? #1⬇️

💡요약: 쿼리에서 자주 사용되는 컬럼만 선별적으로 인덱스를 설정하는 것이 성능을 유지하는 데 도움이 된다.

• 조건절에 사용되는 모든 컬럼에 인덱스를 생성하는 것은 성능 저하를 가져옴 (Creating indexes for every column used in the WHERE clause can degrade performance): 필요하지 않은 컬럼에 무분별하게 인덱스를 생성하면 오히려 성능이 나빠진다.

• 필요한 만큼의 인덱스를 생성 (Create as many indexes as needed): 쿼리 성능을 최적화하기 위해 필요한 컬럼에만 인덱스를 설정하는 것이 좋다.

• 쿼리 실행 빈도수가 상대적으로 높은 컬럼에 인덱스를 생성 (Create indexes on columns with relatively high query execution frequency): 빈번하게 사용되는 컬럼에 인덱스를 설정해야 효율적이다.

---

인덱스 컬럼 선정 기준 - 조건절에서 자주 사용되는 컬럼인가? #2⬇️

✅조건절에서 자주 사용되는 컬럼에 인덱스를 어떤 기준으로 생성할지에 대한 가이드라인을 알아보자

• 쿼리 일일 실행 횟수 (Daily query execution frequency): 쿼리가 얼마나 자주 실행되는지에 따라, 자주 실행되는 쿼리(예: 쿼리3)에 대해 우선적으로 인덱스를 설정하는 것이 좋다.

• 조건절에 나오는 횟수 (The number of occurrences in the WHERE clause): 여러 쿼리에서 자주 사용되는 컬럼(예: 컬럼3, 컬럼4, 컬럼6)은 인덱스를 생성할 우선순위가 높다.

• 결합 인덱스 (Composite index): 여러 컬럼을 조합하여 하나의 인덱스를 생성하는 것도 방법이다. 예를 들어, 컬럼3 + 컬럼4 + 컬럼6 + 컬럼7을 결합하여 인덱스를 생성하면 더 효율적인 성능을 낼 수 있다.

  • 위의 결합 인덱스는 자주 사용되는 컬럼들을 묶어 쿼리 성능을 높이고, 개별 인덱스를 생성하는 것보다 효율적이라는 판단하에 설정되었다.

---

인덱스 컬럼 선정 기준 - 조인의 연결고리에 사용되는 컬럼인가? #1 ⬇️

💡요약: 조인의 종류와 인덱스 필요성에 대해 설명하고 있다. 조인 유형에 따라 인덱스가 반드시 필요하거나 그렇지 않을 수 있다. 특히 Nested Loop Join에서는 인덱스의 존재가 조인의 성능에 중요한 영향을 미치기 때문에 반드시 필요하다.

• Nested Loop Join: 조인절에 인덱스가 반드시 필요(Index is required in the join condition). 온라인 쿼리의 대부분이 Nested Loop Join을 사용하며, 빠른 조인 처리를 위해 인덱스가 필수적이다.

  • 이 방식은 두 테이블을 하나씩 비교하는 방식이다. 첫 번째 테이블에서 하나의 행을 가져오고, 그 행에 맞는 데이터를 두 번째 테이블에서 찾는 식으로 작동한다.

  • 왜 인덱스가 필요하냐면, 테이블의 특정 행을 찾을 때 인덱스가 있으면 빠르게 검색할 수 있기 때문이다. 인덱스가 없으면 테이블의 모든 행을 하나씩 확인해야 하므로 속도가 느려진다.

• Sort Merge Join: 조인에 인덱스가 없어도 가능하지만, 인덱스가 있으면 더 빠른 성능을 기대할 수 있다. 조인절에 인덱스가 반드시 필요한 것은 아니다.(Index is not strictly required in the join condition).

  • 두 테이블을 정렬한 다음, 정렬된 데이터를 병합하여 조인하는 방식이다.

  • 인덱스가 있으면 테이블을 정렬하는 데 시간이 덜 걸려서 조인 속도가 더 빨라질 수 있지만, 꼭 인덱스가 있어야만 하는 것은 아니다. 인덱스가 없어도 조인은 된다.

• Hash Join: 마찬가지로 인덱스가 없어도 수행 가능하며, 조인절에 인덱스가 반드시 필요한 것은 아니다.(Index is not strictly required in the join condition).

  • 한 테이블의 데이터를 해시 테이블로 변환하여 빠르게 비교할 수 있도록 만든 다음, 두 번째 테이블의 데이터를 해시 테이블과 비교하여 조인하는 방식이다.

✅해시테이블(Hash Table) 컴퓨터 과학에서 데이터를 효율적으로 저장하고 검색할 수 있도록 도와주는 자료 구조이다. **키-값 쌍 (key-value pairs)**으로 데이터를 저장하며, 해시 함수를 사용하여 데이터를 빠르게 검색할 수 있는 것이 특징이다.

---

✅조인(JOIN) 복습: 조인은 두 개 이상의 테이블을 특정 조건을 기반으로 결합하는 방법이다. 주로 테이블 간에 공통된 컬럼을 기준으로 데이터를 연결한다. 조인의 종류는 여러 가지가 있지만, 기본적인 세 가지는 다음과 같다: 내부조인(Inner Join), 외부조인(Outer Join), 교차 조인(Cross Join)

• 내부 조인(Inner Join):

  • 두 테이블 간에 조건이 일치하는 공통된 데이터만 가져온다.

      SELECT * FROM 테이블1
      INNER JOIN 테이블2 ON 테이블1.공통컬럼 = 테이블2.공통컬럼;
    

  • 예: 고객과 그 고객의 주문 정보를 가져오려면 고객 ID를 기준으로 두 테이블을 조인한다.

• 외부 조인(Outer Join):

  • 왼쪽 외부 조인(Left Outer Join): 왼쪽 테이블의 모든 데이터와 오른쪽 테이블에서 일치하는 데이터를 가져오며, 일치하지 않는 오른쪽 테이블 데이터는 NULL로 표시된다.

  • 오른쪽 외부 조인(Right Outer Join): 오른쪽 테이블의 모든 데이터와 왼쪽 테이블에서 일치하는 데이터를 가져오며, 일치하지 않는 왼쪽 테이블 데이터는 NULL로 표시된다.

      SELECT * FROM 테이블1
      LEFT JOIN 테이블2 ON 테이블1.공통컬럼 = 테이블2.공통컬럼;
    

• 교차 조인(Cross Join):

  • 두 테이블의 모든 조합을 반환하며, 조건 없이 각 행이 다른 테이블의 모든 행과 결합된다.

  • 이 방식은 조인 조건을 명시하지 않거나, 명시적으로 교차 조인을 사용할 때 발생한다.

---

✅카디션 프로덕트(Cartesian Product) 복습: 카티션 프로덕트는 두 테이블 간의 모든 행을 서로 조합하는 연산이다. 조인 조건이 없을 때 발생하는 현상. 예를 들어, 3개의 행을 가진 테이블 A와 2개의 행을 가진 테이블 B가 있다면, 카티션 프로덕트는 3 * 2 = 6개의 결과를 반환한다.

SELECT * FROM 테이블1, 테이블2;

위와 같은 구문에서는 테이블 간의 조건이 없기 때문에 모든 행이 결합된 카티션 프로덕트가 발생하게 된다.

✅ 조인과 카티션 프로덕트의 차이

• 조인은 두 테이블 간의 논리적인 관계를 기반으로 데이터를 결합하고 필요한 데이터만을 반환하는 방식이다.

• 카티션 프로덕트는 두 테이블의 모든 행을 결합하여 데이터를 반환하는 방식으로, 특정 조건이 없으면 비효율적이고 불필요한 결과를 많이 생성할 수 있다.

---

인덱스 컬럼 선정 기준 - 조인의 연결고리에 사용되는 컬럼인가? #2⬇️

✅ 고객 테이블과 주문 테이블이 각각 다른 데이터를 담고 있으며, 어느 쪽이 더 성능이 좋은지에 대해 질문하고 있다. 성능은 주로 데이터의 양, 인덱스 사용 여부, 그리고 조인이나 검색의 빈도에 따라 달라진다.

💡요약: 고객 테이블은 데이터 양이 적어서 검색 성능이 빠를 수 있지만, 주문 테이블은 더 많은 데이터와 조인을 통해 검색할 가능성이 크기 때문에, 인덱스를 잘 사용하면 주문 테이블의 성능이 더 중요할 수 있다. **인덱스 (Index)**는 대량의 데이터를 빠르게 검색하는 데 도움을 주기 때문에 데이터가 많은 주문 테이블에서 인덱스를 활용하면 더 큰 성능 향상을 기대할 수 있다.

• 고객 테이블 (Customer Table): 고객 ID가 **기본 키 (Primary Key)**로 설정되어 있고, 이름, 연락처, 주소 같은 정보를 가지고 있다. 데이터 양은 1,000건으로 상대적으로 적다.

• 주문 테이블 (Order Table): 주문 번호가 **기본 키 (Primary Key)**이며, 고객 ID와 상품 ID가 **외래 키 (Foreign Key)**로 설정되어 있다. 데이터 양은 10,000건으로 더 많다.

---

💡요약: 결론적으로, 이름 검색을 자주 한다면 고객 이름에 인덱스가 필요하고, 조인을 자주 한다면 주문 테이블의 고객 ID에 인덱스가 필요하다. 위의 상황에 따라 인덱스를 적절히 선택하는 것이 성능 최적화의 핵심이라고 할 수 있다.

• 고객 이름에 인덱스가 있을 때: 고객 테이블의 이름 컬럼에 인덱스가 있으면, '나%'로 시작하는 이름을 빠르게 검색할 수 있다. 하지만 주문 테이블의 고객 ID에는 인덱스가 없으면, 조인 과정에서 성능이 느려질 수 있다.

• 주문 테이블의 고객 ID에 인덱스가 있을 때: 반면, 고객 이름에 인덱스가 없고 주문 테이블의 고객 ID에만 인덱스가 있을 경우, 조인은 빠를 수 있지만, 이름 검색이 느릴 수 있다.

---

✅생각해보기: 데이터를 '정렬'하는 것은 시간이 많이 소요되는 작업일까?

그렇다. 데이터를 정렬 (sorting)하는 작업은 시간이 많이 소요되는 작업이다. 특히 데이터의 양이 많거나, 정렬해야 할 컬럼이 복잡한 경우에는 더 많은 리소스와 시간이 필요하기 때문이다. 따라서 정렬을 최소화하거나, 필요한 경우 정렬 컬럼에 인덱스를 미리 설정해 두면 성능을 크게 개선할 수 있다.

인덱스 컬럼 선정 기준 - 정렬이 필요한 컬럼인가? #1 ⬇️

💡요약: ORDER BY 절에서 사용하는 정렬 컬럼도 인덱스 후보로 고려해야 하며, 인덱스가 없을 경우 쿼리 성능이 저하될 수 있다.

• ORDER BY 절 (ORDER BY Clause)에 사용되는 컬럼도 인덱스 후보로 선정될 수 있다. 예를 들어, 상품코드가 'IPHONE'이고 배송 여부가 'N'인 주문 데이터를 주문일자 기준으로 정렬하고 싶다면, 아래와 같은 쿼리를 사용할 수 있다:

    SELECT * FROM 주문
    WHERE 상품코드 = 'IPHONE' AND 배송여부 = 'N'
    ORDER BY 주문일자;
  

• 이 쿼리에서 ORDER BY 주문일자가 등장하기 때문에, 주문일자 컬럼에 인덱스를 생성하면 정렬 작업의 성능을 높일 수 있다.

• ORDER BY 절에 사용되는 컬럼은 데이터베이스가 정렬 작업을 수행할 때 많은 리소스를 소모할 수 있기 때문에, 이러한 컬럼에 인덱스를 생성하는 것이 좋다.

• 즉, 정렬이 자주 발생하는 컬럼을 인덱스로 선정하면 성능이 크게 향상될 수 있다.

---

인덱스 컬럼 선정 기준 - 정렬이 필요한 컬럼인가? (Is the column required for sorting?) #2 ⬇️

💡요약: 정렬이 필요한 컬럼을 포함한 결합 인덱스(Composite Index)를 설정하면, 데이터를 가져와서 별도로 정렬하는 작업을 줄여 성능을 향상시킬 수 있다. 결합 인덱스는 여러 컬럼을 조합해서 인덱스를 만드는 방식으로, 정렬에 사용되는 컬럼까지 포함된 인덱스를 만드는 것이 성능 향상에 도움이 될 수 있다. 따라서 정렬 작업을 줄이기 위해서는 결합인덱스2와 같이 더 많은 관련 컬럼을 포함하는 인덱스를 선택하는 것이 좋다.

• 두 가지 결합 인덱스 (Composite Index) 후보를 살펴보자:

  1. 결합인덱스1: 상품코드 + 배송여부

  2. 결합인덱스2: 상품코드 + 배송여부 + 주문일자

• 결합인덱스2가 더 많은 컬럼을 포함하고 있기 때문에, 정렬할 때 추가적인 정렬 작업의 부하 (sorting overhead)를 줄일 수 있다. 즉, 데이터를 가져와서 정렬하는 작업이 인덱스를 사용하는 것보다 부하가 더 크다고 판단되면, 결합인덱스2를 선택하는 것이 적합하다.

---

3️⃣ 인덱스 컬럼 결정 (Determine the index column)

💡Keyword: 결합인덱스 컬럼 순서 결정을 위한 고려사항

• 공통적으로 사용하는 필수 조건절 컬럼을 우선한다.

• '=' 조건의 컬럼을 다른 연산자 컬럼보다 우선한다.

• 대분류/중분류/소분류 컬럼순으로 구성한다.

• 위치(조건) 정보 컬럼은 순서(정렬) 정보 컬럼보다 우선한다.

인덱스 판단 기준 - 인덱스 수 결정 기준(Criteria for Determining the Number of Indexes) #1 ⬇️

💡요약: 테이블의 성격에 따라 인덱스 수를 조절하는 것이 중요하며, 처리 중심 테이블(Transactional Table)은 인덱스 수를 적게, 조회 중심 테이블(Query-based Table)은 인덱스 수를 늘리는 것이 좋다.

• 인덱스 수가 많아지면 조회 (SELECT) 성능은 향상될 수 있지만, 처리 (INSERT, UPDATE, DELETE) 작업 시에는 부하가 증가하게 된다.
  (When the number of indexes increases, SELECT performance improves, but INSERT, UPDATE, DELETE operations experience more overhead.)

• 테이블 성격에 따라 인덱스 수를 결정해야 한다.
  (You need to decide the number of indexes based on the nature of the table.)

그러므로

• 처리성 테이블 (Transactional Table): 처리 작업이 많으므로 인덱스 수를 적게 유지하는 것이 좋다.
  (Keep the number of indexes low because transactional operations are frequent.)

• 조회성 테이블 (Query-based Table): 조회 성능이 중요한 테이블에서는 인덱스 수를 많게 유지하는 것이 좋다.
  (Maintain a larger number of indexes on tables where query performance is critical.)

---

인덱스 판단 기준 - 인덱스 수(Index Count) #2 ⬇️

✅테이블의 유형에 따라 인덱스를 얼마나 생성해야 하는지에 대한 기준을 알아보자. 각 테이블의 성격에 맞게 적절한 인덱스 수를 결정해야 하기 때문이다.

💡요약:

• 코드성 테이블은 조회가 많은 경우 필요한 만큼 인덱스 생성.

• 처리성 테이블은 데이터 변경이 많아 인덱스 최소화.

• 집계성 테이블은 분석 및 조회를 위해 적절한 인덱스 사용.

• 로그성 테이블은 조회가 거의 없으므로 인덱스 생성 최소화.

---

• 코드성 테이블 (Code Tables):

  • 특징: 주로 조회가 많고, 데이터 변경이 거의 없는 테이블이다. 코드값을 저장하거나 고정된 데이터를 관리하는 데 사용된다.

  • 예시: 국가 코드, 제품 카테고리, 직급 코드 테이블

    • 예: COUNTRY_CODE (code, country_name)

      • 이 테이블은 국가 코드를 조회하는 데 주로 사용됩니다.

  • 인덱스: 필요한 만큼 인덱스를 생성할 수 있으며, 조회가 많으므로 인덱스가 유용하다.

• 처리성 테이블 (Transaction Tables):

  • 특징: 주문, 계약 등 데이터가 자주 변경되며, 트랜잭션 처리 시 부하가 많이 발생하는 테이블이다.

  • 예시: 주문 내역 테이블, 결제 기록 테이블

    • 예: ORDER (order_id, customer_id, order_date, product_id)

      • 주문 내역을 저장하는 테이블로, 데이터가 자주 추가되고 수정됩니다.

  • 인덱스: 데이터 변경이 많기 때문에 인덱스 개수를 최소화하여 성능 저하를 방지해야 한다.

• 집계성 테이블 (Aggregation Tables):

  • 특징: 분석을 위해 데이터를 집계하는 테이블로, 데이터 처리 후 주로 조회가 이루어진다. 대부분의 데이터가 야간에 배치작업으로 이루어지게 된다.

  • 예시: 월별 매출 집계 테이블, 고객 활동 집계 테이블

    • 예: SALES_AGGREGATE (month, total_sales)

      • 월별로 매출을 집계한 데이터를 저장하는 테이블입니다.

  • 인덱스: 데이터를 필요한 만큼 적절하게 정렬하거나 집계할 때 인덱스가 유용할 수 있다.

• 로그성 테이블 (Log Tables):

  • 특징: 시스템 활동이나 트랜잭션 기록을 저장하는 테이블로, 데이터 조회가 거의 없다. 추가로 설명하자면 로그성 테이블은 실시간으로 기록되지만, 조회 빈도가 낮고 주로 문제 해결이나 배치 처리 작업을 위해 사용되기 때문에 자주 조회되지 않는 이유이다.

  • 예시: 시스템 로그, 사용자 액세스 기록 테이블

    • 예: USER_LOGS (log_id, user_id, access_time, action)

      • 사용자 액세스 기록을 저장하는 테이블이다.

  • 인덱스: 쿼리에서 조회가 거의 발생하지 않으므로 인덱스 사용이 적다.

---

인덱스 판단 기준 - 인덱스가 제공하는 중요한 정보 #1 ⬇️

💡요약: 인덱스는 테이블 행의 위치와 순서 정보를 제공하여 데이터 조회를 최적화한다. 결합 인덱스를 생성할 때 이 두 정보를 고려하여 생성하면 성능이 더 향상될 수 있다.결론적으로 인덱스는 데이터의 위치와 순서를 관리하여 쿼리 성능을 최적화하는 중요한 도구이다.

• 위치 정보 (Location Information):

  • 인덱스는 테이블의 행이 저장된 위치 (where the table rows are stored)에 대한 정보를 포함하고 있다. 이를 통해 쿼리가 데이터를 빠르게 찾을 수 있다.

• 순서 정보 (Order Information):

  • 인덱스의 마지막 노드는 순서대로 정렬 (The last node of the index is sorted in order)됩니다. 즉, 인덱스는 테이블 데이터가 특정 순서에 따라 정렬되어 있는지에 대한 정보를 제공한다. 이는 정렬이 필요한 쿼리를 더 빠르게 처리할 수 있게 해준다.

---

인덱스 판단 기준 - 결합인덱스 사용시 포함될 컬럼의 수 결정하기 ⬇️

아래와 같은 쿼리가 있을때 1번 인덱스, 2번 인덱스 중에 어느 옵션이 최적의 성능을 가지게 될까? 자세히 알아보자.

• 결합인덱스1 (Composite Index 1):

  • 이 인덱스는 "유형"과 "상품명" 컬럼을 포함하고 있다. 따라서 쿼리에서 WHERE 조건에 있는 두 컬럼, 즉 유형과 상품명을 기준으로 데이터를 검색하는 데 도움이 된다.

• 결합인덱스2 (Composite Index 2):

  • 이 인덱스는 "유형", "상품명", 그리고 "제조일" 컬럼을 포함하고 있다. WHERE 조건에 나오는 두 컬럼 외에도 ORDER BY 절에 사용된 제조일까지 인덱스에 포함해, 데이터를 정렬할 때 성능을 향상시킬 수 있다.

판단기준의 핵심은 정렬 비용 고려 (Consider the sorting cost)이다.

• 결합집합의 수가 적은 경우 (When the number of result sets is small): 결합인덱스1을 선택하는 것이 좋다. 이 경우에는 인덱스에 포함된 컬럼이 적어도 충분히 빠르게 데이터를 찾을 수 있게 된다.

• 결합집합의 수가 많은 경우 (When the number of result sets is large): 결합인덱스2를 선택한다. 정렬이 필요한 경우 인덱스에 "제조일"을 포함해 정렬 비용을 줄일 수 있기 때문이다.

당연히 상황에 따라 다르지만 보통 쿼리에서 ORDER BY 절이 있다면, 정렬 비용을 줄이기 위해 결합인덱스에 정렬 기준 컬럼을 추가하는 것이 좋다.

---

인덱스 판단 기준 - 단일 인덱스/결합인덱스 #1 ⬇️

✅ 단일 인덱스와 결합 인덱스를 만들 때 각기 다른 요소를 고려해야 한다. 단일 인덱스는 컬럼의 분포도가 중요한 반면, 결합 인덱스는 컬럼의 순서가 성능에 중요한 영향을 미친다.

• 단일 인덱스 (Single Index): 하나의 컬럼으로 구성된 인덱스이다. 이 경우 중요한 요소는 컬럼의 분포도(Column Distribution)이다. 즉, 얼마나 다양한 값이 존재하는지가 인덱스의 성능에 영향을 미친다.

  • 예를 들어, 상품 테이블에서 상품명에 단일 인덱스를 걸면, 상품명에 대한 검색이 빠르게 처리된다.

      SELECT * FROM 상품 WHERE 상품명 = '노트북';
    

    • 이 경우, 상품명의 분포도가 중요한 요소가 된다. 즉, 상품명이 고유한 값이 많을수록(예: 각기 다른 모델 이름) 인덱스 성능이 좋아진다.

    • 왜 상품 분포도가 인덱스 스캔에서 중요한 요소가 될까? 아래 ✅생각해보기 참조

• 결합 인덱스 (Composite Index): 여러 개의 컬럼으로 구성된 인덱스이다. 이 경우 중요한 요소는 컬럼의 순서(Column order)이다. 어느 컬럼이 먼저 오느냐가 중요하다. 쿼리의 조건절이 인덱스 컬럼 순서와 일치할수록 더 효율적으로 데이터를 검색할 수 있게 된다.

  • 예를 들어, 상품 테이블에서 유형과 상품명을 결합 인덱스로 만들면, 두 컬럼을 기준으로 검색이 최적화된다.

      SELECT * FROM 상품 WHERE 유형 = '전자제품' AND 상품명 = '노트북';
    

    • 유형과 상품명의 컬럼 순서가 중요하다. 예를 들어, 결합 인덱스가 유형을 먼저, 상품명을 나중에 설정하면, 유형을 기준으로 검색할 때 성능이 최적화된다.

    • 왜 컬럼 순서가 중요하냐면 먼저 큰 카테고리를 기준으로 데이터를 좁히고 나서 작은 카테고리로 세분화하는 것이 더 효율적이기 때문이다.

    • 유형은 "전자제품", "가전제품", "의류" 같은 큰 범주이고, 큰 카테고리이다.

    • 상품명은 개별 제품 이름(예: "노트북", "세탁기" 등)이고, 작은 카테고리이다.

---

✅생각해보기: 테이블 스캔과 인덱스 스캔의 차이점

• 테이블 스캔 (Table Scan): 테이블의 모든 행을 하나하나 살펴보며 조건에 맞는 데이터를 찾는 방식인데 이 방식은 매우 비용이 크다. 왜냐하면 테이블 전체를 읽기 때문에, 데이터 양이 많아질수록 성능에 큰 영향을 미치게 되기 때문이다. 따라서 테이블 스캔은 최대한 적은 수의 행을 가져오는 것이 성능을 높이는 데 중요하다.

• 인덱스 스캔 (Index Scan): 인덱스를 사용해서 필요한 데이터를 먼저 찾은 후, 해당 데이터가 저장된 위치로 이동해 실제 테이블에서 데이터를 가져오는 방식이다. 인덱스 자체는 용량이 작기 때문에, 상대적으로 많은 양의 인덱스를 스캔하더라도 성능에 크게 영향을 미치지 않게된다.

즉, 인덱스 스캔을 할 때는 큰 범위든 작은 범위든 성능 차이가 크지 않으므로, 특정 순서로 스캔할 때 비용 차이가 적기 때문에 상품 분포도가 인덱스 스캔에서 최적의 성능을 보장하는 것이다.

---

인덱스 판단 기준 - 결합인덱스 컬럼 순서 결정 #2 ⬇️

✅ 결합 인덱스를 만들 때 어떤 순서로 컬럼을 배치할지 고려할 사항들을 알아보자

💡요약: 결합 인덱스를 만들 때 자주 사용되는 조건절과 '=' 조건이 있는 컬럼을 우선 배치하고, 대분류에서 소분류로 범위를 좁혀가며, 위치 정보 컬럼을 우선하여 배치하는 것이 효율적이다. 핵심 메시지는 인덱스를 만들 때 컬럼의 배치 순서가 매우 중요하며, 검색 성능을 최적화할 수 있는 순서를 고려해야 한다는 점이다.

• 자주 사용되는 필수 조건절 컬럼을 우선한다
  (Prioritize commonly used required condition columns)

  • 자주 사용되는 조건절에 있는 컬럼은 항상 인덱스에서 먼저 나와야 한다. 예를 들어, 우리가 자주 "상품 종류"로 검색을 한다면 이 컬럼이 인덱스에서 가장 먼저 나오는 것이 좋다.

      SELECT * FROM 상품
      WHERE 상품종류 = '우유' AND 가격 > 1000;
    

    이 경우 상품종류는 항상 조건에 들어가기 때문에 인덱스에서 제일 먼저 배치하는 것이 좋다.

• '=' 조건의 컬럼을 다른 연산자 컬럼보다 우선한다
  (Prioritize columns with '=' condition over other operators)

  • 설명: '=' 연산자는 검색에서 가장 빠른 방식이다. 따라서 '같다' 조건을 사용하는 컬럼이 다른 연산자 (예: '>', '<')보다 우선적으로 배치되게 된다.

    예시:

      SELECT * FROM 상품
      WHERE 상품종류 = '우유' AND 가격 > 1000;
    

    여기서 상품종류는 '=' 연산자(같다)를 사용하고, 가격은 '>' 연산자를 사용하고 있다. 그러므로 인덱스는 상품종류를 먼저 배치하는 것이 더 빠르다.

• 대분류/중분류/소분류 컬럼 순으로 구성한다
  (Organize columns in the order of major, intermediate, and minor categories)

  • 설명: 대분류는 검색 범위가 넓고, 소분류는 더 좁은 범위를 다룬다. 그래서 대분류부터 좁히는 것이 효율적이다..

    예시:

      SELECT * FROM 상품
      WHERE 카테고리 = '식품' AND 상품종류 = '유제품' AND 브랜드 = '고려우유';
    

    • 이 경우 카테고리(대분류), 상품종류(중분류), 브랜드(소분류) 순으로 인덱스를 배치해야 검색이 효율적이다.

• 위치(조건) 정보 컬럼은 순서(정렬) 정보 컬럼보다 우선한다
  (Location (condition) information columns take priority over sorting (order) columns)

  • 설명: WHERE 절에서 사용되는 조건은 데이터 위치를 빠르게 찾는 데 필수적이므로 정렬보다 우선이 된다.

      SELECT * FROM 상품
      WHERE 상품종류 = '유제품' AND 가격 BETWEEN 1000 AND 2000
      ORDER BY 출시일 DESC;
    

    • 이 경우 상품종류와 가격은 데이터 위치를 찾는 조건이다. 따라서 이 두 조건이 인덱스에 먼저 들어가야 하고, 정렬은 그 후에 처리되어도 된다.

---

인덱스 판단 기준 - 결합인덱스 컬럼의 순서 ⬇️

✅결합 인덱스 컬럼의 순서를 결정할 때 어떻게 하면 효율적인지에 대한 설명을 다시 한번 살펴보자.

💡요약: 결합 인덱스는 큰 범위에서 작은 범위로, 위치 정보를 우선한 후 순서 정보를 배치하는 것이 효율적이다. 이렇게 하면 쿼리 성능을 최적화할 수 있게 된다. 핵심 메시지는 결합 인덱스의 순서를 정할 때 범위의 크기와 위치, 순서 정보를 적절히 고려해야 한다는 점이다.

1. 큰 범위에서 작은 범위로 (From larger to smaller categories): 큰 범위의 조건을 먼저 배치하고 작은 범위 조건을 뒤에 배치하는 것이 좋다. 예를 들어, 제품군과 같은 대분류 조건을 먼저 두고, 제품명과 같은 소분류 조건을 나중에 둔다. 이렇게 하면 더 적은 데이터에 대해 빠르게 검색이 가능하다.

2. 위치 정보가 우선 (Location information first): 위치 정보를 기반으로 한 컬럼을 먼저 배치하고, 정렬과 같은 순서 정보는 그 다음에 둔다. 예를 들어, 조건에 따라 필요한 데이터를 먼저 필터링한 다음, 그 데이터를 정렬하는 것이 성능에 유리하다.

3. 인덱스 구성: 인덱스는 유형(위치) + 상품명(위치) + 제조일(위치+순서) + 제조번호(순서)로 구성된다. 이 순서는 큰 범위 조건을 먼저, 정렬할 순서 정보를 뒤에 두어 효율적으로 검색할 수 있게 돕는다.

    SELECT * FROM 상품
    WHERE 유형='식품' AND 상품명='고려우유' AND 제조일 BETWEEN '20210101' AND '20210331'
    ORDER BY 제조일 DESC, 제조번호 ASC;
   

   • 이 쿼리는 '식품'이라는 큰 범위의 데이터를 먼저 필터링한 후, '고려우유'라는 구체적인 상품명을 필터링하고, 그 이후에 제조일과 제조번호를 이용해 데이터를 정렬하고 있다.

---

학습정리