разработка схемы для огромных таблиц в oracle

У меня есть таблица пользователей, таблица транзакций и таблица user_transaction. количество пользователей составляет около 75 000. Количество уникальных транзакций, возможных в приложении, составляет около (количество строк в таблице транзакций составляет от 1 до 3 миллионов). user_transaction - это объединение двух приведенных выше таблиц, в которых хранятся данные о том, какие транзакции пользователи выполняли, в какой день и время. Итак, эта таблица будет огромной за 1 год данных (мы собираемся удалить активные данные из таблицы и заархивировать их через 1 год). Мы ожидаем, что количество строк будет около 50-60 миллионов. Это будет окончательный объем данных в конце года.

Я бы сказал, что средний размер составляет около 30 миллионов записей. Кроме того, еженощное задание импорта обновляет все эти таблицы, и это единственная часть, когда в эти таблицы выполняются вставки, мы получаем доступ только к данным (используем запросы выбора) из нашего приложения.

Как лучше всего спроектировать таблицу соединений, чтобы ускорить извлечение из огромной таблицы транзакций? Мы добавили много полей в таблицу, чтобы денормализовать ее и уменьшить количество объединений, и почти все данные доступны только в таблице транзакций и user_transaction.

Если мы хотим разбить таблицу на разделы, как нам это сделать? Приложение чаще всего используется для запроса самых свежих данных.

Мы думаем о том, чтобы разделить таблицу транзакций по месяцам, чтобы у нас была 1 таблица на каждый месяц.

Другой вариант, о котором мы думали, - иметь по 7 таблиц на 1 день недели, но это значительно увеличивает сложность запросов, учитывая, что мы используем спящий режим.

Как мы спроектируем огромный стол на 60 миллионов?

Дополнительная информация по запросу:
Мне нужно будет построить диаграмму из схемы, а пока еще немного информации: отношения несложные, это примерно 4 таблицы: пользователи, транзакции. , users_transaction, таблица ресурсов. user_transaction - это объединяемая таблица, содержащая все остальные три идентификатора таблиц, и эта таблица будет огромной, поскольку она будет иметь отдельные записи для каждого из этих идентификаторов, а также отдельные записи на основе метки времени.
Количество пользователей приложения сейчас очень мало ‹20. (но может вырасти в будущем).
Основными потребителями таблиц являются:
1) еженедельные отчеты самооценки, рассылаемые в виде электронных писем, содержащие сведения об активности пользователей за последнюю неделю из этих таблиц. они будут отправлены (в конечном итоге) 75 000 лайкам пользователей, а создание отчета и отправка электронного письма для 1 пользователя в настоящее время занимает около 1 минуты (тестирование на пилотной фазе). нам нужно серьезно улучшить производительность, чтобы лайк составлял менее 5 секунд на одно электронное письмо. Это внутреннее задание, которое выполняется ночью (должно занимать не более 3-4 часов)
2) Панели мониторинга, содержащие диаграммы, которые показывают сводное представление транзакции из этих таблиц. Эти запросы выполняются и суммируют данные на основе различных полей в диапазоне дат. Следовательно, мы планируем суммировать таблицу user_transactions, в которой хранятся счетчики за каждый день (не включая время), если все остальные поля одинаковы (идентификатор пользователя, идентификатор ресурса, resource_eventid, местоположение).
И разделите эти сводные таблицы по месяцам. (по одному на каждый месяц)
На заметку: решение должно подходить для всех баз данных (MySQL, DB2 и т. д.), а не только для Oracle.

С уважением, Приянк Девуркар


database-design oracle11g schema-design
person pri_dev    schedule 12.01.2012    source источник
comment
Во-первых, вопрос о лицензировании. Вы лицензировали (или будете лицензировать) вариант разделения? Это дополнительная плата за лицензию на корпоративную версию.   -  person Justin Cave    schedule 13.01.2012
comment
Да, мы будем готовы это сделать, если потребуется, учитывая, что клиенту действительно нужен этот инструмент и он готов потратить как можно больше на лучшее устройство.   -  person pri_dev    schedule 13.01.2012
comment
Я хотел бы увидеть схему таблиц, а также некоторые бизнес-правила. Сколько пользователей обычно участвует в транзакции. Есть ли предел?   -  person Adam Musch    schedule 16.01.2012
comment
Я подозреваю, что ваше лучшее решение будет специфичным для каждой базы данных. В Oracle вы бы использовали секционирование.   -  person Jeffrey Kemp    schedule 02.03.2012
comment
Может быть глупый вопрос. Но действительно ли у вас есть отношения M-N между пользователями и транзакциями? то есть действительно ли для отдельной транзакции более 1 пользователя? coz, если нет, вам не нужна таблица сопоставления M-N. Вы можете сохранить идентификатор пользователя в таблице транзакций. Даже если в транзакции может быть, скажем, 2 пользователя, я все равно рекомендую иметь просто 2 столбца в таблице транзакций вместо таблицы M-N user_transaction. Также вам определенно понадобится разбиение, учитывая объем ваших данных и их временную природу.   -  person    schedule 17.04.2012
comment
Я думаю, что в настоящее время между пользователем и таблицей транзакций существует связь m-n. транзакция - это уникальный ресурс / URL-адрес, к которому осуществляется доступ в приложении, и может выполняться каждым пользователем приложения. Предположим, у нас есть 40 пользователей - 60 пользователей, которые все равно сделают таблицу транзакций огромной, если мы поместим туда столбец user, следовательно, у нас есть таблица соединения user_transactions. Что касается секционирования, мы пока секционировали данные на ежемесячной основе, поэтому это не касается всех секционированных таблиц user_transaction для последних запросов данных, которые являются наиболее часто запрашиваемыми данными.   -  person pri_dev    schedule 13.09.2012
comment
См. Комментарий выше - транзакция обычно выполняется одним пользователем (если не в этом случае, пожалуйста, объясните, почему!) Избавление от связи "многие ко многим" значительно упростит задачу. Да, таблица транзакций будет огромной, но структура будет проще запрашивать / вставлять / обновлять, о чем вам следует позаботиться.   -  person kayakpim    schedule 23.04.2014

Ответы (1)


arrow_upward
0
arrow_downward

Хорошо, обо всем по порядку.

  1. Таблица с 30 миллионами строк - НЕ ОГРОМНАЯ по стандартам Oracle.
  2. Утверждение, что у вас 75 000 пользователей, означает, что база данных не управляет вашими учетными записями пользователей и, возможно, есть несколько ролей, которые имеют дело с базой данных.

Аудит...

Oracle имеет очень мощные функции аудита, поэтому следует изучить их, прежде чем пытаться применить свои собственные.

Если вы действительно хотите развернуть свой собственный, то между пользователями и транзакциями существует связь «один-много». Теперь я использую термин транзакции здесь очень свободно, поскольку вам кажется, что вы хотите делать запись, когда пользователь X что-то делает для изменения таблицы или строки в таблице.

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

вставить в таблицу аудита (userID, Operation) значения ('fred', 'обновлена ​​таблица платежей и изменено старое значение некоторого столбца на новое значение');

Я бы сделал составной индекс идентификатора пользователя и отметки времени, чтобы можно было запрашивать if для этих двух столбцов как одного объекта. Таблица будет выглядеть примерно так:

create table user_audit as 
(
user_id number,
action_timestamp systimestamp,
db_action clob
)

CREATE INDEX idx_user_audit_ia ON  user_audit (user_id,action_timstamp);

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

Эта единственная таблица будет очень-очень быстрой для удаления и вставки. Вы можете сделать это еще быстрее:

  • Отсутствие ограничения первичного ключа.
  • Отключение ведения журнала для этой таблицы или табличного пространства, в котором она находится.

  • Отсутствие FK обратно в таблицу пользователей (это действительно бессмысленно).

  • ЕСЛИ у вас достаточно оперативной памяти на компьютере с базой данных, установите ее на буферы кеша, но ТОЛЬКО, если у вас достаточно оперативной памяти, иначе вы поместите сервер в резервуар.

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

  • Убедитесь, что ваше табличное пространство - БОЛЬШАЯ ТАБЛИЦА, когда вы определяете его, так как это гарантирует, что вы не превысите ограничение на размер (по крайней мере, в Linux) для одного файла. .

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

Всегда помните девиз UNIX: делайте одно и делайте это хорошо.

person FlyingGuy    schedule 08.02.2015