MySQL로 카카오톡같은 채팅을 만들고 있는데, LIMIT개수에 따라서 쿼리가 엄청나게 느려집니다


안녕하세요 . 카카오톡처럼 최근 대화를 보다가 위로 올리면 과거의 대화가 나오는 식의 채팅을 구현하고 있는데요.

SELECT * FROM chat WHERE user_id=유저아이디  order by chat_id desc limit 10
SELECT * FROM chat WHERE user_id=유저아이디  order by chat_id desc limit 100

위와 같은 쿼리로 뽑아낸 다음 저 상태에서 서버에서 다시 반대로 정렬하여 프론트쪽에 주도록 구현했습니다.

문제는 위 두개의 쿼리의 속도가 엄청나게 차이가 납니다.

user_id는 인덱스 걸려있고, chat_id는 Primary Key이고, Auto Increasement가 적용되어 있습니다.

위에꺼(limit 10)는 무려 0.6초정도 걸리구요. 밑에꺼(limit 100)는 0.016초 걸립니다.(사실상 거의 바로됨)

테이블의 총 row는 195만개정도이구요.

대체 왜 limit 100일때는 정상인거고, 오히려 더 적게 뽑아냈을 때는 엄청나게 많은 시간이 걸리는 걸까요? 또한, 이런 경우 쿼리 튜닝을 어떻게 해야 좋을까요..?

저 단순작업 하나가 0.6초나 걸린다면 서버 내의 다른 작업들에도 영향을 줄 것이 분명하기 때문에 이를 방치하기는 어려울 것 같습니다 ㅜㅜ

참고가 될 수 있도록 몇가지 정보를 덧붙이자면

0.6초가 걸리는 쿼리에서 limit 10으로 뽑아냈을 때 상위 10개의 chat_id를 뽑아내면 다음과 같습니다.

1826208
1649834 
1649806 
1649805 
1649803 
1647423 
1646364 
1387224 
1379463 
1379461 

limit 100번째 chat_id는 115만정도이구요.

limit 100으로 explain을 하면

SIMPLE  | chat |  | ref | user |    user |  4|  const| 8193| 100.00 | Using index condition; Using filesort

이렇게 나오고,

limit 10으로 explain을 하면

SIMPLE  |chat | | index | user | PRIMARY    | 8 | 2376 | 0.42 | Using where

이렇게 나옵니다.

아마도 type이 ref로 되지 않고 index로 되는 것과 관련이 있을 것 같은데..

limit 10으로 해도 ref로 되도록 하는 방법은 없는걸까요??ㅜㅜ

mysql에서 자체적으로 optimizing한 것 같은데, limit개수가 explain type과 관련되는지는 몰랐네요..

참고로 SELECT COUNT(*) FROM chat WHERE user_id=유저아이디 한 값은 8500개정도입니다.

도와주세요!!

  • 2016년 05월 10일에 작성됨

조회수 332


1 답변


좋아요
1
싫어요
채택취소하기
SELECT * FROM chat WHERE user_id=유저아이디  order by chat_id desc

이 때 , user_id와 chat_id를 각기 별도의 인덱스를 가지고 있는 경우, MySQL에서 알아서 Optimization 하는 것 같네요.

http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

한번 복합인덱스(user_id, chat_id) 혹은 (chat_id, user_id) 를 만들어 테스트 해보세요.

  • 2016년 05월 10일에 작성됨
    리눅스(유닉스) 기반의 시스템에서 웹 서비스를 개발하고 있습니다.

  • 복합인덱스를 만들어주어야 했네요.. (user_id, chat_id)로 해주니 바로 해결되었습니다. 진심으로 감사드립니다 ㅜ!!    나성수   2016.5.10 15:24     

로그인이 필요한 기능입니다.

Hashcode는 개발자들을 위한 무료 QnA사이트 입니다. 작성한 답변에 다른 개발자들이 댓글을 작성하거나 좋아요/싫어요를 할 수 있기 때문에 계정을 필요로 합니다.
► 로그인
► 계정만들기
Close