커서
테이블에서 여러 개의 행을 쿼리한 후, 쿼리 결과 집합을 한 행씩 처리하기 위한 개체
- 주로 SQL 쿼리 결과 집합을 반복적으로 탐색하고 처리하는 데 사용됨
- 스토어드 프로시저, 스토어드 함수, 트리거에서 커서를 사용할 수 있음
커서 분류
속성에 따라 두 그룹으로 분류해 보았다.
변화에 얼마나 민감한지에 따라 - Sensitive / Insensitive / Asensitive
- Sensitive Cursor
- 일치하는 레코드 정보를 실제 레코드의 포인터만으로 유지하는 형태
- 커서를 이용해 칼럼의 데이터를 변경하거나 삭제하는 것이 가능함
- 별도로 임시 테이블로 레코드를 복사하지 않기 때문에 커서의 오픈이 빠름
- Insensitive Cursor
- 일치하는 레코드를 별도의 임시 테이블로 복사해서 가지고 있는 형태
- SELECT 쿼리에 부합되는 결과를 우선적으로 임시 테이블로 복사해야 하기 때문에 느림
- 이미 임시 테이블로 복사된 데이터를 조회함 ➡️ 칼럼 값을 변경하거나 레코드를 삭제하는 작업이 불가능함
- Asensitive Cursor
- `센서티브 커서`와 `인센서티브 커서`를 혼용해서 사용하는 방식
선언 방식에 따라 - 묵시적 / 명시적
- 묵시적(Implicit Cursor)
- DBMS가 자동으로 생성하는 커서로, SQL 문을 실행할 때 자동으로 생성됨
- 일반적으로 개발자가 직접 선언하거나 제어하지 않고, 데이터베이스 시스템 내부에서 사용됨
- 명시적(Explicit Cursor)
- 개발자가 직접 선언하고 제어하는 커서
- 복잡한 데이터 처리 작업이 필요한 경우에 사용함
커서의 특징
MySQL 커서는 다음과 같은 특징을 갖는다.
Asensitive
- 센서티브와 인센서티브 혼용 ➡️ 데이터가 임시 테이블로 복사될 수도 있고(인센서티브), 아닐 수도 있음(센서티브)
- 그런데 만들어진 커서가 센서티브인지 인센서티브인지 확인할 방법이 없음
➡️ 서버가 결과 테이블의 복사본을 만들 수도 있고 만들지 않을 수도 있음
Read Only
- 커서를 이용해서 테이블에 있는 데이터를 업데이트하거나 삭제할 수 없음
Nonscrollable
- 한 방향(전진)으로만 이동할 수 있으며, 행을 건너뛸 수 없음
커서 사용법
커서의 처리 순서
커서 선언
DECLARE cursor_name CURSOR FOR select_statement
- `DECLARE` 키워드를 사용하여 커서를 선언함
- 커서가 가리킬 SELECT 문을 지정해야 함
반복 조건 선언
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done = TRUE;
- `DECLARE CONTINUE HANDLER`: 반복 조건을 준비하는 예약어
- `FOR NOUT FOUND`: 더 이상 행이 없을 때 이어진 문장을 수행함
- 여기서는 `SET done = TRUE`를 했으므로 행이 끝나면 `done` 변수에 `TRUE`를 대입하라는 뜻
커서 열기
OPEN cursor_name;
- `OPEN` 키워드를 이용하여 커서 열어주기
커서에서 데이터 가져오기
FETCH cursor_name INTO var_name ...;
- `FETCH` 키워드를 이용하여 커서에서 한번에 한 행(row)씩 데이터를 가져옴
데이터 처리 (필요한 쿼리 수행)
- 원하는 동작을 수행하는 쿼리문을 작성하면 됨
커서 닫기
CLOSE cursor_name
- 커서를 사용한 후에는 `CLOSE` 키워드를 이용해서 반드시 커서를 닫아줘야 함
➡️ 해당 커서와 관련된 자원이 모두 해제되고, 커서를 재사용할 수 있게 됨
예제 - 게시판 조회수 평균 구하기(with 커서)
예제 참고 사이트
https://chinggin.tistory.com/269
DROP PROCEDURE IF EXISTS cursorProc;
DELIMITER $$
CREATE PROCEDURE cursorProc()
BEGIN
-- 0. 필요한 변수 선언
DECLARE noticeHit INT DEFAULT 0; -- 조회수
DECLARE noticeCnt INT DEFAULT 0; -- 게시글 수
DECLARE totalHit INT DEFAULT 0; -- 조회수 총합
DECLARE done BOOLEAN DEFAULT FALSE; -- 커서 종료 상태
-- 1. 커서 선언
DECLARE noticeCursor CURSOR FOR
SELECT hit from Notice;
-- 2. 반복 조건 선언
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done = TRUE;
-- 3. 커서 열기
OPEN noticeCursor;
-- 반복문
cursor_loop: LOOP
-- 반복문 종료 쿼리
IF done THEN
LEAVE cursor_loop;
END IF;
-- 4. 커서에서 데이터 가져오기 (FETCH)
FETCH noticeCursor INTO noticeHit;
-- 5. 데이터 처리 (필요한 로직 수행)
-- 조회 수 계산
SET noticeCnt = noticeCnt + 1;
SET totalHit = totalHit + noticeHit;
END LOOP cursor_loop;
SELECT totalHit;
SELECT noticeHit;
SELECT noticeCnt;
SELECT CONCAT('게시판의 조회수 평균', (totalHit/noticeCnt));
-- 6. 커서 닫기
CLOSE noticeCursor;
END $$
DELIMITER ;
CALL cursorProc();
정리
커서를 사용하는 것은 비디오를 다운로드하는 대신에 스트리밍으로 시청하는 것과 같다.
- https://stackoverflow.com/questions/3861558/what-are-the-benefits-of-using-database-cursor
- 다운로드하면 큰 저장 공간이 필요하고 다운로드가 완료될 때까지 기다려야 함
- 보통 쿼리는 서버로 보내지고 실행되며 결과 집합이 한 번에 네트워크로 전송됨
- 커서는 데이터에 접근하여 요청할 때마다 데이터를 한 행씩 스트리밍 하여 보여줌
➡️ 모두 다운로드될 때까지 기다릴 필요가 없으므로 시간 절약 & 메모리 절약
커서의 장단점
- 장점
- 전체 결과 집합을 한 번에 가져오지 않고 필요한 만큼만 데이터를 가져올 수 있으므로 메모리 부담이 줄어듦
- 커서를 사용하면 결과를 실시간으로 처리할 수 있음
- 단점
- 결과를 한 번에 한 행씩 가져오므로 데이터 전송 및 처리에 대한 오버헤드가 발생할 수 있음
- 결과 집합을 한 번에 가져오는 것보다 메모리 사용량을 줄어들지만 커서 자체가 메모리를 소비함
- 커서를 사용하면 전체 결과 집합의 일관성을 보장하기 어려움
- 잠금 및 차단을 발생함
커서.. 웬만하면.. 사용하지 말자! 😿
커서는 개별 행을 처리가 필요한 복잡한 쿼리를 처리하거나 각 행에 대해 스토어드 프로시저를 실행할 때 유용하기도 하지만, 검색해 보면 많은 개발자들이 커서 사용을 추천하지 않는다. 물론 상황에 따라 다르지만 공통적으로 언급하는 단점이 있다.
- 오버헤드 발생: 한 행씩 처리하므로 데이터 수가 많아질수록 시간이 많이 걸림
- 리소스 낭비: 커서는 열려 있는 동안 서버 리소스를 소비함
- 구현 복잡: 대부분의 경우 커서를 사용하는 것보다 단순 쿼리의 조합으로 작성하는 것이 더 간단함
- 잠금 및 차단: 커서는 잠금 및 차단 문제를 발생시킴
지금까지 커서에 대해 열심히 알아봤지만, 이러한 단점이 있으므로 웬만하면 커서를 사용하기 보다는 단순 쿼리로 해결하는 것이 더 좋을 것 같다고 생각이 들었다.
Ref
https://dev.mysql.com/doc/refman/8.0/en/cursors.html
https://www.prepbytes.com/blog/dbms/cursor-in-dbms/
https://www.youtube.com/watch?v=bMQ_dAoaMzA
https://stackoverflow.com/questions/3861558/what-are-the-benefits-of-using-database-cursor
https://intellipaat.com/blog/cursor-in-sql/#no8
'Backend > Database' 카테고리의 다른 글
[MySQL] MySQL 성능 모니터링 방법 (0) | 2024.04.04 |
---|---|
[MySQL] 데이터베이스 확장 방식 (0) | 2024.03.21 |
[MySQL] Direct I/O (0) | 2024.02.29 |
[MySQL] 문자열 패턴 매칭 연산자 (REGEXP, LIKE) (0) | 2024.02.22 |
[MySQL] 실행 계획 - partitions 칼럼 (0) | 2024.02.15 |