MariaDB очень медленные запросы относительно соединений соединений соединений

Этот шаблон может быть особенно медленным:

JOIN ( SELECT ... ) ON ...
JOIN ( SELECT ... ) ON ...

Найдите другой способ сформулировать запрос.

Есть такая вещь, как "сверхнормализация".

Рассмотрите возможность использования ENUM вместо нормализации. Это значительно увеличило бы возможность предоставления полезного указателя.

Схема «ключ-значение», также известная как EAV, проблематична. Подумайте, действительно ли вам это нужно. Если вам необходимо сохранить EAV, следуйте советам здесь. Подробнее об EAV.

Скрытие индексированных столбцов внутри вызовов функций предотвращает использование индекса.

После того, как вы продвинулись в одном из этих направлений, укажите SHOW CREATE TABLE и EXPLAIN SELECT, чтобы получить дальнейшую помощь.

Как упомянул Рик, MySQL/MariaDB не может оптимизировать несколько подзапросов, объединенных в предложении FROM (в MySQL 5.7 были сделаны некоторые улучшения, но этого недостаточно).

Поэтому я бы рекомендовал изменить этот шаблон, извлекая соответствующие подзапросы во временные таблицы, которые можно индексировать для более быстрого поиска.

Прежде чем мы начнем, общее замечание о командах создания индекса: возможно, что некоторые операторы ALTER не будут работать как есть, потому что я не знаю типов полей. В некоторых случаях вам придется немного изменить их, сообщив базе данных о длине столбцов (например, в столбцах TEXT).

Во-первых, мы создадим индексы, чтобы основной запрос выполнялся как можно быстрее. Это нужно сделать только один раз (надеюсь, я ничего не пропустил):

ALTER TABLE `concern` ADD INDEX `concern_idx_name_id` (`name`, `id`);
ALTER TABLE `datatype` ADD INDEX `datatype_idx_name_id` (`name`, `id`); 
ALTER TABLE `design` ADD INDEX `design_idx_name_id` (`name`, `id`);
ALTER TABLE `metadata` ADD INDEX `metadata_idx_id_data` (`id`, `data`); 
ALTER TABLE `metric` ADD INDEX `metric_idx_idc_idd_iddt_ido_id_idm` (`idc`, `idd`, `iddt`, `ido`, `id`, `idm`);
ALTER TABLE `operation` ADD INDEX `operation_idx_name_id` (`name`, `id`); 
ALTER TABLE `parameter` ADD INDEX `parameter_idx_id` (`id`); ALTER TABLE `parametervalue` ADD INDEX `parametervalue_idx_id_idp` (`id`, `idp`);
ALTER TABLE `parametervaluemetric` ADD INDEX `parametervaluemetric_idx_idm_idpv` (`idm`, `idpv`);
ALTER TABLE `technology` ADD INDEX `technology_idx_id` (`id`);
ALTER TABLE `technologymetric` ADD INDEX `technologymetric_idx_idm_idt` (`idm`, `idt`);
ALTER TABLE `technology` ADD INDEX `technology_idx_name_id` (`name`, `id`); 
ALTER TABLE `technologymetric` ADD INDEX `technologymetric_idx_idt_idm` (`idt`, `idm`);
ALTER TABLE `technology` ADD INDEX `technology_idx_name_id` (`name`, `id`); 
ALTER TABLE `technologymetric` ADD INDEX `technologymetric_idx_idt_idm` (`idt`, `idm`);
ALTER TABLE `parameter` ADD INDEX `parameter_idx_name_id` (`name`, `id`); 
ALTER TABLE `parametervalue` ADD INDEX `parametervalue_idx_id_idp_value` (`id`, `idp`, `value`);
ALTER TABLE `parametervaluemetric` ADD INDEX `parametervaluemetric_idx_idpv_idm` (`idpv`, `idm`);

Теперь идет часть, которая должна выполняться для каждого выполнения запроса:

1. Создайте временные таблицы.
2. Создайте индексы для временных таблиц.
3. Запустите основной запрос.

Начнем с создания соответствующих временных таблиц:

CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT
        technologymetric.idm 
    FROM
        technologymetric 
    JOIN
        technology 
            ON technology.id = technologymetric.idt 
            AND technology.name = 'AIAK';

CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS SELECT
        technologymetric.idm 
    FROM
        technologymetric 
    JOIN
        technology 
            ON technology.id = technologymetric.idt 
            AND technology.name = 'DSA';

CREATE TEMPORARY TABLE IF NOT EXISTS temp3 AS SELECT
        parametervaluemetric.idm,
        parametervalue.value AS xplotvarvalue 
    FROM
        parametervaluemetric 
    JOIN
        parametervalue 
            ON parametervalue.id = parametervaluemetric.idpv 
    JOIN
        parameter 
            ON parameter.id = parametervalue.idp 
            AND parameter.name = 'KeySize' 
            AND parametervalue.value <= 1024;

Теперь давайте создадим индексы для временных таблиц:

ALTER TABLE `temp1` ADD INDEX `temp1_idx_idm` (`idm`);
ALTER TABLE `temp2` ADD INDEX `temp2_idx_idm` (`idm`);
ALTER TABLE `temp3` ADD INDEX `temp3_idx_idm_xplotvarvalue` (`idm`, `xplotvarvalue`);

И теперь вы можете запустить этот преобразованный основной запрос:

SELECT
        DISTINCT metric.id,
        a2.xplotvarvalue,
        metric.joules,
        metric.seconds,
        metadata.data 
    FROM
        metric 
    JOIN
        concern 
            ON concern.id = metric.idc 
            AND concern.name = 'Security' 
    JOIN
        design 
            ON design.id = metric.idd 
            AND design.name = 'Cryptography' 
    JOIN
        datatype 
            ON datatype.id = metric.iddt 
            AND datatype.name = 'String' 
    JOIN
        operation 
            ON operation.id = metric.ido 
            AND operation.name = 'KeyGeneration' 
    JOIN
        technologymetric 
            ON technologymetric.idm = metric.id 
    JOIN
        temp1 AS a0 
            ON a0.idm = technologymetric.idm 
    JOIN
        temp2 AS a1 
            ON a1.idm = technologymetric.idm 
    JOIN
        technology 
            ON technology.id = technologymetric.idt 
    JOIN
        parametervaluemetric 
            ON parametervaluemetric.idm = metric.id 
    JOIN
        parametervalue 
            ON parametervalue.id = parametervaluemetric.idpv 
    JOIN
        temp3 AS a2 
            ON a2.idm = parametervaluemetric.idm 
    JOIN
        parameter 
            ON parameter.id = parametervalue.idp 
    JOIN
        metadata 
            ON metadata.id = metric.idm 
    ORDER BY
        metadata.data,
        FIELD('parameter.name',
        'KeySize')