SQL Server 2016: производительность запросов с присоединением и без присоединения
У меня есть 2 таблицы TABLE1 и TABLE2.
- TABLE1 имеет столбцы masterId, Id, col1, col2, category
- TABLE2 имеет столбцы Id, col1, col2
TABLE2.Id — это первичный ключ, а TABLE1.Id — внешний ключ.
TABLE1.masterId — это первичный ключ TABLE1.
TABLE1 имеет 10 миллионов строк с идентификатором от 1 до 10 миллионов, а первые 10 строк имеют категорию = 1.
TABLE2 имеет только 10 строк с идентификаторами от 1 до 10.
Теперь мне нужны значения col1 и col2 с категорией = 1 (либо из TABLE1, либо из TABLE2, потому что значения одинаковы в обеих таблицах)
Какой из двух запросов быстрее выдает результат?
Решение1:
SELECT T1.col1, T1.col2
FROM TABLE1 T1
WHERE T1.category = 1
Решение2:
SELECT T2.col1, T2.col2
FROM TABLE2 T2
INNER JOIN TABLE1 T1 ON T1.Id = T2.Id
WHERE T1.category = 1
Экономит ли Solution2 время сканирования таблицы на миллионах строк TABLE1.
Ограничение: В моем реальном сценарии БД я могу сделать Table1.Id некластеризованным индексом, а Table1.category также некластеризованным индексом. Я не могу сделать Table1.Id кластеризованным индексом, потому что в реальном сценарии у меня есть еще один столбец с автоинкрементом в качестве первичного ключа в моей таблице Table1. Поэтому, пожалуйста, поделитесь своими мыслями об этом ограничении.
Пожалуйста, подтвердите и поделитесь мыслями по этому поводу.
Извините, я использую Ms SqlServer 2016.
Несколько необычно позволять одному запросу определять структуру вашей схемы. Если есть смысл иметь две таблицы, используйте этот дизайн и вместо этого сосредоточьтесь на ускорении второго запроса с помощью таких вещей, как индексы.
Хранение одних и тех же данных в двух разных таблицах (почти) никогда не бывает хорошей идеей.
«Какой из двух запросов быстрее выдает результат?» Это можно проверить на вашей системе. Как кто-нибудь может знать, какой из двух быстрее, если у нас нет данных?
Да, но в моем сценарии нужна главная таблица, скажем, с 10 строками, а дочерняя таблица имеет множество наборов из этих 10 строк с другой категорией. В моей реальной БД нет столбца категорий в основной таблице, я включил его сюда для ясности вопроса.
Мое намерение состоит в том, чтобы узнать, сокращает ли внутреннее соединение время сканирования таблицы в TABLE1, которая содержит миллионы записей.
Ответы 3
Это зависит от существующих индексов. С некластеризованным индексом по идентификатору в T1 решение 2 может работать лучше, чем решение 1, которое потребует полного сканирования таблицы для выбора строк с категорией1. Если вместо этого у нас также есть некластеризованный индекс для категории, то решение 1 будет быстрее, поскольку для поиска строк потребуется только поиск некластеризованного индекса.
Без какого-либо индекса по Id на T1 потребуется полное сканирование, чтобы найти строку T2.Id, поэтому может быть 10 полных просмотров T1 для решения 2 и 1 полное сканирование T1.Category для решения 1, поэтому решение 1 может быть быстрее. Но это зависит от оптимизатора запросов, и лучше всего проверить реальный случай, чтобы увидеть, каковы фактические планы выполнения.
Но правильный путь — реализовать правильную модель, а затем приступить к созданию индексов, необходимых для быстрого выполнения запроса.
Обновлено: адаптированный ответ в соответствии с изменениями запроса. Edit2: покрытие индекса будет дорогим, а поиск 10 индексов в PK в таблице 1 не будет стоить так дорого.
Но в моем реальном сценарии БД я могу сделать Table1.Id некластеризованным индексом, а Table1.category также некластеризованным индексом. Я не могу сделать Table1.Id кластеризованным индексом, потому что в реальном сценарии у меня есть еще один столбец с автоинкрементом в качестве первичного ключа в моей таблице Table1. Поэтому, пожалуйста, поделитесь своими мыслями об этом ограничении.
Теперь я включил первичный ключ для TABLE1, чтобы было понятно. Также исправлено мое внутреннее условие соединения.
Пожалуйста, проверьте 2 запроса сейчас
@Mahen, индекс первичного ключа может быть кластеризованным или некластеризованным. Индекс PK кластеризован по умолчанию, если таблица уже не имеет кластеризованного индекса. Рассмотрите все запросы в вашей рабочей нагрузке, чтобы выбрать наиболее подходящий кластеризованный индекс. Есть компромиссы.
@DanGuzman Конечно. Поскольку моим первичным ключом является столбец идентификаторов, он станет кластеризованным. Поэтому мне придется сделать Table1.category некластеризованным индексом, а Table1.Id также некластеризованным индексом. Итак, как сказал сергиом, решение 1 было бы для меня предпочтительнее.
@Mahen, личность или нет, индекс ПК не должен быть кластеризован. Свойства физического индекса не зависят от налагаемого им ограничения. Я не утверждаю, что кластеризация — лучший выбор, поскольку она зависит от многих других факторов.
Ой. Но в комментарии Гордона Линоффа в другом ответе ниже говорится, что в MS SqlServer Identity PrimaryKey будет Clustered Index. Я должен буду подтвердить это.
@ Дэн Гузман, ты прав. Я проверил это в MS SQL Server 2016. То, что говорит Гордон, остается в силе, если я только попытаюсь сделать это с помощью SSMS. Но, написав запрос «Создать таблицу» вручную, я могу создать первичный ключ с некластеризованным индексом в столбце «Идентификация», который автоматически увеличивается. И я могу создать еще один столбец как кластеризованный индекс. Так что это ошибка в SSMS.
[Уведомление]
Этот ответ был дан для более старой версии вопроса, https://stackoverflow.com/revisions/65263530/7
Сценарий тогда был такой:
- В T2 также был столбец
category, и, - второй запрос был:
SELECT T2.col1, T2.col2
FROM TABLE2 T2
INNER JOIN TABLE1 T1 ON T1.categoryId = T2.category Id
WHERE T2.category = 1
Предполагая, что единственными индексами являются ПК, нет, Решение 2 НЕ позволит избежать сканирования таблицы. Худший:
Решение 1 Полное сканирование таблицы
Решение 2 Полное сканирование таблицы на T2 (T2.category), а затем вложенные циклы (T2.category = T1.category)
Пожалуйста, каковы ваши цели здесь?
Извините, исправлено Solution2, где условие: INNER JOIN TABLE1 T1 ON T1.Id = T2.Id
Пожалуйста, проверьте 2 запроса сейчас
Начнем с того, что это утверждение показывает отсутствие понимания баз данных:
первые 10 строк с категорией = 1
Таблицы SQL представляют собой неупорядоченные наборы. Не существует такого понятия, как «первые 10 строк». В контексте вашего вопроса, я думаю, вы имеете в виду «10 строк с наименьшими значениями id». Однако порядок таблицы по-прежнему произволен с точки зрения движка. Бывают ситуации, когда кластеризованный индекс можно разумно принять за "упорядочивание таблиц", но никогда нет гарантии, что:
select *
from t;
возвращает данные в определенном порядке даже с кластеризованным индексом.
Два возможных плана выполнения для первого запроса — в зависимости от индексации — таковы:
- Сканирование таблицы (т.е. чтение миллионов строк) и выполнение теста для каждой строки.
- Сканирование индекса на
categoryи выборка необходимых строк.
В общем, (1) будет намного медленнее, чем (2), когда отсканированные строки исчисляются миллионами, а возвращаемых строк всего несколько. Однако, если это может быть не так, если значительная часть всех записей была возвращена.
Я интерпретирую ваш вопрос как вопрос о том, может ли второй запрос быть быстрее первого:
SELECT T2.col1, T2.col2
FROM TABLE2 T2 INNER JOIN
TABLE1 T1
ON T1.Id = T2.Id
WHERE T1.category = 1;
Ответ «определенно быстрее, чем сканирование». Это возможно, если у вас есть индекс на Table1(id, category). Однако запрос лучше написать с помощью EXISTS:
select t2.*
from table2 t2
where exists (select 1
from table1 t1
where t1.id = t2.id and t2.category = 1
);
Я ожидаю, что это будет быстрее, чем индексированная версия первого запроса. Даже с индексом (category) база данных все равно должна получить данные для select. Если данные находятся на одной странице (как можно предположить из «первого» утверждения), то они могут быть вполне сопоставимы. Однако было бы сложно измерить разницу в производительности при правильной индексации table1.
Примечание о кластерных индексах в SQL Server. Если id является первичным ключом identity и нет другого кластеризованного индекса, то он автоматически используется в качестве кластеризованного индекса.
Да, сказав первые 10 строк, я просто хотел передать первые 10 строк, вставленных из таблицы 2 в таблицу 1, с категорией = 1 и автоматическим увеличением первичного ключа masterId
@ Если у вас есть первичный ключ identity в SQL Server, то это кластерный индекс. Это может сделать второй подход немного быстрее, чем первый, даже с индексом category, но разница в любом случае будет очень небольшой.
Хорошо Я определенно предпочту идентичность PrimaryKey вместо создания уникального первичного ключа для каждой вставки. И после некластеризованного индекса в столбце «Категория», я понимаю, как вы сказали, Solution2 будет немного быстрее, но разница будет очень небольшой. Поскольку разница очень мала, я оставлю Solution1, который уже существует, и уже имеет около 6 соединений с другими таблицами. Просто полагаясь на некластеризованный индекс по категории, который позволит избежать полного сканирования таблицы в таблице 1 с огромными данными. Надеюсь, моя мысль верна.
Я проверил это в MS SQL Server 2016. То, что вы сказали, остается в силе, если я попытаюсь сделать это только с SSMS. Но, написав запрос «Создать таблицу» вручную, я могу создать первичный ключ с некластеризованным индексом в столбце «Идентификация», который автоматически увеличивается. И я могу создать еще один столбец как кластеризованный индекс. Так что это ошибка в SSMS.
Таким образом, при таком понимании я предпочту сделать Table1.masterId столбцом Identity и первичным ключом с некластеризованным индексом. И сделайте Table1.category кластеризованным индексом. Надеюсь, это даст гораздо лучшую производительность по сравнению с некластеризованной категорией.
@Махен. . . Я не уверен, что вы имеете в виду в своем предыдущем комментарии. Первичный ключ удостоверения является кластеризованным индексом, когда не определен никакой другой кластеризованный индекс. Вы можете создать вторичный индекс для любых столбцов, включая те, которые используются для первичного ключа.
Да, первичный ключ идентификации по умолчанию является кластеризованным, но я имею в виду, что кластеризация не является обязательной. Я также могу создать его как NonClustered, если я создаю таблицу с написанным вручную запросом, но не с SSMS.
Какую базу данных вы используете? Ваши отмеченные
sql-serverиplsql, которые противоречат друг другу