Индексы сосут в SQL?
Скажем, у меня есть таблица с большим количеством строк и один из столбцов, которые я хочу проиндексировать, может иметь одно из 20 значений. Если бы я поместил индекс в столбец, был бы он большим?
Если так, то почему? Если бы мне пришлось разделить данные на данные в 20 таблиц, по одной для каждого значения столбца, размер индекса был бы тривиальным, но эффект индексации был бы таким же.
9 ответов
Это не индексы, которые будут сосать. Это помещает индексы в неправильные столбцы, которые будут сосать.
Если серьезно, зачем вам таблица с одним столбцом? Каков будет смысл этих данных? Какой цели это будет служить?
А 20 столов? Я предлагаю вам сначала ознакомиться с дизайном базы данных или иным образом объяснить нам контекст вашего вопроса.
Индексы (или индексы) не отстой. За последние несколько десятилетий многие очень умные люди потратили поистине замечательное количество времени, чтобы убедиться, что это так.
Однако вашей схеме, в которой не хватает такого же количества опыта и усилий, действительно может быть очень плохо.
Секционирование в описанном случае эквивалентно применению кластерного индекса. Если таблица отсортирована иначе (или в произвольном порядке), тогда индекс обязательно должен занимать гораздо больше места. В зависимости от платформы некластеризованный индекс может уменьшаться в размере по мере увеличения сортировки строк относительно индексированного значения.
YMMV.
Короткий ответ: индексы отстой: да и нет
Более длинный ответ: они не сосут при правильном использовании. Возможно, вам стоит начать читать о том, как работают индексы, почему они могут работать и почему они иногда не работают.
Хорошие отправные точки: http://www.sqlservercentral.com/articles/Indexing/
Никакие индексы не отстой, но вы должны обратить внимание на то, как вы их используете, иначе они могут негативно повлиять на производительность ваших запросов.
Первый: схема / дизайн
Зачем вам создавать таблицу только с одним столбцом? Это, вероятно, делает нормализацию на шаг впереди. Проектирование базы данных является одной из самых важных вещей, которые необходимо учитывать при оптимизации производительности.
Второе: индексы
В двух словах, индексы помогут базе данных выполнить двоичный поиск вашей записи. Без индекса по столбцу (или по множеству столбцов) база данных часто возвращается к просмотру таблицы. Сканирование таблицы очень дорого, потому что включает в себя перечисление каждой записи.
Для сканирования индекса не имеет большого значения, сколько записей в таблице базы данных. Из-за (сбалансированного) поиска в двоичном дереве удвоение количества записей приведет только к одному дополнительному шагу поиска.
Определите первичный ключ вашей таблицы, SQL автоматически поместит кластерный индекс в этот столбец (столбцы). Кластерные индексы работают очень хорошо. Кроме того, вы можете размещать некластеризованные индексы в столбцах, которые часто используются в инструкциях SELECT, JOIN, WHERE, GROUP BY и ORDER BY. Помните, что индексы имеют определенное перекрытие, старайтесь никогда не включать ваш кластерный индекс в некластеризованный индекс.
Также интересным может быть коэффициент заполнения индексов. Вы хотите оптимизировать свою таблицу для чтения (высокий коэффициент заполнения - меньше памяти, меньше ввода-вывода) или для записи (низкий коэффициент заполнения - больше памяти, меньше перестроение страниц базы данных).
Третье: разделение
Одной из причин использования секционирования является оптимизация доступа к данным. Допустим, у вас есть 1 миллион записей, из которых 500000 записей больше не актуальны, но хранятся в целях архивирования. В этом случае вы можете разделить таблицу и сохранить 500000 старых записей на медленном хранилище, а остальные 500000 записей - на быстром.
Чтобы измерить, значит знать
Лучший способ понять, что происходит, - это измерить, что происходит с вашим процессором и компьютером. Microsoft SQL Server имеет некоторые инструменты, такие как профилировщик и планы выполнения в Management Studio, которые сообщат вам продолжительность вашего запроса, количество операций чтения / записи и использования процессора. Также план выполнения скажет вам, какие индексы или IF используются. К вашему удивлению вы можете увидеть сканы таблицы, хотя вы этого не ожидали.
Скажем, у меня есть таблица с большим количеством строк и один столбец, который я хочу индексировать, может иметь одно из 20 значений. Если бы я поместил индекс в столбец, был бы он большим?
Размер индекса будет пропорционален количеству ваших строк и длине индексированных значений.
Индекс хранит не только индексированное значение, но и своего рода указатель на строку (ROWID в Oracle, LCID в PostgreSQL, первичный ключ в InnoDB так далее).
Если у вас есть 10,000 строки и 1 отличное значение, вы все равно будете иметь 10,000 записи в вашем индексе.
Если так, то почему? Если бы я разбил данные на 20 таблиц, по одной на каждое значение столбца, размер индекса был бы тривиальным, но эффект индексации был бы таким же
В этом случае вы получите 20 индексов, размер которых будет таким же, как и у исходного.
Этот метод иногда фактически используется в так называемых секционированных индексах. У него есть свои преимущества и недостатки.
Стандартные индексы b-дерева лучше всего подходят для довольно избирательных индексов, чего не было бы в этом примере. Вы не говорите, какую СУБД вы используете; У Oracle есть другой тип индекса, называемый индексом растрового изображения, который больше подходит для индексов с низкой селективностью в средах OLAP (поскольку эти индексы дороги в обслуживании, что делает их неподходящими для сред OLTP).
Оптимизатор решает на основе статистики, считает ли он, что индекс поможет получить данные в кратчайшие сроки; если это не так, optmiser не будет использовать его.
Разделение - это еще одна стратегия. В Oracle вы можете определить таблицу как секционированную по некоторому набору столбцов, и оптимизатор может автоматически выполнить "удаление разделов", как вы предлагаете.
Извините, я не совсем уверен, что вы подразумеваете под "большим".
Если ваш индекс кластеризован, все данные для каждой записи будут находиться на одной и той же конечной странице, создавая тем самым наиболее эффективный индекс, доступный для вашей таблицы, если вы правильно пишете свои запросы к ней.
Если ваш индекс не кластеризован, то на ваших конечных страницах будут находиться только данные, связанные с индексом. Затем, в зависимости от таких вещей, как количество других ваших индексов в сочетании с такими деталями, как ваш коэффициент заполнения, ваш индекс может быть или не быть эффективным. В общем, если у вас нет тонны индексов на вашей таблице, вы должны быть в безопасности.
Эффективность вашего индекса также будет определяться типом данных из 20 значений, о которых вы говорите в столбце. Если это предопределенные значения, то их данные, вероятно, должны быть в справочной таблице с простым типом данных первичного ключа (например, Int/Number). Затем добавьте этот столбец в таблицу в качестве внешнего ключа с индексом в столбце.
В конечном итоге вы можете получить идеальный индекс для столбца. Но его лучшее использование будет определяться по большей части запросами, которые вы пишете. Так что, если ваши запросы используют индексы, вы великолепны.
Он будет достаточно большим, чтобы хранить эти значения для всех строк в отсортированном порядке.
Скажем, у вас есть 20 различных строк по 4 символа и 1 миллион строк, для хранения этих значений потребуется не менее 4 миллионов байтов (или 8, если используется 16-битный юникод).
Индексы предназначены исключительно для производительности. Если индекс не повышает производительность по интересующим вас запросам, то это отстой.
Что касается использования диска, вы должны взвесить ваши проблемы. Различные поставщики SQL строят индексы по-разному, но, как клиент, вы, как правило, уверены, что они делают все возможное, что можно сделать. В случае, если вы описываете, кластеризованный индекс может быть оптимальным как для размера и производительности.