[Real MySQL] 쿼리 작성 및 최적화 1

쿼리 작성과 연관된 시스템 변수

영문 대소문자 구분

• MySQL의 경우 설치된 운영체제 따라 테이블명의 대소문자 구분
  • 윈도우의 경우 대소문자 구분 X
  • 유닉스 계열의 경우 대소문자 구분 O
• 따라서 운영체제와 관계없이 대소문자 구분의 영향을 받지 않게 하려면 설정 파일의 lower_case_table_names 시스템 변수를 설정
  • lower_case_table_names=1 로 설정하면 모든 테이블이 소문자로 저장되고 대소문자 구분 X

MySQL 연산자와 내장 함수

숫자

• MySQL 서버에서는 문자열=숫자 간 비교일 때 문자열을 숫자로 자동 변환
  • where 조건 비교가 수행될 때 주의가 필요

select * from tab_test where number_column='10001';

select * from tab_test where string_column=10001;

• 첫 번째 쿼리의 경우 문자열을 숫자로 바꾸기 때문에 비교조건인 ‘10001’만 숫자로 변환
• 두 번째 쿼리의 경우 string_column의 모든 값을 숫자로 바꾼 뒤 10001과 비교를 수행
  • 만약 string_column에 인덱스가 있더라도 값을 변환해 비교하기 때문에 인덱스를 제대로 이용하지 못 한다.

날짜

• 문자열값을 DATE 타입으로 변환해 비교

불리언

• True=1, False=0으로 매핑해 사용
  • 내부적으로도 Boolean 타입은 TINYINT로 처리
• C/C++과 다르게 True는 반드시 1만 의미

MySQL 연산자

• 동등 비교
  • <=> : 기본 = 연산자와 같지만, 추가적으로 NULL을 하나의 값으로 인식해 비교
    • null <=> null : 1
    • null <=> 1 : 0
• AND(&&), OR(||) 연산자
  • MySQL에서는 AND는 연산 우선순위가 OR보다 높다.
• REGEXP 연산자
  • 정규 표현식을 사용한 문자열 매칭 조건
  • REGEXP 조건 비교는 인덱스 레인지 스캔을 사용할 수 없기 때문에 WHERE 조건절에 REGEXP 연산자를 단독으로 사용하는 것은 좋지 않다.
• LIKE 연산자
  • LIKE는 와일드카드 문자가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔을 사용할 수 있다.
  • % : 0 또는 1개 이상의 모든 문자에 일치
  • _ : 정확히 1개의 문자에 일치
• BETWEEN/IN 연산자
  • BETWEEN : ≤ + ≥ 연산
  • IN : = 연산
  • IN은 동등 비교를 여러번 하는 것 vs BETWEEN은 범위 검색
    • 여러 컬럼으로 인덱스가 만들어져 있는데, 인덱스 앞쪽에 있는 컬럼의 선택도가 떨어질 때 IN으로 변경하는 방법으로 쿼리의 성능을 개선할 수도 있다.

MySQL 내장 함수

• 현재 시각 조회 : NOW, SYSDATE
  • 둘 모두 현재의 시간 반환
  • NOW : 하나의 SQL에서 모든 NOW 함수는 같은 값
  • SYSDATE : 하나의 SQL 내에서도 호출되는 시점에 따라 결괏값이 달라짐

select now(), sleep(2), now()

 

select sysdate(), sleep(2), sysdate()

• 이러한 특징 때문에 SYSDATE는 다음과 같은 문제가 존재
  • SYSDATE 함수가 사용된 SQL의 경우 레플리카 서버에 안정적으로 복제되지 못 함
  • SYSDATE 함수와 비교되는 컬럼은 인덱스를 효율적으로 사용하지 못 함
    • SYSDATE는 상수가 아니기 때문에 인덱스 스캔 시 비교되는 레코드마다 함수를 실행해야 함

• CASE WHEN … THEN … END
  • CASE WHEN 구문의 경우 조건에 일치하는 경우에만 THEN 이하의 절이 실행된다.
    • 아무리 무거운 쿼리가 있더라도 조건에 맞지 않으면 실행되지 않는다.

  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)
  from dept_emp de, employees e
  where e.emp_no = de.emp_no
     and de.dept_no='d001'
                 
  [2024-04-29 22:49:23] 500 rows retrieved starting from 1 in 33 ms (execution: 18 ms, fetching: 15 ms)
  

  • 만약 위 쿼리에서 성별이 여자인 경우에만 최종 급여 정보가 필요하고 남자의 경우 이름만 필요하다면, 남자 사원의 급여 정보를 굳이 가져올 필요는 없다.
  • 이러한 상황에서 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 last_salary
   from dept_emp de, employees e
   where e.emp_no = de.emp_no
       and de.dept_no='d001'
       
  [2024-04-29 22:52:12] 500 rows retrieved starting from 1 in 23 ms (execution: 10 ms, fetching: 13 ms)
  

 

• BENCHMARK : 성능 측정
  • 반복 수행할 횟수 및 반복해서 수행할 표현식을 입력
    • 두 번째 인자의 표현식은 반드시 스칼라값을 반환해야 한다.

  select benchmark(100000, (select count(*) from salaries))
  [2024-04-29 22:57:06] 1 row retrieved starting from 1 in 163 ms (execution: 152 ms, fetching: 11 ms)
  

  • 하지만 이렇게 수행하는 경우, 단 한번의 네트워크 전송, 쿼리 파싱 및 최적화가 수행되기 때문에 실제 수행시간과는 차이가 존재
  • 따라서 성능 그 자체로의 의미보다, 쿼리 간의 성능 비교 시에 주로 활용

SELECT

웹 서비스와 같은 일반적인 온라인 트랜잭션 DB의 insert/update는 레코드 단위로 발생하기 때문에 성능 상 문제가 되는 경우가 거의 없다.

• SELECT의 경우는 여러 테이블로부터 데이터를 조합해 가져와야 하기 때문에 성능 상 이슈가 발생할 여지가 많음

SELECT 절 처리 순서

• 예외적으로 ORDER BY가 조인보다 먼저 실행되는 경우도 존재
  • 주로 GROUP BY 절이 없이 ORDER BY만 사용된 쿼리
• with 절(CTE)은 항상 제일 먼저 실행되어 임시 테이블로 저장 된다.

where절 인덱스 사용

• 기본적으로 인덱스를 사용하려면 인덱스된 컬럼 값 자체를 변환하지 않고 그대로 사용해야 함
• where 절의 조건은 인덱스에 명시된 컬럼의 순서와 관계 없다.

GROUP BY 절 인덱스 사용

• GROUP BY 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 인덱스 사용 가능
• 인덱스를 구성하는 컬럼 중 뒷부분의 컬럼은 GROUP BY 절에 명시되지 않아도 인덱스 사용이 가능
• GROUP BY에 명시된 컬럼 중 하나라도 인덱스가 아니면 인덱스를 전혀 이용하지 못 함

ORDER BY 절 인덱스 사용

• GROUP BY와 기본적으로 유사
• 추가적으로 정렬되는 각 컬럼의 오름차순 및 내림차순 옵션이 인덱스와 같거나 정반대인 경우에만 사용 가능

WHERE + ORDER BY / GROUP BY

• WHERE + ORDER BY / GROUP BY가 모두 같은 인덱스를 사용 : 가장 속도가 빠르다.
• WHERE 절에만 있는 인덱스를 이용 : WHERE 인덱스를 통해 데이터 검색 후 별도 정렬 처리 과정 수행(filesort)
• ORDER BY 절에만 있는 인덱스를 이용 : ORDER BY 절 순서의 인덱스대로 레코드를 읽으면서 WHERE 조건에 맞는지를 검색
  • 아주 많은 레코드를 조회해서 정렬해야 할 때 이런 형태로 튜닝하기도 함

GROUP BY + ORDER BY

• GROUP BY와 ORDER BY가 같이 사용된 쿼리에서는 둘 중 하나라도 인덱스를 이용할 수 없을 때는 둘 다 인덱스를 사용할 수 없음

WHERE, ORDER BY, GROUP BY 인덱스 사용 흐름

LIMIT

limit는 필요한 레코드 건수만 준비되면 즉시 쿼리를 종료

1. select * from employees limit 0, 10;
2. select first_name from employees group by first_name limit 0, 10;
3. select distinct first_name from employees limit 0, 10;
4. select * from employees 
where emp_no between 10001 and 11000 order by first_name limit 0, 10

1. 테이블 풀 스캔 수행하면서 10개를 읽어오는 순간 쿼리 종료
   • 정렬이나 그루핑 또는 DISTINCT가 없는 쿼리에서 limit는 상당히 빨리 끝날 수 있다.
2. group by가 있기 때문에 group by 처리가 완료된 후 limit 처리가 가능
   • limit이 group by와 함께 사용되는 경우에는 limit 절이 있더라도 작업 내용을 크게 줄여주지 못 함
3. 정렬이 필요 없는 DISTINCT는 테이블 풀 스캔을 통해 중복 제거 작업을 수행하다 10개를 읽는 순간 쿼리 종료
   • DISTINCT + LIMIT는 중복 제거 작업 범위를 줄여주기 때문에 작업량을 줄일 수 있다.
4. where 조건에 맞는 레코드를 모두 읽은 뒤, first_name으로 정렬 수행. 정렬을 수행하면서 10건이 완성되는 순간 쿼리 종료
   • where절로 읽어온 레코드가 결국 정렬되어야 하기 때문에 작업량을 크게 줄여주지 못 함

limit n, m에서 n값이 커지면 성능 상 문제가 발생할 수 있다.

• n-1번째 레코드를 읽은 후 버린 다음, n번부터 m개의 데이터를 가져오는 것이므로
• 따라서 첫 번째 페이지 조회 이후에 페이지에서 레코드를 읽는 경우에는 where 절로 읽어야 할 위치를 먼저 filtering하는 것이 좋다.
• select * from salaries where salary>=38864 AND NOT (salary=38864 AND emp_no<=274049) ORDER BY salary LIMIT 0, 10;

COUNT

• InnoDB 스토리지 엔진에서는 where 조건이 없는 count(*) 쿼리라고 해도 직접 데이터나 인덱스를 읽어야 함
  • 따라서 큰 테이블에서 count(*) 함수를 사용할 때에는 조심
• mySQL 8.0부터는 count(*) 쿼리의 ORDER BY는 무시
• count() 함수에 컬럼명이나 표현식이 인자로 사용되는 경우 해당 컬럼이나 표현식의 결과가 NULL이 아닌 레코드 건수만 반환

JOIN

• 두 테이블 조인 시 인덱스가 존재하는 테이블을 주로 드리븐 테이블로 선택, 둘 다 인덱스가 있거나 없는 경우는 통계 정보를 기반으로 레코드 건 수가 적은 테이블을 드라이빙 테이블로 선택.
  • 인덱스를 이용한 쿼리는 크게 “인덱스 탐색”, “인덱스 스캔” 과정으로 나뉘어진다.
  • “인덱스 탐색”이 대부분의 시간을 차지
  • 드라이빙 테이블은 “인덱스 탐색”을 한 번 수행한 뒤 “인덱스 스캔”을 수행
  • 드리븐 테이블은 드라이빙 테이블에서 읽은 레코드 수 만큼 “인덱스 탐색”과 “인덱스 스캔”을 수행
  • 따라서 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있도록 실행 계획 수립
• 조인 시 두 테이블의 조인 키 컬럼의 데이터 타입이 맞지 않는 경우 인덱스를 제대로 사용하지 못 한다.
• OUTER JOIN(LEFT, RIGHT JOIN)을 수행하는 경우, 아우터로 조인되는 테이블을 드라이빙 테이블로 선택하지 못 한다.
  • FROM 절의 테이블이 무조건 드라이빙 테이블로 선택되 테이블 풀 스캔이 발생하므로, 옵티마이저가 성능 최적화를 하지 못 할 수 있다.
  • 테이블의 데이터가 일관되지 못 한 경우가 아니면 굳이 아우터 조인을 사용할 필요가 없다.
• outer table에 대한 조건을 where절에 함께 명시 X
  • 해당 조인은 결국 inner join과 동일

  SELECT *
  FROM employees e
  	LEFT JOIN dept_manager mgr
  	ON mgr.emp_no = e.emp_no
  WHERE mgr.dept_no='d001';
  

   

• 원래 의도가 mgr.dept_no=’d001’인 employees에만 manager 컬럼 값을 채우고 그 외에는 null을 넣는 것이었다면 다음과 같이 ON 절에 조건을 넣어서 쿼리를 수행해야 한다.

SELECT *
FROM employees e
	LEFT JOIN dept_manager mgr
	ON mgr.emp_no = e.emp_no and mgr.dept_no='d001';

 

LATERAL JOIN

• 조ROM 절에 정의된 테이블의 컬럼을 참조할 수 있는 조인

SELECT *
FROM employees e
  LEFT JOIN LATERAL (
	  SELECT *
	  FROM salaries s
	  WHERE s.emp_no=e.emp_no
	  ORDER BY s.from_date DESC LIMIT 2) s2
	 ON s2.emp_no=e.emp_no
  )
 WHERE e.first_name = "Matt";

Window function

• 결과 집합의 형태를 바꾸지 않으면서 다른 레코드의 컬럼값을 참조하기 위해 Window function을 사용
  • 일반적인 SQL 문장에서 하나의 레코드를 연산할 때 다른 레코드의 값을 참조할 수 없다.
  • GROUP BY와 같은 집계 함수를 사용하는 경우 다른 레코드의 값을 참조 가능
  • GROUP BY의 경우 결과 집합의 형태가 바뀜
• 윈도우 함수가 있는 경우 실행 순서
  1. WHERE, FROM, GROUP BY, HAVING절 수행
  2. WINDOW 함수 처리
  3. SELECT, ORDER BY, LIMIT 수행
  • window 함수는 limit 전에 수행되기 때문에 집계 시 limit 전의 데이터를 집계
• 윈도우 함수의 기본 사용법
  • AGGREAGTE_FUNC() OVER(<parittion> <order>) AS window_func_column

  SELECT e.*,
  	RANK() OVER(ORDER BY e.hire_date) AS hire_date_rank
  FROM employees e;
  
  SELECT de.dept_no, e.emp_no, e.first_name, e.hire_date
  	RANK() OVER(PARTITION BY de.dept_no ORDER BY e.hire_date) AS hire_date_rank
  FROM employees e
  	INNER JOIN dept_emp de 
  	ON de.emp_no = e.emp_no
  ORDER BY de.dept_no, e.hire_date;
  

윈도우 함수의 각 파티션 내에서도 연산 대상 레코드별로 연산을 수행할 소그룹을 지정할 수 있다.

• 해당 소그룹을 프레임이라고 한다.
• 프레임을 직접 지정할 수도 있다.
  • AGGREGATE_FUNC() OVER(<partition> <order> <frame>) AS window_func_column
  • frame : {ROWS | RANGE} {frame_start | frame_between}
  • frame_between : BETWEEN frame_start AND frame_END
  • frame_start, frame_end : {CURRENT_ ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING}
• 프레임을 만드는 기준은 ROWS 또는 RANGE 중 하나를 선택
  • ROWS : 레코드의 위치를 기준으로 프레임 생성
  • RANGE : ORDER BY 절에 명시된 컬럼을 기준으로 값의 범위로 프레임 생성
• 프레임의 시작과 끝을 의미하는 키워드의 의미는 다음과 같다.
  • CURENT ROW : 현재 레코드
  • UNBOUNDED PRECEDING : 파티션의 첫 번째 레코드
  • UNBOUNDED FOLLOWING : 파티션의 마지막 레코드
  • expr PRECEDING : 현재 레코드로부터 n번째 이전 레코드
  • expr FOLLOWING : 현재 레코드로부터 n번째 이후 레코드
    • 프레임이 ROWS인 경우 expr에는 레코드의 위치를 명시
      • 10 PRECEDING : 현재 레코드로부터 10건 이전부터
    • 프레임이 RANGE인 경우 expr에는 컬럼과 비교할 값을 설정
      • INTERVAL 5 DAY PRECEDING : 현재 레코드의 ORDER BY 컬럼값보다 5일 이전 레코드부터

잠금을 사용하는 SELECT

• FOR SHARE : SELECT로 읽은 레코드에 대해 읽기 잠금 설정
• FOR UPDATE : SELECT로 읽은 레코드에 대해 쓰기 잠금 설정
• MySQL에서는 이렇게 잠금을 걸어도 일반 SELECT 쿼리는 아무런 대기 없이 그대로 실행된다.
• 잠금 테이블 선택
  • 여러 테이블을 JOIN해서 SELECT를 할 때 잠금을 거는 경우 JOIN에서 사용된 모든 테이블에 잠금이 걸리게 된다.

  SELECT *
  FROM employees e
  	INNER JOIN dept_emp de ON de.emp_no = e.emp_no
  	INNER JOIN departments d ON d.dept_no = de.dept_no
  FOR UPDATE;
  

  • mySQL 8.0부터는 OF 테이블 명령어를 사용해 특정 테이블에 대해서만 잠금을 걸 수 있도록 설정할 수 있다.

  SELECT *
  FROM employees e
  	INNER JOIN dept_emp de ON de.emp_no = e.emp_no
  	INNER JOIN departments d ON d.dept_no = de.dept_no
  FOR UPDATE OF e;
  

• NOWAIT : SELECT 쿼리가 해당 레코드에 대해 바로 잠금을 획득하지 못 하는 경우 쿼리를 즉시 종료
  • 일반 SELECT 문은 기본적으로 잠금을 획득하지 않으므로 소용 X
  • FOR UPDATE, FOR SHARE를 사용한 경우에 유의미

  SELECT *
  FROM employees
  WHERE emp_no=10001
  FOR UPDATE NOWAIT;
  

• SKIP LOCKED : 레코드가 잠겨있다면 에러를 반환하지 않고 잠긴 레코드는 무시하고 잠금이 걸리지 않은 레코드만 가져온다.
  • 이러한 SKIP LOCKED 기능은 특정 상황에 매우 용이
  • 사용자가 쿠폰을 요청하면 쿠폰 테이블에서 다른 사용자에게 할당되지 않은 쿠폰ID를 찾아 해당 쿠폰을 사용자에게 발급

BEGIN;
SELECT *
	FROM coupon
	WHERE owner_user_id IS NULL
	ORDER BY coupon_id ASC
	LIMIT 1
	FOR UPDATE **SKIP LOCKED**;

.. 기타 연산 수행 ..

UPDATE coupon SET owner_user_id=? WHERE coupon_id=?;
COMMIT;

 

• SKIP LOCKED가 없다면 다른 사용자가 쿠폰을 발급받을 때까지 다른 사용자들은 모두 대기
• SKIP LOCKED이 있다면 다른 사용자가 발급받고 있는 쿠폰을 제외한 다른 쿠폰 중 하나를 바로 발급할 수 있다.