Неиндексированные внешние ключи могут ухудшить производительность
Пересказ статьи Brent Ozar. Unindexed Foreign Keys Can Make Performance Worse
Я знаю, дорогой читатель, что вы зашли сюда, возмутившись увиденным заголовком. Вы хотите иметь сконфигурированные связи по внешнему ключу во всех ваших таблицах для предотвращения появления в них ошибочных данных.
Но их тоже нужно проиндексировать.
Давайте возьмем базу данных Stack Overflow - я использую 50Гб базу версии Stack Overflow 2013 для этой статьи. Я собираюсь начать вообще без некластеризованных индексов, а затем собираюсь добавить внешние ключи между таблицей Users и несколькими таблицами, куда пользователи добавляют контент, например, Badges, Comments, Posts и Votes:
Теперь, скажем, мое приложение хочет удалить конкретного пользователя, id # 26837. Сначала приложение начинает удаление всех строк этого пользователя из контентных таблиц:
При этом выполняется сканирование таблиц, но мы считаем это допустимым по той причине, что наше приложение редко выполняет удаления. Теперь, после обработки всех контентных таблиц, давайте вернемся и удалим строку пользователя:
Этот оператор просто удаляет одну строку, правильно? Насколько это может быть плохо?
Ужасно, чем это все обернулось. SQL Server хочет выполнить двойную проверку всех контентных таблиц, чтобы убедиться в том, что пользователь 26837 не имеет никаких наград, комментариев, постов или просмотров. Мы делаем также тонну логических чтений:
Первое решение, о котором вы, вероятно, подумали: проиндексировать все столбцы внешних ключей:
Это ускоряет удаление, однако имеет некоторые накладные расходы: более медленные операции вставки и обновления за счет обслуживания этих индексов, на 3% увеличение размера базы данных, на 3% дольше административные работы и т.д.
А недавно мы просто не смогли использовать это решение. Заказчики уже имели серьезные проблемы с производительностью при вставке: их оборудование не могло справиться с дополнительными замедлением при вставке и обновлении, и они уже имели проблемы с обширными блокировками и записью. Другие варианты решения:
Хотел бы я иметь базы данных с идеальными данными, совершенными внешними ключами, индексами и таким оборудованием, которое выдерживало бы любые нагрузки? Абсолютно. Но в реальной жизни ограничения не белые и не черные, они имеют 50 оттенков ..., ну вы знаете.
/* Создаем внешние ключи: */
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 оттенков ..., ну вы знаете.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой