추가 열을 선택할 때 MariaDB에서 SQL 응답 속도가 느립니다.

추가 열을 선택할 때 MariaDB에서 SQL 응답 속도가 느립니다.

다음 두 개의 쿼리에 주목하십시오. 두 쿼리 사이의 차이점이 강조 표시됩니다.

쿼리 A:

SELECT  tasks.TaskID, tasks.CardID, tasks.CritPath, tasks.ReworkCount,
        cs.WorkflowID,
         cs.StageCode,    -- This is added
        tasks.CurrentEscalationLevel,
        tasks.Title, tasks.Description, tasks.EscalationDelay,
        tasks.StartDate, tasks.EndDate, tasks.OriginalStartDate,
        tasks.OriginalEndDate, tasks.Priority, tasks.Duration,
        tasks.Status
    FROM  Tasks tasks
    INNER JOIN  CardsSettings cs  ON cs.CardID = tasks.TaskID
    INNER JOIN  
    (
        SELECT  t1.WorkflowID
            FROM  
            (
                SELECT  WorkflowID
                    from  Workflow
                    Where  IsWFActive = "YES"
                      and  LastUpdatedDateTime BETWEEN "2018-11-21 23:59:59" AND "2019-11-21 23:59:59"
                      AND  WorkflowTypeID = 9
            ) t1
            INNER JOIN  
            (
                SELECT  formSubCardSettings.WorkflowID, cfsm.Value as
                    Value
                     FROM  CardsSettings AS formSubCardSettings
                    INNER JOIN  custom_form_submissions cfs  ON cfs.FormSubmissionID = formSubCardSettings.CardID
                      AND  cfs.IsHistory = 'NO'
                    INNER JOIN  custom_form cf  ON cf.FormID = cfs.FormID
                    INNER JOIN  custom_form_metadata cfm  ON cfm.FormID = cf.FormID
                    INNER JOIN  custom_form_submissions_metadata cfsm  ON cfsm.FormSubmissionID = cfs.FormSubmissionID
                      AND  cfsm.FormMetaID = cfm.FormMetaID
                    INNER JOIN  Workflow cfwf  ON cfwf.WorkflowID = formSubCardSettings.WorkflowID
                    WHERE  cf.FormTitle = "Project Initiation"
                      AND  cfm.FieldLabel = "wid"
                      AND  cfwf.WorkflowTypeID = 9
                      AND  (cfsm.Value IN("413"))) t2  ON t1.WorkflowID = t2.WorkflowID
    ) a  ON a.WorkflowID = cs.WorkflowID;

쿼리 B:

작업을 선택합니다.작업 ID, 작업.카드 ID, 작업.CritPath, 작업.재작업 횟수, cs.워크플로우아이디, 작업.CurrentScalation레벨, 작업.제목, 작업.설명, 작업.에스컬레이션 지연, 작업.시작 날짜, 작업.EndDate, 작업.원래 시작 날짜, 작업.OriginalEndDate, 작업.우선순위, 과제.기간, 작업.Status FROM Tasks INNER JOIN CardsSettings cs Oncs.카드 ID = 작업.TaskID 내부 조인(TESOCT t1).워크플로우ID FROM(SELECT Workflow)Workflow IsWFactive = "YES" 및 "2018-11-21 23:59:59"와 "2019-11-21 23:59:59" 사이의 마지막 업데이트 날짜 시간과 Workflow에서 ID타입ID = 9) t1 INNER JOIN (SELECT form SubCard Settings).워크플로우아이디, cfsm.Value as Value FROM CardsSettings AS formSubCardSettings INNER JOIN custom_form_submissions cfs ON cfs.양식 제출ID = formSubCardSettings.카드 ID와 cfs.IsHistory = 'NO' INNER JOIN custom_form cf ON cf입니다.양식 ID = cfs.FormID INNER JOIN custom_form_metadata cfm ON cfm.양식 ID = cf.FormID INNER JOIN custom_form_submissions_metadata cfsm ON cfsm.양식 제출ID = cfs.양식 제출아이디와 cfsm.폼메타아이디 = cfm.폼MetaID INNER JOIN 워크플로우 cfwf ON cfwf.워크플로우ID = formSubCardSettings.워크플로우아이디 WHERE cf.양식 제목 = "프로젝트 시작" 및 cfm.필드 레이블 = "wid" 및 cfwf.워크플로우ID = 9 AND(cfsm)를 입력합니다.값 IN("413")) t2 ON t1.워크플로우아이디 = t2.워크플로우ID) ANA.워크플로우아이디 = cs.워크플로우ID;

StageCode 열은 varchar(1024)입니다.

나는 두 개의 다른 DB 엔진에 정확히 동일한 데이터베이스와 스키마를 가지고 있는데, 하나는 mysql(5.6)이고 다른 하나는 mariadb(10.4)입니다.

mariadb에서 쿼리를 실행하면 응답 시간이 매우 다릅니다.

쿼리 A : 약 5초

쿼리 B : 약 0.2초

cs에서 여분의 필드를 추가하는 것만으로도 응답 시간이 몇 배로 증가하고 있습니다.이것은 매번 일어나는 것처럼 일회성 행동이 아닙니다.또한 두 쿼리 모두 cs를 가지고 있음을 유의하십시오.워크플로우선택한ID입니다.

mysql에서 위의 쿼리를 실행하면 응답이 빨라지며 둘 다 0.1초 이내에 반환됩니다.

두 DB 엔진 모두에서 문제가 있는 쿼리 A에 대한 설명은 다음과 같습니다.

mysql:

+----+-------------+---------------------+--------+----------------------------+------------------+---------+------------------------------------------------------+-------+-------------+
| id | select_type | table               | type   | possible_keys              | key              | key_len | ref                                                  | rows  | Extra       |
+----+-------------+---------------------+--------+----------------------------+------------------+---------+------------------------------------------------------+-------+-------------+
|  1 | PRIMARY     | tasks               | ALL    | PRIMARY                    | NULL             | NULL    | NULL                                                 |  3091 | NULL        |
|  1 | PRIMARY     | cs                  | eq_ref | PRIMARY,FK_CS_WFID_WF_WFID | PRIMARY          | 4       | zestlTitan_3000037868.tasks.TaskID                   |     1 | Using where |
|  1 | PRIMARY     | <derived2>          | ref    | <auto_key0>                | <auto_key0>      | 4       | zestlTitan_3000037868.cs.WorkflowID                  |    10 | Using index |
|  2 | DERIVED     | <derived3>          | ALL    | NULL                       | NULL             | NULL    | NULL                                                 |    81 | NULL        |
|  2 | DERIVED     | <derived4>          | ref    | <auto_key0>                | <auto_key0>      | 5       | t1.WorkflowID                                        |   715 | NULL        |
|  4 | DERIVED     | cfs                 | ALL    | PRIMARY,FormID             | NULL             | NULL    | NULL                                                 | 17888 | Using where |
|  4 | DERIVED     | cf                  | eq_ref | PRIMARY                    | PRIMARY          | 4       | zestlTitan_3000037868.cfs.FormID                     |     1 | Using where |
|  4 | DERIVED     | formSubCardSettings | eq_ref | PRIMARY,FK_CS_WFID_WF_WFID | PRIMARY          | 4       | zestlTitan_3000037868.cfs.FormSubmissionID           |     1 | Using where |
|  4 | DERIVED     | cfwf                | eq_ref | PRIMARY,FK_WTID_WFT_WTID   | PRIMARY          | 4       | zestlTitan_3000037868.formSubCardSettings.WorkflowID |     1 | Using where |
|  4 | DERIVED     | cfsm                | ref    | FormSubmissionID           | FormSubmissionID | 4       | zestlTitan_3000037868.cfs.FormSubmissionID           |     4 | Using where |
|  4 | DERIVED     | cfm                 | eq_ref | PRIMARY,FormID             | PRIMARY          | 4       | zestlTitan_3000037868.cfsm.FormMetaID                |     1 | Using where |
|  3 | DERIVED     | Workflow            | ref    | FK_WTID_WFT_WTID           | FK_WTID_WFT_WTID | 4       | const                                                |    81 | Using where |
+----+-------------+---------------------+--------+----------------------------+------------------+---------+------------------------------------------------------+-------+-------------+
12 rows in set (0.00 sec)

mariadb에서:

+------+-------------+---------------------+--------+----------------------------+--------------------+---------+--------------------------------------------------+------+-------------+
| id   | select_type | table               | type   | possible_keys              | key                | key_len | ref                                              | rows | Extra       |
+------+-------------+---------------------+--------+----------------------------+--------------------+---------+--------------------------------------------------+------+-------------+
|    1 | SIMPLE      | tasks               | ALL    | PRIMARY                    | NULL               | NULL    | NULL                                             | 3072 |             |
|    1 | SIMPLE      | cs                  | eq_ref | PRIMARY,FK_CS_WFID_WF_WFID | PRIMARY            | 4       | zestlTitan_3000037868.tasks.TaskID               | 1    | Using where |
|    1 | SIMPLE      | Workflow            | eq_ref | PRIMARY,FK_WTID_WFT_WTID   | PRIMARY            | 4       | zestlTitan_3000037868.cs.WorkflowID              | 1    | Using where |
|    1 | SIMPLE      | cfwf                | eq_ref | PRIMARY,FK_WTID_WFT_WTID   | PRIMARY            | 4       | zestlTitan_3000037868.cs.WorkflowID              | 1    | Using where |
|    1 | SIMPLE      | formSubCardSettings | ref    | PRIMARY,FK_CS_WFID_WF_WFID | FK_CS_WFID_WF_WFID | 5       | zestlTitan_3000037868.cs.WorkflowID              | 219  | Using index |
|    1 | SIMPLE      | cfs                 | eq_ref | PRIMARY,FormID             | PRIMARY            | 4       | zestlTitan_3000037868.formSubCardSettings.CardID | 1    | Using where |
|    1 | SIMPLE      | cf                  | eq_ref | PRIMARY                    | PRIMARY            | 4       | zestlTitan_3000037868.cfs.FormID                 | 1    | Using where |
|    1 | SIMPLE      | cfsm                | ref    | FormSubmissionID           | FormSubmissionID   | 4       | zestlTitan_3000037868.formSubCardSettings.CardID | 4    | Using where |
|    1 | SIMPLE      | cfm                 | eq_ref | PRIMARY,FormID             | PRIMARY            | 4       | zestlTitan_3000037868.cfsm.FormMetaID            | 1    | Using where |
+------+-------------+---------------------+--------+----------------------------+--------------------+---------+--------------------------------------------------+------+-------------+
9 rows in set (0.002 sec)

누가 무엇이 잘못되고 있는지 확인해 줄 수 있습니까?두 시스템 모두 리소스(RAM/Disk)가 비슷합니다.

편집 1:

StageCode(Varchar 1024) 대신 작은 int(10)인 다른 CardsSettings(cs) 필드를 사용하면 동일한 이상한 동작이 나타납니다.그래서 저는 이것이 긴 기둥과 관련이 있다고 생각하지 않습니다.그러나 카드 설정 테이블에는 몇 개의 텍스트 열과 함께 varchar(1024) 및 varchar(4096)가 있습니다.

편집 2:

별도로 쿼리할 경우 t2에 대한 하위 쿼리는 0.05초밖에 걸리지 않습니다.

SELECT formSubCardSettings.WorkflowID FROM CardsSettings AS formSubCardSettings INNER JOIN custom_form_submissions cfs ON cfs.FormSubmissionID = formSubCardSettings.CardID AND cfs.IsHistory = 'NO' INNER JOIN custom_form cf ON cf.FormID = cfs.FormID INNER JOIN custom_form_metadata cfm ON cfm.FormID = cf.FormID INNER JOIN custom_form_submissions_metadata cfsm ON cfsm.FormSubmissionID = cfs.FormSubmissionID AND cfsm.FormMetaID = cfm.FormMetaID INNER JOIN Workflow cfwf ON cfwf.WorkflowID = formSubCardSettings.WorkflowID WHERE cf.FormTitle = "Project Initiation" AND cfm.FieldLabel = "wid" AND cfwf.WorkflowTypeID = 9 AND (cfsm.Value IN("413"));
+------------+
| WorkflowID |
+------------+
|        413 |
+------------+
1 row in set (0.056 sec)

원래 쿼리 "A"에 서브쿼리 전체를 대입하면 쿼리 A는 다음과 같이 됩니다.

MariaDB [zestlTitan_3000037868]> 작업 선택작업 ID, 작업.카드 ID, 작업.CritPath, 작업.재작업 횟수, cs.워크플로우아이디, cs.스테이지 코드, 작업.CurrentScalation레벨, 작업.제목, 작업.설명, 작업.에스컬레이션 지연, 작업.시작 날짜, 작업.EndDate, 작업.원래 시작 날짜, 작업.OriginalEndDate, 작업.우선순위, 과제.기간, 작업.Status FROM Tasks INNER JOIN CardsSettings cs Oncs.카드 ID = 작업.TaskID 내부 조인(TESOCT t1).워크플로우ID FROM(SELECT Workflow)Workflow IsWFactive = "YES" 및 "2018-11-21 23:59:59"와 "2019-11-21 23:59:59" 사이의 마지막 업데이트 날짜 시간과 Workflow에서 IDType ID = 9) t1 내부 조인(워크플로우로 "413" 선택)ID) t2 ON t1.워크플로우아이디 = t2.워크플로우ID) ANA.워크플로우아이디 = cs.워크플로우ID;

이것은 현재 mariadb(~0.1초)에서 매우 빠르게 실행되고 있습니다.

편집 3:

편집 2는 왜 이런 일이 일어나고 있는지에 대한 단서를 주었습니다. 분명히 하위 쿼리 t2가 사전 계산되지 않고 있었습니다(제가 틀렸을 수도 있습니다).그래서 WHERE t1을 사용하도록 쿼리를 수정했습니다.워크플로우내부 조인 대신 IDIN (...)t2는 다음과 같습니다.

작업을 선택합니다.작업 ID, 작업.카드 ID, 작업.CritPath, 작업.재작업 횟수, cs.워크플로우아이디, cs.스테이지 코드, 작업.CurrentScalation레벨, 작업.제목, 작업.설명, 작업.에스컬레이션 지연, 작업.시작 날짜, 작업.EndDate, 작업.원래 시작 날짜, 작업.OriginalEndDate, 작업.우선순위, 과제.기간, 작업.Status FROM Tasks INNER JOIN CardsSettings cs Oncs.카드 ID = 작업.TaskID 내부 조인(TESOCT t1).워크플로우ID FROM(SELECT Workflow)Workflow IsWFactive = "YES" 및 "2018-11-21 23:59:59"와 "2019-11-21 23:59:59" 사이의 마지막 업데이트 날짜 시간과 Workflow에서 ID유형 ID = 9) t1 WHERE t1.워크플로우IDIN (SELECT formSubCardSettings).워크플로우ID FROM CardsSettings AS formSubCardSettings INNER JOIN custom_form_submissions cfs ONcfs.양식 제출ID = formSubCardSettings.카드 ID와 cfs.IsHistory = 'NO' INNER JOIN custom_form cf ON cf입니다.양식 ID = cfs.FormID INNER JOIN custom_form_metadata cfm ON cfm.양식 ID = cf.FormID INNER JOIN custom_form_submissions_metadata cfsm ON cfsm.양식 제출ID = cfs.양식 제출아이디와 cfsm.폼메타아이디 = cfm.폼MetaID INNER JOIN 워크플로우 cfwf ON cfwf.워크플로우ID = formSubCardSettings.워크플로우아이디 WHERE cf.양식 제목 = "프로젝트 시작" 및 cfm.필드 레이블 = "wid" 및 cfwf.워크플로우ID = 9 AND(cfsm)를 입력합니다.값 IN("413")) a ONa.워크플로우아이디 = cs.워크플로우ID;

이제 mariadb 쿼리도 0.2초까지 빠르게 작동합니다.

이 성공과 시도를 통해 쿼리에 빠르게 응답할 수 있었습니다. 이 모든 것을 이해할 수 있는 사람이 있습니까?

행이 "너무 큰" 경우 일부 열의 값은 "기록 해제" 저장소에 기록됩니다.행 다시 읽기(SELECTing다른 장소에서 가져오는 것이 필요합니다.데이터가 RAM에 캐싱되지 않으면 이는 또 다른 디스크 히트입니다.디스크 히트는 시간이 걸립니다.

테이블에 기둥이 많은 것 같습니까?많은 분들이.TEXT크든 크든VARCHARs? 이것이 첫번째 쿼리의 부진을 설명할 수 있습니다.

MySQL과 MariaDB의 Optimizer가 서로 다른 지점 중 하나는 서브쿼리를 처리하는 것입니다.

추가칼럼의 문제는 두가지가 더 있습니다...

• 쿼리의 두 번째(?) 테이블로 페치되며, 나머지 쿼리에 대해 포워딩해야 합니다.
• 중간 임시 테이블이 필요한 경우(인지 알 수 없음)EXPLAIN메모리가 아닌 마이 아이샘이 될 수밖에 없을 것입니다.이것은 큰 것 때문입니다.VARCHAR(1024), 실제 데이터가 작더라도.내 ISAM이 MEMORY보다 느립니다. (다른 열이 '큰'인 경우에는 My ISAM이 이미 필요하므로 이 대답은 적용되지 않습니다.)

이 구성은 특히 까다롭기 때문에 다음과 같이 다르게 처리되었습니다.

SELECT ...
    FROM ( SELECT ... )
    JOIN ( SELECT ... )

MySQL은 종종 도출된 테이블을 구체화하고 필요한 인덱스를 동적으로 결정하여 해당 인덱스를 구축합니다. 참조<auto-key>에서EXPLAIN.

MariaDB는 하위 쿼리를 다른 방식으로 처리합니다.

도움이 되는 인덱스가 있을 수도 있습니다.

        Where  IsWFActive = "YES"
          and  LastUpdatedDateTime BETWEEN "2018-11-21 23:59:59" AND "2019-11-21 23:59:59"
          AND  WorkflowTypeID = 9

필요.

INDEX(IsWFActive, WorkflowTypeID,   -- in either order
      LastUpdatedDateTime)          -- after the others

그리고:

ON a.WorkflowID = cs.WorkflowID
ON cs.CardID = tasks.TaskID

두 번째 파트에서 시작한 다음 첫 번째 테이블로 돌아갈 수 있도록 필요합니다.

CardsSettings:  INDEX(WorkflowID)
Tasks:          INDEX(TaskID)   -- unless that is the PRIMARY KEY

이 마지막 제안은 제가 앞서 언급했던 "큰 기둥들의 이동"을 없앨 수 있을 것입니다.

더

이렇게 하면 모든(?) 경우 쿼리가 더 빨리 실행됩니다.

INDEX(WorkflowID,    -- first
      CardID, StageCode)   -- to make it covering (with or without stage

Optimizer(옵티마이저)가 먼저 파생된 테이블(일반적으로 최적)을 분사한 다음 다음 다음으로 이동하도록 권장합니다.cs경유로WorkflowID. 마지막으로 필요한 나머지 열("커버링")이 포함됩니다.

추가적인 논의를 위해 다음을 제공해 주시기 바랍니다.SHOW CREATE TABLE.

언급URL : https://stackoverflow.com/questions/58975005/slow-sql-response-in-mariadb-on-selecting-an-extra-column