VPF::оптимизация запроса - Форум программистов

kuzyara

Дата 4.12.2012, 15:09 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 215
Регистрация: 13.11.2006

Репутация: нет
Всего: 1

Собираю данные о скорости с netflow-коллектора домашней сетки в mysql perl-скриптом в таблицу flowdata2 вида:

Код


mysql> select * from flowdata2 limit 20;
+---------------------+------+----------+-----------+
| time                | ip   | in_bytes | out_bytes |
+---------------------+------+----------+-----------+
| 2012-12-04 20:30:00 |  255 |       68 |         0 |
| 2012-12-04 20:30:00 |   35 |        0 |        40 |
| 2012-12-04 20:30:00 |   31 |     6654 |      7932 |
| 2012-12-04 20:30:00 |   14 |      361 |       305 |
| 2012-12-04 20:30:00 |   11 |    86995 |     11667 |
| 2012-12-04 20:30:00 |   10 |  1012436 |     81901 |
| 2012-12-04 20:30:00 |    8 |      391 |       296 |
| 2012-12-04 20:30:00 |    7 |      474 |      1167 |
| 2012-12-04 20:30:00 |    6 |     2393 |         0 |
| 2012-12-04 20:30:00 |    3 |   155342 |     33976 |
| 2012-12-04 20:30:00 |    1 |     1029 |      2599 |
| 2012-12-04 20:29:00 |  255 |      606 |         0 |
| 2012-12-04 20:29:00 |   35 |      266 |       234 |
| 2012-12-04 20:29:00 |   31 |    17389 |     10134 |
| 2012-12-04 20:29:00 |   14 |    37914 |      5187 |
| 2012-12-04 20:29:00 |   11 |   152718 |     36188 |
| 2012-12-04 20:29:00 |   10 |  1032846 |     86335 |
| 2012-12-04 20:29:00 |    8 |       56 |       994 |
| 2012-12-04 20:29:00 |    7 |     2585 |      4392 |
| 2012-12-04 20:29:00 |    6 |     2813 |        84 |
+---------------------+------+----------+-----------+
20 rows in set (0.00 sec)

Внутри данные о входящем-исходящем трафике для каждого ip 192.168.0.* поминутно.

Задача:
для отрисовки графика на веб-страничке выбрать записи в заданном интервале времени в килобайтах. Если для ip-адреса в какой-то момент времени нет данных о трафике - заполнить нулями. (Например если бы в таблице были бы только вышеприведённые записи, то нужно было бы добавить 2 строки за 29 минуту по адресам 3 и 1 с нулями в in_kbytes и out_kbytes).

В php я реализую это так:

Код


$t1=$_GET["t1"];
$t2=$_GET["t2"];
$table="flowsdb.flowdata2";

mysql_query("
CREATE TABLE aaa (
time INTEGER ,
ip INTEGER ,
in_kbytes INTEGER ,
out_kbytes INTEGER 
)") or die("create query failed: ".mysql_error());

mysql_query("
INSERT INTO aaa 
SELECT UNIX_TIMESTAMP(time) as 'time', ip, ceil(in_bytes/1024) as in_kbytes, ceil(out_bytes/1024) as out_kbytes 
FROM $table 
WHERE time>FROM_UNIXTIME($t1) AND time<FROM_UNIXTIME($t2) 
GROUP BY time,ip 
ORDER BY time, --SUBSTRING_INDEX(ip, '.',-1) 
") or die("insert1 failed: ".mysql_error());

mysql_query("
INSERT INTO aaa
SELECT DISTINCT st.time, st.ip, st.in_kbytes, st.out_kbytes
FROM ( 
  SELECT t1.time, t2.ip, 0 as in_kbytes, 0 as out_kbytes
  FROM 
    (select distinct time from aaa) as t1,
    (select distinct ip from aaa) as t2 
) as st
WHERE NOT EXISTS (
  SELECT 1
  FROM aaa as t2
  WHERE t2.time = st.time
    AND t2.ip = st.ip
)") or die("insert2 failed: ".mysql_error());

$query = "select * from aaa order by time, ip";
$res = mysql_query($query) or die("query failed: ".mysql_error());

mysql_query("drop table aaa");

или с подсветкой sql:

Код


-- создаем временную таблицу (используется в следующем запросе более одного раза)
CREATE TABLE aaa (
time INTEGER ,
ip INTEGER ,
in_kbytes INTEGER ,
out_kbytes INTEGER 
);

-- вставляем в неё данные за выбранный период в килобайтах
INSERT INTO aaa 
SELECT UNIX_TIMESTAMP(time) as 'time', ip, ceil(in_bytes/1024) as in_kbytes, ceil(out_bytes/1024) as out_kbytes 
FROM $table 
WHERE time>FROM_UNIXTIME($t1) AND time<FROM_UNIXTIME($t2) 
GROUP BY time,ip 
ORDER BY time, --SUBSTRING_INDEX(ip, '.',-1) ;

INSERT INTO aaa         -- #3 и добавляем их к искомой выборке
SELECT DISTINCT st.time, st.ip, st.in_kbytes, st.out_kbytes
FROM (                  -- #1 создаем декартово произведение time и ip с нулями в килобайтах
  SELECT t1.time, t2.ip, 0 as in_kbytes, 0 as out_kbytes
  FROM 
    (select distinct time from aaa) as t1,
    (select distinct ip from aaa) as t2 
) as st
WHERE NOT EXISTS (      -- #2 выбираем "нулевые" записи отсутствующих сочетаний time/ip в aaa
  SELECT 1
  FROM aaa as t2
  WHERE t2.time = st.time
    AND t2.ip = st.ip
);

-- получаем получившийся "полный" набор данных
select * from aaa order by time, ip;

-- удаляем временную таблицу
drop table aaa;

Проблема: при обработке запроса за последние 2 часа мой старенький атлончик задумывается на пол минуты и больше, можно ли оптимизировать запрос?

upd: Вот он, этот коварный тип гражданской наружности!

Код


mysql> INSERT INTO aaa
    -> SELECT DISTINCT st.time, st.ip, st.in_kbytes, st.out_kbytes
    -> FROM ( 
    ->   SELECT t1.time, t2.ip, 0 as in_kbytes, 0 as out_kbytes
    ->   FROM 
    ->     (select distinct time from aaa) as t1,
    ->     (select distinct ip from aaa) as t2 
    -> ) as st
    -> WHERE NOT EXISTS (
    ->   SELECT 1
    ->   FROM aaa as t2
    ->   WHERE t2.time = st.time
    ->     AND t2.ip = st.ip
    -> );
Query OK, 657 rows affected (13.24 sec)
Records: 657  Duplicates: 0  Warnings: 0

mysql>

Это сообщение отредактировал(а) kuzyara - 4.12.2012, 15:38

--------------------

подпись

Akina

Дата 4.12.2012, 15:21 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

1) Покажите DDL на таблицу flowdata2. Больше всего интересует, что там с индексами...
2) Есть ли таблица существующих адресов? потому как догадываться, что некий адрес, отсутствующий в статистике, таки должен быть отображён - занятие неблагодарное...

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

kuzyara

Дата 4.12.2012, 15:46 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 215
Регистрация: 13.11.2006

Репутация: нет
Всего: 1

Цитата(Akina @ 4.12.2012, 20:21

)

1) Покажите DDL на таблицу flowdata2.

Код


mysql> show create table flowdata2;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                     |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| flowdata2 | CREATE TABLE `flowdata2` (
  `time` datetime DEFAULT NULL,
  `ip` int(11) DEFAULT NULL,
  `in_bytes` bigint(20) DEFAULT NULL,
  `out_bytes` bigint(20) DEFAULT NULL,
  UNIQUE KEY `time_ip` (`time`,`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Цитата(Akina @ 4.12.2012, 20:21

)

2) Есть ли таблица существующих адресов?

не, одни компы добавляются, другие уходят, постоянной таблицы нет, но их всего ~30

--------------------

подпись

Akina

Дата 4.12.2012, 15:55 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(kuzyara @ 4.12.2012, 16:46

)

одни компы добавляются, другие уходят, постоянной таблицы нет, но их всего ~30

Должна быть таблица. Типа (IP - Дата ввода - Дата отключения - прочее).

1) Выполните генерацию таблицы времён с использованием переменных, а не подзапросом;
2) Создайте таблицу IP-адресов, и используйте её вместо подзапроса (вернее, в подзапросе - надо же выбрать действующие);
3) Выполните декартово умножение этих таблиц, и привяжите к результату справа таблицу данных.

Хотя я лично считаю, что не нужно получать в запросе нули для отсутствующих пар адрес-время. С этим прекрасно справится скриптовая часть непосредственно в момент формирования графика.

Это сообщение отредактировал(а) Akina - 4.12.2012, 15:56

--------------------

tzirechnoy

Дата 4.12.2012, 17:01 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1173
Регистрация: 30.1.2009

Репутация: 2
Всего: 16

Второй запрос можно тупо выкинуть, нули для недостающих ip показывать чисто скриптом.

PS Впрочем, если хотите красиво и быстро -- добавляйте данные [такжэ] в rrdtool. Ну, в 30 баз (на каждый ip).

kuzyara

Дата 4.12.2012, 17:47 (ссылка)

(нет голосов)

Загрузка ...

Бывалый

Профиль
Группа: Участник
Сообщений: 215
Регистрация: 13.11.2006

Репутация: нет
Всего: 1

в итоге сам разобрался, заменил ресурсоемкий запрос на

Код


create unique index time_ip on aaa(time,ip);
INSERT IGNORE INTO aaa
  SELECT t1.time, t2.ip, 0 as in_kbytes, 0 as out_kbytes
  FROM 
    (select distinct time from aaa) as t1,
    (select distinct ip from aaa) as t2;

--------------------

подпись

Akina

Дата 4.12.2012, 21:08 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

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

--------------------

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »