어제 RDBMS의 PK에 대한 대화를 좀 나누어 보았습니다.
—-------—-------—-------—-------—-------—-------—-------—-------
[ONE1] [오후 8:58] 그리고 클러스터드 인덱스로 순차적이지 않은 값을 주어도 느리지가 않은데 이것은 하드디스크가 아닌 파일시스템에 순차적으로 쓰인다는 의미이고 실제 비트리 같은 자료구조에서 삽입시 순서를 관리 및 정리하는 비용은 크지 않을것 같습니다.
[ONE1] [오후 8:59] 이런 생각을 했는데 기본키는 왜 클러스터드 일까요
[ONE2] [오후 9:43] 무슨말인지 잘 모르겠어요
[ONE2] [오후 9:44] "순차적이지 않은 값을 주어도 느리지가 않은데" -> 이걸 측정해보셨다는건가요?
[ONE1] [오후 9:44] 아니오 저걸로 서비스를 못하지 않은 것 같아서요.
[ONE1] [오후 9:45] 저렇게 해도 서비스 잘 하니까요.
[ONE2] [오후 9:46] mysql 에선 기본키가 클러스터드인게 강제사힝이고 mssql 에선 아니에요
[ONE1] [오후 9:49] 아 그런가요 mssql에선 그냥 테이블 만들고 pk만들면 그게 클러스터드 아녜요?
[ONE2] [오후 9:50] 그럴걸요
[ONE1] [오후 9:50] 그러니까요.
[ONE1] [오후 9:50] 기본키는 클러스트가 default인게 왜 그럴까 싶은거죠.
[ONE2] [오후 9:50] 대신 non-primary 인 clustered index 를 생성할수 있음
[ONE2] [오후 9:51] 기본키 말고 다른 선택지가 어떤게 있죠?
[ONE1] [오후 9:51] 기본키를 non-clustered가 default일 수도 있지 않나 해서.
[ONE1] [오후 9:51] 왜 굳이 개발자에서 clustered를 default로 했을까 의문이 드네요.
[ONE2] [오후 9:52] 기본키를 클러스터드 인덱스로 삼든 다른걸 클러스터드 인덱스로 삼든
[ONE2] [오후 9:52] 클러스터드 인덱스는 무조건 존재해야하잖아요
[ONE2] [오후 9:52] 아님?
[ONE1] [오후 9:54] 클러스터드 인덱스가 무조건 존재해야하는 건가요?
[ONE1] [오후 9:54] heap이란 타입이있던데
[ONE1] [오후 9:54] 거긴 아니던데요
[ONE2] [오후 9:55] 클러스터드인덱스가 뭔가요?
[ONE1] [오후 9:58] clustered index
[ONE2] [오후 9:58] 그게 무슨의미인지 알고있냐는거죠
[ONE1] [오후 9:59] 파일 시스템에 순차적으로 저장되는 데이터요.
[ONE2] [오후 9:59] DB에 테이블을 만들면 파일시스템에 저장될거아니에요
[ONE1] [오후 9:59] 그렇죠.
[ONE2] [오후 9:59] 어떤 순서로든 저장이 될텐데 그 순서가 클러스터드인덱스인거죠
[ONE1] [오후 10:00] 네. 순서대로.
[ONE1] [오후 10:00] 근데 heap이란 자료구조에는 클러스터드 인덱스를 지정하지 않아도 되던데
[ONE1] [오후 10:00] 실제 데이터도 순서대로 저장되지 않아요
[ONE2] [오후 10:00] 힙이란 자료구조는 원래 순서대로 저장되는 자료구조가 아니니까요
[ONE1] [오후 10:01] 네. 그게 mssql에도 있다는거죠
[ONE1] [오후 10:01] 테이블 관리시에
[ONE1] [오후 10:01] non-clustered인덱스로만 구성된 테이블.
[ONE2] [오후 10:06] heap 테이블은 안써봐서 잘 모름
[ONE1] [오후 10:06] clusterd index가 일반적인게 맞는거 같네요 대화하다보니까 기본키를 tree구조로 관리할테니까요.
[ONE1] [오후 10:07] tree구조가 필요없으면 clusterd index굳이 없어도 되고
[ONE2] [오후 10:07] 기본키의 용도는 레코드를 가리키는 용도인데
[ONE2] [오후 10:08] 해당 테이블에 다른 인덱스가 생성되려면 기본키가 있어야하지않나
[ONE2] [오후 10:08] 인덱스는 보통 인덱스에 해당하는 컬럼 튜플 + 기본키 형태로 구성된 btree 니까
[ONE1] [오후 10:08] 대신 RID인가 그게 할당된다는데
[ONE2] [오후 10:08] 근데 힙 같은 경우는 순차 접근밖에 안되는 구조면 인덱스는 어떻게만들어지죠?
[ONE2] [오후 10:09] RID는 btree 가 아니고 다른 자료구조인건가요?
[ONE1] [오후 10:09] 그거까지는 문서에서 못본거같아요.
[ONE1] [오후 10:10] 아마도 hash구조로도 index를 만들 수 있지 않나요?
[ONE2] [오후 10:11] 해시 인덱스가 있긴한데
[ONE2] [오후 10:11] pk 로도 쓸수있나
[ONE2] [오후 10:11] 암튼 클러스터드 인덱스로는 못쓸거같은데요
[ONE1] [오후 10:11] pk가 없는 테이블인데 heap은.
[ONE1] [오후 10:12] 맞을걸요 잠시만. 찾아봐야해요.
[ONE2] [오후 10:12] 그렇군요
[ONE2] [오후 10:12] 그냥 순서상관없이 아무데나 삽입하고
[ONE1] [오후 10:12] https://learn.microsoft.com/ko-kr/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-ver16
[ONE2] [오후 10:12] RID 라는 해시 인덱스로 heap 을 만드나보네요
[ONE1] [오후 10:13] FileID:PageID:SlotID로 해시 만드는듯
[ONE2] [오후 10:13] 그렇겠네요
[ONE1] [오후 10:14] 그러면 프라이머리 키가 Non-ClusteredIndex라고 해도 Clustered-Index가 내부적으로 존재한다는 것이군요.
[ONE1] [오후 10:14] PK가 존재한다면 말이죠.
[ONE2] [오후 10:15] mssql 은 잘 모르겠고
[ONE2] [오후 10:15] mysql 은 프라이머리 키를 지정 안해주면 자동으로 생성해줘요
[ONE1] [오후 10:15] mysql은 clusterd, non-cluseterd이런 옵션이 없던데요
[ONE1] [오후 10:15] 그냥 key 라는 옵션으로 넣어줬던 기억이 나네요.
[ONE1] [오후 10:16] key 를 내부적으로 어떤 자료구조로 쓸지 정해줄 수도 있고.
[ONE2] [오후 10:18] key 는 index 고
[ONE2] [오후 10:18] primary key 가 암시적으로 클러스터드인덱스임
[ONE1] [오후 10:19] key를 뭐 어떤 자료구조로 관리하든 상관이 없겠죠.
[ONE1] [오후 10:19] primary key는 mssql에선 non-clustered로 사용하면 내부적으로는 clustered-index를 사용하는 걸까요?
[ONE1] [오후 10:20] 어차피 sql들이 서로 지원하는 feature는 서로 다르니까요. 그게 불가능한지 가능한지와 다르게.
[ONE2] [오후 10:20] mssql 은 제가알기로 클러스터키가 최소 한개 최대 한개 존재해야한다고 알고있었음
[ONE1] [오후 10:22] 그러면 rdbms는 모두 테이블에 clusterd-index를 가진다고 봐야할까요?
[ONE1] [오후 10:22] pk를 갖는한?
[ONE2] [오후 10:23] 뭐 대부분 비슷하지않을까요
[ONE2] [오후 10:24] 색다른 방식으로 구현된 rdbms 가 있을수도 있으니 100% 라고는 말 못하겠네요
[ONE1] [오후 10:24] pk의 어떤 특성때문에 clustered-index가 필요한 걸까요?
[ONE1] [오후 10:24] 100%가진 아니어도.
[ONE2] [오후 10:25] 정렬되어있으면 유리한 특성때문이겠죠
[ONE2] [오후 10:25] 클러스터도 인덱스가 가까운 레코드끼리는 같은 페이지에 있을 가능성이 높아서
[ONE1] [오후 10:26] 그럼 정렬되어 있지 않아도 되면
[ONE1] [오후 10:26] non-clusterdindex만 있어도 되잖아요.
[ONE2] [오후 10:26] 네 그러면 아까 [ONE1]가 말한 file:page:slot 같은걸로 RID 만드는 방식이 되는거 같네요
[ONE1] [오후 10:27] 넵 그걸로도 pk를 만들 수 있을거같아서용.
[ONE1] [오후 10:27] 그리고 정렬되면 제일 유리한게 제 생각에는
[ONE1] [오후 10:28] auto inc보다
[ONE1] [오후 10:28] datetime인데
[ONE1] [오후 10:28] 이걸 clusterd-index로 하는 경우는 거의 없으니까요
[ONE2] [오후 10:28] datetime 은 unique 제약조건을 만족할수가 없지않나
[ONE1] [오후 10:28] 네 그래서 그런듯
[ONE2] [오후 10:28] 그걸 pk 로 쓰려면 뒤에 auto inc 같은 컬럼 하나 붙여야되니까
[ONE2] [오후 10:28] 개손해죠
[ONE1] [오후 10:29] 그런데 정렬된 자료구조가 정말 필요하다고 생각이 들어서. auto inc나 character id같은걸 clusterd-index로 써서 그다지 내 근처 레코드가 필요한 경우가 없었던 거 같아요.
[ONE2] [오후 10:30] 인벤토리같은 경우에
[ONE2] [오후 10:30] 특정 플레이어 소유의 아이템 한꺼번에 select 하지 않아요?
[ONE2] [오후 10:30] pk 에 플레이어 id 가 들어있으면
[ONE2] [오후 10:30] 그만큼 io가 줄어드는건데
[ONE1] [오후 10:30] 플레이어 id 컬럼 따로 item id 컬럼 따로 가지고 있죠 보통
[ONE1] [오후 10:31] item id가 pk일거고요 inventory table이라면.
[ONE2] [오후 10:31] 왜그렇게 만들어요?
[ONE1] [오후 10:31] 그렇게 만들면 잘못된거에요? 저는 그렇게 만든것들만 본거같아서.
[ONE2] [오후 10:32] 아뇨 잘못됐다는건 아니고
[ONE2] [오후 10:32] 그렇게 만드는 이유가 있을테니까
[ONE1] [오후 10:32] 모르겠는데 들어가보니까 그렇게 만들어놨더라고요
[ONE1] [오후 10:32] [ONE2]은 어떻게 만드시는데요 알려주시면 그런 방식으로 만드는 분들이 더 많은지
[ONE1] [오후 10:32] 고민해 볼게요
[ONE2] [오후 10:32] 뭐 튜닝 단계에서 고민해야할거같긴 한데
[ONE2] [오후 10:32] 읽고 쓰는 패턴이
[ONE2] [오후 10:33] 플레이어 테이블 같은 경우엔 플레이어 id 하나에 대해서 읽기/쓰기가 발생하는게 거의 대부분이고
[ONE2] [오후 10:33] 인벤토리 테이블 같은 경우엔 여러 레코드에 걸쳐서(player 컬럼이 같은) 읽기/쓰기가 발생하는 비율이 높아서
[ONE2] [오후 10:34] 그걸 보고 clustered index 를 결정하면 유리하겠죠
[ONE2] [오후 10:34] select 나 update 비율이 낮고 insert 만 주로 발생하는 테이블(주로 로그)은 auto inc 컬럼을 pk 로 삼는게 유리하고요
[ONE1] [오후 10:35] 음 그냥 골고루 일어나는거같은데
[ONE1] [오후 10:35] 대부분
[ONE1] [오후 10:35] select, udpate, insert 골구로 일어나죠
[ONE1] [오후 10:35] 뭐 하나만 주로 일어나는게 특별한 경우인거 같은데요.
[ONE2] [오후 10:35] 로그는 insert 만 일어나는건 맞지않아요?
[ONE1] [오후 10:36] 특별한 경우죠. 일단 인벤토리 테이블 pk를 어떻게 구성할지 알려줘 보세요 고민 좀 해볼께요.
[ONE2] [오후 10:36] ? 방금 얘기해준건데
[ONE1] [오후 10:37] 예시로 들어주시면 편리할듯 item_id , player_id bit섞는다던가
[ONE1] [오후 10:37] 실제 숫자를 넣어서
[ONE2] [오후 10:37] bit 를 왜섞음
[ONE1] [오후 10:37] 그러니까 예시를 숫자로.
[ONE1] [오후 10:38] 음.
[ONE1] [오후 10:38] 이 경우를 말씀하신듯
[ONE1] [오후 10:38] 그...
[ONE1] [오후 10:38] 멀티 컬럼 index
[ONE2] [오후 10:38] 네
[ONE2] [오후 10:38] 인벤토리 id 는 따로 있는거고
[ONE2] [오후 10:38] 그 왼쪽에 player_id 를 넣는다는거죠
[ONE2] [오후 10:39] (player_id, item_id) 튜플로
[ONE1] [오후 10:39] select시에는 빠르게 select되지만 insert, delete시에는 정렬이 일어나지 않을까요.
[ONE1] [오후 10:40] 그리고 날짜를 non-clusterdindex로 해서도 서비스가 잘되는데
[ONE2] [오후 10:40] 항상 정렬이 유지되는 자료구조인데 당연히 정렬이 일어나죠
[ONE1] [오후 10:40] 정렬된 데이터가 아닌 non-clusterd index여도
[ONE1] [오후 10:40] select잘될거같아요. 성능 차이 미비할듯.
[ONE1] [오후 10:40] insert, delete시에 빠르고
[ONE2] [오후 10:42] 뭐 게임마다 다르겠죠.. 같은 플레이어 소유의 아이템이 비슷하게 select/update 될 확률이 높은 게임이라면 성능향상은 확실히 있겠죠
[ONE1] [오후 10:42] 저는 그냥 뭘로 가든 서비스 하는데 지장일 정도가 아닌거 같아서.
[ONE1] [오후 10:43] 차라리 샤딩을 어떻게 할지 고민하는게 더 좋을듯
[ONE1] [오후 10:44] 제가 의문이 드는게 그런 부분이어서.
[ONE1] [오후 10:44] clusterd-index가 pk의 default인건 그냥 관례 같아 보여요.
[ONE2] [오후 10:45] 그냥 아는만큼 보이는거겠죠