Должен ли я индексировать битовое поле в SQL Server?

Question

Должен ли я индексировать битовое поле в SQL Server?

Я помню, что читал в какой-то момент, что индексирование поля с низкой мощностью (низкое количество различных значений) на самом деле не стоит делать. Я признаю, что не знаю достаточно о том, как работают индексы, чтобы понять, почему это так.

Ну и что, если у меня есть таблица со 100 миллионами строк в ней, и я выбираю записи, где битовое поле равно 1? И скажем, что в любой момент времени, есть только несколько записей, где битовое поле равно 1 (а не 0). Стоит ли индексировать это битовое поле или нет? Зачем?

конечно, я могу просто проверить его и проверить план выполнения, и я сделаю это, но мне также интересно узнать о теории, стоящей за ним. Когда кардинальность имеет значение, а когда нет?

799 19

sql-server indexing

19 ответов:

Comments

Ничего не найдено.

Geoff Cox · Accepted Answer · 2013-07-10 22:48:40

рассмотрим, что такое индекс в SQL-а индекс-это действительно кусок памяти, указывающий на другие куски памяти (т. е. указатели на строки). Индекс разбивается на страницы так, чтобы части индекса могут быть загружены и выгружены из памяти в зависимости от использования.

когда вы просите набор строк, SQL использует индекс, чтобы найти строки быстрее, чем сканирование таблицы (Глядя на каждую строку).

SQL имеет кластеризованные и некластеризованные индексы. Мое понимание кластеризации индексы-это то, что они группируют одинаковые значения индекса на одной странице. Таким образом, когда вы запрашиваете все строки, соответствующие значению индекса, SQL может возвращать эти строки из кластеризованной страницы памяти. Вот почему попытка кластеризации индекса столбца GUID является плохой идеей-вы не пытаетесь кластеризировать случайные значения.

при индексировании целочисленного столбца индекс SQL содержит набор строк для каждого значения Индекса. Если у вас есть диапазон от 1 до 10, то у вас будет 10 указателей индекса. В зависимости от способа многие строки есть это может быть по-разному постранично. Если ваш запрос ищет индекс, соответствующий "1", а затем где имя содержит" Fred "(предполагая, что столбец Name не индексируется), SQL очень быстро получает набор строк, соответствующих" 1", а затем сканирует таблицу, чтобы найти остальные.

Так что SQL действительно пытается уменьшить рабочий набор (количество строк), который он должен перебирать.

когда вы индексируете битовое поле (или некоторый узкий диапазон), вы только уменьшаете рабочий набор количество строк, соответствующих этому значению. Если у вас есть небольшое количество совпадающих строк, это значительно уменьшит ваш рабочий набор. Для большого количества строк с распределением 50/50, это может купить вам очень мало прироста производительности против поддержания индекса в актуальном состоянии.

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

Ben Thul · Accepted Answer · 2014-06-12 00:11:57

Я просто наткнулся на этот вопрос по другому. Предполагая, что ваше утверждение о том, что только несколько записей принимают значение 1 (и что это те, которые вас интересуют), тогда отфильтрованный индекс может быть хорошим выбором. Что-то вроде:
create index [IX_foobar] on dbo.Foobar (FooID) where yourBitColumn = 1
Это создаст значительно меньше индекса, оптимизатор достаточно умен, чтобы использовать, когда это предикат в запросе.

C. Dragon 76 · Accepted Answer · 2008-10-23 23:58:56

100 миллионов записей только с несколькими битными полями, установленными в 1? Да, я бы подумал, что индексирование битового поля определенно ускорит запрос записей bit=1. Вы должны получить логарифмическое время поиска из индекса, а затем только коснуться нескольких страниц с записями bit=1. В противном случае, вам придется коснуться всех страниц 100 миллионов записей в таблице.

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

BradC · Accepted Answer · 2008-10-23 22:54:36

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

простой пример-это индекс ACTIVE, LASTNAME вместо просто lastname, когда ваше приложение почти всегда ищет активных клиентов.

Jeff · Accepted Answer · 2014-05-23 01:14:42

в случае, если вы не читали его, Джейсон Мэсси недавно написал статью, в которой обсуждалась эта самая тема.

http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/302/Never-Index-a-BIT.aspx

изменить: новое расположение статьи -http://sqlserverpedia.com/blog/sql-server-bloggers/never-index-a-bit

машина обратного хода для ранее "новой" статьи местоположение: http://web.archive.org/web/20120201122503/http://sqlserverpedia.com/blog/sql-server-bloggers/never-index-a-bit/

новое расположение SQL Server Pedia-Toadworld, в котором есть новая статья от Кеннета Фишера, обсуждающая эту тему:

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/02/17/dba-myths-an-index-on-a-bit-column-will-never-be-used.aspx

Philippe Boucher · Accepted Answer · 2015-05-21 22:15:07

Если ваше распределение довольно известно и несбалансировано, например, 99% строк bit = 1 и 1% bit = 0, когда вы делаете предложение WHERE с bit = 1, полное сканирование таблицы будет примерно в то же время, что и сканирование индекса. Если вы хотите иметь быстрый запрос, где bit = 0, лучший способ, который я знаю, это создать отфильтрованный индекс, добавив предложение, где bit = 0. Таким образом, этот индекс будет хранить только строку 1%. Затем выполнение WHERE bit = 0 просто позволит оптимизатору запросов выбрать этот индекс, и все строки из него будут бит = 0. У вас также есть преимущество, чтобы иметь очень небольшой объем дискового пространства, необходимого для сравнения полного индекса на бит.

Bogdan Maxim · Accepted Answer · 2008-10-23 22:42:35

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

теперь с SQL 2008 Вы можете использовать функции секционирования, и вы можете фильтровать данные, которые входят в индекс. Недостатком для более ранних версий было бы то, что индекс был бы сделан для всех данных, но это может быть оптимизировано путем хранения интересных значений в отдельной группе файлов.

Jeremiah Peschka · Accepted Answer · 2008-10-23 22:47:18

Как говорили другие, вы захотите измерить это. Я не помню, где я это читал, но столбец должен иметь очень высокую мощность (около 95%), чтобы индекс был эффективным. Лучшим тестом для этого было бы построить индекс и изучить планы выполнения для значений 0 и 1 битового поля. Если вы видите операцию поиска по индексу в плане выполнения, то вы знаете, что ваш индекс будет использоваться.

ваш лучший курс действий будет проверить с основные выберите * из таблицы, где BitField = 1; запрос и медленно построить функциональность оттуда шаг за шагом, пока у вас есть реалистичный запрос для вашего приложения, изучая план выполнения с каждым шагом, чтобы убедиться, что поиск индекса все еще используется. Правда, нет никакой гарантии, что этот план выполнения будет использоваться в производстве, но есть хороший шанс, что это будет.

некоторая информация может быть найдена на sql-server-performance.com форумы и в указанной статьи

DJ. · Accepted Answer · 2008-10-23 23:07:12

"Я помню, что читал в какой-то момент, что индексирование поля с низкой мощностью (низкое количество различных значений) на самом деле не стоит делать"

Это потому, что SQL Server почти всегда найдет более эффективным просто выполнить сканирование таблицы, чем читать индекс. Так что в основном ваш индекс никогда не будет использоваться, и это пустая трата для его поддержания. Как говорили другие, это может быть нормально в составном индексе.

score 2 · Accepted Answer · 2008-10-24 00:41:03

Если ваша цель состоит в том, чтобы сделать запрос для записей, где значение битового поля равно '1' быстрее, вы можете попробовать индексированное представление вашей базовой таблицы, которая содержит только записи, где ваше битовое поле равно '1'. В enterprise edition, Если запрос может использовать индексированное представление вместо указанной таблицы для повышения производительности запроса, он будет использовать представление. Теоретически это увеличило бы скорость запросов select, которые ищут только записи со значением битового поля '1'.

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

все это предполагает, что вы Microsoft SQL Server 2005 Enterprise. То же самое может относиться к 2008 году, я не знаком с этой версией.

Craig Nicholson · Accepted Answer · 2008-10-23 23:31:05

сам по себе, нет, поскольку это приводит к очень малой селективности. Как часть составного индекса. вполне возможно, но только после других столбцов равенства.

thijs · Accepted Answer · 2008-10-25 10:51:58

Если вы хотите знать, если индекс имеет желаемые эффекты: тест и еще раз тест.

В общем случае вы не хотите индекс, который не сужает вашу таблицу достаточно, из-за стоимости для поддержания индекса. (затраты > прибыль). Но если индекс в вашем случае сократит таблицу пополам, вы можете получить что-то, но положить его на стол. Все зависит от точного размера / структуры вашей таблицы и того, как вы ее используете (количество операций чтения/записи).

Ian Boyd · Accepted Answer · 2010-02-10 14:35:02

вы Не могу индексируйте битовое поле в SQL Server 2000, как было указано в электронной документации в то время:

немного

целочисленный тип данных 1, 0 или NULL.

Примечания

столбцы типа немного не может есть индексы на них.

да, если у вас есть только несколько строк, из миллионов, индекса поможет. Но если вы хотите это сделать в этом случае вам нужно сделать столбец a tinyint.

Примечание: Enterprise Manager не позволит вам создать индекс для битового столбца. Если вы хотите, вы все еще можете вручную создать индекс на столбец:
CREATE INDEX IX_Users_IsActiveUsername ON Users
(
   IsActive,
   Username
)
но SQL Server 2000 фактически не будет использовать такой индекс-запуск запроса, где индекс будет идеальным кандидатом, например:
SELECT TOP 1 Username 
FROM Users
WHERE IsActive = 0
SQL Server 2000 будет выполнять сканирование таблицы вместо этого, действуя так, как будто индекс даже не существовать. Если вы измените столбец на tinyint SQL Server 2000 будет выполните поиск индекса. Кроме того, следующий непокрытый запрос:
SELECT TOP 1 * 
FROM Users
WHERE IsActive = 0
он будет выполнять поиск индекса, а затем поиск закладки.

SQL Server 2005 имеет ограниченную поддержку индексов для битовых столбцов. Например:
SELECT TOP 1 Username 
FROM Users
WHERE IsActive = 0
вызовет поиск индекса через индекс покрытия. Но не покрытый случай:
SELECT TOP 1 * 
FROM Users
WHERE IsActive = 0
не вызовет индекс поиск с последующим поиском закладки, он будет выполнять сканирование таблицы (или кластеризованное сканирование индекса), а не выполнять поиск индекса с последующим поиском закладки.

проверено экспериментально и прямым наблюдением.

gbn · Accepted Answer · 2014-02-05 16:46:58

очень поздний ответ...

Да, это может быть полезно в соответствии с командой SQL CAT (обновлено, консолидировано)

jason saldo · Accepted Answer · 2008-10-23 22:38:43

Это общий запрос? Это может быть стоит при поиске "горстки" записей, но не поможет вам много на других строках. Существуют ли другие способы идентификации данных?

Anthony Potts · Accepted Answer · 2008-10-23 23:13:22

мощность-это один фактор, другой-насколько хорошо индекс разделяет ваши данные. Если у вас есть примерно половина 1 и половина 0, то это поможет. (Предполагая, что этот индекс является лучшим путем для выбора, чем какой-либо другой индекс). Однако, как часто вы вставляете и обновляете? Добавление индексов для SELECT performance также повредит вставке, обновлению и удалению производительности, поэтому имейте это в виду.

Я бы сказал, Если от 1 до 0 (или наоборот) не лучше, чем 75% до 25%, не надо беспокоить.

Steven A. Lowe · Accepted Answer · 2008-10-26 17:54:36

мера время ответа до и после и посмотреть, если это стоит; теоретически это должно улучшить производительность для запросов с использованием индексированных полей, но это действительно зависит от распределения истинных / ложных значений и других полей, участвующих в запросах, которые вы беспокоитесь о

John B · Accepted Answer · 2010-02-10 01:49:09

Ян Бойд прав, когда говорит, что вы не можете сделать это через Enterprise Manager for SQL 2000 (см. Его примечание о создании его через T-SQL.

Chetan Verma · Accepted Answer · 2017-04-01 15:54:33

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