Блог

У меня есть таблица с миллионами строк:

 CREATE TABLE [dbo].[RequestIdentities]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UniqueKey] [nvarchar](256) NULL,
    [Timestamp] [datetime] NULL,

    CONSTRAINT [PK_RequestIdentities] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RequestIdentities] 
    ADD CONSTRAINT [DF_RequestIdentities_Timestamp]  
        DEFAULT (GETDATE()) FOR [Timestamp]
GO 
  

Веб-API управляет базой данных с помощью ADO.NET и делает:

1. Выполняет этот запрос:

     SELECT 1 
    FROM RequestIdentities WITH (nolock) 
    WHERE UniqueKey = @key
     

2. Если существует:

     if(reader.HasRows)
     

3. Возвращает HTTP-ответ.

4. В противном случае он вставляет идентификатор в таблицу:

     INSERT INTO RequestIdentities(UniqueKey) 
    VALUES(@key)
     

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

1 МЛН строк — это не так много данных для современной базы данных на компьютерах, использующих твердотельные накопители. Несколько сотен вставок тоже не так много. Хотя вы можете оптимизировать это, например, с помощью таблиц в памяти, сначала необходимо устранить существующие проблемы.

Таблицы в памяти также могут упростить обслуживание в некоторых сценариях.

Проблемы

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

• WITH (NOLOCK) это очень плохая идея, которая вообще не улучшает производительность. На самом деле это требует * более обширных блокировок (на уровне схемы), считывает грязные, незафиксированные данные, может возвращать одни и те же данные дважды и даже может выдавать ошибку.
• Для вставки одной строки код выполняет два удаленных вызова, что приводит к удвоенной задержке. Помимо задержки, это означает, что блокировки, полученные во время SELECT операции, должны удерживаться намного дольше, чем необходимо, что потенциально блокирует другие соединения, которые пытаются использовать ту же таблицу.
• TOP 1 в лучшем случае не работает. Если UniqueKey действительно уникально, будет возвращен только один результат.

Исправить

Вы можете улучшить это, удалив подсказки и проверку существования. INSERT Запрос может включать FROM WHERE предложение and, что означает, что вы можете написать один запрос только для вставки новой строки. Вы можете использовать предложение OUTPUT для возврата идентификатора новой строки.

Во-первых, вам нужно добавить либо УНИКАЛЬНЫЙ индекс, либо ограничение на UniqueKey столбец. Без одного из них это просто не уникально. Любой может вставлять повторяющиеся значения. УНИКАЛЬНОЕ ограничение фактически создает УНИКАЛЬНЫЙ индекс:

 CREATE UNIQUE INDEX IX_ RequestIdentities_UniqueKey   
   ON RequestIdentities (UniqueKey);
  

После этого вы можете условно вставить и получить новый идентификатор с помощью :

 INSERT INTO RequestIdentities (UniqueKey)
    OUTPUT inserted.ID
SELECT @key
FROM RequestIdentities
WHERE NOT EXISTS ( select * 
                   from RequestIdentities
                   where UniqueKey = @key)
  

Оптимизатор запросов знает, что ему не нужно выдавать никаких результатов EXISTS ( SELECT * , чтобы это не влияло на производительность.

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

Вы можете выполнить этот запрос через SqlCommand с помощью ExecuteScalar() . Это вернет новый идентификатор или null , если результатов нет, потому что строка не была вставлена :

 using(var connection=new SqlConnection(connString))
using(var cmd=new SqlCommand(query,connection))
{
    cmd.Parameters.Add("@key",SqlDbType.NVarChar,256).Value=key;
    connection.Open();
    var result = cmd.ExecuteScalar();
    if (result!=null)
    {
        var newID=(long)result;
        //Use the ID
        ...
    }
}
  

Вы можете пофантазировать, используя синтаксис сопоставления с образцом в C # 8 :

 if(result is long newId)
{
   //Use the ID
}
  

Если этого кода становится слишком много, вы можете использовать микро-ORM, такой как Dapper :

 using(var connection=new SqlConnection(connString))
{
    var result=connection.ExecuteScalar(query,new {key=keyValue});
    if (result is long new ID)
    {
        ...
    }
}
  

Dapper используется StackOverflow, поэтому его производительность гарантирована.

Другие оптимизации

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

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

В этом примере документации используются таблицы в памяти для двух таблиц с высоким трафиком:

• Корзина — это надежная таблица в памяти, данные которой сохраняются на диске. Если сервер выходит из строя, корзины сохраняются.
• Пользовательские сеансы — это ненадежная таблица в памяти. Если сервер выходит из строя, нам плевать на сеансы

В этом случае таблица может быть:

 CREATE TABLE [dbo].[RequestIdentities]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL 
        PRIMARY KEY NONCLUSTERED,
    [UniqueKey] [nvarchar](256) NULL,
    [Timestamp] [datetime] NULL
)  
WITH (  
    MEMORY_OPTIMIZED = ON,  
    //Assuming we want to retain the data
    DURABILITY = SCHEMA_AND_DATA);  
go  

ALTER TABLE RequestIdentities  
    ADD CONSTRAINT RequestIdentities_UniqueKey  
    UNIQUE NONCLUSTERED (UniqueKey);  
go  
  

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

Сначала добавьте новый столбец в таблицу:

 ALTER TABLE [...]
ADD [UniqueKeyHash] VARBINARY(64);
  

Затем добавьте к ней индекс:

 CREATE INDEX IX_..._UniqueKeyHash ON [...]
(
    [UniqueKeyHash] 
);
  

Заполните значения:

 UPDATE [...]
SET [UniqueKeyHash] =  HASHBYTES('SHA2_512', UniqueKey);
  

Измените процедуры CRUD, чтобы также вычислить HASH .

Затем в поиске:

 DECLARE @UniqueKeyHash VARBINARY(64);

SET @UniqueKeyHash = HASHBYTES('SHA2_512', 'some value');

SELECT *
FROM [...]
WHERE [UniqueKeyHas] = @UniqueKeyHash;
  

Кроме того, вы можете добавить столбец as computed and persisted , чтобы пропустить изменение CRUD процедур.

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

Если это слишком сложно для вас, вы можете начать с создания индекса на UniqueKey .

1. Коэффициент ЗАПОЛНЕНИЯ = 80 для очень большой таблицы, особенно для КЛАСТЕРИЗОВАННОГО ИДЕНТИФИКАЦИОННОГО ИНДЕКСА, — это нонсенс. Вы теряете 20% свободного места!
2. если столбец uniqueKey УНИКАЛЕН, добавьте ограничение УНИКАЛЬНОСТИ.
3. [Временная метка] — это зарезервированное слово, не используйте его в качестве имени столбца.

После выполнения этой работы вы можете использовать следующий запрос :

 INSERT INTO dbo.RequestIdentities(UniqueKey)
OUTPUT inserted.* INTO ...
SELECT @key
EXCEPT
SELECT UniqueKey
FROM   dbo.RequestIdentities
WHERE  UniqueKey = @Key;
  

Aand возвращает предложение OUTPUT result в таблицу вашему клиентскому приложению