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

[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-скрипт, либо что-то другое. Буду очень признателен любым решения. Заранее спасибо.

 

[c0dex]

EXPLAIN для начала

 

[Вурдалак]

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

(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

Первое будет включать в себя и последнее.

 

[Romen]

Разъясните пожалуйста, немного не понимаю.

 

[Romen]

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) и записывание в отдельную таблицу вместе с датой агрегации. Тогда в конце месяца ты будешь работать с меньшим количеством данных.

 

[Vin-Diesel]

VIEW:
SELECT abonent, CONCAT(SUM(delivered),'/',SUM(not_delivered)) FROM tbl1 GROUP BY Первый символ от(channel_info)

И запрос присобачить с итерацией, чтоли?)

 

[Romen]

Очень это больше 4 часов, а надеюсь сделать этот процесс так чтобы выгрузка данных занимала не более 5-10 минут.
Не обязательно в MySQL делать, вот сейчас думаю над другим решением, может AWK или как-то так.

 

[c0dex]

Romen
SHOW CREATE table покажи, там сдается мне у тебя нет индексов вообще

 

[SiZE]

Ваще огонь

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

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

 

[Gas]

да там по explain'у видно что индексы вообще не используются.

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

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

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

потом проверь свой запрос, если тормозит, покажи ещё раз explain.

 

[Gas]

Ну и сам запрос логически, конечно, не верен. Тебе нужна статистика за месяц, а в подзапросах у тебя нет условия на период, то-есть 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, который я рекомендовал.

 

[Вурдалак]

Chushkin, да кого это волнует вообще?

 

[Вурдалак]

Твои цифры, полученные на примере, высосанном из пальца. Ты занимаешься онанизмом, но нам-то зачем об этом знать?

 

[Romen]

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

 

[Gas]

говорит что тупо не получится добавить

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

 

[prolis]

Это хороший пример для того, что бы создать рядом агрегирующую таблицу по дням и абонентам.
После закрытия дня по крону добавлять в неё агрегирующие записи, ну а там уже и индексы и весь фарш.
А на операционный биллинг индексы конечно лучше не вешать.

 

[Jon]

А сколько база весит? Ради интереса. Работал с базой запчастей, 2 гига весила, вообще проблем с запросами не было.