데이터베이스 질문과 답변
안녕하세요.
쿼리를 작성하는 데 있어 고민이 생겨 글을 올리게 되었습니다.
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
답변 감사합니다.
여쭤보신 "UNION 의 각각의 개별 쿼리는 실행계획은 원하는 대로 1번처럼 나오나요?" 에 대한 답변은 1번 처럼 나오고 성능 또한 좋습니다!
말씀하신 대로 정렬 구문 아래 LIMIT 구문을 넣으니 정렬이 되네요!!
감사합니다...!
정렬은 보통 최종쿼리에 하게 되어 있죠.
그래서 예전부터 인라인뷰 안의 정렬은 허용하지 않고 에러를 발생시켰습니다.
오라클의 경우 예전 버전(8.0)에서는 허용 자체가 안되었고 지금은 되네요.
MSSQL 은 지금도 허용이 안되구요.
MySQL 은 구문 허용은 하되 실행은 안되도록 하나 보네요.
개인적인 추측입니다.
설명 감사합니다!
많은 걸 배웠습니다.
저도, 올려주신 질문을 통해 튜닝 아이디어를 배웠네요.
모델을 아래와 같이 수정하는 쪽으로 제안드려 봅니다.
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에서 정렬하도록 처리하면 더 좋을 것 같습니다.
늦게 답변 드려 죄송하고, 의견 감사합니다.
b.type 까지만 정렬을 한다는 가정하에는 속도가 빠를 것 같지만, 저희 프로젝트의 규칙이 정렬은 서버사이드에서 처리되야 해서 a.due_date 까지 붙이면 느려질 것 같네요.
말씀하신 스키마는 PK가 3개의 애트리뷰트로 구성되어 있어서 저장속도와 인덱스 화일이 무거워지는 데, 갱신보다 검색이 많다는 워크로드로 간주하여 제안해주신 건가요?
) 버튼을 클릭하여 작성 하시면 됩니다.