아야로
910
2018-09-14 18:02:22 작성 2018-09-14 18:11:16 수정됨
9
305

ORDER BY 사용시 지나치게 느려집니다


안녕하세요. 현재 아래와 같은 댓글 테이블이 있습니다. 여기에 목록 조회 쿼리를 수행하는데요,



ORDER BY없이 아래의 쿼리 수행시 2ms(매우 빠름)

[쿼리]

SELECT
	CMT_SQ_PK,
	CMT_CLS_FK,
	CMT_CLS_ORD,
	CMT_CLS_DPTH,
	CMT_REG_DT,
	CMT_MOD_DT,
	CMT_SCRT_FL,
	CMT_BODY
FROM
	CMT_TB AS C
WHERE
	CMT_GRP_FK = 1

[실행계획]

id |select_type |table |partitions |type |possible_keys                                 |key                                           |key_len |ref   |rows   |filtered |Extra |
---|------------|------|-----------|-----|----------------------------------------------|----------------------------------------------|--------|------|-------|---------|------|
1  |SIMPLE      |C     |           |ref  |FK_CMT_TB_CMT_GRP_FK_CMT_GRP_TB_CMT_GRP_SQ_PK |FK_CMT_TB_CMT_GRP_FK_CMT_GRP_TB_CMT_GRP_SQ_PK |4       |const |239975 |100      |      |



이번엔 ORDER BY 걸고 아래의 쿼리 수행시 2.197초 가 나옵니다.

[쿼리]

SELECT
	CMT_SQ_PK,
	CMT_CLS_FK,
	CMT_CLS_ORD,
	CMT_CLS_DPTH,
	CMT_REG_DT,
	CMT_MOD_DT,
	CMT_SCRT_FL,
	CMT_BODY
FROM
	CMT_TB AS C
WHERE
	CMT_GRP_FK = 1
ORDER BY
	CMT_CLS_FK DESC, CMT_CLS_ORD ASC

[실행계획]

id |select_type |table |partitions |type |possible_keys                                 |key                                           |key_len |ref   |rows   |filtered |Extra                                 |
---|------------|------|-----------|-----|----------------------------------------------|----------------------------------------------|--------|------|-------|---------|--------------------------------------|
1  |SIMPLE      |C     |           |ref  |FK_CMT_TB_CMT_GRP_FK_CMT_GRP_TB_CMT_GRP_SQ_PK |FK_CMT_TB_CMT_GRP_FK_CMT_GRP_TB_CMT_GRP_SQ_PK |4       |const |239975 |100      |Using index condition; Using filesort |


mariaDB - 댓글 40만 건 에서의 결과 입니다.

댓글이 늘어날수록 점점 느려지더니 ORDER BY 유무에 따라 천 배 가까이 차이나기 시작하는 것도 이해가 안되고,

정렬에 6초, 8초씩 사용하기 시작하는게 너무 느려서 글을 올리게 되었습니다.

속도를 높일 방법이 있는지 조언 좀 부탁드립니다.

감사합니다.



=======

대략 이런식으로 댓글 정렬하는 쿼리입니다.


인덱스 정보 추가


1
1
  • 답변 9

  • order by 자체가 원래 좀 느리긴 합니다만 글 내용으로만 보기에는 인덱스 못타고 있는 것 같은데요...

    https://blog.naver.com/ez_/140129818967 참조하셔서 쿼리 튜닝하셔야 할듯.....


    explain으로 index여부와 등등 확인해보세요... 그 후 느려지는 부분 하나씩 하나씩 잡아서 튜닝하시는 수밖에 없습니다...

    1
  • 하루를 즐겁게
    327
    2018-09-14 18:10:21

    쿼리의 문제가 아니라면 index 문제입니다.


    아래 참조해보세요 ^^


    http://database.sarang.net/?inc=read&aid=32685&criteria=oracle&subcrit=&id=&limit=20&keyword=long&page=3

    0
  • 최자바
    663
    2018-09-14 18:14:02

    PK로 order by 하세요

    속도는 인덱스 문제입니다 거의다

    0
  • 하두
    7k
    2018-09-14 18:14:04 작성 2018-09-14 18:15:03 수정됨

    미리 정렬된 index생성한후에 hint로 접근하심  됩니다.

    그러면 정렬부하없이 수행됩니다.

    0
  • 앙앙이
    3k
    2018-09-14 20:28:50
    아직까지 MaraiDB 는 인덱스 구성시 asc만 지원합니다. 하여 복합 인덱스 구성시 하나는 asc 다른 또하나는 desc 로 설정할수 없습니다.
    하여 order by 에 인덱스 적용 될려면 모두 asc 이거나 desc 가 되어야 합니다.
    0
  • LichKing
    11k
    2018-09-14 20:29:03

    http://multifrontgarden.tistory.com/149?category=471242

    http://multifrontgarden.tistory.com/151?category=471242


    maria db도 mysql랑 거의 비슷합니다. 다른분들도 많이 말씀해주셨는데 인덱스없는 컬럼으로 정렬을 하는 문제일것같네요. 실행계획 보면서 인덱스가 걸려있는 다른 컬럼으로 정렬해도 되는지 확인해보시고, 그게 안된다면 정렬컬럼에 인덱스를 주는것도 고려해볼만 합니다.

    0
  • rezigrene
    425
    2018-09-14 21:46:11

    40만건 정렬이 당연히 공짜로 되진않습니다.

    아래 순서로 복합인덱스 생성해 주시면 많이 개선될겁니다.

    desc 까지 일치하도록 생성한다면 훨씬빠를겁니다.

    CMT_GRP_FK,CMT_CLS_FK,CMT_CLS_ORD
    0
  • 아야로
    910
    2018-09-15 14:04:50
    감사합니다. 하나하나 체크해보겠습니다.
    멀티채택이 없는게 너무 아쉽네요 ㅎㅎ;
    0
  • 아야로
    910
    2018-09-18 13:12:38

    앙앙이

    주신 정보 덕분에 아래와 같이 해결하였습니다.

    ㄱ. CMT_CLS_FK 값을 모두 음수로 변경

    UPDATE XMT_TB
    set CMT_CLS_FK=(-1 * CMT_CLS_FK);


    ㄴ. ASC-ASC 복합 인덱스 생성

    CREATE INDEX IX_CMT_TB ON CMT_TB (CMT_CLS_FK, CMT_CLS_ORD);


    ㄷ. DESC -> ASC 변경

    SELECT
    	CMT_SQ_PK,
    	CMT_CLS_FK,
    	CMT_CLS_DPTH,
    	CMT_BODY
    FROM
    	CMT_TB2
    WHERE
    	CMT_GRP_FK = 1
    ORDER BY
    	CMT_CLS_FK ASC, CMT_CLS_ORD ASC


    ㄹ. 결과

    앞 쪽 3천건 까지는 2~10ms 소요.

    그 이후 2~6초 소요.

    90%의 요청이 앞쪽 1, 2페이지 조회에 쏠려있는 만큼 만족스런 결과.


    감사합니다 ^^



    1
  • 로그인을 하시면 답변을 등록할 수 있습니다.