Mysql 인덱스 사용법 및 실행 계획 정리

데이터베이스/mysql|2020. 6. 10. 19:18

mysql 인덱스에 대한 정확한 이해도 없이 사용을 하다보니 조금 개념적으로 헷갈리는게 많이 있었다. 이 부분에 대해 한번 정리하고 넘어가고자 기록해본다.

 

인덱스


인덱스는 빠르게 특별한 컬럼과 함께 값을 찾는데 사용된다. 인덱스가 없으면 Mysql은 처음 행부터 전체 테이블을 읽어 들여서 데이터를 찾는다. 거대한 테이블에서 이런 행동은 비용이 상당히 많이 들어가게 된다. 만약에 테이블이 인덱스를 가지고 있으면 빠르게 접근할 수 있게 된다.

대부분의 Mysql 인덱스 (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT)는 B-tree안에 저장된다. 예외적으로 spatial 데이터 타입은 R-tree를 사용, 메모리 테이블은 또한 hash index를 지원, InnoDB는 FULLTEXT 인덱스를 위해 inverted list를 사용한다.

 

 

인덱스 동작 방식


- 행을 찾기 위해서 매칭되는 WHERE 구문을 빠르게 찾는다.

 

- 조건으로 부터 불필요 행을 제거한다. 만약에 여러 인덱스가 있는 경우 Mysql은 가장 적은 수의 행을 사용하는 인덱스를 선택한다. (Mysql은 한번에 하나의 인덱스만 사용할 수 있다.)

 

- 만약 테이블이 multiple column 인덱스를 가지고 있으면 인덱스의 가장 왼쪽에 컬럼을 사용하여 옵티마이저를 통해 행을 찾는다. 예를 들어 만약에 (col1, col2, col3)을 사용하는 인덱스가 있는 경우 인덱스는 이 순서로 검색을 진행한다. (col1), (col1, col2), (col1, col2, col3)

 

- 조인이 있는 경우 다른 테이블에서 행을 찾는다. Mysql은 동일한 유형과 사이즈로 되어 있는 열을 index로 사용할 때 더욱 효과적으로 행을 찾는다. VARCHAR, CHAR는 두개를 같은 사이즈로 명시 하였을 경우에 같은 타입으로 고려되어 사용된다. 예를 들어 VARCHAR(10) = CHAR(10)이지만 VARCHAR(10) ≠ CHAR(15)는 같지 않다.

 

- binary가 아닌 문자열 사이를 비교하기 위해서는 동일한 문자열 집합을 사용해야한다. utf8과 latin1열을 비교할 경우 인덱스를 사용할 수 없다.

 

- 타입이 다른 문자열과 숫자 등을 비교하려고 할때도 마찬가지로 인덱스를 탈 수 없다. (묵시적 형변환)

 

- index로 사용되는 key_col에서 min(), max()의 값을 찾기 위해 인덱스에서 key_col 이전에 발생하는 모든 키 파트에서 WHERE key_part_n = const를 사용하는지 여부를 확인하기 위해서 전처리기를 통해서 최적화가 진행된다. 이 경우에서 Mysql은 각 min() 또는 max() 표현식에 대해 단일키 조회를 수행하여 상수를 대체한다. 모든 표현식이 상수로 바뀌고 나면 쿼리가 한번에 반환된다.

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

 

 

실행계획


- 실행계획 필드 정리

  • Id
    • Select 구문 구분 ID
  • select_type
    • SIMPLE : 단순 SELECT
    • DERIVED : 서브 쿼리 중 가장 안쪽에 있는 쿼리 
    • PRIMARY : 서브 쿼리 바깥쪽에 있는 쿼리
    • DEPENDENT SUBQUERY  : 조건절 내부에서 외부 쿼리와 연결된 SELECT 
// PRIMARY
SELECT * FROM ( 
    // DERIVED : 서브 쿼리 중 가장 안쪽에 있는 쿼리
    SELECT * FROM timeline_item ti
) tt;


// DEPENDENT SUBQUERY
SELECT * FROM timeline_item t1 WHERE EXISTS ( SELECT * FROM timeline_site t2 WHERE t1.id = t2.id)
  • table

    • 참조하는 테이블 이름
  • type

    • 조인 혹은 조회 타입 (아래로 갈수록 성능 하락)
      1. System : 테이블에 데이터가 하나만 있는 경우
      2. const : SELECT에서 Primary Key 혹은 Unique Key를 상수로 조회하는 경우
      3. eq_ref : 조인할 때 Primary, Unique Key로 매칭하는 경우
      4. ref : 조인할 때 Primary, Unique Key로 매칭하지 않은 경우
      5. ref_or_null : ref와 같지만 NULL이 추가되어 검색된 경우
      6. index_merge : 두개의 인덱스가 병합되어 검색이 된경우
      7. unique_subquery : In절 내부 서브쿼리에서 Primary Key가 있는 경우
      8. index_subquery : In절 내부 서브쿼리에서 Primary Key가 아닌 인덱스가 있는 경우
      9. range : 특정 범위 내에서 인덱스를 사용하여 데이터 추출 하는 경우
      10. index : 인덱스를 처음부터 끝까지 찾아서 검색하는 경우로 일반적인 인덱스 풀스캔
      11. all : 테이블 풀스캔
  • possible_keys

    • 데이터 조회 시 DB에서 사용할 수 있는 인덱스 리스트
  • key

    • 실제로 사용할 인덱스
  • key_len

    • 실제로 사용할 인덱스 길이
  • ref

    • key 안의 인덱스와 비교하는 컬럼(상수)
  • rows

    • 쿼리 실행 시 조사하는 행수
  • extra

    • 추가 정보 (데이터가 많고 Using filesort, Using temporary 상태가 나온다면 무조건 최적화 필요)

      1. Using Index

        커버링 인덱스라고 하며 인덱스 자료구조를 이용하여 데이터를 추출

      2. Using where

        where 조건으로 데이터를 추출 (Type이 All 또는 Index와 같이 표현될 시 성능이 안좋다는 뜻)

      3. Using filesort

        데이터 정렬이 필요한 경우로, 메모리 혹은 디스크 상에서의 정렬을 모두 포함 (데이터 많을 시 성능 하락)

      4. Using Temporary

        쿼리 처리 시 내부적으로 Temporary 테이블이 사용됨

 

 

WHERE 조건문 주의사항


  • 묵시적 형변환에 조심하라
  • 무턱되고 함수를 사용하면 옵티마이저가 데이터 분포도 체크를 하지 못하기에 사용하지 말 것
// 함수 사용
SELECT * FROM timeline_item ti WHERE DATE_FORMAT(modified_at, '%Y%m%d') <= '20200402'

// 대안방안
SELECT * FROM timeline_item ti WHERE modified_at <= '2020-04-02'
  • Like 검색은 % 위치에 따라 다르게 수행 된다. %123, %125%의 경우에는 데이터 풀 스캔이 발생된다. 하지만 12312%와 같은 경우에는 인덱스를 사용해서 진행됨. 하지만 1%등과 같이 데이터 분포도에 비해 너무 추상적으로 %를 사용하게 될 경우에는 옵티마이저가 인덱스 사용과 데이터 풀스캔의 효율성 판단 했을 때 풀스캔이 효율적이다고 생각하고 데이터 풀스캔이 실행된다. 

댓글()

Junit5 Test Container사용하여 테스트 환경 구축하기 (인프런 백기선님 강의 정리)

web/Junit|2019. 12. 26. 15:21

도커와 테스트 (TestContainers)

테스트를 위해서는 운영과 동일한 형태의 개발 환경에서 테스트 하는 것이 중요하다. 하지만 매번 동일하게 환경을 구축할 수 없고 모든 개발 자들과 같은 환경을 맞추기도 쉽지 않다.

그래서 Docker를 이용해서 테스트마다 테스트를 위한 컨테이너를 실행시켜서 테스트하고 컨테이너를 제거해주면 좋은데 그런 기능을 TestContainer를 이용해서 가능하다. 실제로 이번에 이직한 회사에서 동일하게 테스트 환경을 사용하는 것을 봤다. 막연하게 그 기능을 사용할 수 있었지만 이번 Junit5 백기선님 강의를 들어서 확실하게 정리할 수 있어서 좋았다. 역시 듣길 잘했다. 꼭 들어보길 강추한다. 그럼 그 내용을 정리해보자.

테스트 컨테이너(Test Container) 라이브러리 추가 및 설정

https://www.testcontainers.org를 보고 테스트에 필요한 모듈의 라이브러리를 추가하면 된다. 나는 gradle을 사용하여 mysql을 사용해야 하기에 다음과 같이 추가했다.

testCompile "org.testcontainers:testcontainers:1.12.4"
testCompile "org.testcontainers:junit-jupiter:1.12.4"
testCompile "org.testcontainers:mysql:1.12.4"

그리고 생성된 mysql test container에서 사용할 설정 값들에 대한 설정이 필요한대 기존 설정과는 다르게 다음과 같이 별도의 jdbc url을 적어줘야한다. 이도 testcontainer 홈페이지에 모듈 설명에 나와있다.

spring:
  datasource:
    url: jdbc:tc:mysql:5.7.22://localhost:3306/test
    driver-class-name: org.testcontainers.jdbc.ContainerDatabaseDriver

    dbcp2:
      driver-class-name: com.mysql.jdbc.Driver
      test-on-borrow: true
      validation-query: SELECT 1
      max-total: 1

  jpa:
    show-sql: true

테스트 수행

라이브러리와 연결에 대한 설정을 모두 완료하였으면 테스트를 진행해야한다. 테스트 컨테이너를 이용해서 테스트를 진행하기 위해서는 클래스에 @TestContainers 어노테이션을 붙이고 불러들인 MysqlContainer에 어노테이션 @Container를 붙여주면 된다. 그리고 container가 테스트가 실행될 때 시작하고 종료되면 같이 끝나기 위해서 @BeforeAll과 @AfeterAll을 지정해준다.

package com.wedul.javajunit5studyjunit.docker;

import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import javax.transaction.Transactional;
import static org.assertj.core.api.Assertions.*;
/**
 * java-junit5-study
 *
 * @author wedul
 * @since 2019/12/24
 **/
@ActiveProfiles("test")
@SpringBootTest
@Testcontainers
class StudentServiceTest {

    @Autowired
    StudentService studentService;

    @Container
    static MySQLContainer mariaDBContainer = new MySQLContainer();

    @Test
    @DisplayName("학생 추가하기")
    @Transactional
    void create_student_test() {
        studentService.createStudent(Student.builder()
            .studentId(2L)
            .age(10)
            .name("wedul")
            .address("seoul jamsil")
            .build()
        );
    }

    @DisplayName("student 조회 테스트")
    @Test
    @Transactional
    void find_student_test() {
        studentService.createStudent(Student.builder()
            .studentId(2L)
            .age(10)
            .name("wedul")
            .address("seoul jamsil")
            .studentNickname("duri")
            .build()
        );
        Student student = studentService.getStudent(2L);
        assertThat(student).isNotNull();
    }

}

만약 별도록 MysqlContainer처럼 지정이 되어 있지 않은 컨테이너를 올려서 테스트 하고 싶을 때는 GenericContainers를 사용하여 공식 이미지를 다운받아서 사용할수도 있다. 자세한건 강의를 참조하면 좋다.

@Container
static GenericContainer genericContainer = new GenericContainer("mysql");

Docker container 값을 스프링 value로 사용하기

서비스로 올라간 Docker container에 속성을 spring에서 사용하고 싶을때는 Application의 설정값을 읽어서 사용할수 있도록 해주는 ApplicationContextInitializer를 구현하여 값을 application에 전달하여 사용할 수 있다.

우선 ApplicaionContextInitializer를 구현하여 컨테이너 속성 값을 environment에 넘겨주고 이를 스프링 value로 꺼내서 사용하면 된다.

@ActiveProfiles("test")
@SpringBootTest
@Testcontainers
@ContextConfiguration(initializers = StudentServiceTest.ContainerPropertyInitializer.class)
class StudentServiceTest {

    @Autowired
    StudentService studentService;

    @Container
    static MySQLContainer mySQLContainer = new MySQLContainer();

    @Value("${container.databaseName}")
    private String databaseName;

    @Test
    @DisplayName("학생 추가하기")
    @Transactional
    void create_student_test() {
        System.out.println(databaseName);
        studentService.createStudent(Student.builder()
            .studentId(2L)
            .age(10)
            .name("wedul")
            .address("seoul jamsil")
            .build()
        );
    }

    @DisplayName("student 조회 테스트")
    @Test
    @Transactional
    void find_student_test() {
        studentService.createStudent(Student.builder()
            .studentId(2L)
            .age(10)
            .name("wedul")
            .address("seoul jamsil")
            .studentNickname("duri")
            .build()
        );
        Student student = studentService.getStudent(1L);
        assertThat(student).isNotNull();
    }

    static class ContainerPropertyInitializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

        @Override
        public void initialize(ConfigurableApplicationContext applicationContext) {
            TestPropertyValues.of("container.databaseName=" + mySQLContainer.getDatabaseName())
                .applyTo(applicationContext.getEnvironment());
        }
    }

}

Docker Compose를 사용하여 테스트하기

매번 새로운 설정을 프로그램상이나 yml으로 정의하는건 너무 번거롭다. 그래서 생성할 컨테이너들을 한번에 기재해서 컨테이너로 올릴 때 사용하는 docker compose를 사용하면 편리하다. 간단하게 mysql 컨테이너를 올릴 yml을 만들고 테스트에서 사용해보자. docker-compose 파일을 읽어들일 때는 DockerComposeContainer를 사용해서 올리면 된다.

### docker-compose.yml 파일

version: '3.1'

services:
  maria:
    image: mariadb:latest
    restart: "always"
    ports:
    - "13306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=test
      - MYSQL_DATABASE=test
      - MYSQL_USER=wedul_dev
      - MYSQL_PASSWORD=test
// 사용방법
@Container
static DockerComposeContainer composeContainer =
    new DockerComposeContainer(new File("src/test/resources/docker-compose.yml"));

그리고 위에서는 host에서 연결할 port 13306을 지정하였으나 실제로는 지정하지 않고 하면 랜덤포트를 자유롭게 이용하기 때문에 더 좋을 것 같다. 왜냐하면 해보니까 내가 지정한 포트가 다른 개발자 컴퓨터에서는 이미 다른 컨테이너로써 운영중일 수도 있기 때문에 랜덤 포트를 사용하게 하는게 좋은 것 같다.

ports:
- "3306"

 

근데 사용해보니 단점이 있다. container_name과 같이 몇개의 docker-compose에서 제공하는 몇개의 부가 속성들을 제대로 읽어들이지 못하고 오류를 뱉어내기도 한다. 다음과 같이 에러가 발생되어서 지웠다 ㅜㅜ

docker-compose.yml has 'container_name' property set for service 'maria' but this property is not supported by Testcontainers, consider removing it

docker-compose를 매번 테스트할때마다 레포지토리에 dev-tool에 놔뒀다가 사용했었는데 확실히 편해지고 좋을 것 같다. 잘 활용해보자.

출처 : https://www.inflearn.com/course/the-java-application-test
github : https://github.com/weduls/junit5

댓글()

[공유] mysql/mariadb utf8mb4 언어셋 설명

데이터베이스/mysql|2018. 11. 6. 22:59

mysql 을 사용하다보면 utf8 캐릭터 셋을 자주 사용한다. 그 중에서도 여러 패키지가 존재하는데 잘 알지 못하고 사용하는 것 같다.


주로 사용하는 캐릭터 셋을 정리된 글을 공유해본다. 

핵심 부분만 정리했다. 자세한 내용은 아래 출저 페이지에가서 보면 좋은 공부가 될 것 같다.


- Mysql에서 주로 보는 Charset과 collection이 있는데 각각 문자집합과 정렬을 뜻한다.

- utf8은 1 ~ 4 바이트까지 저장할 수 있도록 설계된 가변 바이트 자료형이다.

- Mysql에서는 utf8을 3바이트 가변 자료형으로 저장한다. (chraset: utf8, collation: utf8_general_ci)

- emoji와 같은 문자는 4바이트이기 때문에 해당 필드에 저장하면 문제 발생

- Mysql 5.5.3부터 가변 4바이트가 제공되는 utf8mb4가 추가됨.

- 기존 mysql의 utf8_general_ci를 utf8mb4로 변경하는건 별 문제 없다. (같은 utf8)

- collation에서 사용되는 utf8_bin은 바이너리 저장 값 대로 정렬하고 utf8_general_ci는 라틴 계열 문자를 사람의 인식에 맞게 정렬하고 utf8_unicode_ci는 조금 더 특수하게 정렬됨.


출처

https://blog.lael.be/post/917

댓글()

Spring boot hibernate jpa에서 Auditing 사용 - update, create 시간 자동 변경

web/Spring|2018. 10. 3. 23:55

엔티티를 만들고 데이터를 삽입하고 조작할 때 create date와 last modified date를 별도로 업데이트 해주면서 관리하였다.

하지만 이번에 JPA를 공부하면서 별도의 작업 없이 JPA의 Auditing 기능을 사용하면 데이터를 삽입하고 수정할 때 자동으로 날짜를 수정하도록 할 수 있는 기능이 있는 것을 확인했다.


1. Configuration

JPA Auditing을 사용하기 위해서는 기능을 자동으로 활성화 해주는 어노테이션을 붙혀주면 된다. 처음에는 @Configuration을 사용하는 클래스에 함께 선언해주었는데 정상적으로 적용이 되지 않아서 @SpringBootApplication을 사용하는 곳에 적용했더니 성공적으로 적용되었다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.wedul.springboottest;
 
import com.wedul.common.exception.ValidationException;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.error.DefaultErrorAttributes;
import org.springframework.boot.web.servlet.error.ErrorAttributes;
import org.springframework.context.annotation.Bean;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.web.context.request.WebRequest;
 
import java.util.Map;
 
@SpringBootApplication
@EnableJpaAuditing
public class SpringboottestApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(SpringboottestApplication.class, args);
    }
 
    @Bean
    public ErrorAttributes errorAttributes() {
        return new DefaultErrorAttributes() {
 
            @Override
            public Map<String, Object> getErrorAttributes(WebRequest webRequest,
                                                          boolean includeStackTrace) {
                Map<String, Object> errorAttributes = super.getErrorAttributes(webRequest, includeStackTrace);
                Throwable error = getError(webRequest);
 
                // validatijon Exception에 경우 별도의 처리를 진행한 에러 데이터 추가
                if (error instanceof ValidationException) {
                    errorAttributes.put("errors", ((ValidationException)error).getErrors());
                }
                return errorAttributes;
            }
 
        };
    }
}
 
cs


2. Entity에 CreatedDate, LastModifiedDate

Auditing을 사용할 엔티티에는 몇가지 어노테이션이 사용된다.

먼저 설정을위해서 사용되는 @MappedSuperclass, @EntityListeners(AuditingEntityListener.class)이다. 첫 번재 어노테이션은 이후에 사용될 createdDate, modifiedDate와 같은 필드들을 컬럼으로 인식하게 도와주는 역할을 하고 두 번째 어노테이션은 해당 Entity에 Auditing기능을 포함한다라는 명시를 한다.

처음에는 필요한 Entity에만 CreatedDate, lastModifiedDate가 포함하도록 Entity마다 기재해주었다.하지만 많은 Entity에서 필요로 할 것 같고 필요할 때마다 새로 써주기가 비효율적인 것 같아서 추상클래스로 만들고 필요한 Entity에서 이를 상속받아서 사용하도록 하였다.



- 추상클래스

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
package com.wedul.common.dto;
 
import lombok.Getter;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
 
import javax.persistence.EntityListeners;
import javax.persistence.MappedSuperclass;
import java.time.LocalDateTime;
 
/**
 * 모든 Entity들의 상위 클래스가 되어 Entity들의 createdDate, modifiedDate를 자동으로 관리
 *
 * @author wedul
 * @since 2018. 8. 14.
 **/
@Getter
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class TimeEntity {
 
    @CreatedDate
    private LocalDateTime createdDate;
 
    @LastModifiedDate
    private LocalDateTime modifiedDate;
 
}
cs


- 상속받아서 사용하는 Product Entity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
package com.wedul.springboottest.product.dto;
 
import com.wedul.common.dto.BaseTimeEntity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
 
import javax.persistence.*;
import java.io.Serializable;
import java.sql.Timestamp;
 
/**
 * 상품 정보
 *
 * @author wedul
 * @since 2018. 08. 12
 **/
@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity // class (hibernate)
@Table(name = "product")
public class ProductDto extends TimeEntity implements Serializable {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long productId;
 
    @Column(nullable = false, unique = true)
    private String productName;
 
    @Column(nullable = false)
    private long price;
 
    public ProductDto(ProductRequestDto req) {
        this.productName = req.getProductName();
        this.price = req.getPrice();
    }
 
}
 
cs


테스트 코드를 작성하여 정상적으로 시간값이 들어가고 또 변경되는지 확인해보자.

1. 처음 데이터 삽입시 시간값 입력 테스트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
/**
 * Product Test
 *
 * @author wedul
 * @since 2018. 8. 14.
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest // rolleback 설정
@Rollback(value=true)
public class ProductTest {
 
    private MockMvc mockMvc;
    private final MediaType mediaType = new MediaType(MediaType.APPLICATION_JSON.getType(), MediaType.APPLICATION_JSON.getSubtype(), Charset.forName("utf8"));
 
    @Autowired
    ProductCtrl productCtrl;
 
    @Autowired
    ProductServiceI productService;
 
    @Before // before 클래스는 한번만 실해되고 before는 각 테스트마다 실행된다.
    public void beforeClass() {
        this.mockMvc = standaloneSetup(productCtrl).build();
    }
 
    @Test
    public void insertTest_reqestBody() throws Exception {
 
        // Mock Request Builder
        MockHttpServletRequestBuilder req =
                post("/api/product/new")
                        .content(CommonUtil.getJsonStrFromObject(new ProductRequestDto("i-mac", 2220011L)))
                        .contentType(mediaType);
 
        // 테스트
        MvcResult result = mockMvc.perform(req).andExpect(status().isOk()).andReturn();
        ResultDto resultDto = CommonUtil.getObjectFromJsonStr(result.getResponse().getContentAsString(), ResultDto.class);
 
        assertTrue(resultDto.isResult());
    }
}
cs


정상적으로 날짜값이 들어가 있는 것을 확인 할 수있다. 이제 수정 테스트를 진행해보자.


2. 수정 후 last modified date 시간 변경여부 테스트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void updateTest_requestBody() throws Exception {
    // Mock Request Builder
    MockHttpServletRequestBuilder req =
           put("/api/product/edit")
           .content(CommonUtil.getJsonStrFromObject(new ProductRequestDto(1,"macbook pro", 2312111L)))
           .contentType(mediaType);
 
    // 테스트
    MvcResult result = mockMvc.perform(req).andExpect(status().isOk()).andReturn();
    ResultDto resultDto = CommonUtil.getObjectFromJsonStr(result.getResponse().getContentAsString(), ResultDto.class);
 
    assertTrue(resultDto.isResult());
}
cs


업데이트 후에 정상적으로 modified_date만 변경된 것을 확인할 수있다.

좋은 기능인것같다. JPA를 공부하고 있는데 Mybatis보다 솔직히 불편하다 그런데 한번 잘 구축해놓으면 편하기는 하다. 그래도 무엇하나 변경된다면 결국 너무 손이 많이가고 러닝커브가 좀 심하다.

흠 더 사용해보고 실무에서 써봐야 이걸 왜 요새 사용하는지 더 이해할 수 있을 것 같다.

자세한 코드는 github 참조
https://github.com/weduls/spring_boot_test


댓글()
  1. Happy 2019.05.22 17:27 댓글주소  수정/삭제  댓글쓰기

    안녕하세요 글 잘보았습니다. :) 몇가지 궁금증이 생겨서 글을 쓰게 되었습니다.

    만일 특정 사용자가 서비스 로그인 이후에 로그아웃을 하고 다시 로그인을 하면 변경사항은 전혀 없습니다.

    그렇다면 임의로 제가 특정 컬럼을 변경시켜서 (더미컬럼 ?) 수정시간이 jpa auditing 에 의해서 변경되어야 하는건지. 궁금하네요.

    한편으로는 사용자에 해당하는 테이블에 현재 로그인 여부를 컬럼으로 추가해서 그렇게 수정하는 방법도 생각해볼 수 있습니다만 어떻게 하는게 효율적일지 몰라 이렇게 장문으로 글을 남깁니다.

    • Favicon of https://wedul.site BlogIcon 위들 wedul 2019.05.22 18:45 신고 댓글주소  수정/삭제

      안녕하세요!

      사용자의 마지막 로그인 시간을 별도의 컬럼으로 관리하시면 어떨까 싶습니다.

      그리고 현재 로그인중인 사용자를 테이블의 컬럼으로 관리하는건 힘들지 않을까 싶습니다.

Mysql 묵시적 형변환

데이터베이스/mysql|2018. 10. 3. 23:47

묵시적 형변환
조건절의 데이터 타입이 다를 때 우선순위가 높은 타입으로 형이 내부적으로 변환 되는 것. 
정수 > 문자열 순이며 만약 정수와 문자열이 비교가 되는 경우에는 둘중에 우선순위가 낮은 것이 변경된다. 

우리는 이렇게 자동으로 형변환 해주는 경우에 익숙해져 있다. 자바에서도 Integer와 int 두 개의 변수의 값을 묵시적으로 형변환 시켜주지만 이는 이펙티브 자바 책에서도 볼 수 있지만 성능저하의 원인이 된다고 한다.

Mysql도 예외가 아닌 것 같다. 

예를 들어 보자 아래와 같은 테이블을 생성 후 데이터를 삽입한다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 테이블 생성 
create table chagne_data (
    id int unsigned not null auto_increment,
    sub_id int unsigned not null,
    val varchar(64not null,
    date_d datetime not null,
    primary key(id)
);
 
# 랜덤 데이터 삽
insert INTO 
chagne_data (
        sub_id,
        val,
        date_d
    )
values
    (
        crc32(rand()),
        crc32(rand()) * 12345,
        date_add(now(), interval - crc32(rand()) / 5 second)
    );
INSERT INTO test.chagne_data(sub_id, val, date_d) SELECT sub_id, val, date_d FROM test.chagne_data;
cs

인덱스를 생성하고 
정수형 컬럼에 문자열 조건을 주어서 실행계획을 확인해보자.

1
2
3
4
5
# 인덱스 생성     
CREATE INDEX int_index ON test.chagne_data(sub_id);    
 
# 정수형에 문자열형 조건으로 추가 (정수가 더 우선순위가 높으므로 문제 없음)     
SELECT * FROM test.chagne_data where sub_id = '3689107608';
cs


별 문제 없다. 왜냐면 정수형 데이터가 우선순위가 더 높기 때문에 우측의 문자열 데이터가 변경되었기 때문에 인덱스를 정상적으로 사용했기 때문이다.


그렇다면 문자열 컬럼을 정수형 데이터로 조건을 주어서 데이터를 추출한다면 어떨까?

우선 정상적인 경우의 실행계획을 살펴보자.

1
2
3
4
5
# 인덱스 생성 
CREATE INDEX int_index ON test.chagne_data(val);    
 
# 문자열에 문자열로 조건을 주고 실행계획 확인 
SELECT * FROM test.chagne_data WHERE val = '10227816402120';
cs

이번에는 문자열 컬럼에 정수 데이터를 넣고 조회해보자. 

1
2
# 문자열에 정수형 조건 추가 (묵시적 형변환 발생)
SELECT * FROM test.chagne_data WHERE val = 10227816402120;
cs


인덱스 사용을 못하고 문제가 되는 것을 확인 할 수 있다.

특히 이런 문제가 발생하는 대표적인 부분이 mybatis에서 데이터를 #{}형태로 넣어서 사용할 때 문제 없이 실행되기 때문에 잘 몰라서 문제소지를 일으킬 수 있다.

항상 조심하자.


댓글()

Mysql 실행계획 설명

데이터베이스/mysql|2018. 10. 3. 23:43

프로그램의 성능을 높히기 위해서는 DB튜닝이 필요하다. 
Mysql에서 튜닝을 하기 위해서 제공하는 쿼리의 실행 계획에 대해 정리해보자.



Mysql의 데이터 처리 방식

우선 Mysql의 데이터 처리방식에 대해 정리해보자.

- Mysql은 단일 코어로 데이터를 처리하기 때문에 멀티코어로 scale out을 진행하는 것 보다 cpu 자체의 성능을 높히는 scale up을 하는 것이 더 효율적이다. 
- Oracle과 달리 mysql은 nested loop join 알고리즘만 사용한다.  
- Nested Loop Join은 선행 테이블의 검색 결과 값 하나하나 테이블 B와 조인하는 방식이다. 그래서 데이터 양이 적을 때는 상관이 없으나 데이터가 많은 테이블끼리 조인할 시 성능에 문제가 있을 수 있다. 그래서 내부적으로 join buffer를 사용하여 드라이빙 테이블에서 조인에 사용될 데이터를 찾아 join buffer에 채우고 조인 버퍼에서 조인 대상 B 테이블의 데이터를 스캔하면서 풀, 인덱스 스캔, 인덱스 범위 스캔등을 사용하여 테이블에 데이터와 조인한다. 



Mysql 쿼리 성능 진단 (for 최적화)
성능 진단을 위해서 사용하는 방법은 Explain을 사용하는 것이다.  

Explain을 사용해서 쿼리 실행계획을 살펴보면 하단에 그림과 같이 출력된다.


각 필드에 대한 설명은 다음과 같다. 
ID : Select 아이디 
Select_type : 참조 타입 
Table : 참조하는 테이블 
Type : 조인 타입 
Possible_keys : 데이터를 조회할 때 DB에서 사용할 수 있는 인덱스 리스트 
Key : 실제로 사용할 인덱스 
Key_len : 실제로 사용할 인덱스 길이 
Ref : Key 안의 인덱스와 비교하는 컬럼(상수) 
Rows : 쿼리 실행 시 조사하는 행 수 
Extra : 추가 정보 

이 필드중에 Select_type, type, Extra에 대해서만 잘 확인하면 좋은 쿼리를 작성할 수 있다.


Select_type 종류

구분
설명
예시
SIMPLE
UNION이나 서브쿼리가 없는 단순 SELECT를 의미한다. 
SELECT * FROM USER;
PRIMARY 
서브쿼리가 있을 때 가장 바깥쪽에 있는 SELECT 
SELECT * FROM (SELECT * FROM USER) t; 
DERIVED 
FROM절 안의 서브쿼리 
SELECT * FROM (SELECT * FROM USER) t; 
DEPENDENT SUBQUERY 
외부 쿼리와 상호 연관된 서브쿼리 
SELECT * FROM user u1 WHERE EXISTS ( 
    SELECT * FROM user u2 WHERE u1.user_id = u2.user_id 
);


Type
Type에는 system, const, ref... 등등 많이 있지만 성능상 문제가 되는 부분은 index, all이 두가지 타입이 문제다.
구분
설명
index 
인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 일반적으로 인덱스 풀스캔이라고 지칭 
all 
테이블 풀스캔으로 모든 부분을 스캔하는 것


Extra
쿼리 실행에 대한 추가적인 정보를 보여준다. 
하단의 대표적인 설명인 4가지중에서 특히 FileSort와 Using Temporary의 경우에는 쿼리 튜닝이 필요한 상태
구분
설명
Using Index 
인덱스를 이용해서 데이터를 추출
Using Where 
Where 조건으로 데이터를 추출.  (Type에서 All과 Index와 마찬가지로 성능에 문제) 
Using Filesort 
데이터의 정렬이 필요한 경우로써 데이터 양이 많을수록 성능에 직접적인 영향을 끼친다. 
Using Temporary 
내부적으로 Temporary Table을 사용하는 경우


Join 최적화 포인트
- Nested Loop 조인으로 되어있기 때문에 기준 테이블에서 조회되는 데이터양에 따라 연관 테이블의 데이터양이 결정되기 때문에 기준 테이블(왼쪽)의 데이터양을 줄이는 것이 관건. 
- Outer Join은 지양한다. 꼭 필요한 경우만 사용한다. 
- join시 조합 경우의 수를 줄이기 위해 복합 컬럼 index를 사용.


댓글()

Mysql Exists와 IN절 설명과 차이점

데이터베이스/mysql|2018. 10. 3. 23:34

두 개 모두 where절에 조건을 보고 결과를 걸러낼때 사용하는데 정리가 잘 안되서 정리해봤다.

Exists 
서브쿼리가 반화나는 결과값이 있는지를 조사한다.
단지 반환된 행이 있는지 없는지만 보고 값이 있으면 참 없으면 거짓을 반환한다.

1
SELECT * FROM sample1;
cs

1
SELECT * FROM sample2;
cs


두 개의 테이블중 조건에 맞는 Row만 추출된다.

1
SELECT * FROM sample1 s1 WHERE EXISTS(SELECT * FROM sample2 s2 WHERE s1.no = s2.no);
cs

그럼 반대로 조건에 맞지 않는 ROW만 추출하고 싶으면 어떻게 해야할까?

1
SELECT * FROM sample1 s1 WHERE NOT EXISTS(SELECT * FROM sample2 s2 WHERE s1.no = s2.no);
cs


IN 
집합 내부에 값이 존재하는지 여부 확인한다. 
실제로 존재하는 데이터의 값을 비교하기 때문에 Exists보다 속도가 느린경우가 있다.

두가지 경우로 사용이 가능하다. 

1.집합군

1
SELECT * FROM sample1 s1 WHERE NO IN (45);
cs


2.서브쿼리

1
SELECT * FROM sample1 s1 WHERE NO IN (SELECT NO FROM sample2 s2);
cs

반대로 포함되지 않은 경우를 추출하고 싶은경우에는 NOT IN 사용

1
SELECT * FROM sample1 s1 WHERE NO NOT IN (SELECT NO FROM sample2 s2);
cs


※ 주의
하지만 NOT IN에 경우에 조건에 맞는 데이터가 있어도 중간에 NULL이 존재하게되면 no rows selected가 나오게 되니 NVL 처리로 NULL 처리를 해야한다.

NULL이 포함된 sample3 테이블

쿼리 조회에 사용될 sample1 테이블


NOT IN절

1
SELECT * FROM sample1 s1 WHERE a IN (SELECT val FROM sample3 s3);
cs

sample1테이블에 null 값이 포함되어 있기 때문에 검색시 no rows selected 결과가 나온다.


NVL 처리 후 확인

1
SELECT * FROM sample1 s1 WHERE a Not IN (SELECT IFNULL (val, 'd'FROM sample3 s3);
cs




댓글()

Mysql의 서버엔진과 스토리지 엔진

데이터베이스/mysql|2018. 8. 10. 11:30

Mysql에는 두 가지 형태의 엔진이 존재한다. 

아래 그림에서 보면 하단에 길게 표시된 Pluggable 스토리지 엔진을 제외하고 위에 모든 부분이 서버엔진이다.


엔진별 특징 정리

서버엔진 (SQL Interface, Parser, Optimizer, Cache & Buffer)
- 클라이언트의 요청을 받아 SQL을 처리하는 DB 자체의 기능적인 역할을 수행 
- DB가 SQL을 이해할 수 있도록 쿼리를 파싱하고 메모리, 물리적 저장장치와 통신하는 기능을 수행
- 디스크와 직접적인 접근을 제외한 대부분의 역할 수행

스토리지 엔진
- 서버 엔진이 필요한 데이터를 물리적 장치에서 가지고 오는 역할을 수행
- 물리적 저장장치에서 데이터를 읽어오는 역할을 수행하고 플러그인 형식으로 여러 스토리지 엔진을 필요에 따라 추가 삭제 할 수 있다.
- 대표적 스토리지 엔진으로 MyISAM, InnoDB, CSV 등등이 있다. (8버전부터 MyISAM을 지원하지 않는것으로 알고 있다.)
- 다양한 사용 DBMS와의 호완성있게 동작하기 위해서 DB엔진에 최적화 되어있지는 않지만 범용성은 다른 DBMS보다 좋다. (카산드라나 스핑크스 등과도 연동 가능)


그럼 다른 DBMS와 다르게 유동적으로 플러그인처럼 변경이 가능한 Storage Engine에는 어떤 특징이 있을까 조금 더 알아보자.

대표적으로 존재하는 스토리지 엔진은 MyIsam과 InnoDB 스토리지 엔진이 존재한다. 
MyIsam 엔진
- 데이터는 디스크에서 인덱스와 키만 메모리에 적재해서 사용한다. 
- 트랜잭션을 지원하지 않고 테이블 단위 Lock 을 지원한다. (특정 테이블의 여러 세션에서 데이터를 변경하려 하면 성능저하. -> Lock 기준에 테이블이기 때문에 무조건 대기)
- 저사양 서버를 위해 고안된 방법으로 데이터 사이즈와 키, 인덱스를 압축해서 사용한다. 그렇기 때문에 인덱스가 필요한 검색기능이 추가된 테이블을 사용하기에 부적절

InnoDB 스토리지 엔진
- 트랜잭선이 지원, Concurrency control이 가능하고 행 단위 잠금으로 데이터 변경 작업 시 다른 사용자가 테이블에 접근할 수 있다. (MyIsam에 한계를 넘어선다.)
- 메모리에 인덱스와 데이터가 모두 적재되기 때문에 메모리 버퍼 커기가 DB 성능에 많은 영향을 끼치기 때문에 MyIsam보다 더 고사양의 서버를 요구한다.



다음에는 MySQL 성능 높이기 위한 프로파일링에 대해 공부해보자.



댓글()