После своего поста, рассказывающего о том, как можно оптимизировать подсчёт строк в таблице, мне несколько раз задавали вопрос о том, как работает COUNT. И теперь, я хочу поделиться этой ценной информацией.
Функция подсчёта строк, всегда пробегает по всем записям таблицы, т.е. последовательно их перебирает в памяти (если не используется условие WHERE). С этим связан довольно распространённый миф: “COUNT(имя_поля) лучше использовать чем COUNT(*), т.к. мы явно указываем по какому полю делать перебор, и в этом случае оптимизатор будет использовать индекс”. На самом деле это не так. Если для таблицы созданы индексы, то перебирается листовой уровень индекса с наименьшим размером ключа, таким образом оптимизатор сам выбирает наиболее оптимальный план выполнения, в независимости от того, что вы указываете в условии COUNT. В этом можно убедиться, запустив пару скриптов.
Вначале создадим тестовую таблицу, заполним её данными и создадим необходимые для теста индексы (если вставить в таблицу меньшее количество записей (например 100), то использоваться будет IDX_BIG (думаю в этом случае оптимизатору просто не хватает информации)):
CREATE TABLE dbo.Test
(Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, SmallField tinyint NOT NULL, BigField bigint NOT NULL)
GO
CREATE INDEX IDX_SMALL ON dbo.Test(SmallField)
GO
CREATE INDEX IDX_BIG ON dbo.Test(BigField)
GO
INSERT INTO dbo.Test VALUES(1, 2)
GO 1000
И выполним инструкции COUNT в различном формате:
SELECT COUNT(*) FROM dbo.Test
GO
SELECT COUNT(BigField) FROM dbo.Test
GO
SELECT COUNT(1) FROM dbo.Test
GO
Из планов выполнения видно, что в любом случае используется наименьший индекс IDX_SMALL:
Если удалить IDX_SMALL, то будет сканироваться IDX_BIG, т.к. в этом случае он станет самым маленьким. Если же удалить и его, будет сканироваться первичный ключ таблицы. В данном примере показан пример полного сканирования таблицы. Если у вас есть конкретное условие WHERE или поля NULL, то в запросе будет использоваться индекс указанного в запросе поля.
PS: Думаю выкладывать скрипты, планы выполнения, и другие материалы используемые в постах, в свой Sky Drive - http://xtourl.com/22et
Ссылки по теме:
- Блог Пола Рэндала
- Блог Аарона Альтона
- Функция COUNT
- Альтернатива COUNT(*)
- Материалы поста