mysql Query Plan 성능 최적화 관련 문장 100개

EXPLAIN 으로 실행계획 볼때 기억해야할 것들 정리

0. 기본적인 실행 계획 관련 문장

• EXPLAIN을 사용하여 쿼리 실행 계획을 분석할 수 있습니다.
• type = ALL: 테이블을 전체 스캔하므로 성능이 저하될 수 있습니다.
• type = const: 주어진 값 하나만 조회하는 경우로, 가장 빠른 방식 중 하나입니다.
• type = range: 인덱스를 활용한 범위 검색으로, 성능이 우수합니다.
• type = index: 테이블 전체를 인덱스만을 통해 스캔하는 방식으로, 필요하지 않다면 피하는 것이 좋습니다.
• key: 실제로 사용된 인덱스를 나타내며, 효율성을 평가할 수 있습니다.
• rows: 쿼리 실행 시 스캔해야 할 행의 개수를 의미하며, 값이 적을수록 성능이 좋습니다. 값이 크다면 적절한 인덱스를 추가하는 것이 필요할 수 있습니다.
• Using index: 커버링 인덱스를 사용하여 테이블 접근 없이 데이터를 조회하는 방식으로, 효율적입니다.
• Using where: 인덱스를 사용했지만 추가적인 필터링이 필요한 경우에 나타납니다.
• Using temporary / Using filesort: 임시 테이블 및 파일 정렬이 발생하면 성능 저하의 원인이 될 수 있으므로 피하는 것이 좋습니다.
• key_len: 사용된 인덱스의 길이를 나타내며, 값이 짧을수록 인덱스 활용이 효율적입니다.

---

1. 쿼리 최적화 및 성능 개선

• EXPLAIN을 사용하여 쿼리 실행 계획을 분석하고, type, key, rows 등의 정보를 확인하여 성능을 최적화합니다.
• EXPLAIN ANALYZE를 활용하면 실제 실행 시간과 행(row) 처리 개수를 확인할 수 있습니다.
• 불필요한 서브쿼리는 조인으로 대체하는 것이 성능에 유리합니다.
• WHERE 절에서 불필요한 조건을 제외하고, 인덱스를 활용할 수 있도록 작성하면 성능이 향상됩니다.
• ORDER BY와 GROUP BY는 성능에 영향을 줄 수 있으므로, 필요할 경우 적절한 인덱스를 추가해야 합니다.
• HAVING 절은 GROUP BY 이후에 실행되므로, 필터링이 가능한 경우 WHERE 절에서 처리하는 것이 좋습니다.
• COUNT(*)는 인덱스를 활용하여 최적화할 수 있습니다(예: COUNT(인덱스_컬럼)).

---

2. 인덱스 활용 및 조인 최적화

• eq_ref: 가장 빠른 조인 방식으로, 조인된 각 행에 대해 정확히 하나의 행을 찾는 방식입니다.
• ref: 조인 시 인덱스를 사용하여 레코드를 빠르게 찾을 수 있는 방식입니다.
• 두 테이블의 조인 키가 적절한 인덱스를 사용하지 않으면 성능이 저하될 수 있습니다.
• INNER JOIN은 필요하지 않은 데이터를 제외하여 성능을 최적화할 수 있습니다.
• LEFT JOIN을 사용할 때 가능하다면 INNER JOIN을 고려하는 것이 성능상 유리할 수 있습니다.
• ON 절과 USING 절을 적절히 사용하면 조인의 가독성과 성능을 향상할 수 있습니다.
• NATURAL JOIN은 공통 컬럼을 기준으로 자동 조인되지만, 명확한 컬럼 지정이 어렵기 때문에 권장되지 않습니다.
• EXPLAIN을 사용하여 JOIN 시 Using index가 나타나는지 확인하고, 필요한 경우 인덱스를 추가하여 성능을 개선할 수 있습니다.
• WHERE 절에서 인덱스의 첫 번째 컬럼을 정확히 지정하면 복합 인덱스를 효율적으로 활용할 수 있습니다.
• 복합 인덱스의 모든 컬럼을 정확한 값으로 조회하면 const 타입이 나타나며, 최적화됩니다.
• 단일 인덱스보다는 복합 인덱스를 사용하는 것이 여러 조건을 결합한 조회에서 유리할 수 있습니다.

---

3. 서브쿼리 및 필터링 최적화

• 복잡한 서브쿼리는 성능을 저하시킬 수 있으므로, 가능하면 조인으로 변환하는 것이 좋습니다.
• WHERE 절에서 != 연산자는 인덱스를 비효율적으로 만들 수 있습니다.
• IN 절은 작은 범위의 값에 대해서는 효과적이지만, 너무 많은 값을 포함하면 성능이 저하될 수 있습니다.
• 서브쿼리 대신 JOIN을 활용하면 성능을 개선할 수 있습니다.

---

4. 데이터 조회 및 정렬 최적화

• SELECT * 대신 필요한 컬럼만 조회하면 불필요한 데이터 로딩을 방지할 수 있습니다.
• DISTINCT는 중복 제거를 위해 추가적인 정렬 연산이 필요하므로 꼭 필요한 경우에만 사용합니다.
• LIKE '%값'(앞부분이 와일드카드인 경우)은 인덱스를 사용할 수 없어 성능이 저하될 수 있습니다.
• GROUP BY와 ORDER BY를 함께 사용할 때, 인덱스를 활용하면 성능을 향상시킬 수 있습니다.
• LIMIT을 사용하면 불필요한 데이터를 줄여 성능을 개선할 수 있습니다.
• UNION 대신 UNION ALL을 사용하면 중복 제거 연산을 생략하여 성능이 향상될 수 있습니다.

---

5. 대량 데이터 처리 및 삽입 최적화

• 대량 데이터 삽입 시 INSERT INTO SELECT 문을 활용하면 성능이 향상될 수 있습니다.
• LIMIT을 사용하여 필요한 데이터만 조회하면 성능이 향상될 수 있습니다.
• 대량 삽입 시 트랜잭션을 사용하여 한 번에 처리하면 성능이 개선될 수 있습니다.
• 여러 개의 INSERT 문 대신, BULK INSERT 또는 INSERT ... VALUES (...), (...), (...) 문을 사용하는 것이 더 효율적입니다.

---

6. 연산자 및 조건문 최적화

• OR 연산자는 인덱스를 활용하지 못할 수 있으므로, 가능하면 UNION ALL 또는 별도 조건을 분리하는 것이 성능에 유리할 수 있습니다.
• 복잡한 CASE WHEN 문을 단순화하면 쿼리 성능이 개선될 수 있습니다.
• BETWEEN 연산자는 범위 조건을 설정할 때 인덱스를 효율적으로 사용할 수 있습니다.
• EXPLAIN을 사용하여 filesort 또는 temporary가 발생하는지 확인하고, 인덱스를 활용하여 최적화하는 것이 좋습니다.

---