SQL-Ex blog

Давайте возьмем базу данных Stack Overflow - я использую 50Гб базу версии Stack Overflow 2013 для этой статьи. Я собираюсь начать вообще без некластеризованных индексов, а затем собираюсь добавить внешние ключи между таблицей Users и несколькими таблицами, куда пользователи добавляют контент, например, Badges, Comments, Posts и Votes:

/* Создаем внешние ключи: */

ALTER TABLE dbo.Badges WITH NOCHECK

    ADD CONSTRAINT fk_badges_users_id FOREIGN KEY (UserId) 

        REFERENCES dbo.Users(Id);

GO

ALTER TABLE dbo.Comments WITH NOCHECK

    ADD CONSTRAINT fk_comments_users_id FOREIGN KEY (UserId) 

        REFERENCES dbo.Users(Id);

GO

ALTER TABLE dbo.Posts WITH NOCHECK

    ADD CONSTRAINT fk_posts_users_id FOREIGN KEY (OwnerUserId) 

        REFERENCES dbo.Users(Id);

GO

ALTER TABLE dbo.Votes WITH NOCHECK

    ADD CONSTRAINT fk_votes_users_id FOREIGN KEY (UserId) 

        REFERENCES dbo.Users(Id);

GO

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

/* Подготовительная работа, чтобы удалить строки во всех дочерних таблицах: */

DELETE dbo.Badges WHERE UserId = 26837;

DELETE dbo.Comments WHERE UserId = 26837;

DELETE dbo.Posts WHERE OwnerUserId = 26837;

DELETE dbo.Votes WHERE UserId = 26837;

GO

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

DELETE dbo.Users WHERE Id = 26837;

GO

Этот оператор просто удаляет одну строку, правильно? Насколько это может быть плохо?



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



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

CREATE INDEX UserId ON dbo.Badges(UserId);

CREATE INDEX UserId ON dbo.Comments(UserId);

CREATE INDEX OwnerUserId ON dbo.Posts(OwnerUserId);

CREATE INDEX UserId ON dbo.Votes(UserId);

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

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

• Поставить удаления в очередь - в этом примере Stack Overflow приложение, скажем, не удаляет непосредственно строки пользователей, а вместо этого добавляет строки в таблицу очередей для более поздней обработки. Во время окна техобслуживания мы могли бы сделать пакетное удаление, удаляя группы пользователей по таблицам за один проход.


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

Хотел бы я иметь базы данных с идеальными данными, совершенными внешними ключами, индексами и таким оборудованием, которое выдерживало бы любые нагрузки? Абсолютно. Но в реальной жизни ограничения не белые и не черные, они имеют 50 оттенков ..., ну вы знаете.