INDEX

https://www.jstwrite.com/blog/10-steps-to-creating-your-books-index

Index

• 일종의 색인
• 저장된 데이터가 많아질수록 검색속도가 느려지게 되는데 인덱스를 사용해 빠르게 값을 찾을수 있다
  • 데이터들이 정렬된 형태를 갖기 때문에 조건에 맞는 데이터를 빠르게 찾는다
• 조회속도를 높여주는 자료구조
• PK , UNIQUE 는 자동으로 index가 생성이된다 ▶ 고유 인덱스
• 사용자가 index를 생성한다 ▶ 비 고유 인덱스
• 행의 삽입 , 삭제가 자주 발생 시 index 갯수를 최소화 하는 것이 효울적이다
  • insert , update가 적은 테이블에 인덱스 사용하는게 굿

 

select
    admin_no 			as no,
    admin_id 			as id,
    admin_pw			as pw,
    admin_name 			as name,
    admin_email 		as email,
    admin_tel 			as tel,
    admin_del_yn		as deleted,
    admin_reg_date 		as createdDate,
    admin_update_date 	as updatedDate
from
    admin
where
    admin_id = #value#
    
-- 인덱스 생성
CREATE INDEX {인덱스 명} ON {어떤 테이블}({어떤 칼럼});
CREATE INDEX idx_admin_id ON admin(admin_id);
CREATE INDEX idx_admin_email ON admin(admin_email);

---

왜 INDEX 를 사용할까 ?

인덱스는 테이블의 데이터 크기에 비해 굉장히 작다.
인덱스는 실제 데이터베이스 테이블에 비해 메모리에 적재하기 쉽다.
메모리(RAM)에 최대 1gb를 적재할 수 있고, 하드디스크에 100gb의 데이터베이스가 존재한다고 가정하자.

만약 우리가 찾고자 하는 데이터가 데이터베이스의 맨 끝에 있다면, 100gb의 데이터를 모두 탐색한 끝에야 원하는 데이터를 얻을 수 있다. 하지만, 우리에게 주어진 메모리는 1gb이므로 100gb의 데이터를 100개로 나눈뒤, 100번 꺼내와야한다. 그런데, 주 기억장치 대비 보조 기억장치의 IO성능은 굉장히 떨어진다.

인덱스를 사용하면, 이런 불필요한 IO를 줄여 데이터 탐색 성능을 개선할 수 있다. 인덱스만을 메모리에 적재하고, 원하는 데이터의 물리적 주소를 찾아 접근하면 되기 때문이다.

---

INDEX 가 없다면 ?

인덱스를 사용하지 않으면, 데이터를 탐색할 때 풀 테이블 스캔(Full Table Scan)이 발생한다. 사용자가 원하는 데이터를 찾기 위해 테이블에 존재하는 모든 행을 읽어내는 방법이다. 풀 테이블 스캔은 전체 데이터를 탐색하므로, 디스크에서 데이터를 읽어 메모리로 적재하는 IO 비용이 많이 발생하는 가장 느린 테이블 스캔 방식이다.

풀 테이블 스캔은 테이블에 인덱스가 존재하지 않거나, 인덱스가 존재한다고 하더라도 데이터베이스의 옵티마이저(Optimizer)가 인덱스 대신 풀 테이블 스캔으로 탐색하는 것이 더 적절하다고 판단할 때 수행된다. 

풀 테이블 스캔(Full Table Scan)

MySQL에서 데이터를 읽을 때 전체 테이블을 순차적으로 스캔하여 검색. 
인덱스를 사용하지 않고 모든 행을 확인하여 필요한 데이터를 찾는 방식.

---

 

INDEX 를 추가해도 작동하지 않을 때

- 테이블에 인덱스가 없는 경우
쿼리에서 사용된 조건이나 순서에 따라 인덱스가 생성되지 않은 경우에는 MySQL이 전체 테이블을 스캔하여 데이터를 찾는다

- 인덱스를 사용할 수 없는 경우
인덱스를 사용하기보다 테이블을 전체로 스캔하는 것이 더 효율적이라고 판단할 때 발생
일부 조건이나 함수 등이 인덱스를 사용할 수 없는 경우에 주로 발생한다

풀 테이블 스캔은 작은 테이블에는 큰 영향을 주지 않지만, 대용량 테이블에서는 성능 문제를 발생시킬수 있다
풀 테이블 스캔은 모든 행을 검사해야 하므로 처리 속도가 느려질 수 있고, 메모리나 디스크 I/O를 많이 사용한다

가능하면 인덱스를 적절히 활용하여 풀 테이블 스캔을 피하는 것이 좋다고 판단한다

 

---

 

INDEX  는 설계는 언제 할까 ?

• 기능 개발을 끝마치고 나서 mapper(쿼리를) 분석해서 인덱스를 설계한다 

1️⃣ 검색 성능향상 : WHERE 절에서 특정 컬럼을 이용하면 조건 검색이 빨라진다 
인덱스가 없다면 데이터베이스 엔진은 테이블 전체를 스캔해서 조건에 맞는 행을 찾아야한다 
하지만 인덱스가 있다면 인덱스를 사용해 더 빠르게 검색이 가능하다

2️⃣ 조인 성능 향상 : JOIN 절에서 조인할 때 인덱스가 있는 경우 , 데이터베이스 엔진은 인덱스를 이용해
조인을 수행할 수 있다
두 테이블간의 매칭되는 행을 효율적으로 찾을수 있도록 도와준다

3️⃣ 정렬 및 그룹화 성능 향상 : 인덱스는 정렬 및 그룹화 작업에 도움을 주는데 ORDER BY , GROUP BY 에 인덱스를 사용하면 정렬이나 그룹화 작업이 빨라진다

4️⃣ 범위검색 최적화 : 일부 인덱스는 범위 검색에 특히 유용한데 , 예를들면 날짜의 범위나 , 숫자의 범위에 대한 검색에서 인덱스를 사용하면 효율 적으로 원하는 결과를 얻을 수 있다

 

https://infjin.tistory.com/187

[Oracle]DB 인덱스 정의, 특징, 주의사항

---

INDEX 설정 주의사항

 

INSERT 와 UPDATE 가 자주 발생하는 곳에서 오버헤드가 발생한다고한다

오버헤드
오버헤드(overhead)는 어떤 처리를 하기 위해 들어가는 간접적인 처리 시간 · 메모리 등을 말한다. - 위키피디아-

1. 마트에 물건 사러간다
2. 10만원 어치 물건을 산다면 '마트까지 가는 행위'는 장보기에서 큰 부분을 차지 하지 않는다
3. 마트에 아이스크림 하나를 사러 가는데 '마트까지 가는 행위'  는 장보기에서 큰 부분을 차지한다
4. 아이스크림을 사러 마트까지 가는 것은 '오버헤드가 너무 크다' 라고 비유할 수 있을것이다

 

---

 

 WHERE , JOIN에 컬럼이 여러가지라면 어떻게 설정해야할까 ?

 

<select id="selectInquiryListPaging" resultType="com.example.bookhub.user.dto.InquiryListDTO">
    select
        i.INDIVIDUAL_INQUIRY_NO             as no,
        i.INQUIRY_CATEGORY_NO               as "faqCategory.no",
        f.FAQ_CATEGORY_NAME                 as "faqCategory.name",
        i.INQUIRY_USER_NO                   as "user.no",
        u.USER_ID                           as "user.id",
        i.INDIVIDUAL_INQUIRY_TITLE          as title,
        i.INDIVIDUAL_INQUIRY_CONTENT        as content,
        i.INDIVIDUAL_INQUIRY_ANSWER_YN      as answerYn,
        i.INDIVIDUAL_INQUIRY_DELETE_YN      as deleteYn,
        i.INDIVIDUAL_INQUIRY_CREATE_DATE    as createdDate ,
        i.INDIVIDUAL_INQUIRY_UPDATE_DATE    as updatedDate,
        u.USER_NAME                         as "user.name"
    from INDIVIDUAL_INQUIRIES i , USER u , FAQ_CATEGORIES f
    where i.INQUIRY_USER_NO = u.USER_NO
      and i.INQUIRY_CATEGORY_NO = f.FAQ_CATEGORY_NO
      and u.USER_ID = #{id}
    order by  i.INDIVIDUAL_INQUIRY_CREATE_DATE DESC
        LIMIT #{offset} ,10
</select>

 

📌 컬럼에 카디널리티 수치를 기준으로 설정한다 ( 특정 컬럼이나 관계에서 , 고유한 값을 가지는 정도 )
Ex ) 사람이라는 테이블에 성별 , 이메일 , 주민번호는 다른 컬럼에 비해 카디널리티 수치가 높다 

 

select
    concat(ROUND(COUNT(DISTINCT INDIVIDUAL_INQUIRY_NO) / count(*) * 100 , 2),'%') AS inquiryNO_carinality,
    concat(ROUND(COUNT(DISTINCT INQUIRY_CATEGORY_NO) / count(*) * 100 , 2),'%') AS cateNO_carinality,
    concat(ROUND(COUNT(DISTINCT INQUIRY_USER_NO) / count(*) * 100 , 2),'%') AS  userNO_carinality,
    concat(ROUND(COUNT(DISTINCT INDIVIDUAL_INQUIRY_CONTENT) / count(*) * 100 , 2),'%') AS inquiryContent_carinality,
    concat(ROUND(COUNT(DISTINCT INDIVIDUAL_INQUIRY_CREATE_DATE) / count(*) * 100 , 2),'%') AS created_carinality,
    concat(ROUND(COUNT(DISTINCT INDIVIDUAL_INQUIRY_UPDATE_DATE) / count(*) * 100 , 2),'%') AS upated_carinality
from INDIVIDUAL_INQUIRIES;

 

 

카디널리티 수치 (cardinality)

상대적인 개념으로 전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표
중복도가 '낮으면' 카디널리티가 '높다'
중복도가 '높으면' 카디널리티가 '낮다'

주민등록번호는 중복되는 값이 없으므로 카디널리티가 높다고 할 수 있다
이름은 동명이인 때문에 중복되는 값이 있으므로 '주민등록 번호' 에 비해 카디널리티가 낮다고 할 수 있다

DISTINCT 값이 많다는 것은 '중복도가 낮다 카디널리티가 높다' 라는 의미다

 

---

단일 인덱스 &  결합 인덱스

 

1. 단일 인덱스

• 테이블의 단일 컬럼에 대해 생성된다
• 주로 특정 컬럼을 검색하거나 정렬할 때 사용한다
• 예를들면 USER 테이블의 단일 인덱스를 사용하여 id 에 대한 인덱스를 생성한다

CREATE INDEX idx_user_id ON USER (id);

 

2. 결합 인덱스

• 테이블에서 2개 이상의 컬럼에 대해 생성된다
• 여러 컬럼의 조합에 대해 인덱싱을 하기 때문에 해당 컬럼들의 조합에 대한 검색 또는 정렬에 효과적이다

CREATE INDEX idx_last_name_first_name ON USER (lastName , firstName);

단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기 때문에 신중해야한다.

 

• 결합인덱스 는 생성시 컬럼 순서에 따라 완전히 다른 인덱스가 되어 성능차이가 난다
• 결합인덱스를 구성시 선택성이 좋은 컬럼을 앞으로 구성하는것이 일반적으로 좋다
  • 선택성이면 카디널리티 수치가 높거나 , 자주 선택하는 건가 ?
• 등치조건 = 을 사용하는 컬럼이 BETWEEN 같은 범위조건을 사용하는 컬럼보다 앞에 두는것이 좋다

USER_NO 는 등치조건 =  , CREATED_DATE 는 범위조건

CREATE INDEX idx_no_createdDate USER (USER_NO , CREATED_DATE );

 

 

 

---

• 인덱스가 조회속도개선에는 도움이되지만, 입력/수정/삭제에서는 성능이 감수한다.
• 무조건 많이 설정하지 않는다. (한 테이블당 3~5개가 적당 목적에 따라 상이)
• 조회시 자주 사용하는 컬럼
• 고유한 값 위주로 설계
• 카디널리티가 높을 수록 좋다 (= 한 컬럼이 갖고 있는 중복의 정도가 낮을 수록 좋다.)
• INDEX 키의 크기는 되도록 작게 설계
• PK, JOIN의 연결고리가 되는 컬럼
• 단일 인덱스 여러 개 보다 다중 컬럼 INDEX 생성 고려
• UPDATE가 빈번하지 않은 컬럼
• JOIN시 자주 사용하는 컬럼