/
/

[Real MySQL] 11. 쿼리 작성 및 최적화 (2) - 연산자

avatar
chae
2025.03.27
·
10 min read
44254427

(1)에 이어서 작성한다. 11.3.2 MySQL 연산자 내용을 정리했으며, 내장함수는 내용이 길어서 다음 포스팅에 작성

🔍11.3 MySQL 연산자와 내장함수

🖇11.3.2 MySQL 연산자

11.3.2.1 Equal 비교

  • = 를 통해 비교 하지만 MySQL은 <=>도 제공

  • <=>는 =와 같지만 부가적으로 NULL값에 대한 비교도 수행 (NULL-Safe 비교 연산자)

  • =는 한쪽이 NULL이면 비교도 NULL이지만, <=>는 1이나 0으로 반환

11.3.2.2 Not-Equal 비교

  • <> , != 사용 가능

11.3.2.3. NOT

  • ! 로 사용

11.3.2.4 AND와 OR 연산자

  • &&, ||로 사용 , AND와 OR도 허용

  • 오라클에서 ||는 문자열을 결합하는 연산자

  • TRUE OR (FALSE AND FALSE) = 1로 FALSE AND FALSE의 AND연산자를 먼저 처리

11.3.2.5 나누기와 나머지 연산자

  • 나누기는 /로, 나머지는 % 또는 MOD를 사용

11.3.2.6 REGEXP 연산자

  • 문자열 값이 어떤 패턴을 만족하는지 확인한다

  • RLIKE: REGEXP와 똑같은 비교를 수행하는 연산자

  • select 'abc' regexp '^[x-z]';

    • abc라는 문자열 값이 x,y,z 문자로 시작하는지 검증하는 표현식의 예

* 정규 표현식 패턴 키워드

  • ^: 문자열 시작 표시

  • $: 문자열 끝을 표시

  • []: 문자 그룹을 표시, 문자 하나와 일치하는지 확인

  • (): 문자열 그룹표시, 모든 문자가 있는지 확인

  • |: |로 연결된 문자열 중 하나인지 확인, abc|xyz는 abc이거나 xyz인지 확인

  • .: 어떠한 문자든지 1개의 문자를 표시

  • *: 이 기호 앞에 표시된 정규표현식이 0또는 1번 반복

  • +: 이 기호 앞에 표시된 정규표현식이 1번 이상 반복

  • ?: 이 기호 앞에 표시된 정규표현식이 0또는 1번만 올 수 있음

[0-9]* # 0~9까지의 숫자만 0또는 1번 이상 반복되는 문자열을 위한 정규표현
[a-z]* # a~z까지의 소문자 알파벳만 0또는 1번 이상 반복되는 문자열을 위한 정규표현
[a-zA-Z]* # a~z까지, A-Z까지 대소문자 알파벳만 0또는 1번 이상 반복
[a-zA-Z0-9]* #영문 대소문자와 숫자만으로 구성된 문자열에 대한 정규표현
^Tear # Tear 문자열로 시작하는 정규 표현
Tear$ # Tear 문자열로 끝나는 정규 표현
^Tear$ # Tear와 같은 문자열에 대한 정규표현

11.3.2.7 LIKE 연산자

  • 인덱스를 이용해 처리할 수 있다.

  • 어떤 상수 문자열이 있는지 없는지 판단

  • 와일드카드 문자는 %, _가 전부

    • % : 0또는 1개 이상의 모든 문자에 일치

    • _ : 정확히 1개의 문자에 일치

EXPLAIN
SELECT COUNT(*)
FROM employees
WHERE first_name LIKE 'Christ%';

# employees 테이블에서 Christ로 시작하는 이름을 검색 
# 인덱스 레인지 스캔 사용 

EXPLAIN
SELECT COUNT(*)
FROM employees
WHERE first_name LIKE '%rist';

# rist로 끝나는 이름을 검색할때는 와일드카드가 검색어 앞에 있음
# 이 경우에는 인덱스의 Left-most 특성으로 레인지 스캔 불가 
# 인덱스를 처음부터 끝까지 읽는 인덱스 full 스캔 방식으로 쿼리 처리

11.3.2.8 BETWEEN 연산자

  • 크거나 같다와 작거나 같다를 합친 연산자

주의해야할 예시

  1. dept_emp 테이블에는 (dept_np, emp_no) column으로 구성된 기본키 존재

  2. 밑의 첫 번째 쿼리는 범위를 줄여주는 방법으로 사용 가능

  3. 두 번째 쿼리에서 사용한 BETWEEN은 범위를 읽어야하는 연산자라, dept_no가 d003보다 크거나 같고 d005보다 작거나 같은 모든 인덱스의 범위를 검색해야함

  4. 결국 두번째 쿼리에서 emp_no=10001 조건은 비교 범위의 줄이는 역할을 하지 못한다.

SELECT * FROM dept_emp 
WHERE dept_no='d003' AND empno = 10001;

SELECT * FROM dept_emp
WHERE dept_no BETWEEEN 'd003' AND 'd005' AND emp_no=100001;

BETWEEN과 IN

  • 위의 쿼리를 다음과 같은 형태로 바꾸면 emp_no=10001 조건도 작업 범위를 줄이는 용도로 인덱스를 이용할 수 있게 된다.

    • 이는 IN 연산자가 = 연산자와 처리 방식이 같음에 있다.

  • BETWEEN이 선형으로 인덱스를 검색하는 것과 달리 IN은 동등 비교를 여러 번 수행한 것과 같은 효과가 있다. -> dept_emp table의 인덱스(dept_no, emp_no)를 최적으로 사용

  • 이 예제처럼 여러 칼럼으로 인덱스가 만들어져 있는데, 인덱스 앞쪽에 있는 칼럼의 선택도가 떨어질때 IN으로 변경하는 방법으로 쿼리의 성능이 개선가능하다. 😀😀

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

BETWEEN과 IN의 인덱스 사용 방법 차이

  • BETWEEN을 사용한 쿼리와 IN을 사용한 쿼리 둘 다 인덱스 레인지 스캔을 하고 있지만 실행 계획의 rows 칼럼에 표시된 레코드 건수는 매우 큰 차이가 있음

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

IN(subquery)

예전 버전의 MySQL 서버에서는 BETWEEN 연산자를 IN으로 변경하기 위해서 우선 dept_no column의 값이 d003과 d005 사이의 모든 부서 코드 값을 가져와 dept_no IN ('d003', 'd004', 'd005')조건을 만들어야했다. 하지만 MySQL 8.0부터는 다음과 같이 IN (subquery) 형태로 작성하면 옵티마이저가 세미 조인 최적화를 이용해 더 빠른 쿼리로 변환해서 실행한다.

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

-> JOIN은 성능을 떨어뜨린다고 알고있었는데 왜 세미조인 최적화로 바꿔준다는걸까?

위와 같은 방식은 서브 쿼리 결과를 먼저 계산하고 그 결과를 가지고 employees 테이블에서 조건을 건다. 하지만 이 방식은 서브쿼리가 매번 재실행될 수 있고 겨로가가 많을 경우 IN (...) 리스트가 커져 버린다. 옵티마이저가 서브쿼리의 내용을 잘 파악하지 못해서 비효율적인 실행계획을 세울 수 있다.

11.3.2.9 IN 연산자

  • 여러 개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리

  • 다음과 같이 두 형태를 구분

    • 상수가 사용된 경우 - IN ( ? , ? , ? )

    • 서브쿼리가 사용된 경우 - IN (SELECT .. FROM ..)

  • MySQL 8.0버전부터는 IN(subquery)같은 세미 조인의 최적화가 많이 안정

  • NOT IN의 실행 계획은 인덱스 풀 스캔으로 표시

- 컬렉션 아티클