Вопрос или проблема
По чисто эстетическим причинам я всегда делал первую колонку(и) таблицы колонкой(и) первичного ключа. После этого я не обращал внимания на порядок добавления колонок в таблицу. Это неправильно?
Есть ли повышение производительности, если размещать, скажем, целочисленные колонки в таблице перед текстовыми или бинарными колонками? Или, возможно, сначала индексированные колонки?
Текущая база данных, с которой я работаю, это MySQL, но ответы для других баз данных тоже будут полезны.
Это неправильно?
Нет, я делаю то же самое — в основном потому, что таблица всегда начинается с первичного ключа.
Есть ли повышение производительности, если
размещать, скажем, целочисленные колонки в таблице
перед текстовыми или бинарными колонками? Или
возможно сначала индексированные колонки?
Нет, в SQL Server. Если это так в MySQL, это было бы ошибкой довольно крупного масштаба, если только издержки не очень малы. Индексы живут отдельно от таблиц. За время жизни базы данных, индексированные и даже существующие колонки могут меняться.
На самом деле, для действительно горячих нагрузок это может иметь значительное влияние. Если вы посмотрите на Анатомию Записи, вы увидите, что колонки следуют за заголовком записи, сначала фиксированные, затем переменной длины. Поэтому, когда колонка запрашивается, в первую очередь должен быть доступ к заголовку записи, и этот доступ почти всегда приводит к промаху кеша L2. Любой последующий доступ в пределах той же строки кеша (64 байта) будет попаданием в кеш L2 почти в 100% случаев. Учитывая, что разница в циклах процессора между промахом и попаданием в кеш L2 составляет примерно 2 порядка величины, вы получаете довольно значительное повышение производительности, если расположите часто запрашиваемые колонки ближе к заголовку записи. Полное повышение производительности не будет равняться 2 порядкам величины, но для определенных OLTP-нагрузок это может составить 5-10% в целом. Для аналитических нагрузок стоимость ввода-вывода перевешивает все остальное, и, вероятно, вы не сможете измерить разницу.
Эта логика применяется к каждому индексу индивидуально, но по индексам нужно учитывать, что порядок объявления индекса — это фактический порядок ключа, так что здесь у вас мало места для изменений.
Я бы сказал, что в SQL Server это не имеет значения. SQL Server читает целые страницы, и я не уверен, что обработка для доступа к 3-й колонке на странице отличается от 2-й или 5-й или любой другой.
Не имеет значения для таблицы, но имеет для индекса. Первая колонка в индексе должна быть в предложении WHERE, чтобы индекс был использован.
Ответ или решение
Влияние порядка создания столбцов на производительность базы данных
Теория
При создании таблицы в базе данных, естественным образом возникает вопрос: влияет ли порядок создания столбцов на производительность? Часто первый столбец используется в качестве основного ключа (Primary Key), и после этого мы можем не обращать особого внимания на порядок столбцов. Однако имеет ли это значение с точки зрения производительности, например, если сначала идут целочисленные столбцы, а затем текстовые или бинарные? Или же имеет ли значение расположение индексируемых столбцов в начале таблицы?
Пример
В некоторых реляционных системах управления базами данных (СУБД), например MySQL, PostgreSQL или SQL Server, порядок столбцов влияет на организацию данных и их доступ. Например, в MySQL физическое расположение данных на диске может варьировать производительность доступа, особенно при "горячем" обращении к данным.
Сначала фиксированные данные (целые числа, флаги и т.д.) обычно следуют заголовку записи, за ними идут переменной длины данные (строки, бинарные данные). Когда осуществляется доступ к столбцу, сначала должен быть прочитан заголовок записи, и этот доступ обычно является пропуском L2-кэша. Любой последующий доступ в пределах той же линии кэша (обычно 64 байта) будет почти всегда попадать в кэш, что значительно ускоряет производительность.
Применение
На практике это означает, что столбцы, к которым часто обращаются, лучше размещать ближе к началу записи, чтобы минимизировать кэш-пропуски и повысить производительность. Такой подход может повысить общую производительность OLTP-систем (системы, с высокой частотой транзакций) на 5-10%. Для аналитических нагрузок, где доминирует стоимость ввода-вывода, эта оптимизация будет менее ощутимой.
В дополнение, необходимо учитывать, что порядок столбцов в индексе также важен. Первый столбец индекса должен обязательно участвовать в WHERE-условии, чтобы индекс был эффективно использован.
Таким образом, хотя порядок столбцов в таблице может не иметь критического значения для некоторых систем и нагрузок, в других случаях (особенно в системах с высокой частотой транзакций) грамотное размещение часто используемых столбцов может существенно повысить производительность работы базы данных.