Как лучше хранить широкие таблицы в реляционной БД?
Есть мнение, что на практике второй вариант работает быстрее.
А какой характер работы с этой таблицей?
Выборки по произвольному набору полей. Insert, update, delete.
Думаю ничто не мешает комбинировать подходы. В зависимости от типов столбцов, их селективности в типичных запросах, и т.д. В теории для заданной СУБД и заданного сценария можно подобрать оптимальную реализацию 
Скорее всего есть какая-то золотая середина, которую надо искать на основании особенностей твоей конкретной ситуации... Я вот в принципе не могу себе помыслить 100 различных по сути полей, наверное есть все-таки какие-то между ними взаимосвязи, точнее сказать закономерности что ли. Вот и отражай их в базе. Банальность конечно пишу, но все-таки... 
Все равно нужно расставить приоритеты, т.к. определенная реализация будет лучше для определенных сценариев.
Есть такое предположение, что столбцы с большей селективностью можно выносить в отдельные таблицы, при этом даже если на внешние ключи и не будет индексов, то через хэш-джойны все равно будет нормально работать. Так ли это?
Я вот в принципе не могу себе помыслить 100 различных по сути полей, наверное есть все-таки какие-то между ними взаимосвязиНапример, при покупки автомобиля в кредит покупатель заполняет большую анкету. Банки хотят знать о клиенте побольше. Вот и получается больше 100 полей.
Еще наверно столбцы мелких типов фиксированной длины лучше группировать в одной таблице. Дальше всякие строковые особенно переменноц длины нужно группировать по таблицам так, чтобы в одной таблице была не очень большая разбежка по длинам. Т.е. одна таблица с не очень длинными, вторая с средними, и третья с длинными.
их селективности в типичных запросах, и т.д.я же написал, нет понятия типичных запросов. Запросы по произвольному набору полей.
Еще наверно столбцы мелких типов фиксированной длины лучше группировать в одной таблице. Дальше всякие строковые особенно переменноц длины нужно группировать по таблицам так, чтобы в одной таблице была не очень большая разбежка по длинам. Т.е. одна таблица с не очень длинными, вторая с средними, и третья с длинными.вам не кажется, что вы слишком много о предметной области хотите знать. И вообще хватит строить рамки, у нас в руках такие вычислительные мощности, а вы...
А если по лучше проанализировать твой пример, то можно выделить разные классы столбцов. Будут скорее всего какие-то по которым вообще не имеет смысл поиск делать (например, адрес) -- их точно можно выкинуть в отдельную таблицу.
это почему, может мне хочется знать сколько людей, живущих на этой улице, купили мерседес.
Просто характер данных и работа с ними сильно зависит от семантики столбца. Сравни, например,"адрес" (хз кому надо поиск делать, наверно средней длинный "любимый цвет" (цветов не так много, поиск если и будет то по одному значению, короткий по длине "трудовой стаж " (тут поиск по диапазону и т.д.
Просто характер данных и работа с ними сильно зависит от семантики столбца. Сравни, например,"адрес" (хз кому надо поиск делать, наверно средней длинный "любимый цвет" (цветов не так много, поиск если и будет то по одному значению, короткий по длине "трудовой стаж " (тут поиск по диапазону и т.д.как это относится к теме этого треда?
ну тогда адрес не подходит
Очень просто -- если нет данных от селектах, то нужно хоть какие-то данные (который повлияют на реализацию) о самих столбцах собрать. Вот я и пытаюсь сообразить на что нужно братить внимание, и такие их этого сделать выводы (т.е. нужны хоть какие-то критерии, иначе от балды получается). ИМХО типы столбцов и их семантика дают уже неплохую подсказку от том, как поступать с этими столбцами. Оталось только правильно проанализировать, выделить характерные (значимые с точки зрения реализации) классы столбцов.
Нормально с учетом того, что тебе сказали, что в чистом виде ни один (горизонтальный или вертикальный) не будет лучшим вариантом.
В ответ на:
--------------------------------------------------------------------------------
Просто характер данных и работа с ними сильно зависит от семантики столбца. Сравни, например,"адрес" (хз кому надо поиск делать, наверно средней длинный "любимый цвет" (цветов не так много, поиск если и будет то по одному значению, короткий по длине "трудовой стаж " (тут поиск по диапазону и т.д.
--------------------------------------------------------------------------------
как это относится к теме этого треда?
Выборки по произвольному набору полей. Insert, update, delete.Во-первых, попробуй и то и другое. Посмотри на производительность.
Лично по-моему опыту горизонтальный вариант нужен тогда, когда ты собираешься задействовать все столбцы. Например, залить новую анкету или её показать.
Но если нужны insert, update, delete произвольных строк, то вертикальный вариант подойдет лучше всего.
Разные подходы к проектированию описаны много где. Например, здесь: http://ids.snu.ac.kr:8080/DocsData/International/IC-03-003.pdf
Во-первых, попробуй и то и другое. Посмотри на производительность.Это дорого
. Я хотел теоретического обоснования.Ок. Интересное упражнение -- сравнить теоретически эти два варианта. Могу попробовать для SQL Server, самому интересно.
Рассмотрим вертикальный вариант с таблицей T(rowid, colid, value). Вот уж не знаю какого типа должно быть value -- Variant? ИМХО все равно лучше сделать несколько таблиц для разных типов (хотя это конечно немножко усложняет реализацию). colid пусть будет smallint или даже tinyint, а то получается, что этот вариант обладает неслабым недостатком, что для каждой строчки в таблице сидят строковые значения всех столбцов (со значениями). Так размер таблицы может и в несколько раз вырасти.
Очевидно, что нужен индекс на (colid, value). Иначе на условия colid=n & C(value) (а именно такие условия у нас и будут) будет делаться полный скан таблицы. Это делает этот вариант хуже, чем горизонтальный, т.к. там полный скан дает уже готовый ответ (ну почти, не считая группировки и сортиворки, по эта часть скорее всего будет общая, т.е. они будут выполняться после джойнов и фильтрации). Скорее всего важно будет быстро загружать одну запись, поэтому пусть будет кластерный индекс на rowid (можно подумать о варианте некластерного, тут есть свои плюсы и минусы, но с кластерныйм интексом этот вариант будет ближе по упаковке данных в страницы к горизонтальному варианту.
Горизонтальный вариант простой: таблица T(rowid, value1, …, valueN и пусть пока на каждый столбец по индексу.
Очевиден сразу плюс у горизонтального варианта -- запросы проще.
Теперь сравним как будут выполняться селекты.
Нехороший момент (очень связанный с вертикальным вариантом состоит в том, что на (colid, value) статистика будет плохо работать, т.е. фактически уже даже при не очень большой таблице статистка мало, что будет отображать. Не говоря о том, что по умолчанию статистика создается только по первому столбцу составного индекса. Что это означает. Просто планировщик не может оценить правильный порядок селф-джойнов, который очень важен из-за того, что мердж-джойны тут не применимы, т.к. делается идекс сик. Т.е. остается вариант с хеш-джойном, но он хорошо работается для случая, когда хотя бы одна таблица небольшая. В этом варианте все это будет делаться в случайном порядке.
Теперь, что будет с горизонтальным вариантом. Все очень круто Планировщик выберет несколько индексов, у который самая высокая селективность по условиям запроса (это очень неплохо оценивается по статистикам). Дальше сделает по ним хэш-джойны в правильном порядке. Дальше может сделать сортировку по rowid (хотя не факт, что лучше -- это тоже нормально оценит) и сделает мердж-джойн (ну или хэш-джойн) с нужно частью T и до фильтрует по оставшимся условиям. В итоге, всегда будет очень эффективная реализация селективных запрос. Ну а не селективные запросы, они и по определению дорогие .
Короче вертикальный вариант ужасно работает для селектов, годится разве что для загрузки записей. Разве, что руками выбрать порядок джойнов, хотя для этого нужно знать специфику запросов, чего автор треда хотел избежать.
Теперь про добавления, изменения и удаления. Да, 100 индексов это, что-то нереально, по крайней мере в чистом варианте. Но тут можно кое-что сделать. Например, для твоей ситуации с банком скорее всегда, что будут делать много инсетов, мало удалений (хотя это можно решить с помощь введения флага «удален», да и сам SQL Server довольно не плохо справляется, у него есть такая штука как записи-призраки мало изменений, где скорее всего мало полей меняют значения (т.е. затрагивается немного индексов). Проблема с инсертами решается просто -- создается еще одна таблица, с теми же столбцами, но с меньшим числом индексов (оставим самые часто используемые) -- туда и делаем инсерты. Селекты делаем по объединению таблиц. Периодически переливаем вторую таблицу в первую, при этом снимаем индексы, а потом восстанавливаем). Ограничение на размер второй таблицы рассчитываем так, чтобы селекты по ней были заметно дешевле, чем перелить таблицу в первую. Плюс можно делать переливание ночью всемте со всякими дефрагментациями и шринками.
Еще можно заменить, что в горизонтальном случае будет больше проблем с фрагментированностью и не эффективной упаковкой данный (из-за больших записей). Но, это слабо отразится на преимуществе этого варианта при выполнении селективных запросов.
Но, это все теория -- как оно на практике, хз.
Все. Ничего себе накатал.
Рассмотрим вертикальный вариант с таблицей T(rowid, colid, value). Вот уж не знаю какого типа должно быть value -- Variant? ИМХО все равно лучше сделать несколько таблиц для разных типов (хотя это конечно немножко усложняет реализацию). colid пусть будет smallint или даже tinyint, а то получается, что этот вариант обладает неслабым недостатком, что для каждой строчки в таблице сидят строковые значения всех столбцов (со значениями). Так размер таблицы может и в несколько раз вырасти.
Очевидно, что нужен индекс на (colid, value). Иначе на условия colid=n & C(value) (а именно такие условия у нас и будут) будет делаться полный скан таблицы. Это делает этот вариант хуже, чем горизонтальный, т.к. там полный скан дает уже готовый ответ (ну почти, не считая группировки и сортиворки, по эта часть скорее всего будет общая, т.е. они будут выполняться после джойнов и фильтрации). Скорее всего важно будет быстро загружать одну запись, поэтому пусть будет кластерный индекс на rowid (можно подумать о варианте некластерного, тут есть свои плюсы и минусы, но с кластерныйм интексом этот вариант будет ближе по упаковке данных в страницы к горизонтальному варианту.
Горизонтальный вариант простой: таблица T(rowid, value1, …, valueN и пусть пока на каждый столбец по индексу.
Очевиден сразу плюс у горизонтального варианта -- запросы проще.
Теперь сравним как будут выполняться селекты.
Нехороший момент (очень связанный с вертикальным вариантом состоит в том, что на (colid, value) статистика будет плохо работать, т.е. фактически уже даже при не очень большой таблице статистка мало, что будет отображать. Не говоря о том, что по умолчанию статистика создается только по первому столбцу составного индекса. Что это означает. Просто планировщик не может оценить правильный порядок селф-джойнов, который очень важен из-за того, что мердж-джойны тут не применимы, т.к. делается идекс сик. Т.е. остается вариант с хеш-джойном, но он хорошо работается для случая, когда хотя бы одна таблица небольшая. В этом варианте все это будет делаться в случайном порядке.
Теперь, что будет с горизонтальным вариантом. Все очень круто
Планировщик выберет несколько индексов, у который самая высокая селективность по условиям запроса (это очень неплохо оценивается по статистикам). Дальше сделает по ним хэш-джойны в правильном порядке. Дальше может сделать сортировку по rowid (хотя не факт, что лучше -- это тоже нормально оценит) и сделает мердж-джойн (ну или хэш-джойн) с нужно частью T и до фильтрует по оставшимся условиям. В итоге, всегда будет очень эффективная реализация селективных запрос. Ну а не селективные запросы, они и по определению дорогие .Короче вертикальный вариант ужасно работает для селектов, годится разве что для загрузки записей. Разве, что руками выбрать порядок джойнов, хотя для этого нужно знать специфику запросов, чего автор треда хотел избежать.
Теперь про добавления, изменения и удаления. Да, 100 индексов это, что-то нереально, по крайней мере в чистом варианте. Но тут можно кое-что сделать. Например, для твоей ситуации с банком скорее всегда, что будут делать много инсетов, мало удалений (хотя это можно решить с помощь введения флага «удален», да и сам SQL Server довольно не плохо справляется, у него есть такая штука как записи-призраки мало изменений, где скорее всего мало полей меняют значения (т.е. затрагивается немного индексов). Проблема с инсертами решается просто -- создается еще одна таблица, с теми же столбцами, но с меньшим числом индексов (оставим самые часто используемые) -- туда и делаем инсерты. Селекты делаем по объединению таблиц. Периодически переливаем вторую таблицу в первую, при этом снимаем индексы, а потом восстанавливаем). Ограничение на размер второй таблицы рассчитываем так, чтобы селекты по ней были заметно дешевле, чем перелить таблицу в первую. Плюс можно делать переливание ночью всемте со всякими дефрагментациями и шринками.
Еще можно заменить, что в горизонтальном случае будет больше проблем с фрагментированностью и не эффективной упаковкой данный (из-за больших записей). Но, это слабо отразится на преимуществе этого варианта при выполнении селективных запросов.
Но, это все теория -- как оно на практике, хз.
Все. Ничего себе накатал
.Инсерт одной строки шириной 100 выполняется дольше, чем инсерт 100 строк в вертикальном варианте? (пусть и в том и в другом варианте постороены все индексы)
А да, не подумал, что с инсертами у вертикального варианта тоже не все в порядке. Ну думаю, что много инсертов в один индекс можно сделать эффективно, если сначала сделать временную таблицу А для одной записи, а потом сделать INSERT T SELECT * FROM A. Тогда SQL Server будет упорядоченно модифицировать индекс. Хотя с учетом того, что colid в A будут разными, и по одной строке, то это мало поможет. Скорее всего преимущество вертикального варианта будет заметно, если группировать инсерты нескольких записей, строя A, а только потом перегонять в Т.
Оставить комментарий
6yrop
Таблицы с количеством столбцов порядка 100-200 и больше. Требуется выполнять sekect-ы по произвольному набору полей.Первый вариант (горизонтальный) создать в БД табличку с большим количеством столбцов.
Второй вариант (вертикальный) описывался .
Как отмечалось в том треде, и как кажется на первый взгляд, второй вариант будет проигрывать первому в производительности, поскольку в запросах будет много join-ов таблицы с большим количеством записей самой на себя. Но оказывается не всё так просто. Для широкой таблицы придется строить большое количество индексов, по каждому полю, а потом еще парные, тройные и т.д. Во втором варианте табличка имеет всего 3 (в реальности 5-6) полей, поэтому можно построить все возможные комбинации индексов.
Кто-нибудь может четко обосновать какой из вариантов будет работать быстрее?