SQL Server Эффективное удаление группы строк с миллионами и миллионами строк

Ответ 1

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

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

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

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

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

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

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

Капля в 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: 20000 за категорию). Вы можете настроить параметры для изменения объема данных и/или мощности.

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

Профиль Script

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

DELETE FROM Stars
WHERE CategoryID = 50

Результаты:

Это было протестировано на рабочем хосте 5-летней рабочей станции, IIRC, 32-битном двухъядерном AMD Athlon и дешевом диске SATA с частотой 7200 об/мин.

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

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

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

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

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

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

CREATE CLUSTERED INDEX IX_Stars_Category
ON Stars (CategoryID)

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

Вывод:

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

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

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

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

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

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

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

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

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

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

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

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

• Переход на SQL Server 2008, который дает вам множество опций сжатия , которые могут значительно улучшить производительность ввода/вывода;

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

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

Ответ 2

Вы должны удалить их? Часто лучше просто установить столбец бит IsDeleted равным 1, а затем выполнить асинхронное удаление в нерабочее время.

Изменить:

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

Ответ 3

Это был старый метод в SQL 2000, секционированные представления и остается допустимым вариантом для SQL 2005. Проблема возникает из-за наличия большого количества таблиц и связанных с ними расходов на обслуживание.

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

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

Ответ 4

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

Является ли стоимость Enterprise Edition более дорогой, чем стоимость отдельно построенного и поддерживающего схему разделения?

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

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

Ответ 5

Возможно, в таблице Stars установите PK в некластеризованный и добавьте кластерный индекс на categoryid.

Кроме этого, хорошо ли выполняется настройка сервера в отношении наилучшей практики для производительности? Это использует отдельные физические диски для данных и журналов, не используя RAID5 и т.д.

Ответ 6

Когда вы говорите, что удаление миллионов строк "слишком интенсивно для SQL-сервера", что вы имеете в виду? Вы имеете в виду, что файл журнала слишком сильно растет во время удаления?

Все, что вам нужно сделать, это выполнить удаление в партиях фиксированного размера:

DECLARE @i INT
SET @i = 1

WHILE @i > 0
BEGIN
    DELETE TOP 10000 FROM dbo.SuperBigTable
        WHERE CategoryID = 743
    SELECT @i = @@ROWCOUNT
END

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

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

Ответ 7

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

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

Ответ 8

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

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

Я могу предложить следующие предложения:

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

• Попробуйте следующее. После того, как каждый x удаляет (в зависимости от размера), выдает следующий оператор

BACKUP LOG WITH TRUNCATE_ONLY;

Это просто обрезает журнал транзакций, пробел остается для заполнения журнала. Однако я не уверен, сколько времени это добавит к операции.

Ответ 9

Что вы делаете со звездными данными? Если вы смотрите только данные по одной категории в любой момент времени, это может сработать, но ее сложно поддерживать. Каждый раз, когда у вас есть новая категория, вам нужно будет создать новую таблицу. Если вы хотите запросить разные категории, он становится более сложным и, возможно, более дорогим с точки зрения времени. Если вы делаете это и хотите запросить разные категории, представление, вероятно, лучше всего (но не создавайте представления поверх представлений). Если вы ищете данные о конкретной звезде, вы знаете, какую таблицу нужно запросить? Если нет, то как вы собираетесь определять, какая таблица или вы goign, чтобы запросить их все? При вводе данных, как приложение может решить, в какую таблицу помещать данные? Сколько будет категорий? И, кстати, связанные с каждым, имеющим отдельный идентификатор, используют идентификаторы bigint и объединяют личность с типом категории для вашего уникального идентификатора.

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

Вы пытались удалить партии (1000 записей или так за один раз в цикле). Это часто намного быстрее, чем удаление миллиона записей в одном заявлении удаления. Он часто препятствует тому, чтобы таблица блокировалась во время удаления.

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

Учитывая ваши ответы, я думаю, ваше предложение может быть разумным.

Ответ 10

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

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

Звучит для меня, как будто вы используете основные функции запроса SELECT?

Ответ 11

Просто принимая ваше представление о многих таблицах - как вы можете понять, что...

Как использовать динамические запросы.

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

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

Оба эти варианта - это как идеи в мозговом штурме.

Ответ 12

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

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

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

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

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

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

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

Ответ 13

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

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

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

Использовать объект PersistentDictionary и отслеживать пары starid, starname. Если вам нужно удалить категорию, просто удалите объект PersistentDictionary для этой категории.

PersistentDictionary<int, string> starsForCategory = new PersistentDictionary<int, string>("Category1");

Это создаст базу данных под названием "Категория1", на которой вы можете использовать стандартные методы словаря .NET(add, exists, foreach и т.д.).