데이터베이스 질문과 답변
안녕하세요.
이번에 새로운 기능을 추가하는 데 있어서 테이블 설계에 대해 고민을 하고 있습니다.
예를 들어서 설명드리면,
A라는 남성이 집에 도어락(비밀번호로 문을 여는 역할)을 설치하였습니다.
해당 도어락의 비밀번호는 매일매일 바뀌고, 어플리케이션 쪽에서 바뀐 비밀번호를 A남성에게 알림으로 알려주게 됩니다.
오늘 날짜가 2019-01-23 이라고 하면, 과거인 비밀번호 (2019-01-22)는 필요없게 됩니다. 로그로써 남기는 게 나을 거 같긴 합니다...
lock이 도어락 테이블이라고 치면, lock_password라는 테이블을 생성하고
lock_id(도어락 테이블 PK), today_date(오늘 날짜) 을 PK로 두고
password 칼럼을 두려고 합니다.
CREATE TABLE `lock_password` ( `lock_id` int(10) unsigned NOT NULL COMMENT '[lock ID]', `today_date` date NOT NULL COMMENT '[target date]', `password` varchar(10) NOT NULL COMMENT '[pw]', PRIMARY KEY (`lock_id`, `today_date`) ) ENGINE=InnoDB
위 테이블을 가지게 되면 과거의 데이터도 계속 가지게 되는 데... 좋은 방법이 없을까요?
###
생각해본 방법으로는
1. lock_password 에 새로운 날짜의 데이터가 들어오면 트리거를 이용해서 로그 테이블로 과거 데이터를 옮긴 뒤 인서트 한다.
(이 방법은 lock_password에 과거 데이터는 보이지 않지만, 내부적으로는 남아있기 때문에 디스크 사이즈를 위해 optimize를 실행해줘야 하는데 테이블 락이 걸린다)
2. lock_password 에 년마다 파티션을 걸어서 정기적으로 로그 테이블로 옮긴다
(이 방법 또한, 옮기고 난 후 내부적으로 남아 있기 때문에 디스크 사이즈가 줄지 않으므로 opmizite를 실행해줘야 한다...)
감사합니다!
1. 테이블 하나로 ORDER BY TO_DATE_DATE DESC LIMIT 1 해서 처리한다.
2. 테이블 두개로 하나는 오늘날짜를 제외한 데이터를 담는다 또는 오늘날짜 포함 담는다.
두번째 테이블는 현재날짜만 담는다. ( 날짜도 뺄수도 있네요 .. ^^)
3. 파티션은 추후의 문제고 쌓이는 데이터양도 알고 있어야합니다.
하나더.. 지난 패스워드는 전체 보관하지말고 5일치 정도만 보관하면 더 편할듯합니다.
답변 감사합니다.
2번에 관해서 질문 드리면, today_password , past_password가 생길 거 같은 데
이 경우 내일이 되면 오늘 날짜의 데이터를 past_password에 넣은 후 today_password 에서 삭제하면
내부적으로는 today_password에 남아있기 때문에 디스크 사이즈는 줄지 않습니다.
이 경우 optimize를 걸지 않으면 디스크 사이즈 확보 할 수 없는 데, 이걸 방지 할 수 있는 방법이 있을까요?
과거 패스워드는 다른 테이블로 쌓이는데 왜 관리하시나요?
성능이슈가 발생하시나요?
2번 처럼 쓰는 이유는 성능개선때문인데요.
하나의 테이블을 두개로 나눈것이지요 ( shapshot )
어짜피 지난 이력은 쓸일이 한정적(통계등..) 적이서 쓸일이 적습니다.
제가 말씀 드리고 싶은 점을 잘 전달 못해드린 거 같네요... 죄송합니다.
예를 들어서 설명드리면,
2018-01-23 비밀번호는 today_password에 들어가 있습니다.
내일(1월 24일)이 되면 today_password 에 들어있는 2018-01-23 비밀번호는 past_password로 이동하겠죠?
이동 시킨 뒤 today_password에 저장되어 있던 2018-01-23 비밀번호를 삭제합니다.
검색 시 today_password에 2018-01-23 비밀번호는 보이지 않지만 내부적으로 아직 존재합니다.
그러므로 디스크 사이즈 또한 줄어들지 않습니다...
제가 이해안되는데 PAST_PASSWORD가 컬럼인가요?
컬럼이 있다면 왜 있나요?
다른테이블에 ROW 단위로 쌓이는데요?
이력이 계속 쌓이면 테이블 사이즈가 당연이 증가하겠죠?
마지막으로 왜 이력이 필요한가요?
패스워드는 암호화 되지 않습니다.
이력은 만일의 상황에 대비해서 남겨둡니다.
PAST_PASSWORD는 테이블 입니다.
과거 테이블은 시간이 갈 수록 사이즈가 늘어나는 건 당연합니다.
제가 말씀드리고 있는 건 today_password 테이블 입니다.
past_password로 이동되는 데이터는 무조건 today_password에 있었던 거겠죠?
today_password에 데이터를 지우지만 실제론 데이터 파일 페이지 내부에서 플래그가 1로 바뀌는 것 뿐입니다.
고로 디스크 사이즈 x2가 됩니다.
today_password에 데이터를 지우지만 실제론 데이터 파일 페이지 내부에서 플래그가 1로 바뀌는 것 뿐입니다.
고로 디스크 사이즈 x2가 됩니다.
-- today_password 에 LOCK_ID만 PK로 하시고 관리하시는데 디스크 사이즈가 2배가 된다는 말씀이신가요?
제가 생각하는 거랑 댓글자분이 생각하시는 스키마가 다른 것 같은 데...
어떻게 생각하고 계신지 알려주시면 얘기가 더 스무스하게 진행될 것 같네요.
삭제시 플래그가 1로 바뀐다는게???
- 1. 실제로 삭제를 안하고 플래그만 업데이트 한다는 건가요?
- 2. 아니면 삭제해도 사용하던 공간은 그대로 남아 있다는 것을 표현한 건가요?
1번의 경우라면?
- 굳이 로그 테이블을 둘 필요가 없어 보이구요.
- 그래서 이걸 말하는 건 아닌 것 같네요.
2번의 경우라면?
- 삭제된 공간은 어차피 다시 재사용 되게 됩니다.
- 아니면 아예 삭제 후 인서트 하지 마시고, 바로 업데이트 하는 방식을 사용해 보세요.
답변 감사합니다.
2번 입니다.
이노 디비의 경우 삭제된 공간이 재사용 되는 경우는 PK가 동일한 데이터가 들어왔을 때 입니다.
저희 서비스의 기능은 위에 경우에 해당하지 않네요...ㅠ
삭제 후 저장이 아니라, 업데이트를 한다 -> 좋은 방법이라고 생각합니다!
다만, 글로벌 서비스라고 한다면 시차가 있어서 조금... 복잡해 질 것 같기도 하네요.
접근방법이 다른 방법인데요.
해시함수 같은 것을 이용한 펑션을 만들면 로그를 남길 필요가 없을 것 같은데요.
사용자별로 유니크한 KEY를 할당하고, 해당 사용자 KEY + 일자를 입력값으로 하는 해시함수를 만드는 겁니다.
그러면 데이터를 따로 저장할 필요도 없고 과거 이력도 조회해 볼 수 있으니 좋을 것 같은데요.
예) 사용자 A : KEY001, 사용자 B : KEY002
사용자 A의 2019-01-23일 패스워드를 조회시
ㅇ 입력값 : KEY00120190123
ㅇ 출력값 : 해시값
사용자 A의 2019-01-22일 패스워드를 조회시
ㅇ 입력값 : KEY00120190122
ㅇ 출력값 : 해시값
답변 감사합니다.
해당 설계는 참조 제약을 걸 수 없어서 힘들 것 같네요...
) 버튼을 클릭하여 작성 하시면 됩니다.