В настоящее время я разрабатываю архитектуру базы данных для продукта, над которым работаю. Я просто рисую все в файле 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
, чтобы уменьшить "раздувание" в моей базе данных?
@tadman Я новичок в MySQL, поэтому у меня возникли проблемы с пониманием этой штуки VIEW
, ее материализацией и триггерами. Я просто собирался обновлять TABLE A
каждый раз, когда вставляю ежедневную запись в TABLE B
.
Вы можете автоматизировать это обновление с помощью триггера, и часть VIEW
- это то, как вы изначально заполняете таблицу. Это концепции, которые стоит прочитать, если вы не совсем знакомы, чтобы знать, какие инструменты у вас есть.
@tadman, я обязательно прочитаю о них сегодня! дело в том, что все это в любом случае будет автоматизировано. от скрапинга до загрузки и обработки / обработки данных (то есть их вставки и обновления). но я все равно буду читать о других вещах
@tadman только что узнал, что, к сожалению, мой план хостинга не позволяет мне использовать разделы на общем хостинге :( Хотел бы я проверить это, прежде чем тратить столько времени на чтение об этом lol
В наши дни виртуальный хостинг - это анахронизм, потому что даже с очень скромным бюджетом вы можете использовать службы управляемых баз данных, такие как Amazon RDS, чтобы предоставить вам полнофункциональную установку базы данных, которую вы контролируете. Есть много других решений с «размещением в облаке» по разным ценам, которые могут служить вам лучше, чем стандартные, как правило, довольно некачественный хостинг с общей базой данных, который часто есть у этих провайдеров.
Если это не «критично для бизнеса», ваш собственный настольный компьютер / ноутбук в течение некоторого времени будет работать нормально. Даже при 20 000 строках в день вы можете не переполнить машину за год.
@RickJames, о, я не беспокоюсь о размере хранилища базы данных, а только о времени выполнения / производительности. эти две конкретные таблицы должны занимать менее 200 мегаbytes после год., особенно это будет «критически важная для бизнеса» среда, однако
@Anthony - Производительность обычно сводится к () Индексы (особенно «составные», см. _мой блог_, () формулировке запросов, (*) дизайну схемы. Тонкая настройка и "решение проблемы аппаратными средствами" редко бывают полезными.
@RickJames, спасибо!
Это неправильный подход. У вас есть два разумных варианта:
Вы, жестяная банка, управляете двумя таблицами, но это большая проблема, и есть встроенные методы для обработки этой ситуации.
разве мне не придется обновлять индекс каждый раз, когда я вставляю новые ежедневные записи в TABLE B
? Я новичок в MySQL, поэтому я действительно не понимаю разбиение на разделы. Означает ли это, что даже если все ежедневные записи будут храниться в TABLE B
, TABLE B
может / будет разбит на "отдельные" таблицы на основе, скажем, значений столбца даты ??
@ Энтони. . . Индекс обновляется автоматически. Я предполагаю, что для 30 000 записей в день вы не заметите увеличения времени. Это займет больше времени, чем вставка без индекса, но ненамного.
о, я не беспокоюсь о вставке данных. меня беспокоит выбор данных. Вы уверены, что индекс будет обновляться автоматически? новые записи будут постоянно вставляться в table b
на ежедневной основе, и самые последние записи - это те записи, к которым мне нужен доступ к select
. мое предположение о том, как работают разделы, примерно соответствует точке или я неправильно это понимаю?
@ Энтони. . . Разделы и индексы - это разные вещи. Однако оба они эффективны, если у вас есть where date = ?
в предложении where
.
да, я знаю, что это разные вещи. хорошо, теперь я понимаю, что ты говоришь. в любом случае, я только что узнал, что, к сожалению, мой план хостинга не позволяет мне использовать разделы на общем хостинге :( жаль, что я не проверил это, прежде чем тратить столько времени на чтение об этом lol. индекса записей за последний день должно быть достаточно чтобы время исполнения было разумным?
@anthony. . . Я так думаю. Я бы порекомендовал кластеризованный индекс, потому что самые свежие данные очень важны.
хорошо, сладкая. Благодарю. в чем разница между кластеризованным индексом и простым старым индексом?
@ Энтони. . . Кластеризованный индекс гарантирует, что строки с одинаковыми значениями ключей являются смежными. Это уменьшает количество страниц данных, которые необходимо прочитать при доступе к данным за один день.
За исключением редких случаев, разделение нет ускоряет SELECTs
не больше, чем хороший индекс.
@RickJames так я прочитал, но разбиение на разделы пригодится в одной из моих ситуаций. вместо разделения хэштегов на алфавитные таблицы (например, table_ [a-z]) я могу поместить все это в одну секционированную таблицу, чтобы уменьшить количество запросов и их сложность. по крайней мере, насколько мне известно. ive никогда раньше не использовал разделы и только недавно начал читать о них
@Anthony - Для простого поиска по ключу (хэштегу) это промывка. Без разбиения на разделы это простое углубление в BTree (см. Википедию), чтобы добраться до строки (строк). При разбиении на разделы сначала вы выбираете, какой раздел, а затем просматриваете BTree в этом разделе. Компромисс между выбором раздела и немного более мелким BTree - это несложный выбор. Пока вы читаете, добавьте мой блог раздела в свой список.
@RickJames, у меня действительно нет причин разделять TABLE_A
. однако, поскольку ТАБЛИЦА B накапливается значительно больше, и поскольку она может быть хорошо разделена по годам, первой букве, обоим или чему-то в этом роде, я буду разбивать эту таблицу, как только я переключу хосты (потому что мой текущий хост не разрешить разбиение на общие серверы). о да, я на самом деле прочитал большую часть этого на днях, когда наткнулся на одну из ваших других публикаций в другой ветке
В ситуациях, когда мне нужны как «текущие» данные, так и «история», я делаю именно это - одна таблица с текущими данными и одна с историей. Возможно, они по-разному индексируются для разных целей и т. д.
Я бы подумал, в чем разница между «историей» и «текущим», а затем сделал бы таблицы разные не одинаковые.
Когда появится новая запись (или 20К строк в вашем случае), я, по крайней мере, помещу ее в Current
. Я также могу записать его на History
, тем самым сохраняя его целостность (за счет небольшой избыточности). Или я могу переместить строку (строки) в History
, когда следующая строка (строки) войдет в Current
.
Я не вижу необходимости в PARTITIONing
, если я не собираюсь удалять «старые» данные. В этом случае я бы использовал PARTITION BY RANGE(TO_DAYS(..))
и выбрал еженедельно / ежемесячно / что угодно, чтобы количество разделов не превышало примерно 50. (Если вы выберете «ежедневно», History
будет замедляться через несколько месяцев только из-за разбиения на разделы. )
20 тысяч строк каждый день - многие ли из них не изменились со вчерашнего дня? Вероятно, это неправильный способ делать что-то. Расскажите, что происходит каждый день. Вам следует избегать дублирования строк в History
(кроме даты).
20 тысяч строк обычно будут почти, если не полностью, уникальными. что я делаю, так это соскабливаю точки данных, относящиеся к хэштегам Instagram. соответствующие записи как в таблице A, так и в таблице B также будут отличаться друг от друга. одна из таблиц будет содержать минимальный объем данных (всего 4 столбца); это таблица, которую я буду кормить ежедневно примерно 20k ежедневных записей. тогда как другая таблица будет содержать набор производных показателей, полученных в результате агрегирования этих записей с течением времени. [продолжение]
ive решил хранить метрики на 2 дня (вместо только самой последней) в таблице «производных метрик» (15 столбцов) и очищать самую старую из двух записей для каждого уникального хэштега, когда я очищаю и вставляю каждую новую запись день. так что для этого лучше всего подойдет раздел? таблица «производных показателей» с 15 столбцами всегда будет иметь примерно 40 тыс. строк. больше не надо. Не меньше. [2 * общее количество уникальных хэштегов]
это в основном идея. мой паук будет очищать данные, создавать файл Excel, загружать его на сервер, чтобы его можно было проанализировать, а затем скрипт php на сервере будет обрабатывать файл / данные и ежедневно вставлять запись для каждого хэштега. будет 4 столбца: (1) unique_key, (2) hashtag, (3) data, (4) date_inserted. когда php-скрипт анализирует файл Excel для создания ежедневных записей, я также выбираю все необходимые предыдущие ежедневные записи для выполнения моих вычислений, из которых я создаю «сводку» значений в другой таблице. никогда не слышал о "резюме"
table до сих пор, но это в основном идея. Я считаю, что гораздо эффективнее хранить / кэшировать значения моих показателей в другой, гораздо меньшей таблице на ежедневной основе, чем постоянно извлекать все ежедневные записи для определенного хэштега, а затем выполнять вычисления для этих записей каждые время, когда пользователь отправляет запрос или что-то еще
Быстрый вопрос: может ли длина имен столбцов влиять на количество возможных столбцов, разрешенных в таблице? Причина, по которой я спрашиваю, заключается в том, что одна из моих "сводных" таблиц может иметь примерно 146 столбцов, 99% из которых являются просто числами, половина из которых будут процентами (т.е. «82» представляют 82% или «3» представляют 3 %) ???
@Anthony - Да, вы понимаете "сводную таблицу". Есть ли причина держать Excel в курсе? Похоже, что работа может выполняться на SQL и / или PHP. Соответствуют ли номера TINYINT
, SMALLINT
и т. д.? Проценты могут быть TINYINT
(или FLOAT
для дополнительной точности). Не похоже, что вы собираетесь выйти за пределы каких-либо ограничений. См. Также mysql.rjweb.org/doc.php/limits. Для столбца имена см. «Общие ограничения».
единственное, для чего я использую excel, - это временно хранить данные, которые я очищаю, а затем использовать этот файл для загрузки на сервер для обработки ?? сделать это без Excel, что ты имеешь в виду? Да, в таблице со 146 столбцами 72 из этих столбцов могут быть tinyint
или, вероятно, float
или decimal
. Разве decimal
не более точен, чем float
, при этом decimal
позволяет мне делать точные вычисления на основе данных, а float
- нет? не то, чтобы делать расчеты ... остальным 72, вероятно, понадобится не больше, чем smallint
у меня снова есть еще один вопрос. главное преимущество разбиения в том, что разбиение выполняется автоматически с помощью mysql?
Float vs decimal - зависит от того, для чего используются данные. Деньги должны быть DECIMAL
. Что-то вроде «среднего» вполне нормально для FLOAT
.
@Anthony - Да, MySQL будет автоматически искать в нескольких разделах по мере необходимости. Но это не значит, что он быстрее.
Вы говорите о создании производного набора данных, оптимизированного для ваших требований к чтению. Это нормально. Ключевым моментом здесь является сделать это таким образом, чтобы вы могли убедиться, что таблица A является точным отражением того, что находится в B, и что они не выпадают из синхронизации. Один из способов сделать это - сделать таблицу A
VIEW
и периодически материализовать ее или обновлять с помощью триггеров.