[📕 Real MySQL 8.0 (2)] 쿼리 작성 및 최적화 SELECT - 키워드 및 함수 1

1. LIMIT n

쿼리에서 지정된 순서에 위치한 레코드만 가져오고자 함
👉 LIMIT에서 필요한 레코드 건수만 준비되면 즉시 쿼리 종료 

 

SELECT * FROM salaries ORDER BY salary LIMIT n, m;

• n, m에 주어지는 수치가 매우 커지면 쿼리 실행에 오랜 시간 걸림
• LIMIT 조건의 페이징이 처음 몇 개 페이지 조회로 끝나지 않을 가능성이 높으면 WHERE 조건절로 읽어야 할 위치를 찾고 그 위치에서 m개만 읽는 형태의 쿼리 사용 

-- // 두 번째 페이지 조회용 쿼리(첫 페이지의 마지막 salary 값과 emp_no 값 이용)
SELECT * FROM salaries
WHERE salary>=38864 AND NOT (salary=38864 AND emp_no<=274049)
ORDER BY salary LIMIT 0, 10;

• NOT (salary=38864 AND emp_no<=274049) 조건은 이전 페이지에서 이미 조회됐던 것을 제외하기 위해 추가

 

 

 

2. COUNT()

• MyISAM 스토리지 엔진을 사용하는 테이블은 항상 테이블의 메타 정보에 전체 레코드 건수 관리
  👉 WHERE 조건이 없는 COUNT(*) 쿼리는 MySQL 서버가 실제 레코드 건수를 세어보지 않아도 바로 결과 반환하므로 빠르게 처리
  👉 WHERE 조건이 있는 COUNT(*) 쿼리는 그 조건에 일치하는 레코드를 읽어보지 않는 이상 알 수 없으므로 일반적인 DBMS와 같이 처리됨
  
  
• InnoDB 스토리지 엔진을 사용하는 테이블에서는 WHRER 조건이 없는 COUNT(*) 쿼리라고 하더라도 직접 데이터나 인덱스를 읽어야만 레코드 건수 가져올 수 있음
• SELECT COUNT(*) 쿼리에 사용된 ORDER BY 절은 옵티마이저가 무시

💡 게시물의 전체 건수를 조회하는 작업은 피하는 것이 좋다.. 

👉 게시물의 페이지 번호를 보여주는 방식보다는 "이전"과 "다음" 버튼만 표시하는 방식을 검토해볼 것을 권장 !

 

 

 

 

3. JOIN

JOIN이 어떻게 인덱스를 사용하는지에 대해 쿼리 패턴별로 살펴보고, JOIN의 유형별로 주의할 사항을 알아보자 !

 

✳️ JOIN의 순서와 인덱스

• 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 단 한 번만 수행하고 그 이후부터는 스캔만 실행
• 드리븐 테이블에서는 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복
• 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획 수립

SELECT * 
FROM employees e, dept_emp de
WHERE e.emp_no=de.emp_no;

 

🔷 두 컬럼 모두 각각 인덱스가 있는 경우

• 어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리 가능
• 옵티마이저가 통계 정보(레코드 건수)를 이용해 적절히 드라이빙 테이블 선택

🔷 employees.emp_no에만 인덱스가 있는 경우

• dept_emp 테이블이 드리븐 테이블로 선택되면 employees 테이블의 레코드 건수만큼 dept_emp 테이블을 풀 스캔해야만 조건에 일치하는 레코드 발견
  👉 반복되는 풀 스캔을 피하기 위해 dept_emp 테이블을 드라이빙 테이블로 선택하고, employees 테이블을 드리븐 테이블로 선택

 

🔷 dept_emp.emp_no에만 인덱스가 있는 경우

• 위의 경우와 반대로 처리됨

 

🔷 두 컬럼 모두 인덱스가 없는 경우

• 어느 테이블을 드라이빙으로 선택하더라도 드리븐 테이블의 풀 스캔은 발생하기 때문에 옵티마이저가 적절히 드라이빙 테이블 선택
• 레코드 건수가 적은 테이블을 드라이빙 테이블로 선택하는 것이 훨씬 효율적
• 조인 조건을 빠르게 처리할 적절한 인덱스가 없는 경우 
  • MySQL 8.0.18 이전 버전 까지는 블록 네스티드 루프 조인 사용
  • 8.0.18 버전부터는 해시 조인 사용

---

✳️ JOIN 컬럼의 데이터 타입

• 데이터 타입이 불일치 시 인덱스의 변형이 필요하므로 제대로 사용 X 👉 풀 테이블 스캔으로 접근
• CHAR 타입과 VARCHAR 타입, INT 타입과 BIGINT, SMALLINT 타입, DATE 타입과 DATETIME 타입 사이에서는 발생 X
• 같은 CHAR 타입이더라도 문자 집합이나 콜레이션이 다른 경우 문제 발생
• 같은 INT 타입이더라도 부호의 존재 여부가 다른 경우 문제 발생

---

✳️ OUTER JOIN의 성능과 주의사항

• 테이블의 데이터가 일관되지 않은 경우에만 OUTER JOIN 필요

SELECT * 
FROM employees e
	LEFT JOIN dept_emp de ON de.emp_no=e.emp_no
    LEFT JOIN departments d ON d.dept_no=de.dept_no AND d.dept_name='Development';

 

• 제일 먼저 employees 테이블을 풀 스캔하면서 dept_emp 테이블과 departments 테이블을 드리븐 테이블로 사용
• employees 테이블에 존재하는 사원 중에 dept_emp 테이블에 레코드를 갖지 않는 경우가 있다면 OUTER JOIN 필요하지만 대부분 그런 경우는 없으므로 굳이 OUTER JOIN 사용 X
• INNER JOIN을 사용했다면 부서명이 Development인 레코드 1건만 찾아서 조인 실행 

---

SELECT * 
FROM employees e
	LEFT JOIN dept_manager mgr ON mgr.emp_no=e.emp_no
WHERE mgr.dept_no='d001';

 

• WHERE 절의 조건 때문에 MySQL 옵티마이저가 LEFT JOIN을 INNER JOIN으로 변환해서 실행
• LEFT JOIN은 오른쪽 테이블에 매칭되지 않는 레코드도 포함되지만, WHERE절의 조건으로 인해 NULL 값 제거

SELECT * 
FROM employees e
	LEFT JOIN dept_manager mgr
	ON mgr.emp_no=e.emp_no AND mgr.dept_no='d001';

• 조건을 WHERE 절이 아닌 ON 절에 포함시켜야 LEFT JOIN의 특성 유지

---

SELECT * 
FROM employees e
	LEFT JOIN dept_manager dm ON dm.emp_no=e.emp_no
WHERE dm.emp_no IS NULL
LIMIT 10;

• OUTER 테이블의 컬럼이 WHERE 절에 사용될 수 있는 유일한 경우 👉 ANTI JOIN

---

✳️ JOIN과 외래키 

• 외래키는 JOIN과 연관 X
• 데이터 모델을 DB에 생성할 때는 그 테이블 간의 관계는 외래키로 생성하지 않을 때가 더 많음

---

✳️ 지연된 조인 (Delayed Join)

• JOIN이 실행되기 이전에 GROUP BY나 ORDER BY를 처리하는 방식 = 지연된 조인
  👉 LIMIT이 함께 사용된 쿼리에서 더 큰 효과를 얻을 수 있음
  
  
• JOIN은 대체로 실행되면 될수록 결과 레코드 건수가 늘어남
  👉 JOIN의 결과를 GROUP BY하거나 ORDER BY 하면 JOIN을 실행하기 전의 레코드에 GROUP BY나 ORDER BY를 수행하는 것보다 많은 레코드 처리

---

🔷 쿼리 예시 

SELECT e.*
FROM salaries s, employees e
WHERE e.emp_no=s.emp_no
	AND s.emp_no BETWEEN 10001 AND 13000
GROUP BY s.emp_no
ORDER BY SUM(s.salary) DESC
LIMIT 10;

• employees 테이블을 드라이빙 테이블로 선택해서 "emp_no BETWEEN 10001 AND 13000" 조건을 만족하는 레코드를 읽고, salaries 테이블을 조인
• GROUP BY 처리를 통해 건수 줄임
• ORDER BY 처리로 상위 10건만 최종적으로 반환

---

🔷 위의 쿼리를 지연된 조인으로 변경하면 ...? 

SELECT e.*
FROM 
	(SELECT s.emp_no
    FROM salaries s
    WHERE s.emp_no BETWEEN 10001 AND 13000
    GROUP BY s.emp_no
    ORDER BY SUM(s.salary) DESC
    LIMIT 10) x,
    employees e
WHERE e.emp_no=x.emp_no;

👉 지연된 조인으로 변경된 쿼리의 조인 횟수가 훨씬 적음

---

✳️ 래터럴 조인 (Lateral Join)

특정 그룹별로 서브쿼리를 실행해서 그 결과와 조인

 

SELECT *
FROM employees e
	LEFT JOIN LATERAL (SELECT *
    					FROM salaries s
                        WHERE s.emp_no=e.emp_no
                        ORDER BY s.from_date DESC LIMIT 2) s2 ON s2.emp_no=e.emp_no
WHERE e.first_name='Matt';

• FROM 절에 사용된 서브쿼리에서 외부 쿼리의 FROM 절에 정의된 테이블의 컬럼 참조 가능 
  👉 salaries 테이블을 읽는 서브쿼리에서 employees 테이블의 emp_no 참조 
  
  
• FROM 절에 사용된 서브쿼리가 외부 쿼리의 컬럼을 참조하기 위해서는 "LATERAL" 키워드 명시 

🔸 실행 계획으로 인한 정렬 흐트러짐

- 네스티드 루프 방식으로 조인되면 드라이빙 테이블을 읽은 순서대로 결과 조회됨
- 해시 조인이 사용되는 경우에는 쿼리 결과의 정렬 순서가 드라이빙 테이블을 읽는 순서와 다르게 출력됨 

👉 정렬이 필요하다면, ORDER BY 절을 명시적으로 사용 !