dongzzirit 님의 블로그

1. 테스트 배경

웹 애플리케이션에서 데이터베이스 접근은 성능에 매우 큰 영향을 미칩니다. 특히 대규모 트래픽 상황에서 동일한 데이터 요청이 반복된다면, 데이터베이스 부하가 급격히 증가하여 응답 속도가 느려지고 시스템 안정성이 저하될 수 있습니다.

이를 해결하기 위해 흔히 사용하는 방법 중 하나가 데이터베이스 인덱싱입니다. 하지만 인덱싱만으로 해결되지 않는 부하 문제를 더 효율적으로 해결하기 위해 캐싱(Caching)을 도입할 수 있습니다.

이번 테스트에서는 데이터베이스에 적절한 인덱싱을 설정한 상태에서 캐싱을 추가로 적용했을 때 성능에 어떤 차이가 발생하는지 궁금하여, Spring Boot와 JPA를 사용하여 @Cacheable을 활용한 성능 효과를 Artillery로 측정하고 분석해 보았습니다.

2. 캐싱 적용 전후 비교

테스트는 다음 두 가지 시나리오로 나누어 진행했습니다

• 캐싱 미적용: 데이터베이스에서 모든 요청을 처리하며, 데이터 캐싱을 사용하지 않는 상태.
• 캐싱 적용: Spring의 @Cacheable을 사용하여 동일한 요청에 대해 캐싱된 데이터를 반환.

@Cacheable 사용 코드

@Cacheable(value = "customers", key = "#condition.gender + '-' + #pageable.pageNumber")
public Page<CustomerResponse> search(Pageable pageable, CustomerSearchCondition condition) {
    pageable = pageable == null ? PageRequest.of(0, 5) : pageable;
    return customerSearchRepository.search(condition, pageable);
}

3. 캐싱 적용 전후 성능 비교

항목캐싱 적용 전캐싱 적용 후개선 효과

4. 결론

캐싱의 효과

• 요청 처리 속도 향상: 캐싱을 통해 데이터베이스 요청 횟수가 감소하면서 전체 지연 시간이 크게 단축되었습니다. 캐싱된 데이터를 반환하므로 중앙값 기준 지연 시간이 3963.5ms에서 1ms로 개선되었습니다.
• 처리량 증가: 평균 RPS(초당 요청 처리 수)가 21.17에서 46.42로 약 2배 증가하여 더 많은 요청을 처리할 수 있게 되었습니다.
• 안정성 향상: 캐싱 미적용 상태에서 데이터베이스 과부하로 인해 발생했던 ETIMEDOUT 오류 413건이 캐싱 적용 후 완전히 사라졌습니다.

시스템 안정성

• 캐싱 적용은 데이터베이스 부하를 줄여 응답 속도와 시스템 안정성을 동시에 개선할 수 있음을 확인했습니다. 특히, 95th 퍼센타일 지연 시간이 8904ms에서 78ms로 크게 단축되어 사용자 경험도 향상되었습니다.

5. 캐싱 적용 시 유의사항

캐싱은 모든 경우에 적합하지 않으며, 적용 시 주의해야 할 점이 있습니다:

• 정적 데이터에 적합: 캐싱은 변경되지 않거나 자주 변경되지 않는 데이터(예: 코드 테이블, 인기 상품 등)에 적합합니다.
• 메모리 관리 필요: JVM 메모리를 사용하는 캐싱(Simple Cache)은 대규모 트래픽 환경에서는 메모리 부족 문제를 유발할 수 있습니다. Redis와 같은 분산 캐싱 솔루션을 고려해야 합니다.
• TTL(Time To Live) 설정: 캐싱된 데이터의 유효 기간을 적절히 설정하여 오래된 데이터 반환 문제를 방지해야 합니다.

6. 최종 생각

캐싱은 데이터베이스 부하를 줄이고 애플리케이션의 응답 속도를 높이는 데 효과적인 도구입니다. 이번 테스트를 통해 캐싱이 성능 개선에 미치는 영향을 수치로 확인할 수 있었습니다.

• 적용 사례: 읽기 요청이 많은 API, 동일한 조건으로 반복 호출되는 요청, 외부 API 호출 결과 캐싱 등.
• 향후 확장 가능성: Simple Cache를 Redis 등 분산 캐시로 확장하여 대규모 트래픽도 안정적으로 처리할 수 있습니다.

데이터베이스 성능 최적화에서 인덱싱은 빠른 조회 속도를 보장하는 중요한 도구입니다. 하지만 인덱스를 많이 생성하면 쓰기 작업(INSERT, UPDATE, DELETE)의 성능이 저하된다고 알려져 있습니다. 이번 글에서는 실제 실험 데이터를 기반으로 이 가정을 테스트한 결과를 공유합니다.

1. 테스트 목적

멀티 컬럼 인덱스와 단일 컬럼 인덱스를 다수 적용했을 때, 쓰기 작업의 성능이 얼마나 저하되는지 확인하는 것이 목적이었습니다. 특히, 데이터베이스에 110만 건 이상의 데이터를 넣고 쓰기 작업을 수행하며, 인덱스가 없는 상태와 있는 상태를 비교해 보았습니다.

2. 테스트 환경

• 데이터베이스: MySQL 8.0
• 테이블: Customer 테이블
• 데이터 크기: 약 110만 건
• 인덱스 설정:
  • 테스트 A: 기본 키 외에 추가 인덱스 없음
  • 테스트 B: 복합 인덱스 2개 적용

CREATE INDEX idx_customer_name_email ON customer(name, email);
CREATE INDEX idx_customer_phone_grade ON customer(phone, grade);

• 테스트 도구: Artillery를 사용한 부하 테스트
• 테스트 작업:
  • INSERT: 고객 데이터 삽입
  • UPDATE: 고객 데이터 수정
  • DELETE: 고객 데이터 삭제

3. 테스트 시나리오 및 데이터

요청 예시

config:
  target: 'http://localhost:8080'
  phases:
    - duration: 10
      arrivalRate: 10
    - duration: 20
      arrivalRate: 20
    - duration: 30
      arrivalRate: 30
    - duration: 20
      arrivalRate: 10

scenarios:
  - flow:
      # 1. 로그인 후 토큰 획득
      - post:
          url: "/api/v1/auth/signintest"
          json:
            email: "test2@gmail.com"
            password: "123456789"
          capture:
            - json: "$.data.atk"
              as: "authToken"

      # 2. 고객 정보 수정 (쓰기 작업)
      - patch:
          url: "/api/v1/customers/1000"
          headers:
            Authorization: "Bearer {{ authToken }}"
          json:
            name: "Updated Name {{ $randomInt }}"
            phone: "010-{{ $randomInt }}-{{ $randomInt }}"

      # 3. 고객 삭제 (쓰기 작업)
      - delete:
          url: "/api/v1/customers/{{ $randomInt }}"
          headers:
            Authorization: "Bearer {{ authToken }}"

      # 4. 고객 메모 작성/수정 (쓰기 작업)
      - put:
          url: "/api/v1/customers/100/memo"
          headers:
            Authorization: "Bearer {{ authToken }}"
          json:
            content: "This is a test memo with random value {{ $randomInt }}."
            title: "Test Memo Title {{ $randomInt }}"

4. 테스트 결과

1. 지연 시간(Latency) 비교

지표 첫 번째 결과 (인덱스 없음) 두 번째 결과 (인덱스 있음)

분석

• 최소 지연 시간은 두 결과 모두 동일합니다 (0 ms).
• 최대 지연 시간에서 큰 차이가 나타납니다:
  • 인덱스가 있는 경우 최대 지연 시간은 391 ms.
  • 인덱스가 없는 경우 최대 지연 시간은 109 ms.
• 중앙값에서 인덱스가 있는 경우 2 ms, 없는 경우 1 ms로 차이가 있습니다.
• 95% 및 99% 지연 시간은 거의 동일하게 나타났습니다.

5. 결론

멀티 컬럼 인덱스를 적용하면 최대 지연 시간이 늘어났다는 것을 알았지만 지금 테스트가 뭔가 이상한 거 같아서 다시 한번 검토가 필요한 거 같습니다.

1. 커버링 인덱스란?

커버링 인덱스(Covering Index)는 인덱스 자체에 쿼리에서 필요한 모든 컬럼이 포함되어 있어 테이블 데이터를 조회하지 않고 인덱스만으로 결과를 반환할 수 있는 인덱스입니다.

커버링 인덱스의 특징

• WHERE 조건과 SELECT 컬럼 모두를 인덱스에 포함시킵니다.
• 테이블 접근이 제거되므로 랜덤 I/O 비용이 줄어들어 성능이 크게 향상됩니다.

일반 인덱스와의 차이

• 일반 인덱스: WHERE 조건을 최적화하지만 SELECT 컬럼이 인덱스에 없으면 테이블 데이터를 추가로 조회해야 합니다.
• 커버링 인덱스: 모든 조건과 결과 컬럼이 인덱스에 포함되므로 테이블 접근 없이 바로 결과를 반환합니다.

2. 커버링 인덱스를 적용한 성능 비교

테스트 환경

• 데이터 개수: 10만 개
• 조회 조건: WHERE grade = 'SILVER' AND gender = 'Male'
• 쿼리: SELECT * FROM customer WHERE grade = 'SILVER' AND gender = 'Male' LIMIT 0, 20000;

3. 단계별 성능 비교

단계인덱스 유형스캔된 행 수실제 반환된 행 수비용 (cost) 실행 시간특징

4. 단계별 분석

1. 인덱스 적용 전 (테이블 전체 스캔)

• 인덱스 없음: 테이블 전체를 스캔하며 조건에 맞는 데이터를 필터링합니다.
• 스캔된 행 수: 99,256개
• 비용: 10,238
• 실행 시간: 65.5ms
• 결론: 성능이 가장 나쁘며, 테이블 전체 스캔으로 인해 I/O 비용이 큽니다.

2. 단일 컬럼 인덱스 (idx_grade)

• 인덱스 사용: grade 컬럼만 최적화됨. gender 조건은 추가 필터링이 발생합니다.
• 스캔된 행 수: 49,628개
• 비용: 3,420
• 실행 시간: 44.7ms
• 결론: 스캔된 행 수가 절반으로 줄었지만, gender 조건이 추가로 처리되므로 완벽한 최적화는 되지 않았습니다.

3. 멀티컬럼 인덱스 (idx_grade_gender)

• 인덱스 사용: grade와 gender 조건을 동시에 최적화합니다.
• 스캔된 행 수: 24,436개
• 비용: 3,382
• 실행 시간: 36.6ms
• 결론: 두 조건을 인덱스에서 처리하면서 추가 필터링 비용이 제거되어 성능이 크게 개선되었습니다.

4. 커버링 인덱스 (idx_covering_all_columns)

• 인덱스 사용: WHERE 조건 최적화 + 모든 컬럼이 인덱스에 포함되어 테이블 접근이 제거되었습니다.
• 스캔된 행 수: 24,392개
• 비용: 3,377
• 실행 시간: 34.9ms
• 특징:
  • Using index가 실행 계획에 나타남.
  • 인덱스만으로 모든 데이터를 반환 → 랜덤 I/O 최소화.
• 결론: 가장 빠른 성능을 보여주며, 테이블 접근 비용이 완전히 제거되었습니다.

5. 결론 및 추천

성능 개선 순서

1. 인덱스 적용 전 → 성능이 가장 낮음 (테이블 전체 스캔).
2. 단일 컬럼 인덱스 → WHERE 조건 일부만 최적화되므로 추가 필터링 비용 발생.
3. 멀티컬럼 인덱스 → 두 조건을 동시에 최적화.
4. 커버링 인덱스 → WHERE + SELECT를 인덱스만으로 처리해 성능이 가장 좋음.

최적화 추천

모든 컬럼이 필요한 경우: 커버링 인덱스가 가장 효율적입니다.

CREATE INDEX idx_covering_all_columns ON customer (grade, gender, birth, created_date, is_deleted, visit_cnt, employee_id, id, address(50), email(50), memo(50), name(50), phone(20));

INSERT/UPDATE 성능 저하를 감안해야 하므로 읽기 중심 테이블에 적용하는 것이 좋습니다.

지난 글 인덱스 적용 전/후 성능 비교에서 단일 인덱스(idx_grade)를 적용해 쿼리의 성능을 일부 개선했지만, 기대했던 만큼 성능이 크게 향상되지 않았습니다.

단일 인덱스를 사용하면 한 가지 조건만 최적화되기 때문에 다른 조건(gender = 'Male')은 여전히 추가 필터링 단계를 거쳐야 했습니다. 결과적으로 스캔된 데이터는 절반으로 줄었지만, 실행 시간과 비용은 여전히 비효율적이었습니다.

이번에는 복합 인덱스를 적용해 두 조건(grade와 gender)을 동시에 처리하고, 어떤 성능 개선이 이루어지는지 확인해 보겠습니다.

1. 복합 인덱스 생성

기존 쿼리

SELECT * FROM customer WHERE grade = 'Silver' AND gender = 'Male';

복합 인덱스 생성:

복합 인덱스를 사용하면 두 조건을 동시에 인덱스 단계에서 처리할 수 있습니다.

CREATE INDEX idx_grade_gender ON customer (grade, gender);

2. 실행 계획 비교

단일 인덱스 사용 (기존)

Filter: (customer.gender = 'Male') 
  -> Index lookup on customer using idx_grade (grade='Silver')

• 스캔된 행 수: 49,628개.
• 실제 반환된 행 수: 12,562개.
• 추가 필터링: gender 조건을 따로 처리해야 함.
• 비용(cost): 약 3,420.

복합 인덱스 사용 (새로운 인덱스 적용 후)

Filter: (customer.gender = 'Male') 
  -> Index lookup on customer using idx_grade (grade='Silver')

• 스캔된 행 수: 24,436개 → 절반으로 감소.
• 실제 반환된 행 수: 12,562개.
• 추가 필터링 없음: grade와 gender 조건이 인덱스에서 동시에 처리됨.
• 비용(cost): 약 3,382 → 더 낮아짐.

3. 성능 비교

항목단일 인덱스 (idx_grade) 복합 인덱스 (idx_grade_gender)

4. 개선된 점

1. 추가 필터링 단계 제거:
   복합 인덱스(idx_grade_gender)를 사용하면서 grade와 gender 조건이 인덱스 단계에서 처리되었습니다.
   • Using where 대신 바로 결과 반환이 가능해졌습니다.
2. 스캔된 행 수 감소:
   기존에 idx_grade로 약 49,628개의 행을 읽었지만, 복합 인덱스는 약 24,436개만 스캔하게 되었습니다.
3. 실행 시간 단축:
   쿼리 실행 시간이 평균 0.034 ~ 0.039초로 대폭 단축되었습니다.
4. 비용 감소:
   MySQL의 쿼리 비용(cost)도 기존보다 낮아졌습니다.

5. 결론

복합 인덱스(idx_grade_gender)를 생성하면서 WHERE 절의 두 조건을 동시에 최적화할 수 있었습니다.

• 단일 인덱스에서는 추가 필터링이 발생했습니다.
• 복합 인덱스는 인덱스 단계에서 모든 조건을 처리하므로 I/O 비용이 감소하고 실행 시간이 크게 단축되었습니다.

6. 다음 예고: 커버링 인덱스로 SELECT 최적화

복합 인덱스는 WHERE 조건 최적화에 강력하지만, SELECT *처럼 모든 컬럼을 조회하는 경우에는 랜덤 I/O 비용이 여전히 발생합니다.

다음 글에서는 커버링 인덱스를 활용해 테이블 조회를 완전히 없애고, SELECT 성능을 극대화하는 방법을 다루겠습니다.

데이터베이스에서 성능 최적화를 위해 인덱스를 적용하는 것은 일반적인 방법입니다. 하지만 때로는 인덱스를 추가했음에도 불구하고 속도가 기대만큼 개선되지 않는 상황을 경험하게 됩니다. 오늘은 이러한 상황을 실습과 함께 분석하고, 인덱스의 한계점과 해결 방안을 공유해보겠습니다.

1. 문제 상황

다음과 같은 쿼리를 실행한다고 가정해 봅시다.

SELECT * FROM customer WHERE grade = 'Silver' AND gender = 'Male';

• customer 테이블의 데이터 수: 약 100,000개.
• 조건: grade가 'Silver'이고, gender가 'Male'인 데이터를 조회.

2. 인덱스 적용 전 분석

실행 계획

'1', 'SIMPLE', 'customer', NULL, 'ALL', NULL, NULL, NULL, NULL, '99256', '1.00', 'Using where'

설명

• 타입: ALL → 테이블 전체 스캔이 수행됨.
• 스캔된 행 수: 약 99,256개.
• 비용(cost): 10,238 – 전체 데이터를 읽기 때문에 비용이 높음.
• 실제 실행 시간: 0.0738 ~ 65.5ms.
• 필터링: gender와 grade 조건을 전체 테이블에서 필터링.

결론

인덱스가 없기 때문에 MySQL은 테이블 전체를 스캔해야 합니다. 조건에 맞는 데이터만 가져오는 데 불필요한 I/O 비용이 발생하고 속도가 느립니다.

3. 인덱스 적용 후 분석

grade 컬럼에 인덱스를 생성해 봅니다.

CREATE INDEX idx_grade ON customer (grade);

실행 계획

'1', 'SIMPLE', 'customer', NULL, 'ref', 'idx_grade,idx_gender', 'idx_grade', '1023', 'const', '49628', '50.00', 'Using where' -> Filter: (customer.gender = 'Male') (cost=3420 rows=24814) 
-> Index lookup on customer using idx_grade (grade='Silver') (cost=3420 rows=49628)

설명

• 타입: ref → 인덱스(idx_grade)를 사용하여 일부 행만 조회.
• 스캔된 행 수: 49,628개 → 절반으로 감소.
• 필터링된 행 수: gender = 'Male' 조건을 추가로 적용해 12,562개 행만 반환.
• 비용(cost): 3,420 → 비용이 절반 이하로 감소.
• 실제 실행 시간:
  • Index Lookup: 0.137 ~ 42ms.
  • 추가 필터링: 0.147 ~ 44.7ms.

결론

• idx_grade 인덱스를 사용해 grade = 'Silver' 조건을 빠르게 처리했습니다.
• 하지만 gender = 'Male' 조건이 인덱스에 포함되지 않아 추가 필터링 단계가 발생했습니다.
• 실행 시간은 줄었지만, 여전히 최적화되지 않은 부분이 남아 있습니다.

4. 성능 비교

항목인덱스 적용 전인덱스 적용 후

개선된 점

1. 스캔된 행 수가 절반으로 줄어들었습니다.
2. 비용(cost)이 절반 이하로 감소했습니다.
3. 실행 시간도 개선되었지만, 여전히 추가 필터링이 남아있습니다.

5. 결론

단일 인덱스(idx_grade)를 적용한 결과, 데이터 접근 비용은 감소했지만 속도 개선이 완벽하지 않았습니다. 이유는 gender 조건에 대한 추가 필터링 작업이 남아있기 때문입니다.

해결 방안: 복합 인덱스 또는 커버링 인덱스를 활용하면 두 조건을 동시에 처리할 수 있어 더 나은 성능을 기대할 수 있습니다.

6. 다음 블로그 예고

다음 글에서는 복합 인덱스와 커버링 인덱스를 비교해보겠습니다.

마무리

이번 글을 통해 인덱스 적용 전/후 성능 차이를 비교하고, 단일 인덱스의 한계점을 확인했습니다. 다음 단계에서는 인덱스 설계를 더 최적화해 MySQL 성능을 극대화하는 방법을 배워보겠습니다.
👉 인덱스 설계를 더 최적화해 MySQL 성능을 극대화하는 방법

MySQL Workbench를 사용하다 보면 쿼리 실행 결과로 나오는 Rows의 수가 제한되어 있어 불편함을 느낄 때가 있습니다. 기본적으로 MySQL Workbench는 쿼리 실행 시 최대 1,000개의 행(Row)만 보여줍니다. 하지만 설정을 통해 이 제한을 늘릴 수 있습니다. 아래 단계를 따라 한 번에 더 많은 데이터를 조회할 수 있도록 설정을 변경해 보세요.

1. 기본 Rows 제한 확인하기

MySQL Workbench에서 쿼리를 실행했을 때, 상단에 "Showing rows 0 - 999"와 같은 메시지가 표시됩니다. 이는 현재 설정된 최대 행 수가 1,000개로 제한되어 있다는 의미입니다.

2. Rows 제한 변경 방법

1. Workbench 열기
   • MySQL Workbench를 실행합니다.
2. Preferences 열기
   • Windows: Edit > Preferences
   • Mac: MySQL Workbench > Preferences
3. SQL Editor 설정으로 이동
   • Preferences 창에서 왼쪽 메뉴에서 SQL Execution을 선택합니다.
4. Limit Rows 옵션 수정
   • "Limit Rows" 섹션에서 Limit Rows Count 값이 기본적으로 1000으로 설정되어 있습니다.
   • 이 값을 원하는 숫자로 변경합니다. 예를 들어, 10,000으로 설정하면 한 번에 최대 10,000개의 행을 조회할 수 있습니다.
5. 저장 및 적용
   • 설정을 저장한 뒤, MySQL Workbench를 재시작하면 변경된 설정이 적용됩니다.
    

3. 쿼리 실행에서 제한 없이 조회하기

설정 변경 외에도 특정 쿼리를 실행할 때만 제한 없이 데이터를 조회하고 싶다면 다음과 같은 방법을 사용할 수 있습니다.

• 쿼리 끝에 LIMIT 조건을 추가하지 않고 실행하면 설정된 최대 행 수까지 조회됩니다.
• 무제한으로 조회하고 싶은 경우:
  • Preferences의 "Limit Rows" 값을 0으로 설정하면 모든 데이터를 제한 없이 조회할 수 있습니다. 하지만 데이터가 많을 경우 Workbench가 느려지거나 멈출 수 있으니 주의하세요.

SELECT * FROM your_table;

4. 주의

• 많은 데이터를 조회할 때
  • 너무 많은 행을 한 번에 조회하면 MySQL Workbench가 느려질 수 있습니다. 필요한 데이터만 가져오는 조건(WHERE)을 추가하는 것을 추천합니다.
• 설정 복원
  • 필요에 따라 다시 기본값(1,000)으로 복원할 수 있습니다.

MySQL Workbench를 사용하다 보면 SQL 예약어를 대문자로 통일하면 가독성이 좋아지고, 코드 스타일을 일관되게 유지할 수 있습니다. MySQL Workbench는 이 작업을 자동으로 처리해 주는 기능을 제공합니다. 아래 단계를 따라 설정하면 SQL 예약어를 자동으로 대문자로 변환할 수 있습니다.

1. SQL Editor 설정에서 예약어 대문자 활성화

MySQL Workbench는 SQL 구문을 자동으로 스타일링할 수 있는 기능을 제공합니다. 예약어를 자동으로 대문자로 바꾸려면 아래 단계를 따르세요:

1. Workbench 열기: MySQL Workbench를 실행합니다.
2. Preferences 열기:
   • Windows: Edit > Preferences
   • Mac: MySQL Workbench > Preferences
3. SQL Editor 설정으로 이동:
   • Preferences 창에서 SQL Editor 탭을 선택합니다.
4. Syntax Style 변경:
   • "Query Editor" 섹션으로 이동합니다.
   • Use UPPERCASE keywords on completion 옵션을 활성화합니다.
5. 저장 및 적용:
   • 설정을 저장하고 적용한 뒤 Workbench를 다시 시작하세요.

데이터베이스에서 트랜잭션(Transaction)은 데이터의 일관성과 안정성을 보장하는 데 중요한 역할을 합니다. 데이터 변경 작업을 묶어서 실행하거나, 오류 발생 시 이전 상태로 되돌릴 수 있는 기능을 제공합니다.
이번 글에서는 트랜잭션의 개념과 COMMIT, ROLLBACK의 활용 방법을 살펴보겠습니다.

1. 트랜잭션의 개념

트랜잭션이란?

• 트랜잭션(Transaction)은 데이터베이스에서 하나의 논리적인 작업 단위를 의미합니다. 여러 작업을 하나의 단위로 묶어서 실행하며, 데이터의 일관성(Consistency)을 보장합니다.

트랜잭션은 다음 4가지 특성을 만족해야 합니다(ACID 특성)

트랜잭션의 실행 흐름

1. 트랜잭션 시작 (START TRANSACTION 또는 BEGIN).
2. 작업 수행 (INSERT, UPDATE, DELETE 등).
3. 결과를 확정(COMMIT)하거나 취소(ROLLBACK)하여 완료.

2. COMMIT과 ROLLBACK

COMMIT

• 작업을 완료하고 데이터 변경 사항을 데이터베이스에 영구적으로 반영합니다.
• COMMIT 이후에는 트랜잭션의 변경 사항을 되돌릴 수 없습니다.

문법

COMMIT;

ROLLBACK

• 작업 도중 오류가 발생하거나 취소해야 할 경우, 데이터 변경 사항을 트랜잭션 시작 이전 상태로 되돌립니다.
• 트랜잭션 내에서만 사용 가능합니다.

문법

ROLLBACK;

예제

1. 트랜잭션 시작 후 COMMIT

• 계좌에 $1,000을 추가하고 $100을 차감한 작업을 COMMIT으로 확정합니다.

START TRANSACTION; 

INSERT INTO Account (AccountId, Balance) VALUES (1, 1000);
UPDATE Account
SET Balance = Balance - 100
WHERE AccountId = 1; 

COMMIT;

2. 트랜잭션 시작 후 ROLLBACK

• 작업 도중 오류가 발생한 경우, ROLLBACK으로 모든 변경 사항을 취소합니다.

START TRANSACTION;

INSERT INTO Account (AccountId, Balance) VALUES (2, 500); 

UPDATE Account SET Balance = Balance + 100
WHERE AccountId = 2; 

ROLLBACK;

SAVEPOINT

• 트랜잭션 내에서 특정 지점을 저장하여 부분적으로 롤백할 수 있는 기능입니다.

문법

SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;

예제

START TRANSACTION;

INSERT INTO Account (AccountId, Balance) VALUES (3, 1000);
SAVEPOINT sp1;

UPDATE Account SET Balance = Balance - 200 WHERE AccountId = 3;
SAVEPOINT sp2;

ROLLBACK TO sp1; -- sp1 이후의 작업을 취소
COMMIT;

3. 자동 커밋 (Auto-Commit)

MySQL에서는 기본적으로 자동 커밋(Auto-Commit) 모드가 활성화되어 있습니다.

자동 커밋 모드에서는 각 쿼리가 실행될 때마다 자동으로 COMMIT이 수행됩니다.

자동 커밋 끄기

SET autocommit = 0; -- 자동 커밋 비활성화

START TRANSACTION;

INSERT INTO Account (AccountId, Balance) VALUES (4, 200);

COMMIT; -- 명시적으로 COMMIT 실행

자동 커밋 다시 활성화

SET autocommit = 1;

4. 요약

• 트랜잭션: 데이터를 안전하게 처리하기 위해 여러 작업을 하나의 단위로 묶습니다.
• COMMIT: 작업을 완료하고 변경 사항을 영구적으로 저장합니다.
• ROLLBACK: 작업 중단 시 변경 사항을 취소하고 이전 상태로 복원합니다.
• SAVEPOINT: 트랜잭션 내 특정 지점을 저장하여 부분적으로 롤백할 수 있습니다.
• 자동 커밋: MySQL 기본 설정으로, 명시적인 트랜잭션 관리가 필요하면 Auto-Commit을 비활성화합니다.

SQL 트랜잭션은 데이터 무결성을 보장하며, 특히 중요한 데이터를 다룰 때 필수적인 도구입니다.

다음 글에서는 SQL 윈도우 함수를 다뤄보겠습니다.

SQL 복합 쿼리는 여러 쿼리의 결과를 하나로 결합하거나 특정 조건에 따라 결과를 비교하는 기능을 제공합니다. MySQL 8.0.31 이상에서는 INTERSECT와 EXCEPT도 공식적으로 지원됩니다. 이번 글에서는 UNION, UNION ALL, INTERSECT, EXCEPT의 개념과 활용 방법을 예제와 함께 살펴보겠습니다.

1. UNION과 UNION ALL

연산자 설명

기본 문법

SELECT 컬럼명
FROM 테이블1
UNION [ALL]
SELECT 컬럼명
FROM 테이블2;

예제

1. 중복 제거한 결과 반환 (UNION)

SELECT FirstName, LastName
FROM Customer
UNION
SELECT FirstName, LastName
FROM Employee;

• 고객과 직원 테이블에서 이름 목록을 합칩니다.

2. 중복 포함한 결과 반환 (UNION ALL)

SELECT FirstName, LastName
FROM Customer
UNION ALL
SELECT FirstName, LastName
FROM Employee;

• 고객과 직원 테이블에서 이름 목록을 중복 포함하여 합칩니다.

3. ORDER BY와 함께 사용

SELECT FirstName, LastName
FROM Customer
UNION
SELECT FirstName, LastName
FROM Employee
ORDER BY FirstName ASC;

• 고객과 직원 이름을 합친 후 이름 기준으로 오름차순 정렬합니다.

2. INTERSECT

INTERSECT는 두 쿼리의 결과에서 공통된 데이터를 반환합니다.

기본 문법

SELECT 컬럼명
FROM 테이블1
INTERSECT
SELECT 컬럼명
FROM 테이블2;

예제

1. 고객(Customer)과 직원(Employee) 중 이름이 같은 사람 조회

SELECT FirstName, LastName FROM Customer
INTERSECT 
SELECT FirstName, LastName FROM Employee;

2. 두 테이블에서 동일한 국가 조회

SELECT Country FROM Customer
INTERSECT
SELECT Country FROM Employee;

3. EXCEPT

EXCEPT는 첫 번째 쿼리의 결과에서 두 번째 쿼리의 결과를 제외한 차집합을 반환합니다.

Oracle에서는 MINUS라는 이름으로 사용됩니다.

기본 문법

SELECT 컬럼명
FROM 테이블1
EXCEPT
SELECT 컬럼명
FROM 테이블2;

예제

1. 고객(Customer) 중 직원(Employee)에는 없는 사람 조회

SELECT FirstName, LastName FROM Customer 
EXCEPT
SELECT FirstName, LastName FROM Employee;

2. 직원(Employee) 테이블에는 없고 고객(Customer) 테이블에만 있는 국가 조회

SELECT Country FROM Customer 
EXCEPT
SELECT Country FROM Employee;

4. MySQL에서 INTERSECT와 EXCEPT

MySQL 8.0.31부터는 INTERSECT와 EXCEPT가 기본적으로 지원되지만, 이전 버전에서는 아래와 같은 대체 방법을 사용할 수 있습니다.

INTERSECT의 대체 쿼리

SELECT Customer.FirstName, Customer.LastName
FROM Customer
INNER JOIN Employee
ON Customer.FirstName = Employee.FirstName
   AND Customer.LastName = Employee.LastName;

EXCEPT의 대체 쿼리

SELECT Customer.FirstName, Customer.LastName
FROM Customer
LEFT JOIN Employee
ON Customer.FirstName = Employee.FirstName
   AND Customer.LastName = Employee.LastName
WHERE Employee.FirstName IS NULL;

5. 요약

연산자 설명 MySQL 지원 여부

결론

• MySQL 8.0.31부터 INTERSECT와 EXCEPT를 기본적으로 지원하므로, 대체 쿼리 없이 간편하게 사용할 수 있습니다.
• 이전 버전을 사용하는 경우 INNER JOIN과 LEFT JOIN으로 동일한 결과를 구현할 수 있습니다.
• SQL 복합 쿼리는 데이터 분석 및 통합에서 강력한 도구이므로, 다양한 상황에서 활용할 수 있습니다.

SQL 복합 쿼리는 데이터 통합 및 비교에 강력한 도구입니다. 데이터베이스 시스템에 따라 지원 여부가 다르므로, MySQL의 대안 방법도 익혀두는 것이 좋습니다. 다음 글에서는 SQL 트랜잭션과 롤백에 대해 알아보겠습니다.

이번 블로그에서는 Artillery를 이용한 성능 테스트 결과를 JSON 파일로 저장하고, 이를 HTML로 변환하여 시각화하는 방법을 다룹니다.

1. 테스트 결과를 JSON 파일로 저장

Artillery 실행 시 --output 옵션을 사용하여 결과를 JSON 파일로 저장할 수 있습니다. 아래 명령어는 advanced-load-test.yml 설정 파일로 테스트를 실행하고, 결과를 result.json 파일에 저장합니다:

artillery run --output result.json advanced-load-test.yml

result.json 파일은 테스트 결과의 원시 데이터를 담고 있으며, 이 데이터를 활용해 다양한 방식으로 분석하거나 시각화할 수 있습니다.

2. JSON 결과를 HTML로 변환

Artillery는 JSON 결과를 HTML 파일로 변환해 주는 report 명령어를 제공합니다. 이를 사용하면 시각화된 결과를 쉽게 확인할 수 있습니다:

artillery report result.json --output result.html

위 명령어를 실행하면 result.html 파일이 생성됩니다. 이 HTML 파일에는 테스트의 주요 지표(요청 성공률, 응답 시간 통계, 처리량 등)가 포함된 시각화된 결과가 담깁니다.

3. HTML 결과 확인

생성된 result.html 파일을 브라우저에서 열면 테스트 결과를 시각적으로 확인할 수 있습니다. 예를 들어:

open result.html

결과 화면에서는 다음과 같은 정보를 확인할 수 있습니다:

• 요청 성공률 및 실패율
• 응답 시간 분포 (평균, 95th 퍼센타일 등)
• 초당 처리량 (RPS)

4. HTML 결과지 주요 지표 설명

Artillery의 HTML 결과 페이지에는 성능 테스트의 다양한 메트릭이 시각화되어 있습니다. 아래는 주요 지표와 그 의미를 간략히 정리한 내용입니다:

1. Overall Latency Distribution

• 요청의 응답 시간 분포를 보여주는 섹션입니다. 주요 지표는 다음과 같습니다:이 값들을 통해 시스템의 응답 속도가 어떤 수준으로 유지되고 있는지 파악할 수 있습니다.
  • max: 테스트 중 측정된 가장 긴 응답 시간 (최대값).
  • p95 (95th Percentile): 요청 중 상위 95%가 이 시간보다 빠르게 응답. 즉, 상위 5%의 느린 요청을 제외한 최대 응답 시간.
  • p99 (99th Percentile): 요청 중 상위 99%가 이 시간보다 빠르게 응답. 가장 느린 1% 요청을 제외한 응답 시간을 의미합니다.

2. Summary

• Test duration: 테스트가 실행된 총 시간 (초 단위).
  • 예: 130 sec는 테스트가 130초 동안 실행되었음을 의미합니다.
• Scenarios created: 테스트 동안 생성된 사용자 시나리오 수.
  • 예: 1320은 총 1320개의 사용자 시나리오가 생성되었음을 나타냅니다.
• Scenarios completed: 시나리오가 정상적으로 완료된 횟수.
  • 예: 1320은 생성된 1320개의 시나리오가 모두 성공적으로 완료되었음을 의미합니다. 실패한 시나리오가 있다면 이 수는 작아질 것입니다.

3. Other Key Metrics

• Requests Per Second (RPS): 초당 처리된 요청 수. 서버의 처리량을 측정합니다.
• Failures: 실패한 요청 수. 서버에서 오류가 발생했거나 시간 초과된 요청을 포함합니다.
• Response Time (ms):
  • 요청에 대한 서버의 응답 속도를 밀리초(ms) 단위로 표시.
  • 평균, 최소, 최대 응답 시간과 다양한 퍼센타일 값(예: p95, p99)을 제공합니다.

예시 설명

HTML 결과지에서 아래와 같은 정보가 나왔다면:

• Test duration: 130초 동안 테스트가 진행됨.
• Scenarios created: 1320개의 시나리오가 생성됨.
• Scenarios completed: 모든 시나리오가 정상적으로 완료됨.
• Overall Latency Distribution:
  • max: 900ms → 가장 느린 응답 시간은 900ms.
  • p95: 150ms → 95%의 요청이 150ms 이내에 응답.
  • p99: 300ms → 99%의 요청이 300ms 이내에 응답.