안녕하세요.
쿼리를 작성하는 데 있어 고민이 생겨 글을 올리게 되었습니다.
ER다이어그램은 첨부한 파일을 참고 부탁드립니다.
추가적으로 설명을 드리면,
1. mt_company: 부서 라고 생각하시면 될 것 같습니다.
2. mt_pm_account: 부서에 속한 팀원이라고 생각하시면 될 것 같습니다.
3. mt_pm_account_task: 팀원들이 작성한 작업이 저장되는 곳 입니다.
4. rs_pm_account_pm_account_task: 작업을 작성한 사람(type=1), 작업을 지시당한 사람(type=2)이 저장되는 곳 입니다.
5. Index: mt_pm_account_task 테이블 복합키 (mt_company_id, due_date), rs_pm_account_pm_account_task 테이블 단일키 mt_pm_account_id, mt_pm_account_task_id
UI 상에서 특정 팀원의 작업 리스트를 보여줘야 하는 곳이 있는 데, 정렬 기준이 자신이 작성한 작업을 보여주고 난 후에 지시받은 작업을 보여주어야 합니다. +@로 type별로 만기일이 가까운 작업을 먼저 보여주어야 합니다.
글 보다는 그림으로 보여드리는 게 정확할 거 같네요.
mysql> select * from mt_pm_account_task where mt_company_id = 83; +----+---------------+------+------------+---------------+---------------------+---------------------+------------+ | id | title | body | due_date | mt_company_id | registered_at | updated_at | deleted_at | +----+---------------+------+------------+---------------+---------------------+---------------------+------------+ | 8 | eighth-title | NULL | 2019-01-01 | 83 | 2019-04-22 12:09:08 | 2019-04-22 12:09:08 | NULL | | 6 | sixth-title | NULL | 2019-03-04 | 83 | 2019-04-22 12:08:47 | 2019-04-22 12:08:47 | NULL | | 5 | fifth-title | NULL | 2019-05-01 | 83 | 2019-04-22 12:08:38 | 2019-04-22 12:08:38 | NULL | | 4 | fourth-title | NULL | 2019-05-06 | 83 | 2019-04-22 12:08:28 | 2019-04-22 12:08:28 | NULL | | 7 | seventh-title | NULL | 2019-12-31 | 83 | 2019-04-22 12:08:56 | 2019-04-22 12:08:56 | NULL | +----+---------------+------+------------+---------------+---------------------+---------------------+------------+ 5 rows in set (0.00 sec) mysql> select * from rs_pm_account_pm_account_task where mt_pm_account_id = 360678; +----+-----------------+--------+------+------------------+-----------------------+ | id | completion_date | status | type | mt_pm_account_id | mt_pm_account_task_id | +----+-----------------+--------+------+------------------+-----------------------+ | 4 | NULL | 0 | 1 | 360678 | 7 | | 5 | NULL | 0 | 2 | 360678 | 5 | | 6 | NULL | 0 | 1 | 360678 | 8 | +----+-----------------+--------+------+------------------+-----------------------+ 3 rows in set (0.00 sec) mysql> SELECT a.id, a.title, a.body, a.due_date, a.mt_company_id, b.mt_pm_account_id, b.type FROM mt_pm_account_task a JOIN rs_pm_account_pm_account_task b ON a.id = b.mt_pm_account_task_id AND b.mt_pm_account_id = 360678 WHERE mt_company_id = 83 ORDER BY type,due_date; +----+---------------+------+------------+---------------+------------------+------+ | id | title | body | due_date | mt_company_id | mt_pm_account_id | type | +----+---------------+------+------------+---------------+------------------+------+ | 8 | eighth-title | NULL | 2019-01-01 | 83 | 360678 | 1 | | 7 | seventh-title | NULL | 2019-12-31 | 83 | 360678 | 1 | | 5 | fifth-title | NULL | 2019-05-01 | 83 | 360678 | 2 | +----+---------------+------+------------+---------------+------------------+------+
이런 식으로 나와야 하는 데, 문제는 속도입니다...!
위의 쿼리는 "Using temporary; Using filesort" 가 Extra로 찍히고,
UNION을 사용하면 속도는 좋으나 정렬이 적용되질 않네요.
mysql> EXPLAIN (SELECT a.id, a.title, a.body, a.due_date, a.mt_company_id, b.mt_pm_account_id, b.type FROM mt_pm_account_task a JOIN rs_pm_account_pm_account_task b ON a.id = b.mt_pm_account_task_id AND b.mt_pm_account_id = 360678 AND type = 1 WHERE mt_company_id = 83 ORDER BY due_date) UNION ALL (SELECT a.id, a.title, a.body, a.due_date, a.mt_company_id, b.mt_pm_account_id, b.type FROM mt_pm_account_task a JOIN rs_pm_account_pm_account_task b ON a.id = b.mt_pm_account_task_id AND b.mt_pm_account_id = 360678 AND type = 2 WHERE mt_company_id = 83 ORDER BY due_date); +----+--------------+------------+--------+----------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------------------------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+--------+----------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------------------------+------+-----------------+ | 1 | PRIMARY | b | ref | fk_rs_pm_account_pm_account_task_mt_pm_account_id,fk_rs_pm_account_pm_account_task_mt_pm_account_task_id | fk_rs_pm_account_pm_account_task_mt_pm_account_id | 4 | const | 3 | Using where | | 1 | PRIMARY | a | eq_ref | PRIMARY,idx_mt_pm_account_task_mt_company_id_due_date | PRIMARY | 4 | kitdb.b.mt_pm_account_task_id | 1 | Using where | | 2 | UNION | b | ref | fk_rs_pm_account_pm_account_task_mt_pm_account_id,fk_rs_pm_account_pm_account_task_mt_pm_account_task_id | fk_rs_pm_account_pm_account_task_mt_pm_account_id | 4 | const | 3 | Using where | | 2 | UNION | a | eq_ref | PRIMARY,idx_mt_pm_account_task_mt_company_id_due_date | PRIMARY | 4 | kitdb.b.mt_pm_account_task_id | 1 | Using where | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+--------+----------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------------------------+------+-----------------+ 5 rows in set (0.00 sec) mysql> (SELECT a.id, a.title, a.body, a.due_date, a.mt_company_id, b.mt_pm_account_id, b.type FROM mt_pm_account_task a JOIN rs_pm_account_pm_account_task b ON a.id = b.mt_pm_account_task_id AND b.mt_pm_account_id = 360678 AND type = 1 WHERE mt_company_id = 83 ORDER BY due_date) UNION ALL (SELECT a.id, a.title, a.body, a.due_date, a.mt_company_id, b.mt_pm_account_id, b.type FROM mt_pm_account_task a JOIN rs_pm_account_pm_account_task b ON a.id = b.mt_pm_account_task_id AND b.mt_pm_account_id = 360678 AND type = 2 WHERE mt_company_id = 83 ORDER BY due_date); +----+---------------+------+------------+---------------+------------------+------+ | id | title | body | due_date | mt_company_id | mt_pm_account_id | type | +----+---------------+------+------------+---------------+------------------+------+ | 7 | seventh-title | NULL | 2019-12-31 | 83 | 360678 | 1 | | 8 | eighth-title | NULL | 2019-01-01 | 83 | 360678 | 1 | | 5 | fifth-title | NULL | 2019-05-01 | 83 | 360678 | 2 | +----+---------------+------+------------+---------------+------------------+------+
혹시 좋은 방법이나, 테이블 설계를 바꿔야 하는 의견이 있으시면 댓글 부탁드리겠습니다.
감사합니다
1. 부서 인덱스를 이용한 정렬을 하면서 나머지 팀원을 버리는 것이 유리할지?
2. 특정 사원만 검색하여 조인한뒤 정렬하는 것이 유리할지?
어느 것이 유리할지 모르겠네요.
1번은 정렬 부하는 없지만 버려지는 사원이 많을 것으로 예상이 되고(램덤엑세스 부하)
2번은 버려지는 사원은 없지만 정렬에 대한 부담이 있겠네요(정렬 부하)
작성하신 유니온 쿼리는
- 2번의 정렬부하를 줄이고자 1번 방법을 사용하려고 한 듯 한데요.
- UNION 의 각각의 개별 쿼리는 실행계획은 원하는 대로 1번처럼 나오나요?
- 1번 처럼 나온다면? 성능이 원하는 대로 나오나요?
정렬이 안되는 이유는
- 인라인뷰 안의 정렬이 무시가 되어서 그러네요.
- 정렬 구문 아래 LIMIT 구문을 넣어서 정렬이 무시되지 않도록 해보세요.
- LIMIT 9999
모델을 아래와 같이 수정하는 쪽으로 제안드려 봅니다.
rs_pm_account_pm_account_task 테이블의 PK 및 인덱스
- PK : mt_pm_account_id + mt_pm_account_task_id + type
- INDEX : mt_pm_account_task_id
SELECT STRAIGHT_JOIN a.id, a.title, a.body, a.due_date, a.mt_company_id, b.mt_pm_account_id, b.type FROM rs_pm_account_pm_account_task b FORCE INDEX FOR JOIN (PRIMARY) INNER JOIN mt_pm_account_task a FORCE INDEX FOR JOIN (PRIMARY) ON a.id = b.mt_pm_account_task_id WHERE b.mt_pm_account_id = 360678 ORDER BY b.type, a.due_date;
1. mt_pm_account_id = 360678 조건이 mt_company_id = 83 조건을 포함한다고 볼 수 있으므로, 불필요한 mt_company_id = 83 조건은 뺐습니다.
2. 힌트는.. 의도한 물리적인 조인의 순서와 사용하는 인덱스의 이해를 위해 적었습니다.
ㄴ 직원 수보다 태스크가 훨씬 많을 것이기 때문에.. 태스크를 driven table로 삼는 것이 유리해 보입니다.
3. order by b.type, a.due_date 라고 적었지만.. order by b.type 까지만 남겨놓거나 아예 생략하고 WAS에서 정렬하도록 처리하면 더 좋을 것 같습니다.