mysql 스토리지 엔진, 인덱스 관련해서 현실적으로 어떤 방법이 나은 건가요?

Question

InnoDB은 테이블에 내림차순 인덱스를 설정할 수 있지만 복합키로 설계했을때는 제대로 auto_increment가 동작하지 않거나 생성이 안되는데요예를 들어, create table test1(a int, b int auto_increment, primary key(a,b)); 이렇게 테이블을 생성하려고 하면17:19:54 create table test1( int, b int auto_increment, primary key(a,b)) Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key 0.000 sec이런 오류 메시지를 띄우면서 생성이 안되고 primary key의 순서를 바꿔주면 생성은 되는데 의도한대로 (1,1),(1,2),(2,1),(2,2)...이런 식으로 증가하는게 아니라 (1,1),(1,2),(1,3),(1,4) 이렇게 증가해서 그냥 테이블 생성할 때 제약조건으로 넣는 방법은 안되겠고DELIMITER $$ CREATE TRIGGER xxx BEFORE INSERT ON issue_log FOR EACH ROW BEGIN SET NEW.sr_no &#61; ( SELECT IFNULL(MAX(sr_no), 0) &#43; 1 FROM issue_log WHERE app_id &#61; NEW.app_id AND test_id &#61; NEW.test_id ); END $$ DELIMITER ;구글링을 해보니 InnoDB 엔진에서는 이렇게 트리거나 혹은 프로시저를 사용해야 되는 것 같네요 MyISAM에서는 복합키로 생성해서 (1,1),(1,2),(2,1),(2,2)...이런 식으로 값이 증가하는 테이블을 만들 수 있지만 내림차순 정렬 인덱스가 생성이 안되는 문제가 있고요 제가 내림차순 정렬을 사용하려는 곳은 게시판을 만들때 게시글(게시판 번호, 게시글 번호) 이런 식으로 된 테이블을 게시판 번호 asc, 게시글 번호 desc 이렇게 인덱스를 사용해서 역순으로 가져와서 최신순으로 보여지게 하려는거고요 요약하자면 InnoDB은 내림차순 정렬 인덱스를 사용할 수 있지만 복합키를 기본키로 갖는 테이블을 만들때 auto_increment 제약조건을 제대로 사용할 수 없어서 트리거 혹은 프로시저를 사용해야하고 MyISAM은 복합키 중 일부 컬럼에 auto_increment 제약조건을 넣고 테이블을 생성할 수 있지만 내림차순 정렬 인덱스를 사용할 수 없기 때문에 조회를 할 때마다 정렬을 해줘야 된다는 문제가 있는 것 같습니다 어느 방법이 현실적으로 나은 건가요?

캐티 · Accepted Answer

이런 부분은 프로시저를 사용했스빈다.그리고 DB에 인덱스는 1부터 시작 하빈다. 이유는 NULLABLE 때문일것 같쩌여. NULL 논리 벨류를 사용 하지 않으면 자료구조가 간단해지빈다. 저용량 고효율.처음부터 비어있는 테이블이면 IFNULL 을 써야하지만 처음 데이터를 생성할 때 초기화 가능한 것이면 1 new record 처럼 로그를 남겨줄 수 있스빈다. 그면 IFNULL 은 빼도 작동하는데 이상이 없쩌.이케이케 잘 따져서 쿼리를 단순하게 만드는 것이 아주 중요하빈다.내림차순 정렬을 하려면 PK 는 INT 나 BIGINT 그대로 두어야 할 것 같스빈다. unique key에 두개 칼럼을 나누고 PK 를 기준으로 정렬 해야하겠쩌. PK는 어찌되었건 단순한 자료구조여야하빈다.

장독깨기 · Answer

굳이 복합 키로 할 필요가 있을까요.저 같으면 게시판 번호는 pk 에서 빼고 외부 키로 연결 해 두겠습니다.

Iskra · Answer

캐티 답변해주셔서 감사합니다

Iskra · Answer

장독깨기 전에는 게시물을 다른 게시판으로 옮길 수도 있어야 되지 않나 생각해서 게시물 번호만 기본키로 하고, 게시판 번호는 그냥 외래키로만 두는 구조를 저도 생각했었는데요 게시판 종류가 정말 많다면 기본키 컬럼의 값이 너무 비대해지는 문제가 일단 있기도 하고, 그리고 새 게시판을 추가했을 때 번호를 새로 시작해야 하는게 맞는 것 같은데 게시판 번호만 기본키로 사용했을 때는 그게 안될듯해서 복합키로 쓰려고요...

무명시민 · Answer

CREATE TABLE IF NOT EXISTS &#96;tb_xxxxx&#96;( &#96;BLBM_NO_VAL&#96; VARCHAR(10) NOT NULL COMMENT '게시글번호값' , &#96;REPL_SQNO&#96; BIGINT NOT NULL AUTO_INCREMENT COMMENT '답변순번' , PRIMARY KEY (&#96;BLBM_NO_VAL&#96;, &#96;REPL_SQNO&#96;) , KEY &#96;ix_TBMPD002_01&#96; (&#96;REPL_SQNO&#96;)) ENGINE &#61; InnoDB COMMENT &#61; '답변목록' ; AUTO_INCREMENT 사용하는 컬럼으로만 일반인덱스를 별도로 위와 같이 KEY &#96;ix_TBMPD002_01&#96; (&#96;REPL_SQNO&#96;) 만들고 PK 를 원하시는 컬럼 순서대로 위와 같이 PRIMARY KEY (&#96;BLBM_NO_VAL&#96;, &#96;REPL_SQNO&#96;) 만드시면 됩니다.

mysql 스토리지 엔진, 인덱스 관련해서 현실적으로 어떤 방법이 나은 건가요?

5개의 답변

#innodb 목록