1.2 SQL 공유 및 재사용
SQL 내부 최적화 과정의 복잡성을 알고나면,
동시성이 높은 온라인 트랜잭션 처리 시스템에서 바인드 변수가 왜 중요한지 알게 될 것 !
바인드 변수란(Bind Varible)
- SQL 실행 시 값(데이터)을 변수처럼 미리 지정해두고 나중에 넣는 자리 표시자
- 바인드 변수를 사용하면:
- 파싱 비용 절감 - 쿼리 재사용, SQL 캐싱
- 보안 - SQL Injection 방어
- 성능 - 실행 계획 고정, DB 부하 감소
바인드 변수의 중요성과 이름없는 SQL
- 사용자 정의 함수, 프로시저, 트리거, 패키지는 생성 시 이름을 가짐
- 컴파일 완료된 상태로 딕셔너리 저장, 영구 보관
- ✨ 데이터 딕셔녀리: DB 내부의 메타데이터 저장소(테이블, 인덱스, 제약조건, 권한정보, 기타 등)
(즉, DB가 자기자신을 설명해둔 DB) - 실행 시 라이브러리 캐시에 적재 → 여러 사용자 공유 및 재사용
- ❗그러나 SQL은 이름이 별도로 존재❌, SQL 내용 자체가 이름 역할
- 따라서 SQL이 변경될 때마다 최적화 과정을 거쳐 프로시저 생성하고
라이브러리 캐시에 적재 - 그러나 공간 부족시 삭제됨 - 삭제된 이후 동일한 SQL이 실행되면 다시 최적화 과정을 거침
- 즉, SQL 자체가 이름이고 변경될 때마다 다른 객체가 생성됨
- 따라서 SQL이 변경될 때마다 최적화 과정을 거쳐 프로시저 생성하고
- 이런 이유로 SQL을 영구 저장❌ - 공간 문제, 성능 문제
공유 가능 SQL과 바인드 변수
- 쇼핑몰에서 이벤트로 인해 동시 접속 사용자(로그인)가 몰린다면?
SELECT * FROM USER WHERE LOGIN_ID = '짱구'
SELECT * FROM USER WHERE LOGIN_ID = '유리'
SELECT * FROM USER WHERE LOGIN_ID = '철수'
SELECT * FROM USER WHERE LOGIN_ID = '맹구'
SELECT * FROM USER WHERE LOGIN_ID = '훈이'- SQL마다 최적화 과정 → 프로시저 생성 → 라이브러리 적재됨 - 비효율!
LOGIN_ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 효율적- 이렇게 파라미터 Driven 방식으로 SQL을 작성하는 방법이 바인드 변수
- ✨파라미터 Driven: SQL 문장 고정, 파라미터만 바꿔 실행 (SQL 재사용, 파싱 최소화 , 성능 안정화)
- 바인드 변수 사용 시 하드 파싱은 최소 1회, 캐싱된 SQL을 공유하며 재사용 가능
이 대목에서 생각해볼거리
// 로그인 프로그램
public void login(String login_id) throws Exception{
String SQLStmt = **"SELECT * FROM CUSTOMER WHERE LOGIN_ID = '"+login_id+"';**
...
}이 쇼핑몰에서 어느 날 12시 정각부터 딱 30분간 대대적인 할인 이벤트를 하기로 했다.
500만 명 중 20%에 해당하는 100만 고객이 이벤트 당일 12시를 전후해
동시에 시스템 접속을 시도할 경우 어떤일이 발생할까?
DBMS에 발생하는 부하는 대가 과도한 I/O가 원인인데,
이날은 I/O가 거의 발생하지 않음에도 불구하고 CPU사용률은 급격히 올라가고, 라이브러리 캐시에 발생하는 여러 종류의 경합 때문에
로그인이 제대로 처리되지 않을 것이다.
각 고객에 대해서 동시다발적으로 발생하는 SQL 하드파싱때문이다.
I/O가 거의 발생하지 않는다고?
- 로그인 프로그램이 저런 쿼리를 사용하는데 I/O가 거의 발생하지 않는다고 … .?
그리고 SQL 하드파싱은 왜 CPU를 많이 소모할까?
그리고 라이브러리 캐시 경합이란 무엇일까? 짚고 넘어가보자
1. 로그인 SQL이 I/O를 발생시키지 않는 이유
- GPT한테 물어봐도 잘 이해가 안된다 씌앙 .. . . ..
✨ 우선 내가 이해한대로 정리하기
- 로그인을 처리하는 쿼리 (
SELECT * FROM CUSTOMER WHERE LOGIN_ID = ‘짱구123’)는 데이터베이스에서 데이터를 읽어오는 작업이므로 실행단계에서 I/O가 발생하는 쿼리 - 그러나 위와 같이 동시 접속자가 많은 상황에서 로그인 작업은
각각 다른LOGIN_ID로 인해 SQL하드파싱 → 라이브러리 캐시 적재 과정을 거치면서 CPU 사용률이 높아지고, 캐시 락 경합으로 인해 실행단계의 I/O가 많이 발생하지 않음 - 즉, 실행 단계 이전의 문제로 인해 I/O가 거의 발생하지 않는다는 의미
2. SQL 하드 파싱이 CPU를 많이 소모하는 이유
LOGIN_ID가 달라질때마다 매번 새로운 SQL 문장으로 인식하고 하드 파싱이 발생- 하드파싱은
- SQL 구문분석
- 객체확인
- 권한검사
- 실행 계획 수립
- 라이브러리 캐시 적재 작업을 수행
- 따라서 하드파싱은 CPU를 많이 소모하게 됨
3. 라이브러리 캐시 경합이란?
- 라이브러리 캐시는 SQL 실행 계획 등을 저장해놓은 공유 메모리 영역
- 수많은 클라이언트가 동시에 다 다른 SQL을 요청하면 하드파싱 발생하고,
생성된 실행 계획을 캐시에 적재할 때 접근하려는 스레드 간 락 경쟁 발생 - 동시 사용자 수 증가 → 하드파싱 증가 → 라이브러리 캐시 락 증가
✨ 락의 종류(Oracle)
- Library Cache Lock :
파싱 중인 SQL이 캐시에 등록될 때 다른 세션이 해당 객체를 못건드리게 막음 - Library Cache Pin :
SQL실행 중 해당 실행계획이 변경되지 않도록 고정 - Latch(Spin Lock) :
메모리 구조 접근 제어용 빠른 락(짧고 빈번하게 발생)
그래서 바인드 변수의 중요성을 깨달았나?
책의 저자는 SQL 재사용을 설명하기에 앞서 아래와 같이 서술했다.
" SQL 내부 최적화 과정의 복잡성을 알고나면,
동시성이 높은 온라인 트랜잭션 처리 시스템에서 바인드 변수가 왜 중요한지 알게 될 것"
그래서 1장을 읽고 바인드 변수의 중요성을 깨달았냐구?
이론적으로는 깨달았다.(최적화 과정 개빡셈, 그 와중에 비용과 성능 문제, 캐싱 중요)
그런데 바인드 변수를 사용했을 경우와 사용하지 않았을 경우를
개발적인 측면에서 직접 경험해보지 않았기에 큰 체감을 느끼긴 어려운 점이 있는 것 같다.
개발짱이 되서 얼른 성능 최적화를 고민하고 실행해보고 싶다 ..
"개발자는 해결사이자 발견자이다✨" - Michael C. Feathers