API 성능 향상을 위해서, SQL 튜닝 관련 블로그 글을 읽다가 IS NOT NULL, IS NULL을 사용하면 인덱스를 사용해도, 전체 table을 full scan한다는 말을 보게 되었습니다.
저희 서비스에서는 User Table에서 탈퇴 날짜인 deleted_date를 null로 하고, 탈퇴하면 해당 column을 업데이트하는 방식으로 탈퇴여부를 확인하고 있었습니다.
모든 API에서는 해당 회원이 유효한 회원인지 확인하기 위해서 탈퇴한 회원인지 확인하는 과정이 모두 들어갑니다.
아래의 코드는 스크랩을 검색하는 서비스 로직입니다.
이처럼 User user = userService.validateUser(email);
으로 해당 회원의 유효성을 검사를 하는 과정이 있습니다.
@Transactional
public Slice<GetScrapResponse> searchScraps(String email, String keyword, Pageable pageable) {
User user = userService.validateUser(email);
Slice<Scrap> scrapSlice = scrapRepository.searchKeywordInScrapOrderByCreatedDateDesc(user,
keyword, pageable);
return scrapSlice.map(scrap -> GetScrapResponse.of(scrap,
memoRepository.findMemosByScrapAndDeletedDateIsNull(scrap)));
}
@Transactional
public User validateUser(String email) {
return userRepository.findByEmailAndDeletedDateIsNull(email).orElseThrow(
() -> new NotFoundException(ErrorCode.NOT_EXISTS_MEMBER)
);
}
즉, users 테이블에서 Email이 유저의 email 인 지와 deletedDate is null인지를 확인하는 SQL 쿼리가 나가게 됩니다.
그러면 해당 SQL 쿼리가 유저가 10000000(1천만)명인 경우에 성능이 어떻게 되는지와 해당 SQL 쿼리를 개선하기 위해서 튜닝하는 과정까지 알아보겠습니다.
우선, SQL 쿼리의 성능의 차이를 확실하게 비교하기 위해서는 데이터가 최대한 많이 있는 것이 좋습니다.
따라서, 유저가 10000000(1천만)명인 경우의 SQL의 성능을 확인하기 위해서, 아래의 더미데이터들을 생성하겠습니다.
이때의, 프로시저를 이용해서 생성해주었습니다.
참고 자료 : https://velog.io/@jkijki12/MySql-더미데이터-생성하기
DELIMITER $$
DROP PROCEDURE IF EXISTS insertUserDummyData$$
CREATE PROCEDURE insertUserDummyData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 10000000 DO
IF i <= 1000000 THEN
INSERT INTO users(user_id, created_date, modified_date, email, name, profile_url, provider, role)
VALUES(i, now(), now(), concat(i,'@gmail.com'), concat('이름', i), concat('profile_url', i), 0, 'USER');
ELSEIF (i<= 3000000 AND i >= 1000001) THEN
INSERT INTO users(user_id, created_date, modified_date, deleted_date, email, name, profile_url, provider, role)
VALUES(i, now(), now(), now(), concat(i,'@gmail.com'), concat('이름', i), concat('profile_url', i), 0, 'USER');
ELSEIF (i<= 5000000 AND i >= 3000001) THEN
INSERT INTO users(user_id, created_date, modified_date, email, name, profile_url, provider, role)
VALUES(i, now(), now(), concat(i,'@gmail.net'), concat('이름', i), concat('profile_url', i), 0, 'USER');
ELSEIF (i <= 6000000 AND i>= 5000001) THEN
INSERT INTO users(user_id, created_date, modified_date, deleted_date, email, name, profile_url, provider, role)
VALUES(i, now(), now(), now(), concat(i,'@gmail.com'), concat('이름', i), concat('profile_url', i), 0, 'USER');
ELSEIF (i<= 8000000 AND i >= 6000001) THEN
INSERT INTO users(user_id, created_date, modified_date, email, name, profile_url, provider, role)
VALUES(i, now(), now(), concat(i,'@gmail.com'), concat('이름', i), concat('profile_url', i), 0, 'USER');
ELSE
INSERT INTO users(user_id, created_date, modified_date, deleted_date, email, name, profile_url, provider, role)
VALUES(i, now(), now(), now(), concat(i,'@gmail.com'), concat('이름', i), concat('profile_url', i), 0, 'USER');
END IF;
SET i = i + 1;
END WHILE;
END$$
DELIMITER $$
위의 사진에서 더미 데이터들이 성공적으로 생성되었음을 확인할 수 있습니다.
MySQL Explain은 '데이터 베이스가 데이터를 찾아가는 일련의 과정을 사람이 알아보기 쉽게 DB 결과 셋으로 보여주는 것'입니다.
따라서, MySQL Explain 실행계획을 활용하여 기존의 쿼리를 튜닝할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수립 등과 같이 성능 최적화에 대한 전반적인 업무를 처리할 수 있습니다.
아래의 SQL 쿼리를 MySQL Explain으로 데이터 베이스의 성능 및 쿼리를 살펴보겠습니다.
위의 SQL 쿼리를 아래의 EXPLAIN으로 나타내었습니다.
EXPLAIN
SELECT
users.user_id,
users.created_date,
users.deleted_date,
users.modified_date,
users.email,
users.name,
users.profile_url,
users.provider,
users.role,
users.uuid
from
users
where
users.email = '7000000@gmail.com'
and users.deleted_date is null
💡 쿼리 튜닝 시 반드시 체크해야 할 중요한 정보입니다.
접근 방식
위의 MySQL Explain을 실행하면, 아래와 같이 결과가 나오게 됩니다.
위의 결과표를 각 항목에서 SQL 성능에서 중요한 항목인 type과 rows, extra를 해석해보겠습니다.
MySQL Explain 결과값을 보고 나서, 생각한 최적화 방법입니다.
따라서, 실제로 최적화되었는지 알 수 없지만, 다음 블로그글으로 실제로 최적화되는지 확인해보겠습니다.
@Getter
@NoArgsConstructor
@Entity
@ToString(exclude = "scrapList")
@Table(name = "users")
public class User extends BaseTimeEntity implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "user_id")
private Long id;
@OneToMany(mappedBy = "user")
private List<Scrap> scrapList = new ArrayList<>();
@Column(length = 100, nullable = false)
private String name;
@Column(length = 320, nullable = false)
private String email;
@Column(length = 2083, nullable = false)
private String profileUrl;
@Column(nullable = false)
private Provider provider;
private String uuid;
@Enumerated(EnumType.STRING)
@Column(nullable = false)
private Role role;
}