Здравствуйте, Merle, Вы писали:
M>Вообщем спор у нас свелся к тому, какие запросы по БД выполняются чаще, а это уж каждый пусть за себя решает.
Спор действительно получился интересный. Читал сначала и на этом месте забыл с чего он начинался.
Рассмотрим варианты:
1) PK по GUIDу -- кластерный индекс действительно не нужен.
2) PK по IDENTITY. Я не понимаю почему кластерный PK -- плохо, если поле первичного ключа увеличивается (пусть даже не последовательно). Более подходящие кандираты на кластерный индекс встречаются очень редко. Это должны быть поля, в которых находятся редко изменяемые значения и уже отсортированные при вставке в таблицу. Идеальный пример -- дата/время создания записи, но кластерный индекс понадобится только в случае выборки по этому полю (joinы — тоже считаются, зависит от плана запроса). А насчет расположения данных в порядке кластерного ключа BOL не врет, возможно просто не договаривает что при fill factor < 100% при добавлении записи в середину таблицы на не полностью заполненную страницу -- записи на текущей странице не сортируются (к стати это хорошо с точки зрения производительности INSERTа).
A_>2) PK по IDENTITY. Я не понимаю почему кластерный PK -- плохо, если поле первичного ключа увеличивается (пусть даже не последовательно).
Это может быть плохо при интенсивной вставке.
A_> Более подходящие кандираты на кластерный индекс встречаются очень редко.
Гораздо чаще чем кажется, пример с FK я уже приводил.
A_> Идеальный пример -- дата/время создания записи,
Отвратительный пример. Это плохо при интенсивной вставке, все опять попадает на последнюю страницу.
Определяющим фактором для создания кластерного индекса является интенсивность групповых и диапазонных выборок по этой таблице, а так же характер этих выборок (имеется ввиду, что в идеале удается физически разнести разные транзакции по разным частям таблицы)
A_> А насчет расположения данных в порядке кластерного ключа BOL не врет,
БОЛ действительно не врет, а просто не договаривает.
A_> возможно просто не договаривает что при fill factor < 100%
fill factor играет роль только на момент создания индекса, все последующие изменения на fill factor не смотрят.
Здравствуйте, DemAS, Вы писали:
DAS>Здравствуйте, Merle, Вы писали:
M>> ... а во вторых строить кластерный индекс по идентификационному полю — в принципе занятие сомнительное..
DAS> Почему ?
Странно, что такой вопрос вызвал так много споров.
Для MS SQL Server'a версии >=7.0 верным будет вот что:
1. Данные в таблице с кластерным индексом физически упорядочены, а данные в "куче" (таблице без кластерного индекса) — нет.
2. Кластерный индекс лучше всего добавлять для колонки в которой данные изменяются монотонно. Так лучше делать, когда выполняется много DELETE и INSERT. А вот, когда нужно делать много выборок, то надо уже смотреть, как лучше сделать.
3. Кластерные индексы НЕ НАДО делать на основе столбцов типа GUID, хотя издержки и не будут значительными.
Вот и все и не надо ничего выдумывать
Здравствуйте, Merle, Вы писали:
A_>>2) PK по IDENTITY. Я не понимаю почему кластерный PK -- плохо, если поле первичного ключа увеличивается (пусть даже не последовательно). M>Это может быть плохо при интенсивной вставке.
Интенсивная вставка работает просто замечательно, когда новые записи вставляются на последнюю страницу. При выключенной опции принудительной записи на диск она скорее всего будет записана один раз, когда будет полностью заполнена.
A_>> Более подходящие кандираты на кластерный индекс встречаются очень редко. M>Гораздо чаще чем кажется, пример с FK я уже приводил.
Мне такой пример в общем случае абсолютно не нравится потому, что:
1) Обычно в таблицах поболее одного FK, а кластерный индекс один на таблицу. Кроме того, при наличии других индексов они будут иметь ссылки на кластерный индекс, что опошляет всю идею оптимизации.
If the table does not have a clustered index, the row locator is the row's disk address. If the table does have a clustered index, the row locator is the clustered index key for the row.
2) При INSERT/UPDATE будут затраты на вставку страниц в середину таблицы.
Если мы говорим о справочниках, которые состоят из двух таблиц и редко изменяются, то только в этом случае я согласен с пользой кластерного индекса по FK.
A_>> Идеальный пример -- дата/время создания записи, M>Отвратительный пример. Это плохо при интенсивной вставке, все опять попадает на последнюю страницу. M>Определяющим фактором для создания кластерного индекса является интенсивность групповых и диапазонных выборок по этой таблице, а так же характер этих выборок (имеется ввиду, что в идеале удается физически разнести разные транзакции по разным частям таблицы)
Задача кластерного индекса -- минимизировать объем дисковых операций. Единственная дополнительная польза от кластерного ключа будет меньшая вероятность эскаляции блокировок.
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft® SQL Server™ 2000 automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold.
For example, when a transaction requests rows from a table, SQL Server automatically acquires locks on those rows affected and places higher-level intent locks on the pages and table, or index, which contain those rows. When the number of locks held by the transaction exceeds its threshold, SQL Server attempts to change the intent lock on the table to a stronger lock (for example, an intent exclusive (IX) would change to an exclusive (X) lock). After acquiring the stronger lock, all page and row level locks held by the transaction on the table are released, reducing lock overhead.
A_>> А насчет расположения данных в порядке кластерного ключа BOL не врет, M>БОЛ действительно не врет, а просто не договаривает.
A_>> возможно просто не договаривает что при fill factor < 100% M>fill factor играет роль только на момент создания индекса, все последующие изменения на fill factor не смотрят.
Действительно не смотрят, они им нагло пользуются. fill factor это процент заполненности страниц, в случае с кластерным индексом -- страниц с данными.
P.S. Я думаю что полезность кластерного индекса надо рассматривать в каждом конкретном случае.
Before creating clustered indexes, understand how your data will be accessed. Consider using a clustered index for:
Columns that contain a large number of distinct values.
Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
Columns that are accessed sequentially.
Queries that return large result sets.
Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because the rows are already sorted. This improves query performance.
OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.
Clustered indexes are not a good choice for:
Columns that undergo frequent changes
This results in the entire row moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.
Wide keys
The key values from the clustered index are used by all nonclustered indexes as lookup keys and therefore are stored in each nonclustered index leaf entry.
P.P.S. А дефолтовый кластерный индекс по интовому PK в основном полезен. Правда когда я увидел 11 гигабайтную базу с кластерными char(16) PK по умолчанию -- я рыдал.
Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.
Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.
Опять по новой...
A_>Интенсивная вставка работает просто замечательно, когда новые записи вставляются на последнюю страницу. При выключенной опции принудительной записи на диск она скорее всего будет записана один раз, когда будет полностью заполнена.
Отвратительно она работает...
Во-первых, что за опция такая загадочная, "принудительная запись на диск"? Отключить это нельзя, ну да ладно, это здесь вообще не причем, так как непосредственно в базу данные сбрасываются по чекпойнту, а это уже отдельная песня...
Давай и тебе расскажу, в чем тут дело.
Помимо высокоуровневых блокировок (lock), с которыми мы все привыкли иметь дело, есть куча низкоуровневых (latch, spinlock). Когда ты просишь сервер добавить запись в таблицу, ему надо на странице данных модифицировать:
1. Заголовок страницы
2. Собственно добавить запись в страницу
3. Модифицировать Slot Array, все на той же странице.
Все эти действия должны быть выполнены атомарно. Самый простой способ сделать это — заблокировать всю страницу на время добавления записи, что сервер и делает накладывая latch на всю страницу при добавлении записи.
В случае интенсивной вставки, если новые записи попадают на разные страницы, то этот latch на производительность никакого эффекта не оказывает, но если каждая новая запись гарантировано попадет на одну и ту же страницу страницу, то эта страница окажется перманантно заблокированной, и к ней выстроится длинная очередь желающих что-нибудь записать, что есть совсем не гуд.
Если не веришь мне, можешь почитать классиков о том же самом:http://rsdn.ru/Forum/Message.aspx?mid=729872&only=1
A_>1) Обычно в таблицах поболее одного FK, а кластерный индекс один на таблицу.
Ну надо ж выбирать, какой FK актуальнее и нет проблем спроектировать базу так, чтобы в случае необходимости было не более одного критичного поля на таблицу.
A_> Кроме того, при наличии других индексов они будут иметь ссылки на кластерный индекс, что опошляет всю идею оптимизации.
С какого перепугу?
A_>2) При INSERT/UPDATE будут затраты на вставку страниц в середину таблицы.
Какие затраты?
A_>Если мы говорим о справочниках, которые состоят из двух таблиц и редко изменяются, то только в этом случае я согласен с пользой кластерного индекса по FK.
Скорее наоборот, справочники чуть-ли не единственный случай, когда оправдан кластерный индекс по ПК.
A_>Задача кластерного индекса -- минимизировать объем дисковых операций.
Правильно, именно поэтому на PK он наименее полезен, так как в большинстве случаев количество дисковых операций с ПК и так достаточно мало.
A_>Единственная дополнительная польза от кластерного ключа будет меньшая вероятность эскаляции блокировок.
Не надо мне рассказывать про эскалацию блокировок..
A_>Действительно не смотрят, они им нагло пользуются. fill factor это процент заполненности страниц, в случае с кластерным индексом -- страниц с данными.
Ага, только при создании индекса. При заполнении страница делится пополам, а не по фил-фактору, так что он тут совершенно не при чем.
A_>P.S. Я думаю что полезность кластерного индекса надо рассматривать в каждом конкретном случае.
Правильно, только заметь, что из всех предложенных вариантов, только один имеет отношение к ПК, да и тот идет самым последним...
Вообщем опять-таки, не веришь мне, могу отослать к классикам:http://rsdn.ru/Forum/Message.aspx?mid=729872&only=1
Здравствуйте, Merle, Вы писали:
M>Опять по новой...
Как знать, может быть чего-нибудь умное скажу.
A_>>Интенсивная вставка работает просто замечательно, когда новые записи вставляются на последнюю страницу. При выключенной опции принудительной записи на диск она скорее всего будет записана один раз, когда будет полностью заполнена. M>Отвратительно она работает... M>Во-первых, что за опция такая загадочная, "принудительная запись на диск"? Отключить это нельзя, ну да ладно, это здесь вообще не причем, так как непосредственно в базу данные сбрасываются по чекпойнту, а это уже отдельная песня...
Насчет опции я наверное что-то перепутал, возможно это было еще в 6.5. Я был уверен, что эта опция sp_configure. В документации по 2000 есть только конфигурирование lazy writera.
M>Давай и тебе расскажу, в чем тут дело. M>Помимо высокоуровневых блокировок (lock), с которыми мы все привыкли иметь дело, есть куча низкоуровневых (latch, spinlock). Когда ты просишь сервер добавить запись в таблицу, ему надо на странице данных модифицировать: M>1. Заголовок страницы M>2. Собственно добавить запись в страницу M>3. Модифицировать Slot Array, все на той же странице. M>Все эти действия должны быть выполнены атомарно. Самый простой способ сделать это — заблокировать всю страницу на время добавления записи, что сервер и делает накладывая latch на всю страницу при добавлении записи. M>В случае интенсивной вставки, если новые записи попадают на разные страницы, то этот latch на производительность никакого эффекта не оказывает, но если каждая новая запись гарантировано попадет на одну и ту же страницу страницу, то эта страница окажется перманантно заблокированной, и к ней выстроится длинная очередь желающих что-нибудь записать, что есть совсем не гуд.
Все так как ты говоришь. Но это меньшее зло, чем вставка страницы в середину таблицы, т.к. реально она выделяется непонятно где и происходит фрагментация. Тем более 8К страница очень быстро заполнится данными и максимальный размер очереди ожидания latchя будет 8К/rec_size. Таблицы из двух интов не рассматриваем.
Я немного сгруппировал параграфы, относящиеся к этому вопросу. A_>>Действительно не смотрят, они им нагло пользуются. fill factor это процент заполненности страниц, в случае с кластерным индексом -- страниц с данными. M>Ага, только при создании индекса. При заполнении страница делится пополам, а не по фил-фактору, так что он тут совершенно не при чем.
Хочу заметить что ПОЛНАЯ страница делится пополам. А это приводит к
When a new row is added to a full page, SQL Server moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. Page splitting can impair performance and fragment the storage of the data in a table.
А когда она будет 100% заполнена зависит от fill factora, задаваемого при создании кластерного индекса. В случае заполнения таблицы с кластерным индексом уже отсортированными значениями все будет то же, но с меньшей фрагментацией.
Можно убрать проблему “hot spot”ов, но заработать проблему периодического ребилда кластерных индексов.
Also, because of how SQL Server 2000 and 7.0 allocate pages, the statistical trend will be to allocate new pages near the split, starting with the extent in which the full page resides. The only time this won't occur is if your database application rarely deletes enough rows on any given page to free up enough space. In that case, the newly allocated page will probably be physically located far away from the full page. How this will affect your I/O depends on how many files you have in your filegroup and where those files reside. The solution is to periodically rebuild your clustered index to let SQL Server move the rows to physically contiguous pages. You can also create your indexes with a fill factor of, say, 50 percent. But remember that the lower the fill factor, the more pages you need for your data. More pages translates into more I/O and more memory in the data cache when SQL Server needs to query the table.
A_>>2) При INSERT/UPDATE будут затраты на вставку страниц в середину таблицы. M>Какие затраты?
Выделить новую страницу, скопировать на нее половину записей со старой 100% заполненной.
A_>>1) Обычно в таблицах поболее одного FK, а кластерный индекс один на таблицу. M>Ну надо ж выбирать, какой FK актуальнее и нет проблем спроектировать базу так, чтобы в случае необходимости было не более одного критичного поля на таблицу.
Ну хорошо, есть многомиллионная таблица, которая ссылается на две маленькие таблицы или на одну, но разными полями. Например:
table Orders (id int, r_BillingAddr int, r_ShipmentAddr int). Надо сделать выборку заказа и показать 2 адреса, а также должна быть возможность фильтровать заказы по адресам. По какому полю строим кластерный индекс?
A_>> Кроме того, при наличии других индексов они будут иметь ссылки на кластерный индекс, что опошляет всю идею оптимизации. M>С какого перепугу?
Потому что при выборке по кластерному FK все будет быстро, а при выборке по некластерному FK сервер все равно будет метаться по многим страницам.
If the table does not have a clustered index, the row locator is the row's disk address. If the table does have a clustered index, the row locator is the clustered index key for the row.
A_>>Если мы говорим о справочниках, которые состоят из двух таблиц и редко изменяются, то только в этом случае я согласен с пользой кластерного индекса по FK. M>Скорее наоборот, справочники чуть-ли не единственный случай, когда оправдан кластерный индекс по ПК.
Имелось в виду что есть например большая таблица адресов, которая ссылается на таблицу городов. Тогда будет гут сделать id города кластерным и fk в таблице адресов тоже.
A_>>Задача кластерного индекса -- минимизировать объем дисковых операций. M>Правильно, именно поэтому на PK он наименее полезен, так как в большинстве случаев количество дисковых операций с ПК и так достаточно мало.
Буду стрелять цитатами из BOL
Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.
A_>>Единственная дополнительная польза от кластерного ключа будет меньшая вероятность эскаляции блокировок. M>Не надо мне рассказывать про эскалацию блокировок..
Не буду
A_>>P.S. Я думаю что полезность кластерного индекса надо рассматривать в каждом конкретном случае. M>Правильно, только заметь, что из всех предложенных вариантов, только один имеет отношение к ПК, да и тот идет самым последним...
Ты забываешь исключить те варианты, когда "Columns that undergo frequent changes".
M>Вообщем опять-таки, не веришь мне, могу отослать к классикам:http://rsdn.ru/Forum/Message.aspx?mid=729872&only=1
M>Остальные цитаты после первых двух...
Классиков надо критиковать, а то их разведется немерянно.
Интересно ведь обменяться практическим опытом с живыми людьми, которые сталкивались с теми же проблемами.
Здравствуйте, Alexey_ch, Вы писали:
A_> Я был уверен, что эта опция sp_configure. В документации по 2000 есть только конфигурирование lazy writera.
Небыло никогда такой опции, да и быть не могло...
A_>Все так как ты говоришь. Но это меньшее зло, чем вставка страницы в середину таблицы, т.к. реально она выделяется непонятно где и происходит фрагментация.
Не меньшее, а скорее даже большее, так как из таблицы данные еще удаляют и происходит ровно таже самая фрагментация...
Да и практика показывает, что с этим hot-spot'ом можно очень серьезно встрять, мне один раз удалось.
A_>
A_>Page splitting can impair performance and fragment the storage of the data in a table.
Да кто бы спорил... Но вот это вот как раз меньшее зло...
A_>А когда она будет 100% заполнена зависит от fill factora, задаваемого при создании кластерного индекса.
Это верно...
A_>В случае заполнения таблицы с кластерным индексом уже отсортированными значениями все будет то же, но с меньшей фрагментацией.
Она либо будет, либо ее не будет вообще, причем не будет вообще ее только в том случае, если данные идут строго последовательно и при этом никогда не удаляются.
A_>Можно убрать проблему “hot spot”ов, но заработать проблему периодического ребилда кластерных индексов.
Для этого надо оченть постараться...
A_>Выделить новую страницу, скопировать на нее половину записей со старой 100% заполненной.
Это происходит достаточно редко, так как в большинстве случаев ключи распределяются равномерно по всей таблице.
A_>Ну хорошо, есть многомиллионная таблица, которая ссылается на две маленькие таблицы или на одну, но разными полями. Например: A_>table Orders (id int, r_BillingAddr int, r_ShipmentAddr int). Надо сделать выборку заказа и показать 2 адреса, а также должна быть возможность фильтровать заказы по адресам. По какому полю строим кластерный индекс?
По любому, кроме id, в зависимости от того, какие адреса требуются чаще, по оставшемуся построить покрывающий индекс... Или разнести это дело на две таблицы. А вообще возможны довольно любопытные варианты, например кластерный индекс по r_BillingAddr, id, и индексы по id, r_ShipmentAddr и r_ShipmentAddr, от остальных запросов зависит...
A_>Потому что при выборке по кластерному FK все будет быстро, а при выборке по некластерному FK сервер все равно будет метаться по многим страницам.
Значит надо сделать так, чтобы не метались, разнести на несколько таблиц или построить покрывающие индексы... делание PK кластерным ситуацию не улучшит, а наоборот.
A_>Буду стрелять цитатами из BOL
Не стоит, я его тоже не плохо знаю..
A_>
A_>Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.
Еще раз: Я же никогда не говорил, что PK никогда нельзя использовать в качестве кластерного индекса, я говорил, что это бывает оправданым достаточно редко. И этот пример в БОЛ идет после всех остальных, которые к ПК никакого отношения не имеют...
A_>Ты забываешь исключить те варианты, когда "Columns that undergo frequent changes".
А при чем тут ПК?
A_>Интересно ведь обменяться практическим опытом с живыми людьми, которые сталкивались с теми же проблемами.
Вот я и делюсь своим практическим опытом..
Здравствуйте, Merle, Вы писали: A_>>В случае заполнения таблицы с кластерным индексом уже отсортированными значениями все будет то же, но с меньшей фрагментацией. M>Она либо будет, либо ее не будет вообще, причем не будет вообще ее только в том случае, если данные идут строго последовательно и при этом никогда не удаляются.
В последнее время заказчики пошли жадные, а диски дешевые. Записи в основном помечаются удаленными.
A_>>Можно убрать проблему “hot spot”ов, но заработать проблему периодического ребилда кластерных индексов. M>Для этого надо оченть постараться...
Неделя работы базы ящиков голосовых сообщений у крупного телефонного провайдера и результат обеспечен.
A_>>Выделить новую страницу, скопировать на нее половину записей со старой 100% заполненной. M>Это происходит достаточно редко, так как в большинстве случаев ключи распределяются равномерно по всей таблице.
Была такая ситуация. Распределение пришлось прибить, т.к. страдала скорость выборки. Было около 25 млн. записей. После этого суммарная скорость транзакций возросла.
INSERTы долбят в конец таблицы, статистика считается по условию datediff(d, -3, getdate())
A_>>Ну хорошо, есть многомиллионная таблица, которая ссылается на две маленькие таблицы или на одну, но разными полями. Например: A_>>table Orders (id int, r_BillingAddr int, r_ShipmentAddr int). Надо сделать выборку заказа и показать 2 адреса, а также должна быть возможность фильтровать заказы по адресам. По какому полю строим кластерный индекс? M>По любому, кроме id, в зависимости от того, какие адреса требуются чаще, по оставшемуся построить покрывающий индекс... Или разнести это дело на две таблицы. А вообще возможны довольно любопытные варианты, например кластерный индекс по r_BillingAddr, id, и индексы по id, r_ShipmentAddr и r_ShipmentAddr, от остальных запросов зависит...
А я бы сделал кластерный индекс по ID. И жил бы он до тех пор, пока бы реально не понадобился для другого поля. И что тут на две таблицы разносить?
M>Еще раз: Я же никогда не говорил, что PK никогда нельзя использовать в качестве кластерного индекса, я говорил, что это бывает оправданым достаточно редко. И этот пример в БОЛ идет после всех остальных, которые к ПК никакого отношения не имеют... A_>>Ты забываешь исключить те варианты, когда "Columns that undergo frequent changes". M>А при чем тут ПК?
Поле, по которому постороен PK, не изменяют при апдейтах, также как правило заполняют его увеличивающимися значениями.
Кластерный индекс ускоряет выборку единичной записи из таблицы. Т.е. его применение для PK всегда оправдано.
Другое дело что в каких-то отдельных случаях можно найти более подходящее применение кластерному ключу. Эти случаи должны быть перечислены в BOL под заголовком "Consider using a clustered index for" и одновременно не должны быть в "Clustered indexes are not a good choice for".
A_>>Интересно ведь обменяться практическим опытом с живыми людьми, которые сталкивались с теми же проблемами. M>Вот я и делюсь своим практическим опытом..
Ну, за опыт.
Здравствуйте, Alexey_ch, Вы писали:
A_>А я бы сделал кластерный индекс по ID. И жил бы он до тех пор, пока бы реально не понадобился для другого поля.
Это для случая, когда совершенно пофигу какой кластерный индекс делать, а вот когда окажется не пофигу, то это точно будет не ПК.
A_>Кластерный индекс ускоряет выборку единичной записи из таблицы. Т.е. его применение для PK всегда оправдано.
Нет, не оправдано, оно оправдано, когда других кандидатов нет. Ускорять выборку единичной записи особого смысла не имеет, так как это и так достаточно быстрая операция. Букмарк-лукап за одной записью ничего не стоит, а вот букмарк за каждой записью диапазона 1. дорог сам по себе, 2. может служить причиной выбора оптимизатором сканирования таблицы/кластерного индекса, что в свою очередь приводит к торможению других запросов из-за ожидания на блокировках и резкому увеличению вероятности дедлока, ввиду того что запрос лезет к чужим записям, которые на самом деле ему не нужны.
Это плохо при интенсивной вставке, все опять попадает на последнюю страницу.
Мы уже победили, просто это еще не так заметно...
