Есть ли причина беспокоиться о порядке столбцов в таблице?

Ответ 1

Порядок столбцов оказал большое влияние на некоторые из баз данных, которые я настроил, охватывая Sql Server, Oracle и MySQL. Это сообщение хорошие правила:

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

Примером различия в производительности является поиск индекса. Механизм базы данных находит строку на основе некоторых условий в индексе и возвращает адрес строки. Теперь скажите, что вы ищете SomeValue, и он в этой таблице:

 SomeId int,
 SomeString varchar(100),
 SomeValue int

Двигатель должен угадать, где запускается SomeValue, потому что SomeString имеет неизвестную длину. Однако, если вы измените порядок на:

 SomeId int,
 SomeValue int,
 SomeString varchar(100)

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

EDIT: Sql Server 2005 хранит поля фиксированной длины в начале строки. И каждая строка имеет ссылку на начало varchar. Это полностью отрицает эффект, который я привел выше. Поэтому для последних баз данных порядок столбцов больше не влияет.

Ответ 2

Update:

В MySQL может быть причина для этого.

Поскольку переменные типы данных (например, VARCHAR) хранятся с переменной длиной в InnoDB, механизм базы данных должен пересекать все предыдущие столбцы в каждой строке, чтобы узнать смещение заданного.

Эффект может быть как 17% для столбцов 20.

Смотрите эту запись в моем блоге для более подробной информации:

• Выбор порядка столбцов

В Oracle конечные столбцы NULL не занимают места, поэтому вы всегда должны помещать их в конец таблицы.

Также в Oracle и в SQL Server в случае большой строки может возникнуть a ROW CHAINING.

ROW CHANING - это разбиение строки, которая не вписывается в один блок и охватывает ее по нескольким блокам, связанным со связанным списком.

Чтение трейлинг-столбцов, которые не вписывались в первый блок, потребует прохождения связанного списка, что приведет к дополнительной операции I/O.

Смотрите на этой странице для иллюстрации ROW CHAINING в Oracle:

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

Важное примечание:

Если вам нравится этот ответ и вы хотите проголосовать за него, проголосуйте за @Andomar answer.

Он ответил на одно и то же, но, по-видимому, никем не склонен.

Ответ 3

Во время обучения Oracle на предыдущем задании наш администратор баз данных предположил, что размещение всех столбцов с недействительными значениями до нулевых может быть выгодным... хотя TBH я не помню подробностей о том, почему. Или, может быть, именно те, которые, вероятно, будут обновляться, должны идти в конце? (Может быть, отключение необходимости переместить строку, если она расширяется)

В общем, это не должно иметь никакого значения. Как вы говорите, запросы всегда должны указывать сами столбцы, а не полагаться на порядок от "select *". Я не знаю ни одной БД, которая позволяет им изменять... ну, я не знал, что MySQL разрешил это, пока вы не упомянули об этом.

Ответ 4

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

Ответ 5

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

Марк

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

Отношение определяется как набор n-кортежей. Как в математике, так и в модели реляционной базы данных набор представляет собой неупорядоченный набор элементов, хотя некоторые СУБД налагают порядок на их данные. В математике кортеж имеет порядок и допускает дублирование. E.F. Codd изначально определял кортежи, используя это математическое определение. Позже, это был один из самых известных идей Э.Ф. Кодда, что использование имен атрибутов вместо заказа было бы намного более удобным (в общем) на компьютерном языке, основанном на отношениях. Это понимание все еще используется сегодня.

Ответ 6

Считываемость вывода при нахождении:

select * from <table>

в вашем программном обеспечении для управления базами данных?

Это очень ложная причина, но на данный момент я не могу думать ни о чем другом.

Ответ 7

Единственная причина, по которой я могу думать, - это отладка и пожаротушение. У нас есть таблица, чей столбец "name" появляется около 10-го числа в списке. Это боль, когда вы быстро выбираете * из таблицы, где id в (1,2,3), а затем вам нужно прокрутить страницу, чтобы посмотреть на имена.

Но об этом.

Ответ 8

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

Ответ 9

Помимо очевидной настройки производительности, я просто столкнулся с угловым случаем, когда переупорядочивание столбцов вызвало ошибку (ранее функциональный) sql script.

В документации столбцы TIMESTAMP и DATETIME не имеют автоматических свойств, если они не указаны явно, с этим исключением: по умолчанию первый столбец TIMESTAMP имеет как DEFAULT CURRENT_TIMESTAMP, так и ON UPDATE CURRENT_TIMESTAMP, если они не указаны явно " https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

Итак, команда ALTER TABLE table_name MODIFY field_name timestamp(6) NOT NULL; будет работать, если это поле является первой меткой времени (или datetime) в таблице, но не иначе.

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

Ответ 10

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

Ответ 11

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

Ответ 12

В общем, что происходит в SQL Server при изменении порядка столбцов через Management Studio, заключается в том, что он создает временную таблицу с новой структурой, перемещает данные в эту структуру из старой таблицы, отбрасывает старую таблицу и переименовывает новую один. Как вы можете себе представить, это очень плохой выбор для производительности, если у вас большой стол. Я не знаю, делает ли мой SQL то же самое, но это одна из причин, почему многие из нас избегают переупорядочения столбцов. Поскольку select * никогда не должен использоваться в производственной системе, добавление столбцов в конце не является проблемой для хорошо продуманной системы. Порядок столбцов в таблице должен в genral не быть запутанным.

Ответ 13

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

Конечно, любые последствия для производительности сильно зависят не только от производителя, который вы используете, но также и от версии. Несколько месяцев назад я заметил, что наши Postgres не могли использовать индекс для сравнения "как". То есть, если вы написали "somecolumn like" M% ", он не был достаточно умен, чтобы перейти к M и выйти, когда обнаружил первый N. Я планировал изменить кучу запросов, чтобы использовать" между ". Затем мы получили новую версию Postgres, и она справлялась так же разумно. Рад, что мне не удалось изменить запросы. Очевидно, что здесь прямо не уместно, но я считаю, что все, что вы делаете для соображений эффективности, может быть устаревшим со следующей версией.

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