3장. 데이터

본 포스트는 사내 스터디를 진행하며 정리한 포스트입니다

MYSQL을 더 빠르게, 성능 최적화 선택과 집중

---

이번 장에서는?

• 간접 쿼리 최적화를 시작하려고 한다
• 직접 쿼리 최적화가 많은 문제를 해결하지만 전부를 해결해 주는 것은 아닙니다
• 잠깐 직접 vs 간접 쿼리 최적화 복습
  • 직접 쿼리 최적화: 쿼리와 인덱스를 변경하는 일
  • 간접 쿼리 최적화: 데이터와 접근 패턴을 변경하는 일
• 적은 데이터는 더 나은 성능을 가져온다
• 데이터가 적을수록 시스템 리소스(CPU, 램, 스토리지 등)가 적게 필요하므로 데이터를 줄임으로써 성능을 향상시킬 수 있다

 

세가지 비밀

• 먼저 MySQL 성능에 관한 세가지 비밀을 알아보자
• 위와 같은 문제들을 항상 밝히지 않는 이유는 문제를 복잡하게 만들 수 있고, 직관적이지 않기 때문이다

인덱스가 도움이 되지 않을 수 있다

• 인덱스는 성능에서 핵심이지만 좋은 인덱스라도 쿼리가 느릴 수 있다
• 인덱스와 인덱싱에 대해 학습한 후 엔지니어는 인덱스 스캔과 테이블 스캔을 피하는 데 너무 능숙해져서 인덱스 조회만 남게된다. 이는 바람직한 문제이지만 여전히 아이러니한 면이 존재한다
• 인덱스 없이는 성능을 달성할 수 없지만, 인덱스가 무한한 데이터 크기에 대해 무한한 영향력을 제공한다는 것은 아니다
• 인덱스 스캔
  • 복습(인덱스 스캔): MySQL은 PK로 모든 행을 읽기전에 세컨더리 인덱스로 행 읽기를 시도하는데, 이를 인덱스 스캔이라고 한다
  • 인덱스 스캔은 테이블이 커질수록 인덱스도 함께 커지므로 영향력이 점점 감소한다(즉 테이블 행 수가 증가할수록 쿼리 응답시간도 증가한다 )
  • 인덱스 전용 스캔은 대부분 많은 수의 값을 읽어야 하는데 이 인덱스 전용 스캔조차 확장성이 부족한 경향이 있다
  • 복습(인덱스 전용 스캔): MySQL은 인덱스에서 열값(전체 행이 아님)을 읽는다, 이를 위해 커버링 인덱스가 필요
• 행 찾기
  • 인덱스 조회를 사용하는 느린 쿼리를 최적화할 때 확인하는 첫 번째 쿼리 메트릭은 조회된 행이다
  • 일치하는 행을 찾는 것이 쿼리의 기본 목적이지만 좋은 인덱스를 사용하더라도 쿼리가 너무 많은 행을 검사할 수도 있다
  • 여러 인덱스 조회 접근 유형이 많은 행과 일치할 수 있기 때문
  • 한 행만 일치하는 인덱스 조회 접근 유형
    • type: system
      • 단일 행만 존재하는 테이블에 대해 최적화된 조회
      • 보통 시스템 테이블이나 아주 작은 정적 데이터셋에서 볼 수 있다
      • 특수한 경우에만 나타나며, 일반적인 테이블에서는 자주 볼 수 없습니다
      • type: const와 비슷하게 처리되지만, 더 작은 규모의 테이블에서 사용됨
    • type: const
      • 고정된 값으로 조회되는 경우로 매우 효율적인 쿼리 실행 유형 중 한 가지
      • 테이블에 있는 1개의 행 또는 매우 적은 수의 행을 특정 조건을 통해 고정적으로 찾을 수 있을 때 나타난다
      • Primary Key나 Unique Key 컬럼에 대해 = (등호) 조건으로 조회하는 경우에 발생
      • 하나의 행만 검색되기 때문에 테이블 스캔이 필요 없고, 즉시 해당 행을 반환
    • type: eq_ref
      • Primary Key 또는 Unique Key를 기반으로 조인할 때 발생하며, 매칭되는 정확한 하나의 행을 찾을 수 있는 경우
      • 조인에서 두 테이블 간의 관계가 1:1로 매핑될때
    • type: unique_subquery
      • 특정 유형의 서브쿼리에 대한 최적화된 쿼리 실행 방식
      • IN 절에 사용된 서브쿼리를 최적화하기 위해 사용되며, Unique Index나 Primary Key를 통해 단일 값 집합만을 반환할 수 있는 경우에 발생
      • SELECT * FROM products WHERE product_id IN (SELECT order_id FROM orders WHERE customer_id = 1);
        • orders 테이블의 order_id가 Unique Index나 Primary Key
        • WHERE customer_id = 1, 동등비교로 행의 order_id를 고유하게 조회하고 products 테이블의 product_id와 비교하는 작업을 할때 나타날 수 있다
  • EXPLAIN 계획의 위에서 나열된 4가지 type 필드가 아니라면 rows 필드와 조회된 쿼리 메트릭 행에 주의를 기울여야 한다
  • 너무 많은 행을 검사하는 것은 어떤 인덱스 조회든 관계없이 느려진다
  • 매우 낮은 인덱스 선택도(카드널리티를 테이블의 행 수로 나눈 값)는 인덱스 조회를 느리게 하는 공범일 가능성이 높다
  • 즉 너무 많은 행과 일치할 수 있어서 선택도가 매우 낮은 인덱스를 선택하지 않는다
  • MySQL이 선택하지 않은 실행 계획을 보려면 FORCE INDEX로 쿼리를 EXPLAIN하여 possible_keys 필드에 나열된 인덱스를 사용하라

• 더 알아보기
  • USE INDEX: MySQL에 특정 인덱스를 선호하도록 지시하지만, 최적화기는 여전히 다른 인덱스를 선택할 수 있습니다.
  • FORCE INDEX: 특정 인덱스를 강제 사용합니다. MySQL이 이 인덱스를 반드시 사용해야 하며, 다른 인덱스는 무시됩니다.
  • IGNORE INDEX: 지정된 인덱스를 사용하지 않도록 MySQL에 지시합니다. 최적화기가 이 인덱스를 사용하지 않으며, 다른 인덱스를 선택하게 됩니다.
  • 반드시 충분한 테스트후에 사용해야한다
• 복습(인덱스 통계): MySQL은 인덱스 통계를 활용하여 최적의 인덱스를 선택한다. 해당 통계를 갱신하려면 ANALYZE TABLE을 사용하라. 단 해당 명령어는 테이블에 접근하는 모든 쿼리를 차단할 수 있는 플러시 잠금이 필요하기에 사용량이 많은 서버에서는 주의해야한다, 실행속도는 빠르다
• 인덱스 선택도는 카디널리티와 테이블의 행 수를 대상으로 하는 기능이다. 카디널리티는 일정하게 유지되지만 행 수가 증가하면 선택도가 감소한다. 따라서 테이블이 작을 때 도움이 되었던 인덱스가 테이블이 커지게되면 도움이 되지 않을 수 있다
• 테이블 조인
  • 테이블을 조인할 때 각 테이블의 몇 개 행이 성능을 빠르게 떨어뜨린다
  • 중첩반복조인(NLJ) 알고리즘은 조인을 위해 접근한 전체 행 수가 각 테이블에 대한 접근한 행의 곱이다 ( 3개 테이블이 각각 100개씩인 경우, 도합 100만 개 행에 접근 )
  • 이를 방지하려면 조인된 각 테이블에 대한 인덱스 조회가 위에서 봤던 한 행만 일치하는 인덱스 조회 접근 유형 중 하나를 사용하여 하나의 행만 일치하는 것이 좋다
  • MySQL은 작성된 쿼리에 따라 거의 어떤 순서로든 테이블을 조인합니다
  • 잘못된 조인에 대한 해결책은 다른 테이블에서 더 나은 인덱스를 생성하여 MySQL이 조인 순서를 변경할 수 있도록 하는 것
  • 인덱스 조회가 없다만 셀렉트 풀 조인 ( 이건 무조건 막아야함.. ), 있더라도 단일 행과 일치하지 않으면 테이블 조인에 어려움을 겪을 수 있다
• 작업 세트 크기
  • 인덱스는 메모리에 있을 떄만 유용하다
  • 쿼리가 조회하는 인덱스값이 메모리에 없으면 MySQL은 디스크에서 값을 읽습니다.
  • 인덱스를 구성하는 B-TREE 노드는 16KB 페이지에 저장되고, MySQL은 필요에 따라 메모리와 디스크 간에 페이지를 교환한다
  • 여기서 주요한 문제는 인덱스가 메모리를 놓고 경쟁을 한다
  • 메모리는 제한적이나 인덱스가 많고 테이블 크기 대비 많은 비율의 값을 자주 조회할 때는, MySQL이 자주 사용하는 인덱스값을 메모리에 유지하려고 시도하기 때문에 스토리지 입출력이 증가한다 ( 메모리와 디스크간 페이지 교환 )
  • 자주 사용하는 인덱스값과 이들이 참조하는 PK행을 작업세트라고 한다
  • DBA는 보통 전체 데이터 크기의 10%에 해당하는 메모리를 할당하며 표준 메모리값(64GB, 128GB 등)에 맞춘다
  • 작업 세트의 크기가 사용할 수 있는 메모리보다 훨씬 커지면 인덱스가 도움이 되지 않을 수 있다
  • 물론 메모리가 클수록 빠르게 해결할 수 있지만, 지속 가능한 접근 방식이 아니다(이전 장에서 다룬 내용)

데이터가 적을수록 좋다

• 제한 없이 증가하는 데이터는 관리하기 어렵다
• 데이터 크기 때문에 문제가 발생하기 전 제한 없이 증가하는 데이터에 문제를 제기해야한다

QPS가 낮을수록 좋다

• 복습(QPS): queries Per Second의 약자로, 초당 쿼리 수를 의미합니다. 이는 MySQL 서버가 1초당 처리하는 SQL 쿼리의 개수를 나타내며, 데이터베이스 서버의 성능을 평가하는 중요한 지표 중 하나입니다. QPS가 높을수록 MySQL 서버가 많은 쿼리를 효율적으로 처리할 수 있다는 것
• QPS는 숫자에 불과하며 원시 처리량을 측정한 값이다
  • QPS는 일반적으로 쿼리나 성능에 대해 질적인 정보를 제공하지 않는다
  • 1000 QPS에서 SELECT 1을 실행하려면 시스템 리소스가 거의 필요하지 않지만, 똑같은 QPS에서 복잡한 쿼리는 모든 시스템 리소스에 많은 부담을 줄 수 있습니다
• QPS값은 객관적인 의미가 없다
  • 애플리케이션과 관련해서만 의미가 있다
  • 평소 평균적인 처리량이 2000QPS라면, 100QPS로 떨어진 상황이 중단을 나타내는 지표일 수 있다
• QPS를 높이기는 어렵다
• QPS는 질적이지 않고 애플리케이션과 관련만 있을 뿐이며 높이기도 어렵다. 즉 도움이 되지않는다. 따라서 QPS가 낮을수록 좋다..?

 

최소 데이터 원칙

• 필자는 최소 데이터 원칙을 필요 데이터만 저장과 접근으로 정의한다고 합니다

데이터 접근

• 필요 이상으로 많은 데이터에 접근하지 마세요
• 접근은 MySQL이 쿼리를 실행하기 위해 수행하는 모든 작업을 의미
• 데이터 접근 효율성을 확인하기 위해 아래 점검표를 활용해봐라
  • 필요한 열만 반환
  • 쿼리 복잡성 감소
  • 행 접근 제한
  • 결과 세트 제한
  • 행 정렬 피하기 << 일반적으로 성능에 영향을 미치진 않는다
• 데이터 접근이 효율적일 때 MySQL은 거의 인메모리 캐시처럼 작동하며, 놀라운 QPS와 부하로 쿼리를 실행한다
• 필요한 열만 반환
  • 필요한 열만 반환해야한다
  • SELECT * 쿼리를 남용하지말라
  • 테이블에 BLOB, TEXT또는 JSON 열이 있을 때 특히 중요하다
  • 복습
    • BLOB: 이진 데이터(binary data)를 저장하는 데 사용되는 데이터 타입으로, 주로 이미지, 오디오, 비디오 파일, 또는 바이너리 형식의 대용량 데이터를 저장
    • TEXT: 대용량 텍스트 데이터를 저장하는 데 사용됩니다. BLOB과 비슷하지만, 텍스트 데이터로 취급되며, 문자 인코딩이 적용
    • JSON: JSON 형식으로 데이터를 저장할 수 있는 타입입니다. MySQL 5.7부터 도입된 JSON 타입은 구조화된 데이터를 저장하는 데 매우 유용
• 쿼리 복잡성 감소
  • 쿼리는 될 수 있는 한 단순해야한다
  • 쿼리 복잡도는 쿼리를 구성하는 모든 테이블, 조건, SQL 절을 나타낸다
  • 쿼리가 복잡할수록 분석하고 최적화하기가 더 어렵다
  • 데이터 접근과 관련하여 단순 쿼리는 테이블, 조건, SQL 절이 몇 개 없어서 MySQL에 대한 작업이 더 단순해지기 때문에 더 작은 데이터에 접근하는 경향이 있다
  • 쿼리 최적화시 EXPLAIN 계획을 항상 확인해라
• 행 접근 제한
  • 쿼리는 될 수 있는 한 적은 수의 행에 접근해야한다
  • 일반적으로 시간 경과에 따른 데이터 증가가 원인이 된다
  • 데이터 증가와 단순한 실수가 교차하는 지점에서 가장 중요한 원인은 범위와 목록을 제한하지 않는 것
  • LIMIT절은 행을 일치시킨 후 결과 세트에 LIMIT가 적용되므로 행 접근을 제한하지 않는다
  • ORDER BY ... LIMIT 최적화는 예외다. MySQL이 인덱스 순서대로 행에 접근할 수 있을 때, 일치하는 행을 찾다가 LIMIT 수만큼 발견되면 행 읽기를 중지한다

LIMIT 절이 없는 쿼리, 8개의 행을 반환

• ORDER BY ... LIMIT 최적화가 사용될때 EXPLAIN은 보고되지 않는다
• ORDER BY ... LIMIT가 사용됐음에도 rows: 8이 되어있는것을 확인할 수 있다
• LIMIT 쿼리 최적화

MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.19 LIMIT Query Optimization

• 이어서
  • 하지만 MySQL이 ORDER BY ... LIMIT 최적화를 사용하여 두 행에만 접근한다고 추론할 수 있다
    • 쿼리는 인덱스(type: range)를 사용
    • ORDER BY 열은 해당 인덱스의 맨 왼쪽 접두사이다(key: a)
    • Extra 필드는 "Using filesort"를 보고하지 않음
  • 범위와 목록 제한과 관련하여 확인해야할 중요한 요소는 어플리케이션이 쿼리에 사용되는 입력을 제한하는가? 이다
  • 쓰기는 일반적으로 InnoDB가 일치하는 행을 갱신하기 전에 접근하는 모든 행을 잠그므로 행 접근을 제한하는 것이 중요하다
  • EXPLAIN을 사용하여 예상되는 행 접근(EXPLAIN 보고의 rows 필드)을 확인하고 조회된 행을 모니터링하여 너무 많은 행에 접근하는 것을 방지하세요
• 결과 세트 제한
  • 쿼리는 될 수 있는 한 적은 수의 행을 반환해야한다
  • 첫번째 변형: WHERE 조건을 사용하는 대신 행을 필터링하는 애플리케이션 코드가 있는지, 이를 의도한 것인지 확인해보세요
    • 어플리케이션은 더 많은 행을 선택하여 복잡한 쿼리를 피하고, MySQL에서 어플리케이션으로 일치하는 행을 옮기는 경우가 있을 수 있습니다. 이 기술은 응답 시간을 단축할때에만 유용합니다
  • 두번째 변형: 쿼리에 ORDER BY절이 있고 어플리케이션이 정렬된 행의 일부분을 사용할 때 발생
    • 행의 순서가 결정적인 특징입니다
    • 쿼리는 1000개의 행을 반환하지만 어플리케이션은 정렬된 20개의 행만 사용한다면 LIMIT 20으로 간단하게 해결가능
    • ORDER BY ... LIMIT 최적화를 사용할 수 있을 때만 LIMIT 20 OFFSET N으로 페이징처리를 효율적으로 할 수 있다
    • 만약 최적화가 없다면 크지만 합리적인 LIMIT 절을 통해서 (ex LIMIT 100)  요청에 대한 결과세트 크기를 획기적으로 줄여도 도움이 된다
  • 세번째 변형: 어플리케이션이 결과 세트를 집계하기만 할 때 발생
    • 결과 세트(열 값)를 직접적으로 집계하지말고 SQL의 집계함수를 사용해봐라
    • 안티 패턴
      • 열값 추가하기 => SUM(column)
      • 행의 개수 세기 => COUNT(*)
      • 값의 개수 세기 => COUNT(column) ... GROUP BY column
      • 고윳값의 갯수 세기 => COUNT(DISTINCT column)
      • 고윳값 추출하기 => DISTINCT
    • 쿼리는 애플리케이션 코드가 아닌 SQL에서 집계하도록하여 결과 세트를 제한할 수 있다
• 행 정렬 피하기
  • 쿼리는 행 정렬을 피해야한다
  • MySQL 대신 어플리케이션에서 행을 정렬하면 ORDER BY절을 제거하여 쿼리 복잡성을 줄이고 어플리케이션 인스턴스에 작업을 분산시켜 더 좋은 확장성을 가질 수 있다
  • LIMIT절이 없는 ORDER BY절은 삭제 가능하며 어플리케이션이 행을 정렬할 수 있음을 알리는 신호이다

COUNT(*) vs COUNT(column)

• COUNT(*)는 일치하는 행의 수를 계산
• COUNT(column)는 일치하는 행의 열에서 NULL이 아닌 값의 수를 계산

 

데이터 스토리지

• 필요이상으로 많은 데이터를 저장하지 마세요
• 효율적인 데이터 스토리지 점검표
  • 필요한 행만 저장됨
  • 모든 열이 사용됨
  • 모든 열이 간결하고 실용적임
  • 모든 값이 간결하고 실용적임
  • 모든 세컨더리 인덱스가 사용되며 중복되지 않음
  • 필요한 행만 유지됨
• 데이터 스토리지의 비효율성을 찾아 수정하기에 가장 좋은 시기는 데이터베이스가 작을 때이기 때문에 꼭 체크해봐라

필요한 행만 저장됨

• 어플리케이션에 변경 사항이 늘어남에 따라 엔지니어는 어플리케이션에 저장된 내용을 추적하지 못할 수 있다
• 어플리케이션이 무엇을 저장하고 있는지 검토한 지 오래되었다면 저장되는 데이터를 살펴보아야한다

모든 열이 사용됨

• 필요한 행만 저장하는 것보다 한 단계 더 심층적인 방법은 필요한 열(컬럼)만 저장하는 것
• 어플리케이션의 변경 사항이 늘어남에 따라 엔지니어는 특히 객체 관계형 매핑을 사용할 때 열을 추적하지 못할 수 있다
• 유일한 해결책은 수동 검토이기 때문에 어플리케이션 쿼리에서 사용하는 열과 테이블의 열을 비교해봐야한다

모든 열이 간결하고 실용적임

• 모든 열을 간결하고 실용적으로 만드는 것이 중요하다
• 간결함이란 가장 작은 데이터 타입을 사용하여 값을 저장하는 것
• 실용적이란 너무 작아서 사용자나 어플리케이션이 번거롭거나 오류가 발생하기 쉬운 데이터 타입을 사용하지 않는 것
• 고전적인 안티패턴은 VARCHAR(255)이다 ( 뜨금...;; )
  • 이 데이터 타입은 무엇이든 저장하는 데 사용될 것이고, 그것이 비효율적인 이유
  • 의미없는 데이터가 저장되면 의미없는 데이터가 출력된다
  • 사용하려는 크기에 따라 CHAR(2)가 더 실용적일 수 있으며, ENUM등의 타입도 고려하여 선택하면 좋다
• 여기서 잠깐. ENUM TYPE
  • 유효성 검증: 지정된 값 외에는 저장할 수 없으므로 데이터 무결성을 유지하는 데 도움이 됩니다.
  • 효율성: 내부적으로 숫자 인덱스를 사용해 메모리 공간을 절약합니다.
  • 가독성: 제한된 값 목록을 명시적으로 설정할 수 있어 데이터베이스 설계가 명확해집니다.
  • VS VARCHAR
    • ENUM의 값 집합이 빈번하게 변경되어야 하는 경우 관리가 어렵고, 이를 수정할 때는 테이블 구조를 변경해야 하므로 다소 비효율적일 수 있습니다.
    • 값의 범위가 고정되어 있지 않고 자유롭게 변경되거나 추가될 가능성이 높다면, 차라리 VARCHAR를 사용하는 것이 유연하다

• 열의 문자 세트에 주의하라
  • 열의 문자 세트는 명시적으로 정의하지 않으면 테이블의 문자 세트가 기본값이다 ( 이는 서버의 문자 세트를 기본으로 사용한다 )
  • MySQL 8.0에서 기본 서버의 문자세트는 utf8mb4
    • 문자 집합은 Collation의 첫 부분으로, 예를 들어 utf8mb4_general_ci에서 utf8mb4가 문자 세트를 의미합니다
    • utf8mb4는 CHARSET, utf8mb4_general_ci은 정렬규칙
  • MySQL 5.7과 이전 버전의 경우는 latin1
  • utf8mb4 vs latin1
    • utf8mb4는 UTF-8의 확장 버전으로, 모든 유니코드 문자를 지원 ( 문자 하나당 최대 4바이트 )
    • latin1(또는 ISO-8859-1)은 주로 서유럽 언어에서 사용되는 문자 집합 ( 문자 하나당 최대 1바이트 )

• BLOB, TEXT, JSON 데이터 타입은 매우 보수적으로 사용하라
• 상위 정렬 비트에 대한 낭비를 방지하라 ( 값이 음수일 수 없으면 부호 없는 데이터 타입을 고려해봐라 )

모든 값이 간결하고 실용적임

• 모든 값을 간결하고 실용적으로 만드는 것 또한 중요하다 ( 위에선 열, 여기선 값 )
• 쿼리 최소화...? 이건 이해못했음 어떤 의미에서 데이터 크기가 작아졋다는 것인지...

SELECT /*!40001 SQL_NO_CACHE */ col1, col2 FROM tbl1 WHERE foo=' bar ' LIMIT 1

• 첫 쿼리 137byte에서 70byte로 최소화되었다...?
• 가장 최솟값은 값이 전혀 없는 NULL이다
  • COALESCE를 사용하면 설정된 값으로 치환하여 반환도 가능하다
• COALESCE에 대하여

# null값을 대체
SELECT COALESCE(user_email, 'no-email@example.com') AS email FROM users;

# 여러 값중 선택, phone_home이 null이면 phone_work, 세개다 null이면 null 반환
SELECT COALESCE(phone_home, phone_work, phone_mobile) AS contact_number FROM users;

# 계산시 초기값 설정
SELECT COALESCE(discount, 0) + price AS final_price FROM products;

• NULL값은 고유하다 ( 즉 비교가 불가능하다 ) , 두 개의 NULL 값을 비교하면, 그 값들이 서로 동일하지 않다고 간주된다
• 성능을 확장하기 위해 값의 압축이 필요하다고 생각되면 전문가와 논의하라
• 인코딩의 모범사례로 UTC로만 날짜와 시간을 저장하고 접근하는 점이다
• 추가로 MySQL TIMESTAMP 데이터 타입은 2038년 1월19일에 종료된다
• TIMESTAMP VS DATETIME
  • TIMESTAMP는 서버의 타임존에 의존합니다. 값을 저장할 때는 UTC로 변환되어 저장되고, 조회할 때는 서버의 타임존에 맞게 변환되어 출력
  • DATETIME은 타임존을 고려하지 않고 그 자체로 값을 저장합니다. 따라서 서버나 클라이언트의 타임존 변경과 관계없이 동일한 값을 유지합니다.

• 값의 중복제거를 위한 방법을 알아보자

• 단 비정규화를 통해 위에서 했던 정규화와 반대로 오히려 테이블 조인과 수반되는 복잡성을 제거하여 성능을 향상시킬수도있다
• 이는 속도를 위해 중복 데이터와 트레이드 오프한 사례이다

모든 세컨더리 인덱스가 사용되며 중복되지 않음

• 사용되지 않는 인덱스와 중복 인덱스를 피하는 것은 항상 바람직한 생각이지만, 인덱스는 데이터의 복사본이므로 전체 테이블보다 훨씬 작더라도 테이블 크기가 증가함에 따라 세컨더리 인덱스 크기가 증가한다
• 중복 인덱스 찾기에는 비교적 쉬우나 사용되지 않은 인덱스를 찾는건 어렵다 ( 오랜기간 확인해야한다 )

 

데이터 삭제 또는 보관

• 데이터를 보관하려면 먼저 데이터를 복사한 다음 삭제해야한다
• 어플리케이션에 영향을 미치지 않도록 비잠금 SELECT문(READ_UNCOMMITTED)을 사용한 다음, 어플리케이션이 접근하지 않는 다른 테이블이나 데이터 스토리지에 복사된 행을 작성해야한다

• 위 그림처럼 절대 하지말라고 한다
• 사유는 LIMIT절이 너무 크고 for 반복문 사이에 지연이 없다. 이는 어플리케이션 중단을 유발할 수 있다
• 배치 크기는 안전하고 효과적인 데이터 보관도구에서 핵심이다

배치 크기

• 행이 작고(BLOB, TEXT, JSON 열 없음) MySQL에 큰 부하가 없을 때, 단일 DELETE 문에서 1000개 이하의 행을 수동으로 삭제하는 것이 안전
• 여기서 수동이란 DELETE 문을 병렬이 아닌 직렬(하나씩 실행)로 실행함을 의미
• 반드시 명령문 사이에는 적당한 지연이 필요하다
• 배치 크기는 DELETE 문당 삭제되는 행 수로, LIMIT 절로 제어되고 필요한 경우 단순 지연인 스로틀(뒤에서 다룰 예정)로 조절된다

복제 지연

• 원본 MySQL 인스턴스의 실행시간은 복제본 MySQL 인스턴스에서 복제 지연을 생성한다
• 원본에서 500ms가 걸렸다면 복제본에서도 500ms가 걸린다

스로틀링

• 한 번에 들어오는 요청 수나 자원을 제한하는 기법
• 스로틀링이 없으면 대량 쓰기가 다른 쿼리를 방해하고 어플리케이션에도 영향을 줄 수 있다
• 배치 크기를 500ms 실해 시간으로 보정하려면 배치 크기를 1000으로 시작하고 DELETE 문 사시에 200ms의 지연을 두자
• 이후 배치를 모니터링하고 보정하며 배치크기를 늘리거나 지연시간을 줄여본다
• 최대 실행 시간이 일관되게 목표에 도달할 때까지 배치 크기를 두 배로 늘리거나 더 작게 조정해라, 가능하면 목표치보다 약간 낮은 수준으로 배치 크기를 설정하는 것이 좋다
• 이렇게 보정된 배치 크기를 사용하여 스로틀을 설정하려면 10분 주기로 재실행할 때마다 지연을 천천히 줄여 프로세스를 반복해라

 

로우 락 경합

• 쓰기 작업이 많은 워크로드의 경우, 대량 작업으로 인해 로우 락 경합이 발생할 수 있다
• 쿼리는 동일한 행(혹은 가까운 행)에서 로우 락을 획득하기 위해 대기 ( 주로 INSERT, UPDATE이지만 삭제된 행이 기존의 행과 산재되어 있다면 DELETE문도 영향이 있을 수 있다 )
• 문제는 500ms 실행시간내 10만개의 행을 삭제할 수 있지만 해당 행에 대한 잠금이 어플리케이션이 갱신하는 행과 겹쳐 로우락 경합을 유발할 수 있다는 점이다
• 훨씬 더 짧은 실행 시간으로 보정하여 배치 크기를 줄여야한다 ( 물론 데이터 아카이빙 속도가 느려지는 트레이드 오프이다 )

 

공간과 시간

• 데이터를 삭제해도 실제 디스크 공간이 확보되진 않는다
• 500GB의 데이터를 삭제하면 500GB의 여유페이지가 생기는 것이다 사용하는 디스크 공간은 동일
• 여유 페이지는 성능에 영향을 미치지 않으며 InnoDB는 새 행이 삽입될 때 여유 페이지를 재사용한다
• 하지만 다른 어플리케이션에서 사용할 수 있는 디스크 공간을 낭비하진 말라고 한다

ALTER TABLE ... ENGINE=INNODB

# 5.6이상 온라인으로 가능
ALTER TABLE your_table ENGINE=INNODB, ALGORITHM=INPLACE;

 

• 5.6 이하 온라인이 불가능한 경우
  • 기존 데이터를 새로운 엔진에 맞게 재구성합니다. 이 과정에서 데이터의 복사가 발생하므로, 일시적인 성능 저하가 있을 수 있습니다. 특히 큰 테이블일 경우 더 많은 시간이 소요될 수 있습니다.
  • 테이블을 재구성하는 동안 테이블에 대한 잠금이 발생하므로, 해당 테이블에 대한 읽기 및 쓰기 작업이 차단될 수 있습니다. 이로 인해 애플리케이션의 성능에 영향을 미칠 수 있습니다
• DBA분과 충분한 상의가 필요할듯..

 

바이너리 로그 역설

• 데이터를 삭제하면 바이너리 로그가 발생한다
• 복제에 바이너리 로그가 필요하기 때문
• 테이블에 큰 BLOB, TEXT, JSON열이 포함된 경우, MySQL 시스템 변수 binlog_row_image는 full 설정이 기본이므로 바이너리 로그 크기가 급격히 증가할 수 있다
• 바이너리 로그의 전체 행 이미지에 의존하는 외부 서비스가 없는경우 minimal(또는 noblob)을 사용하는 것이 안전하고 권장된다

 

 

요점 정리

• 데이터가 적을수록 성능이 향상된다
• QPS는 자산이 아니라 부채이므로 낮을수록 좋다
• 인덱스는 MySQL 성능을 위해 필요하지만 도움이 되지 않는 경우가 있다
• 최소 데이터 원칙은 필요한 데이터만 저장하고 접근한다는 의미
• 쿼리가 될 수 있는 한 적은 수의 행에 접근하도록 해라
• 필요 이상으로 많은 데이터를 저장하지 말라
• 데이터를 삭제하거나 보관하는 것은 중요하며 성능을 향상시킨다