인덱스 란?

추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조로 마치 책의 목차처럼 테이블 칼럼을 색인화 하여 검색 속도를 향상시키는 기술이다.

인덱스 종류

• 클러스터형 인덱스
  • 인덱스 생성시 데이터 전체가 다시 정렬된다.
  • 인덱스 자체의 리프페이지가 곧 데이터 페이지이다. (인덱스 자체에 데이터 포함)
  • 보조 인덱스보다 검색 속도는 빠르나 입력/수정/삭제 작업에서는 더 느리다.
  • 성능이 좋으나 테이블에 단 하나만 생성할 수 있다.
  • Primary key로 지정하거나 unique not null 지정시 클러스터형 인덱스가 생성된다. 우선순위는 Primary key가 더 우선시 된다.

• 보조 인덱스 (논 클러스터형 인덱스)
  • 데이터 페이지와 별개로 별도의 페이지에 인덱스를 구성한다.
  • 리프페이지에 데이터가 아니라 데이터가 위치하는 주소값을 가지고 있다.
  • 보조 인덱스를 테이블 당 여러가지 생성할 수 있으나, 남용 경우 시스템 성능을 떨어뜨리는 결과를 초래한다.
  • not null이 아닌 unique 경우 보조 인덱스가 생성된다.

• 둘 혼합 경우
  • 보조 인덱스 리프 페이지에 데이터 주소값이 아닌 클러스터형 인덱스의 키 값을 가지게 된다.
  • 보조 인덱스 조회 후 다시 클러스터형 인덱스 조회
  • 왜 보조 인덱스에 주소값을 넣지 않을까? 주소값으로 리프페이지에 저장되어 있으면, 데이터에 삽입 삭제 등으로 페이지 분할 등 변화가 생길 때, 클러스터형 인덱스의 하나 변화로 클러스터형 데이터페이지가 일단 페이지 변화, 오프셋 등이 대폭 변경이 된다. 이에 따라 보조 인덱스의 주소값 까지 대폭 변경되는 상황이 벌어져 큰 부하가 일어난다. 그래서 둘 혼합 경우 보조 인덱스의 리프페이지는 클러스터형 인덱스의 키 값만을 가지게 된 것이다.

인덱스 적용 방법

✅ 클러스터형 인덱스

- 생성 1 : 테이블 생성시 PRIMARY KEY 적용

CREATE TABLE 테이블이름 (
		열이름 타입 PRIMARY KEY,       // 클러스터형 인덱스
		열이름 타입,
		열이름 타입 UNIQUE,            // 보조 인덱스
		열이름 타입 UNIQUE NOT NULL,   // 클러스터형 인덱스 
};

- 생성 2 : 이미 생성된 테이블에 PRIMARY KEY 지정

ALTER TABLE 테이블이름 ADD PRIMARY KEY (열이름);

- 삭제 :

ALTER TABLE 테이블이름 DROP PRIMARY KEY;

✅ 보조 인덱스

- 생성

기본생성

형식:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 인덱스이름 
	[index_type]
ON 테이블이름 (key_part, ...)
	[index_option]
	[algorithm_option | lock_option] ...

key_part :
	{컬럼이름 [(length)|(expr)} [ASC | DESC}

index_option :
		KEY_BLOCK_SIZE [=] value
	| index_type
	| WITH PARSER parser_name
	| COMMENT 'string'
	| {VISIBLE | INVISIBLE}

index_type :
	USING {BTREE | HASH}

algorithm_option :
	ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option :
	LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

간단하게

CREATE INDEX 인덱스이름 ON 테이블이름 (열이름);

- 이미 생성된 테이블에 INDEX 생성

ALTER TABLE 테이블이름 ADD INDEX 인덱스이름 (열이름);

- UNIQUE 제약조건으로 보조 인덱스 생성

ALTER TABLE 테이블이름 ADD CONSTRAINT UK_인덱스이름 UNIQUE (열이름);

- 삭제

DROP INDEX 인덱스이름 ON 테이블이름;

인덱스 성능 비교

조건) 정렬 안된 동일한 데이터가진 테이블 3개를 가지고 비교 (110만건 기준)

아래 명령어를 통해 검색시 몇개의 페이지를 읽었는 지 검색 이전과 이후의 값 차이로 알수있다.

show global status like 'Innodb_pages_read';

• 아무 인덱스도 없을때

- 읽기전

- 쿼리와 실행 계획 (Full Table Scan으로 작동)

select * from test_product_none where id = 100000;

- 읽은후

읽은후 값 - 읽기전 값 = 읽어들인 페이지 수
클러스터형도 보조도 없을 경우 조회를 위해 10658개를 읽어들였다.

• 클러스터형 인덱스가 있을때

- 읽기전

- 쿼리와 실행 계획 (클러스터형 인덱스 작동)

select * from test_product_cluster where id = 100000;

- 읽은후

! 클러스터형은 겨우 2페이지 만에 데이터를 찾아냈다.

• 보조 인덱스가 있을때

- 읽기전

- 쿼리와 실행 계획 (클러스터형 인덱스 작동)

select * from test_product_secondary where id = 100000;

- 읽은후

! 보조 인덱스 경우도 5페이지 만에 데이터를 찾아냈다.

인덱스 없이 데이터가 무려 10658개의 페이지를 읽었던 것과 비교하면 인덱스가 얼마나 유용한지 알 수 있다.

인덱스 설정시 고려사항

• WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
• WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
• 데이터 중복도가 높은 열은 인덱스를 만들어도 효과가 없다.
• 외래 키를 지정한 열엔 자동으로 외래키 인덱스가 생성된다.
• JOIN이 자주 사용되는 열에 인덱스를 생성해 주는 것이 좋다.
• INSERT / UPDATE / DELETE 가 얼마나 자주 일어나는지 고려해야한다. 이 작업이 자주 일어나는 테이블에 인덱스를 쓰면 오히려 성능이 나빠진다 (페이지 분할이 빈번하게 발생하기 때문)
• 클러스터형은 테이블당 하나만 생성할 수 있다.
• 클러스터형 인덱스가 없는게 더 좋은 경우도 있다.
  (새로운 데이터가 들어올때마다 정렬이 계속 수행되어 페이지 분할이 일어날 수도 있기 때문)
• 사용하지 않는 인덱스는 제거해야 한다.
• 클러스터형과 보조 인덱스 혼합시
  클러스터형 키 값으로 결정될 열은 최대한 적은 자릿수의 열을 선택하는 것이 좋다. 이 열 값으로 보조 리프페이지에도 저장되기 때문이다.

복합인덱스 (Composite Index, 결합인덱스, 멀티인덱스) 란?

인덱스 컬럼이 2개 이상 걸려있는 경우로 데이터가 많고 조건에 컬럼의 개수가 많을 경우에 사용한다.

인덱스에서는 선행 칼럼에서 걸러진 범위에서 그다음 컬럼이 걸러지고 연속해서 걸러진 범위안에서 값을 검색하기 때문에 인덱스 순서가 매우 중요하다.

• 인덱스 첫번째 컬럼을 Where절로 사용하지 않는다면 인덱스가 사용되지 않기 때문에 일단 공통적으로 사용하는 필수 조건절 컬럼을 우선시 해야한다.
• 조건(WHERE) 절에서 Equal('=')이 아닌 다른 연산자(BETWEEN, LIKE, <, >)의 첫 번째 컬럼까지만 인덱스를 사용하고 그 이후 컬럼들은 인덱스를 사용하지 않는다. (=,=,between,=,= 순서로 where절 조건을 넣었다면 3번째의 between까지만 인덱스를 사용한다. = 연산자 컬럼을 다른 연산자보다 우선으로 한다. (=이 아닌 첫번째 연산자 까지만 인덱스를 타지 않고 필터링 한다.)

추가 자료들

• 인덱싱 알고리즘 종류
  • B-tree 알고리즘
    가장 일반적으로 사용, 오래전부터 도입되어 많이 안정화 된 알고리즘. 컬럼 값을 변환하지 않고 원래 값을 이용해 인덱싱.
  • Hash 인덱스 알고리즘
    컬럼 값으로 Hash 값을 계산하여 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원. 변환된 값을 이용하여 인덱싱 하므로 LIKE 검색과 같은 주로 메모리 기반의 DB에서 많이 사용.
    동등비교에 최적화되어 있고 부분일치 값을 검색하고자 할때는 사용 불가
  • Fractal-Tree 알고리즘
    B-tree 알고리즘의 단점을 보완을 위해 등장, 원래 값을 이용해 인덱싱. 데이터의 저장과 삭제 시 처리비용을 감축, 조만간 B-tree 알고리즘을 대체할 수 있을거라 예상.

📌공부 이유

SQL 문제를 풀때 WHERE 절로 필터링 하는 것과 HAVING으로 필터링 하는 데에 결과값은 같을 때가 있었다.

그럼 성능적으로 어떤 차이가 있을까? 하는 궁금증이 생겨 비교해봤다.

공통 조건:
상품 테이블에서 big_category 기준으로 그룹화하여 그중 big_category가 패션인 경우가 몇개가 되는 지를 검색

1️⃣ where 로 필터링

select big_category, count(*) as count from product where big_category = '패션' group by big_category;

2️⃣ having 으로 필터링

select big_category, count(*) as count from product group by big_category having big_category = '패션';

📢 결과

Having이 where보다 약 3.7배 더 많은 시간이 걸린다. 

❓왜 

1) 차이점

where은 모든 필드를 대상으로 조건을 둘수 있다

having은 group by로 그룹화 되어진 새로운 테이블에 조건을 둘 수 있다. 

2) 성능 차이

having은 모든 행을 집합으로 먼저 정렬한 후 having으로 필터링 적용을 한다.
하지만 where 절로 먼저 불필요한 행을 미리 필터링 후 그룹화를 하게 되어 내부 정렬에 필요한 행 수를 줄여 훨씬 효율적이다. 

결론: 
성능적으로 having보다는 where 절이 유리한 것으로 보인다. 

📌 사용 계기

RDS 말고 로컬에서 공부해보기 위해 설치

1. MySQL 다운

* 웹 버젼은 설치 진행시 필요한 파일만을 선택해 인터넷으로부터 다운로드하여 설치가 가능하다

* 일반 버젼은 설치 파일을 모두 가지고 있어 설치시 다운로드 없이 설치가 가능해 인터넷 연결없이 설치가 가능하다

2. MySQL 설치

1) 설치 타입 선택

* 공부 목적도 있으니 최대한 전체 설치하는 Developer Default 선택함 

2) 요구조건 체크

MySQL 설치에 있어서 각 컴퓨터 마다 충족되야할 프로그램들이 나온다. 

** 띠용. Check는 installer에서 요구하는 환경을 세팅한 후에 Check하면 넘어가는 방식인가 보다. Check를 누른다 해서 해당 프로그램을 설치해주는 방식이 아니다. 아니 원래는 Execute 버튼으로 설치 유도하는 방식이 었다는 데 왜 바꾼걸까 

각 컴퓨터 환경마다 다른 조건이 나오므로 그에 해당하는 프로그램을 설치한다.  

여기서 요청하는 Visual Studio 설치방법은 접은 글로.. 

더보기

 

 

MySQL :: MySQL Community Downloads

The world's most popular open source database Contact MySQL  |  Login  |  Register

dev.mysql.com

다운로드 아카이브 >

MySQL for Visual Studio > 

 

다운받아 설ㅊ ....? 

결국엔 받아야 하나 보다 ㅠ 통채로 받는건 어떻게든 피하고 싶었는데

 

Visual Studio Tools 다운로드 - Windows, Mac, Linux용 무료 설치

Visual Studio IDE 또는 VS Code를 무료로 다운로드하세요. Windows 또는 Mac에서 Visual Studio Professional 또는 Enterprise Edition을 사용해 보세요.

visualstudio.microsoft.com

가장 최근 버전은 2022 라서 2019를 찾아 이전 다운로드로 간다

key가 요구되지 않는 Community 버전을 다운로드 

설치 > 워크로드 등 설정 없이 기본사항으로 설치

설치가 되었다면 다시 MySQL for Visual Studio를 설치 (아까와 같은 경고가 안나온다 굳)

설치가 완료 되었다면 다시 MySQL 설치를 켜보자 

3) Product 설치

* 접은 글에서 MySQL for Visual Studio에 대한 문제를 해결하고 왔다면 다시 MySQL Installer를 켜 Add를 누른다.

* developer default 경우 각 항목마다 최근 버전에 해당하는 것을 설치해주기 때문에 모든 항목에서 최근걸 선택하고 Connector 부분에 대해서만 내가 사용하지 않는 언어에 대한 것은 제외했다. MySQL for Visual Studio는 이미 설치했으니 패스

Excute를 통해 다운로드를 진행한다. 

"다운로드"가 체크 되었다면 Next

순간 아까 전 화면 같아 보이겠지만 아까는 다운로드고 이번엔 설치다. Excute로 설치를 진행한다. 

설치가 확인되었다면 Next 

4) Product 설정. 이제 이걸 차근차근 설정해 나간다. 

5) 서버 설정 타입과 연결 등 선택

local 서버로 해볼거라 Development Computer를 선택한다

6) 인증방법 선택

7) 루트 및 사용자 계정 생성

위에 것은 Root 계정의 Password 를 지정하고 아래는 사용자 계정을 추가한다.

여기서 만든 계정으로 Workbench 접속이 가능하다.

8) 윈도우 서비스 

윈도우에서 나타나는 서비스 명과 시스템 시작시 같이 시작할 것인지 등을 설정한다. 

9) 서버 파일 권한

10) 설정 적용

11) 확인

12) 이제 Router 설정에 들어간다

13) 별도의 설정없이 Next

14) Product Configuration 화면에서 다시 Next해 아래 창이 뜨면 아까전에 만들었던 root 계정과 Password를 넣고 Check하면 연결여부를 확인 할 수 있다. 확인 됬다면 Next

15) 설정 적용

16) 확인 후 finish

17) 설정 완료

18) 기본 설치는 완료 

Workbench나 Shell은 원하는 대로 체크해서 바로 실행하면 된다. 

📌 사용 계기

Redis 만료시간 설정을 사용해서 저장해 놓은 reflesh token을 일정 시간이 지나면 자동으로 삭제할 필요가 있었다. 사용할 수 있는 가장 기본적인 예시로 방법을 정리

📍공통적으로 사용한 Redis Configuration

@EnableRedisRepositories
@Configuration
public class RedisConfig {

    @Bean
    public RedisConnectionFactory redisConnectionFactory() {
        LettuceConnectionFactory lettuceConnectionFactory = new LettuceConnectionFactory();
        return lettuceConnectionFactory;
    }

    @Bean
    public RedisTemplate<?, ?> redisTemplate(RedisConnectionFactory connectionFactory) {
        RedisTemplate<byte[], byte[]> redisTemplate = new RedisTemplate<>();
        redisTemplate.setConnectionFactory(connectionFactory);
        redisTemplate.setKeySerializer(new StringRedisSerializer());
        redisTemplate.setValueSerializer(new StringRedisSerializer());
        return redisTemplate;
    }
}

📌 1) Redis Template Set 설정 방법

: Strings (기본적인 key-value)자료구조로 저장하며, 저장하는 오퍼레이션 실행시 만료시간을 설정하는 방법. 

📍저장 객체

@RedisHash(value = "redis")
public class RedisTemplateEntity {
    private String email;
    private String testText;
}

📍Repository

@Repository
@RequiredArgsConstructor
public class RedisTemplateRepository {
    private final RedisTemplate<String, String> redisTemplate;
    private long expireTime = 60;

    public String saveValue (String email, String testText) {
    	// Point!TimeUnit을 통해 만료시간 단위 변경 가능
        redisTemplate.opsForValue().set(email, testText, expireTime, TimeUnit.MINUTES);
        return redisTemplate.opsForValue().get(email);
    }

    public void deleteValue (String email) {
        redisTemplate.delete(email);
    }
}

📍사용

// 사용 테스트만을 위한 컨트롤러라 서비스로 나누지 않음
@RestController
@RequiredArgsConstructor
public class RedisTemplateController {

    private final RedisTemplateRepository redisTemplateRepository;

    // 저장
    @PostMapping("/testValue")
    public String saveTestValue (@RequestBody RedisTemplateDto redisTemplateDto){
        return redisTemplateRepository.saveValue(redisTemplateDto.getEmail(), redisTemplateDto.getTestText());
    }

    // 수정 : Redis는 같은 key값의 데이터를 쓰게되면 기존 데이터위에 덮어쓰게됨. 그래서 save나 update나 방식이 동일
    @PostMapping("/testValue/{email}")
    public String updateTestValue (@RequestParam String email, @RequestBody RedisTemplateDto redisTemplateDto){
        return redisTemplateRepository.saveValue(email, redisTemplateDto.getTestText());
    }

    // 삭제
    @DeleteMapping("/testValue/{email}")
    public void deleteTestValue (@RequestParam String email) {
        redisTemplateRepository.deleteValue(email);
    }
}

📌 2) Crud Repository 설정 방법

: 저장하는 객체 자체에 만료시간을 설정해주는 방법. 객체 @RedisHash 어노테이션에 timeToLive 만료시간을 설정

📍저장 객체

@Getter
@RedisHash(value = "REDIS_USE", timeToLive = 5*60L) // Point! 기본 초단위
public class RedisCrudUseEntity {
    @Id
    private Long id;
    private String testText;

    @Builder
    public RedisCrudUseEntity(Long id, String testText) {
        this.id = id;
        this.testText = testText;
    }
}

📍Repository

@Repository
public interface RedisCrudUseRepository extends CrudRepository<RedisCrudUseEntity, Long> {
}

📍사용

// 사용 테스트만을 위한 컨트롤러라 서비스로 나누지 않음
@RestController
@RequiredArgsConstructor
public class RedisCrudUseController {
    private final RedisCrudUseRepository redisCrudUseRepository;

    // 저장
    @PostMapping("/redisCrudUse")
    public void saveRedisCrudUse (@RequestBody RedisCrudUseDto redisCrudUseDto) {
        RedisCrudUseEntity redisCrudUseEntity = RedisCrudUseEntity.builder()
            .id(redisCrudUseDto.getId())
            .testText(redisCrudUseDto.getTestText())
            .build();
        redisCrudUseRepository.save(redisCrudUseEntity);
    }

    // 수정 : Redis는 같은 key값의 데이터를 쓰게되면 기존 데이터위에 덮어쓰게됨. 그래서 save나 update나 방식이 동일
    @PostMapping("/redisCrudUse/{id}")
    public void updateRedisCrudUse (@PathVariable Long id, @RequestBody RedisCrudUseDto redisCrudUseDto) {

        RedisCrudUseEntity redisCrudUseEntity = RedisCrudUseEntity.builder()
                .id(id)
                .testText(redisCrudUseDto.getTestText())
                .build();
        redisCrudUseRepository.save(redisCrudUseEntity);
    }

    // 삭제
    @DeleteMapping("/redisCrudUse/{id}")
    public void deleteRedisCrudUse (@PathVariable Long id) {

        redisCrudUseRepository.deleteById(id);
    }
}

➕ 실습 레포지토리

📌 Error Message

Error creating bean with name 'gameStartSetRepository2' defined in com.example.namoldak.repository.GameStartSetRepository2 defined in @EnableRedisRepositories declared on RedisRepositoriesRegistrar.EnableRedisRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.mapping.MappingException: Entity com.example.login.entity. Token requires to have an explicit id field. Did you forget to provide one using @Id?

📍문제 상황

Redis Repository를 Bean 으로 등록이 안되면서 실행이 안되는 에러가 발생!!

📍원인 

아래처럼 레디스에 저장해야할 객체 안에서 Id의 어노테이션의 임포트를 하는 데 잘못 임포트해서 생긴 것!

📍 해결 

위에 보면 javax.persistence.Id; 가 임포트 되어 있는 데 이건 JpaRepository에서 사용했던 거고

CrudRepository를 사용할 거면 org.springframework.data.annotation 을 임포트하면 해결된다!