데이터베이스 질문과 답변
안녕하세요~
어떤 이벤트가 발생한 것을 기록하는 로그성 데이터 저장 테이블이 있습니다.
해당 테이블에 데이터는 일정 기간 넘어갈 경우(eg, 6개월) 삭제는 하고 있습니다만,
그래도 쌓인 데이터가 많아서, 일시 기준으로 범위를 잡고 페이징 처리를 하다보니 쿼리가 점점 느리지고 있습니다.
<< 테이블 스키마 >>
log_data (
log_id BIGINT,
log_dt DATETIME,
log_type VARCHAR
)
<< 조회 쿼리 >>
SELECT log_id, log_time, log_type
FROM log_data
WHERE log_time >= from_unixtime(123)
AND log_time <= from_unixtime(456)
LIMIT 500, 250
(시간은 실제 epoch 타임인데, 위에서는 임의로 넣었습니다.)
인덱스로 잡을만한 필드도 없는 상황인데, 쿼리 속도를 향상 시킬 방법이 있을까요?
생각해 본 트릭으로는, 로그일시 필드에서 날짜만 저장하는 log_date 필드를 새로 추가하고 여기에 인덱스를 걸어둔 다음,
이 필드도 검색 조건에 같이 추가하면 어떨까 하는 것입니다.
<< 테이블 스키마 >>
log_data (
log_id BIGINT,
log_dt DATETIME,
log_date INT,
log_type VARCHAR
)
<< 조회 쿼리 >>
SELECT log_id, log_time, log_type
FROM log_data
WHERE log_date >= 100
AND log_date <= 500
AND log_time >= from_unixtime(123)
AND log_time <= from_unixtime(456)
LIMIT 300, 100
아니면 그냥 바로 log_dt 필드에 인덱스를 추가하는 것도 문제가 없을까요?
이외에 다른 좋은 방법이 있는지 궁금합니다.
미리 감사드립니다~
파티셔닝 기능 적용해보시는건 어떨까요?
오, 감사합니다. 파티셔닝은 처음 들어봤는데, 한번 조사해보겠습니다~!
MYSQL 파티셔닝은 어떤지 모르지만
파티셔닝 기능 사용시 관리목적이 더 강하고 잘못하면 배보다 배꼽이 클 수 있습니다.
설계시 경험자의 도움을 받는것이 좋을듯해보입니다.
@우리집아찌님 말씀 처럼, 제 상황에서는 지속적으로 만들고 삭제하고를 반복해야 할 것 같아 사용이 어려울 듯 싶습니다.ㅠ
mysql에서 리스트 파티션을 지원하면은 01~12 파티션만들면 관리가 더 편하시겁니다.
데이터량도 같이 올려주시면 좋을것 같아요.
로그테이블을 사용하는 다른 SQL 은 없나요?
넵, 지금은 서비스 초기이고, 지금은 3달치 보관 50만개 데이터인데 슬슬 느려질 기미가 보입니다.
동일 기간 보관을 하더라도 앞으로 데이터 발생 장치가 늘어나서,
적어도 몇백만 row 단위도 처리 가능하도록 고려가 필요한 상황입니다.
사용하는 SQL은, insert (빈번), select (insert 보다 적음), delete (새벽 배치 작업) 입니다.
조회용 select문은 2종류이기는 하나, 나머지 하나는 무시해도 될 정도로 사용량이 없어서, 고려하지 않아도 됩니다.
50만건은 많은 데이터가 아닌데요.
파티션은 고려대상이 아닐것 같고 인덱스만 잘 설계하면 될것같습니다.
넵, 답변 감사합니다~
다만 말씀하신 인덱스 설계를 어떻게 하는 것이 좋을지 감이 없네요..
현재 있는 필드에서는 인덱스를 걸만한 것이 없어 보이고,
본문에 적은 것과 같이 인덱스 걸만한 필드를 추가하는 것이 방법일까요?
log_dt 컬럼에는 어떤 이유로 인덱스 정의를 주저하시는지요..?
log_dt 필드 값은 datetime 값이기 때문에 거의 모든 값들이 고유한 값을 가지고 있습니다. 그래서,
- 인덱스 내부적으로 B-tree를 사용하므로, 매번 insert 시 마다 밸런싱이 일어나서 성능이 저하되는 것이 아닐까?
- 추가로, 디스크 공간을 더 차지하는 것이 아닐까?
인터넷 검색 등으로만 알아본 얕은 지식이기에, 혹여나 잘못된 정보이면 바로 잡아주시면 감사하겠습니다~
-----
질문을 주시고 나서 다시 잠시 생각을 해보니, 제가 갖고 있던 의문점은 충분히 테스트가 가능할 것 같네요ㅎㅎㅎ
다음 주에 바로 테스트를 해보아야 겠습니다.
그리고 또, 각 테이블마다 PK를 추가하고 있었는데 (여기서는 log_id), 생각해 보니 이 값 또한
sequencial하게 증가하는 unique 값이라 datetime 값과 동일/유사한 성격이라고 볼수 있을 것 같습니다.
질문 주신대로 PK에 인덱스 추가되는 것과, 제가 datetime필드에 인덱스 추가하는 것에 차이가 없는 것 같네요.
댓글 감사드립니다~
인덱스 밸런싱이 자동으로 매번 실행되는 것은 아닙니다만
인덱스의 존재가 insert 성능에 영향을 미치는 건 당연합니다.
조회 성능과 입력 성능과의 trade-off 득실을 따져봐야 하는 문제인 것 맞습니다.
인덱스로 인한 저장공간에 대한 염려는
아주 예외적인 상황을 제외하고는
예전에 디스크가 고가였던 시절에나 할 법한 걱정이 아닐까 싶네요.
) 버튼을 클릭하여 작성 하시면 됩니다.