문제상황을 겪은점은, 세미나 목록 200만건을 넣은뒤 세미나의 참여가격 별로 검색을 해주는 기능을 만들고 싶었습니다.
해당 조건에서 단순하게 페이징 기능을 사용하여 SQL을 구현하고 테스트했는데, 단순히 200만건을 조회하는데도 약 1초 이상이 걸리고 있었습니다. 이러한 문제사항을 DB의 Indexing 기능을 활용하여 해결해보려고 하였고, 해당사항을 어떻게 해결하려했는지, 어떤 문제점을 발견했는지에 대한 내용을 남겨보겠습니다.
1. Seminar Entity 구조
Seminar 구조는 아래와 같습니다. 우리가 이번에 정렬조건을 사용할 것은 seminar_price 부분입니다.
@Entity
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Getter
@ToString(exclude = {"member_seminar_list"})
public class Seminar extends BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long seminar_no;
@Column(length = 100, nullable = false, unique = true)
private String seminar_name;
@Column(length = 500)
private String seminar_explanation;
@Column
private Long seminar_price;
@Column(nullable=true)
private LocalDateTime del_dt;
}2. 인덱스 삽입
- 제가 이번에 조건으로 사용할 것은 가격별로 세미나를 나눠볼것이므로 seminar_price를 Index에 넣습니다.
- 추가적으로 말씀드리자면, PK 값인 seminar_no 는 이미 Clustered Index에 포함되어 있습니다.
CREATE INDEX seminar_price_idx ON seminar (seminar_price);2. 일반적인 페이징 처리 쿼리
SELECT s.*
FROM seminar s
where s.seminar_price = 10000
order by s.seminar_no
limit 10 OFFSET 300000;- 이 쿼리의 실행시간은 488 ms 가 나옵니다.
- 왜 그럴까요? 저희는 Indexing을 넣어놓았지만, 위의 코드의 SELECT *절에 모든것을 조회하므로 커버링 인덱스가 적용되지 않고 있기 때문입니다.
- 아래 실행게획을 통해 Covering Indexing가 적용되지 않는 것을 확인할 수 있습니다. ( Condition Push Down 이 진행됨 )
여기서 추가로 궁금했던점은, 위의 페이징 처리 쿼리에 where 동등조건이 아닌 상태에서 order by를 어떻게하면 같이 인덱싱을 할 수 있을까가 궁금했습니다. 해당 내용은 5번 과정에서 나옵니다.
이번에는 커버링 인덱스를 적용해보겠습니다.
SELECT s.seminar_no
FROM seminar s
where s.seminar_price = 10000
order by s.seminar_no
limit 10 OFFSET 300000;- 이 쿼리의 실행시간은 53ms가 걸리고 있습니다. 커버링 인덱스를 적용함으로써 약 10배나 빨라졌습니다.
- 하지만 이 쿼리 자체로 Paging에 사용하기에는 무리가 있습니다. 이유는, SELECT 절에 단순히 s.seminar_price 가격만 나오면 해당 쿼리는 페이징으로써의 역할을 하지 않습니다.
- 실행계획은 아래와 같습니다.
- 여기서 Using where 구문이 나와서 어떤 의미일지 찾아보았는데 https://stackoverflow.com/questions/62947430/what-does-mysql-explain-extra-using-where-really-mean 글을 확인해보니 'Using where'가 일관적인 기준을 가지고서 발견되는것이 아니라고합니다. 그래도 이 글에서는 Indexing을 완료한 후 조사할 행에 대해 추가적인 필터링을 사용할떄 Using where가 사용되고있다고 말하고있습니다.
여기서 추가로 궁금했던점은, 위의 페이징 처리 쿼리에 where 동등조건이 아닌 상태에서 order by를 어떻게하면 같이 인덱싱을 할 수 있을까가 궁금했습니다.
SELECT s.seminar_no
FROM seminar s
where s.seminar_price > 10000
order by s.seminar_price, s.seminar_no
limit 10 OFFSET 300000;- 우선 결과를 말해보면 이 쿼리는 WHERE (조건검색) + ORDER BY 를 같이 사용함에도 인덱싱이 적용됩니다.
- 하지만, 이렇게 할경우 가격이 자동으로 오름차순으로 정렬된 값이 나옵니다. (그 조건을 원한다면 이와 같이 진행하면 됩니다. 여기서 포인트는 where을 동등조건을 사용하지 않으면서 order by 를 사용하려면 index의 순서대로 컬럼값들을 적용하면 된다는것입니다. ) 관련 정보는 https://weicomes.tistory.com/191 글에 잘 정리되어있습니다.
- 또한 추가로, 저희는 seminar_no와 함꼐 Multi Column Index를 적용하지 않았는데 어떻게 사용가능한지 궁금할 수 있습니다.
- seminar_no는 Primary Key로써 CLustered Index이기에, 각 테이블 당 1개의 Clustered Index는 모든 Non-Clustered Index와 연관되어있습니다. 즉, Non-Clustered Index의 정렬되어있는 값이 Clustered Index의 값을 포함하고 있다는 의미입니다.
- https://weicomes.tistory.com/191 이유는 옆의 블로그에서 찾았습니다.
- 내용을 살펴보면, WHERE 조건 절에서 범위 조건의 비교가 사용되는 방법은 인덱스의 순서를 보장해야한다고합니다.
- 현재 where 조건 절에 seminar_price가 작동되고있고, order by에도 해당 seminar_price 인덱스 순서가 where절에 사용된 대로 seminar_price가 먼저 나오고있으므로 사용이 가능합니다.
- 하지만, 여기서 desc는 적용할 수 없었습니다. 즉 제가 원하는대로 정렬이 안되었습니다.
- create index seminar_price_seminar_no_desc_idx on seminar(seminar_price, seminar_no desc); 이와 같이 seminar_no desc를 같이 적용해보려고했지만, 적용되지 않았습니다.
- desc 를 적용하면 filesort가 되어 제가 Indexing은 적용되지만 제가 원하는 대로 정렬이 되지 않아 사용할 수는 없었지만, WHERE + ORDER BY 에서 Indexing의 사용조건에 대해 알 수 있었습니다.
- mysql 8.0 이전 버전까지는 지원하지않고, mysql 8.0 버전 이후부터는 적용되며 인덱스 컬럼 중 특정 컬럼만 DESC가 되지 않고 인덱스 컬럼 전체를 asc 혹은 desc 스캔을 하는 방법만 존재합니다.
3. 커버링 인덱스를 활용하여 페이징에 적용해보기
우리는 서브쿼리 join 문을 활용하여 커버링 인덱스를 페이징에서도 사용할 수 있게 해보겠습니다.
select *
from seminar as s1
join (select s2.seminar_no
from seminar s2
where s2.seminar_price = 10000
order by s2.seminar_no
limit 10 OFFSET 300000
) as temp on temp.seminar_no = s1.seminar_no
order by s1.seminar_no;- 이 쿼리의 작동시간은 53 ms 입니다.
- 우리가 원하는대로 값을 모두 가져올 수도 있고, 정렬할 수도 있으면서 커버링 인덱스까지 사용했습니다.
- 실행계획은 다음과같습니다.
- 아래 첫행의 Using fileSort는 order by s1.seminar_no를 사용하면서 발동됩니다. (이 부분이 조금 아쉽습니다.)
- JOIN 문 안의 구문이 Using where, Using index로 커버링 인덱스가 작동합니다.
- 여기서 seminar_no는 Index에 넣지않았는데 왜 그럴까? 라는 궁금증이 생길 수 있는데 seminar_no 는 Primary key로써 Clustered Index에 이미 포함되어있기에 seminar_price_index를 통해서 데이터블록에 접근하지않고 바로 접근 가능하기에 그렇습니다.
추가로 아까 WHERE 조건 동등절을 사용하지 않았을경우도 구현해봅니다.
select *
from seminar as s1
join (select s2.seminar_no
from seminar s2
where s2.seminar_price > 10000
order by s2.seminar_price, s2.seminar_no
limit 10 OFFSET 300000
) as temp on temp.seminar_no = s1.seminar_no;
order by s1.seminar_no;- 이 쿼리의 작동시간은 52 ms 입니다.
- 오히려 WHERE 조건 동등조건을 사용할때보다 줄었습니다.
- 이유는 밖에 Parent Sql의 order by가 fileSort가 사용되지 않기 떄문입니다.
- 실행계획입니다.
4. Querydsl 에서 직접 사용해보기
처음에 일반적인 페이징 쿼리를 만들었었습니다. 해당 쿼리를 Querydsl로 사용해보겠습니다.
public List<SeminarPageResultDTO> pagingSeminarWithSeminar_Price(long seminar_price, int pageNo, int pageSize){
QSeminar seminar = QSeminar.seminar;
List<SeminarPageResultDTO> seminarPageResultDTOList = queryFactory
.select(Projections.fields(SeminarPageResultDTO.class,
seminar.seminar_no,
seminar.seminar_name,
seminar.seminar_explanation,
seminar.seminar_price
))
.from(seminar)
.where(seminar.seminar_price.eq(seminar_price))
.orderBy(seminar.seminar_no.desc())
.limit(pageSize)
.offset(pageNo * pageSize)
.fetch();
return seminarPageResultDTOList;
}테스트를 진행해보겠습니다.
@DisplayName("testPagingSeminarWithSeminar_Price test")
@Test
public void testPagingSeminarWithSeminar_Price(){
int pageNo = 30000;
long seminar_price = 10000;
int pageSize = 10;
List<SeminarPageResultDTO> seminarPageResultDTOList = seminarQuerydslRepository.pagingSeminarWithSeminar_Price(seminar_price ,pageNo, pageSize);
for(int i=0;i<seminarPageResultDTOList.size();i++){
System.out.println("cnt:"+i+" "+seminarPageResultDTOList.get(i).toString());
}
}
- 1sec 193ms 가 걸립니다.
이번에는 Querydsl에서는 서브쿼리를 사용할 수 없으므로 Query를 2개로 나누어서 진행해보겠습니다.
public List<SeminarPageResultDTO> pagingSeminarWithCoveringIndexWithSeminar_Price(long seminar_price, int pageNo, int pageSize){
QSeminar seminar = QSeminar.seminar;
List<Long> ids = queryFactory
.select(seminar.seminar_no)
.from(seminar)
.where(seminar.seminar_price.eq(seminar_price))
.orderBy(seminar.seminar_no.asc())
.limit(pageSize)
.offset(pageNo*pageSize)
.fetch();
if(CollectionUtils.isEmpty(ids)){
System.out.println("EMTPY why?");
return new ArrayList<>();
}
List<SeminarPageResultDTO> seminarPageResultDTOList = queryFactory
.select(Projections.fields(SeminarPageResultDTO.class,
seminar.seminar_no,
seminar.seminar_name,
seminar.seminar_explanation,
seminar.seminar_price
))
.from(seminar)
.where(seminar.seminar_no.in(ids))
.orderBy(seminar.seminar_no.desc())
.fetch();
return seminarPageResultDTOList;
}테스트코드를 작성합니다.
@DisplayName("testPagingSeminarWithCoveringIndexWithSeminar_Price")
@Test
public void testPagingSeminarWithCoveringIndexWithSeminar_Price(){
int pageNo =30000;
long seminar_price = 10000;
int pageSize = 10;
List<SeminarPageResultDTO> seminarPageResultDTOList = seminarQuerydslRepository.pagingSeminarWithCoveringIndexWithSeminar_Price(seminar_price,pageNo, pageSize);
for(int i=0;i<seminarPageResultDTOList.size();i++){
System.out.println("cnt:"+i+" "+seminarPageResultDTOList.get(i).toString());
}
}
- 443ms 가 걸렸습니다. 이전과 비교하면 약 2.7배 빨라졌습니다.
- 커버링 인덱스를 활용함으로써 약 0.8 초를 단축하게되었습니다.
- 또한 이때 유의해야할점은 커버링 인덱스를 사용할경우 asc 를 사용하여 정렬을 진행하므로 정렬의 순서가 처음의 페이징과 다릅니다. 인덱스에서 desc를 적용하여 인덱스 정렬이 작동하지 않았습니다.
5. 추가적으로. Like를 사용하여 키워드 검색할시에는 한계가 존재했습니다.
아래와 같이 만약에 seminar_name 별로 검색을 하고 싶다는 가정을 해봅니다.
우선, Where 조건을 비동등조건으로 사용하면서 Index를 적용하려면 '%' 가 문자앞에 오면 안됩니다.
이유는, '%' 가 앞에있으면 인덱스 별로 정렬한 seminar_name을 이진탐색이 불가능하기에 그렇습니다.
그렇기에, 해당 내용을 포함하는 검색구문을 만들떄는 이러한 커버링 인덱스 전략을 사용하기 어렵습니다.
이 경우에는 따로 조건문을 파서 적용시키거나 Elastic Search 를 활용하여 진행하는 방안이 있는 것으로 보입니다.
LIKE를 사용하여 반쪽짜리 검색 SQL도 만들어보았습니다.
일반적인 페이징입니다.
SELECT s.*
FROM seminar s
where s.seminar_name like 'Seminar%'
order by s.seminar_no
limit 10 OFFSET 1800000;위의 페이징을 커버링 인덱스로 감쌀려면
아래와 같이 진행해야 Covering Index가 실행됩니다.
EXPLAIN
SELECT s.seminar_no
FROM seminar s
where s.seminar_name like 'Seminar%'
order by s.seminar_name
limit 10 OFFSET 1800000;의문이 갔던점은, 제가 이해했던 바로는 WHERE 구문에 비동등조건일시에는 인덱스의 순서를 그대로 맞추어서 적용해주면 작동한다고 알고있었어, 위의 쿼리에서 올바르게 작동을 예측했습니다.
추가로, 아래의 코드를 보겠습니다. order by 절을 보면, seminar_name, seminar_no가 순서대로 들어가 있고 where 절에는 비동등조건으로 seminar_name이 들어가있습니다. 여기서 명시적으로 seminar_name과 seminar_no를 multi column index로 생성하지 않았으나 논-클러스터링 Index로 선언되어있는 seminar_name index가 s.seminar_no와도 함꼐 연관되어 잘 작동할 줄 알았습니다. 하지만 Using Index가 일차적으로 실행되나, order by s.seminar_no에서 다시 fileSort를 진행하고 있습니다. 해당 원인을 명확하게 이해하지는 못하였지만, Clustered Index와 Non-Clustered Index가 제가 이해한대로 연결되어있는 상태가 아닌 것 같습니다. (물론, 명시적으로 새로 seminar_name과 seminar_no의 인덱스를 만들어 테스트해보기도했습니다.)
EXPLAIN
SELECT s.seminar_no
FROM seminar s
where s.seminar_name like 'Seminar%'
order by s.seminar_name, s.seminar_no
limit 10 OFFSET 1800000;JOIN 문 안의 sub query가 온전히 커버링 인덱스가 적용되지 않으므로 이 쿼리 또한 Indexing이 완전히 적용되지 않습니다.
select *
from seminar as s1
join (select s2.seminar_no
from seminar s2
where s2.seminar_name like 'Seminar%'
order by seminar_name
limit 10 OFFSET 1800000
) as temp on temp.seminar_no = s1.seminar_no
order by s1.seminar_no desc;
도움받은글
https://jojoldu.tistory.com/529