Долгое время обработки запроса, работа с большими базами

  • Автор темы Автор темы Romen
  • Дата начала Дата начала

Romen

Новичок
Добрый день.

Есть вот такой несложный запрос :

SELECT DISTINCT abonent, COUNT(*) AS cnt,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '1%' AND abonent = a.abonent) AS channel1,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '2%' AND abonent = a.abonent) AS channel2,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '3%' AND abonent = a.abonent) AS channel3,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '4%' AND abonent = a.abonent) AS channel4,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '5%' AND abonent = a.abonent) AS channel5,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '6%' AND abonent = a.abonent) AS channel6,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '7%' AND abonent = a.abonent) AS channel7,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '8%' AND abonent = a.abonent) AS channel8,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '9%' AND abonent = a.abonent) AS channel9,
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '10%' AND abonent = a.abonent) AS channel10
FROM tbl1 a WHERE add_date>='2012-06-01' AND add_date<'2012-07-01'
GROUP BY abonent ORDER BY abonent

Этим запросом мы должны получить статистику за месяц, по номерам абонентов и доставлялись ли сообщения до них через определенный канал, либо нет.
Проблема в том что объем данных очень большой и запрос может выполнятся ну очень долго.
Помогите пожалуйста с решением задачи, другого пути кроме запроса пока не вижу, хотя возможно мне может помочь какой-либо PHP-скрипт, либо что-то другое. Буду очень признателен любым решения. Заранее спасибо.
 
Периодически аргегировать данные за небольшие промежутки времени (час, день) в зависимости от количества набираемых данных.

(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '1%' AND abonent = a.abonent) AS channel1,
...
(SELECT CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 WHERE channel_info LIKE '10%' AND abonent = a.abonent) AS channel10
Первое будет включать в себя и последнее.
 
EXPLAIN

id select_type t able type possible_keys key key_len ref rows Extra
1899-12-31 PRIMARY a ALL \N \N \N \N 1594979 Using where; Using temporary; Using filesort
1900-01-02 DEPENDENT SUBQUERY tbl1 ALL \N \N \N \N 10300861 Using where
1900-01-01 DEPENDENT SUBQUERY tbl1 ALL \N \N \N \N 10300861 Using where


для 2х подзапросов
 
Под агрегацией я подразумеваю периодическое предварительное вычисление вот этих самых SUM(delivered), SUM(not_delivered) и записывание в отдельную таблицу вместе с датой агрегации. Тогда в конце месяца ты будешь работать с меньшим количеством данных.
 
VIEW:
SELECT abonent, CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 GROUP BY Первый символ от(channel_info)

И запрос присобачить с итерацией, чтоли?)
 
Очень это больше 4 часов, а надеюсь сделать этот процесс так чтобы выгрузка данных занимала не более 5-10 минут.
Не обязательно в MySQL делать, вот сейчас думаю над другим решением, может AWK или как-то так.
 
Romen
SHOW CREATE table покажи, там сдается мне у тебя нет индексов вообще
 
Ваще огонь :)

Что сразу бросилось в глаза

1. Создаем в таблице столбец chanel_num куда записываем число 1, 2, 3 и тд. Ставим индекс. Смотрим прибавку к скорости используя chanel_num=1 вместо LIKE '1%'
2. И не пойму логику запроса. Выбираем всех абонентов за определенный период, а потом в вложенных суммируете для абонента за весь период. Уверен вы забыли добавить в вложенные запросы add_date>='2012-06-01' AND add_date<'2012-07-01'
3. Этот запрос надо декомпозировать
 
да там по explain'у видно что индексы вообще не используются.

Romen
добавить индексы:

alter table tbl1 add index add_date(add_date);
alter table tbl1 add index abonent_channel(abonent, channel_info(2));

только на создание индексов на 10M записей может минимум несколько минут уйти, смотри если это критично.

потом проверь свой запрос, если тормозит, покажи ещё раз explain.
 
Ну и сам запрос логически, конечно, не верен. Тебе нужна статистика за месяц, а в подзапросах у тебя нет условия на период, то-есть channel1...10 считаются за всё время.

Если ничего не менять, а по-хорошему уже посоветовали channel вынести в какое-то отдельное интовое поле, то я бы сделал без подзапросов, типа

Код:
SELECT DISTINCT abonent, COUNT(*) AS cnt, 
       CONCAT(SUM(IF('1'=LEFT(channel_info, 1),delivered,0)),'/',SUM(IF('1'=LEFT(channel_info, 1),not_delivered,0))) AS channel1,
       CONCAT(SUM(IF('2'=LEFT(channel_info, 1),delivered,0)),'/',SUM(IF('2'=LEFT(channel_info, 1),not_delivered,0))) AS channel2,
....
FROM tbl1 a 
WHERE add_date>='2012-06-01' AND add_date<'2012-07-01' 
GROUP BY abonent 
ORDER BY abonent

правда всё равно остаётся вопрос с channel 1 и channel 10, но чтоб тут что-то советовать, нужно знать какой символ может быть после 1 в случае channel 1, чтоб не смешивать статистику с 10-м.

для приведённого выше запроса не нужен и составной индекс abonent_channel, который я рекомендовал.
 
Твои цифры, полученные на примере, высосанном из пальца. Ты занимаешься онанизмом, но нам-то зачем об этом знать?
 
Сорри, уезжал из города, инета не было.
Спасибо всем за советы.
Но вот к сожалению индексы добавить не могу, база данных очень большая, даже пробовал решить этот вопрос с тех.директором, говорит что тупо не получится добавить.
Сейчас буду пробовать совет Gas'а .
 
говорит что тупо не получится добавить

Судя по explain'у в таблице ~10M записей - это не много. Технических причин чтоб не создался индекс по add_date нет, другое дело что в момент создания индекса таблица залочится - это да. По классике эта ситуация разруливается настройкой master-slave репликации и уже на слейве, можно играться с индексами и генерацией отчётов.
 
Это хороший пример для того, что бы создать рядом агрегирующую таблицу по дням и абонентам.
После закрытия дня по крону добавлять в неё агрегирующие записи, ну а там уже и индексы и весь фарш.
А на операционный биллинг индексы конечно лучше не вешать.
 
А сколько база весит? Ради интереса. Работал с базой запчастей, 2 гига весила, вообще проблем с запросами не было.
 
Сверху