Нормализация чрезвычайно большого стола

Я сталкиваюсь со следующей проблемой. У меня очень большой стол. Эта таблица-наследие людей, которые ранее работали над проектом. Таблица находится в MS SQL Server.

Таблица имеет следующие свойства:

    Он имеет около 300 колонок. Все они имеют тип "text", но некоторые из них в конечном итоге должны представлять другие типы (например, integer или datetime). Поэтому перед использованием этих текстовых значений необходимо преобразовать их в соответствующие типы
  1. таблица имеет более 100 млн строк. Пространство для таблицы скоро достигнет 1 терабайта
  2. таблица не имеет индексов
    3. Таблица не имеет каких-либо реализованных механизмов разбиения на разделы.

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

Одной из наиболее важных особенностей таблицы является то, что те поля, которые являются чисто текстовыми (т. е. их не нужно преобразовывать в другой тип), обычно имеют часто повторяющиеся значения. Таким образом, фактическое разнообразие значений в данном столбце находится в диапазоне 5-30 различных значений. Это наводит на мысль сделать нормализацию: для каждого такого текстового столбца я создам дополнительную таблицу с список всех различных значений, которые могут появиться в этом столбце, затем я создам (tinyint) первичный ключ в этой дополнительной таблице, а затем буду использовать соответствующий внешний ключ в исходной таблице вместо сохранения этих текстовых значений в исходной таблице. Затем я помещу индекс на этот столбец внешнего ключа. Число столбцов, обрабатываемых таким образом, составляет около 100.

В связи с этим возникают следующие вопросы:

  1. действительно ли эта нормализация увеличит скорость Квейрис навязывать условия на некоторых из этих 100 полей? Если мы забудем о размере, необходимом для хранения этих столбцов, будет ли какое-либо увеличение производительности из-за подстановки начальных текстовых столбцов с помощью tinyint-столбцов? Если я не делаю никакой нормализации и просто помещаю индекс на эти начальные текстовые столбцы, будет ли performace таким же, как для индекса на запланированном tinyint-столбце?
  2. Если я выполню описанную нормализацию, то построю представление, показывающее текстовых значений потребует присоединиться к моей основной таблице около 100 дополнительных таблиц. Положительным моментом является то, что я буду делать эти соединения для пар "primary key"="foreign key". Но все же достаточно большое количество столов должно быть объединено. Возникает вопрос: будет ли производительность запросов, сделанных к этому представлению, по сравнению с производительностью запросов к исходной ненормализованной таблице не хуже? Действительно ли оптимизатор SQL Server сможет оптимизировать запрос так, как это позволяет пользуясь преимуществами нормализации?

Извините за такой длинный текст.

Спасибо за каждый комментарий!

PS Я создал связанный вопрос о соединении 100 таблиц; соединение 100 таблиц

4 9
sql sql-server sql-server-2008 normalization database-normalization

4 ответа:

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

Однако это также, вероятно, повысит скорость запросов; в настоящее время наличие одной строки, содержащей 300 текстовых столбцов, является массивным и почти наверняка превышает предел в 8060 байт для хранения страницы данных строки... и вместо этого хранится в выделении ROW_OVERFLOW_DATA или LOB_DATA Единицы.

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

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

5

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

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

Кроме того, SQL Server распространит столбцы varchar на дополнительные страницы. Он не разливает другие типы. У вас есть только 300 столбцов, но в конечном итоге ваши данные записи могут приблизиться к пределу 8k для данных на одной странице.

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

4

1) в настоящее время системе приходится выполнять полное сканирование таблиц на очень значительных объемах данных, что приводит к проблемам с производительностью. Существует много аспектов оптимизации, которые могут улучшить эту производительность. Преобразование столбцов в правильные типы данных не только значительно повысит производительность за счет уменьшения размера каждой записи, но и позволит корректировать данные. При запросе столбца вы в данный момент смотрите на текст, который сравнивается с текстом в поле. С помощью простого индексирования это можно было бы улучшить, но переход на поиск позволит искать значение идентификатора из таблицы, достаточно маленькой, чтобы сохранить его в памяти, а затем использовать его для сканирования только целочисленных значений, что намного быстрее. 2) если данные нормализованы до 3-й нормальной формы или тому подобного, то вы можете увидеть случаи, когда производительность страдает во имя целостности данных. Это большая проблема, если механизм не может решить, как ограничить строки, не проецируя данные в первую очередь. Если это тем не менее, план выполнения может определить это, и запрос может быть изменен, чтобы уменьшить вероятность этого.

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

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

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

2
  1. Создайте в своей голове и на бумаге нормализованную структуру базы данных
  2. построить базу данных (с индексами)
  3. разрушьте этот монолит. Все будет выглядеть не так уж плохо. Я бы предположил, что много (я имею в виду много) данных повторяется
  4. создание инструкций SQL insert для вставки данных в базу данных
    5. Идите к людям, которые создали этот кошмар в первую очередь с дробовиком. Повеселись.
1