[📕 Real MySQL 8.0 (2)] 쿼리 작성 및 최적화 - MySQL 내장함수(2)

1. 값의 비교와 대체 (CASE WHEN ... THEN ... END)

• CASE WHEN은 함수가 아니라 SQL구문
• 프로그래밍 언어에서 제공하는 SWITCH 구문과 같은 역할 
• CASE로 시작하고, END로 끝나야 함
• WHEN ... THEN ... 은 필요한 만큼 반복해서 사용
• 단순히 코드값을 실제 값으로 변환하거나 특정 일자를 기준으로 이전인지 이후인지 비교해 설명을 붙이는 용도로 사용
• CASE WHEN 절이 일치하는 경우에만 THEN 이하의 표현식이 실행됨

---

🔹 Marketing(d001) 부서에 소속된 적이 있는 모든 사원의 가장 최근 급여 조회

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 DECS LIMIT 1) AS last_salary
FROM dept_emp de, employees e
WHERE e.emp_no=de.emp_no
	AND de.dept_no='d001';

만약 성별이 여자인 경우에만 최종 급여 정보가 필요하고, 남자는 그냥 이름만 필요하다면...

👉 위의 쿼리를 그대로 사용하면서 남자일 때는 가져온 last_salary 컬럼을 그냥 버리면 된다. 

 

하지만 남자는 그냥 이름만 필요하기 때문에 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';

👉 여자인 경우에만 서브쿼리가 실행되므로 남자 사원의 수만큼 서브쿼리의 실행 횟수를 줄일 수 있다 !

 

 

 

2. 타입의 변환 (CAST, CONVERT)

🔷 CAST()

• 변환할 수 있는 데이터 타입
  👉 DATE, TIME, DATETIME, BINARY, CHAR, DECIMAL, SIGNED INTEGER, UNSIGNED INTEGER
• 하나의 인자에서 첫 번째 부분에 타입을 변환할 값이나 표현식을, 두 번째 부분에 변환하고자 하는 데이터 타입 명시 👉 AS로 구분 
• 문자열, 숫자, 날짜 변환은 명시적으로 해주지 않아도 자동으로 필요한 형태로 변환하나, SIGNED나 UNSIGNED와 같은 부호 있는 정수 또는 부호 없는 정수값의 변환은 그렇지 않을 때가 많으므로 명시적으로 타입 변환을 해줘야 함

SELECT CAST(1-2 AS UNSIGNED);

+-------------------------+
| CAST(1-2 AS UNSIGNED)   |
+-------------------------+
| 4294967295              |
+-------------------------+

 

 

🔷 CONVERT()

• CAST() 처럼 타입을 변환하는 용도
• 문자열의 문자 집합을 변환하는 용도 
• 타입 변환 시 첫 번째 인자로 변환하려는 값이나 표현식 명시, 두 번째 인자로 변환하려는 데이터 타입 명시
• 문자열의 문자 집합 변경 시 첫 번째 인자로 변환하고자 하는 값이나 표현식을, 두 번째 인자에는 문자 집합의 이름 지정
  첫 번째와 두 번째 부분의 구분자로 USING 키워드 명시

SELECT CONVERT(1-2, UNSIGNED);

+--------------------------+
| CONVERT(1-2, UNSIGNED)   |
+--------------------------+
| 4294967295               |
+--------------------------+

SELECT CONVERT('ABC' USING 'utf8mb4');

+-----------------------------+
| CONVERT('ABC' USING utf8mb4)|
+-----------------------------+
| ABC                         |
+-----------------------------+

 

 

 

3. 이진값과 16진수 문자열 (Hex String) 변환(HEX, UNHEX)

🔷 HEX()

• 이진값을 사람이 읽을 수 있는 형태의 16진수의 문자열로 변환

 

🔷 UNHEX()

• 16진수의 문자열을 읽어서 이진값(Binary)로 변환

---

 

4. 암호화 및 해시 함수 (MD5, SHA, SHA2)

비대칭형 암호화 알고리즘으로, 인자로 전달한 문자열을 각각 지정된 비트 수의 해시값을 만들어내는 함수
👉 사용자의 비밀번호와 같은 암호화가 필요한 정보를 인코딩하는 데 사용됨 
👉 함수들의 결과값은 중복 가능성이 매우 낮으므로 길이가 긴 데이터를 크기를 줄여 인덱싱(해시)하는 용도로 사용됨 

 

 

🔷 SHA()

• SHA-1 암호화 알고리즘을 사용하며 결과로 160비트(20 바이트) 해시값을 반환

 

🔷 SHA2()

• SHA 암호화 알고리즘보다 더 강력한 224비트부터 512비트 암호화 알고리즘을 사용해 생성된 해시값 반환

 

🔷 MD5()

• 메시지 다이제스트 알고리즘을 사용해 128비트(16바이트) 해시값 반환
• 입력된 문자열의 길이를 줄이는 용도로 사용

---

🌟 MD5() 활용 예시

• URL 같은 값은 전체적으로 길이가 긴 편이라 이러한 데이터를 검색하기 위해서는 인덱스가 필요함
• 긴 컬럼에 대해 전체 값으로 인덱스를 생성하는 것은 불가능하며 공간 낭비도 커짐 
• URL의 값을 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)) )
);

 

-- // MD5 해시 값으로 검색하면 원하는 결과 출력됨 (평문으로 검색 X)

SELECT * 
FROM tb_accesslog 
WHERE MD5(access_url) = MD5('http://matt.com');

 

저장 공간을 더 줄이고자 하면....

👉 MD5() 함수의 결과를 UNHEX() 함수를 이용해 이진값으로 만듦 👉 메모리 사용량 조금 더 줄일 수 있음 !!

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 ( UNHEX((MD5(access_url))) )
);

 

 

 

5. 처리 대기 (SLEEP)

🔷 SLEEP()

• 쿼리 실행 도중 멈춰서 대기 
• 개발이나 디버깅 용도로 잠깐 대기하거나 일부러 쿼리의 실행을 오랜 시간 유지하고자 할 때 유용
• 대기할 시간을 초 단위로 인자를 받음

 

 

 

6. 벤치마크 (BENCHMARK)

🔷 BENCHMARK()

• 디버깅이나 간단한 함수의 성능 테스트용으로 유용
• 첫 번째 인자는 반복해서 수행할 횟수
• 두 번째 인자는 반복해서 실행할 표현식 👉 반드시 스칼라값(하나의 컬럼을 가진 하나의 레코드)을 반환하는 표현식이어야 함
• 지정한 횟수만큼 반복 실행하는 데 얼마나 시간이 소요됐는지가 중요 
  • 해당 함수로 얻은 쿼리나 함수의 성능은 그 자체로는 큰 의미 X
  • 두 개의 동일 기능을 상대적으로 비교 분석하는 용도로 사용 권장

---

⛔ 주의사항

 

"SELECT BENCHMARK(10, expr)"과 "SELECT expr"을 10번 직접 실행하는 것은 차이 있음

• 10번 직접 실행하는 경우에는 매번 쿼리의 파싱이나 최적화, 테이블 잠금이나 네트워크 비용 등이 소요됨
• BENCHMARK() 사용 시 벤치마크 횟수에 관계없이 단 1번의 네트워크, 쿼리 파싱 및 최적화 비용이 소요됨.
  메모리 할당도 직접 10번 실행하는 것보다 1/10밖에 일어나지 않음

 

 

7. IP 주소 변환 (INET_ATON, INET_NTOA)

IP 주소는 4바이트의 부호 없는 정수인데, 대부분의 DBMS에서는 IP 정보를 VARCHAR(15) 타입에 '.'으로 구분해서 저장하므로 저장공간이 훨씬 많이 필요

👉 MySQL에서 제공하는 함수를 이용해 IPv4 주소를 문자열이 아닌 부호 없는 정수 타입에 저장할 수 있게 제공

 

 

🔷 INET_ATON()

• 문자열로 구성된 IPv4 주소를 정수형으로 변환

 

🔷 INET_NTOA()

• 정수형의 IPv4 주소를 사람이 읽을 수 있는 형태의 '.'으로 구분된 문자열로 변환

INET6_ATON(), INET6_NTOA() 함수를 이용하면 IPv6 주소 변환 가능