Как ускорить соединение таблиц mariadb

Этот ответ основан на моих знаниях об индексации в целом; У MariaDB могут быть какие-то более специализированные опции, о которых я не знаю.

Однако в целом индексы ускоряют запросы двумя способами.

• Имея только необходимые столбцы, что означает меньше данных для чтения и обработки
• Путем сортировки соответствующим образом, чтобы помочь в обработке

Во-первых, вам обычно нужен индекс покрытия.

Для второго это включает

• Сортируются так же (например, индексируются по тем же полям), что и таблицы, к которым они присоединяются в запросе.
• Сортировка, чтобы предложения WHERE и другие типы фильтрации могли напрямую использовать сортировку для перехода к соответствующему месту в индексе/таблице.

На практике часто лучшим улучшением производительности является последнее, однако в вашем коде нет предложений WHERE. Если (как обычно) пользователи фильтруют результаты (например, показывают мне только результаты, где ProductName = 'Handbag'), вам может потребоваться настроить индексы для них (но об этом чуть позже).

Покрытие индексов для запроса выше

Я думаю, что с текущим запросом (и без фильтрации и т. д.) быстрее всего вы можете получить два индекса.

CREATE INDEX `IX_prices_ProductIdentifier` ON `prices`.`prices_table` 
    (`ProductIdentifier`,
    `Datum`,
    `Retailer`,
    `Prijs`);
CREATE INDEX `IX_productmatch_ProductIdentifier` ON `prices`.`product_match_table`
    (`ProductIdentifier`,
    `Merk`,
    `Product`,
    `Formaat`); 

Они обеспечивают покрывающие индексы для показанного запроса, и оба они отсортированы одинаково (по productIdentifier), чтобы упростить соединение.

Поиск/фильтрация (не указано в исходном примере)

Однако, если люди часто сначала выполняют поиск по определенному полю, то имеет смысл изменить порядок полей в соответствующей таблице (чтобы искомое поле было первым) или иметь несколько индексов с полем поиска впереди.

Например, люди могут искать определенные значения в pr.Retailer, pm.Merk или pm.Product. Поэтому вы можете добавить эти дополнительные индексы

CREATE INDEX `IX_prices_Retailer` ON `prices`.`prices_table` 
    (`Retailer`,
    `ProductIdentifier`,
    `Datum`,
    `Prijs`);
CREATE INDEX `IX_productmatch_Merk` ON `prices`.`product_match_table`
    (`Merk`,
    `ProductIdentifier`, 
    `Product`,
    `Formaat`); 
CREATE INDEX `IX_productmatch_Product` ON `prices`.`product_match_table`
    (`Product`,
    `ProductIdentifier`, 
    `Merk`,
    `Formaat`); 

Обратите внимание на то, что порядок полей имеет значение. Данные (индекс) сортируются по первому полю, затем по второму полю, затем по третьему полю и т. д. Чтобы эффективно использовать индекс, ваше предложение filtering/WHERE должно включать как минимум первое поле, если не больше.

В качестве альтернативы этим индексам (для фильтрации) можно использовать исходные два индекса, как указано выше, но затем поместить отдельный индекс в каждое из полей, по которым они могут выполнять поиск, например, если пользователи могут фильтровать по розничному продавцу, мерку и продукту. , затем создайте

• один индекс на pr.Retailer
• один на pm.Merk и
• один на pm.Product

Предупреждения

Добавление индексов делает вставку данных в соответствующую таблицу (и часто удаление/обновление) медленнее, чем если бы индексов не было. Причина в том, что нужно не только обновить данные в таблице, но и обновить индекс(ы).

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