Имеет ли значение порядок столбцов в ваших таблицах MySQL?
Изучая mysql, я прочитал, что вы можете выполнить следующее утверждение при добавлении столбца в таблицу mysql:
ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;
или же
ALTER TABLE contacts ADD email VARCHAR(60) FIRST;
Когда бы вы хотели это сделать? Можно ли использовать порядок столбцов для оптимизации запросов? Должны ли longblobs быть последним столбцом для оптимизации потребления пространства? Или эти команды существуют по какой-то другой причине?
7 ответов
Да, порядок столбцов имеет значение. Но, если вы хотите оптимизировать, ваша самая вероятная ставка (в 90% случаев) - добавить индекс. В официальной документации MySQL обсуждается оптимизация только в контексте добавления индексов (Источник: Dev.MySQL.com: Как MySQL использует индексы).
Но к вопросу - порядок столбцов абсолютно важен. На самом деле все зависит от того, как связанные строки и блоки памяти работают в MySQL Engine. Процитируем Мартина Зана, сертифицированного специалиста Oracle, из статьи о секретах цепочки строк и миграции Oracle...
Связанные строки по-разному влияют на нас. Здесь это зависит от того, какие данные нам нужны. Если бы у нас была строка с двумя столбцами, распределенная по двум блокам, запрос:
SELECT column1 FROM tableгде column1 находится в блоке 1, не приведет к "продолжению строки выборки таблицы". На самом деле ему не нужно было бы получать column2, он не будет полностью следовать за цепочкой строк. С другой стороны, если мы попросим:
SELECT column2 FROM tableа column2 находится в блоке 2 из-за цепочки строк, тогда вы фактически увидите "непрерывную строку выборки таблицы"
Это явно создает впечатление, что если мы выберем столбец 2 больше, чем столбец 1, то мы должны переупорядочить столбцы как средство оптимизации наших запросов к базе данных.
Я нашел старый пост на форумах HP Enterprise от 2002 года, который с тех пор был скопирован как минимум сотней постов после поиска. Приведенные здесь предложения о том, что делать для упорядочивания столбцов, определенно соответствуют подробным объяснениям профессионалов. Итак, спасибо, Билл Торстейнсон!
Чтобы оптимизировать запросы, упорядочивайте столбцы в соответствии со следующими правилами:
- Сначала столбцы первичного ключа.
- Следующие столбцы внешнего ключа.
- Далее следуют часто просматриваемые столбцы.
- Часто обновляемые столбцы позже.
- Обнуляемые столбцы идут последними.
- Наименее используемые столбцы, допускающие значение NULL, после более часто используемых столбцов, допускающих значение NULL.
- BLOB-объекты в собственной таблице с несколькими другими столбцами.
Источник: форумы HP.
Вопрос не имеет ничего общего с реляционной моделью или SQL. Это вопрос производительности.
В некоторых базах данных более эффективно упорядочивать столбцы определенным образом из-за способа доступа к диску. Существенное преимущество также зависит от платформы. Это низкоуровневая проблема ввода-вывода, связанная с тем, как спроектировано основное хранилище и как к нему обращается механизм. Поставщики патентованных движков обычно предоставляют эту информацию через свои учебные и учебные отделы.
Я думаю, вам нужно поговорить с кем-то, кто знает мельчайшие подробности о модели хранилища и методах ввода / вывода для MySQL на вашей конкретной платформе, или с тем, кто тестировал это на вашей платформе, чтобы получить ответ.
Вполне возможно, что они положили его на диск оптимизированным способом и скрыли порядок столбцов от вас.
Это, однако, повлияет на порядок результата в select * from mytable,
Вот почему вы всегда должны называть столбец в операторе выбора, например select col1, col2 from mytable, Но если вы знаете, что приложение использует *, то вы должны быть осторожны, когда добавляете столбец.
В противном случае упорядочите столбец так, чтобы он был наиболее логичным для понимания. Если это влияет на производительность, то это означает, что вы уже находитесь на темной стороне настройки производительности базы данных и, возможно, у вас проблема в другом месте.
В реляционной модели отсутствует концепция упорядочения столбцов в строках и отсутствует порядок упорядочения строк в таблицах.
"Когда вы захотите использовать это", а не проблема производительности.
Репликация на основе строк прервется, если порядок столбцов будет различаться между главным и подчиненным, а типы столбцов несовместимы (ошибка 1677).
ALTER TABLE contacts MODIFY email VARCHAR(60) AFTER name;
будет одним из способов исправить эту проблему.
Я думал, что это не имеет значения производительности, но в некоторых случаях - когда вы используете индексы.
Пример.
Я использовал таблицу соединений:
| category_id | user_id |
Обе колонки были unsigned int (10), а также primary key ( category_id, user_id )Обратите внимание на порядок столбцов. Когда я начал выбирать из этой таблицы с where user_id = ? - производительность была низкой.
После того, как я изменил порядок столбцов (сначала сделал user_id), он начал выбирать время быстрее.
Нет, это не должно иметь значения. Нормализованная база данных также не должна иметь ограничений на порядок столбцов.
Порядок столбцов не имеет значения. Это чисто удобная функция. просто чтобы позволить вам реструктурировать таблицу базы данных так, как вам нравится после того, как она была создана.