쿼리 작성 및 최적화 (2)

where 절의 비교 조건 사용 시 주의사항

NULL 비교

• null도 하나의 값으로 인정해서 관리
• null 비교는 is null

문자열이나 숫자 비교

• 그 타입에 맞춰 상수를 사용할 것을 권장

DATE나 DATETIME 과 문자열 비교

• DATE나 DATETIME 타입의 값과 문자열을 비교할 때는 문자열 값을 자동으로 DATETIME 타입의 값으로 변환해서 비교를 수행

DATE와 DATETIME의 비교

• DATETIME 타입의 값을 DATE 타입으로 만들지 않고 그냥 비교하면 MySQL 서버가 DATE 타입의 값을 DATETIME 으로 변환해서 같은 타입을 만든 다음 비교를 수행한다. 예를 들면 DATE 타입의 값 "2017-01-26" 과 DATETIME 타입의 값 "2017-01-26 00:00:00"을 비교하는 과정에서는 "2017-01-26" 을 "2017-01-26 00:00:00" 으로 변환해서 비교를 수행한다.

DISTINCT

• DISTINCT는 MIN(), MAX(), COUNT()와 같은 집합 함수와 함께 사용하는 경우와, 집합 함수가 없이 사용하는 경우 두 가지로 구분된다. 두가지 경우로 구분하는 이유는 DISTINCT 키워드가 영향을 미치는 범위가 달라지기 때문

SELECT DISTINCT

SELECT DISTINCT first_name, last_name FROM employees;

first_name+last_name 전체가 유니크한 레코드

집합 함수와 함께 사용된 DISTINCT

EXPLAIN
SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;

• 이 쿼리의 실행 계획의 Extra 칼럼에는 출력되진 않지만 COUNT(DISTINCT s.salary) 의 처리에 인덱스 대신 임시테이블을 사용
• 위의 쿼리에서는 employees 테이블과 salaries 테이블을 조인한 결과에서 salary 칼럼의 값만 저장하기 위해 임시 테이블을 만들어 사용한다. 임시테이블 salary 칼럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리

SELECT COUNT(DISTINCT s.salary), COUNT(DISTINCT e.last_name)
FROM employees e, salaries s
WHERE  e.emp_no=s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;

위의 쿼리는 DISTINCT 처리를 위해 인덱스를 이용할수 없기 때문에 임시테이블이 필요

SELECT COUNT(DISTINCT emp_no) FROM employees;
SELECT COUNT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no;

쿼리 같이 인덱스된 칼럼에 대한 DISTINCT는 인덱스를 이용해 효율적으로 처리

JOIN의 순서와 인덱스

인덱스 레인지 스캔으로 레코드를 읽는 순서를 정리

1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색(Index seek)이라고 한다.
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 죽 읽는다. 이 과정을 인덱스 스캔(Index scan)이라고 한다.
3. 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

• 인덱스 풀 스캔이나 테이블 풀 스캔은 인덱스 탐색(Index seek)과정이 거의 없지만 실제 인덱스나 테이블의 모든 레코드를 읽기 때문에 부하가 높다. 하지만 인덱스 레인지 스캔 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 인덱스 스캔(Index scan) 과정은 부하가 작지만 특정 인텍스 키를 찾는 인덱스 탐색 과정이 상대적으로 부하가 높은 편이다.
• 옵티마이저는 항상 드라이빙 테이블이 아닌 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립

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

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

어느 쪽 테이블이 드라이빙 테이블이 되든 옵티마이저가 선택하는 방법이 최적일 때가 많다.

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

옵티마이저는 항상 dept_emp 테이블을 드라이빙 테이블로, employees 테이블을 드리븐 테이블로 선택

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

employees 테이블을 드라이빙 테이블로 선택하고 dept_emp 테이블을 드리븐 테이블로 조인을 수행하도록 실행 계획을 수립

두 칼럼 모두 인덱스가 없는 경우

레코드 건수가 적은 테이블을 드리븐 테이블로 선택하는 것이 훨씬 효율적

JOIN 칼럼의 데이터 타입

타입을 반드시 동일한하게 사용

OUTER JOIN 주의사항

OUTER로 조인되는 테이블의 칼럼에 대한 조건은 모두 ON절에 명시해야 한다. 조건을 ON 절에 명시하지 않고 다음 예제와 같이 OUTER 테이블의 칼럼이 WHERE 절에 명시하면 옵티마이저가 INNER JOIN과 같은 방법으로 처리한다.

OUTER JOIN을 이용한 ANTI JOIN

• 두 개의 테이블에서 한쪽 테이블에서는 있지만 다른 테이블에 없는 레코드를 검색할 때 ANTI JOIN을 사용
• IN(subquery)나  NOT IN(subquery) 도 상당히 비효율적을 작동한다. 조회하는 레코드 건수가 적다면 NOT EXISTS로 처리해도 별 문제가 없다. 하지만 처리해야 할 레코드 건수가 많다면 OUTER JOIN을 이용한 ANTI JOIN을 사용하는 방법이 좋은 해결책일 것이다.

INNNER JOIN과 OUTER JOIN의 선택

• INNER JOIN은 조인의 양쪽 테이블 모두 레코드가 존재하는 경우에만 레코드가 반환된다. 하지만 OUTER JOIN은 아우터 테이블에 존재하면 레코드가 반환
• 사실 OUTER JOIN과 INNER JOIN은 실제 가져와야 하는 레코드가 같다면 쿼리의 성능은 거의 차이가 없다.

FULL OUTER JOIN 구현

• MySQL에는 FULL OUTER JOIN을 제공하지 않는다.
• 두 개의 쿼리 결과를 UNION으로 결합하면 FULL OUTER JOIN의 효결과를 얻을 수 있다.

JOIN과 FOREIGN KEY

FOREIGN KEY를 생성하는 주 목적은 데이터의 무결성을 보장하기 위해서다. FOREIGN KEY와 연관된 무결성을 참조 무결성이라고 표현한다.

*** 실무에서는 모두 fk로 연결하지는 않는다고 한다.

지연된 조인(Delayed Join)

• 지연된 조인이란 조인이 실행되기 이전에 GROUP BY나 ORDER BY를 처리하는 방식을 의미

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;
실제로 이러한 쿼리를 많이 사용한것 같다.

SELECT *
FROM dept_emp de, employees e
WHERE de.dept_no = 'd001' AND e.emp_no = de.emp_no
LIMIT 100, 10;

사용자가 필요로 하는 데이터는 10건이므로 조인을 해서 가져왔던 앞쪽 100건의 데이터는 불필요하게 읽은 것이 된다.

SELECT *
FROM ( SELECT * FROM dept_emp WHERE dept_no='d001' LIMIT 100, 10 ) de,
employees e
WHERE e.emp_no = de.emp_no;

dept_emp 테이블에서 100번째부터 10개의 레코드만 가져오는 서브 쿼리로 FROM  절의 dept_emp 테이블을 대체했다. 이 서브 쿼리의 결과가 저장도니 임시 테이블의 레코드 10건과 employees 테이블을 조인했다. 즉, 꼭 필요한 레코드만 조인을 수행한 것이다. 이처럼 쿼리를 변경해 불필요한 조인 10번을 없앤 것이다.