한 테이블의 인덱스 크기가 MySQL이 사용 가능한 메모리 공간보다 크면 쿼리의 처리가 느려짐
파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용할 수 있게 함
또한 데이터 파일이나 인덱스 파일의 물리적인 저장소를 분리해서 백업과 관리를 편하게 함
로그를 효율적으로 관리 가능 - 로그는 주로 일정 기간을 보관했다 삭제하곤 함 - 일일히 데이터를 백업하거나 삭제하는 것을 일반 테이블에서 하기에는 부하가 큼 - 단순히 파티션을 추가하거나 삭제 가능
파티션의 내부 처리 - insert
insert 쿼리가 실행되면 insert되는 칼럼의 값 중 파티션 키인 regdate 칼럼의 값을 이용해 레코드가 저장될 파티션을 결정
이 파티션이 결정되면 나머지 과정은 일반 테이블과 마찬가지
파티션의 내부 처리 - update
파티션 키 칼럼이 변경될 때는 기존의 파티션에서 해당 레코드를 삭제하고 새로운 파티션을 결정해서 레코드를 저장함
파티션 내부 검색
where절의 조건으로 검색해야 할 파티션을 선택할 수 있는지, 조건이 인덱스를 효율적으로 사용할 수 있는지에 따라 성능에 차이가 있음 - 상황1: 쿼리가 가장 효율적으로 처리됨. 검색을 해야 하는 파티션의 인덱스만 레인지 스캔 - 상황 2: 모든 파티션을 대상으로 인덱스 레인지 스캔 수행 - 상황 3: 대상 파티션 풀스캔 - 상황 4: 풀 테이블 스캔
파티션 테이블의 인덱스 스캔과 정렬
MySQL에서 인덱스는 모두 로컬 인덱스, 인덱스는 파티션 단위로 생성됨
그림에서 reg_userid 컬럼으로 인덱스가 만들어져있고, 이 값을 조회할 때 partition 은 년도별로 저장이 되어있어서 user_id로 정렬이 되어있지 않음, 별도의 정렬작업이 필요할 것 같아 보임
하지만 실제 수행될 때 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위 큐(priority queue)에 임시로 저장함. 이는 파티션에서 읽은 데이터가 이미 정렬되어 있는 상태라서 가능함
파티션 프루닝: 옵티마이저가 불필요한 파티션은 실행 계획에서 배제하는 것을 뜻함
파티션과 유니크 키
테이블에 UK가 있으면 파티션 키는 모든 UK의 일부 또는 모든 칼럼을 포함해야 함
나쁜 예 - 1: 파티션 키와 UK가 관련이 없음 - 2: 잘 모르겠음.. fd1로만으로 파티션 결정이 되지 않는다(?) - 3: fd1로만 파티션 결정을 할 수가 없음
좋은 예 - 1: fd1로 결정 가능 - 2: 나쁜 예 2 와 헷갈림. - 3: fd3으로 파티션 키 결정 가능
파티션 종류
range: 파티션 키의 연속된 범위로 파티션을 정의. 날짜를 기반으로 분석, 삭제. 범위 기준으로 균등하게 나눌 때
list: range 와 비슷하지만 파티션 키 값이 코드값처럼 고정적이고, 키 값이 연속되지 않을 때 사용
hash: 위의 두 방식을 사용하기 어려울 때 사용. 파티션을 추가하거나 삭제할 때 테이블 전체적으로 레코드를 재분배하는 작업이 따름
key: 해시와 비슷하지만 모듈러 연산도 MySQL 서버가 함
QnA🖐🏻
Q1. 해시/키 파티션을 사용하는 곳이 있나요? A1. 해시 파티션 관리시 키 계산과 재배치 작업으로 인해 비효율이 생겨 서비스에서 잘 사용하지 않음Q2. 파티션 생성, 삭제 작업시 점검을 걸고 하나요? A2. 추가 작업은 재배치가 없다면 (다음 년도의 파티션 추가 등) 그렇게 오래 걸리지 않지만, drop partition 등은 디비에 영향을 끼칠 수 있기 때문에 사용량이 적을 때에 진행함
11 스토어드 프로그램
프로시저
오라클처럼 프로시저가 있지만, 찾아보니까 오라클보다 성능이 많이 안좋다고 함
오라클과의 비교 - oracle은 stored program의 캐시가 전역 캐시에 관리되고, 여러 클라이언트가 참조함. - mysql은 클라이언트끼리 공유하지 않고 thread 단위로(connection 기반으로) 관리됨. - 그래서 mysql에서 thread를 사용할 때의 사이드이펙트는 메모리이다. - connection * procedure * procedure에서 사용하는 메모리 - alter 를 해서 스토어드 프로그램의 코드를 변경하면, 모든 커넥션에서 이를 재컴파일 해야 함
QnA🖐🏻
Q3. MySQL에서 프로시저를 많이 사용하나요? 지금까지 문제가 된 적 있나요? A3. 프로시저를 쓴다고 해서 성능향상이 크게 되지는 않기 때문에 되도록 쓰지 않도록 권유하지만, 어쩔 수 없는 경우 사용하기도 함Q4. Oracle to MySQL로 넘어갔을 때 프로시저에서 문제가 생긴 것이 있나요? A4. 오라클은 중첩 트랜잭션을 지원하기 때문에 start start commit rollback 이렇게 가능하지만 MySQL은 불가능함. 어플리케이션단에서 처리해야 함
12 쿼리 종류별 잠금
InnoDB 기본 잠금 방식
select는 select for update 구문이 아니라면 아무런 잠금을 사용하지 않음
insert, update, delete는 쓰기 잠금을 사용함, 필요시에는 읽기 잠금을 사용함.
auto commit이 활성화된 상태라 하더라도 잠금을 걸고 해제하는 작업은 진행함. 단, 자동으로 트랜잭션 시작과 종료가 발생하는 것
update와 delete를 할 때에는 조건에 일치하는 레코드를 찾기 위해 스캔하는 인덱스의 모든 레코드에 잠금을 건다. where 절에 일치하지 않는 레코드도 잠금의 대상이 될 수 있음을 의미함
이 예제에서는 대상에 맞는 인덱스가 없어서 불필요하게 성별이 f인 값들도 잠궈버린다. 따라서 어떤 인덱스를 사용하는지에 따라 큰 차이가 발생할 수 있음
select
기본 select는 별도의 잠금을 사용하지 않고, 다른 트랜잭션의 쿼리에 영향을 받지도 않음.
share mode 옵션을 사용한 select 문장은 검색을 위해 접근한 모든 레코드에 shared next key lock을 필요로 함. 이미 쓰기 잠금이 걸려 있다면 기다려야 하지만 읽기 잠금이 걸려있을 때에는 바로 읽기잠금 lock 획득 가능
select from update는 다른 트랜잭션이 잠금을 가지고 있다면 대기해야 한다.
잠금
배타적 잠금은 내가 쓰기를 하는 동안 남들이 쓰지 못하게 막는 것
공유 잠금은 내가 읽는 동안 남들이 내가 읽고 있는 데이터를 변경하거나 삭제하지 못하도록 막는 것
Insert 데드락 상황
순서대로 insert 쿼리가 실행된 후 commit이나 rollback이 실행되지 않으면 다음과 같은 상태 - 1에서는 pk가 1인 레코드에 대해 x lock을 갖고 있음 - 2,3은 slock을 획득하기 위해 대기. lock queue에 요청한 상태로 멈춰있음.
여기서 1 이 rollback을 하면 2, 3번은 pk가 1인 레코드에 대해 s lock을 검
그 다음 xlock을 걸게 되는데, xlock이 걸리지 않음. 둘다 s lock을 가진 상태에서 기다리고 있기 때문에 데드락 발생
실제로는 일정 시간이 초과하면 b,c 세션에서 lock wait timeout이 남.
insert
그래서 입력하려는 테이블에 UK나 PK가 있을 때에는 공유 레코드 잠금을 먼저 획득해서 중복 체크를 해야 함. insert를 전제로 한 읽기 작업 중에 다른 트랜잭션에서 레코드를 변경하거나 삭제하면 일관성이 깨지기 때문
insert는 추가적으로 insert intention lock이라는 방식도 사용함.
insert intention lock끼리는 호환되어서 여러 트랜잭션이 동시에 획득 가능 - 충돌하지 않는 값을 INSERT 한다면 간섭을 받지 않고 처리 가능.
insert into on duplicate key update: insert하려는 값에 중복된게 있는지 먼저 slock을 걸고 있으면 xlock 을 걸고 업데이트를 수행하며, 아니라면 insert intention lock을 걸고 실행. 새로 insert 된 레코드에 대해서 x lock을 획득
replace: 중복된 값이 있는지 확인하기 위해 slock을 검. 있으면 xlock을 걸고 삭제하고, 나머지는 마찬가지
insert into select: new 테이블에는 새로 입력되는 레코드에 대해 xlock 획득해야 함. tb_onld 테이블은 select 하기 때문에 shared next key lock을 가져야 함. 쿼리가 실행되는 동안 tb_old 테이블이 변하지 않도록 해주기 위해서.
replace into select: 읽어오는 테이블에는 공유 잠금이, 새로운건 xlock 이 걸림. 위와 같음
update 잠금
단순 update는 where 조건에 일치하는 레코드를 찾기 위해 참조한 레코드에 next key lock을 검. 다른 트랜잭션에 의해 처리 범위의 레코드가 영향을 받지 않게 하기 위해서.
join update는 여러 테이블이 동시에 사용됨. 최종 update 칼럼이 포함된 테이블은 x lock,이 , 참조용으로 사용되는 테이블은 shared next key lock이 걸림.
delete 잠금
update와 똑같이 배타적 넥스트 키 락을 검. 복제에서 마스터와 슬레이브의 동기화를 유지하기 위해 간격까지 잠금.
join delete: 실제 delete 되는건 배타적 넥스트 키 락, 단순 참조용은 공유 넥스트 키 락
QnA🖐🏻
Q5. create table select나 insert into select를 할 때 어떻게 처리하나요? A5. create as select를 하게 되면 인덱스는 생성되지 않기 때문에 차라리 인덱스를 포함해서 create table를 먼저 한 다음 insert into select로 나눠서 작업하는 것을 권장함. MySQL에서 lock이 걸리는 이유는 isolation level 때문이고, 이를 우회하려면 isolation leve을 커넥션에 따라 read-committed로 변경한 뒤 진행하면 lock이 걸리지 않고 oracle에서와 같이 작업 가능
15 데이터 타입
문자열
char와 varchar는 가변길이인지 고정길이인지의 차이
varchar는 최대 저장할 수 있는 값의 길이가 제한돼 있지만 그 이하 값이 저장하면 그만큼 저장공간이 줄어듬. 하지만 유효 크기가 얼마인지를 별도로 저장해야 하기 때문에 1~2바이트의 저장공간이 추가로 필요함
그래서 char(1) 와 varchar(1)을 비교했을 때 char는 딱 1바이트만 저장하지만 varchar는 1바이트+1바이트 총 2바이트를 저장함.
만약에 ABCD 에 ABCDE가 저장되면 char는 그냥 변경되는 값을 업데이트하면 되지만 varchar는 레코드 자체를 다른 공간으로 옮기거나 컬럼 나머지 부분을 다른 공간에 저장해야 함.
그래서 저장되는 문자열의 길이가 대개 비슷한가? 칼럼의 값이 변경되는가? 에 의해 값을 결정해야 함
캐릭터 셋
character_set_system: 테이블명이나 칼럼명을 저장할 때 사용함. 항상 utf8
character_set_server: 기본 문자집합. default값
character_set_database: db 생성시 명시되지 않았으면 default
character_Set_filesystem: 인자로 지정되는 파일의이름을 해석할 때 사용됨
character_set_client: 클라이언트로부터 전송된 쿼리 문장의 캐릭터셋. 이 값으로 인코딩해서 mysql으로 전송됨. 커넥션에서 임의로 변경 가능
character_set_connection: 클라이언트로부터 전달받은 sql 문장어르 처리하기 위해 이 값을 사용함. 클라이언트로부터 전달받은 숫자값을 문자열로 변환할 때도 이걸 사용함.
caracter_set_Results: 쿼리 결과를 클라이언트로 보낼 때 사용
숫자
exact value / 근사값으로 나뉨
값이 저장되는 포맷에 따라 십진 표기법(decimal)과 이진표기로 나뉨
부동소수점에서 유효 범위 이외의 값은 가변적이므로 금액이나 대출이자 등 고정된 소수점까지만 정확하게 관리해야 할 때에는 decimal을 사용. 소수점의 위치가 가변적이지 않은 고정 소수점 타입
날짜와 시간
date, datetime을 많이 씀
이건 클라이언트로부터 입력된 값을 그대로 저장하고 조외할 때도 변환 없이 출력
timestamp는 항상 UTC 타임존으로 저장됨, 달라져도 자동으로 보정됨
QnA🖐🏻
Q6. 캐릭터셋에서 가장 우선시되는 설정은? A6. 캐릭터셋이 다를 때 클라이언트에 따라 handshake를 통해 맞추는 경우도 있지만, 그냥 무시하는 경우도 있다. 서버에서 캐릭터셋을 변경할 때에는 character-set-server를 건드린다. 컬럼단위 캐릭터셋 변경도 가능하다
