[Real MySQL] 16장. 베스트 프랙티 - SQL 작성 표준

조인 조건은 항상 ON 절에 기재하자.

MySQL의 조인 조건은 WHERE절에 모두 모아서 표기할 수도 있고, JOIN 키워드를 이용해 각 ON절에 명시할 수 도 있다.

SELECT *
FROM employees e
  LEFT JOIN dept_manager dm
WHERE e.first_name='Smith'
  AND dm.emp_no=em.emp_no;

하지만, 위와 같은 형태의 쿼리는 MySQL 옵티마이저가 LEFT JOIN이 아니라 INNER JOIN으로 고쳐서 실행해 버리므로, 의도한 결과와는 다른 결과가 나오게 된다.

 

따라서 LEFT JOIN 뿐만 아니라 INNER JOIN에서도 SQL 표준 조인 표기법으로 작성하고 반드시 조인 조건은 WHERE 절이 아니라 조인의 ON 절에 명시하자!

---

테이블 별칭 (Alias) 사용 및 컬럼 명에 테이블 별칭을 포함하자.

다음과 같이 employees와 dept_manager라는 두 테이블을 조인하는 쿼리를 잘 사용하고 있었는데, dept_manager 테이블에 first_name이라는 칼럼이 추가됐다고 가정해보자.

SELECT first_name, last_name
FROM employees e INNER JOIN dept_manager dm
ON dm.emp_no=e.emp_no
WHERE first_name='Will';

dept_manager 테이블에 first_name 칼럼이 추가되는 순간부터 이 쿼리는 '칼럼명이 모호하다'라는 에러를 발생하면서 실행이 멈춰버릴 것이다.

 

가능하다면 여러 테이블의 조인여부와 상관없이 짧은 이름으로 테이블의 별칭(Alias)을 부여하고, 모든 칼럼의 이름 앞에는 테이블의 별칭을 붙이는 습관을 들이자.

---

FULL GROUP BY을 사용하자.

MySQL의 GROUP BY는 FULL GROUP BY의 제약이 없다.

FULL GROUP BY란 GROUP BY 절에 명시된 칼럼 이외의 모든 칼럼은 집합 함수를 통해서만 조회할 수 있는 것을 의미한다.

 

FULL GROUP BY를 사용하지 않는 쿼리는 가독성을 떨어뜨리고 사용자의 실수를 유발시킬 가능성이 높으므로 가능하면 FULL GROUP BY 조건을 충족해서 쿼리를 작성하는 습관을 들이자.

---

문자열 리터럴 표기는 홑따옴표만 사용

SQL 표준에서는 문자열 리터털은 홑 따옴표(')만 사용 가능하고, 쌍따옴표는 식별자에 사용하지만 MySQL에서는 식별자를 표기할 때 역 따옴표(`)를 사용하므로 문자열 리터럴 표기를 위해 쌍따옴표(")까지 사용할 수 있다.

하지만 문자열 리터럴에서 홑따옴표를 사용할 때와 쌍따옴표를 사용할 때의 문자의 이스케이프 방식이 조금 달라서 혼란을 초래할 수 있고, 이로 인해 잘못된 데이터가 저장될 가능성도 있다.

 

따라서 문자열 리터럴은 하나만 선정해서 사용하는 것이 좋은데, 가능하다면 SQL 표준인 홑따욤포를 사용하는 것을 권장한다.

---

서브 쿼리는 조인으로 변경

MySQL에서는 서브 쿼리를 최적화하는 능력이 상당히 부족하다. (MySQL 5.6부터 서브 쿼리의 성능 개선이 많이 이루어지긴 했다.)

쿼리를 작성할 때 FROM 절에 사용될 괄호의 개수만큼 MySQL 서버는 임시 테이블을 만들어 처리한다고 가정하면 될 정도로 취약하다.

 

임시 테이블은 가급적 사용하지 않느 편이 좋은데, 이를 위해서는 가장 먼저 이처럼 불필요한 FROM절의 서브 쿼리를 제거하는 것이 좋다.

또한 조인이 최적화가 상당히 높은 수준이므로 서브 쿼리보다는 조인을 사용하는 것이 여러모로 좋다.

 

참고로 MySQL 5.6 이후의 서브쿼리 최적화 요건은 다음과 같다.

MySQL 5.6 서브쿼리 최적화 요건

출처) https://jojoldu.tistory.com/520

---

UNION은 사용 자제

MySQL의 UNION은 항상 내부적으로 임시 테이블을 만들어 버퍼링 한 다음에 사용자에게 결과를 반환한다. 이 작업은 대량의 레코드를 처리하는 쿼리에서 상당히 부담될 것이다.

여러 집합의 중복된 레코드를 제거해야 하는 UNION DISTINCT를 꼭 사용해야 한다면 특별한 우회 방법은 없다. 하지만 중복 제거 없이

 

UNION ALL로 가능한 쿼리는 두 개의 쿼리 문장으로 분리해서 쿼리를 실행하는 편이 훨씬 더 효율적으로 처리될 수 있다는 점을 기억하자.

 

---

UPDATE, DELETE 쿼리와 적용 건수 체크

일반적으로 한 건의 레코드를 INSERT하는 쿼리는 성공하면 1, 실패하면 0으로 처리된 레코드 건수가 에러 여부에 따라 상당히 명확하다.

하지만 UPDATE, DELETE 문장은 쿼리의 성공적인 실행 여부를 업무적인 정상 처리 여부로 판단하기에는 부족할 수 있다.

반드시 1건이 UPDATE or DELETE 돼야 하는데, 적용된 건수는 체크해보지 않고 무조건 COMMIT 해버린다면 나중에 문제가 될 수도 있다.

 

따라서 프로그램을 작성할 때는 처리된 레코드 건수를 반드시 검증하는 형태의 프로그램 로직을 추가하는 습관을 들이자.

---

숫자 값은 반드시 숫자 타입의 칼럼으로 정의

문자열 타입에 숫자 값이 저장될 때, 문자열 타입에 저장된 숫자 값을 비교하기 위해 "char_type_column=2"와 같은 형태로 쿼리를 사용할 때가 많다. 하지만 이 조건을 위해 MySQL 옵티마이저는 뒤의 숫자 값을 문자열로 바꿔서 비교하는 것이 아니라 앞의 문자열 칼럼을 모두 숫자로 변환해서 비교를 수행한다. 그래서 칼럼에 인덱스가 있어도 이를 이용하지 못하고 풀 테이블 스캔을 수행하거나 인덱스 풀 스캔을 수행할 때가 많다.

 

따라서 순수한 숫자 값은 숫자 타입에 저장하고 알파벳이나 숫자가 혼용되는 값은 CHAR or VARCHAR 타입에 저장하자.