Подсчет совпадений разных значений в mysql

#php #mysql #sql

В mysql ведётся лог недоступности оборудования. Встала задача считать, сколько раз
оборудование "падало" в заданном интервале.
Структура таблицы такая:

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(20)     | NO   | PRI | NULL    | auto_increment |
| ip          | varchar(15) | NO   |     | NULL    |                |
| power_state | varchar(10) | NO   |     | NULL    |                |
| power_time  | int(10)     | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+


На данный момент подсчитываю количество падений весьма извращенным способом (получаю
список ip из другой таблицы и делаю запрос в эту)
Понимаю, что можно сделать как-то по человечески, но как - не могу придумать..

$query="SELECT * FROM `device_data` WHERE `visible` = '1'"; //список ip
$result = mysql_query($query) or die(mysql_error());
for ($c=0; $c '$datefrom' AND `power_time` < '$dateto'");
    $num_rows = mysql_num_rows($down_query);

    echo "

    
        $ip
    
    
        $num_rows
    
";
}


Как результат - задача выполнена, но долго. Помогите оптимизировать, либо пните в
нужную сторону. 
    

Ответы

Ответ 1

1. JOIN

В принципе, про джойн уже написали. Это должно быть на автомате - если запрос в цикле,
то джойн. Делается он совсем несложно - берутся две таблицы и связываются по общему полю:

SELECT ip FROM power_data p, device_data d WHERE p.ip=d.ip 


и вот мы уже получили сводную таблицу, в которой к первой дописаны данные из второй,
подтянутые по ip=ip (причем из первой данные не берутся, если во второй нет связи.
За это отвечает тип джойна). Дальше к этому запросу можно дописывать любые операторы
как обычно. d и p - это алиасы, для сокращения писанины.

2. GROUP BY

Теперь GROUP BY. Это ведь совсем несложно. Сгруппировать по одинаковым IP и попросить
базу посчитать количество вхождений по ним. То есть - получить ровно те данные, которые
нам нужны: ip - цифра, ip - цифра... 

Причем сделано это будет внутри самой БД, без необходимости гонять все данные между
скриптом и базой:

SELECT p.ip, count(1) cnt 
    FROM power_data p, device_data d 
    WHERE p.ip=d.ip
        AND p.power_state=0 AND d.visible = 1
        AND p.power_time > [datefrom] AND p.power_time < [dateto]
    GROUP BY p.ip


EXPLAIN и индексы.

Здесь без готовой базы (причем заполненной данными - это важно), получить правильную
аналитику не получится. Генерить тестовые данные - настолько далеко мой альтруизм не
распространяется. Но обычно оптимизация запросов - это отдельный вопрос, который автору
велят задавать отдельным постом, включив все запросы, а так же вывод команды, которую
надо скопировать 1-в-1 и выполнить в консоли или phpmyadmin

3. EXPLAIN [тестируемый запрос]


Но в принципе можно дать пару рекомендаций и так: Надо смотреть в колонки key - актуально
задействованный ключ, и rows - количество затронутых запросом строк. Причем надо перемножить
между собой все цифры из этой колонки. В колонке key должно быть хоть что-то, а в колонке
rows - как можно меньшее число. 

Если наугад, чисто из опыта - то в device_data для этого запроса пригодился бы индекс
по ip,visible, а в power_data - по power_state,power_time

Эти первые три пункта - главное. С них должна начинаться любая оптимизация и именно
они дадут принципиальный прирост производительности, пренебрегать ими нельзя.

4. Мелкие оптимизации

Идея сократить размер данных в таблице - здравая. В первую очередь она касается самого
IPv4 адреса, который представляет из себя ничто иное, как обычный 4-байтовый int. И
может храниться в колонке соответствующего типа. Для конвертации в mysql есть функции
inet_aton() и inet_ntoa().
Разумеется, никаких проблем с WHERE эти функции не создают. Ну, то есть, если самому
не тупить и не писать WHERE inet_ntoa(ip) = '127.0.0.1', применяя функцию к каждой
записи в таблице.
Применять её надо к константе, оставляя поле как есть: 

WHERE ip = inet_aton('127.0.0.1')


и индекс будет работать прекрасно.

Также можно вместо слов писать в статусы цифры, и таким образом сократить размер
всей записи до 16 байт!

5. PDO

Как говорилось выше и все время говорится на всех сайтах и форумах, надо от от функций
mysql переходить к использованию PDO preparеd statements. Это даст сразу ТРИ эффекта: 


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


Главное правило при работе с PDO - все запросы, в которых используются какие-либо
переменные, должны выполняться строго через prepare()/execute(), а вместо переменных
в запрос должны вставляться пейсхолдеры:

$stmt = $pdo->prepare("SELECT inet_ntoa(p.ip) ip, count(1) cnt 
    FROM power_data p, device_data d 
    WHERE p.ip=d.ip
        AND p.power_state=0 AND d.visible = 1
        AND p.power_time > ? AND p.power_time < ?
    GROUP BY p.ip");
$stmt->execute(array($datefrom, $dateto));
$stats = $stmt->fetchALL(PDO::FETCH_KEY_PAIR);


в результате мы получим массив вида [ip => cnt] который очень удобно потом вывести
в шаблоне:

?>
 $cnt)

    
    






*) утверждение не совсем верно, поскольку есть случаи, в которых PDO не справляется



PS. Вот меня часто ругают за то отношение, которое я высказываю по отношению к сообществу
пхп в целом, и к тем его представителям, которые отвечают на вопроса на SO.

Ну скажите, какое может быть отношение, если три ответа, из которых 


один переливает из пустого в порожнее: спросив, не ip ли v4 хранится в базе (как
будто в варчар(15) можно запихнуть ipv6), потом, когда читатель ждёт уж рифмы "INT"...
предлагает тот же варчар, только в профиль. Плюс немасштабируемый IN. Плюс куча мелкой
непринципиальной косметики, причем не видно различия между действительно важными вещами
и мелочевкой.
а другие два смогли-таки в джойн, но груп бай уже не осилили. 


Потом в вопрос придут добрые самаритяне, исправят "несправедливо" проставленные минусы,
и все останутся довольны, а на просторах сети останется еще одна бессмысленная страница.


Ответ 2

На самом деле я не знаком ни с PHP ни с MySql, но возможно мои советы сомгут Вам помочь. 
Если я правильно понял, то  сначала получаете список всех IP и потом выполняете в
цикле много запросов по каждому из IP. Кажется,
 что имеет смысл сразу попробовать объединить все в 1 запрос.

Он будет выглядеть примерно так:

SELECT * FROM `power_data` pd inner join  `device_data` dd on pd.`ip`=dd.`ip`
   WHERE /*`ip` LIKE '$ip' AND*/ `power_state` LIKE 'DOWN' 
   AND pd.`power_time` > '$datefrom' AND pd.`power_time` < '$dateto' 
   and dd.`visible` = '1'


Так же условие     power_state LIKE 'DOWN' возможно следует заменить на     power_state
= 'DOWN' ? Не знаю, как в MySql, но в других БД это может немного ускорить запрос


Ответ 3

По запросам:


Максимально избегайте запросы в цикле. Для перечисление, существует оператор IN
если у вас идёт точное совпадение, то в LIKE нет необходимости: power_state='DOWN'


По структуре:

ip - если ipv4, то CHAR(15). Непринципиально VARCHAR(15) или CHAR(15), но тип CHAR
используется, когда известна длина поля. В вашем случае, это 15.

power_state: tinyint(1) UNSIGNED, т.е состояние желательно хранить в цифрах: DOWN
- 0, UP - 1. Тогда запрос будет выглядить: power_state=0

power_time: лучше хранить в timestamp, хотя непринципиально. Тип int удобен при миграции
с одной СУБД на другую. timestamp же позволяет использовать функции по работе с датами
на уровне СУБД да и логично смотрится.

В первом запросе:

SELECT * FROM `device_data` WHERE `visible` = 1 


поскольку вас интересуют только ip, то оптимальным по памяти будет указание необходимого
столбца:

SELECT `ip` FROM `device_data` WHERE `visible` = 1


Аналогично и по второму запросу, если в этом существует необходимость.

Со всеми изменениями итоговый запрос будет выглядит так:

SELECT * FROM `power_data` 
    WHERE `ip` IN ('127.0.0.1', '127.0.0.2') 
    AND `power_state`=0
    AND `power_time` > '$datefrom' 
    AND `power_time` < '$dateto'


Таким образом, вы получаете два атомарных запроса. Если есть необходимость использовать
связанность, то @lDrakonl показал, как это сделать:

  SELECT power_data.* FROM `power_data` 
    INNER JOIN  `device_data` ON `power_data`.`ip`=`device_data`.`ip`
    WHERE `power_data`.`power_state`=0
    AND `power_data`.`power_time` > '$datefrom'
    AND `power_data`.`power_time` < '$dateto'
    AND `device_data`.`visible` = 1



  Если очень много связей, то оптимально будет использование именно атомарных запросов.


По поводу индексов:

Необходимо выставить для всех полей, которые участвуют в условие WHERE, ORDER BY,
GROUP BY, агрегаторов MIN и MAX, а также в связях JOIN. Существуют нюансы при которых
индекс может быть не задействован (некоторые связки WHERE и JOIN) - избыточный индексы,
который весит в памяти наравне с остальными. В вашем случае, индексы уместны для полей:
power_data.power_state, power_data.power_time и device_data.visible. При атомарном
подходе дополнительно на power_data.ip.

Существует возможность попасть в так называемый покрывающий индекс - обращение будет
происходить не к самим данным, которые лежат на диске, а только к индексу. Смотря какие
поля вы укажите в SELECT. Требуется те поля на которых имеется индекс.

И да, используйте PDO вместо процедур:


подготовленные выражение, которые позволят вам избежать SQL Injection
mysql-query и иже с ними помечены, как deprecated