[태그:] mysql

[mysql] 인덱스 정리

인덱스란

  • 책의 목차와 같이 테이블 컬럼에 대한 인덱스를 생성해서 데이터를 검색속도를 향상 시키는것
  • 인덱스를 타면 풀스캔을 피할 수 있어서 데이터 검색 속도가 빠르다.

풀스캔

  • 인덱스가 없는 테이블에서 데이터를 찾을 때 처음부터 마지막 레코드까지 조회하여 검색 조건과 비교하게 된다.
  • 많은 양의 데이터를 조회 할 경우 풀스캔은 모둔 레코드를 조회하기 떄문에 성능이 느려지게 된다.

언제쓰면 좋을까

  • SELECT 쿼리에서 성능이 잘나오지만 INSERT, UPDATE, DELETE 쿼리에서는 때에 따라 다르다.
  • UPDATE, DELETE는 WHERE절에 잘 설정된 인덱스로 조건을 붙여주면 조회할 때 성능은 크게 저하 되지 않는다 (인덱스로 인해 데이터 조회에 속도가 빨라지는 것이고 데이터 수정 자체가 빨라지는 것이 아님)
  • INSERT의 경우, 새로운 데이터가 추가되면서 기존 인덱스 페이지에 저장되어 있던 탐색 위치가 수정되어야 하므로 효율이 좋지 않다.
  • WHERE 절에서 자주 사용되는 Column
  • 외래키가 사용되는 Column
  • join에 자주 사용되는 Column
  • ORDER BY, ORDER BY
  • 데이터양이 많은 테이블

인덱스 칼럼 기준

  • 카디널리티가 높은 것
  • 전체 행에 대한 특정칼럼의 중복 수치를 나타낸 지표로 복합인덱스인 경우 카디널리티가 높은 순에서 낮은순으로 구성하는게 좋다.

인덱스 사용방법

  • 생성
CREATRE INDEX 인덱스이름 ON 테이블이름(필드1, 필드2, ...)
  • 삭제
ALTER TABLE 테이블이름 DROP INDEX 인덱스이름
  • 실행계획
SHOW INDEX FROM 테이블 이름
구분설명
Table테이블 이름
Non_unique인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함
Key_name인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함
Seq_in_index인덱스에서의 해당 필드의 순서를 표시함
Column_name해당 필드의 이름을 표시함
Collation기본적인 정렬 형태, A오름 차순, NULL: 정렬구분 없음
Cardinality인덱스에서 저장된 유일한 값들의 수를 표시함
Sub_part인덱스 접두어를 표시함
Packed키가 압축되는 방법을 표시함
Null해당 필드가 Null을 저장 할 수 있으면 YES, 그렇지 않으면 ”를 표시함
Index_type인덱스에 사용되는 메소드를 표시함 인덱스 모드(BTREE, FULLTEXT, HASH, RTREE) / FULLTEXT 는 5.7 이상에서 유효
Comment해당 필드를 설명하는 것이 아닌 인덱스에 관한 정보를 표시함
Index_comment인덱스에 관란 모든 기타 정보를 표시함
실행계획 정보

유의사항

  • 최소한 첫번째 인덱스 조건은 조회조건에 포함되어야만 한다.
    • 인덱스 컬럼순서와 조회쿼리 컬럼 순서를 지킬필요는 없다.
  • 인덱스로 사용된 컬럼값 그대로 사용해야 인덱스가 사용된다.
    • 인덱스는 가공된 데이터를 저장하고 있지 않다.
  • AND 연산자는 검색 범위를 좁혀주지만 OR 연산자는 비교할 행이 더 늘기 때문에 풀 스캔이 발생할 확률이 높다.
  • 범위조건일때 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않는다.
  • null 값의 경우 is null 조건으로 인덱스 레인지 스캔 가능
  • LIKE 검색시 %가 앞에 위치하면 풀스캔 발생

참조

계층형 게시판 만들기

posts 테이블 구조

id아이디
subject제목
content내용
ori_id원글의 아이디값
grp_ord그룹(원글)내에서 순서
depth게시글의 깊이
  • 원글 : 최상위 글로 depth가 0이다.
  • 부모글 : 원글을 제외하고 자식을 갖는 글(depth 1이상이면서 자식을 갖는글), 첫번째 댓글(depth가 1)인 경우 부모글은 원글이 된다.
  • LAST_INSERT_ID() : 마지막으로 인서트된 id값
  • 최근에 작성한 글이 먼저 보이게 정렬한다.

게시글등록

  • 게시글을 인서트할때 반환되는 id값을 ori_id에 대입해준다.
  • grp_ord, depth는 원글이기 때문에 0을 준다.
INSERT INTO posts SET subject = '첫번째 글',
                     content = '',
                     grp_ord = 0,
                     depth = 0;
                     
UPDATE posts SET ori_id = (select last_insert_id()) WHERE id = (select last_insert_id());

댓글등록

UPDATE posts SET grp_ord = grp_order+1 WHERE ori_id = '부모글 ori_id' AND grp_ord > '부모글의 grp_ord';

INSERT INTO posts SET subject = '첫번째 글의 댓글',
                     content = '',
                     ori_id = '부모글의 ori_id',
                     grp_ord = '부모글의 grp_ord' + 1,
                     depth = '무보글의 depth' + 1;
  • 최근에 등록한 댓글이 상단에 위치하기 위해서 부모글의 grp_ord보다 큰 값을 찾아 1씩 증가시켜 순서를 뒤로 밀어준다.
  • 업데이트 문으로 숫자가 1씩 밀렸기 때문에 인서트할 때 grp_ord + 1를 줘서 댓글 순서를 처음으로 오게한다.

리스트쿼리

SELECT * FROM posts ORDER BY ori_id DESC, grp_ord

참고한 사이트

https://adgw.tistory.com/entry/계층형-게시판-알고리즘-댓글-알고리즘

[mysql] FIND_IN_SET()

필드에 string list로 저장된 a,b,c,d의 값중에서 b를 포함한 행을 찾으려면 어떻게 해야할까?

text_table

idtext
1a,b,c,d
2b,c,d,e
3c,d,e,f

위와같은 테이블이 있을때 b를 포함하는 행만 찾으려면 FIND_IN_SET 함수를 사용하면 된다.

'SELECT * FROM text_table WHERE FIND_IN_SET("b", text)';

결과

idtext
1a,b,c,d
2b,c,d,e

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set