[MySQL] 커서(Cursor)

커서

테이블에서 여러 개의 행을 쿼리한 후, 쿼리 결과 집합을 한 행씩 처리하기 위한 개체

• 주로 SQL 쿼리 결과 집합을 반복적으로 탐색하고 처리하는 데 사용됨
• 스토어드 프로시저, 스토어드 함수, 트리거에서 커서를 사용할 수 있음

https://soliloquiess.github.io/class/2021/11/09/SQL-%EC%8A%A4%ED%86%A0%EC%96%B4%EB%93%9C-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80.html

커서 분류

속성에 따라 두 그룹으로 분류해 보았다.

변화에 얼마나 민감한지에 따라 - Sensitive / Insensitive / Asensitive

• Sensitive Cursor
  • 일치하는 레코드 정보를 실제 레코드의 포인터만으로 유지하는 형태
  • 커서를 이용해 칼럼의 데이터를 변경하거나 삭제하는 것이 가능함
  • 별도로 임시 테이블로 레코드를 복사하지 않기 때문에 커서의 오픈이 빠름
• Insensitive Cursor
  • 일치하는 레코드를 별도의 임시 테이블로 복사해서 가지고 있는 형태
  • SELECT 쿼리에 부합되는 결과를 우선적으로 임시 테이블로 복사해야 하기 때문에 느림
  • 이미 임시 테이블로 복사된 데이터를 조회함 ➡️ 칼럼 값을 변경하거나 레코드를 삭제하는 작업이 불가능함
• Asensitive Cursor 
  • `센서티브 커서`와 `인센서티브 커서`를 혼용해서 사용하는 방식

선언 방식에 따라 - 묵시적 / 명시적

• 묵시적(Implicit Cursor)
  • DBMS가 자동으로 생성하는 커서로, SQL 문을 실행할 때 자동으로 생성됨
  • 일반적으로 개발자가 직접 선언하거나 제어하지 않고, 데이터베이스 시스템 내부에서 사용됨

• 명시적(Explicit Cursor)
  • 개발자가 직접 선언하고 제어하는 커서
  • 복잡한 데이터 처리 작업이 필요한 경우에 사용함

커서의 특징

MySQL 커서는 다음과 같은 특징을 갖는다.

Asensitive

• 센서티브와 인센서티브 혼용 ➡️ 데이터가 임시 테이블로 복사될 수도 있고(인센서티브), 아닐 수도 있음(센서티브)
• 그런데 만들어진 커서가 센서티브인지 인센서티브인지 확인할 방법이 없음
  ➡️ 서버가 결과 테이블의 복사본을 만들 수도 있고 만들지 않을 수도 있음

Read Only

• 커서를 이용해서 테이블에 있는 데이터를 업데이트하거나 삭제할 수 없음

Nonscrollable

• 한 방향(전진)으로만 이동할 수 있으며, 행을 건너뛸 수 없음

 

커서 사용법

커서의 처리 순서

https://velog.io/@onenewarm/%EC%8A%A4%ED%86%A0%EC%96%B4%EB%93%9C

 

커서 선언

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

[MySQL] 커서

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://ko.wikipedia.org/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%EC%BB%A4%EC%84%9C

https://www.prepbytes.com/blog/dbms/cursor-in-dbms/

https://blog.duveen.me/20

https://www.youtube.com/watch?v=bMQ_dAoaMzA

https://stackoverflow.com/questions/3861558/what-are-the-benefits-of-using-database-cursor

http://www.databaser.net/moniwiki/wiki.php/%EC%BB%A4%EC%84%9C%EB%A5%BC%EC%96%B8%EC%A0%9C%EC%82%AC%EC%9A%A9%ED%95%B4%EC%95%BC%ED%95%A0%EA%B9%8C

https://intellipaat.com/blog/cursor-in-sql/#no8