Медленный ответ SQL в MariaDB при выборе дополнительного столбца

Обратите внимание на следующие два запроса, разница между ними выделена

Запрос А:

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;

Запрос Б:

ВЫБЕРИТЕ tasks.TaskID, tasks.CardID, tasks.CritPath, tasks.ReworkCount, cs.WorkflowID, 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 МЕЖДУ "2018-11-21 23:59:59" и "2019-11-21 23:59:59" И 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_metada ta cfsm ON cfsm.FormSubmissionID = cfs.FormSubmissionID И cfsm.FormMetaID = cfm.FormMetaID ВНУТРЕННЕЕ СОЕДИНЕНИЕ Рабочий процесс cfwf ON cfwf.WorkflowID = formSubCardSettings.WorkflowID ГДЕ cf.FormTitle = "Инициация проекта" И cfm.FieldLabel = "wid" И cfwf. WorkflowTypeID = 9 И (cfsm.Value IN("413"))) t2 ON t1.WorkflowID = t2.WorkflowID) a ON a.WorkflowID = cs.WorkflowID;

Столбец StageCode представляет собой тип varchar (1024).

У меня есть точно такая же база данных и схема на двух отдельных механизмах БД, один из которых - mysql (5.6), а другой - mariadb (10.4).

Выполнение запросов на mariadb привело к очень разным временам ответа:

Запрос A: около 5 секунд.

Запрос B: около 0,2 секунды

Просто добавив дополнительное поле из cs, время отклика увеличивается во много раз. Это не разовое поведение, так как это происходит каждый раз. Также обратите внимание, что для обоих запросов выбран cs.WorkflowID.

Выполнение вышеуказанных запросов в mysql приводит к быстрым ответам, оба возвращаются менее чем за 0,1 секунды.

Объяснение проблемного запроса 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)

На мариадб:

+------+-------------+---------------------+--------+----------------------------+--------------------+---------+--------------------------------------------------+------+-------------+
| 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)

Может ли кто-нибудь определить, что происходит не так? Обе машины имеют одинаковые ресурсы (ОЗУ/Диск)

ИЗМЕНИТЬ 1:

Такое же странное поведение проявляется, если вместо StageCode(Varchar 1024) я использую другое поле CardsSettings(cs) с типом tinyint или int(10). Поэтому я сомневаюсь, что это связано с длинными столбцами. Однако в таблице CardsSettings есть пара текстовых столбцов, а также пара 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]> SELECT tasks.TaskID, tasks.CardID, tasks.CritPath, tasks.ReworkCount, cs.WorkflowID, cs.StageCode, 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 (ВЫБЕРИТЕ WorkflowID из Workflow, где IsWFActive = "YES" и LastUpdatedDateTime МЕЖДУ "2018-11-21 23:59:59" и "2019-11-21 23:59:59" И WorkflowTypeID = 9) t1 INNER JOIN ( ВЫБЕРИТЕ «413» в качестве идентификатора рабочего процесса) t2 ON t1.ID рабочего процесса = t2.ID рабочего процесса) a ON a.ID рабочего процесса = cs.ID рабочего процесса;

Теперь это работает очень быстро на mariadb (~ 0,1 секунды).

ИЗМЕНИТЬ 3:

Редактирование 2 дало мне некоторое представление о том, почему это может происходить, ясно, что подзапрос t2 не вычислялся априори (я могу ошибаться). Поэтому я изменил запрос, чтобы использовать WHERE t1.WorkflowID IN (...) t2 вместо INNER JOIN следующим образом:

ВЫБЕРИТЕ tasks.TaskID, tasks.CardID, tasks.CritPath, tasks.ReworkCount, cs.WorkflowID, cs.StageCode, 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" и LastUpdatedDateTime МЕЖДУ "2018-11-21 23:59:59" и "2019-11-21 23:59:59" И WorkflowTypeID = 9) t1 WHERE t1.WorkflowID IN ( SELECT formSubCardSettings.WorkflowID FROM CardsSettings AS formSubCardSettings ВНУТРЕННЕЕ СОЕДИНЕНИЕ 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.FormmissionIN custom_submet_inner JO ata cfsm ON cfsm.FormSubmissionID = cfs.FormSubmissionID И cfsm.FormMetaID = cfm.FormMetaID ВНУТРЕННЕЕ СОЕДИНЕНИЕ Рабочий процесс cfwf ON cfwf.WorkflowID = formSubCardSettings.WorkflowID ГДЕ cf.FormTitle = "Инициация проекта" И cfm.FieldLabel = "wid" И cfwf. WorkflowTypeID = 9 И (cfsm.Value IN("413")))) a ON a.WorkflowID = cs.WorkflowID;

А теперь и запрос mariadb работает быстро ~ 0,2 сек.

Несмотря на то, что благодаря этому удару и испытанию мне удалось быстро ответить на запрос, может ли кто-нибудь попытаться разобраться во всем этом?


mysql mariadb response-time
person Ouroboros    schedule 21.11.2019    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Когда строка «слишком велика», значения некоторых столбцов записываются в «незаписываемое» хранилище. Повторное чтение строки (SELECTing) требует извлечения из этого другого места. Если данные не кэшируются в ОЗУ, то это еще один удар по диску. Обращение к диску требует времени.

Похоже, в таблице много столбцов? Многие TEXT или большие VARCHARs? Это может объяснить медлительность первого запроса.

Одно из мест, где оптимизаторы MySQL и MariaDB расходятся, — это обработка подзапросов.

Есть еще две возможные проблемы с дополнительным столбцом...

  • Это выборка как вторая (?) Таблица запроса, и ее нужно перетаскивать для остальной части запроса.
  • Если требуется промежуточная временная таблица (не могу сказать по EXPLAIN), она, вероятно, будет вынуждена быть MyISAM, а не MEMORY. Это из-за большого VARCHAR(1024), даже если фактических данных мало. MyISAM медленнее, чем MEMORY. (Если какой-то другой столбец «большой», то MyISAM уже потребуется, поэтому этот ответ не применяется.)

Эта конструкция особенно сложна, и с ней обращались по-разному:

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

Обратите внимание, что оптимизатору будет предложено сначала отказаться от производной таблицы (что обычно является оптимальным), а затем перейти к cs через WorkflowID. Наконец, остальные необходимые столбцы включены («покрытие»).

Для дальнейшего обсуждения просьба предоставить SHOW CREATE TABLE.

person Rick James    schedule 24.11.2019
comment
CardsSettings имеет WorkflowID в качестве внешнего ключа в таблицу Workflow, где WorkflowID — это PKEY. Кроме того, TaskID является первичным ключом в задачах. - person Ouroboros; 25.11.2019
comment
Интересно, что проблема возникает в тот момент, когда я добавляю любой столбец из CardsSettings. Поэтому, даже если я добавлю выбор для поля tinyint в таблице CardsSettings вместо StageCode, это также займет много времени. То же самое касается поля int(10). Так может быть проблема не в varchar(1024)? - person Ouroboros; 25.11.2019
comment
@Ouroboros - я добавил еще кое-что. Примечание. Включение varchar(1024) в индекс может быть невозможным. Это может работать на 10.4, но не работать на 5.6. Что такое ROW_FORMAT таблицы? - person Rick James; 25.11.2019