by 피타칩스 [SQL Query] 튜닝 SQL [2019.12.04 21:39:43]
유저들을 매칭시켜주는 프로그램을 만들고 있습니다.
한 명의 유저가 지난 10일간 매칭된 내역을 쫙 보여줄 수 쿼리를 만드니 SELECT가 4번, JOIN이 2번이나 일어나는데요 ㅠㅠ
어떤 부분을 변경하면 깔끔한코드 또는 속도개선을 할 수 있을지 조언 주시면 감사하겠습니다.
match 테이블은 매치가 일어난 매치id, 매치장소, 매칭이 이뤄진 시간(createdAt) 를 가지고 있구요.
mysql> select * from matches; +----+----------+---------------------+---------------------+ | id | location | createdAt | updatedAt | +----+----------+---------------------+---------------------+ | 1 | yongsan | 2019-12-01 10:14:35 | 2019-12-01 10:14:35 | | 2 | yongsan | 2019-12-01 10:20:15 | 2019-12-01 10:20:15 | | 3 | yongsan | 2019-12-01 10:21:16 | 2019-12-01 10:21:16 | +----+----------+---------------------+---------------------+
matchTimes 테이블은 각각의 매치마다 가능한 미팅 시간을 담은 테이블로, OneToMany 관계입니다.
mysql> select * from matchTimes; +----+-----------+---------+---------+ | id | timeStart | timeEnd | matchId | +----+-----------+---------+---------+ | 1 | 750 | 780 | 1 | | 2 | 1250 | 1290 | 1 | | 3 | 750 | 780 | 2 | | 4 | 1250 | 1290 | 2 | | 5 | 700 | 780 | 3 | | 6 | 1250 | 1300 | 3 | +----+-----------+---------+---------+
그리고 userMatches 테이블은 User와 Match 테이블 간의 ManyToMany 테이블 입니다.
mysql> select * from userMatches; +---------------------+--------+---------+ | createdAt | userId | matchId | +---------------------+--------+---------+ | 2019-12-01 10:14:46 | 1 | 1 | | 2019-12-01 10:20:15 | 1 | 2 | | 2019-12-01 10:21:16 | 1 | 3 | | 2019-12-01 10:14:46 | 2 | 1 | | 2019-12-01 10:20:15 | 2 | 2 | | 2019-12-01 10:21:16 | 3 | 3 | +---------------------+--------+---------+
제가 하고 싶은건,
userId를 알고있는 상황에서, 해당 유저가 최근 10일 안에 매칭된 모든 match 를 찾고, 그 match에 매칭된 복수의 유저들(본인 포함)을 받고 싶고, 해당 매치의 매치장소, 그리고 매치가능한 복수의 시간대들도 받고싶습니다.
결과적으로 이런 데이터의 모습입니다.
+-------------------+---------+----------+---------------------+----------------+ | matchedTimeSets | matchId | location | createdAt | matchedUserIds | +-------------------+---------+----------+---------------------+----------------+ | 750~780,1250~1290 | 1 | yongsan | 2019-12-01 10:14:35 | 1,2 | | 750~780,1250~1290 | 2 | yongsan | 2019-12-01 10:20:15 | 1,2 | | 700~780,1250~1300 | 3 | yongsan | 2019-12-01 10:21:16 | 3,1 | +-------------------+---------+----------+---------------------+----------------+
제가 짠 쿼리는 예를들어 userId가 1일 때,
SELECT GROUP_CONCAT(CONCAT(mt.timeStart, '~', mt.timeEnd)) as 'matchedTimeSets', m_and_um.matchId, m_and_um.location, m_and_um.createdAt, m_and_um.matchedUserIds FROM matchTimes as mt INNER JOIN (SELECT um.matchId, m.location, m.createdAt, GROUP_CONCAT(um.userId) as 'matchedUserIds' FROM (SELECT matchId, userId from userMatches WHERE matchId in (SELECT matchId from userMatches WHERE userId=1) ) as um INNER JOIN matches AS m ON um.matchId = m.id WHERE TIMESTAMPDIFF(DAY, m.createdAt, UTC_TIMESTAMP()) < 10 GROUP BY um.matchId ) as m_and_um on mt.matchId = m_and_um.matchId GROUP BY m_and_um.matchId;
이렇게 생겨서 ㅠㅠㅠ 개선하고 싶습니다.
matchTimes에 관한 데이터를 matchTimes 테이블로 정규화 시키지 않고 match 테이블 안에 애초에 콤마로 연결된 스트링으로 넣으면 조금 쿼리가 짧아지긴 하는데... 고수님들 도와주세요 ㅠ
SELECT GROUP_CONCAT(CONCAT(e.timeStart, '~', e.timeEnd)) matchedTimeSets , c.matchId , d.location , d.createdAt , c.matchedUserIds FROM (SELECT b.matchId , GROUP_CONCAT(b.userId) matchedUserIds FROM userMatches a INNER JOIN userMatches b ON a.matchId = b.matchId WHERE a.userId = 1 GROUP BY b.matchId -- ① ) c INNER JOIN matches d ON c.matchId = d.id INNER JOIN matchTimes e ON d.id = e.matchId WHERE d.createdAt > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 10 DAY) -- ② GROUP BY c.matchId , d.location, d.createdAt, c.matchedUserIds -- ③ ;
① 그룹바이 위치 변경
- 조인후 구룹바이 -> 그룹바이 후 조인
- um 과 m 을 조인하기 전 um 안에서 그룹바이 수행
② 조건 줄 때
- 컬럼을 가공하지 말고 조건값을 가공
- 기준일과 날짜차이가 10일인걸 구하지 말고 -> 기준일 10일 이전 날짜와 비교
③ 표준 구문 이용
- 알리아스에 홑따옴표 빼기
- 그룹바이 표준 지키기
- matchId 만으로 그룹핑해도 다른 항목들이 matchId 에 종속된 1개의 값이므로 결과엔 문제가 없지만
- 이는 그룹바이 표준에 어긋나는 문법입니다.
- 다른 항목들도 그룹바이에 포함시켜야 합니다.
우와 감사합니다. 항목별로 설명해주신게 큰 도움이 되었어요. 칼럼데이터의 WHERE ... IN 을 JOIN 으로도 바꿔서 select 한번 사라진것도 이득일 것 같아요.
그리고 혹시 sql 표준 가이드는 어디서 볼 수 있나요? 공부하면 도움될 것 같은데 공인기관의 가이드가 있는건가요? 잘 안 찾아지는데, http://www.gurubee.net/bookcafe/sqlp_1st 이 링크가 표준적인 sql이라고 보고 공부하면 될까요?