MySQL 효율적인 인덱스 설계 방법이 궁금합니다.

Question

안녕하세요, MySQL 입문자 입니다.현재 재직 중인 회사의 규모가 많이 작아서 DB에 대해 조언 구할 사람도 없고.. 너무 답답해서 글 적어봅니다 ㅠ.ㅠ어드민 페이지 &#43; MySQL 로그 테이블을 개발 중인데요, 여러 가지 로그 데이터(로그인, 결제 등)를 어드민 페이지에서 다양한 조건으로 조회할 수 있게끔 개발해야 하는 상황입니다. 로그 테이블이라 수정할 일은 없으니, 조회 성능을 향상 시키고자 인덱스 설계를 하려고 하는데요. 현재는 아래 2개만 잡아놨습니다.AUTO_INCREMENT PRIMARY KEY (log_idx)INDEX idx_logtm_index_desc (log_tm DESC, log_idx DESC)우선 발생할 수 있는 조회 상황은 총 4가지 정도 됩니다.1. log_tm 날짜 범위만 조회 (필수)2. log_tm 날짜 범위 &#43; log_code 조합으로만 조회3. log_tm 날짜 범위 &#43; user_id 조합으로만 조회4. log_tm 날짜 범위 &#43; log_code &#43; user_id 모두 조회그리고 마지막으로 항상 log_tm 내림차순 > log_idx 내림차순으로 정렬 후, 페이징 처리를 위해 1,000건 단위로 잘라냅니다.아무래도 날짜 범위 조건이 필수이고, 나머지 log_code나 log_id는 옵셔널인데다가 마지막에 항상 log_tm과 log_idx로 정렬을 해야 하니, 도대체 인덱스를 어떻게 설계해야 할지 감이 안 오더라구요.. 나름 이렇게 저렇게 다 조합해봤는데 EXPLAIN 때려 보니 효율이 너무 별로고..ㅠㅠ이러한 상황에서 적절하고 효율적이게 인덱스를 설계하려면 도대체 어떻게 해야 될까요..? 그렇다고 모든 경우의 수를 인덱스로 잡는 건 아닌 것 같고. 실무에서는 보통 어느정도로 인덱스를 설계하는지 궁금합니다. 선배님들 조언좀 부탁드립니다..

2948284 · Answer

1. log_tm 날짜 범위만 조회 (필수)2. log_tm 날짜 범위 + log_code 조합으로만 조회3. log_tm 날짜 범위 + user_id 조합으로만 조회4. log_tm 날짜 범위 + log_code + user_id 모두 조회logtm+ logcode+ loguseridlogtm+ loguserid일케 두개 만들면 해걀되네여dbms에따라 1번인덱스 한개로 해결되기도 합니다.mysql의 경우 옛날엔(?) 인덱스 순번이 조회순서와 영향이 있어서 저렇게 두개 만들면 됬는데요1번 한개 만들어서 익스플레인 해보세요 요즘 버전은 순서 상관이 없을수도 있을거에요.

굿맨77 · Answer

다른 접근으로 log_tm 컬럼으로 테이블 파티셔닝을 하는 것도 고려해보시기 바랍니다.

르매 · Answer

그 내용에 대해 포스팅했던 글이 있는데 참고해 보세요.https://purumae.tistory.com/209

austar · Answer

로그 테이블에서 검색 조건이 항상 들어간다면 pk를 날짜를 조합한 테이블로 구성하고 그 외의 조건을 index를 거는것이 유효할 수 있습니다.현재 올려주신 정보로는 테이블 구조를 알 수 없으나CREATE TABLE `tbl_errorlog` ( `errorCode` varchar(30) NOT NULL COMMENT '에러 코드', `errorPoint` varchar(100) NOT NULL COMMENT '오류 위치', `errorNumber` varchar(100) NOT NULL COMMENT '오류 번호', `errorState` varchar(100) NOT NULL COMMENT '오류 상태', `errorText` varchar(200) NOT NULL COMMENT '오류 메시지', PRIMARY KEY (`errorCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;다음과 같은 로그 테이블이 존재할 때 pk인 에러코드를 YYYYMMDDHHIISS0001 와 같이 만드는겁니다.앞부분은 년월일시분초로 구성 하고 초뒤에 오는 숫자를 시퀀스를 이용해 LPAD해서 특정 자릿수로 채웁니다.만약 초당 들어오는 데이터가 만건이 넘는다면 시퀀스의 크기를 더 늘려주시면 됩니다.이렇게되면 로그 테이블의 날짜를 검색할때 기본적으로 pk를 이용한 검색이 가능합니다.검색 방법은 SELECT * FROM tbl_errorlog AS er WHERE er.errorCode BETWEEN CONCAT(:sRegDate, '0000') AND CONCAT(:eRegDate, '9999')다음과 같습니다.sRegDate, eRegDate 값은 년월일시분초 입니다.이렇게 검색을 한다면 pk 검색을 타게되므로 몇억건의 데이터도 엄청난 속도로 검색이 됩니다.인덱스 조건을 생각함에 앞서 pk 의 구성또한 사용 테이블의 유형에 맞춰서 변경해서 사용하심이 좋습니다.

MySQL 효율적인 인덱스 설계 방법이 궁금합니다.

4개의 답변

Q&A 목록