WHERE,GROUP BY, ORDER BY 절 인덱스 사용
인덱스를 사용하려면 칼럼 값을 변환하지않고, 그대로 사용해야함
SELECT * FROM SALAIES WHERE SALARY*10 > 15000; // (X)
SELECT * FROM SALAIES WHERE SALARY > 15000/10; // (O)
데이터 비교 시 타입이 같아야함
WHERE 절은 A인덱스, GROUP BY(ORDER BY)는 B인덱스를 사용 할순 없음
- WHERE, GROUP BY(ORDER BY) 모두 같은 인덱스 사용 : 가장 좋은 방법
- WHERE 절만 인덱스 사용 : 조건에 일치하는 데이터가 많지 않을 때 좋은 방법
- ORDER BY만 인덱스 사용 : 많은 양의 데이터를 조회해서 정렬할때 사용하는 방법 (정렬된 데이터를 조건에 부합하는지 확인함)
WHERE절의 인덱스 사용
WHERE 조건 순서와 인덱스 순서는 무관함
인덱스 사용할 수 있는 조건을 알아서 뽑아서 잘 사용함
GROUP BY절의 인덱스 사용
GROUP BY 절에 명시된 칼럼의 순서와 인덱스 칼럼의 순서가 같으면 인덱스 사용 가능함
순서가 하나라도 다르거나 없으면 사용 못함
ORDER BY절의 인덱스 사용
오름차순, 내림차순 옵션이 인덱스와 같거나 정반대의 경우에만 인덱스 사용이 가능함
인덱스의 모든 칼럼이 ORDER BY에 사용돼야 하는건 아니지만, 왼쪽부터 순서는 맞아야함
GROUP BY, ORDER BY 동시 인덱스 사용
둘중 하나라도 인덱스 사용 불가하면 둘다 사용 못함
정리
WHERE 인덱스 사용가능한가? GROUP BY가 인덱스 사용 가능한가?
GROUP BY, ORDER BY 둘다 인덱스 사용 가능한가?
예예예 ⇒ 셋다 사용
예예노 ⇒ 조건만 사용
예노노 ⇒ 조건만 사용
노노노 ⇒ 아무도 못씀
노노예 ⇒ 아무도 못씀
노예예 ⇒ 그룹, 정렬만 사용
JOIN 순서와 인덱스
인덱스 스캔 : 인덱스를 이용해서 데이터를 가져오는 작업
인덱스 탐색 : 특정인덱스 키를 찾는 과정
옵티마이저는 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립함 ⇒ 드라이빙 테이블은 인덱스 탐색 및 스캔의 과정이 1번 뿐이지만, 드리븐 테이블은 드라이빙 테이블의 데이터 수 만큼 인덱스 탐색과 스캔의 과정이 반복적으로 이루어지기 때문
❓조인문 이렇게도 쓸 수 있는건가요??
SELECT * FROM EMPLOYEE E, DEPT_EMP DE WHERE E.EMP_NO = DE.EMP_NO
- 두 칼럼 모두 인덱스가 있음 어느 테이블을 드리븐 테이블이 되든 인덱스를 이용해 빠른 처리가 가능함 (아마 데이터가 적은게 드라이빙 테이블이 될거임)
- 둘중 하나만 인덱스가 있음 E.EMP_NO가 있다면, 인덱스가 없는 DEPT_EMP 테이블을 드라이빙 테이블로 선택하고, 인덱스가 있는 테이블을 드리븐으로 두어 데이터가 많아도 인덱스를 통해 빠르게 조회함
- 두 칼럼 모두 인덱스가 없음 어떤 테이블을 선택하든 풀스캔이므로 그나마 데이터가 적은 테이블을 드라이빙 테이블로 사용함
JOIN 칼럼의 데이터 타입
조인 칼럼 간의 비교에서 칼럼의 데이터 타입이 일치하지 않으면 이덱스를 효율적으로 이용할 수 없음
일때 데이터 타입이 다를 경우 VARCHAER를 INT 로 변환해서 사용하기 때문에 비효율적
OUTER JOIN 성능과 주의사항
SELECT * FROM EMPLOYEE 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";
아우터 조인 테이블은 드라이빙 테이블로 사용하지 못함 ⇒ EMPLOYEE 을 드라이빙 풀스캔, DEPT_EMP, DEPARTMERNS를 드리븐 테이블로 사용
INNER JOIN을 사용했다면, DEPARTMERNS 테이블에서 D.DEPT_NAME = "DEVELOPMENT" 인 레코드를 1건을 가지고 드리븐 테이블을 조회 가능했음
고로, 왠만해선 아우터 조인 보다는 이너 조인을 사용하면 좋음!
SELECT * FROM EMPLOYEE E
LEFT JOIN DEPT_MANAGER MGR ON MGR.EMP_NO = E.EMP_NO
WHERE MGR.DEGT_NO = "d001";
아우터 조입의 조건을 WHERE에 명시 할 경우 옵티마이저가 이를 INNER JOIN으로 변환해서 실행함
SELECT * FROM EMPLOYEE E
LEFT JOIN DEPT_MANAGER MGR ON MGR.EMP_NO = E.EMP_NO AND MGR.DEGT_NO = "d001";
이렇게 사용해야 아우터 조인을 사용 할 수 있음
JOIN과 외래키
외래키는 조인과 아무런 연관이 없음
외래키는 데이터의 무결성을 보장하기 위해 사용함 → 참조 무결성
지연된 조인
쿼리문은 모든 JOIN을 실행하고 GROUP BY, ORDER BY를 처리함
지연된 조인 : JOIN을 실행 하기 전 GROUP BY, ORDER BY를 처리하고 JOIN을 실행함
LIMIT 과 실행 시 성능이 더 좋아짐
지연된 조인의 조건
OUTER JOIN : 드라이빙 테이블과 드리븐 테이블응 1:1, M:1 관계
INNER JOIN : 드라이빙 테이블과 드리븐 테이블응 1:1, M:1 관계, 드라이빙 테이블의 데이터는 드리븐 테이블에 모두 존재 해야함
래터럴 조인
여긴 어렵다.. 무슨 말이지..
❓ 지연 조인이랑 무슨 차이죠..? 둘다 조인문이 가장 마지막에 되는것 같은데..
GROUP BY
특정 칼럼의 값으로 데이터를 그룹핑하고, 그룹별 집계된 결과를 하나의 데이터로 조회 할 때 사용
WITH ROLLUP
엑셀의 피벗 테이블과 같은 기능
그룹별로 조회 된 데이터의 칼럼은 NUll 로 표시됨
ORDER BY
데이터를 정렬할 때 사용
인덱스 사용 시 : 인덱스에 정렬된 순서대로 정렬
인덱스 X, 풀스캔 : INSERT된 순서가 아닐 수 있음(데이터가 삭제된 빈자리에 생성되니까) 다만, INNODB의 경우 풀스캔 시 프라이머리키 순서대로 정렬됨
ORDER BY 2 : 두번째 칼럼을 기준으로 정렬
ORDER BY 절 뒤 숫자 값이 안니 문자열 칼럼을 사용하는 경우 정렬되지 않음 이땐 ‘’ 따옴표를 이용해 문자 리터럴로 정렬하라고 해야함
SELECT * FROM EMPLOYEE ORDER BY "LAST_NAME";
❓ 그냥 사용하면 정말 정렬 안되나요? 지금까지 잘된것 같은데..