Как стать автором
Поиск
Написать публикацию
Обновить

Комментарии 44

Disclaimer: да, я понимаю что это перевод, к переводчику претензий нет :)

По содержанию — совсем для начинающих имеет смысл либо более сфокусированное изложение на отдельных деталях (чтобы было понимание «почему», а не «освой индексы за 21 минуту»), либо тезисы с отсылкой на отдельные статьи.
Для людей с опытом недостаточно качественно (лучше sqlskills почитать), см. пример ниже.

In some cases, a surrogate primary key can be an even better choice because, in addition to being unique, the values are small and added sequentially, making the nonclustered indexes that reference those values more efficient as well.


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


Сомнительно выглядит выделенный текст — последовательно увеличивающиеся значения полезны тем, что не приводят к расщеплению страниц, как они влияют на эффективность некластерных индексов непонятно.
Допускаю, что я могу чего-то не знать, однако, в этом случае подобное точно стоило бы объяснить.

P.S. Есть ещё спорные моменты, однако не хочется начинать холивар :)
Всего голосов 1: ↑1 и ↓0+1
Все правильно написано (почти). Для кластерного ключа (ключа, по которому строится кластерный индекс) идеально подходит identity:
1) Меньше page splits — больше производительность вставки данных
2) Некластерные индексы в листах хранят кластерные ключи — чем меньше кластерный ключ, тем меньше занимают индексы
3) Чем меньше размер ключа, тем выше производительность соединений

Не написано только, что кластерный ключ вовсе не обязан быть первичным ключом таблицы, из-за чего обычно и возникает холивар natural key vs surrogate key.
Всего голосов 4: ↑4 и ↓0+4
А вы не подскажите почему все(ну может так получилось) относительно новые системы которые я видел кластерный индекс строят через uniqueidentifier? А в старых системах int identity.Т.е. как раз таки больше цениться ветвление, а не скорость изменения ключа.
Всего голосов 1: ↑1 и ↓0+1
Есть несколько факторов:
1) int identity в качестве первичного ключа удобен для относительно небольших простых баз (до нескольких сотен ГБ), но создает грабли для offline режима работы, который сейчас очень популярен из-за мобильных приложений. Такие же проблемы возникают при прикладной репликации СУБД в распределенных системах.
2) архитектура приложения, когда кластерный ключ не соответствует первичному ключу таблицы, крайне сложна для разработчиков. Ни один ORM, например, не поддерживает такую архитектуру.
3) заметные преимущества разных ключей проявляются в сценариях хранилища данных, когда объем данных и интенсивность записи очень высоки.
4) большинство программистов крайне слабо разбираются в физическом дизайне базы и вопроах быстродействия (увы)
5) Немалый процент разработчиков ведется на холивары о «вредности» int identity (это по сути следствие п4)

В итоге uniqueidentifier в качестве первичного и кластерного ключа для многих это «нормальный» выбор, так как позволяет снизить риски архитектуры и не сильно уронить быстродействие.
Всего голосов 3: ↑3 и ↓0+3
2) архитектура приложения, когда кластерный ключ не соответствует первичному ключу таблицы, крайне сложна для разработчиков. Ни один ORM, например, не поддерживает такую архитектуру.
Не совсем так. ORM ничего не знает про кластерные индексы, поскольку работает уровнем выше. Но при этом запросы от ORM зачастую используют именно первичный ключ — а потому делать кластерный индекс отличным от первичного ключа попросту неоптимально.
Всего голосов 2: ↑2 и ↓0+2
ORM знает как мапить reference свойства на запросы. Только когда ссылка идет не на первичный ключ, то не все ORM это понимают. К уровню абстракции отношения не имеет.
Всего голосов 2: ↑1 и ↓10
Как соотносятся маппинг reference свойств и кластерный индекс?
Всего голосов 1: ↑1 и ↓0+1
В итоге uniqueidentifier в качестве первичного и кластерного ключа для многих это «нормальный» выбор, так как позволяет снизить риски архитектуры и не сильно уронить быстродействие.


Если поле uniqueidentifier поле кластерный индекс, то лучше создавать его через DEFAULT (newsequentialid()). В таком случае слово «нормальный» можно писать не в кавычках или даже просто заменить слово нормальный на — хороший выбор

http://msdn.microsoft.com/ru-ru/library/ms189786.aspx

http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x-NewId-x-NewSeque

http://stackoverflow.com/questions/20974062/why-does-using-newid-use-more-space-than-newsequentialid
Всего голосов 1: ↑1 и ↓0+1
Расскажу одну историю. Была система, где программисты считали также, как и вы, и сделали все ключи uniqueidentifier. И у них была таблица тегов, связанная многие-ко-многим с таблицей сущностей. И получилось так, что запросы по тегам использовались везде. Из-за uniqueidentifier таблица связей занимала в 4 раза больше чем можно было и внезапно перестала влезать в память.

В итоге почти все запосы вызывали physical read, что уронило производительность системы в разы. Хотя никакой потребности в ключах-гуидах не было, так как репликации не было и не предполагалось.
Всего голосов 2: ↑2 и ↓0+2
Очень интересная история :) Но у нас табличка тегов с int Identity :) А вот про сущности, да. Так как-то исторически сложилось, что uniqueidentifier ))
Всего голосов 1: ↑1 и ↓0+1
newsequentialid в принципе не плохая штука, но и у него есть проблемы. После перезагрузки сервера метод NEWSEQUENTIALID может начать генерировать GUID с меньшего чем до перезагрузки диапазона. В том же NHibernate есть утилита для генерации uuid в нарастающей последовательности, привязывая ко времени.

Всего голосов 2: ↑2 и ↓0+2
беру свои слова обратно :) путь будет — «нормальный» выбор
Всего голосов 1: ↑1 и ↓0+1
Если кратко: когда первичный ключ — UUID, то такая таблица немного проще реплицируется. И значительно проще реплицируются зависимые от нее таблицы.
Всего голосов 3: ↑3 и ↓0+3
А можно немного подробнее (заранее извиняюсь за, возможно, глупые вопросы, ибо с базами данных глубоко не разбирался) на тему того должен ли этот первичный ключ быть кластеризованым индексом для репликации (и почему), а также надо ли этот UUID помечать как ROWGUIDCOL, и может ли он быть NEWSEQUENTIALID или обязательно NEWID (особенно если несколько паблишеров)?
Всего голосов 1: ↑1 и ↓0+1
Кластеризованным индексом ему быть не обязательно. ROWGUIDCOL нужен для некоторых способов репликации, но я обходился без него. Как пишут в msdn, NEWSEQUENTIALID() использовать можно — если на компьютере-генераторе имеется сетевая карта.
Всего голосов 2: ↑2 и ↓0+2
NEWSEQUENTIALID() использовать можно — если на компьютере-генераторе имеется сетевая карта.

То есть если только один сервер генерирует их?
Всего голосов 1: ↑1 и ↓0+1
Нет. Просто на каждом сервере СУБД должна быть сетевая карта
Всего голосов 2: ↑2 и ↓0+2
Для разных задач — разные решения…
Про проблемы с GUID хорошо здесь написано.

P.S. Если нужна репликация, лучше хотя бы NEWSEQUENTIALID() использовать.
Всего голосов 2: ↑2 и ↓0+2
Все правильно написано (почти).

У меня претензия к этому (из моего комментария):
Сомнительно выглядит выделенный текст — последовательно увеличивающиеся значения полезны тем, что не приводят к расщеплению страниц, как они влияют на эффективность некластерных индексов непонятно.

Не написано только, что кластерный ключ вовсе не обязан быть первичным ключом таблицы

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

P.S. Да, я тоже иногда читаю «по диагонали» :)
Всего голосов 1: ↑1 и ↓0+1
После прочтения статьи понял, что читать об индексах не понимая механики выполнения запросов практически бесполезно. А по моим наблюдениям от силы половина программистов, использующих СУБД в проектах не знают как выполняется запрос и не умеют читать план.
Всего голосов 2: ↑2 и ↓0+2
Ну, с чего-то надо начинать. Почему бы и не с индексов, а затем про планы уже… И поэтому я привёл внизу небольшой список книг для заинтересовавшихся.
Собственно, вторая и из них отлично все объясняет: SQL Server Execution Plans
Кстати, её автор буквально на днях написал заметку о важности понимания планов выполнения разработчиком:
Why Developers Need to Understand Execution Plans
Комментарий пока не оценивали0
Даже для таких программистов можно попробовать объяснить простые правила, я попробовал :)

Кстати, не сочтите за рекламу, однако коллеги говорили что про план выполнения я тоже достаточно понятно написал (разумеется, разобраны не все детали, иначе была бы книга).
Всего голосов 1: ↑1 и ↓0+1
Спасибо за ссылку. Добавил в избранное. Теперь буду всем заинтересованным темой предлагать прочтение вашей блогозаписи.
Комментарий пока не оценивали0

Спасибо большое!
Очень интересно

Комментарий пока не оценивали0
Вот кстати, интересный вопрос — не кажется ли вам что чем больше от разработчика требуется понимания планов выполнения, устройства индексов и и.д., то тем больше теряется смысл собственно языка SQL? Ведь если мы знаем индексы, знаем планы, то может проще сразу и писать их? Сделать стандартный язык планов вместо стандартного языка запросов, так сказать.
Всего голосов 1: ↑1 и ↓0+1
Если подсистема запросов должна найти данные без преимуществ некластеризованного индекса, то она сделает полное сканирование таблицы для нахождения нужных ей строк. ...
Что это за абстрактные «преимущества»? Любой индекс дает преимущества только если подходит для запроса. К примеру, если у нас индекс по Id — а мы делаем выборку по какому-нибудь Date — то индекс ничем не поможет. Более того, именно этот запрос кластеризованный индекс по Id даже замедлит.
Всего голосов 1: ↑1 и ↓0+1
Это из вопроса про кучи и там имеется в виду, что в куче без некластеризованного индекса на нужном вам столбце будет происходить полное сканирование, поэтому если у вас неприемлемые для ваших условий планы выполнения, то лучше такой некластеризованный индекс по нужному вам ключу создать.
Комментарий пока не оценивали0
Спасибо. Добавил ссылку.
А то я и забыл, как раз и начал читать в pdf, но потом надоело мучаться и перешёл на версию для kindle.
Всего голосов 1: ↑1 и ↓0+1
Когда задумывался о том, какую бы статью еще написать на Хабр, думал как раз об индексах — вещь очень полезная и нужная. Времени, к сожалению, не нашел, поэтому благодарю вас за то, что разместили этот материал.

Теперь немного критики.

Картинка со структурой индекса в целом скорее вредная, чем полезная.
По ней совершенно непонятно, как же идет адресация. На самом деле, на корневом уровне не написано, что индекс содержит значения с 1 по 200. На ней написано что-то такое: «Значения с 1 по 100 ищи на странице 2, значения с 101 по 200 ищи на странице 3», на странице 2 написано — «значения с 1 по 50 ищи на странице 4, значения с 51 по 100 ищи на странице 5», и так далее, до листового уровня, на котором уже расположены сами данные.

К примеру, если коэффициент выставлен в значение 90, то при росте индекс займет на странице 90%, а затем перейдет на следующую страницу.

Какая ерунда. Fill factor используется только при перестройке и создании индекса — т.е. при ребилде страницы будут заполнены на 90%. Это делается специально, чтобы при росте индекса (при вставке в таблицу) на каждой странице оставалось свободное место для вставки значений, дабы избежать необходимости разделения страниц. При вставке новых строк страницы всегда заполняются до 100%.

Если индексы настолько замечательны, то почему бы просто не создать их на каждый столбец?

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

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

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

В остальном информация полезна и нужна — начинающие разработчики на SQL скажут вам спасибо.
Всего голосов 2: ↑2 и ↓0+2
uniquifier увеличивается последовательно от 0 (который не хранится, типа как null в столбцах) и дальше внутри каждого неуникального значения.
Ну и primary key не совсем означает физическую упорядоченность строк. Он всего лишь гарантирует что на странице с ключами 1-25 не будет ключа 52 (значения не из этого диапазона), а уж расположение этих 1-25 внутри страницы может быть совершенно произвольным (в порядке добавления). Из-за чего нет никаких посторонних накладных расходов при добавлении на эту страницу ключа 11, если там есть свободное место.
INSERT, главного врага всех индексов
Почему он главный враг? Главный враг — update на индексируемые столбцы (понятно что так делать не стоит и наверняка делается не так часто, как обыкновенный insert).
Не пойму разницу между CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate) и Sales(OrderID,OrderDate): дерево строится только по OrderID и в нём как данные ещё хранятся OrderDate (типа как в кластерном ключе), ну и соответственно ссылка на всю строку. Т.е. на размере индекса мы не выигрываем, при обновлении OrderDate мы так же должны обновить его и в индексе. В чём его выигрышность?
Всего голосов 2: ↑1 и ↓10
Не пойму разницу между CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate) и Sales(OrderID,OrderDate): дерево строится только по OrderID и в нём как данные ещё хранятся OrderDate (типа как в кластерном ключе), ну и соответственно ссылка на всю строку. Т.е. на размере индекса мы не выигрываем, при обновлении OrderDate мы так же должны обновить его и в индексе. В чём его выигрышность?
Выигрыш — в количестве записей в индексных (внутренних) вершинах. Больше записей в вершине — меньше высота дерева — быстрее доступ.
Всего голосов 1: ↑1 и ↓0+1
Почему он главный враг? Главный враг — update на индексируемые столбцы (понятно что так делать не стоит и наверняка делается не так часто, как обыкновенный insert).
Вот потому insert и главный враг, что чаще выполняется.
Всего голосов 1: ↑1 и ↓0+1
Очень простое объяснение: поля, содержащиеся в INCLUDE, хранятся только на листовом уровне, поля, содержащиеся в определении (до INCLUDE), являются частью ключа индекса, т.е. содержатся как на листовом, так и на уровне ветвей.
Всего голосов 2: ↑2 и ↓0+2
при обновлении OrderDate мы так же должны обновить его и в индексе. В чём его выигрышность?
в конкретном этом примере, вполне реальном, ясно, что поле OrderDate не будет обновляться с вероятность 99,(9)%.
А по сути, в INCLUDE включаются именно поля для выборки в SELECT, чтобы не было Key Lookup.
Комментарий пока не оценивали0
Для чего так делается я понимаю, но самое доходчивое (и в общем-то очевидное, просто я всегда листьями представляю) выше у minamoto.
Всего голосов 1: ↑1 и ↓0+1
Есть ли фильтруемые индексы или их аналоги в Oracle или PostgreSQL?
Всего голосов 1: ↑1 и ↓0+1
В postgres это partial index.
Вообще было бы интересно увидеть статью о сравнении типов индексов и физической организации таблиц между разными БД.
Всего голосов 1: ↑1 и ↓0+1
В Oracle фильтруемых индексов нет, но есть повсеместно используемый хак, связанный с тем, что в индексах не хранятся NULL-значения и сочетаемый с использованием FBI.
Всего голосов 1: ↑1 и ↓0+1
Отличная статья, то что нужно для знакомства с индексами, спасибо.
Всего голосов 2: ↑1 и ↓10
Кластеризованный индекс хранит реальные строки данных в листьях индекса

Честно говоря, меня эта формулировка заметно запутала при дальнейшем чтении. Как я понял из professorweb.ru/my/sql-server/2012/level3/3_5.php, кластеризованный индекс попросту определяет порядок хранения строк в самой таблице и никакие строки повторно не хранит; хотя технически, наверное, это утверждение можно считать корректным — дескать, листья индекса — это сами строки таблицы.

Дальше, например, идёт утверждение, что составной индекс «может содержать более одного столбца», но здесь уже «содержать» имеет другой смысл, чем «хранит» выше. Я, было, недоумевал — если [элемент] индекса хранит уже всю строку, как он может не «содержать» часть столбцов? «Составной индекс строится более чем по 1 столбцу», кажется, гораздо понятнее.

Про уникальный индекс тоже непонятно: «обеспечивает уникальность каждого значения в индексируемом столбце» — это как? Разве не constraints самой таблицы это обеспечивают? Индекс вроде никак не может влиять на данные, нет? Имелось в виду, что такой индекс строится по primary key?
Комментарий пока не оценивали0
кластеризованный индекс попросту определяет порядок хранения строк в самой таблице и никакие строки повторно не хранит

Потому что он их хранит не "повторно", а просто хранит. Других мест для хранения всех данных при наличии кластеризованного индекса попросту нет.

Комментарий пока не оценивали0
Зарегистрируйтесь на Хабре, чтобы оставить комментарий

Публикации

Показать лучшие за всё время

Истории

Ваш аккаунт

Разделы

Информация

Услуги

Техническая поддержка
© 2006–2024, Habr