[Real MySQL 8.0] MySQL 연산자와 내장함수

 

애플리케이션에서 데이터를 저장 또는 조회하기 위해 DB와 통신할 때 DB 서버로 전달되는 것은 SQL 뿐이다.
SQL은 어떠한(What) 데이터를 요청하기 위한 언어이지, 어떻게(How) 데이터를 읽을지를 표현하는 언어는 아니므로 자바와 같은 언어와 비교했을 때 제한적으로 느껴질 수 있다.

그래서 쿼리가 빠르게 수행되게 하려면 DB 서버에서 쿼리가 어떻게 요청을 처리할지 예측할 수 있어야 한다.
애플리케이션 코드를 튜닝해서 성능을 개선한다는 것은 쉽지 않지만, DBMS에서는 몇십 몇백 배의 성능 튜닝은 흔한 일이다.
따라서 SQL 작성 방법이나 규칙은 물론, 내부적인 처리 방식(옵티마이저)에 대해 공부해야 한다.

 

1. MySQL 연산자

(1) 날짜

다른 DBMS에서 날짜 타입을 비교하거나 INSERT하려면 문자열을 DATE 타입으로 변환하는 코드가 필요하다.
하지만 MySQL에서는 정해진 형태의 날짜 포맷으로 표기하면 MySQL 서버가 자동으로 DATE나 DATETIME 값으로 변환하기 때문에 복잡하게 STR_TO_DATE() 같은 함수를 사용하지 않아도 된다.

(2) 불리언

BOOL이나 BOOLEAN이라는 타입이 있지만 사실 TINYINT 타입에 대한 동의어일 뿐이다.
BOOL 타입으로 컬럼을 생성해도 결국에는 TINYINT로 명시되어 있다.

또한, MySQL은 C/C++ 언어에서처럼 TRUE 또는 FALSE 같은 불리언 값을 1과 0 정수로 매핑해서 사용한다.

(3) 동등( Equal ) 비교( =, <=> )

다른 DBMS와 마찬가지로 "=" 기호를 사용해서 비교를 수행할 수 있다.
그 외에도 MySQL은 NULL 값에 대한 비교까지 수행할 수 있는 "<=>" NULL-Safe 비교 연산자도 제공한다.

(4) 부정( Not-Equal ) 비교( <>, != )

같지 않음을 비교하기 위한 연산자로 "<>" 를 일반적으로 많이 사용한다.
또한, "!=" 연산자도 사용 가능하므로 둘 중에 하나로 통일하여 사용하는 게 가독성에 좋다.

(5) AND(&&)와 OR(||) 연산자

일반적으로 DBMS에서는 불리언 표현식의 결과를 결합하기 위해 AND나 OR을 사용한다.
MySQL에서는 AND는 "&&", OR은 "||" 과 같은 연산자로 대체 가능하다.
하지만, 오라클에서는 "||"를 불리언 표현식의 결합 연산자가 아니라 문자열을 결합하는 연산자로 사용한다.

따라서, SQL 가독성을 높이기 위해서는 다른 용도로 사용 가능한 "&&"와 "||"는 사용을 자제하는 것이 좋다.

또한, 주의해야 할 부분은 AND가 OR보다 연산 우선순위가 높다는 점이다.

(6) 나누기( /, DIV )와 나머지( %, MOD ) 연산자

"/" 연산자를 사용하면 소수점까지 몫을 가져오고, DIV 연산자를 사용하면 몫의 정수 부분만 가져온다.
나머지를 가져오는 "%"와 MOD 연산자는 똑같은 출력값을 가진다.

(7) LIKE 연산자

REGEXP 연산자는 인덱스를 전혀 사용하지 못한다는 단점이 있지만 LIKE는 인덱스를 사용할 수 있다는 장점이 있다.
LIKE 연산자는 정규 표현식을 검사하는 것이 아니라 어떤 상수 문자열이 있는지 없는지 정도를 판별하는 연산자다.
사용할 수 있는 와일드 카드는 아래와 같다.

• %: 0 또는 1개 이상의 모든 문자에 일치(문자의 내용과 관계없이)
• _: 정확히 1개의 문자에 일치(문자의 내용과 관계없이)

LIKE 연산자는 와일드카드 문자인 "%", "_"가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔으로 사용할 수 있지만 와일드카드가 검색어의 앞쪽에 있다면 인덱스 레인지 스캔을 사용할 수 없으므로 주의해서 사용해야 한다.

(8) BETWEEN 연산자

크거나 같다와 작거나 같다는 두 개의 연산자를 하나로 합친 연산자다.

하지만 주의해야 할 점이 있다. 아래에서 살펴보자.

1. SELECT * FROM dept_emp WHERE dept_no='d003' AND emp_no=10001;

2. SELECT * FROM dept_emp WHERE dept_no BETWEEN 'd003' AND 'd005' AND emp_no=10001;

dept_emp 테이블에는 (dept_no, emp_no) 컬럼으로 구성된 PK가 존재한다고 가정한다.
첫 번째 쿼리는 dept_no와 emp_no 조건 모두 인덱스를 이용해 범위를 줄여주는 방법으로 사용할 수 있다.

하지만, 두 번째 쿼리에서 사용한 BETWEEN 연산자 같은 경우 범위를 읽어야 하므로 'd003'보다 크거나 같고 'd005'보다 작거나 같은 모든 인덱스 범위를 검색해야만 한다. 여기서 emp_no=10001의 조건은 비교 범위를 줄이는 역할을 하지 못한다.
결국 두 번째 쿼리는 dept_emp 테이블의 (dept_no, emp_no) 인덱스의 상당히 많은 레코드를 읽지만 결국 가져오는 데이터는 1건뿐이다.

두번째 쿼리를 아래처럼 변경하면 emp_no=10001 조건도 작업 범위를 줄이는 용도로 인덱스를 이용하도록 할 수 있다.

SELECT * FROM dept_emp
WHERE dept_no IN ('d003', 'd004', 'd005')
	AND emp_no = 10001;

BETWEEN이 선형으로 인덱스를 검색해야 하는 것과는 달리 IN은 동등(Equal) 비교를 여러 번 수행하는 것과 같은 효과가 있기 때문에 dept_emp 테이블의 인덱스(dept_no, emp_no)를 최적으로 사용할 수 있는 것이다.

해당 예제처럼 여러 컬럼으로 인덱스가 만들어져 있을 때, 인덱스 앞쪽에 있는 컬럼의 선택도가 떨어질 때는 IN으로 변경하는 방법으로 쿼리 성능을 개선할 수도 있다.

정리하자면,
BETWEEN 비교를 사용한 쿼리에서는 부서 번호가 'd003'인 레코드로부터 'd005'인 레코드의 전체 범위를 다 비교해야 하지만
IN을 사용한 쿼리에서는 부서번호와 사원 번호가 (('d003', 10001), ('d004', 10001), ('d005', 10001)) 조합인 레코드만 비교해 보면 된다.

MySQL 8.0부터는 아래와 같이 "IN(subquery)" 형태로 작성하면 옵티마이저가 세미 조인 최적화를 이용해 더 빠른 쿼리로 변환해서 실행한다.

SELECT *
FROM dept_emp USE INDEX(PRIMARY)
WHERE dept_no IN (
	SELECT dept_no
        FROM departments
        WHERE dept_no BETWEEN 'd003' AND 'd005')
	AND emp_no=10001;

 

(9) IN 연산자

IN은 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자다.
범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리된다.

IN 연산자를 사용하는 두 가지 케이스를 생각해 볼 수 있다.

• 1. 상수가 사용된 경우 - IN (?, ?, ?)
• 2. 서브쿼리가 사용된 경우 - IN (SELECT ... FROM ... )

1. 상수가 사용된 경우

IN 연산자에 상수가 사용된 경우는 동등 비교와 동일하게 작동하기 때문에 매우 빠르게 쿼리가 처리될 것이다.

SELECT * FROM dept_emp
	WHERE (dept_no, emp_no) IN (('d001', 10017), ('d002', 10144), ('d003', 10054));

위 예시는 IN 절의 상숫값이 단순 스칼라 값이 아니라 튜플이 사용됐다.  MySQL 8.0 이전 버전까지는 IN 절에 튜플(레코드)을 사용하면 항상 풀 테이블 스캔을 했었다. 따라서 이런 쿼리를 실행하면 성능에 문제가 생겨서 일부로 쿼리를 쪼개어 여러 번 실행했다.

하지만, MySQL 8.0 버전부터는 위의 쿼리와 같이 IN 절에 튜플을 그대로 나열해도 인덱스를 최적으로 사용할 수 있게 개선됐다.
따라서 위의 쿼리 같은 경우 dept_emp 테이블의 프라이머리 키를 이용했는데, dept_no 컬럼(4글자*4바이트)과 emp_no 컬럼(4바이트)을 이용해 인덱스 레인지 스캔을 실행한다.

2. 서브쿼리가 사용된 경우

IN (subquery) 형태의 조건이 사용된 쿼리는 최적화가 매우 까다롭지만, MySQL 8.0부터는 최적화가 많이 안정화되어 옵티마이저가 세미 조인 최적화를 이용하여 더 빠른 쿼리로 변환해서 실행한다.

NOT_IN

NOT_IN의 실행 계획은 인덱스 풀 스캔으로 표시되는데, 동등이 아닌 부정형 비교여서 인덱스를 이용해 처리 범위를 줄이는 조건으로는 사용할 수 없기 때문이다. NOT_IN 연산자가 프라이머리 키와 비교될 때 가끔 쿼리의 실행계획에 인덱스 레인지 스캔이 표시되는 경우가 있다. 하지만 이는 InnoDB 테이블에서 PK가 클러스터링 키이기 때문일 뿐 실제 IN과 같이 효율적으로 실행된다는 것을 의미하지는 않는다.

2. MySQL 내장 함수

DBMS 종류와 관계없이 기본적인 기능의 SQL 함수는 대부분 동일하게 제공된다. 하지만 함수 이름이나 사용법의 표준이 없으므로 DBMS별로 거의 호환되지 않는다.
MySQL 함수는 기본적으로 제공하는 내장 함수와 사용자가 직접 작성해서 추가할 수 있는 사용자 정의함수(UDF, User Defined Function)로 구분된다.
사용자 정의함수는 C/C++ API를 이용해 사용자가 원하는 기능을 직접 함수로 만들어 추가할 수 있다.
사용자 정의함수는 스토어드 프로그램으로 작성되는 프로시저나 스토어드 함수와는 다른 것을 알고 있어야 한다.

(1) 내장 함수 종류 간단 정리

• NULL 값 비교 및 대체 (IFNULL, ISNULL)
• 현재 시각 조회 (NOW, SYSDATE)
  • SYSDATE()를 사용하면 레플리카 서버에서 안정적으로 복제되지 못한다.
  • SYSDATE() 함수와 비교되는 컬럼은 인덱스를 효율적으로 사용하지 못한다.
  • SYSDATE() 함수가 호출될 때마다 다른 값을 반환하므로 사실 정수가 아니다.
  • SYSDATE()는 인덱스를 스캔할 때도 매번 비교되는 레코드마다 함수를 실행해야 하지만, 
    NOW()는 쿼리가 실행되는 시점에서 실행되고 값을 할당받아서 그 값을 SQL 모든 부분에서 사용하기에 쿼리가 1시간 동안 실행되더라도 항상 같은 값을 보장한다.
• 날짜와 시간의 포맷 (DATE_FORMAT, STR_TO_DATE)
  • SQL에서 표준 형태(년-월-일 시:분:초)로 입력된 문자열은 필요한 경우 자동으로 DATETIME 타입으로 변환되어 처리된다. 다른 형태도 가능한 것이 더 있긴 하다.
• 날짜와 시간의 연산 (DATE_ADD, DATE_SUB)
• 타임스탬프 연산 (UNIX_TIMESTAMP, FROM_UNIXTIME)
• 문자열 처리 (RPAD, LPAD / RTRIM, LTRIM, TRIM)
• 문자열 결합 (CONCAT)
• GROUP BY 문자열 결합 (GROUP_CONCAT)
  • COUNT()나 MAX(), MIN(), AVG() 등과 같은 그룹 함수 중 하나다. 주로 GROUP BY와 함께 사용하며, GROUP BY가 없는 SQL에서 사용하면 단 하나의 결과값만 만들어낸다.
• 값의 비교와 대체 (CASE WHEN ... THEN ... END)
  • CASE WHEN은 함수가 아니라 SQL 구문이며, 프로그래밍 언어에서 제공하는 SWITCH 구문과 같은 역할을 한다.
    CASE로 시작하고 END로 끝나야 한다. 또한 WHEN ... THEN ... 은 필요한 만큼 반복하여 사용할 수 있다.

SELECT emp_no, first_name,
	CASE gender WHEN 'M' THEN 'Man'
    			WHEN 'F' THEN 'Woman'
                	ELSE 'Unknown' END AS gender
    FROM employees
    LIMIT 10;

• 타입의 변환(CAST, CONVERT)
• 이진값과 16진수 문자열 (Hex String) 변환(HEX, UNHEX)
• 암호화 및 해시 함수 (MD5, SHA, SHA2)
• 처리 대기(SLEEP)
• 벤치마크(BENCHMARK)
• IP 주소 변환 (INET_ATON, INET_NTOA)

복잡한 내장함수를 사용할 경우가 많지는 않으므로 필요할 때 찾아볼 수 있을 정도만 공부하고자 하였다.