Собираю данные о скорости с 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>
|
|
)