Skip to content

Неиндексированные внешние ключи могут ухудшить производительность

Пересказ статьи Brent Ozar. Unindexed Foreign Keys Can Make Performance Worse


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

Но их тоже нужно проиндексировать.
Давайте возьмем базу данных 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 оттенков ..., ну вы знаете.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Нет комментариев.

Автор не разрешил комментировать эту запись

Добавить комментарий

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

Form options

Добавленные комментарии должны будут пройти модерацию прежде, чем будут показаны.