데이터베이스

[학습노트] MySQL 학습노트

secretjuju 2024. 7. 9. 12:42

업그레이드

  • MySQL은 마이너 버전 간 업그레이드는 대부분 데이터 파일의 변경 없이 진행됨.
  • 따라서 많은 경우 여러 버전을 건너뛰어서 업그레이드할 수 있음 (ex: 8.0.16 -> 8.0.21)
    • 이때 업그레이드는 MySQL 서버 프로그램만 재설치하면 됨.
  • 메이저 버전 간 업그레이드는 크고 작은 데이터 파일 변경이 필요.
  • 때문에 반드시 직전 버전에서만 업그레이드가 허용됨.
  • 따라서 여러 메이저 버전을 업그레이드하는 경우, 인플레이스 업그레이드보다 mysqldump를 이용한 논리적 업그레이드가 더 나을 수 있음.

MySQL 8.0으로 업그레이드 시 고려 사항

  • 사용자 인증 방식 변경: MySQL 8.0 버전부터 Caching SHA-2 Authentication 인증 방식이 기본 인증 방식으로 바뀜.
  • 호환성 체크: 8.0 업그레이드 전 5.7 버전에서 손상된 FRM 파일이나 호환되지 않는 데이터 타입 또는 함수가 있는지 mysqlcheck 유틸을 사용해 체크.
  • 외래 키 이름 길이: 8.0부터 64 글자로 제한됨.
  • 인덱스 힌트: 이전 버전에서 사용하던 인덱스 힌트가 있다면 8.0에서 성능 저하를 유발할 수 있으니 확인.
  • Group by에 사용된 정렬 옵션: 5.x에서 GroupBy 절의 컬럼 뒤에 ASC나 DESC를 사용하고 있다면 제거하거나 다른 방식으로 변경.
  • 파티션을 위한 공용 테이블 스페이스: MySQL 8.x에선 파티션의 각 테이블스페이스를 공용 테이블스페이스에 저장할 수 없음.

백그라운드 스레드

  • InnoDB에선 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 innodb 버퍼 풀에 기록 후 비동기적으로 나중에 처리되기 때문에 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 됨.
  • MyISAM에선 일반적인 쿼리는 쓰기 버퍼링 기능을 사용할 수 없음.

InnoDB

  • 버퍼풀에 더티 페이지가 많으면 많을수록 디스크 쓰기 폭발 현상이 발생할 가능성이 높아짐.
    • 더티 페이지란 INSERT, UPDATE, DELETE 시 디스크에 바로 반영되지 않고 버퍼풀에 저장하는데, 이때 변경된 페이지가 더티 페이지.
  • 어댑티브 플러시 기능을 활성화하면 버퍼풀의 더티 페이지 비율이나 innodb_io_capacity, innodb_io_capacity_max 설정값에 의존하지 않고 새로운 알고리즘 사용.
    • 리두 로그의 증가 속도를 분석해 적절한 수준의 더티 페이지가 버퍼풀에 유지될 수 있도록 디스크 쓰기를 실행함.
    • innodb_adaptive_flushing_lwm의 기본값은 10%인데, 이는 리두 로그의 공간이 10% 미만이면 어댑티브 플러시가 작동하지 않다가 10% 이상이 되면 어댑티브 플러시 알고리즘이 작동함.
  • InnoDB를 사용하는 서버를 재시작하면 쿼리 처리 성능이 기존보다 10%도 안 나오는 경우가 있을 수 있음.
    • 이는 버퍼풀에 쿼리들이 사용할 데이터가 준비되어 있지 않기 때문일 수 있고, 이를 warm-up이라고 함.
    • innodb_buffer_pool_dump_now 시스템 변수를 사용해 버퍼풀의 상태를 백업할 수 있음.
  • 하지만 보통 상황에 해야 할 작업에 집중한 나머지 버퍼풀의 백업과 복구를 잊을 수 있는데, innodb_buffer_pool_dump_at_shutdown과 innodb_buffer_pool_load_at_startup 설정을 MySQL 설정 파일에 넣어두면 이를 자동으로 해줌.

트랜잭션

  • MyISAM 테이블에서 INSERT INTO table values (1,2,3)을 했을 때 이미 데이터 3이 레코드에 있다면 3이 duplicated 되었다고 에러가 발생함 (auto commit 모드).
    • 하지만 이때 1, 2 데이터는 insert 쿼리가 실패했는데도 불구하고 저장됨.
    • InnoDB는 1, 2 데이터가 저장되지 않음.
  • 가능하면 트랜잭션을 짧게 유지하는 것이 좋음.
    • 커넥션을 소유하는 기간이 길어질수록 여유 커넥션의 개수는 줄어들 것임.
    • 만약 트랜잭션 도중 HTTP Request 등의 네트워크를 통한 원격 서버와 통신하는 등과 같은 작업은 트랜잭션 내에 없애는 것이 좋음.
      • 상황이 허락한다면 분리.
  • 글로벌 락
    • FLUSH TABLES WITH READ LOCK 명령어로 획득 가능.
    • 글로벌 락을 획득하면 다른 세션에서의 SELECT를 제외한 대부분의 DDL, DML이 대기 상태.
    • FLUSH TABLES WITH READ LOCK 명령이 실행되기 전 테이블이나 레코드에 쓰기 잠금을 거는 SQL이 실행되었다면 해당 SQL과 트랜잭션이 완료될 때까지 대기.
    • mysqldump 같은 백업 프로그램이 내가 알지 못하는 사이에 이 락을 걸 수 있기 때문에 mysqldump에서 사용하는 옵션에 따라 어떤 락을 걸게 되는지 확인하는 게 좋음.
    • 보통 레플리카 서버에서 실행됨.
  • 네임드 락
    • 원하는 문자열을 이용해서 락을 수동으로 생성할 수 있음.
    • redis 분산락과 비슷함.
  • 레코드 락
    • InnoDB 스토리지 엔진은 다른 상용 DBMS 들과 다르게 레코드 자체가 아니라 인덱스의 레코드를 잠금.
    • 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정함.
    • 위 차이가 왜 중요한지.
      • UPDATE employees set hire_date=NOW() WHERE first_name='A' AND last_name='BB';
      • 인덱스 레코드에 락을 거는 경우: first_name에만 인덱스가 걸려있다면 first_name이 A인 게 30개, last_name이 BB인 게 10개일 때 last_name과 상관없이 30개 모두 락이 걸림.
        • 복합 인덱스, 혹은 적절한 설계를 통해 이를 최적화 가능. (primary 를 쓰는것도 좋을듯?

실행계획 (type)

  • const
    • type이 const면 프라이머리 혹은 유니크 키 컬럼을 이용하는 where 조건을 처리할 때 반드시 1건을 반환하는 쿼리의 처리 방식.
    • Unique index scan이라고도 불림.
    • 조건문에서 사용 시 여러 번 실행되지 않고, 1번만 실행된 후 상수화됨.
  • fulltext
    • const 조건과 함께 사용되면 MySQL 옵티마이저는 const를 선택.
    • const 없이 range와 함께 사용되면 fulltext를 선택하게 됨.
    • 하지만 전문 검색 인덱스보다 range 접근 방법이 더 빠른 경우가 많음 (Real MySQL 저자 경험상).
    • 따라서 전문 검색 쿼리를 사용할 때는 조건별로 성능을 비교해 보는 것이 좋음.
  • unique_subquery
    • where 조건절에서 IN(Subquery) 형태.
  • range
    • <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산에서 사용.
  • index_merge
    • 2개 이상의 인덱스를 사용해 각각 조회 후 병합.
    • 여러 인덱스를 읽어야 해 일반적으로 range보다 효율이 떨어짐.
    • 전문 검색 인덱스를 사용하는 쿼리는 index_merge가 적용되지 않음.
    • index_merge의 결과는 항상 2개 이상의 집합이라 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요.
    • or을 사용하는 경우 사용될 수 있음.
  • index
    • 인덱스를 처음부터 끝까지 읽는 풀 스캔.
    • range는 필요한 부분만 읽는데 이건 다 읽음.
    • 풀 테이블 스캔보다는 빠르게 처리.
    • 인덱스의 정렬된 장점을 이용할 수 있음.
    • select * from A a order by a.name desc limit 10;
    • 위와 같은 쿼리에서 사용됨.
  • ALL
    • 풀 테이블 스캔.

기타

  • Partition 컬럼
    • 파티션 컬럼을 사용해서 컬럼의 데이터가 저장되는 공간을 물리적으로 나눌 수 있음.
    • 옵티마이저는 검색 시 어떤 파티션을 사용할지 알 수 있고, 이때 실행계획에 나머지 파티션에 어떻게 접근할지, 데이터 분포가 어떠한지 등의 분석을 실행하지 않음.
    • 물리적으로 분리되어 있기 때문에 Explain 시 type이 ALL이라고 발생해도, 즉 풀 테이블 스캔이어도 전체 데이터를 읽는 게 아님.