Дублирование данных в другой таблице для увеличения производительности

В настоящее время я разрабатываю архитектуру базы данных для продукта, над которым работаю. Я просто рисую все в файле Excel, прежде чем приступить к созданию всего в MySQL.

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

TABLE A, содержащий самые последние значения каждой точки данных для каждого пользователя.

TABLE B, который содержит ежедневные записи каждой точки данных для каждого пользователя.

Мое довод в пользу создания TABLE A вместо того, чтобы полагаться исключительно на TABLE B, состоит в том, что количество строк в TABLE B будет расти с каждым днем ​​в зависимости от количества моих клиентов. Например, если у меня 20 000 клиентов, TABLE B будет увеличиваться на 20 000 строк каждый день. Итак, создав TABLE A, мне нужно будет искать только 20 000 записей, чтобы найти самые последние значения каждой точки данных для каждого пользователя, поскольку я буду обновлять эти значения каждый день; тогда как для TABLE B мне пришлось бы искать среди постоянно растущего числа строк самую последнюю вставку для каждого пользователя.

Это приемлемая или хорошая практика?

Или я должен просто забыть о TABLE A, чтобы уменьшить "раздувание" в моей базе данных?

Вы говорите о создании производного набора данных, оптимизированного для ваших требований к чтению. Это нормально. Ключевым моментом здесь является сделать это таким образом, чтобы вы могли убедиться, что таблица A является точным отражением того, что находится в B, и что они не выпадают из синхронизации. Один из способов сделать это - сделать таблицу A VIEW и периодически материализовать ее или обновлять с помощью триггеров.

tadman 29.10.2018 23:01

@tadman Я новичок в MySQL, поэтому у меня возникли проблемы с пониманием этой штуки VIEW, ее материализацией и триггерами. Я просто собирался обновлять TABLE A каждый раз, когда вставляю ежедневную запись в TABLE B.

oldboy 29.10.2018 23:09

Вы можете автоматизировать это обновление с помощью триггера, и часть VIEW - это то, как вы изначально заполняете таблицу. Это концепции, которые стоит прочитать, если вы не совсем знакомы, чтобы знать, какие инструменты у вас есть.

tadman 29.10.2018 23:20

@tadman, я обязательно прочитаю о них сегодня! дело в том, что все это в любом случае будет автоматизировано. от скрапинга до загрузки и обработки / обработки данных (то есть их вставки и обновления). но я все равно буду читать о других вещах

oldboy 29.10.2018 23:26

@tadman только что узнал, что, к сожалению, мой план хостинга не позволяет мне использовать разделы на общем хостинге :( Хотел бы я проверить это, прежде чем тратить столько времени на чтение об этом lol

oldboy 30.10.2018 07:38

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

tadman 30.10.2018 19:24

Если это не «критично для бизнеса», ваш собственный настольный компьютер / ноутбук в течение некоторого времени будет работать нормально. Даже при 20 000 строках в день вы можете не переполнить машину за год.

Rick James 31.10.2018 05:33

@RickJames, о, я не беспокоюсь о размере хранилища базы данных, а только о времени выполнения / производительности. эти две конкретные таблицы должны занимать менее 200 мегаbytes после год., особенно это будет «критически важная для бизнеса» среда, однако

oldboy 31.10.2018 10:29

@Anthony - Производительность обычно сводится к () Индексы (особенно «составные», см. _мой блог_, () формулировке запросов, (*) дизайну схемы. Тонкая настройка и "решение проблемы аппаратными средствами" редко бывают полезными.

Rick James 31.10.2018 18:11

@RickJames, спасибо!

oldboy 31.10.2018 21:17
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
10
167
2

Ответы 2

Это неправильный подход. У вас есть два разумных варианта:

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

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

разве мне не придется обновлять индекс каждый раз, когда я вставляю новые ежедневные записи в TABLE B? Я новичок в MySQL, поэтому я действительно не понимаю разбиение на разделы. Означает ли это, что даже если все ежедневные записи будут храниться в TABLE B, TABLE B может / будет разбит на "отдельные" таблицы на основе, скажем, значений столбца даты ??

oldboy 29.10.2018 23:12

@ Энтони. . . Индекс обновляется автоматически. Я предполагаю, что для 30 000 записей в день вы не заметите увеличения времени. Это займет больше времени, чем вставка без индекса, но ненамного.

Gordon Linoff 29.10.2018 23:26

о, я не беспокоюсь о вставке данных. меня беспокоит выбор данных. Вы уверены, что индекс будет обновляться автоматически? новые записи будут постоянно вставляться в table b на ежедневной основе, и самые последние записи - это те записи, к которым мне нужен доступ к select. мое предположение о том, как работают разделы, примерно соответствует точке или я неправильно это понимаю?

oldboy 29.10.2018 23:29

@ Энтони. . . Разделы и индексы - это разные вещи. Однако оба они эффективны, если у вас есть where date = ? в предложении where.

Gordon Linoff 30.10.2018 00:44

да, я знаю, что это разные вещи. хорошо, теперь я понимаю, что ты говоришь. в любом случае, я только что узнал, что, к сожалению, мой план хостинга не позволяет мне использовать разделы на общем хостинге :( жаль, что я не проверил это, прежде чем тратить столько времени на чтение об этом lol. индекса записей за последний день должно быть достаточно чтобы время исполнения было разумным?

oldboy 30.10.2018 07:40

@anthony. . . Я так думаю. Я бы порекомендовал кластеризованный индекс, потому что самые свежие данные очень важны.

Gordon Linoff 30.10.2018 12:40

хорошо, сладкая. Благодарю. в чем разница между кластеризованным индексом и простым старым индексом?

oldboy 30.10.2018 20:02

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

Gordon Linoff 30.10.2018 21:32

За исключением редких случаев, разделение нет ускоряет SELECTs не больше, чем хороший индекс.

Rick James 31.10.2018 05:37

@RickJames так я прочитал, но разбиение на разделы пригодится в одной из моих ситуаций. вместо разделения хэштегов на алфавитные таблицы (например, table_ [a-z]) я могу поместить все это в одну секционированную таблицу, чтобы уменьшить количество запросов и их сложность. по крайней мере, насколько мне известно. ive никогда раньше не использовал разделы и только недавно начал читать о них

oldboy 31.10.2018 10:32

@Anthony - Для простого поиска по ключу (хэштегу) это промывка. Без разбиения на разделы это простое углубление в BTree (см. Википедию), чтобы добраться до строки (строк). При разбиении на разделы сначала вы выбираете, какой раздел, а затем просматриваете BTree в этом разделе. Компромисс между выбором раздела и немного более мелким BTree - это несложный выбор. Пока вы читаете, добавьте мой блог раздела в свой список.

Rick James 31.10.2018 18:07

@RickJames, у меня действительно нет причин разделять TABLE_A. однако, поскольку ТАБЛИЦА B накапливается значительно больше, и поскольку она может быть хорошо разделена по годам, первой букве, обоим или чему-то в этом роде, я буду разбивать эту таблицу, как только я переключу хосты (потому что мой текущий хост не разрешить разбиение на общие серверы). о да, я на самом деле прочитал большую часть этого на днях, когда наткнулся на одну из ваших других публикаций в другой ветке

oldboy 31.10.2018 21:13

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

Я бы подумал, в чем разница между «историей» и «текущим», а затем сделал бы таблицы разные не одинаковые.

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

Я не вижу необходимости в PARTITIONing, если я не собираюсь удалять «старые» данные. В этом случае я бы использовал PARTITION BY RANGE(TO_DAYS(..)) и выбрал еженедельно / ежемесячно / что угодно, чтобы количество разделов не превышало примерно 50. (Если вы выберете «ежедневно», History будет замедляться через несколько месяцев только из-за разбиения на разделы. )

20 тысяч строк каждый день - многие ли из них не изменились со вчерашнего дня? Вероятно, это неправильный способ делать что-то. Расскажите, что происходит каждый день. Вам следует избегать дублирования строк в History (кроме даты).

20 тысяч строк обычно будут почти, если не полностью, уникальными. что я делаю, так это соскабливаю точки данных, относящиеся к хэштегам Instagram. соответствующие записи как в таблице A, так и в таблице B также будут отличаться друг от друга. одна из таблиц будет содержать минимальный объем данных (всего 4 столбца); это таблица, которую я буду кормить ежедневно примерно 20k ежедневных записей. тогда как другая таблица будет содержать набор производных показателей, полученных в результате агрегирования этих записей с течением времени. [продолжение]

oldboy 31.10.2018 10:24

ive решил хранить метрики на 2 дня (вместо только самой последней) в таблице «производных метрик» (15 столбцов) и очищать самую старую из двух записей для каждого уникального хэштега, когда я очищаю и вставляю каждую новую запись день. так что для этого лучше всего подойдет раздел? таблица «производных показателей» с 15 столбцами всегда будет иметь примерно 40 тыс. строк. больше не надо. Не меньше. [2 * общее количество уникальных хэштегов]

oldboy 31.10.2018 10:27

@Anthony - Похоже на Таблица результатов

Rick James 31.10.2018 18:13

это в основном идея. мой паук будет очищать данные, создавать файл Excel, загружать его на сервер, чтобы его можно было проанализировать, а затем скрипт php на сервере будет обрабатывать файл / данные и ежедневно вставлять запись для каждого хэштега. будет 4 столбца: (1) unique_key, (2) hashtag, (3) data, (4) date_inserted. когда php-скрипт анализирует файл Excel для создания ежедневных записей, я также выбираю все необходимые предыдущие ежедневные записи для выполнения моих вычислений, из которых я создаю «сводку» значений в другой таблице. никогда не слышал о "резюме"

oldboy 31.10.2018 21:32

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

oldboy 31.10.2018 21:35

Быстрый вопрос: может ли длина имен столбцов влиять на количество возможных столбцов, разрешенных в таблице? Причина, по которой я спрашиваю, заключается в том, что одна из моих "сводных" таблиц может иметь примерно 146 столбцов, 99% из которых являются просто числами, половина из которых будут процентами (т.е. «82» представляют 82% или «3» представляют 3 %) ???

oldboy 31.10.2018 21:37

@Anthony - Да, вы понимаете "сводную таблицу". Есть ли причина держать Excel в курсе? Похоже, что работа может выполняться на SQL и / или PHP. Соответствуют ли номера TINYINT, SMALLINT и т. д.? Проценты могут быть TINYINT (или FLOAT для дополнительной точности). Не похоже, что вы собираетесь выйти за пределы каких-либо ограничений. См. Также mysql.rjweb.org/doc.php/limits. Для столбца имена см. «Общие ограничения».

Rick James 01.11.2018 02:36

единственное, для чего я использую excel, - это временно хранить данные, которые я очищаю, а затем использовать этот файл для загрузки на сервер для обработки ?? сделать это без Excel, что ты имеешь в виду? Да, в таблице со 146 столбцами 72 из этих столбцов могут быть tinyint или, вероятно, float или decimal. Разве decimal не более точен, чем float, при этом decimal позволяет мне делать точные вычисления на основе данных, а float - нет? не то, чтобы делать расчеты ... остальным 72, вероятно, понадобится не больше, чем smallint

oldboy 01.11.2018 03:08

у меня снова есть еще один вопрос. главное преимущество разбиения в том, что разбиение выполняется автоматически с помощью mysql?

oldboy 01.11.2018 03:59

Float vs decimal - зависит от того, для чего используются данные. Деньги должны быть DECIMAL. Что-то вроде «среднего» вполне нормально для FLOAT.

Rick James 01.11.2018 05:04

@Anthony - Да, MySQL будет автоматически искать в нескольких разделах по мере необходимости. Но это не значит, что он быстрее.

Rick James 01.11.2018 05:04

Другие вопросы по теме