인덱스

Park sang woo·2024년 7월 30일
Index
2

CS스터디

목록 보기
11/25

📘 인덱스

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조입니다.
데이터의 저장(INSERT, UPDATE, DELETE)의 성능을 희생합니다. 대신에 데이터의 읽기 속도를 높이는 테이블의 동작속도(조회)를 높여주는 자료구조입니다.

인덱스에 대해 정리를 했지만 트리 동작과 인덱스 쿼리를 작성과 동작 방법을 명확히 작성하지 않아 추가 작성.
🖇️ 인덱스와 알고리즘


ex) '홍길동'이라는 단어를 찾는다고 한다면 색인페이지에서 '홍'으로 시작하거나 'ㅎ'으로 시작하는 색인을 찾아보면 빠르게 찾을 수 있습니다. (영어도 동일)



🎧 Hash 인덱스 알고리즘

컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘입니다. (매우 빠른 검색을 지원.)

하지만 값을 변형해서 인덱싱하므로, 해시 인덱스는 동등 비교 검색에는 최적화돼 있지만 범위를 검색한다거나 정렬된 결과를 가져오는 목적으로는 사용할 수 없습니다.

주로 인메모리 DB에서 사용하는 인덱스 종류.
인메모리 DB란 : 메모리가 디스크 스토리지의 메인 메모리에 설치되어 운영되는 DB입니다.

+ 해시 함수의 결과만을 저장하므로 키 컬럼의 값이 아무리 길어도 실제 해시 인덱스에 저장되는 값은 4~8 바이트 수준으로 상당히 줄어듭니다.
그래서 타 인덱스 대비 조회 속도가 매우 빠릅니다.

  • 실제 키값과는 관계없이 인덱스 크기가 작고 검색이 빠르고 원래의 키값을 저장하는 것이 아님.
  • 정리 : DB에서 특정 데이터의 위치를 빠르게 찾기 위해 해시 함수를 사용해서 키 값을 고정된 크기의 해시 값으로 변환해서 데이터의 크기를 줄일 수 있음.

- 각 해시값에 주소값을 배정하는 인덱스의 특징에 따라 범위로 조회하는 작업은 작업은 느립니다.
- 범위로 묶어서 보관하는 인덱스가 아니므로 데이터 개수가 증가함에 따라 범위로 묶어서 보관하는 인덱스보다 더 큰 저장공간을 필요로 합니다.



🎧 Fractal-Tree 알고리즘 (TokuDB)

B-Tree의 단점을 보완하기 위해 고안된 알고리즘입니다.
값을 변형하지 않고 인덱싱하며 범용적인 목적으로 상용할 수 있다는 측면에서 B-Tree와 거의 비슷하지만 데이터가 저장되거나 삭제될 때 처리 비용을 상당히 줄일 수 있게 설계된 것이 특징입니다.





📘 인덱스 타입 종류





📘 인덱스 설계 핵심

언제 어느 때에 인덱스를 사용해야 효율적으로 사용할 수 있는지 파악이 필요합니다. (나에게 정말 필요한 것.)


🎧 효율적인 인덱스 설계

1️⃣ WHERE 절에 사용되는 열. (WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있다.)
2️⃣ SELECT 절에 자주 등장하는 컬럼들을 잘 조합해서 INDEX로 만들어두면 INDEX 조회 후 다시 데이터에서 조회할 필요가 없으므로 빠르게 검색이 가능.
3️⃣ JOIN 절에 자주 사용되는 열에는 인덱스의 효율이 좋음.

4️⃣ ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬됐기 때문에 클러스터형 인덱스가 유리.



🎧 금지해야 할 인덱스 설계

1️⃣ 대용량 데이터가 자주 입력되는 경우.
2️⃣ 데이터 중복도가 높은 열은 인덱스 효과가 없습니다. (예를 들어 성별 열에 M, F만 있다고 하면 인덱스를 안쓰는 게 낫다.)
3️⃣ 자주 사용되지 않으면 성능 저하를 초래할 수 있습니다. (INSERT만 주구장창 하는 시스템이라면, 사용해보지도 못하고 데이터 입력에 걸리는 작업량만 많아진다)



🎧 인덱스 사용 시 주의할 점

1️⃣ 데이터 변경(변경, 수정, 삭제) 작업이 얼마나 자주 일어나는지 고려.

2️⃣ 단일 테이블에 인덱스가 많으면 속도가 느려짐.(테이블 당 4~5개 권장)

3️⃣ 검색할 데이터가 전체 데이터의 20% 이상이라면, MySQL에서 인덱스를 사용 X. (강제 사용 시 성능 저하)

  • 전체 페이지의 대부분을 읽어야 하고, 인덱스 관련 페이지도 읽어야 해서 작업량이 크기 때문.

4️⃣ 사용하지 않는 인덱스는 제거하는 것이 바람직.

5️⃣ 클러스터형 인덱스는 테이블당 하나만 생성 가능.

6️⃣ 테이블에 클러스터형 인덱스가 아예 없는 것이 좋은 경우도 있음.



🎧 인덱스가 안 되는 쿼리

1️⃣ 인덱스 입힌 컬럼을 가공

  • WHERE SUBSTR(컬럼명, 1, 4) = '2024'
  • 해결 -> WHERE 컬럼명 LIKE '2024%'
  • 이유 : SUBSTR과 같은 함수는 인덱스의 값을 변형시키기 때문. '%'는 2024로 시작하는 모든 값을 빠르게 찾을 수 있게 해주기 때문에 값을 빠르게 찾을 수 있게 해줌.

2️⃣ 인덱스가 있는 열 이름에는 함수나 연산을 가함.

  • WHERE count*10 = 100
  • 해결 -> WHERE count=100/10
  • 이유

3️⃣ 인덱스 컬럼의 묵시적 형변환 (같은 타입으로 비교해야 함.)

  • WHERE 컬럼명 = '20240730'
  • 해결 -> WHERE 컬럼명 = TO_DATE('20240730', 'YYYYMMDD')

4️⃣ 인덱스 컬럼 부정형 비교

  • WHERE 컬럼명 != '10'
  • 해결 -> WHERE 컬럼명 IN('20', '30')

5️⃣ LIKE % 가 앞에 위치.

  • WHERE 컬럼명 LIKE '%2024'
  • 해결 -> or 조건 사용 -> WHERE 컬럼명 IN('2024', '202024', '302019')


ORDER BY 와 GROUP BY에 대한 인덱스
INDEX는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 INDEX를 타지 않는다.

ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해서 ORDER BY를 사용한 경우
WHERE 컬럼1='값' ORDER BY 인덱스 컬럼 : 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC : DESC와 ASC를 혼합해서 사용한 경우
GROUP BY 컬럼1 ORDER BY 컬럼2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우
ORDER BY ABS(컬럼) : ORDER BY 절에 다른 표현을 사용한 경우





📘 인덱스 컬럼 정리

🎧 테이블에 등록된 인덱스 확인

MySQL

SHOW INDEX FROM [테이블 이름];

현재 사이드 프로젝트 중인 Member 테이블의 인덱스를 가져왔습니다. (데이터는 현재 넣지 않아서 나중에 추가되면 넣겠습니다.)

🔻 Table : 테이블의 이름을 표시.

🔻 Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 PRIMARY로 표시.

  • UNIQUE INDEX는 중복값을 허용하지 않는 컬럼에 적용할 수 있는 인덱스.

🔻 Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시.

🔻 Seq_in_index : 인덱스에서의 해당 필드의 순서를 표시.

🔻 Column_name : 해당 필드의 이름을 표시.

🔻 Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시.

  • A : 대소문자 구분 없이 정렬.
  • B : 대소문자 구분하여 정렬.

🔻 Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시. (인덱스의 효과성을 평가하는 데 중요한 요소입니다.)

  • 값이 높을수록 인덱스의 선택성이 좋고, 쿼리 성능이 향상될 가능성이 높다.
  • ex) 특정 컬럼에 100개의 레코드가 있지만, 그 중 10개의 유일한 값만 있다면 Cardinality는 10이 됩니다.

🔻 Sub_part : 인덱스 접두어를 표시.

  • 인덱스가 생성될 때 컬럼의 일부만을 인덱스에 포함시키는 경우, 그 길이를 나타냅니다.
  • 전체 컬럼이 아닌 지정된 길이만큼의 데이터만을 인덱스에 저장하여 인덱스 크기를 줄이는 데 사용됩니다.
  • ex) VARCHAR(255) 타입의 컬럼에서 Sub_part가 10이라면, 이 인덱스는 컬럼의 처음 10바이트만 인덱스에 사용하고 나머지는 저장하지 않음을 의미.

🔻 Packed : 키가 압축되는 방법을 표시.

  • 인덱스를 압축하면 저장 공간을 절약하고, 메모리에서의 효율성을 높일 수 있음.
  • YES라면 인덱스가 압축되어 저장, NULL이라면 압축 X.

🔻 Null : 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고 저장할 수 없으면 ''를 표시.

🔻 Index_type : 인덱스에 사용되는 메서드를 표시.

🔻 Comment : 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시.

🔻 Index_comment : 인덱스에 관한 모든 기타 정보를 표시.

🔻 Visible : 인덱스가 현재 쿼리에서 사용 가능한지를 보여줌.

🔻 Expression : 인덱스가 특정 컬럼에 대해 생성된 경우, 해당 컬럼의 이름을 보여줌.

  • 만약 인덱스가 함수 기반 인덱스나 계산된 인덱스라면, 이 컬럼에 해당 함수나 표현식이 표시될 수 있음.

PRIMARY KEY와 유사하지만 차이가 있습니다.



🎧 인덱스 크기 확인

show table status;

프로젝트에서 만들었던 테이블들의 상태를 확인할 수 있습니다.
이걸로 테이블의 인덱스 크기 확인이 가능합니다.

🔻 Data_length : 테이블에 저장된 실제 데이터의 크기.

  • 인덱스나 기타 메타데이터의 크기는 포함 X.
  • 바이트 단위, 1,000 바이트는 약 1KB를 나타냄.
  • 테이블의 크기를 파악하고, 데이터가 얼마나 저장되고 있는지를 확인하여 성능 조정이 가능.

🔻 Index_length : 해당 테이블에 대한 모든 인덱스의 총 길이를 바이트 단위로 나타냅니다.

  • 인덱스의 크기가 클수록 검색 성능이 향상될 수 있지만, 동시에 저장 공간이 더 많이 소모됨. (필요없는 인덱스는 정리 필요.)




📘 인덱스 생성

🎧 Create Table 내

테이블 생성할 때 제약조건과 같이 지정해서 인덱스를 생성.

CREATE TABLE books (
  -- 같이 지정
  id varchar(5) primary key, -- 기본키 지정 (클러스터 인덱스)
  name varchar(20) unique, -- 유니크 인덱스 생성 (보조 인덱스) (중복 비허용)
  writer varchar(20) NOT NULL,
  
  INDEX idx_test (writer asc) -- 인덱스 생성 (보조 인덱스)
);
CREATE TABLE books (
  id varchar(5) primary key,
  name varchar(20) NOT NULL,
  writer varchar(20) NOT NULL,
  
  -- 뒤에 따로 할당
  CONSTRAINT [제약조건이름] PRIMARY KEY (id), -- 기본키 지정 (클러스터 인덱스)
  CONSTRAINT [제약조건이름] unique (email) -- 인덱스 생성 (보조 인덱스) (중복 비허용)
  INDEX idx_test (writer asc) -- 인덱스 생성 (보조 인덱스)
);

INDEX <인덱스명> ( 칼럼명1, 칼럼명2 )

  • 인덱스명은 DB내에서 고유해야 합니다.
  • 칼럼명은 인덱스가 적용될 컬럼들을 나열한 것입니다. (여러 개의 컬럼이라면 복합 인덱스가 생성.)
  • 복합 인덱스는 2개 이상의 조건을 포함하는 쿼리에서 더욱 효율적입니다.
  • ex) WHERE writer = 'John' AND name = 'Book Title'와 같은 조건에서 인덱스가 도움이 됩니다.

제약 조건을 설정할 때 인덱스 성능 주의점
보통 제약 조건의 설정은 테이블의 생성 구문에서 하거나, 테이블 생성하고 뒤에 alter문으로 따로 진행합니다.
그러므로 아직 테이블에 데이터가 입력되기 전에 PRIMARY KEY 및 UNIQUE 키의 열에 인덱스가 생성되어져 있기 때문에, 인덱스 자체를 구성하는 시간이 걸리지 않습니다.


많은 데이터가 입력된 후에는 alter문으로 PRIMARY나 UNIQUE를 지정하면 인덱스를 구성하는데 많은 시간이 걸릴 수 있습니다. (페이지 분할 후 다시 정렬이 필요해서.)



🎧 CREATE INDEX

따로 인덱스를 생성.

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명); -- 보조 인덱스 생성 (중복 허용)

CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명); -- 보조 인덱스 생성 (중복 허용 X)

CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (컬럼명); -- 긴 문자의 텍스트 데이터를 빠르게 검색하기 위한 MySQL의 부가적인 기능.

CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬러명2); -- 다중 컬럼 인덱스 생성

ANALYZE TABLE 테이블명; -- !! 생성한 인덱스 적용 !!


🎧 Alter ... Add Index

ALTER TABLE 테이블명
ADD INDEX 인덱스명

ALTER TABLE 테이블명
ADD UNIQUE INDEX 인덱스명

ALTER TABLE 테이블명
ADD PRIMARY KEY INDEX 인덱스명


🎧 인덱스 사용 확인 방법

select * from [테이블명] where [인덱스적용한컬럼명] = '데이터';

마우스 우클릭 후 실행 -> 실행 계획보기 하면 인덱스 사용 여부를 알 수 있습니다.



🎧 인덱스 삭제

DROP INDEX 인덱스명 ON 테이블명

ALTER TABLE 테이블명
DROP INDEX 인덱스명


🎧 클러스터 인덱스 삭제

DROP INDEX PRIMARY ON 테이블명

ALTER TABLE 테이블명
DROP PRIMARY KEY -- 외래키와 연결이 되어있다면 제약조건에 의해 삭제가 안 될 수 있음.
-- 외래키 먼저 삭제한 후에 클러스터 인덱스를 삭제해야 함.

SELECT table_name, constraint_name
FROM information_schema.referential_constraints
WHERE constraint_schema = 디비명

인덱스를 모두 제거할 때는 되도록이면 보조 인덱스부터 삭제하고 클러스터 인덱스를 삭제하는 것이 좋습니다.
클러스터 인덱스를 먼저 삭제하면 보조 인덱스가 다시 재구성이 되기 때문입니다.



🎧 인덱스 정렬

CREATE INDEX [인덱스명] ON 테이블명 (필드명 DESC/ASC)


🎧 다중 컬럼 인덱스

MySQL은 INDEX에 최대 15개 컬럼으로 구성 가능하다고 합니다.
다만 단일 컬럼 인덱스보다 비효율적으로 INDEX/UPDATE/DELETE를 수행해서 신중해야 합니다.

단일 인덱스와 다중 컬럼 인덱스 차이점

CREATE TABLE table1(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    INDEX idx_name(name), -- 단일 인덱스
    INDEX idx_address(address) -- 단일 인덱스
)
CREATE TABLE table2(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    INDEX idx_name(name, address) -- 다중 컬럼 인덱스 
)

위와 같이 2개의 테이블로 하나는 단일 컬럼 인덱스, 다른 하나는 다중 컬럼 인덱스를 만들었다고 했을 때

SELECT * FROM table1 WHERE name='~', AND address='';

table1의 경우 컬럼 각각에 INDEX가 걸려있어 name컬럼과 address 컬럼을 보고 둘 중에 어떤 컬럼의 수가 더 빠르게 검색되는지 판단한 후에 빠른 쪽을 먼저 검색하고 그 다음 컬럼을 검색하게 됩니다.

table2의 경우 INDEX를 저장할 때 name과 address를 같이 저장하기 때문에 바로 원하는 값을 찾습니다.
즉 name과 address의 값을 같이 색인하고 검색에서도 '홍길동서울' 로 검색을 시도하게 됩니다.


다중 컬럼 인덱스는 위처럼 조건이 2개 이상이어야 효과를 볼 수 있습니다.

  • 특히 다중 컬럼 인덱스의 첫 번째 컬럼과 두 번째 컬럼을 모두 사용할 경우 인덱스의 효과가 커집니다.
  • table2의 다중 컬럼 인덱스에서 SELECT하는 조건이 address만 있다면 name이 함께 검색되지 않아 INDEX의 효과를 볼 수 없습니다.




Reference

🖇️ 자세한 트리 동작 원리

profile
Park sang woo
일상의 인연에 감사하라. 기적은 의외로 가까운 곳에 있을지도 모른다.
이전 포스트

CORS 해결 방법

다음 포스트

인덱스 타입 종류

0개의 댓글