SQL Server, Эффективно отбрасывающий группу строк с миллионами и миллионов строк

У меня вопрос, а есть ли проблема иметь сотни тысяч таблиц в вашем SQL Server?

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

Хотя SQL Server теоретически может обрабатывать 2 ³² объектов, будьте уверены, что он начнет деформироваться под нагрузкой гораздо раньше.

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

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

• Написание эффективных запросов (как вы SELECT несколько категорий?)
• Сохранение уникальных идентификаторов (как вы уже обнаружили)
• Поддержание ссылочной целостности (если вам не нравится управлять 300 000 внешних ключей)
• Выполнять обновления с диапазоном значений
• Написать чистый код приложения
• Вести любую историю
• Обеспечьте надлежащую безопасность (кажется очевидным, что пользователи должны иметь возможность инициировать эти создание / удаление - очень опасно)
• Кэширование должным образом - 100 000 таблиц означают, что 100 000 различных планов выполнения, все конкурируют за одну и ту же память, что вы, вероятно, не делаете У меня достаточно;
• Наймите администратора базы данных (будьте уверены, они уйдут, как только увидят вашу базу данных).

С другой стороны, не проблема иметь сотни тысяч строк или даже миллионов строк в одном table - именно так были разработаны SQL Server и другие СУБД SQL, и они очень хорошо оптимизированы для этого случая.

Понижение O (1) для меня крайне желательно. Может быть, есть совершенно другое решение, о котором я не думаю?

Типичное решение проблем производительности в базах данных в порядке предпочтения:

• Запустить профилировщик, чтобы определить, какие части запроса самые медленные;
• ] Если возможно, улучшите запрос (т. Е.путем устранения предикатов, не поддерживающих саргетинг);
• Нормализовать или добавить индексы для устранения этих узких мест;
• Денормализовать при необходимости (обычно не применимо к удалению);
• Если задействованы каскадные ограничения или триггеры, отключите их на время транзакции и продуть каскады вручную.

Но реальность здесь такова, что вам не требуется «решение».

«Миллионы и миллионы строк» ​​- это не так много в базе данных SQL Server. Очень быстро удалить несколько тысяч строк из таблицы миллионов, просто проиндексировав столбец, из которого вы хотите удалить - в данном случае CategoryID . SQL Server может сделать это, не беспокоясь.

Фактически, удаления обычно имеют сложность O (M log N) (N = количество строк, M = количество строк для удаления). Чтобы добиться времени удаления O (1), вам придется пожертвовать почти всеми преимуществами, которые SQL Server предоставляет в первую очередь.

O (M log N) может быть не таким быстрым, как O (1), но замедление, о котором вы говорите (несколько минут на удаление) , должно иметь вторичную причину. Цифры не складываются, и чтобы продемонстрировать это, я пошел дальше и провел тест:

Схема таблицы:

CREATE TABLE Stars
(
    StarID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Stars PRIMARY KEY CLUSTERED,
    CategoryID smallint NOT NULL,
    StarName varchar(200)
)

CREATE INDEX IX_Stars_Category
ON Stars (CategoryID)

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

Пример данных:

Это заполнит таблицу 10 миллионами строк с использованием 500 категорий (т. Е. Количество элементов 1: 20 000 для каждой категории). Вы можете настроить параметры, чтобы изменить объем данных и / или мощность.

SET NOCOUNT ON

DECLARE
    @BatchSize int,
    @BatchNum int,
    @BatchCount int,
    @StatusMsg nvarchar(100)

SET @BatchSize = 1000
SET @BatchCount = 10000
SET @BatchNum = 1

WHILE (@BatchNum <= @BatchCount)
BEGIN
    SET @StatusMsg =
        N'Inserting rows - batch #' + CAST(@BatchNum AS nvarchar(5))
    RAISERROR(@StatusMsg, 0, 1) WITH NOWAIT

    INSERT Stars2 (CategoryID, StarName)
        SELECT
            v.number % 500,
            CAST(RAND() * v.number AS varchar(200))
        FROM master.dbo.spt_values v
        WHERE v.type = 'P'
        AND v.number >= 1
        AND v.number <= @BatchSize

    SET @BatchNum = @BatchNum + 1
END

Скрипт профиля

Самый простой из них ...

DELETE FROM Stars
WHERE CategoryID = 50

Результаты:

Это было протестировано на машине 5-летней давности , работающей, IIRC, 32-битной двойной -ядер AMD Athlon и дешевый SATA-накопитель на 7200 об / мин.

Я запускал тест 10 раз, используя разные CategoryID. Самое медленное время (холодный кеш) было около 5 секунд. Самое быстрое время - 1 секунда.

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

Но мы можем сделать лучше ...

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

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

CREATE TABLE Stars
(
    CategoryID smallint NOT NULL,
    StarName varchar(200)
)

CREATE CLUSTERED INDEX IX_Stars_Category
ON Stars (CategoryID)

Запустите тот же генератор тестовых данных на этом (с ошеломляющим количеством разбиений страниц), и то же удаление заняло в среднем всего 62 миллисекунды и 190 из холодного кеша (выброс). И для справки, если индекс делается некластеризованным (без кластеризованного индекса), то время удаления увеличивается в среднем до 606 мс.

Заключение:

Если вы видите время удаления несколько минут или даже несколько секунд , то что-то очень и очень неправильно .

Возможные факторы:

• Статистика не обновляется (здесь не должно быть проблем, но если это так, просто запустите sp_updatestats );

• Отсутствие индексации (хотя Любопытно, что удаление индекса IX_Stars_Category в первом примере фактически приводит к более быстрому общему удалению, потому что сканирование кластерного индекса выполняется быстрее, чем удаление некластеризованного индекса);

• Неправильно - выбранные типы данных. Если у вас только миллионов строк, в отличие от миллиардов , то вам не нужен bigint на StarID . Вам определенно он не нужен в CategoryID - если у вас меньше 32 768 категорий, вы можете даже использовать smallint . Каждый байт ненужных данных в каждой строке увеличивает стоимость ввода-вывода.

• Конфликт за блокировку.Может быть, проблема вовсе не в скорости удаления; может быть, какой-то другой сценарий или процесс удерживает блокировки на Star строках, а DELETE просто сидит и ждет, пока они отпустят.

• Чрезвычайно плохое оборудование. Мне удалось запустить это без каких-либо проблем на довольно паршивой машине, но если вы запускаете эту базу данных на Presario 90-х годов или на какой-то подобной машине, которая абсурдно не подходит для размещения экземпляра SQL Server, и она сильно загружена , то вы, очевидно, столкнетесь с проблемами.

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

Я, честно говоря, не могу думать ни о каких других возможностях. Удаление в SQL Server только не так уж и медленно .

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

• Советы по оптимизации SQL Server (журнал базы данных)
• Оптимизация SQL Server (MSDN)
• Повышение производительности SQL Server (MSDN)
• Блог группы обработки запросов SQL Server
• Производительность SQL Server (особенно их советы по индексам )

Если это все еще не работает » Если вам не поможет, то я могу предложить следующие дополнительные предложения:

• Обновление до SQL Server 2008, которое дает вам множество вариантов сжатия , которые могут значительно улучшить производительность ввода-вывода;

• Рассмотрите возможность предварительной -сжатие данных по категории Star в компактный сериализованный список (с использованием класса BinaryWriter в .NET) и сохранение его в столбце varbinary . Таким образом, у вас может быть одна строка для каждой категории. Это нарушает правила 1NF, но поскольку вы, похоже, в любом случае ничего не делаете с отдельными данными Star из базы данных, я сомневаюсь, что вы много потеряете.

• Рассмотрите возможность использования нереляционной базы данных или формата хранения, например db4o или Cassandra . Вместо реализации известного анти-шаблона базы данных (печально известного «дампа данных») используйте инструмент, который на самом деле разработан для такого типа хранилища и шаблона доступа.