Здравствуйте!
Опыта и знаний не хватает, поэтому обращаюсь за помощью. Заранее спасибо за любую информацию!
В моём проекте имеется 11 специализированных IP-видеокамер, которые считают посетителей торгового центра.
Каждая из них имеет веб-морду, через которую можно скачать отчёт в виде XML-файла только за день целиком.
Данные такие: вход/выход, дата и время, высота объекта.
Мои PHP-скрипты раз в минуту логинятся на устройствах, выкачивают файлы со статистикой и складывают в MySQL (db_visitors_counter).
Для каждого устройства создано по одной таблице типа InnoDB (deviceXX, где XX номер устройства).
Структура таблиц:
#ИмяТипСравнениеNullПо умолчанию1id (первичный)int(11)НетНет2iochar(6)utf8_general_ciДаNULL3datetimedatetimeДаNULL4heightchar(5) utf8_general_ciДаNULL
Далее, с веб-страницы пользователь задаёт, например, день (а может и неделю), за который он хочет увидеть статистику.
Статистика состоит из многих видов графиков.
Через AJAX страница общается с PHP-скриптом, который ведёт работу с БД.
Например, нужен почасовой график входов через центральный вход за сутки. Центральный вход - это 7 устройств (или 7 таблиц deviceXX).
Делается выборка данных за первый час суток из таблицы device01, затем из device02 и т.д. до device07, например:
1.
2.
3.
4.
5.
6.
7.
SELECT * FROM `device01` WHERE `io` = "In" AND `datetime` BETWEEN "2016-09-30 00:00:00" AND "2016-09-30 00:59:59";
SELECT * FROM `device02` WHERE `io` = "In" AND `datetime` BETWEEN "2016-09-30 00:00:00" AND "2016-09-30 00:59:59";
SELECT * FROM `device03` WHERE `io` = "In" AND `datetime` BETWEEN "2016-09-30 00:00:00" AND "2016-09-30 00:59:59";
SELECT * FROM `device04` WHERE `io` = "In" AND `datetime` BETWEEN "2016-09-30 00:00:00" AND "2016-09-30 00:59:59";
SELECT * FROM `device05` WHERE `io` = "In" AND `datetime` BETWEEN "2016-09-30 00:00:00" AND "2016-09-30 00:59:59";
SELECT * FROM `device06` WHERE `io` = "In" AND `datetime` BETWEEN "2016-09-30 00:00:00" AND "2016-09-30 00:59:59";
SELECT * FROM `device07` WHERE `io` = "In" AND `datetime` BETWEEN "2016-09-30 00:00:00" AND "2016-09-30 00:59:59";
Вопросы:
a. Вообще верный ли подход запросов к таблицам здесь используется? Что можно придумать для оптимизации? Или проблема вообще не в БД, а в приложении в целом?
b. Правильно ли составлены таблицы?
c. Правильно ли выбран тип таблиц в данном случае?
d. Имеет ли смысл добавлять Index? И для какого столбца? datetime?
e. Можно ли как-то оптимизировать сами SQL-запросы?
Спасибо за внимание!
P.S.:
Немного результатов моих исследований по добавлению индекса для столбца datetime к самой тяжеловесной таблице:
Таблица: device07reserve
Количество строк: 3850385
Размер: 165.7МБ
1. Без индекса
Запрос:
1.
SELECT * FROM `device07reserve` WHERE `io` = "In" AND `datetime` BETWEEN "2018-09-12 19:00:00" AND "2018-09-12 19:59:59";
Выбрано 366 строк.
Выполнено за 1,0000 секунд.
2. Запрос:
1.
EXPLAIN SELECT * FROM `device07reserve` WHERE `io` = "In" AND `datetime` BETWEEN "2018-09-12 19:00:00" AND "2018-09-12 19:59:59";
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdevice07reserveNULLALLNULLNULLNULLNULL38503851.11Using where
3. Запрос:
1.
CREATE INDEX `datetime` ON `device07reserve`(`datetime`);
4. С индексом
Запрос:
1.
SELECT * FROM `device07reserve` WHERE `io` = "In" AND `datetime` BETWEEN "2018-09-12 19:00:00" AND "2018-09-12 19:59:59";
Выбрано 366 строк.
Выполнено за 0,0000 секунд.
5. Запрос:
1.
EXPLAIN SELECT * FROM `device07reserve` WHERE `io` = "In" AND `datetime` BETWEEN "2018-09-12 19:00:00" AND "2018-09-12 19:59:59";
idselect_typetablepartitionstypekeykey_lenrefrowsfilteredExtra1SIMPLEdevice07reserveNULLrangedatetimedatetime6NULL82410.00Using index condition; Using where
