Как улучшить соединение MySQL, чтобы быть более эффективным?

Question

Я пытаюсь присоединиться к таблице df1 с df2. Проблема в том, что df2 - это очень длинная таблица с 40 миллионами наблюдений, и я не могу получить объединенный результат в MySQL после ожидания более 48 часов. Поэтому я хочу спросить, есть ли способ повысить эффективность этого объединения. Я уже добавил индекс для столбца Tag для обеих таблиц.

df1 и df2 имеют одинаковую структуру, и вот пример:

|Id        |Tag
| -------- | --------------------------------------------
|1         |c#
|1         |winforms
|1         |type-conversion
|1         |decimal
|2         |.net
|2         |decimal
|3         |.net
|3         |math

Обе таблицы имеют два столбца, Id и Tag. Однако ни Id, ни Tag не являются уникально идентифицированными ключами. Только Id + Tag может быть уникально идентифицированным ключом. Что мне нужно, так это пропустить соединение df1 с df2 в столбце Tag. И вот мой код:

CREATE TABLE matched_outcome AS
SELECT df1.Id AS df1_Id, df2.Id AS df2_Id, COUNT(df2.Tag) AS overlapping
FROM df1
LEFT JOIN df2 ON df2.Tag=df1.Tag
GROUP BY df1.Id, df2.Id;

Answer 1

Я бы попробовал составной индекс (tag, id) для обеих таблиц в этом порядке.

Проверьте, используя план выполнения, если этот индекс получен.

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

Также вы должны запустить это и проверить, где ваш процесс застреваетSHOW FULL PROCESSLIST

Это может дать вам дальнейшие выводы.

Answer 2

"синдром инфляции-дефляции" происходит, когда вы делаете JOIN...GROUP BY, И это обычно приводит к неправильным (высоким) значениям для агрегатов (COUNT, SUM, так далее).

Я слишком озадачен тем, что должен делать запрос, чтобы помочь вам переписать его. Можете ли вы уточнить? Зачем LEFT? Что такое "перекрытие"?

Иногда обходной путь должен сделать что-то вроде:

SELECT df1.some_stuff,
       ( SELECT COUNT(*) FROM df2 WHERE Tag = df1.Tag ) AS overlapping
    FROM df1;

Это даже близко к тому, что вы хотите?