Лучший способ для хранения миллионов записей в день данных, которые могут быть сгруппированы для статистических целей?

Я разрабатываю пользовательский инструмент отслеживания для маркетинговых кампаний. Этот инструмент находится посередине между объявлениями и целевыми страницами. Он заботится о сохранении всех данных от пользователя, таких как информация в user-agent, IP, клики на целевой странице и данные геокодирования IP-адресов пользователей (страны, ISP и т.д.).

На данный момент у меня есть некоторые вопросы дизайна:

  • Трафик в этих кампаниях очень высок, поэтому потенциально у меня есть миллионы строк вставки в день. Эта система может иметь более одного пользователя, поэтому я не могу хранить все эти данные на одной таблице, потому что это станет беспорядком. Возможно, я могу разделить данные в нескольких таблицах, по одной таблице на пользователя, но я не уверен в этом решении.
  • Процесс сохранения данных должен быть выполнен как можно быстрее (несколько миллисекунд), поэтому я думаю, что NodeJS намного лучше, чем PHP для этого. Особенно в отношении скорости и ресурсов сервера. Я не хочу, чтобы сервер рухнул из-за нехватки оперативной памяти.
  • Мне нужно сгруппировать эти данные для статистических целей. Например, у меня есть одна строка для каждого пользователя, который посещает мою целевую страницу, но мне нужно сгруппировать эти данные для отображения количества показов на этой конкретной целевой странице. Таким образом, все эти запросы должны быть выполнены как можно быстрее с таким большим количеством строк.
  • Мне нужно геокодировать IP-адреса, поэтому мне нужна точная информация, как страна, провайдер, тип соединения и т.д., Но это может замедлить процесс сохранения данных, если я вызову службу API. И это должно быть сделано в режиме реального времени и не может быть сделано позже.

После сохранения система должна выполнить перенаправление на целевую страницу. Время важно для того, чтобы не потерять любое возможное лидерство.

В основном, я нахожу лучшие решения для:

  • Эффективное управление очень большой базой данных
  • Сохранение данных от пользователей в кратчайшие сроки (МС)
  • Если возможно, сделайте геокодирование ip в кратчайшие сроки, без блокирования выполнения
  • Оптимизация схемы и запросов для генерации статистики

У вас есть какие-то предложения? Спасибо заранее.

1 ответ

  1. Одна таблица на пользователя хуже беспорядка; не делайте этого.

    Миллионы строк в день-десятки, может быть сотни, в секунду? Это, вероятно, требует некоторой формы «промежуточной» — сбора нескольких строк, а затем пакетной вставки их. Перед дальнейшим обсуждением, пожалуйста, остановитесь на потоке данных: один или несколько клиентов. UI против пакетных процессов. Осторожно CREATE TABLE. Так далее.

    Statistical — план по созданию и постепенному ведению «сводных таблиц».

    Вы пытаетесь сопоставить IP-адреса пользователей со страной? Это отдельный вопрос, и на него был дан ответ.

    «Must «»real-time «»миллисекунды». Взгляните правде в глаза, вам придется пойти на некоторые компромиссы.

    Подробнее: перейти к http://mysql.rjweb.org/ ; оттуда смотрите три блога о методах хранилища данных.

    Как хранить днем

    InnoDB хранит данные по PRIMARY KEYпорядку. Таким образом, чтобы получить все строки за один день, прилегающие друг к другу, необходимо запустить PK с datetime. Для больших баз данных, может значительно улучшить некоторые запросы, позволяя запрос для сканирования данных последовательно, тем самым минимизируя дисковый ввод — вывод.

    Если у вас уже есть id AUTO_INCREMENT(и если вы продолжаете в этом нуждаться), то сделайте это:

    PRIMARY KEY(datetime, id),  -- to get clustering, and be UNIQUE
    INDEX(id)  -- to keep AUTO_INCREMENT happy
    

    Если у вас есть данные за год, и данные не помещаются в оперативную память, то этот метод очень эффективен для небольших временных диапазонов. Но если ваш временной диапазон больше, чем кэш, вы будете во власти скорости ввода-вывода.

    Ведение сводных таблиц с изменением данных

    Это возможно; мне нужно лучше понять данные и изменения.

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

    Сжатие данных

    • Не используйте BIGINT(8 байт), если INT(4 байта) будет достаточно; не используйтеINT, если MEDIUMINT(3 байта) будет делать. Так далее.
    • Используйте UNSIGNEDтам, где это необходимо.
    • Нормализация повторяющихся строк.

    Меньшие данные сделают его более кэшируемым, следовательно, работать быстрее, когда вы должны ударить по диску.