Обработка большого количества данных MySQL

242

28 февраля 2018, 06:45

Здравствуйте, товарищи! Возникла следующая проблема: имеется две таблицы с большим количеством аналитических данных. В одной таблице 5млн строк, в другой - 165млн. Если схематично, то таблицы выглядят так:

id   - int, AutoIncrement
int0 - int
int1 - int
int2 - int
data - int

Из таблиц нужно сделать выборку таким образом, чтобы были показаны только те результаты, значение поля data у которых встречается в таблице не менее N раз. Для примера возьмём N = 5. Вот такой запрос я написал:

select * from results r where (select count(*) from results where data = r.data group by data limit 1) >= 5 order by data;

Проверил запрос на маленькой табличке в 10 строк - работает. Но всю печаль ситуации я понял сейчас, ибо этот запрос на таблице в 5млн строк я запустил ровно 4 часа назад... Запрос до сих пор обрабатывается. Боюсь представить, что будет с таблицей в 165млн строк хД Видимо, я написал нечто ужасное и надо было все это дело реализовать как-то иначе. Прошу указать мне мои ошибки/предложить идеи для оптимизации

Answer 1

Коррелированный запрос на таком объёме данных, да ещё если версия сервера не самая распоследняя - это безнадёжно.

Простейший способ оптимизации - получение в подзапросе списка data, которые требуется вывести:

SELECT *
FROM results 
WHERE data IN ( SELECT data
                FROM results 
                GROUP BY data
                HAVING COUNT(*)>=5
              );

Ещё более разумное решение - предрасчёт данных, получаемых подзапросом, в дополнительной (или временной - но тогда её придётся строить для каждого экземпляра запроса) таблице. Т.е.

CREATE TABLE data_count (data int, cnt bigint);
CREATE INDEX count_data ON data_count (cnt, data);
INSERT INTO data_count (data, cnt)
    SELECT data, COUNT(*)
    FROM results 
    GROUP BY data;

И соответственно быстрая выборка

SELECT results.*
FROM results, data_count
WHERE results.data = data_count.data
  AND data_count.cnt >= 5;

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

Во всех случаях предполагается, что в исходной таблице имеется индекс с префиксным полем data.

Answer 2

Заодно проверьте настройки mysql, шардинг, если возможно делайте предварительную выборку чтобы уменьшить количество строк для последующего поиска, можете добавить поля в мелкую таблицу которые будет отвечать за количество записей в большой таблице, а также хранить строковой массив с их индексами и обновляйте их при добавлении новых записей. Стандартные советы.