MySQL огромные таблицы JOIN делает крах базы данных
После моего недавнего вопросаВыберите информацию из последнего элемента и присоединитесь к общей суммеУ меня проблемы с памятью при генерации таблиц
У меня две таблицыsales1
а такжеsales2
как это:
id | даты | клиент | продажа
С этим определением таблицы:
CREATE TABLE sales (
id int auto_increment primary key,
dates date,
customer int,
sale int
);
sales1
а такжеsales2
имеют такое же определение, ноsales2
имеетsale=-1
в каждой области. Клиент может быть ни в одной, ни в одной, ни в обеих таблицах. Обе таблицы имеют около 300 000 записей и намного больше полей, чем указано здесь (около 50 полей). Они InnoDB.
Я хочу выбрать для каждого клиента:
количество покупокпоследняя стоимость покупкиобщая сумма покупок, когда она имеет положительное значениеЯ использую запрос:
SELECT a.customer, count(a.sale), max_sale
FROM sales a
INNER JOIN (SELECT customer, sale max_sale
from sales x where dates = (select max(dates)
from sales y
where x.customer = y.customer
and y.sale > 0
)
)b
ON a.customer = b.customer
GROUP BY a.customer, max_sale;
Проблема в:
Я должен получить результаты, которые мне нужны для определенных расчетов, разделенных по датам: информация о 2012 году, информация о 2013 году, а также информация за все годы вместе.
Всякий раз, когда я делаю один год, на хранение всей информации уходит 2-3 минуты.
Но когда я пытаюсь собрать информацию за все годы, база данных падает, и я получаю сообщения вроде:
InternalError: (InternalError) (1205, u'Lock wait timeout exceeded; try restarting transaction')
Кажется, что объединение таких огромных таблиц слишком много для базы данных. Когда яexplain
запрос, почти весь процент времени приходит.creating tmp table
Я думал, разбивая сбор данных на кварталы. Мы получаем результаты каждые три месяца, а затем присоединяемся и сортируем их. Но я думаю, что это последнее объединение и сортировка будет слишком много для базы данных снова.
ТакЧто бы вы посоветовали экспертам оптимизировать эти запросы, если я не могу изменить структуру таблиц?