Накладные расходы производительности для одного столбца и нескольких индексов столбцов

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

Если у меня есть 3 одностолбцовых индекса, будет ли это лучше для вставок в эту таблицу, в отличие от 1 индекса с несколькими столбцами.

Для операций, изменяющих данные, один индекс с тремя столбцами должен быть быстрее, чем 3 индекса с одним столбцом, по следующим причинам:

• обновление индекса требует поиска в B-дереве правильного места для выполнения модификации. Выполнение поиска в одном индексе (даже если он составной) имеет тенденцию быть быстрее, чем выполнять 3 поиска в 3 индексах.
• каждый лист B-дерева содержит строку "указатель". 1 индекс будет иметь в 3 раза меньше указателей строк, чем 3 индекса (без учета эффекта NULL, который обычно не индексируется). А меньший размер зачастую быстрее из-за эффектов кэширования.
• 1 индексможет иметь тенденцию к меньшемурасщеплению и коалесцированию узлов B-дерева по сравнению с 3 индексами.

Рассмотрим следующий MS SQL Server тест:

CREATE TABLE ONE_INDEX (
    ID int PRIMARY KEY NONCLUSTERED,
    F1 uniqueidentifier NOT NULL,
    F2 uniqueidentifier NOT NULL,
    F3 uniqueidentifier NOT NULL
);
CREATE INDEX ONE_INDEX_IE1 ON ONE_INDEX (F1, F2, F3);

CREATE TABLE THREE_INDEXES (
    ID int PRIMARY KEY NONCLUSTERED,
    F1 uniqueidentifier NOT NULL,
    F2 uniqueidentifier NOT NULL,
    F3 uniqueidentifier NOT NULL
);
CREATE INDEX THREE_INDEXES_IE1 ON THREE_INDEXES (F1);
CREATE INDEX THREE_INDEXES_IE2 ON THREE_INDEXES (F2);
CREATE INDEX THREE_INDEXES_IE3 ON THREE_INDEXES (F3);
GO



SET NOCOUNT ON
DECLARE @t DATETIME;
DECLARE @id INT;
DECLARE @count INT;
SET @count = 100000;



PRINT 'ONE_INDEX:'

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    INSERT INTO ONE_INDEX VALUES(@id, NEWID(), NEWID(), NEWID());
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    INSERT ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    UPDATE ONE_INDEX SET F1 = NEWID(), F2 = NEWID(), F3 = NEWID() WHERE ID = @id
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    UPDATE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
DELETE FROM ONE_INDEX;
PRINT '    DELETE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';



PRINT 'THREE_INDEXES:'

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    INSERT INTO THREE_INDEXES VALUES(@id, NEWID(), NEWID(), NEWID());
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    INSERT ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    UPDATE THREE_INDEXES SET F1 = NEWID(), F2 = NEWID(), F3 = NEWID() WHERE ID = @id
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    UPDATE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
DELETE FROM THREE_INDEXES;
PRINT '    DELETE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';
GO



DROP TABLE ONE_INDEX;
DROP TABLE THREE_INDEXES;
GO

Который (на моем MS SQL Server Express 2008 R2 под виртуальной машиной) печатает:

ONE_INDEX:
    INSERT 100000 rows: 4173 ms
    UPDATE 100000 rows: 5530 ms
    DELETE 100000 rows: 2706 ms
THREE_INDEXES:
    INSERT 100000 rows: 6640 ms
    UPDATE 100000 rows: 10436 ms
    DELETE 100000 rows: 3516 ms

Увеличение @count до 1000000 приводит к:

ONE_INDEX:
    INSERT 1000000 rows: 40143 ms
    UPDATE 1000000 rows: 55796 ms
    DELETE 1000000 rows: 95576 ms
THREE_INDEXES:
    INSERT 1000000 rows: 61360 ms
    UPDATE 1000000 rows: 91766 ms
    DELETE 1000000 rows: 99500 ms

---

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

Только Вы можете выполнить соответствующие тесты и достичь правильного баланса между всеми этими проблемами.

Имейте в виду, что составной индекс, состоящий из 3 столбцов, таких как Фамилия, Имя и город, не полезен, когда вам нужно искать по городу. Так что в этом случае вам нужно будет иметь индекс только для города.