Архитектура базы данных для миллионов новых строк в день
Мне нужно реализовать специально разработанный сервис веб-аналитики для большого количества веб-сайтов. Ключевыми объектами здесь являются:
- Веб-сайт
- Посетитель
Каждый уникальный посетитель будет иметь одну строку в базе данных с такой информацией, как целевая страница, время суток, ОС, браузер, реферер, IP и т. Д.
Мне нужно будет выполнить агрегированные запросы к этой базе данных, такие как «СЧИТАЙТЕ всех посетителей, которые используют Windows в качестве ОС и пришли с Bing.com»
Я отслеживаю сотни веб-сайтов, и количество посетителей этих веб-сайтов варьируется от от нескольких сотен в день до нескольких миллионов в день. В целом, я ожидаю, что эта база данных будет расти примерно на миллион строк в день.
Мои вопросы:
1) Является ли MySQL хорошей базой данных для этой цели?
2) Что может быть хорошей архитектурой? Я думаю о создании новой таблицы для каждого сайта. Или, возможно, начать с одной таблицы, а затем создать новую таблицу (ежедневно), если количество строк в существующей таблице превышает 1 миллион (мое предположение верно). Единственное, что меня беспокоит, так это то, что если таблица становится слишком большой, SQL-запросы могут значительно замедлиться. Итак, какое максимальное количество строк я должен хранить в таблице? Более того, существует ли ограничение на количество таблиц, которые может обрабатывать MySQL.
3) Желательно ли выполнять агрегированные запросы по миллионам строк? Я готов подождать пару секунд, чтобы получить результаты для таких запросов. Является ли это хорошей практикой или есть какой-либо другой способ выполнения агрегированных запросов?
В двух словах: Я пытаюсь спроектировать крупномасштабное хранилище данных, которое будет тяжело писать . Если вы знаете о каких-либо опубликованных тематических исследованиях или отчетах, это будет здорово!
3 ответа
Если вы говорите о больших объемах данных, обратите внимание на Разделение MySQL на разделы . Для этих таблиц разделение по данным / времени, безусловно, поможет производительности. Здесь есть приличная статья о разбиении на разделы .
Посмотрите на создание двух отдельных баз данных: одну для всех необработанных данных для записи с минимальным индексированием; второй для отчетности с использованием агрегированных значений; либо с помощью пакетного процесса для обновления базы данных отчетов из базы данных необработанных данных, либо с помощью репликации, чтобы сделать это за вас.
РЕДАКТИРОВАТЬ
Если вы хотите быть действительно умными с отчетами агрегирования, создайте набор таблиц агрегирования («сегодня», «за неделю до даты», «за месяц до даты», «по годам»). Агрегировать от необработанных данных к «сегодняшнему дню» либо ежедневно, либо в «реальном времени»; агрегировать от «по дням» к «неделям до настоящего времени» по ночам; от «недели до даты» до «месяца до даты» на еженедельной основе и т. д. При выполнении запросов присоединяйте (UNION) к соответствующим таблицам для интересующих вас диапазонов дат.
РЕДАКТИРОВАТЬ № 2
Скорее чем одна таблица на клиента, мы работаем с одной схемой базы данных для каждого клиента. В зависимости от размера клиента у нас может быть несколько схем в одном экземпляре базы данных или отдельный экземпляр базы данных для каждого клиента. Мы используем отдельные схемы для сбора необработанных данных и для агрегирования / отчетности для каждого клиента. Мы запускаем несколько серверов баз данных, ограничивая каждый сервер одним экземпляром базы данных. Для обеспечения устойчивости базы данных реплицируются на несколько серверов и балансируются по нагрузке для повышения производительности.
Вам действительно следует проверить свой путь вперед, смоделировав среду как можно ближе к реальной среде, с «настоящими фальшивыми» данными (правильный формат и длина). Тестовые запросы и варианты структур таблиц. Поскольку вы, кажется, знакомы с MySQL, начните с этого. У вас не займет так много времени, чтобы настроить несколько скриптов, бомбардирующих вашу базу данных запросами. Изучение результатов вашей базы данных с данными вашего типа поможет вам понять, где возникнут узкие места.
Не решение, но надеюсь, что немного поможет, удачи :)
Некоторые предложения, не зависящие от базы данных.
Самый простой рациональный вариант - различать таблицы с интенсивным чтением и таблицами с интенсивным чтением. Вероятно, было бы неплохо создать две параллельные схемы, ежедневную / еженедельную схему и схему истории. Разделение может быть выполнено соответствующим образом. Можно подумать о пакетном задании для обновления схемы истории данными из ежедневной / еженедельной схемы. И снова в схеме истории вы можете создавать отдельные таблицы данных для каждого веб-сайта (в зависимости от объема данных).
Если вас интересует только статистика агрегирования (что может неверно ). Рекомендуется иметь сводные таблицы (ежемесячно, ежедневно), в которых хранится сводка, такая как общее количество уникальных посетителей, повторных посетителей и т. Д .; и эти сводные таблицы должны обновляться в конце дня. Это позволяет вычислять статистику «на лету», не дожидаясь обновления базы данных истории.