[Real MySQL] 11. 쿼리작성 및 최적화 (3) - 내장함수
11.3.3 MySQL 내장함수
함수의 이름이나 사용법은 표준이 없어서 DBMS별로 거의 호환이 되지 않는다. MySQL 함수는 기본으로 제공하는 내장 함수와 사용자가 직접 작성해서 추가할 수 있는 사용자 정의함수(UDF)로 구분된다. MySQL에서 제공하는 C/C++ API를 사용해 사용하자 원하는 기능을 직접 함수로 만들어 추가할 수 있다.
11.3.3.1 NULL 값 비교 및 대체
IFNULL(): 칼럼이나 표현식의 값이 NULL인지 비교하고 NULL이면 다른 값으로 대체ISNULL(): 인자로 전달한 표현식이나 칼럼의 값이 NULL인지 아닌지 비교
11.3.3.2 현재 시각 조회
NOW(), SYSDATE(): 모두 현재 시간을 반환
하나의 SQL에서 모든 NOW()함수는 같은 값을 가지지만 SYSDATE() 함수는 하나의 SQL 내에서도 호출되는 시점에 따라 결과값이 달라진다.
SYSDATE() 함수는 이러한 특성때문에 두 가지 큰 잠재적 문제가 있다.
SYSDATE() 함수가 사용된 SQL은 레플리카 서버에서 안정적으로 복제되지 못함
SYSDATE() 함수와 비교되는 칼럼은 인덱스를 효율적으로 사용하지 못함
꼭 필요한 때가 아니면 SYSDATE()를 사용하지 않는게 좋겠지만 사용하고있다면 MySQL의 서버 설정파일에 sysdate-is-now 시스템 변수를 넣어 활성화하자. 사실 일반적인 웹 서비스에서 특별히 SYSDATE()를 사용해야 할 이유가 없다.
11.3.3.3 날짜와 시간의 포맷
DATE_FORMAT(): DATETIME 타입의 칼럼이나 값을 원하는 형태의 문자열로 변환%Y: 4자리 연도
%m: 2자리 숫자 표시의 월 (01~12)
%d: 2자리 숫자 표시의 일자 (01~31)
%H: 2자리 숫자 표시의 시 (00~23)
%i: 2자리 숫자 표시의 분 (00~59)
%s: 2자리 숫자 표시의 초 (00~59)
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS current_dt;
# 날짜 포맷 변경을 위한 지정자는 모두 대소문자를 구분해서 사용
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_dttm;
# SQL에서 표준 형태(년-월-일 시:분:초)로 입력된 문자열은 자동으로 DATETIME 타입으로 변환
# 그렇지 않으면 STR_TO_DATE()를 사용해 문자열을 DATETIME 타입으로 변환한다.
SELECT STR_TO_DATE('2020-08-23', '%Y-%m-%d') AS current_dt;11.3.3.4 날짜와 시간의 연산
DATE_ADD(),DATE_SUB(): 특정 날짜에서 연도나 월일 또는 시간을 더하거나 빼기모두 두 개의 인자를 필요로 함
첫 번째 인자는 연산을 수행할 날짜, 두 번째 인자는 더하거나 빼고자 하는 월의 수나 일자의 수
두 번째 인자는 INTERVAL n[YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, ...] 형태로 입력해야 한다. 여기서 n은 더하거나 빼고자 하는 차이 값이며, 그 뒤에 명시되는 단위에 따라 하루를 더할지 한 달을 더할지 결정
단위: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND, QUARTER, WEEK
11.3.3.5 타임스탬프 연산
UNIX_TIMESTAMP(): 1970-01-01 00:00:00으부터 경과된 초의 수를 반환하는 함수인자가 없으면 현재 날짜와 시간의 타임스탬프 값을, 인자로 특정 날짜를 전달하면 그 날짜와 시간의 타임스탬프를 반환
FROM_UNIXTIME(): UNIX_TIMESTAMP() 함수와 반대로 인자로 전달한 타임스탬프 값을 DATETIME 타입으로 변환하는 함수
11.3.3.6 문자열 처리
RPAD(),LPAD()함수: 문자열의 좌측 또는 우측에 문자를 덧붙여서 지정된 길이의 문자열로 만드는 함수3개의 인자: 패딩 처리를 할 문자열, 몇 바이트 패딩할 것인지, 어떤 문자를 패딩할 것인지
RTRIM(),LTRIM(): 문자열의 우측 또는 좌측에 연속된 공백 문자를 제거하는 함수TRIM(): RTRIM(), LTRIM()을 동시에 수행
SELECT RPAD('Close', 10, '_');
# CLOSE_____
SELECT LPAD('123', 10, '0');
# 0000000123
SELECT RTRIM('CLoee ') AS name;
# CLoee
SELECT LTRIM(' Cloee') as name;
# CLoee
SELECT TRIM(' Cloee ') as name;
# Cloee11.3.3.7 문자열 결합
CONCAT(): 여러 개의 문자열을 연결해 하나의 문자열로 반환하는 함수, 인자의 제한 x숫자값을 인자로 전달하면 문자열 타입으로 자동 변환 후 연결
의도된 결과가 아닌 경우에는 명시적으로 CAST() 함수를 이용해 타입을 문자열로 변환하는 것이 안전
CONCAT_WS(): 각 문자열을 연결할 때 구분자를 넣어주는 함수
11.3.3.8 GROUP BY 문자열 결합
GROUP_CONCAT(): COUNT(), MAX(), MIN(), AVG() 등과 같은 그룹 함수 중 하나주로 GROUP_BY와 함께 사용하며 GROUP BY가 없는 SQL에서 사용하면 단 하나의 결과값만 만들어냄
값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정도 가능하며, 여러 값 중에서 중복을 제거하고 연결하는 것도 가능
select group_concat(dept_no) from departments;
# d009, d005, d002, d003, d001, d004, d006, d008, d007
select group_concat(dept_no SEPARATOR '|') FROM departments;
# d009|d005|d002|d003|d001|d004|d006|dpp8|dpp7
select group_concat(dept_no ORDER BY emp_no DESC) from dept_emp
where emp_no between 10001 and 10003;
# d005, d008, d008, d005
select group_concat(distinct dept_no order by emp_no desc) from dept_emp
where emp_no between 10001 and 10003;
# d008, d005 GROUP_CONCAT() 함수는 지정한 칼럼의 값들을 연결하기 위해 제한적인 메모리 버퍼 공간을 사용한다. 어떤 쿼리에서 이 함수의 결과가 시스템 변수에 지정된 크기를 초과하면 쿼리에서 경고 메세지가 발생한다. JDBC로 실행될 때는 경고가 아니라 에러로 취급되어 쿼리가 실패하기에 결과가 지정된 버퍼 크기를 초과하지 않게 주의하자. -> group_concat_max_len 시스템 변수로 조정할 수 있다!! 버퍼가 1KB 밖에 되지 않기에 자주 사용하면 버퍼의 크기를 적절히 늘려거 설정하자.
11.3.3.9 값의 비교와 대체
CASE WHEN:함수가 아니라 SQL 구문이지만 SWTICH와 같은 구문 역할을 함CASE로 시작, END로 끝나야하며 WHEN ... THEN은 반복 가능
select emp_no, first_name,
case gender when 'm' then 'man'
when 'f' then 'woman'
else 'Unkown' END AS gender
from employees
limit 10;
select emp_no, first_name,
case when hire_date < '1995-01-01' then 'old'
else 'new' end as employee_type
from employees
limit 10;
#################################################
select de.dept_no, e.first_name, e.gender,
(select s.salary from salaries s
where s.emp_no=e.emp_no
order by from_date desc limit 1) as last_salary
from dept_emp de, employees e
where e.emp_no=de.emp_no and de.dept_no='d001';
# 성별이 여자인 경우에만 최종 급여 정보가 필요하고, 남자면 그냥 이름만 필요한 경우
# 남자인 경우에는 salaries 테이블 조회할 필요가 없는데 서브쿼리는 실행되므로 불필요한 작업
# 이런 불필요한 작업을 제거하기 위해 case when으로 서브쿼리를 감싸자.
select de.dept_no, e.first_name, e.gender,
case when e.gender='F' then
(select s.salary from salaries s
where s.emp_no=e.emp_no
order by from_date desc limit 1)
else 0 end as last_salary
from dept_emp de, employees e
where e.emp_no=de.emp_no and de.dept_no='d001;
11.3.3.10 타입의 변환
SQL은 텍스트 기반으로 작동하기에 모든 입력값은 문자열처럼 취급
명시적으로 타입의 변환이 필요하면
CAST()를 사용DATE, TIME, DATETIME, BINARY, CHAR, DECIMAL, SIGNED INTEGER, UNSIGNED INTEGER
하나의 인자를 받아들이며, 그 하나의 인자는 다시 두 부분으로 나뉘어 첫 번째 부분에 타입을 변환할 값이나 표현식을, 두 번째 부분에는 변환하고자 하는 데이터 타입을 명시
select cast('1234' as signed integer) as converted_integer;
일반적으로 문자열, 숫자, 날짜의 변환은 명시적으로 해주지 않아도 자동으로 필요한 형태로 변환하는 경우가 많다. 하지만 SIGNED, UNSIGNED 같은 부호 있는 정수 또는 부호 없는 정숫값의 변환은 그렇지 않을 때가 많다. 이럴 때
select cast(1-2 as unsigned);와 같이 명시적으로 변환하자.CONVERT(): CSAT()와 같이 타입을 변환하는 용도와 문자열의 문자 집합을 변환하는 용도 두 가지로 사용select convert(1-2 , unsigned);select convert('ABC' USING 'utf8bm4');
11.3.3.11 이진값과 16진수 문자열
HEX(): 이진값을 사람이 읽을 수 있는 형태의 16진수 문자열로 변환하는 함수UNHEX(): 16진수 문자열을 이진값으로 변환하는 함수여기서 이진값은 바이너리 값
11.3.3.12 암호화 및 해시 함수
MD5, SHA 모두 비대칭형 암호화 알고리즘으로 인자로 전달한 문자열을 각각 지정된 비트 수의 해시 값을 만들어내는 함수
SHA(): SHA-1 암호화 알고리즘을 사용해 결과로 160비트 해시 값을 반환SHA2(): 224비트부터 512비트 암호화 알고리즘을 사용해 생성된 해시 값 반환MD5(): 메세지 다이제스트 알고리즘을 사용해 128비트 해시 값 반환입력된 문자열의 길이를 줄이는(Digest)용도로 사용
이런 함수들의 결과값은 중복 가능성이 매우 낮기 때문에 길이가 긴 데이터 크기를 줄여 인덱싱 하는 용도로 사용된다. URL 같은 값은 1KB를 넘을 때도 있어 공간낭비가 커지므로 MD5() 함수로 단축하면 16바이트로 저장할 수 있고 이 16바이트로 인덱스를 생성하면 되기 때문에 상대적으로 효율적이다.
create table tb_accesslog(
access_id bigint not null auto_increment,
access_url varchar(1000) not null,
access_dttm datetime not null,
primary key (access_id),
index ix_accessurl (MD5(access_url))
) engine=INNODB;
# access_url 칼럼은 길이가 길지만 이 칼럼에 인덱스를 생성해야 한다.
# 함수 기반의 인덱스를 생성하면 별도 칼럼을 추가하지 않아도된다.
# 메모리 사용량을 줄일 수 있다! 11.3.3.13 처리 대기 (SLEEP)
SLEEP(): 프로그래밍 언어나 셸 스크립트 언어에서 제공하는 sleep 기능을 수행DBMS는 빠르게 쿼리를 처리하는 것을 항상 최선으로 생각하는데, 쿼리 실행 도중 멈춰서 대기하는 기능이 왜 필요할까????
SQL의 개발이나 디버깅 용도로 잠깐 대기하거나 일부러 쿼리의 실행을 오랜 시간 유지하고자 할 때 상당이 유용한 함수이다.
대기할 시간을 초 단위로 인자를 받으며 특별히 어떠한 처리를 하거나 반환 값을 넘겨주지 않는다.
단지 지정한 시간만큼 대기
select sleep(1.5)
from employees
where emp_no between 10001 and 10010;
# employees 테이블에 조회되는 레코드 건별로 1.5초 대기
# 조회되는 레코드가 총 10건이라면 15초동안 쿼리를 실행하는 셈 11.3.3.14 벤치마크
BENCHMARK(): SLEEP()함수와 같이 디버깅이나 간단한 함수의 성능 테스트용으로 아주 유용반복해서 수행할 횟수, 반복해서 실행할 표현식을 입력
두번째 인자는 반드시 스칼라값을 반환하는 표현식일 것
실제로 SELECT BENCHMARK(10, expr)로 실행하면 벤치마크 횟수에 관계없이 단 1번의 네트워크, 쿼리 파싱 및 최적화 비용이 소요
이 함수로 얻은 쿼리나 함수의 성능은 그자체로는 큰 의미는 없으며 두 개의 동일 기능을 상대적으로 비교 분석하는 용도로 사용할 것을 권장
11.3.3.15 IP 주소 변환
INET_ATON(),INET_NTOA(): IPv4 주소를 문자열이 아닌 부호 없는 정수 타입에 저장할 수 있게 제공INET_ATON(): 문자열로 구성된 IPv4 주소 -> 정수형 변환
INET_NTOA(): 정수형의 IPv4 주소 -> 사람이 읽을 수 있는 형태의 . 으로 구분된 문자열로 변환
INE6_ATON(), INET6_NTOA를 이용하면 IPv6 주소 변환 가능
11.3.3.16 JSON 포맷
JSON_PRETTY(): JSON 칼럼의 값을 읽기 쉬운 포맷으로 변환
11.3.3.17 JSON 필드 크기
JSON 데이터는 텍스트 기반이지만 MySQL 서버는 디스크의 저장 공간을 절약하기 위해 JSON 데이터를 실제 디스크에 저장할 때 BSON(Binary JSON) 포맷을 사용
STORAGE_SIZE(): BSON으로 변환됐을 때 저장 공간의 크기가 얼마나 되는지 알려줌
11.3.3.18 JSON 필드 추출
JSON_EXTRACT(): 특정 필드의 값을 가져오기JSON_UNQUOTE(): 따옴표 없이 값만 가져오기각각 -> , ->> 연산자로 사용할 수 있음
select emp_no, json_extract(doc, "$.first_name")
from employee_docs;
# 첫 번째 인자는 데이터가 저장된 칼럼 or JSON 도큐먼트, 두 번째 인자는 JSON Path
select emp_no, doc -> "$.first_name"
from employee_docs limit 2;11.3.3.19 JSON 오브젝트 포함 여부 확인
JSON_CONTAINS(): JSON 도큐먼트 또는 지정된 경로에 JSON 필드를 가지고 있는지 확인하는 함수
11.3.3.20 JSON 오브젝트 생성
JSON_OBJECT(): RDMBS 칼럼의 값을 이용해 JSON 오브젝트를 생성하는 함수
11.3.3.21 JSON 칼럼으로 집계
JSON_OBJECTAGG()&JSON_ARRAYAGG()GROUP BY와 함께 사용되는 집계함수로 RDMBS 칼럼의 값들을 모아 JSON 배열 또는 도큐먼트 생성
11.3.3.22 JSON 데이터를 테이블로 변환
JSON_TABLE(): JSON 데이터의 값들을 모아 RDBMS 테이블을 만들어 반환