Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 44
Disclaimer: да, я понимаю что это перевод, к переводчику претензий нет :)
По содержанию — совсем для начинающих имеет смысл либо более сфокусированное изложение на отдельных деталях (чтобы было понимание «почему», а не «освой индексы за 21 минуту»), либо тезисы с отсылкой на отдельные статьи.
Для людей с опытом недостаточно качественно (лучше sqlskills почитать), см. пример ниже.
Сомнительно выглядит выделенный текст — последовательно увеличивающиеся значения полезны тем, что не приводят к расщеплению страниц, как они влияют на эффективность некластерных индексов непонятно.
Допускаю, что я могу чего-то не знать, однако, в этом случае подобное точно стоило бы объяснить.
P.S. Есть ещё спорные моменты, однако не хочется начинать холивар :)
По содержанию — совсем для начинающих имеет смысл либо более сфокусированное изложение на отдельных деталях (чтобы было понимание «почему», а не «освой индексы за 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. Есть ещё спорные моменты, однако не хочется начинать холивар :)
Все правильно написано (почти). Для кластерного ключа (ключа, по которому строится кластерный индекс) идеально подходит identity:
1) Меньше page splits — больше производительность вставки данных
2) Некластерные индексы в листах хранят кластерные ключи — чем меньше кластерный ключ, тем меньше занимают индексы
3) Чем меньше размер ключа, тем выше производительность соединений
Не написано только, что кластерный ключ вовсе не обязан быть первичным ключом таблицы, из-за чего обычно и возникает холивар natural key vs surrogate key.
1) Меньше page splits — больше производительность вставки данных
2) Некластерные индексы в листах хранят кластерные ключи — чем меньше кластерный ключ, тем меньше занимают индексы
3) Чем меньше размер ключа, тем выше производительность соединений
Не написано только, что кластерный ключ вовсе не обязан быть первичным ключом таблицы, из-за чего обычно и возникает холивар natural key vs surrogate key.
А вы не подскажите почему все(ну может так получилось) относительно новые системы которые я видел кластерный индекс строят через uniqueidentifier? А в старых системах int identity.Т.е. как раз таки больше цениться ветвление, а не скорость изменения ключа.
Есть несколько факторов:
1) int identity в качестве первичного ключа удобен для относительно небольших простых баз (до нескольких сотен ГБ), но создает грабли для offline режима работы, который сейчас очень популярен из-за мобильных приложений. Такие же проблемы возникают при прикладной репликации СУБД в распределенных системах.
2) архитектура приложения, когда кластерный ключ не соответствует первичному ключу таблицы, крайне сложна для разработчиков. Ни один ORM, например, не поддерживает такую архитектуру.
3) заметные преимущества разных ключей проявляются в сценариях хранилища данных, когда объем данных и интенсивность записи очень высоки.
4) большинство программистов крайне слабо разбираются в физическом дизайне базы и вопроах быстродействия (увы)
5) Немалый процент разработчиков ведется на холивары о «вредности» int identity (это по сути следствие п4)
В итоге uniqueidentifier в качестве первичного и кластерного ключа для многих это «нормальный» выбор, так как позволяет снизить риски архитектуры и не сильно уронить быстродействие.
1) int identity в качестве первичного ключа удобен для относительно небольших простых баз (до нескольких сотен ГБ), но создает грабли для offline режима работы, который сейчас очень популярен из-за мобильных приложений. Такие же проблемы возникают при прикладной репликации СУБД в распределенных системах.
2) архитектура приложения, когда кластерный ключ не соответствует первичному ключу таблицы, крайне сложна для разработчиков. Ни один ORM, например, не поддерживает такую архитектуру.
3) заметные преимущества разных ключей проявляются в сценариях хранилища данных, когда объем данных и интенсивность записи очень высоки.
4) большинство программистов крайне слабо разбираются в физическом дизайне базы и вопроах быстродействия (увы)
5) Немалый процент разработчиков ведется на холивары о «вредности» int identity (это по сути следствие п4)
В итоге uniqueidentifier в качестве первичного и кластерного ключа для многих это «нормальный» выбор, так как позволяет снизить риски архитектуры и не сильно уронить быстродействие.
2) архитектура приложения, когда кластерный ключ не соответствует первичному ключу таблицы, крайне сложна для разработчиков. Ни один ORM, например, не поддерживает такую архитектуру.Не совсем так. ORM ничего не знает про кластерные индексы, поскольку работает уровнем выше. Но при этом запросы от ORM зачастую используют именно первичный ключ — а потому делать кластерный индекс отличным от первичного ключа попросту неоптимально.
В итоге 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
Расскажу одну историю. Была система, где программисты считали также, как и вы, и сделали все ключи uniqueidentifier. И у них была таблица тегов, связанная многие-ко-многим с таблицей сущностей. И получилось так, что запросы по тегам использовались везде. Из-за uniqueidentifier таблица связей занимала в 4 раза больше чем можно было и внезапно перестала влезать в память.
В итоге почти все запосы вызывали physical read, что уронило производительность системы в разы. Хотя никакой потребности в ключах-гуидах не было, так как репликации не было и не предполагалось.
В итоге почти все запосы вызывали physical read, что уронило производительность системы в разы. Хотя никакой потребности в ключах-гуидах не было, так как репликации не было и не предполагалось.
newsequentialid в принципе не плохая штука, но и у него есть проблемы. После перезагрузки сервера метод NEWSEQUENTIALID может начать генерировать GUID с меньшего чем до перезагрузки диапазона. В том же NHibernate есть утилита для генерации uuid в нарастающей последовательности, привязывая ко времени.
Если кратко: когда первичный ключ — UUID, то такая таблица немного проще реплицируется. И значительно проще реплицируются зависимые от нее таблицы.
А можно немного подробнее (заранее извиняюсь за, возможно, глупые вопросы, ибо с базами данных глубоко не разбирался) на тему того должен ли этот первичный ключ быть кластеризованым индексом для репликации (и почему), а также надо ли этот UUID помечать как ROWGUIDCOL, и может ли он быть NEWSEQUENTIALID или обязательно NEWID (особенно если несколько паблишеров)?
Для разных задач — разные решения…
Про проблемы с GUID хорошо здесь написано.
P.S. Если нужна репликация, лучше хотя бы NEWSEQUENTIALID() использовать.
Про проблемы с GUID хорошо здесь написано.
P.S. Если нужна репликация, лучше хотя бы NEWSEQUENTIALID() использовать.
Все правильно написано (почти).
У меня претензия к этому (из моего комментария):
Сомнительно выглядит выделенный текст — последовательно увеличивающиеся значения полезны тем, что не приводят к расщеплению страниц, как они влияют на эффективность некластерных индексов непонятно.
Не написано только, что кластерный ключ вовсе не обязан быть первичным ключом таблицы
Написано, см. главу Обязательно ли создавать кластеризованный индекс на столбце с первичным ключом?
P.S. Да, я тоже иногда читаю «по диагонали» :)
После прочтения статьи понял, что читать об индексах не понимая механики выполнения запросов практически бесполезно. А по моим наблюдениям от силы половина программистов, использующих СУБД в проектах не знают как выполняется запрос и не умеют читать план.
Ну, с чего-то надо начинать. Почему бы и не с индексов, а затем про планы уже… И поэтому я привёл внизу небольшой список книг для заинтересовавшихся.
Собственно, вторая и из них отлично все объясняет: SQL Server Execution Plans
Кстати, её автор буквально на днях написал заметку о важности понимания планов выполнения разработчиком:
Why Developers Need to Understand Execution Plans
Собственно, вторая и из них отлично все объясняет: SQL Server Execution Plans
Кстати, её автор буквально на днях написал заметку о важности понимания планов выполнения разработчиком:
Why Developers Need to Understand Execution Plans
Даже для таких программистов можно попробовать объяснить простые правила, я попробовал :)
Кстати, не сочтите за рекламу, однако коллеги говорили что про план выполнения я тоже достаточно понятно написал (разумеется, разобраны не все детали, иначе была бы книга).
Кстати, не сочтите за рекламу, однако коллеги говорили что про план выполнения я тоже достаточно понятно написал (разумеется, разобраны не все детали, иначе была бы книга).
Извиняюсь, вторая ссылка неправильная, вот правильная.
Вот кстати, интересный вопрос — не кажется ли вам что чем больше от разработчика требуется понимания планов выполнения, устройства индексов и и.д., то тем больше теряется смысл собственно языка SQL? Ведь если мы знаем индексы, знаем планы, то может проще сразу и писать их? Сделать стандартный язык планов вместо стандартного языка запросов, так сказать.
Если подсистема запросов должна найти данные без преимуществ некластеризованного индекса, то она сделает полное сканирование таблицы для нахождения нужных ей строк. ...Что это за абстрактные «преимущества»? Любой индекс дает преимущества только если подходит для запроса. К примеру, если у нас индекс по Id — а мы делаем выборку по какому-нибудь Date — то индекс ничем не поможет. Более того, именно этот запрос кластеризованный индекс по Id даже замедлит.
Когда задумывался о том, какую бы статью еще написать на Хабр, думал как раз об индексах — вещь очень полезная и нужная. Времени, к сожалению, не нашел, поэтому благодарю вас за то, что разместили этот материал.
Теперь немного критики.
Картинка со структурой индекса в целом скорее вредная, чем полезная.
По ней совершенно непонятно, как же идет адресация. На самом деле, на корневом уровне не написано, что индекс содержит значения с 1 по 200. На ней написано что-то такое: «Значения с 1 по 100 ищи на странице 2, значения с 101 по 200 ищи на странице 3», на странице 2 написано — «значения с 1 по 50 ищи на странице 4, значения с 51 по 100 ищи на странице 5», и так далее, до листового уровня, на котором уже расположены сами данные.
Какая ерунда. Fill factor используется только при перестройке и создании индекса — т.е. при ребилде страницы будут заполнены на 90%. Это делается специально, чтобы при росте индекса (при вставке в таблицу) на каждой странице оставалось свободное место для вставки значений, дабы избежать необходимости разделения страниц. При вставке новых строк страницы всегда заполняются до 100%.
Для начала неплохо бы написать, что при вставке данные вставляются не только в саму таблицу, но и во все индексы, размещенные на таблице, т.е. количество операций собственно вставки умножается на количество индексов + 1 (сама таблица), что, даже без необходимости разделения страниц, уже само по себе негативно влияет на производительность.
Реорганизация — это отдельная операция над индексом, не включающаяся вставкой. Тут уместнее сказать, что вставка или обновление значения может вызвать разделение страниц не только на уровне данных — листовом уровне, но и на более высоких уровнях индекса.
В остальном информация полезна и нужна — начинающие разработчики на SQL скажут вам спасибо.
Теперь немного критики.
Картинка со структурой индекса в целом скорее вредная, чем полезная.
По ней совершенно непонятно, как же идет адресация. На самом деле, на корневом уровне не написано, что индекс содержит значения с 1 по 200. На ней написано что-то такое: «Значения с 1 по 100 ищи на странице 2, значения с 101 по 200 ищи на странице 3», на странице 2 написано — «значения с 1 по 50 ищи на странице 4, значения с 51 по 100 ищи на странице 5», и так далее, до листового уровня, на котором уже расположены сами данные.
К примеру, если коэффициент выставлен в значение 90, то при росте индекс займет на странице 90%, а затем перейдет на следующую страницу.
Какая ерунда. Fill factor используется только при перестройке и создании индекса — т.е. при ребилде страницы будут заполнены на 90%. Это делается специально, чтобы при росте индекса (при вставке в таблицу) на каждой странице оставалось свободное место для вставки значений, дабы избежать необходимости разделения страниц. При вставке новых строк страницы всегда заполняются до 100%.
Если индексы настолько замечательны, то почему бы просто не создать их на каждый столбец?
Для начала неплохо бы написать, что при вставке данные вставляются не только в саму таблицу, но и во все индексы, размещенные на таблице, т.е. количество операций собственно вставки умножается на количество индексов + 1 (сама таблица), что, даже без необходимости разделения страниц, уже само по себе негативно влияет на производительность.
это приведёт к тому, что индексу может потребоваться реорганизация, затрагивающая все связанные индексы и операции, в результате будет повсеместное падение производительности.
Реорганизация — это отдельная операция над индексом, не включающаяся вставкой. Тут уместнее сказать, что вставка или обновление значения может вызвать разделение страниц не только на уровне данных — листовом уровне, но и на более высоких уровнях индекса.
В остальном информация полезна и нужна — начинающие разработчики на SQL скажут вам спасибо.
uniquifier увеличивается последовательно от 0 (который не хранится, типа как null в столбцах) и дальше внутри каждого неуникального значения.
Ну и primary key не совсем означает физическую упорядоченность строк. Он всего лишь гарантирует что на странице с ключами 1-25 не будет ключа 52 (значения не из этого диапазона), а уж расположение этих 1-25 внутри страницы может быть совершенно произвольным (в порядке добавления). Из-за чего нет никаких посторонних накладных расходов при добавлении на эту страницу ключа 11, если там есть свободное место.
Не пойму разницу между CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate) и Sales(OrderID,OrderDate): дерево строится только по OrderID и в нём как данные ещё хранятся OrderDate (типа как в кластерном ключе), ну и соответственно ссылка на всю строку. Т.е. на размере индекса мы не выигрываем, при обновлении OrderDate мы так же должны обновить его и в индексе. В чём его выигрышность?
Ну и 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 мы так же должны обновить его и в индексе. В чём его выигрышность?
Не пойму разницу между CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate) и Sales(OrderID,OrderDate): дерево строится только по OrderID и в нём как данные ещё хранятся OrderDate (типа как в кластерном ключе), ну и соответственно ссылка на всю строку. Т.е. на размере индекса мы не выигрываем, при обновлении OrderDate мы так же должны обновить его и в индексе. В чём его выигрышность?Выигрыш — в количестве записей в индексных (внутренних) вершинах. Больше записей в вершине — меньше высота дерева — быстрее доступ.
Почему он главный враг? Главный враг — update на индексируемые столбцы (понятно что так делать не стоит и наверняка делается не так часто, как обыкновенный insert).Вот потому insert и главный враг, что чаще выполняется.
Очень простое объяснение: поля, содержащиеся в INCLUDE, хранятся только на листовом уровне, поля, содержащиеся в определении (до INCLUDE), являются частью ключа индекса, т.е. содержатся как на листовом, так и на уровне ветвей.
при обновлении OrderDate мы так же должны обновить его и в индексе. В чём его выигрышность?в конкретном этом примере, вполне реальном, ясно, что поле OrderDate не будет обновляться с вероятность 99,(9)%.
А по сути, в INCLUDE включаются именно поля для выборки в SELECT, чтобы не было Key Lookup.
Есть ли фильтруемые индексы или их аналоги в Oracle или PostgreSQL?
В postgres это partial index.
Вообще было бы интересно увидеть статью о сравнении типов индексов и физической организации таблиц между разными БД.
Вообще было бы интересно увидеть статью о сравнении типов индексов и физической организации таблиц между разными БД.
В Oracle фильтруемых индексов нет, но есть повсеместно используемый хак, связанный с тем, что в индексах не хранятся NULL-значения и сочетаемый с использованием FBI.
Отличная статья, то что нужно для знакомства с индексами, спасибо.
Кластеризованный индекс хранит реальные строки данных в листьях индекса
Честно говоря, меня эта формулировка заметно запутала при дальнейшем чтении. Как я понял из professorweb.ru/my/sql-server/2012/level3/3_5.php, кластеризованный индекс попросту определяет порядок хранения строк в самой таблице и никакие строки повторно не хранит; хотя технически, наверное, это утверждение можно считать корректным — дескать, листья индекса — это сами строки таблицы.
Дальше, например, идёт утверждение, что составной индекс «может содержать более одного столбца», но здесь уже «содержать» имеет другой смысл, чем «хранит» выше. Я, было, недоумевал — если [элемент] индекса хранит уже всю строку, как он может не «содержать» часть столбцов? «Составной индекс строится более чем по 1 столбцу», кажется, гораздо понятнее.
Про уникальный индекс тоже непонятно: «обеспечивает уникальность каждого значения в индексируемом столбце» — это как? Разве не constraints самой таблицы это обеспечивают? Индекс вроде никак не может влиять на данные, нет? Имелось в виду, что такой индекс строится по primary key?
(комментарий был удален)
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
14 вопросов об индексах в SQL Server, которые вы стеснялись задать