Консолидация таблиц с отношениями "один к одному"

#mysql #database #database-design #relational-database

#mysql #База данных #база данных-дизайн #реляционная база данных

Вопрос:

У меня есть 3 таблицы MySQL для системы членства.

users : Минимальное требование быть пользователем, связанное только с информацией об учетной записи (email, пароль, is_activated и т.д.)
user_profiles : Личная информация, предоставленная пользователем (имя, адрес, телефон …)
user_member_profiles : Информация, строго контролируемая администраторами (оплаченный регистрационный взнос, посещенные собрания и т.д.)

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

Вариант 1: Оставьте это таким образом и продолжайте выполнять JOIN UPDATE утомительные действия (эта часть данных отправляется в эту таблицу, эта часть — в другую и т.д.), и т.д.). Больше работы для меня, но, может быть, это имеет больше смысла?

Вариант 2: Объединить все в одну таблицу.

Я бы предположил, что использование одной таблицы будет быстрее, не нужно объединять таблицы. Может быть, это зависит от данных? Каждая таблица содержит около 12-20 полей, поэтому объединенная таблица была бы большой.

У каждого пользователя есть не более 1 профиля из каждой таблицы, но может даже не быть профиля вообще (или может быть всего 1).

Чтобы добавить немного контекста к этому: Это для постоянно развивающейся CMS, написанной на PHP, в которую мне нужно будет вносить изменения в таблицы для каждой установки. Администраторам необходимо управлять участниками способом, подобным speadsheet, поэтому я буду выбирать до 200 пользователей одновременно.

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

1. Вы всегда должны выбирать только то, что вам нужно, независимо от структуры таблицы! Я не понимаю, как эти несколько объединений могут быть бременем.

2. Часть варианта 2, «просто убедитесь, что ВЫБИРАЕТЕ только то, что мне нужно», похоже, противоречит «Мне также нужно каждый раз ВЫБИРАТЬ * Из каждой таблицы профиля, потому что мне нужно, чтобы были доступны все поля».

3. @Catcall: Я имел в виду, что когда мне нужны данные из одного профиля, мне нужны все они, но мне не всегда нужны данные из какого-либо профиля.

4. @HLGEM: Да, я понимаю, но я вижу, что инструкции JOIN занимают больше времени, чем простой запрос из одной таблицы, и это усложняет управление обновлениями (из html-форм в приложении), поскольку я должен тщательно выбирать каждое поле и следить за тем, чтобы оно попадало в правильную таблицу профилей.

5. Если ваши отчеты о соединении выполняются медленно, вам нужно убедиться, что установлены правильные индексы. Базы данных оптимизированы для выполнения соединений, но вы должны выполнить работу, чтобы все настроить правильно.

Ответ №1:

Другим фактором, который следует учитывать при работе с широкой таблицей (много столбцов), является влияние на кэш СУБД. Любой хороший разработчик знает, что вы не делаете ‘select * from table’, поскольку это приведет к передаче ненужных данных по сети из СУБД на клиент. Но аналогичный эффект может произойти между диском и оперативной памятью, а также повлиять на объем места в оперативной памяти, который требуется таблице для кэширования.

Большинство СУБД выделяют определенный объем памяти для кэширования данных, тем самым уменьшая количество операций чтения с физического диска и ускоряя ответ пользователю. Это буферный кэш в Oracle или SQL Server

Если у вас большая таблица и вы отправляете запрос в форме «выберите col1, col2, col3 из таблицы», СУБД загрузит полные строки в оперативную память (не с col1 по 3). При этом будут устаревать старые кэшированные данные. Если ваша таблица широкая и вы загружаете 50 столбцов, вам, конечно, потребуется больше оперативной памяти, чем для такого же количества строк * узкой таблицы. Это может оказать заметное влияние на производительность СУБД.

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

Этот фактор следует добавить к другим преимуществам нормализованных данных и учитывать во время разработки таблицы. По сути, если у вас есть потенциально широкая таблица с некоторыми данными, к которым будет регулярный доступ, а к некоторым — редкий, рассмотрите несколько таблиц с отношением «1 к 1».

1. Вы затронули некоторые интересные технические моменты, о которых я не знал: ВЫБОР * Из нескольких таблиц выполняется быстрее, чем ВЫБОР col1, col2, col3 из одной таблицы. Какое максимальное количество столбцов, по вашему мнению, было бы разумным, прежде чем искать способы разделения данных на разные таблицы? (Предполагая, что это возможно и данные не очень тесно связаны)

2. После повторного прочтения вашего ответа, это приобретает больше смысла. Если бы здесь была запятая: rarely**,** consider , это было бы намного понятнее. Я прочитал это как «редко рассматриваю». Большое спасибо за этот совет, мы будем придерживаться оригинального дизайна, как я и ожидал. Я подумал, что, возможно, я слишком «защищаюсь», разделяя данные таким образом, и кто-нибудь придет и скажет: «В этом нет необходимости».

Ответ №2:

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

1. Да. Первая форма, по-видимому, лучше подходит с точки зрения нормализации. Второй способ будет быстрее, если вам нужны столбцы из 3 таблиц. Если ваша главная цель — упростить запросы, создайте представление.

2. Не подумал о таком виде. Это, безусловно, помогло бы.

3. Можно с уверенностью сказать, что у пользователей никогда не будет двух профилей, но некоторым пользователям понадобится только один. Другая проблема заключается в том, что в таблице будет как минимум 50 столбцов, что, учитывая мои ограниченные знания, пахнет плохим дизайном; но опять же: вы должны делать то, что вы должны делать. Концепция «представления» находится за пределами моего полного понимания прямо сейчас, будет ли это хорошим решением или просто другим вариантом?

4. Я работал с таблицами с более чем 40 столбцами по простой необходимости. Представление обычно предназначено для того, чтобы предоставить определенным пользователям определенный доступ к определенным данным, но это не единственный способ их использования. Для вас, вероятно, это просто еще один вариант.

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

Ответ №3:

Вам не нужно использовать так много объединений для извлечения данных.

У вас может быть VIEW для отображения, например, всех столбцов из users и user_profiles :

 CREATE VIEW users2 AS
( SELECT u.id
       , u.email
       , u.password
       , u.is_activated
       , p.name
       , p.address
       , p.phone
  FROM users u
    LEFT JOIN user_profiles p
      ON u.id = p.id
)

и используйте это ПРЕДСТАВЛЕНИЕ в запросах, которым требуются данные из обеих таблиц. Другой ВИД для всех 3 таблиц и т.д.

1. Спасибо за это, кажется, все предлагают использовать представление, чему мне нужно будет научиться. В чем разница между этим и простым написанием кратких инструкций SELECT с одной большой таблицей? «Правильно ли я поступаю», сохраняя данные в отдельных таблицах, или это не имеет значения?

Ответ №4:

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

1. saving all of a minute or so in development : Это действительно важно для меня с точки зрения ясности и упрощения кода, и на самом деле в конечном итоге займет больше минуты или двух по мере развития событий, и мне нужно, например, добавить больше полей или управлять ими по-другому. probably no time at all inperformance to the users : Бывают случаи, когда мне нужно, например, показать сотни пользователей (для управления), поэтому я действительно заинтересован в повышении производительности. Вы хотите сказать, что представления не стоят времени / усилий или что это не является веской причиной для их использования?

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

3. Хорошо, теперь я понимаю (и согласен), спасибо. Это, конечно, очень просто. В принципе, существует 4 сценария: доступ к table1, table1 table2, table1 table3 или table1 2 3. Мне всегда нужны все данные из таблицы, к которой осуществляется доступ. Мне интересно, должен ли я оставить как есть и ВЫБРАТЬ * с помощью JOIN или поместить все в одну таблицу и просто выбрать нужные мне столбцы для каждого из этих 4 экземпляров. Звучит так, будто ваш совет состоит в том, чтобы оставить все так, как у меня есть, это верно?

4. Да, за исключением того, что я бы никогда не использовал select *, вам нужно указать столбцы. Select * — это очень плохая техника программирования, и ее не следует использовать в производственном коде. Это неаккуратная, вредная привычка. Выбор * может вызывать серьезные ошибки при изменении структуры (например, кто-то добавляет столбец, который вы не хотите показывать пользователям, или это приводит к путанице в инструкции insert для другой таблицы), и это вызывает проблемы с производительностью. Когда у вас есть объединение, у вас есть по крайней мере один столбец с одинаковыми данными, которые вы возвращаете дважды, это пустая трата ресурсов сети и сервера.

Ответ №5:

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

если у каждого пользователя несколько профилей, храните данные пользователя и профиля отдельно; используйте столбец в таблице профилей (множественная сторона отношения) для ссылки на первичный ключ таблицы пользователей.
если у каждого пользователя необязательно есть профиль (т. Е. имеет один или ни одного), используйте две таблицы одинаково, но для упрощения объединения используйте один и тот же первичный ключ в обеих таблицах. Цель состоит в том, чтобы избежать таблиц с большим количеством пустых строк. Другой способ думать об этом заключается в том, что profile наследуется от person — и поэтому использует таблицу добавленных данных с тем же ключом.

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

1. Нет нескольких профилей из одной таблицы. Действительно ли ваш второй пункт является » причиной разделения таблиц»? Если я могу использовать представления, как все предлагают, то какой смысл в одной большой таблице, когда мои данные уже распределены по нескольким таблицам?

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

Ответ №6:

Если у вас нет странных проблем с производительностью, у вас должна быть только одна таблица.

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

Такого рода проблемы с производительностью, о которых все мечтают, но которых мало у кого бывает…

1. У меня не столько «проблемы с производительностью», сколько я пытаюсь ускорить процесс и упростить управление в приложении. Unless you're having performance issues, you should just have one table. — То есть вы хотите сказать, что если у меня нет проблем с производительностью, я должен использовать одну таблицу, но использовать несколько таблиц, если у меня есть проблемы??? there are better ways to deal with it — Не уверен, что вы имеете в виду здесь, можете ли вы просветить меня?

2. Я имел в виду действительно странные проблемы с производительностью, а не обычные, я чрезмерно защищал свой ответ. Прошу прощения. Я внесу соответствующие коррективы.