Эффективное индексирование базы данных

Эффективное индексирование базы данных в нормализованной базе данных будет иметь большее влияние на производительность, чем простое добавление дополнительных ресурсов в систему.

Что такое нормализованная база данных?

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

В этой статье основное внимание уделяется базам данных, которые работают в нормализованной структуре, и исследуется область, с которой большинство людей знакомо (или может представить), а именно финансовые транзакции, клиенты и контакты.

Почему нормализованный?

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

Ускорьте обновления (см. Ниже)
Упростите запрос данных
Обычно обеспечивает меньший объем данных
Соответствует отраслевым нормам

Наш подход

Наш стандартный подход состоит в том, чтобы рассматривать данные так, как будто они хранятся тремя разными способами, и при создании новых систем на основе SQL Server мы стараемся хранить их в разных схемах.

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

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

Обзор указателя

В то время как SQL Server сосредоточен, одни и те же принципы применимы ко многим различным системам. Количество и типы индексов могут независимо улучшить или снизить производительность чтения и записи.

Кластеризованный

Вы ограничены одним на таблицу, и это определяет, как данные хранятся на диске.

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

Некластеризованный

Вы можете почти думать об этом как об отдельной таблице, которая ссылается на каждую строку, однако в SQL Server фактическое хранилище изменяется в зависимости от типа таблицы (кластеризованная / куча)

Уникальность

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

Составные индексы

Все индексы могут использовать один или несколько столбцов, однако кластеризованный индекс должен быть меньше 900 байт.

Погодите, а как насчет первичного ключа?

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

Фактически внешний ключ может ссылаться на любой уникальный индекс и даже ссылаться на несколько столбцов.

Справочные данные

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

Стандартное использование

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

Reference Tables

CREATE SCHEMA RefGOCREATE TABLE Ref.AddressType(AddressTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_AddressType PRIMARY KEY CLUSTERED,AddressTypeName NVARCHAR(100))CREATE TABLE Ref.ClientType(ClientTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_Client PRIMARY KEY CLUSTERED,ClientTypeName NVARCHAR(100))CREATE TABLE Ref.ContactType(ContactTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_ContactType PRIMARY KEY CLUSTERED,ContactTypeName NVARCHAR(100))CREATE TABLE Ref.TransactionType(TransactionTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_TransactionType PRIMARY KEY CLUSTERED,TransactionTypeName NVARCHAR(100))

Бизнес-данные

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

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

Ниже представлена таблица для создания таблиц адресов, клиентов и контактов. В этом коде есть дополнительная (объединяющая) таблица, которая объединяет поля Client, Address и Address type, и здесь мы создали кластеризованный индекс, который работает иначе, чем другие таблицы. Это связано с тем, что в большинстве приложений это таблица с интенсивным чтением, и мы можем согласиться с минимальным увеличением производительности вставки. Если бы это было приложение, созданное нами, мы, вероятно, разделили бы контактные данные клиентов аналогичным образом.

Business Tables

CREATE SCHEMA BusGOCREATE TABLE Bus.[Address](AddressID INT CONSTRAINT PK_Bus_Address PRIMARY KEY CLUSTERED,AddressName NVARCHAR(100),AddressTypeID INT CONSTRAINT FK_Bus_Client_AddressTypeID FOREIGN KEY REFERENCES Ref.AddressType(AddressTypeID),AddressLine1 NVARCHAR(MAX)--Use more detail as required...)CREATE TABLE Bus.Client(ClientID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,ClientName NVARCHAR(100),ClientType INT CONSTRAINT FK_Bus_Client_ClientType FOREIGN KEY REFERENCES Ref.ClientType(ClientTypeID))--Use one table to handle all client addressesCREATE TABLE Bus.ClientAddress(ClientAddressID INT IDENTITY(1,1) CONSTRAINT PK_Bus_ClientAddressID PRIMARY KEY NONCLUSTERED,AddressTypeID INT,ClientID INT,AddressID INT,CONSTRAINT UQ_Bus_ClientAddress UNIQUE NONCLUSTERED (ClientID,AddressTypeID)--This ensures one type per client, can slow down inserts slightly)CREATE UNIQUE CLUSTERED INDEX CDX_Bus_ClientAddress ON Bus.ClientAddress(ClientID,AddressTypeID,AddressID)CREATE TABLE Bus.Contact(ContactID INT IDENTITY(1,1) CONSTRAINT PK_Bus_Contact PRIMARY KEY CLUSTERED,ContactName NVARCHAR(100),ContactTypeID INT CONSTRAINT FK_Bus_Contact_ContactTypeID FOREIGN KEY REFERENCES Ref.ContactType(ContactTypeID)--Could be broken out into a joining table if desired--Use more detail as required...)

Транзакционные данные

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

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

Transactional Tables

CREATE SCHEMA TraGOCREATE TABLE Tra.[Transaction](TransactionID INT IDENTITY(1,1) CONSTRAINT PK_Tra_TransactionID PRIMARY KEY NONCLUSTERED,TransactionDate DATETIME CONSTRAINT DF_Tra_Transaction_TransactionDate DEFAULT GETUTCDATE(),--Use GETDATE() for local time.TransactionTypeID INT CONSTRAINT FK_Tra_Transaction_TransactionTypeID FOREIGN KEY REFERENCES Ref.TransactionType(TransactionTypeID),ClientID INT CONSTRAINT FK_Tra_Transaction_ClientID FOREIGN KEY REFERENCES Bus.Client(ClientID),ContactID INT CONSTRAINT FK_Tra_Transaction_ContactID FOREIGN KEY REFERENCES Bus.Contact(ContactID),TransactionAmount DECIMAL(18,2)--Use more detail as required...)CREATE CLUSTERED INDEX CDX_Tra_Transaction ON Tra.[Transaction](TransactionDate,TransactionTypeID,ClientID,ContactID)

Присоединения и отчетность

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

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