Ладно, давай разбираться..
L>Это тоже плохо, т.к. вставка с большой вероятностью будет приводить к лишним модификациям кластерного индекса.
Не совсем так... К модификациям кластерного индекса это не приведет, внутри страницы записи не упорядчены. А вот при частой вставке, при наличии монотонного индекса возможет следующий любопытный эффект: При модификации индекса, в частности при добавлении нового ключа, для обеспечения согласованности, блокируется вся страница индекса, куда этот ключ добавляется. Блокировка (latch) накладывается только на время вставки и в обычном режиме сколь либо заметного эффекта на производительность она не оказывает, но если каждый последующий ключ больше (меньше) предыдущего, то все ключи попадают на последнюю страницу индекса и возникает драка за эту страницу между конкурирующими транзакциями и выстраивается совершенно не нужная очередь на ресурс. И ничего хорошего в этом нет. В случае же GUID'ов нагрузка размажется по всей таблице и чем больше таблица, тем меньше вероятность пересечения.
L>Именно это. Причем весьма убедительно. M>>Кластерный индекс по identity крайне редко бывает оптимальным выбором, то есть это лучше чем отсутствие кластерного индекса вообще, но не более того.. L>Все, пошел звать Влада.
В чем вообще весь цымус кластерного индекса? Это механизм позволяющий с некоторой долей вероятности управлять физическим размещением записей в таблице. В случае кластеризации по identity бонусов с этого можно получить довольно мало.
Пусть у нас есть табличка с PK identity, идентификатором пользователя, и большим количеством других полей с данными.. Пусть каждый пользователь большую часть времени работает только со своими данными. Нагрузка достаточно высокая и данных надо обработать достаточно много.
Что получется, если мы кластеризуем таблицу по identity? Данные конкретного пользователя окажутся размазанными по всей таблице, записи нужные для обработки, с очень высокой вероятностью окажутся физически на разных страницах, что приведет к совершенно не нужному ползанью по диску. Пользователи постоянно мешали бы друг-другу при страничных локировках и latch'ах захватывая чужие данные...
А вот если бы мы кластеризовали эту таблицу по ID пользователя, то картина была бы совершенно другой, получилось бы, что каждый пользователь фактически работал бы со своей частью таблицы и не лез бы к соседу, большинство данных поднималось бы за одно обращение к диску, поскольку с хорошей вероятностью они все окажутся на одной странице, в крайнем случае на соседних...
Если бы у нас была еще и подчиненная таблица с отношением один ко многим, то опять таки, ровно из тех же соображений, кластеризовать ее бы стоило не по PK, а по внешнему ключу...
Это все к тому, что выбор кластерного индекса — крайне важный стратегический вопрос, и выбирать его надо исходя из предстоящей нагрузки и характерных запросов, и крайне редко оптимальным выбором является identity.
L>>>См. выше. Производительность с GUID будет хуже, чем int или bigint.
См. выше
Единственный недостаток GUID'а — это большая длинна, что уменьшает количество записей влезающих в одну страницу индекса и, как следствие, увеличивает количество обращений к диску при прохождении по B+tree индексу. Но, как я уже говорил, сколь-либо заметный эффект это оказывает на таблицах размером как минимум в десяток миллионов записей.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, VladD2, Вы писали:
VD>>А сколько ФК можно кластеризовать на одной таблице? И как будет делаться поиск строки на втором и третьем ФК? M>А это уже надо по задаче смотреть, какой кластерным, а какой не очень. В крайнем случае сделать так, чтобы на одну таблицу больше одного FK не приходилось.
Мне кажется ты тут общими фразами не отделаешься, нужно разжёвывать, или ты просто хотел растянуть удовольствие?
В общем, ни хрена в MS-SQL не смыслю но скажу из общетехнических соображений, про джойны:
Предположим, что имеется табличка tab1 с полями pk1 ( identyty ) и selection1 ( некое поле по которому идет выбор) и прочие , а также дочерняя табличка tab2 с полями pk2 ( identity), fk2_pk1 ( внешний ключь на tab1.pk1) и прочими.
Ну и далее предположим, мы выполняем запрос с соединением этих табличек
select <...samefields from tab1 and tab2...>
from tab1, tab2 where tab1.selection1 = :same_value and tab1.pk1 = tab2.fk2_pk1
Предположим, что под условие tab1.selection1 = :same_value подподают 100 записей tab1,
по внешнему ключу к каждой записи tab1 присоединяется 10 записей из tab2,
по полям pk1, selection1, pk2, fk2_pk1 имеются индексы глубина всех индексов 3.
Пусть таблички большие по сравнению с объемом выборки и оптимизатор решает выбрать nested loops для их соеденения.
1) Рассмотрим сначала вариант когда обе таблички имеют кластерный индекс по первичному ключу.
Для выбора данных по selection1 ( будем считать что оптимизатор выбрал index range scan) потребуется чтение 3 страниц индекса по полю selection1, и скорее всего в листевом блоке найдем все 100 ссылок на строки содержащие
selection1 = :same_value. Поскольку кластеризация у нас по pk1, то соответственно по selection1 её быть не может, таким образом следует ожидать что все 100 искомых значений находятся на 100 РАЗНЫХ страницах таблицы, и соответственно что бы к ним обратится надо осуществить ещё 100 чтений страниц.
Далее для каждой выбранной строки tab1 надо найти дочерние в tab2. Для этого у нас есть индекс по fk2_pk1
т.е по каждому найденноq в tab1 строке необходимо выполнить по 3 чтения страницы индекса по fk2_pk1, где в листьевой странице мы найдем искомые 10 ссылок, поскольку индекс по fk2_pk не кластерный следует ожидать, что для получения соответствующих строк таблицы придется обратится к 10 разным страницам.
Итого для этого варианта имеем 3 + 100 + (3 + 10) * 100 = 1403 чтения страниц.
2) Пусть у нас кластерные индексы теперь не по PK, а по полям tab1.selection1 и tab2.fk2_pk1.
Для выбора данных по selection1 потребуется чтение 3 страниц индекса по полю selection1, там же мы находим все
100 искомых строк, ну пусть они не влезли на одну страницу и нам понадобилась еще 1 страница.
Далее для каждой выбранной строки tab1 надо найти дочерние в tab2. Для этого у нас есть кластерный индекс по fk2_pk1 т.е по каждому найденноq в tab1 строке необходимо выполнить по 3 чтения страницы индекса по fk2_pk1
и в листьевой вершине мы найдем все 10 искомых значений.
Итого для соединения нам понадобится ( 3 + 1) + (3)*100 = 304 чтения страниц.
По объему логического чтения вариант когда кластерный индекс делается не по PK оказался в 4,5 раза эффективнее, в данном примере.
VD>>А сколько ФК можно кластеризовать на одной таблице? И как будет делаться поиск строки на втором и третьем ФК?
Смотрим:
3) (вариант 2, но кластерный индекс по другому FK) Пусть у нас теперь нет кластерного индекса по FK2_PK1, а есть по некому другому FK.
Выборка из первой таблицы таже, и для каждой строки из tab2 потребуется выполнить по 3 чтения страницы индекса по fk2_pk1 и сбегать к 10 блокам по 10 ссылкам.
Итого:
3+100 + (3 + 10) * 100 = 1403
То же значение, что и при кластеризации по PK2, что и следовало ожидать если кластеризация идет не потому индексу что используется, то совершенно пофиг по какому индексу она сделана ( PK или другому FK), на рассматриваемую операцию это не влияет.
В итоге
1) при применение кластерных индексов по PK проигрышь по предпологаемому объему логического i/o в примере c соединением составил 4,5 раза. При увеличении количества "дочерних" записей он будет возрастать.
2) При отсутствии кластерного индекса по FK участвуещему в соединении разницы между вариантом соединения где кластеризация идет по PK ( вариант 1 ) и по другому полю ( вариант 3) не ожидается.
Здравствуйте, Lexey, Вы писали:
L>В общем, я так понимаю, что упорядочивание страниц никто и не обещал. Главное, что они слинкованы в правильном порядке.
совершенно верно.
L>Ну и что? А внутри страницы данные похоже упорядочены. L>Вот если окажется, что внутри одной страницы порядок записей может быть любым, тогда твое утверждение будет верным.
Легко, сейчас покажу:
Создадим табличку в одну страницу с кластерным индексом:
Обрати внимание на параметр Offset — физический адрес — пока все по честному, адреса подряд.
А теперь поизмываемся:
delete from t where a = 200
insert into t (a) values(250)
-- и посмотрим что произошло
-- dbcc page('Cavy', 1, 19543, 3)
-- Slot 0 Offset 0x60
-- a = 100
-- Slot 1 Offset 0x8c
-- a = 250
-- Slot 2 Offset 0x76
-- a = 300
-- Slot 3 Offset 0x81
-- a = 400
Логически записи по прежнему отсортированы — Slot 0, Slot 1, ect... А вот физически запись 250 разместилась не по адресу 0x6b, где была раньше 200, ну или по крайней мере не между 0x60 и 0x76, а по адресу 0x8c, то есть добавилась в конец страницы.
Не могли бы уважаемые, как определитесь с выводами, озвучить их с практической точки зрения ?
Сам это разбирал полтора года назад, как бы понятно процентов на 90% что Вы пишете, но кол-во сообщений не дает сделать осмысленные выводы.
Может в журнале ? Я его читаю !
Здравствуйте, Lexey, Вы писали:
L>>>В описанное верится очень слабо. M>>Тем не менее это так. L>Конкретный пример и цифры в студию, pls (если есть). Пока мой здравый смысл утверждает ровно обратное.
Вот цитаты из умных книжек:
Ken Henderson, "Guru's Guide To T-SQL":
A table without a clustered index is known as a heap table. Rows inserted into a heap table are
inserted wherever there’s room in the table. If there’s no room on any of the table’s existing
pages, a new page is created and the rows are inserted onto it. This can create a hotspot at the
end of the table (meaning that users attempting simultaneous INSERTs on the table will vie for
the same resources). To alleviate the possibility of this happening, you should always establish
clustered indexes for the tables you build. Consider using a unique key that distributes new
rows evenly across the table. Avoid automatic, sequential, clustered index keys as they can
cause hotspots, too.
Microsoft T-SQL Performance Tuning Part 2: Index Tuning Strategies Adapted from “Transact-SQL Programming” By Kevin Kline, Andrew Zanevsky, and Lee Gould. Published by O’Reilly & Associates. ISBN: 1565924010
Columns with monotonously increasing values, such as a column with the IDENTITY property or TIMESTAMP columns, can be dangerous. They create a “hot spot” of activity on the last page. Good for certain types of OLTP applications because it minimizes page splits. But can also be bad for certain locking situations.
Там же, чуть ниже, уже по вопросу выбора кластерного индекса:
Clustered indexes are good for queries that use:
· GROUP BY that use all or the first few columns of the clustered index key,
· ORDER BY that use all or the first few columns of the clustered index key,
· WHERE clause conditions comparing to the first or the first few columns of the clustered index key and retrieving many rows,
· Long keys (either based on long columns or composite keys comprised of many columns), because a clustered index on a long key takes no extra space for the leaf level. A non-clustered index on a long key may be quite large, because it takes a lot of space to store keys of the leaf level of the index.
The first three types benefit from the fact that requested rows are located continuously on the table. SQL Server only has to find the first qualifying row and then keep on scanning until all rows are done. Several rows found on a single page reduce the number of I/O operations needed to access all the data. Additionally, Microsoft SQL Server has a performance booster — the read-ahead feature that automatically detects sequential reads from the same table and prefetches data pages before the query asks for them.
Понятно, что PK, тем более суррогатный тут не кандидат... Примерно те же слова есть и в BOL. Кластерный индекс в первую очередь хорош для данных идущих подряд, а для PK можно и покрывающий индекс соорудить. Хотя конечно же надо каждый случай отдельно рассматривать.
Вот еще, ну просто мои слова :
Rick Sawtell, Michael Lee, Matt Bridges, with Victor Isakov
Chapter 4 from SQL Server 7, 24 seven, published by Sybex, Inc.
Although SQL Server Primary Keys are associated with clustered indexes by default, these are often poor choices for clustered indexes. The maximum performance advantage from a clustered index comes when selecting ranges of data. Primary Keys, when included in a WHERE clause, are usually not selected in ranges.
Ну и наконец Kalen Delaney, большего англоязычного авторитета по MSSQL-ю сложно найти
Clustered indexes are extremely useful for range queries (for example, WHERE sales_quantity BETWEEN 500 and 1000) and for queries in which the data must be ordered to match the clustering key. Only one clustered index can exist per table, since it defines the physical ordering of the data for that table. Since you can have only one clustered index per table, you should choose it carefully based on the most critical retrieval operations. Because of the clustered index's role in managing space within the table, nearly every table should have one. And if a table has only one index, it should probably be clustered.
If a table is declared with a primary key (which is advisable), by default the primary key columns form the clustered index. Again, this is because almost every table should have a clustered index, and if the table has only one index, it should probably be clustered. But if your table has several indexes, some other index might better serve as the clustered index. This is often true when you do single-row retrieval by primary key. A nonclustered, unique index works nearly as well in this case and still enforces the primary key's uniqueness. So save your clustered index for something that will benefit more from it by adding the keyword NONCLUSTERED when you declare the PRIMARY KEY constraint.
Все о том же: используйте кластерный индекс для упорядоченных данных и для диапазонных запросов, а с PK и обычный индекс неплохо справляется.
[]
L>>>См. выше. Производительность с GUID будет хуже, чем int или bigint. M>>На таблицах с количеством записей порядка нескольких десятков миллионов (на меньших размерах производительность GUID'ов от identity мало отличается) GUID'ы проигрывают identity от 5% до 20%, но во-первых это все равно надо на конкретной задаче мерять, а во вторых такой проигрыш в должной мере компенсируется отсутствием
L>При таких объемах на конкретной задаче это уже поздо будет мерять. Менять структуру уже работающей базы — мало радости. Мне вполне достаточно, что GUID'ы медленнее, чтобы от них отказаться, там где это возможно.
В свое время на БД > 30 гигов получали результат замедления всего лишь чуть больше 3%. На конкретных задачах.
M>> геморроя при различного рода репликациях...
L>А кроме репликаций? В варианте с репликациями я в общем-то ничего против GUID'ов не имею.
Пожалуйста.
Большинство приложений сейчас — это минимум трехзвенка с выделенным сервером приложений. И, естественно, с оторванными курсорами/наборами данных (Я не про веб, где на стороне веб-сервера можно использовать любые курсоры, что, правда, тоже не есть хорошо). А это значит, что при вставке новых данных, с клиента естественно, с использованием identity, или любом другом способе получения ключа в едином центре (в данном случае — в сервере БД), придется "тащить" полученные ключи на клиента. А там их еще и сливать с данными, которые на вставку отправлялись. Или просто, возвращать всю выборку. Что скорости серверу БД тоже не прибавляет.
А при использовании гуидов на клиента ключи возвращать не надо. Их проще на клиенте сгенерить.
Т.е. все сводится опять к тому, о чем ты писал: "Единственное, когда GUID реально бывает нужен — при необходимости распределенной генерации ключей".
Здравствуйте, Merle, Вы писали:
iT>>Чтобы совсем не волноваться — надо int64 брать M>GUID — вот решение всех проблем!
Щаз. Нормальный кластерный индекс на нем не построишь. Да и уникальность у гуидов заканчивается, AFAIR, где-то в райне 2026 года. До этого времени и bigint прекрасно потянет. Единственное, когда GUID реально бывает нужен — при необходимости распределенной генерации ключей (например, при репликации). В остальных случаях, ИМХО, GUID — must die.
M>Если статью Синклера на ночь не читать...
Здравствуйте, Lexey, Вы писали:
L> Вставка будет с большой вероятностью между уже существующими значениями.
И ничего в этом страшного...
L> И обычные индексы будут сильно пухнуть.
Вот это единственный, относительно серьезный аргумент, но и то.....
L>Так, давай сюда Влада позовем.
Да бога ради..
L> Мне он уже когда-то доказал, что по identity полям кластерный индекс строить — самое то.
Не, он тебе что-то не то доказал...
Кластерный индекс по identity крайне редко бывает оптимальным выбором, то есть это лучше чем отсутствие кластерного индекса вообще, но не более того..
L>См. выше. Производительность с GUID будет хуже, чем int или bigint.
На таблицах с количеством записей порядка нескольких десятков миллионов (на меньших размерах производительность GUID'ов от identity мало отличается) GUID'ы проигрывают identity от 5% до 20%, но во-первых это все равно надо на конкретной задаче мерять, а во вторых такой проигрыш в должной мере компенсируется отсутствием геморроя при различного рода репликациях...
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, Lexey, Вы писали:
M>Ладно, давай разбираться..
L>>Это тоже плохо, т.к. вставка с большой вероятностью будет приводить к лишним модификациям кластерного индекса. M>Не совсем так... К модификациям кластерного индекса это не приведет, внутри страницы записи не упорядчены. А вот при частой вставке, при наличии монотонного
Откуда у тебя такие сведения. MS в BOL открытым текстом пишет, что clustered index задает физический порядок записей.
M> индекса возможет следующий любопытный эффект: При модификации индекса, в частности при добавлении нового ключа, для обеспечения согласованности, блокируется вся страница индекса, куда этот ключ добавляется. Блокировка (latch) накладывается только на время вставки и в обычном режиме сколь либо заметного эффекта на производительность она не оказывает, но если каждый последующий ключ больше (меньше) предыдущего, то все ключи попадают на последнюю страницу индекса и возникает драка за эту страницу между конкурирующими транзакциями и выстраивается совершенно не нужная очередь на ресурс. И ничего хорошего в этом нет. В случае же GUID'ов нагрузка размажется по всей таблице и чем больше таблица, тем меньше вероятность пересечения.
В описанное верится очень слабо. Даже если будет хоть какая-то драка за блокировки, то сам процесс вставки должен идти быстрее, т.к. нужная страница точно будет в кеше и скидывать его на диск придется меньшее число раз. Так же, скорее всего передраться могут только пишушие вставляющие транзакции (коих обычно гораздо меньше, чем читающих), а при вставке в середину еще придется драться с читателями.
L>>Именно это. Причем весьма убедительно. M>>>Кластерный индекс по identity крайне редко бывает оптимальным выбором, то есть это лучше чем отсутствие кластерного индекса вообще, но не более того.. L>>Все, пошел звать Влада. M>В чем вообще весь цымус кластерного индекса? Это механизм позволяющий с некоторой долей вероятности управлять физическим размещением записей в таблице. В случае кластеризации по identity бонусов с этого можно получить довольно мало.
С некоторой долей вероятности? Это как?
M>Пусть у нас есть табличка с PK identity, идентификатором пользователя, и большим количеством других полей с данными.. Пусть каждый пользователь большую часть времени работает только со своими данными. Нагрузка достаточно высокая и данных надо обработать достаточно много.
Блин, я же тебе не предлагаю ВСЕГДА кластерировать по идентити. Но во многих реальных случаях выбока идет по id записи, которая как раз и есть identity. И кластерный индекс по identity тут самое то.
В твоем пример, кстати, с большой долей вероятности identity в таблице вообще будет не нужен.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, VladD2, Вы писали:
VD>>Все так просто. Сделай эксперемент... Циклик в котором всавляй записи. Сначала с идентити, а потом с гуидами. Думаю, это тебя резко разубедит в своей правоте. M>Делал и не раз и в разных задачах.
Ты не сказки рассказывай, а сделай тест и приведи его код с результатами.
VD>>В итоге, все что ты говорил о блокировке просто меркнет по сравнению с левыми накладными расходами. В общем, тест тебе поможет. M>Вообще, ты заблуждаешься..
Здравствуйте, KisA, Вы писали:
KA>Отсюда приминимость IOT в Oracle, на мой взгляд, достаточно мала.
Упс, странно... А причины такого ограничения известны? И что, в Оракле PK — особенный? Или тут имелась ввиду уникальность? тогда ничего страшного...
KA>P.S. Чего то коды форматирования внизу страницы не срабатывают, приходится вводить руками, это только у меня так или что то "улучшили"?
Да вроде все работает, но я в последнее время больше янусом...
Странно, что такой вопрос вызвал так много споров.
Для MS SQL Server'a версии >=7.0 верным будет вот что:
1. Данные в таблице с кластерным индексом физически упорядочены, а данные в "куче" (таблице без кластерного индекса) — нет.
2. Кластерный индекс лучше всего добавлять для колонки в которой данные изменяются монотонно. Так лучше делать, когда выполняется много DELETE и INSERT. А вот, когда нужно делать много выборок, то надо уже смотреть, как лучше сделать.
3. Кластерные индексы НЕ НАДО делать на основе столбцов типа GUID, хотя издержки и не будут значительными.
Вот и все и не надо ничего выдумывать
Здравствуйте, Lexey, Вы писали:
L>Щаз. Нормальный кластерный индекс на нем не построишь.
Ну во-первых очень даже построишь, а во вторых строить кластерный индекс по идентификационному полю — в принципе занятие сомнительное..
L>Да и уникальность у гуидов заканчивается, AFAIR, где-то в райне 2026 года.
AFAIK — еще лет на 100 хватит...
L>Единственное, когда GUID реально бывает нужен — при необходимости распределенной генерации ключей (например, при репликации).
Как правило, идентификаторы на тех объемах, когда int'а не хватает — в одну таблицу уже не лезут...
L>В остальных случаях, ИМХО, GUID — must die.
Да откуда это пошло, что страшнее GUID'а — зверя нет?
Во многих случаях гораздо лучше GUID пользовать, а не identity... Не когда нельзя быть уверенным заранее, что не потребуется две одинаковых таблицы слить...
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, Lexey, Вы писали:
L>>Щаз. Нормальный кластерный индекс на нем не построишь. M>Ну во-первых очень даже построишь, а во вторых строить кластерный индекс по
Я имел в виду, что не эффективно. Вставка будет с большой вероятностью между уже существующими значениями. И обычные индексы будут сильно пухнуть.
M>идентификационному полю — в принципе занятие сомнительное..
Так, давай сюда Влада позовем. Мне он уже когда-то доказал, что по identity полям кластерный индекс строить — самое то.
L>>Да и уникальность у гуидов заканчивается, AFAIR, где-то в райне 2026 года. M>AFAIK — еще лет на 100 хватит...
Где-то я тем не менее эт цифру видел. Хотя вот тут вообще пишут, что до 3400 года хватит.
L>>Единственное, когда GUID реально бывает нужен — при необходимости распределенной генерации ключей (например, при репликации). M>Как правило, идентификаторы на тех объемах, когда int'а не хватает — в одну таблицу уже не лезут...
Это конечно верно, если забыть про возможные удаления.
L>>В остальных случаях, ИМХО, GUID — must die. M>Да откуда это пошло, что страшнее GUID'а — зверя нет? M>Во многих случаях гораздо лучше GUID пользовать, а не identity... Не когда нельзя быть уверенным заранее, что не потребуется две одинаковых таблицы слить...
См. выше. Производительность с GUID будет хуже, чем int или bigint.
Здравствуйте, Merle, Вы писали:
L>> Вставка будет с большой вероятностью между уже существующими значениями. M>И ничего в этом страшного...
Это тоже плохо, т.к. вставка с большой вероятностью будет приводить к лишним модификациям кластерного индекса.
L>> И обычные индексы будут сильно пухнуть. M>Вот это единственный, относительно серьезный аргумент, но и то.....
И что?
M>Не, он тебе что-то не то доказал...
Именно это. Причем весьма убедительно.
M>Кластерный индекс по identity крайне редко бывает оптимальным выбором, то есть это лучше чем отсутствие кластерного индекса вообще, но не более того..
Все, пошел звать Влада.
L>>См. выше. Производительность с GUID будет хуже, чем int или bigint. M>На таблицах с количеством записей порядка нескольких десятков миллионов (на меньших размерах производительность GUID'ов от identity мало отличается) GUID'ы проигрывают identity от 5% до 20%, но во-первых это все равно надо на конкретной задаче мерять, а во вторых такой проигрыш в должной мере компенсируется отсутствием
При таких объемах на конкретной задаче это уже поздо будет мерять. Менять структуру уже работающей базы — мало радости. Мне вполне достаточно, что GUID'ы медленнее, чтобы от них отказаться, там где это возможно.
M> геморроя при различного рода репликациях...
А кроме репликаций? В варианте с репликациями я в общем-то ничего против GUID'ов не имею.
Здравствуйте, Lexey, Вы писали:
L>Это тоже плохо, т.к. вставка с большой вероятностью будет приводить к лишним модификациям кластерного индекса.
Нет не будет, более того, при интенсивной вставке с индексом по монотонному полю производительность будет даже хуже чем с индексом по GUID'ам..
L>И что?
Есть масса встречных возражений...
L>Именно это. Причем весьма убедительно.
Хм.. Я, в свое время доказал ему обратное..
L>Все, пошел звать Влада.
Зови..
L>При таких объемах на конкретной задаче это уже поздо будет мерять.
Ха, бить надо по башке, того, кто позволил разрастись базе до таких размеров не померяв..
L>Мне вполне достаточно, что GUID'ы медленнее, чтобы от них отказаться, там где это возможно.
20% — это не медленнее...
L>А кроме репликаций? В варианте с репликациями я в общем-то ничего против GUID'ов не имею.
А там где репликаций нет, никто не может утверждать, что они не появятся...
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, Lexey, Вы писали:
L>>Это тоже плохо, т.к. вставка с большой вероятностью будет приводить к лишним модификациям кластерного индекса. M>Нет не будет, более того, при интенсивной вставке с индексом по монотонному полю производительность будет даже хуже чем с индексом по GUID'ам..
Тут у нас с тобой получаются одни голословные утверждения.
L>>И что? M>Есть масса встречных возражений...
Хоть одно приведи, pls.
L>>Именно это. Причем весьма убедительно. M>Хм.. Я, в свое время доказал ему обратное..
Не помню такого.
L>>Все, пошел звать Влада. M>Зови..
Уже, только он пока молчит.
L>>При таких объемах на конкретной задаче это уже поздо будет мерять. M>Ха, бить надо по башке, того, кто позволил разрастись базе до таких размеров не померяв..
ОК, приведи нормальный способ померять это на живой базе (меньшего размера), не уродуя ее структуру.
L>>Мне вполне достаточно, что GUID'ы медленнее, чтобы от них отказаться, там где это возможно. M>20% — это не медленнее...
Хм, а что же тогда медленнее? 200%?
L>>А кроме репликаций? В варианте с репликациями я в общем-то ничего против GUID'ов не имею. M>А там где репликаций нет, никто не может утверждать, что они не появятся...
А вот там никто не помешает добавить GUID для репликации в любой момент.
Здравствуйте, Lexey, Вы писали:
L>Я имел в виду, что не эффективно. Вставка будет с большой вероятностью между уже существующими значениями. И обычные индексы будут сильно пухнуть.
Незнаю как тухлсоть индексов, но из-за кластерной структуры индекса скорость вставки гуидов резко падает. Да и индекс по 128 битам значительно менее эффективен чем по интам. АВК как-то сделал тест (правда не по этому поводу, но все же) в котором сначала были инты, а потом стали гуиды. Тормоза были заметны на глаз. В общем, гуид — это удобно (иногда) но дороговато.
M>>идентификационному полю — в принципе занятие сомнительное..
L>Так, давай сюда Влада позовем. Мне он уже когда-то доказал, что по identity полям кластерный индекс строить — самое то.
Откровенно говря уже не помню, но это чистая правда. Кластерный индекс намного эффективнее заполняется последовательными значениями. Про это, если не ошибаюсь, и в БОЛ-е было написано.
L>>>Да и уникальность у гуидов заканчивается, AFAIR, где-то в райне 2026 года. M>>AFAIK — еще лет на 100 хватит...
Слыхал что МС изменял алгоритм генерации гуидов, но что-то в это не верится. Изначально их алгоритм учитывал маг-адрес сетевухи, так что он принципиально уникален в простнанстве (хотел бы я видеть сервер без сетевухи ), и если что начинает приращивать по ещеричке, так что на одной машине он тоже принципиально уникален. Переполнение 64-х разядных числе (врочем как и 32) вещь мало вероятная. Так что это не аргумент.
L>Где-то я тем не менее эт цифру видел. Хотя вот тут вообще пишут, что до 3400 года хватит.
Во-во.
L>>>Единственное, когда GUID реально бывает нужен — при необходимости распределенной генерации ключей (например, при репликации). M>>Как правило, идентификаторы на тех объемах, когда int'а не хватает — в одну таблицу уже не лезут...
L>Это конечно верно, если забыть про возможные удаления.
Да если не забывать тоже. Если вставлять/ужадять постоянно с частотой 0.1 секунды, то инта хватит на 7 лет. А если раз в секунду, то на 70 .
L>>>В остальных случаях, ИМХО, GUID — must die. M>>Да откуда это пошло, что страшнее GUID'а — зверя нет?
Он не страшен. Но относительно не эффективен. В реестре — это самое то. А в БД основанной на кластерных Б+-деревьях — это очень неэффективная форма генерации ID-ёв.
M>>Во многих случаях гораздо лучше GUID пользовать, а не identity... Не когда нельзя быть уверенным заранее, что не потребуется две одинаковых таблицы слить...
Ну, тогда еще есть смысл вместо типов данных строки использовать. Никогда не знаешь что прийдется пихать в колонки завтра.
L>См. выше. Производительность с GUID будет хуже, чем int или bigint.
100%. Особненно int (если речь о 32-х назрядных системах).
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Не, он тебе что-то не то доказал... M>Кластерный индекс по identity крайне редко бывает оптимальным выбором, то есть это лучше чем отсутствие кластерного индекса вообще, но не более того..
И как ты это объяснишь?
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
L>>Это тоже плохо, т.к. вставка с большой вероятностью будет приводить к лишним модификациям кластерного индекса. M>Не совсем так... К модификациям кластерного индекса это не приведет, внутри страницы записи не упорядчены. А вот при частой вставке, при наличии монотонного индекса возможет следующий любопытный эффект: При модификации индекса, в частности при добавлении нового ключа, для обеспечения согласованности, блокируется вся страница индекса, куда этот ключ добавляется. Блокировка (latch) накладывается только на время вставки и в обычном режиме сколь либо заметного эффекта на производительность она не оказывает, но если каждый последующий ключ больше (меньше) предыдущего, то все ключи попадают на последнюю страницу индекса и возникает драка за эту страницу между конкурирующими транзакциями и выстраивается совершенно не нужная очередь на ресурс. И ничего хорошего в этом нет. В случае же GUID'ов нагрузка размажется по всей таблице и чем больше таблица, тем меньше вероятность пересечения.
Все так просто. Сделай эксперемент... Циклик в котором всавляй записи. Сначала с идентити, а потом с гуидами. Думаю, это тебя резко разубедит в своей правоте.
Там эффект такой. Гуид дает очнь большой разбром. Такой большой, что практически каждый раз вставка будет идти в разные стрницы. Это приводит к тому, что на примитивную операцию нужно:
1. Поднять практически всю таблицу в память.
2. Каждый раз изменять системную информацию.
3. Очень часто делить и объеденять кластры (страницы).
В итоге, все что ты говорил о блокировке просто меркнет по сравнению с левыми накладными расходами. В общем, тест тебе поможет.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
1. В том что это не индекс. Это хранение отсортированной информации. У кластреного индекса (КИ) нижние страницы — это таблица. А сам кластер — это занятие памяти с запасом, в рассчете на то что в него будет производиться вставка. Если заполнение последовательное, то кластыры набиваются довольно плотно и поиск по ним будет шустрый.
2. Если на таблице есть кластерный индекс, то все ссылающиеся таблицы будут содержать в себе не что иное как значение ключа кластерного индекса. Таким образом замена 32-х бит на 128 — это нехилый удар и по другим таблицам.
M> Это механизм позволяющий с некоторой долей вероятности управлять физическим размещением записей в таблице.
Без каких либо вероятностей. КИ — это гарантия что твои данные упорядочены физически. Гуиды дают случайную упорядоченность, что приводит к лишним чтениям, так как связанные данные с боьшой долей вероятности лежат близко друг от друга. Гуиды же — это горантия того, что данные будут разбросаны по даблицам хаотически.
M>В случае кластеризации по identity бонусов с этого можно получить довольно мало.
Как раз наоборот:
1. Маленький ключ, а значит меньший объем как самой талблицы, так и ссылающихся на нее.
2. Совпадение с процессорным словом.
3. Последовательное заполнение.
M>Пусть у нас есть табличка с PK identity, идентификатором пользователя, и большим количеством других полей с данными.. Пусть каждый пользователь большую часть времени работает только со своими данными. Нагрузка достаточно высокая и данных надо обработать достаточно много. M>Что получется, если мы кластеризуем таблицу по identity? Данные конкретного пользователя окажутся размазанными по всей таблице,
Интересный вывод. С чего бы это?
M> записи нужные для обработки, с очень высокой вероятностью окажутся физически на разных страницах,
С точностью на оборот. Гуид — вот гарантия хаотического разноса данных.
M> что приведет к совершенно не нужному ползанью по диску. Пользователи постоянно мешали бы друг-другу при страничных локировках и latch'ах захватывая чужие данные...
Вот все это и отнеси к гуидам.
M>А вот если бы мы кластеризовали эту таблицу по ID пользователя, то картина была бы совершенно другой,
А что ID-пользователя не может быть идентети? Ты что под этим термином понимаешь?
M> получилось бы, что каждый пользователь фактически работал бы со своей частью таблицы и не лез бы к соседу, большинство данных поднималось бы за одно обращение к диску, поскольку с хорошей вероятностью они все окажутся на одной странице, в крайнем случае на соседних...
Ты вообще-то о гуидах говорил. Расскажи лучше как твоя теория на счет гуидов будет работать в этом случае.
Хотя если под ID-пользователя ты имешь в виду строку, то тоже ошибаешся.
M>Если бы у нас была еще и подчиненная таблица с отношением один ко многим, то опять таки, ровно из тех же соображений, кластеризовать ее бы стоило не по PK, а по внешнему ключу...
Вот это уже совсем крамола. ПК первый притендент на кластеризацию. Иначе любая ссылка будет букмарком, коий в скуле очень не хилый. К тому же любой джоин будет иметь стоимость в несколько раз выше. В общем, внешние ключи притенденты только если они используются значительно чаще чем ПК. Но это уже откровенно кривой дизайн БД. Таких таблиц в БД должно быть максимум 2-3 на сотню.
M>Это все к тому, что выбор кластерного индекса — крайне важный стратегический вопрос, и выбирать его надо исходя из предстоящей нагрузки и характерных запросов, и крайне редко оптимальным выбором является identity.
Почти всегда.
M>Единственный недостаток GUID'а — это большая длинна,
Если бы. Его хаотичность — это куда более серьезная проблема.
Кстати, красиво ты опять перешел с ID-пользователя на гуиды. Так в чем кайф гуидов в твоем примере?
M> что уменьшает количество записей влезающих в одну страницу индекса и, как следствие, увеличивает количество обращений к диску при прохождении по B+tree индексу.
Все с точностью до наоборот. В общем, тесты в студию!
M> Но, как я уже говорил, сколь-либо заметный эффект это оказывает на таблицах размером как минимум в десяток миллионов записей.
Уже на сотнях тысяч увидишь. Особенно если машика дохлая.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
L>>Мне вполне достаточно, что GUID'ы медленнее, чтобы от них отказаться, там где это возможно. M>20% — это не медленнее...
Откуда дровишьки? Я вот точных цифр не скажу, но сам наблюдал видимые глазом тормоза после изменения БД на гуиды.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Lexey, Вы писали:
L>Откуда у тебя такие сведения. MS в BOL открытым текстом пишет, что clustered index задает физический порядок записей.
От разработчиков сиквела и от собственных экспериментов...
L>В описанное верится очень слабо.
Тем не менее это так.
L> Так же, скорее всего передраться могут только пишушие вставляющие транзакции (коих обычно гораздо меньше, чем читающих), а при вставке в середину еще придется драться с читателями.
А из последней страницы никто не читает? Как раз при кластеризации по identity к последней странце будут лезть все подряд.
L>С некоторой долей вероятности? Это как?
Так, что физического упорядочивания записи нет, есть упорядоченность страниц и гарантия того, что запись "меньше" самой "большой" и "больше" самой маленькой записи страницы окажется на этой странице.
L> И кластерный индекс по identity тут самое то.
Нет, не то, другие выборки, как правило, выполняются чаще и более критичны к скорости.
Здравствуйте, VladD2, Вы писали:
VD>Все так просто. Сделай эксперемент... Циклик в котором всавляй записи. Сначала с идентити, а потом с гуидами. Думаю, это тебя резко разубедит в своей правоте.
Делал и не раз и в разных задачах.
VD>В итоге, все что ты говорил о блокировке просто меркнет по сравнению с левыми накладными расходами. В общем, тест тебе поможет.
Вообще, ты заблуждаешься..
Все напутал...
VD>Если заполнение последовательное, то кластыры набиваются довольно плотно и поиск по ним будет шустрый.
Поиск по кластеру более шустрый не из-за плотности заполнения — она всегда одинаковая, а из-за отсутствия bookmark lookups, ввиду того, что, как ты верно заметил, нижние узлы индекса содержат сами данные, ну и из-за относительной физической упорядоченности.
VD>2. Если на таблице есть кластерный индекс, то все ссылающиеся таблицы будут содержать в себе не что иное как значение ключа кластерного индекса.
Не таблицы, а другие индексы в этой таблице.
VD>Без каких либо вероятностей. КИ — это гарантия что твои данные упорядочены физически.
Не совсем. Это гарантия того, что физически будут упорядочены страницы, а данные внутри страницы не упорядочены.
VD>3. Последовательное заполнение.
Вот последовательное заполнение я бы как раз в минус записал, я уже писал ранее почему.
VD>Интересный вывод. С чего бы это?
Я имел ввиду данные разных пользователей.
VD>С точностью на оборот. Гуид — вот гарантия хаотического разноса данных.
Я немного о другом.
VD>Вот все это и отнеси к гуидам.
Ты меня не понял.
VD>А что ID-пользователя не может быть идентети? Ты что под этим термином понимаешь?
ID пользователя может быть любым, здесь я уже о том, что делать кластерный индекс по уникальному идентификатору вообще имеет мало смысла, не важно что это за идентификатор GUID или Identity.
VD>Ты вообще-то о гуидах говорил.
Да блин, здесь я уже о другом говорю.
VD>Вот это уже совсем крамола. ПК первый притендент на кластеризацию.
Нет. PK — последний кандидат на кластеризацию.
VD>Иначе любая ссылка будет букмарком, коий в скуле очень не хилый.
Не любая, а очень даже редкая.
VD>Если бы. Его хаотичность — это куда более серьезная проблема.
Его хаотичность в большинстве случаев вообще не роляет.
VD>Кстати, красиво ты опять перешел с ID-пользователя на гуиды. Так в чем кайф гуидов в твоем примере?
А читать внимательнее пробовал?
Здравствуйте, Merle, Вы писали:
VD>>Если заполнение последовательное, то кластыры набиваются довольно плотно и поиск по ним будет шустрый. M>Поиск по кластеру более шустрый не из-за плотности заполнения — она всегда одинаковая, а из-за отсутствия bookmark lookups, ввиду того, что, как ты верно
С чего это она одинакова? При вставке в произвольнные места будет происходить дробление страниц, а это как раз понижение плотности. Плюс к этому индекс фрагментируется. Это может очень нефигово снижать производительность. При вставке в конец нет ни дробления ни фрагментации индекса.
M> заметил, нижние узлы индекса содержат сами данные, ну и из-за относительной физической упорядоченности.
VD>>2. Если на таблице есть кластерный индекс, то все ссылающиеся таблицы будут содержать в себе не что иное как значение ключа кластерного индекса. M>Не таблицы, а другие индексы в этой таблице.
Это верно.
VD>>Без каких либо вероятностей. КИ — это гарантия что твои данные упорядочены физически. M>Не совсем. Это гарантия того, что физически будут упорядочены страницы, а данные внутри страницы не упорядочены.
Тут ты возможно прав. Если будет время, попробую сам посмотреть.
VD>>3. Последовательное заполнение. M>Вот последовательное заполнение я бы как раз в минус записал, я уже писал ранее почему.
Совсем не убедительно сказал.
VD>>А что ID-пользователя не может быть идентети? Ты что под этим термином понимаешь? M>ID пользователя может быть любым, здесь я уже о том, что делать кластерный индекс по уникальному идентификатору вообще имеет мало смысла, не важно что это за идентификатор GUID или Identity.
Очень спорное утверждение.
VD>>Иначе любая ссылка будет букмарком, коий в скуле очень не хилый. M>Не любая, а очень даже редкая.
Тут Влад действительно загнул.
VD>>Если бы. Его хаотичность — это куда более серьезная проблема. M>Его хаотичность в большинстве случаев вообще не роляет.
Роляет, роляет.
VD>>Кстати, красиво ты опять перешел с ID-пользователя на гуиды. Так в чем кайф гуидов в твоем примере? M>А читать внимательнее пробовал?
Здравствуйте, Merle, Вы писали:
L>>Откуда у тебя такие сведения. MS в BOL открытым текстом пишет, что clustered index задает физический порядок записей. M>От разработчиков сиквела и от собственных экспериментов...
ОК, это серьезный аргумент.
L>>В описанное верится очень слабо. M>Тем не менее это так.
Конкретный пример и цифры в студию, pls (если есть). Пока мой здравый смысл утверждает ровно обратное.
L>> Так же, скорее всего передраться могут только пишушие вставляющие транзакции (коих обычно гораздо меньше, чем читающих), а при вставке в середину еще придется драться с читателями. M>А из последней страницы никто не читает? Как раз при кластеризации по identity к последней странце будут лезть все подряд.
С чего бы это? Последняя страница одна, а выборки по чтению идут по всем. Вероятность того, что понадобиться именно последняя страница порядка 1/N (N-число страниц). При больших N она ничтожна.
L>>С некоторой долей вероятности? Это как? M>Так, что физического упорядочивания записи нет, есть упорядоченность страниц и гарантия того, что запись "меньше" самой "большой" и "больше" самой маленькой записи страницы окажется на этой странице.
ОК.
L>> И кластерный индекс по identity тут самое то. M>Нет, не то, другие выборки, как правило, выполняются чаще и более критичны к скорости.
Здравствуйте, Lexey, Вы писали:
L>С чего бы это? Последняя страница одна, а выборки по чтению идут по всем. Вероятность того, что понадобиться именно последняя страница порядка 1/N (N-число страниц). При больших N она ничтожна.
Если в таблицу идут частые инсерты, то, при кластерном индексе по identity, вероятность того что потребуется последняя страница пропорциональна количеству вставляемых в секунду записей и обратно пропорциональна среднему размеру записи. И эта вероятность никак не зависит от числа страниц.
Здравствуйте, VladD2, Вы писали:
VD>Все так просто. Сделай эксперемент... Циклик в котором всавляй записи. Сначала с идентити, а потом с гуидами. Думаю, это тебя резко разубедит в своей правоте.
Guid долго формируется. Функция NewGuid() шифрует информацию о компьютере где был создан идентификатор, поэтому работает не очень быстро.
VD>Там эффект такой. Гуид дает очнь большой разбром. Такой большой, что практически каждый раз вставка будет идти в разные стрницы. Это приводит к тому, что на примитивную операцию нужно: VD>1. Поднять практически всю таблицу в память.
Если бы это было так, то у меня БД не могла бы работать. Потому что много таблиц с размером намного больше оперативной памяти. VD>2. Каждый раз изменять системную информацию.
Это что? Статистику что ли? Так не будет она меняться при каждом изменении. VD>3. Очень часто делить и объеденять кластры (страницы).
Если сделать кластерный индекс по такому полю, то расщепления страниц могут стать частой операцией. Но это незначительная потеря по сравнению со множеством удобств — для моей базы это так. Да и замедление работы пользователями никак не ощущается.
Здравствуйте, Lexey, Вы писали:
L>Откуда у тебя такие сведения. MS в BOL открытым текстом пишет, что clustered index задает физический порядок записей.
О, даже я напутал, даже страницы физически не упорядочены... Физическое упорядочивание было только в семерке и именно по этому при переходе на 2000 появилась куча проблем, так как народ забивал на order by, уповая на упорядоченность кластерного индекса, особенно этим славились разработчики 1С.
Вообщем вот пример:
Создаем простенькую табличк уи навешиваем на нее кластерный индекс:
create table t (id int not null, fld char(3500) not null)
dbcc traceon(3604)
---------------------------------------------------------------------------
-- вставим в нее 100 записей в порядке убывания id.declare @i int
set @i = 100
set nocount on
while @i > 0 begin
insert t values (@i, 'aaa')
set @i = @i - 1
end
create clustered index ix on t(id)
delete from t where id between 21 and 70
declare @j int
set @j = 100
set nocount on
while @j < 200 begin
insert t values (@j, 'bbb')
set @j = @j + 1
end
Там уже записи вовсе не с id = 1, 2...
Итого, при кластерном индексе нет никакой гарантии физического упорядочивания, есть логическое упорядочивание страниц Prev-Next и именно в этом порядке и производится сканирование индекса. И есть гарантия того, что не придется прыгать за следующей записью из середины страницы (пока страница не просмотрена вся — следующая запись гарантировано находится на этой же странице)
Таким образом, хаотичность GUID'ов не может оказывать сколь-нибудь сильного отрицательного эффекта при вставке. Возможно так было в семерке, но 2000 это уже давно не так.
L> Так же, скорее всего передраться могут только пишушие вставляющие транзакции (коих обычно гораздо меньше, чем читающих), а при вставке в середину еще придется драться с читателями.
Опять-таки, при кластеризации по identity записи совершенно разных транзакций попадают в одно и тоже место, и потребоваться эти записи могут так же совершенно разным транзакциям и читать из этой последней страницы будут ни чуть не реже, чем из всех остальных, так что hot spot там может ого-го какой возникнуть. Кластеризация же по неуникальному полю, имеющиму какой-либо смысл с точки зрения бизнес-логики, позволит разнести нагрузку разных транзакций по разным частям таблицы.
L>В твоем пример, кстати, с большой долей вероятности identity в таблице вообще будет не нужен.
А других уникальных идентификаторов там нет, так что либо identity, либо GUID в качестве суррогатного ключа.
VD>А что ID-пользователя не может быть идентети? Ты что под этим термином понимаешь?
Вообщем здесь мы рассматривали два довольно слабо связанных вопроса:
1. недостатки guid по сравнению с identity в качестве уникального идентификатора. Одним из недостатков была неоптимальность построения кластерного индекса, отсюда и вылез второй вопрос:
2. Необходимость вообще строить кластерный индекс по PK (не важно identity ПК или guid). Мое скромное мнение заключается в том, что кластерный индекс по PK очень редко бывает оптимальным выбором. В большинстве случаев это лучше чем отсутствие кластерного индекса вообще, но не более того.
И в этом примере совершенно не важео что из себя представляет ID пользователя, главное, что оно не уникально.
VD>ПК первый притендент на кластеризацию. Иначе любая ссылка будет букмарком, коий в скуле очень не хилый.
Это не так. Первый претендент на кластеризацию — это неуникальное поле, которое позволит физически разнести активность разных групп транзакций по разным частям таблицы. ПК по определению уникальный, букмарк за уникальной записью нге бог весть какой накладной расход, а вот когда приходится делать букмарк за группой записей или за диапазоном, то все становится гораздо печальнее.
L>Конкретный пример и цифры в студию, pls (если есть). Пока мой здравый смысл утверждает ровно обратное.
Пример с кластерным индексом по userID — из жизни...
При пиковой нагрузке, пока кластерный индекс был по identity производительность была никакой, в профайлере четко было видно очередь из latch'ей на страницу индекса, а агрегатные запросы вообще курили... Периодически лезли дедлоки.
После того, как сделали кластерный индекс по ID пользователя все волшебным образом взлетело, дедлоки исчезли, очередей нет, и заработало все не в пример шустрее.
Собственно решение подсказали и объяснили в чем проблема сами разработчики сиквела, на общественных началах, давно, правда, дело было...
Сейчас, к сожалению, нет возможности эксперименты ставить...
L>С чего бы это? Последняя страница одна, а выборки по чтению идут по всем.
Ну, это действительно скорее проблема интенсивной вставки, но есть такой нюанс, что в реальных очень часто нужны именно последние, только что вставленные данные. И простая вероятность тут мало что решает..
L>С чего это она одинакова? При вставке в произвольнные места будет происходить дробление страниц, а это как раз понижение плотности.
Страница делится напополам (или на две трети, уже не помню) часть данных переносится на новую страницу, таким образом после разбиения образуются две страницы заполненные примерно одинаково... Следующее разбиение будет тогда, когда страница опять заполнится до конца, и опять поровну. Если считать, что guid хаотический, то все страницы заполняются доконца равномерно. identity всегда пишет в последнюю страницу, после того как та окажется заполненой она делится пополам и часть данных переносится на следующую страницу, которая продолжает заполняться... То есть, при identity все страницы кроме последней заполнены наполовину, а при guid'ах, скорее всего, окажется что страницы заполнены больше чем на половину, но всего страниц занято меньше, хотя распределение может оказаться и не столь равномерным.
Но количество расщеплений индекса будет в среднем одинаковым в обоих случаях.
M>>А читать внимательнее пробовал? L>Я вроде внимательно читал и тоже не понял.
Это я перенервничал, работать надо, а отвечать интереснее..
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, VladD2, Вы писали:
VD>>А что ID-пользователя не может быть идентети? Ты что под этим термином понимаешь? M>Вообщем здесь мы рассматривали два довольно слабо связанных вопроса: M>1. недостатки guid по сравнению с identity в качестве уникального идентификатора. Одним из недостатков была неоптимальность построения кластерного индекса, отсюда и вылез второй вопрос:
Ну, тут ты ошибаешся, так что ничего отсюда не вылезает. Да и рассматривали тут вроде именно этот вопрос. Так что для дальнейших рассуждений нужно как-то доказать исходные предпосылки.
M>2. Необходимость вообще строить кластерный индекс по PK (не важно identity ПК или guid). Мое скромное мнение заключается в том, что кластерный индекс по PK очень редко бывает оптимальным выбором.
И оно снова ошибочное. МС сами советуют это делать. Даже в тестовых БД все ПК у МС кластерные.
M> В большинстве случаев это лучше чем отсутствие кластерного индекса вообще, но не более того.
А еще это лучше чем отсуствие индексов вообще. Так мы далеко в рассуждениях зайдем.
M>И в этом примере совершенно не важео что из себя представляет ID пользователя, главное, что оно не уникально.
Не, ну, доказывая приемущества ГУИДов переходить на другие примеры как не очень верно с точки зрения логики.
VD>>ПК первый притендент на кластеризацию. Иначе любая ссылка будет букмарком, коий в скуле очень не хилый. M>Это не так. Первый претендент на кластеризацию — это неуникальное поле, которое позволит физически разнести активность разных групп транзакций по разным частям таблицы.
Откуда дровишки?
M> ПК по определению уникальный, букмарк за уникальной записью нге бог весть какой накладной расход,
Если индекс кластерный, то букмарков не будет вообще, и стало быть не будет и связанных с ними накладных расходов.
M> а вот когда приходится делать букмарк за группой записей или за диапазоном, то все становится гораздо печальнее.
В обещм, тесты в студию!
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
VD>>Если заполнение последовательное, то кластыры набиваются довольно плотно и поиск по ним будет шустрый. M>Поиск по кластеру более шустрый не из-за плотности заполнения — она всегда одинаковая, а из-за отсутствия bookmark lookups, ввиду того, что, как ты верно заметил, нижние узлы индекса содержат сами данные, ну и из-за относительной физической упорядоченности.
И из-за качества заполнения тоже. При слабом наполнении писк будет медленнее.
VD>>2. Если на таблице есть кластерный индекс, то все ссылающиеся таблицы будут содержать в себе не что иное как значение ключа кластерного индекса. M>Не таблицы, а другие индексы в этой таблице.
Ну, да. Естественно.
VD>>Без каких либо вероятностей. КИ — это гарантия что твои данные упорядочены физически. M>Не совсем. Это гарантия того, что физически будут упорядочены страницы, а данные внутри страницы не упорядочены.
Этих подробностей я не знаю. По идеи разумно было бы упорядочивать и внутри страницы.
VD>>3. Последовательное заполнение. M>Вот последовательное заполнение я бы как раз в минус записал, я уже писал ранее почему.
И зря. Блокировки на сегодня можно ставить и на запись, а вот распыление между разными страницами — это плохо.
VD>>Интересный вывод. С чего бы это? M>Я имел ввиду данные разных пользователей.
И? Ты уж объясняй подробнее свои идеи.
VD>>С точностью на оборот. Гуид — вот гарантия хаотического разноса данных. M>Я немного о другом.
А исходный вопрос об этом.
VD>>Вот все это и отнеси к гуидам. M>Ты меня не понял.
Возможно. Объясни лучше.
VD>>А что ID-пользователя не может быть идентети? Ты что под этим термином понимаешь? M>ID пользователя может быть любым, здесь я уже о том, что делать кластерный индекс по уникальному идентификатору вообще имеет мало смысла, не важно что это за идентификатор GUID или Identity.
Ну, про недоказанность и маловероятность этой теории я уже вроде говорил...
VD>>Ты вообще-то о гуидах говорил. M>Да блин, здесь я уже о другом говорю.
А зря. Тема то вон она... в сабже.
VD>>Вот это уже совсем крамола. ПК первый притендент на кластеризацию. M>Нет. PK — последний кандидат на кластеризацию.
Только по-твоему.
Объясни тогда почеиму когда создаешь ПК на таблице mssql автоматом делает кластерный индекс? Они по твоему вредительством занимаются?
И вообще, можно хоть какие-то доказательства верности твоей теории? Ну, ссылки, результаты тестов...
VD>>Иначе любая ссылка будет букмарком, коий в скуле очень не хилый. M>Не любая, а очень даже редкая.
Любоая. Если на таблице нет кластеного уникального индекса, то любая ссылка будет букмарком, т.е. все индексы будут содержать букмарки в качестве идентификаторов строк. Если же на таблице есть уникальный кластерный индекс (УКИ), то в индексах будет использоваться именно он (см. BOL).
VD>>Если бы. Его хаотичность — это куда более серьезная проблема. M>Его хаотичность в большинстве случаев вообще не роляет.
Откуда дровишки? Я говорю о том, что видел сам.
VD>>Кстати, красиво ты опять перешел с ID-пользователя на гуиды. Так в чем кайф гуидов в твоем примере? M>А читать внимательнее пробовал?
Пробовал. Не помогает. Видимо нужно тщательнее излагать мысли.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, andsm, Вы писали:
A>Здравствуйте, VladD2, Вы писали:
VD>>Все так просто. Сделай эксперемент... Циклик в котором всавляй записи. Сначала с идентити, а потом с гуидами. Думаю, это тебя резко разубедит в своей правоте.
A>Guid долго формируется. Функция NewGuid() шифрует информацию о компьютере где был создан идентификатор, поэтому работает не очень быстро.
Блин, проведи эксперемент. Уверяю тебя скорость генерации гуидов на порядки выше скорости вставки данных в БД.
VD>>1. Поднять практически всю таблицу в память. A>Если бы это было так, то у меня БД не могла бы работать. Потому что много таблиц с размером намного больше оперативной памяти.
Если памяти нехватает, то всего лишь увеличятся тормоза. Сиквел занимает память пока она есть. Далее если памяти нехватает, он перераспределяет ее для более нужных таблиц. Так что работать то он будет, но медленнее чем мог бы.
VD>>2. Каждый раз изменять системную информацию. A>Это что? Статистику что ли? Так не будет она меняться при каждом изменении.
Не статистика, а структура индекса.
VD>>3. Очень часто делить и объеденять кластры (страницы). A>Если сделать кластерный индекс по такому полю, то расщепления страниц могут стать частой операцией. Но это незначительная потеря по сравнению со множеством удобств — для моей базы это так. Да и замедление работы пользователями никак не ощущается.
Это очень значительная потеря. Удобство конечно есть, но долеко не всегда, а в очень определенных случаях. Вот репликация и распределенное формирование идентификаторов как раз тот случай когда можно пожертвовать скорость в угоду удобству. Но делать это без необходимости, по-моему, не стоит.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, Lexey, Вы писали:
L>>Откуда у тебя такие сведения. MS в BOL открытым текстом пишет, что clustered index задает физический порядок записей. M>От разработчиков сиквела и от собственных экспериментов...
Для каждого кластерного индекса в sysindexes содержится одна строка с indid = 1. При наличии кластерного индекса, страницы в базе данных и строки внутри них размещаются в ключевой последовательности кластерного индекса. Все вставки выполняются по ключевому значению с сохранением последовательности ключей.
В общем, ты путаешь информацию о кластерных индексах с информацией о хранении данных в таблицах не содержащих кластерные индексы. В приципе к теме это не относится, но и тут ты ошибашся.
L>>В описанное верится очень слабо. M>Тем не менее это так.
Еще раз. Ссылки и тесты в студию.
L>>С некоторой долей вероятности? Это как? M>Так, что физического упорядочивания записи нет, есть упорядоченность страниц и гарантия того, что запись "меньше" самой "большой" и "больше" самой маленькой записи страницы окажется на этой странице.
См. выше.
L>> И кластерный индекс по identity тут самое то. M>Нет, не то, другие выборки, как правило, выполняются чаще и более критичны к скорости.
Да лукап по ПК в грамотно спроектированной БД — это самая частая операция. Любая всавка без нее не обходится.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.
Что уж МС называет физическим порядком решать тебе. Видимо они специально врут. Но как минимум записи в странице точно упорядочены. Или это уже не индекс.
Логично было бы распологать и странице поближе, хотя это уже не обязательно, все равно переход идет по указателю.
А вот собственно цитата от туда же:
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.
по-моему, совершенно однозначно дают понять...
ЗЫ
Еще раз говорю. Создай две одинаковые талички. Одну с гуидом и некластерным индексом. Другую с интом и кластерным. Потом пару тройку вторичных индексов и связей на них (на другие табличи). Ну, и далее все будет ясно.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, Lexey, Вы писали:
L>>Откуда у тебя такие сведения. MS в BOL открытым текстом пишет, что clustered index задает физический порядок записей. M>О, даже я напутал, даже страницы физически не упорядочены... Физическое упорядочивание было только в семерке и именно по этому при переходе на 2000 появилась куча проблем, так как народ забивал на order by, уповая на упорядоченность кластерного индекса, особенно этим славились разработчики 1С. M>Вообщем вот пример:
Очень интересный результат. Статейку не хочешь на эту тему написать?
M>Таким образом, хаотичность GUID'ов не может оказывать сколь-нибудь сильного отрицательного эффекта при вставке. Возможно так было в семерке, но 2000 это уже давно не так.
С этим все равно не согласен. Аргументы все те же — фрагментация индекса, меньшая плотность заполнения и cache misses.
L>> Так же, скорее всего передраться могут только пишушие вставляющие транзакции (коих обычно гораздо меньше, чем читающих), а при вставке в середину еще придется драться с читателями. M>Опять-таки, при кластеризации по identity записи совершенно разных транзакций попадают в одно и тоже место, и потребоваться эти записи могут так же совершенно разным транзакциям и читать из этой последней страницы будут ни чуть не реже, чем из всех остальных, так что hot spot там может ого-го какой возникнуть.
Я тебе и не говорил, что из нее будут реже читать (хотя скорее так и будет, т.к. вновь вставленные записи, как правило, нужны реже, чем старые). Я говорил, что в случае вставки в конец заблокирована только одна страница, а у тебя таких страниц может быть пачка. Вероятность нарваться на блокировку при чтении у тебя больше.
M> Кластеризация же по неуникальному полю, имеющиму какой-либо смысл с точки зрения бизнес-логики, позволит разнести нагрузку разных транзакций по разным частям таблицы.
Кластеризации по неуникальному полю вообще не бывает. Сервер все равно сделает кластерный индекс уникальным. Вот ты все про бизнес логику. А с чего ты решал, что бизнес-логике нужны именно такие запросы. Да, такие запросы бывают, но в тех базах, с которыми я работал, они составляли от силы процентов 30. И грузили они сервер не очень сильно. А основная нагрузка шла на join'ы, которые как раз и делаются по identity и напрямую выигрывают от его кластеризации.
L>>В твоем пример, кстати, с большой долей вероятности identity в таблице вообще будет не нужен. M>А других уникальных идентификаторов там нет, так что либо identity, либо GUID в качестве суррогатного ключа.
Т.е. композитный уникальный ключ приемлемой длины в данном случае не построишь?
Здравствуйте, Merle, Вы писали:
L>>С чего это она одинакова? При вставке в произвольнные места будет происходить дробление страниц, а это как раз понижение плотности. M>Страница делится напополам (или на две трети, уже не помню) часть данных
В 7-ке напополам:
When this occurs, SQL Server needs to divide up the data on the full page and move about half of the data to a new page so that both pages now have some open space.
M> переносится на новую страницу, таким образом после разбиения образуются две страницы заполненные примерно одинаково... Следующее разбиение будет тогда, когда страница опять заполнится до конца, и опять поровну. Если считать, что guid M> хаотический, то все страницы заполняются доконца равномерно. identity всегда
Равномерно — да, но заполненность у них при этом может получиться хреновой.
M> пишет в последнюю страницу, после того как та окажется заполненой она делится пополам и часть данных переносится на следующую страницу, которая продолжает заполняться... То есть, при identity все страницы кроме последней заполнены
В случае с хаотической вставкой — так и будет. В случае с последовательной, думаю, ты заблуждаешься. Вот выдержка из MSDN'овской статьи (еще по 7-ке):
Page splitting occurs when an index page or data page can no longer hold any new rows and a row needs to be inserted into the page because of the logical ordering of data defined in that page.
Обрати внимание на выделенное. В случае вставки в конец нет никакой необходимости дробить страницу — гораздо проще начать новую.
M> наполовину, а при guid'ах, скорее всего, окажется что страницы заполнены больше чем на половину, но всего страниц занято меньше, хотя распределение может оказаться и не столь равномерным.
Наполовину — это очень плохо для крупных баз. Нормальные филфакторы — 70-80%, если планируются довольно частые вставки. Иначе можно и больше.
M>Но количество расщеплений индекса будет в среднем одинаковым в обоих случаях.
Нет.
M>>>А читать внимательнее пробовал? L>>Я вроде внимательно читал и тоже не понял. M>Это я перенервничал, работать надо, а отвечать интереснее..
Здравствуйте, andsm, Вы писали:
A>Здравствуйте, Lexey, Вы писали:
L>>С чего бы это? Последняя страница одна, а выборки по чтению идут по всем. Вероятность того, что понадобиться именно последняя страница порядка 1/N (N-число страниц). При больших N она ничтожна.
A>Если в таблицу идут частые инсерты, то, при кластерном индексе по identity, вероятность того что потребуется последняя страница пропорциональна количеству вставляемых в секунду записей и обратно пропорциональна среднему размеру записи. И эта вероятность никак не зависит от числа страниц.
Чушь. Большиству читающих транзакций новые записи вообще не нужны (о их существовании еще просто никто не знает).
Здравствуйте, Merle, Вы писали:
L>>С чего бы это? Последняя страница одна, а выборки по чтению идут по всем. M>Ну, это действительно скорее проблема интенсивной вставки, но есть такой нюанс, что
Это тогда уж скорее проблема твоего конкретного случая.
M> в реальных очень часто нужны именно последние, только что вставленные данные. И простая вероятность тут мало что решает..
Как раз в нормальной жизни между появлением объекта в базе и появлением желающих его читать проходит какое-то время.
Здравствуйте, Lexey, Вы писали:
L>Как раз в нормальной жизни между появлением объекта в базе и появлением желающих его читать проходит какое-то время.
Мне что-то нормальной жизни довольно мало встречалось...
Здравствуйте, VladD2, Вы писали:
VD>Ссылки, плиз. Вот перевод МС-ного мануала: VD>http://www.optim.ru/cs/1999/2/sql7architecture/sql7architecture.asp
Это мануал MSSQL 7.0, в 2000 все поменяли. В семерке действительно была физическая упорядоченность, в 2000 от нее отказались.
VD>Еще раз. Ссылки и тесты в студию.
L>>>С некоторой долей вероятности? Это как? M>>Так, что физического упорядочивания записи нет, есть упорядоченность страниц и гарантия того, что запись "меньше" самой "большой" и "больше" самой маленькой записи страницы окажется на этой странице.
VD>См. выше.
VD>Что уж МС называет физическим порядком решать тебе.
Ну, пример того как на самом деле поступает сервер с кластерным индексом я уже привел... Глазам своим не веришь?
VD>Но как минимум записи в странице точно упорядочены. Или это уже не индекс.
Ничего они не упорядочены, точнее упорядочены логически. Какой смысл в физическом упорядочивании, если с диска поднимается вся страница за раз?
Достаточно гарантии того, что не придется прыгать из середины на другую страницу за следующей записью, эта гарантия есть, а больше ничего кластерный индекс не гарантирует, ну кроме того, что страницы будут сканироваться в логическом порядке, по указателям PrevPID, NextPID.
VD>по-моему, совершенно однозначно дают понять...
Да бога ради...
VD> Ну, и далее все будет ясно.
Делал неоднократно, в различных вариантах, для различных задач. Результаты примерно такие: При интенсивной вставке из параллельных транзакций в табличку с кластерным/некластерным guid'ом/identity индексом, разницы между guid и identity практически нет. Выборка сильно зависит от ситуации, но в среднем колеблется от 5 до 20% в пользу identity, когда количество записей приближается к нескольким миллионам.
Здравствуйте, Lexey, Вы писали:
L>Очень интересный результат. Статейку не хочешь на эту тему написать?
Да этой новости уже 5 лет, периодически статьи появляются на всяких sqlserverperfomance.com, ectю, когда кто-нибудь в очередной раз наталкивается на непонятки с кластерным индексом и делает открытие... Один вот, недавно, на sql.ru натолкнулся, тоже топик километровый наваял...
L>С этим все равно не согласен. Аргументы все те же — фрагментация индекса, меньшая плотность заполнения и cache misses.
Да, по поводу меньшей плотности заполнения и разбиения страниц — согласен.
L> Я говорил, что в случае вставки в конец заблокирована только одна страница, а у тебя таких страниц может быть пачка. Вероятность нарваться на блокировку при чтении у тебя больше.
Нет, все совсем не так. Этот latch очень и очень быстрая операция, когда эти latch'и размазаны по всей таблице из вообще никто не замечает, время ожидания мизерно. А при вставке в конец, из большого числа разных транзакций, последняя страница оказывается заблокирована постоянно, и не смотря на то, что блокировка очень короткая все равно выстраивается очередь на ресурс.
L>Кластеризации по неуникальному полю вообще не бывает. Сервер все равно сделает кластерный индекс уникальным.
Это уже частности.. Смысл в том, что физически записи нужные одной группе транзакций оказываются рядом и не пересекаются с записями нужными другим транзакциям.
L> Вот ты все про бизнес логику. А с чего ты решал, что бизнес-логике нужны именно такие запросы.
Да я и не говорю что всегда, но в большинстве случаев оказывается именно так.
L> Да, такие запросы бывают, но в тех базах, с которыми я работал, они составляли от силы процентов 30. И грузили они сервер не очень сильно. А основная нагрузка шла на join'ы, которые как раз и делаются по identity и напрямую выигрывают от его кластеризации.
Что-то с трудом верится... У вас все таблицы с отношением один к одному? Или при выборке из подчиненной в большинстве случаев FK не учавствует?
L>Т.е. композитный уникальный ключ приемлемой длины в данном случае не построишь?
Можно и построить, а зачем?
Здравствуйте, Lexey, Вы писали:
L>Равномерно — да, но заполненность у них при этом может получиться хреновой.
Ну, скорее всего как раз процентов 70-80, хотя и возможны флуктуации..
L>Обрати внимание на выделенное. В случае вставки в конец нет никакой необходимости дробить страницу — гораздо проще начать новую.
Да, скорее всего ты прав.
VD>Объясни тогда почеиму когда создаешь ПК на таблице mssql автоматом делает кластерный индекс? Они по твоему вредительством занимаются?
Потому что MS разрабатывался так, чтобы работать даже в кривых руках, и даже у людей, которые вообще не знают что такое индекс. А поскольку хоть какой-то кластерный индекс лучше, чем отсутствие кластерного индекса вообще, то они и лепят его по умолчанию на PK, чтобы не промазать.
VD>Любоая. Если на таблице нет кластеного уникального индекса, то любая ссылка будет букмарком, т.е. все индексы будут содержать букмарки в качестве идентификаторов строк. Если же на таблице есть уникальный кластерный индекс (УКИ), то в индексах будет использоваться именно он (см. BOL).
Все немного не так.
Для того что бы не было букмарков уникальности от кластерного индекса не требуется, сервер сам обеспечит эту уникальность если создать кластерный индекс по неуникальному полю, добавив в ключ числовой идентификатор. Но физически одинаковые записи окажутся рядом, на одной или соседних страницах, собственно ради этого все и затевается. Так что букмарка не будет по любому, при поиске по полю с кластерным индексом, в не зависимости от того уникально это поле или нет.
VD>Пробовал. Не помогает. Видимо нужно тщательнее излагать мысли.
Согласен, нужно..
Здравствуйте, VladD2, Вы писали:
VD>И оно снова ошибочное. МС сами советуют это делать.
Они не советуют, они просто пихают кластерный индекс по дефолту на PK, чтобы хоть какой-то был... И в данном случае я доверяю не БОЛ и визардам, а собственному опыту, который говорит, что кластеризация по PK бывает оправдана довольно редко.
VD>Не, ну, доказывая приемущества ГУИДов переходить на другие примеры как не очень верно с точки зрения логики.
Как я уже говорил, здесь я обсуждал вопрос очень слабо связанный со спором GUID vs identity, а именно необходимость кластеризации по PK.
VD>Если индекс кластерный, то букмарков не будет вообще, и стало быть не будет и связанных с ними накладных расходов.
Зато будут букмарки и гораздо более серьезные накладные расходы при диапазонных и массовых выборках.
Здравствуйте, VladD2, Вы писали:
VD>Только по-твоему. VD>Объясни тогда почеиму когда создаешь ПК на таблице mssql автоматом делает кластерный индекс? Они по твоему вредительством занимаются? VD>И вообще, можно хоть какие-то доказательства верности твоей теории? Ну, ссылки, результаты тестов... Re[13]: GUID и кластерны
Ну, и что? Разумные слова про то, что кластерные индексы выгодны при последовательной выборке еще не означают, что они плохи для ПК. Если 90% времени ты дергаешь таблицу по одному индексу, то логично будет сделать его кластерным. Но если таблица постоянно идет через джоин, то как раз ПК самый лучший выбор. Кластреные индексы не только ускоряют последовательную выборку. Они ускоряют и вообще выбогрку по индексу, так как не нужно лазить по таблице.
В общем, ты уже переключился с обсуждения своий идеи о выгодности GUID и теперь еще раз переключаешся. Ты все же сделай тест GUID vs. int+identity, а там уже осудим его результаты.
Я тебе с тем же успехом могу надыбать кучу цитат о выгодности identity в качестве ПК.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
VD>>Ссылки, плиз. Вот перевод МС-ного мануала: VD>>http://www.optim.ru/cs/1999/2/sql7architecture/sql7architecture.asp M>Это мануал MSSQL 7.0, в 2000 все поменяли. В семерке действительно была физическая упорядоченность, в 2000 от нее отказались.
Никаких существенных изменений с тех времен не было. Этот же мануал лежит в BOL-е от 2000-ого.
M>Re[11]: GUID и кластерны
Здравствуйте, Lexey, Вы писали:
M>Кластеризации по неуникальному полю вообще не бывает. Сервер все равно сделает кластерный индекс уникальным.
Но все же кластерый индекс можно создать и по неуникальному полю.
M> Вот ты все про бизнес логику. А с чего ты решал, что бизнес-логике нужны именно такие запросы. Да, такие запросы бывают, но в тех базах, с которыми я работал, они составляли от силы процентов 30. И грузили они сервер не очень сильно. А основная нагрузка шла на join'ы, которые как раз и делаются по identity и напрямую выигрывают от его кластеризации.
Вот именно. Причем чем более хорошо спроектирована БД, тем больше джоинов.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
L>> Да, такие запросы бывают, но в тех базах, с которыми я работал, они составляли от силы процентов 30. И грузили они сервер не очень сильно. А основная нагрузка шла на join'ы, которые как раз и делаются по identity и напрямую выигрывают от его кластеризации. M>Что-то с трудом верится... У вас все таблицы с отношением один к одному? Или при выборке из подчиненной в большинстве случаев FK не учавствует?
А сколько ФК можно кластеризовать на одной таблице? И как будет делаться поиск строки на втором и третьем ФК? Ну, и какова вероятность, что выборка будет всегда идти по тому первому ФК что оказался кластерным?
В общем, еще раз. Тесты в студию.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, VladD2, Вы писали:
VD>>Что уж МС называет физическим порядком решать тебе. M>Ну, пример того как на самом деле поступает сервер с кластерным индексом я уже привел... Глазам своим не веришь?
Ничего такого втом что ты привел нет. По крайней мере твои выводы точно с этих данных делать нельзя. А вот твои утверждения противоречат другим твоим же. В честности твоя идея о непоследовательности как-то плохо стэкуется с мыслью о том, что скорость последовательной выборки из кластерный индексов высокая.
VD>>Но как минимум записи в странице точно упорядочены. Или это уже не индекс. M>Ничего они не упорядочены, точнее упорядочены логически.
Гы.
1. Где подтерждение этому? Опровержение вроде постоянно появляются. Да и логике это противоречит. Индекс есть индекст. Его записи обязаны быть упорядочены.
2. Какя разница как физически что-то реализовано? Главное, что нет нужны заниматься сортировкой при считывании данных или заниматься не эффективным последовтельным перебором для поиска записи. Один фиг страница с большой вероятностью попадет в кэш процессора, а значит скорость будет приблизительно одинакова.
M> Какой смысл в физическом упорядочивании, если с диска поднимается вся страница за раз?
Не делать сотрировки при чтении. Это между прочем не быстрая операция. Вот какой смысл хранить данные неупорядоченно в индеске? Это действительно маразм. Как минимум нужно хотя бы сделать микро-индес внутри страницы.
M>Достаточно гарантии того, что не придется прыгать из середины на другую страницу за следующей записью, эта гарантия есть, а больше ничего кластерный индекс не гарантирует, ну кроме того, что страницы будут сканироваться в логическом порядке, по указателям PrevPID, NextPID.
Почитай какова будет стоимость поиска в линейном списке на одной странице, умож это число на количество считываемых страниц и получишь велечину бессмысленных накладных разсходов.
M>Делал неоднократно, в различных вариантах, для различных задач. Результаты примерно такие: При интенсивной вставке из параллельных транзакций в табличку с кластерным/некластерным guid'ом/identity индексом, разницы между guid и identity практически нет.
Я вот видил вставку из одноко потока. И разница была очень явная.
M> Выборка сильно зависит от ситуации, но в среднем колеблется от 5 до 20% в пользу identity, когда количество записей приближается к нескольким миллионам.
Ну, и что ты доказываешь? Или ГУИД-ы "рвануть" на выборке?
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
VD>>Объясни тогда почеиму когда создаешь ПК на таблице mssql автоматом делает кластерный индекс? Они по твоему вредительством занимаются? M>Потому что MS разрабатывался так, чтобы работать даже в кривых руках, и даже у людей, которые вообще не знают что такое индекс. А поскольку хоть какой-то кластерный индекс лучше, чем отсутствие кластерного индекса вообще, то они и лепят его по умолчанию на PK, чтобы не промазать.
Ну, и тестовые БД в них тоже криворукие делали?
VD>>Любоая. Если на таблице нет кластеного уникального индекса, то любая ссылка будет букмарком, т.е. все индексы будут содержать букмарки в качестве идентификаторов строк. Если же на таблице есть уникальный кластерный индекс (УКИ), то в индексах будет использоваться именно он (см. BOL). M>Все немного не так. M>Для того что бы не было букмарков уникальности от кластерного индекса не требуется, сервер сам обеспечит эту уникальность если создать кластерный индекс по неуникальному полю, добавив в ключ числовой идентификатор.
Предположим так. Какого по твоему мнению будет размер такоего составного ключа? И насколько он окажется эффекнивнее при лукапе чем кластерный индекс по уникальному поли целого типа?
M> Но физически одинаковые записи окажутся рядом, на одной или соседних страницах, собственно ради этого все и затевается. Так что букмарка не будет по любому, при поиске по полю с кластерным индексом, в не зависимости от того уникально это поле или нет.
Блин. Да раз в год по праздникам может оказаться нужно последовательно сканировать таблицу. А джоины делать нужно постоянно. И что будет эффекнивнее? Оптимизировть один запрос с сортировкой по полям совпадающим с КИ или десяток не совпадающих?
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, VladD2, Вы писали:
VD>Предположим так. Какого по твоему мнению будет размер такоего составного ключа?
Не на много больше чем обычного.
VD>И насколько он окажется эффекнивнее при лукапе чем кластерный индекс по уникальному поли целого типа?
При лукапе чего? Тут все от запросов зависит.
VD>Блин. Да раз в год по праздникам может оказаться нужно последовательно сканировать таблицу.
Ха, раз в год. сканировать куски таблицы и выбирать диапазоны приходится гораздо чаще и запросы эти самые критичные.
VD> А джоины делать нужно постоянно.
Джойны тоже разные бывают и в большинстве случаев это все тоже сканирование диапазона.
Здравствуйте, VladD2, Вы писали:
VD> Но если таблица постоянно идет через джоин, то как раз ПК самый лучший выбор.
Джоин чего? джойны тоже разные бывают, и как правило джойнится все тот же диапазон.
VD> Кластреные индексы не только ускоряют последовательную выборку. Они ускоряют и вообще выбогрку по индексу, так как не нужно лазить по таблице.
Правильно, но при выборке по диапазону лазить по таблице приходится гораздо дольше и потери в этом случае гораздо больше — как правило это самое узкое место, поэтому оно и является первым кандидатом на затыкание кластерным индексом.
VD>В общем, ты уже переключился с обсуждения своий идеи о выгодности GUID и теперь еще раз переключаешся.
Я не переключаюсь, я обсуждаю два, довольно мало связанных вопроса.
VD>Я тебе с тем же успехом могу надыбать кучу цитат о выгодности identity в качестве ПК.
Ну надыбай. Просто просмотрел первые попавшиеся статьи и книги у меня на диске, пока время было, все пишут о том, что PK вовсе не первый кандидат на кластеризацию.
Здравствуйте, VladD2, Вы писали:
VD>Никаких существенных изменений с тех времен не было.
Ну так, Storage Engine периписали, оптимизатор переписали, а больше действительно, особо серьезных изменений не было..
VD>Где там тесты?
Там показано, что реально кластерный индекс ничего физически не упорядочивает.
Здравствуйте, VladD2, Вы писали:
VD>Ничего такого втом что ты привел нет. По крайней мере твои выводы точно с этих данных делать нельзя.
Почему нельзя?
VD>В честности твоя идея о непоследовательности как-то плохо стэкуется с мыслью о том, что скорость последовательной выборки из кластерный индексов высокая.
Очень даже хорошо стыкуется. Страницы считываются с диска в логическом порядке, по ссылке. Из середины страницы прыгать не приходится.
Вообщем механизм вкраце описан в одной из цитат, что я приводил...
VD>1. Где подтерждение этому? Опровержение вроде постоянно появляются. Да и логике это противоречит. Индекс есть индекст. Его записи обязаны быть упорядочены.
Они логически упорядочены, ссылками друг на друга, а физически лишь от части, есть гарантия, что диапазон окажется на одной странице, но внутри страницы они все равно в перемешку.
VD>2. Какя разница как физически что-то реализовано?
Для вставки большая, данные можно пихать в любое свободное место на странице, пока оно есть.
VD>Не делать сотрировки при чтении.
А они и не делаются.
VD> Это между прочем не быстрая операция. Вот какой смысл хранить данные неупорядоченно в индеске? Это действительно маразм.
Это не маразм, а оптимизация вставки.
VD> Как минимум нужно хотя бы сделать микро-индес внутри страницы.
Вот это действительно маразм...
VD>Я вот видил вставку из одноко потока. И разница была очень явная.
Да никого не интересует вставка из одного потока, я для таких задач какой-нибудь MySQL возьму или FB embedded, они и сиквел и Оракл и DB2 порвут, всех вместе на таких задачах. Реально то с базой работает много потоков и каждому свое надо.
VD>Ну, и что ты доказываешь?
Что гуиды ничем не хуже identity.
Здравствуйте, VladD2, Вы писали:
VD>А сколько ФК можно кластеризовать на одной таблице? И как будет делаться поиск строки на втором и третьем ФК?
А это уже надо по задаче смотреть, какой кластерным, а какой не очень. В крайнем случае сделать так, чтобы на одну таблицу больше одного FK не приходилось.
VD>В общем, еще раз. Тесты в студию.
Да какие тесты? Реальные приложения поднимать и нагрузку эмулировать у меня времени нет. То что выбор по диапазонам с помощю кластерного оптимальнее — ты и сам знаешь. По моему опыту диапазонных запросов — большинство и оптимизировать их не в пример сложнее, чем выборку по PK...
Здравствуйте, KisA, Вы писали:
KA>Мне кажется ты тут общими фразами не отделаешься, нужно разжёвывать, или ты просто хотел растянуть удовольствие?
Да какое тут удовольствие..
KA>В общем, ни хрена в MS-SQL не смыслю но скажу из общетехнических соображений, про джойны:
В Оракле, если я правильно помню, IOT (что есть аналог кластерного индекса в MSSQL) тоже не по PK рекомендуют делать, примерно из тех же соображений.
KA>3) (вариант 2, но кластерный индекс по другому FK) Пусть у нас теперь нет кластерного индекса по FK2_PK1, а есть по некому другому FK. KA>Выборка из первой таблицы таже, и для каждой строки из tab2 потребуется выполнить по 3 чтения страницы индекса по fk2_pk1 и сбегать к 10 блокам по 10 ссылкам. KA>Итого: KA>3+100 + (3 + 10) * 100 = 1403
Если в первой (master) табличке кластеризация по selection1 то понадобится не 3+100, а 3+1 чтений (если у нас 3й вариант такой же как второй, а не как первый ). Тогда общая стоимость будет 3+1 + (3 + 10) * 100 = 1304 — все хлеб.
Плюс, не следует забывать, что чтение диапазонов с использованием кластерного индекса более эффективно ввиду использования "Read Ahead". То есть, грубо говоря, при удачном стечении обстоятельств 403 чтения второго варианта выполнятся оптимальнее, чем просто 403 букмарк лукапа.
KA>В итоге KA>1) при применение кластерных индексов по PK проигрышь по предпологаемому объему логического i/o в примере c соединением составил 4,5 раза. При увеличении количества "дочерних" записей он будет возрастать. KA>2) При отсутствии кластерного индекса по FK участвуещему в соединении разницы между вариантом соединения где кластеризация идет по PK ( вариант 1 ) и по другому полю ( вариант 3) не ожидается.
Спасибо, прмерно это я и имел ввиду, просто с Владом спорить бывает очень утомительно, да и на работе завал...
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, KisA, Вы писали:
KA>>В общем, ни хрена в MS-SQL не смыслю но скажу из общетехнических соображений, про джойны: M>В Оракле, если я правильно помню, IOT (что есть аналог кластерного индекса в MSSQL) тоже не по PK рекомендуют делать, примерно из тех же соображений.
К сожелению это не так, кроме как по PK нельзя сделать
Oracle® Database Concepts 10g Release 1 (10.1) Part Number B10743-01 Overview of Index-Organized Tables:
An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
Отсюда приминимость IOT в Oracle, на мой взгляд, достаточно мала.
KA>>3) (вариант 2, но кластерный индекс по другому FK) Пусть у нас теперь нет кластерного индекса по FK2_PK1, а есть по некому другому FK. KA>>Выборка из первой таблицы таже, и для каждой строки из tab2 потребуется выполнить по 3 чтения страницы индекса по fk2_pk1 и сбегать к 10 блокам по 10 ссылкам. KA>>Итого: KA>>3+100 + (3 + 10) * 100 = 1403 M>Если в первой (master) табличке кластеризация по selection1 то понадобится не 3+100, а 3+1 чтений (если у нас 3й вариант такой же как второй, а не как первый ). Тогда общая стоимость будет 3+1 + (3 + 10) * 100 = 1304 — все хлеб.
Я на самом деле сначала написал два варианта ( с кластеризацией по selection1 и без), но потом побоявшись запутать людей оставил один, но похоже запутал этим больше.
P.S. Чего то коды форматирования внизу страницы не срабатывают, приходится вводить руками, это только у меня так или что то "улучшили"?
Здравствуйте, Merle, Вы писали:
D>>Предположим так. Какого по твоему мнению будет размер такоего составного ключа? M>Не на много больше чем обычного.
Не на много это в два раза, если ключ целочисленный.
VD>>И насколько он окажется эффекнивнее при лукапе чем кластерный индекс по уникальному поли целого типа? M>При лукапе чего?
Записи, записи. Напомню, что по кластерному индексу производится выборка при навигации по любому индексу, так как в некластерных хранится именно ключ кластерного.
M> Тут все от запросов зависит.
Ничего тут ни от чего не зависит. Уникальный целочисленный ключ будет иметь развем 4 байта. Навигация по нему (на 32-битных платформах) будет всегда быстрее чем по любому другому, а его размер меньше (а стало быть и размер всех остальных индексов).
VD>>Блин. Да раз в год по праздникам может оказаться нужно последовательно сканировать таблицу. M>Ха, раз в год. сканировать куски таблицы и выбирать диапазоны приходится гораздо чаще и запросы эти самые критичные.
Ты все таблицы тлько одним способом выбираешь? Нет? Ну, тогда должен понимать, что сделав кластерный индекс по левому полю ты получишь ускорение при выборке по ниму, и замедлишь все остальные выборки. Бесспорно иногда это оправданно. Но возводить это в ранг правила в корне не верно.
VD>> А джоины делать нужно постоянно. M>Джойны тоже разные бывают и в большинстве случаев это все тоже сканирование диапазона.
Нет. Джоины в большинстве случаев — это лукап по ПК (если конечно БД спроектирована нормально). Причем вариантов джоинов обычно много, и на все ФК кластерных индексов не сделаешь. Так что как раз КИ на ПК — это отличное решение по умолчанию. Вот если есть явный перекос в сторону одного из видов запросов, то можно подумать инад изменением схемы.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, VladD2, Вы писали:
VD>Не на много это в два раза, если ключ целочисленный.
Это "в два раза" все равно не много.
VD>Записи, записи. Напомню, что по кластерному индексу производится выборка при навигации по любому индексу, так как в некластерных хранится именно ключ кластерного.
Не навигация по кластерному индексу, а ссылка на запись представляет из себя ключ кластерного индекса.
VD>Ничего тут ни от чего не зависит.
Зависит.
VD> Уникальный целочисленный ключ будет иметь развем 4 байта. Навигация по нему (на 32-битных платформах) будет всегда быстрее чем по любому другому, а его размер меньше (а стало быть и размер всех остальных индексов).
Размер ключа для 32 битной платформы, вообще роли не играет, так как эти потери, попросту другой порядок малости по сравнению с дисковыми операциями. Все упирается в них. Для дисковых операций разница размера ключа в несколько байт практически ничего не значит. Заметный проигрыш можно получить при разнице в размерах ключа в сотню байт и размерах таблиц в миллионы и десятки миллионов записей.
VD>Ты все таблицы тлько одним способом выбираешь? Нет? Ну, тогда должен понимать, что сделав кластерный индекс по левому полю ты получишь ускорение при выборке по ниму, и замедлишь все остальные выборки.
Оно не левое. Это PK левый.
VD> Бесспорно иногда это оправданно. Но возводить это в ранг правила в корне не верно.
Я в ранг правила возвожу другое: "если на таблице помимо PK есть другие индексы, то вероятнее всего эти другие индексы стоит сделать кластерными, а не PK"
VD>Нет. Джоины в большинстве случаев — это лукап по ПК (если конечно БД спроектирована нормально).
Покажи мне такой джойн. Или у меня все базы не правильные?
Еще раз: ПК — уникальный, лукап за одной записью ничего не стоит. Выборка по некластерному диапазону означает лукап за всеми записями этого диапазона, а это уже дорогая операция. Для примера, если у нас в запросе в диапазон попадает 5 записей — это означает 5 полноценных букмарк-лукапов (поскольку с хорошей вероятностью все пять записей сидят на разных страницах), а значит диапазонный запрос оказался в пять раз дороже. Таким образом, если выборка по ПК более чем в пять раз чаще, чем выборка по диапазону, фиг с ним, пусть будет кластерным, но в реальных задачах обычно бывает наоборот, и в диапазон попадает не пять записей, а гораздо больше.
Поэтому я и говорю (и не только я, как выяснилось), что ПК один из наименее вероятных кандидатов на кластеризацию.
Плюс к этому, правильно выбранный кластерный индекс позволяет размазать нагрузку по таблице и физически разнести разные транзакции от греха подальше.
VD> Причем вариантов джоинов обычно много, и на все ФК кластерных индексов не сделаешь.
На все и не надо, надо на один, самый критичный.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, VladD2, Вы писали:
VD>>А сколько ФК можно кластеризовать на одной таблице? И как будет делаться поиск строки на втором и третьем ФК? M>А это уже надо по задаче смотреть, какой кластерным, а какой не очень. В крайнем случае сделать так, чтобы на одну таблицу больше одного FK не приходилось.
Сдается мне, что фантазировать нужно меньше. Количество ФК определяется потребностями дизайна. Из-за смутных предпочтений АБД вряд ли кто станет менять дизайн БД.
А насчет того, что на задачу нужно смотреть тебе сразу сказали. Это ж ты даешь универсальный совет "Гуиды на ПК, и ни в коем случае не делать ПК кластерным...".
VD>>В общем, еще раз. Тесты в студию. M>Да какие тесты?
Очень просты. ПК в иде ГУИД-а и без кластра вс. Инт + КИ. Плюс табличек 5 связанных между собой и набор запросов по ним. Естественно хотя бы в одной табличне нужно залить пару миллионов данных (чтобы хоть было видно не вооруженным взглядом).
M> Реальные приложения поднимать и нагрузку эмулировать у меня времени нет.
Там и без нагрузки будет все отчетливо вдино. Даже одиночный запрос будет отличаться.
M> То что выбор по диапазонам с помощю кластерного оптимальнее — ты и сам знаешь.
Ну, вот и покажешь как это будет выглядеть при реальных джоинах.
M> По моему опыту диапазонных запросов — большинство и оптимизировать их не в пример сложнее, чем выборку по PK...
Ну, поехали еще в одну степь. Не нужно отвлекаться. Речь идет об общей производительности, а не одного специально оптимизированного запроса.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, KisA, Вы писали:
KA>Мне кажется ты тут общими фразами не отделаешься, нужно разжёвывать, или ты просто хотел растянуть удовольствие?
KA>В общем, ни хрена в MS-SQL не смыслю но скажу из общетехнических соображений,
Хорошая аргументация. Сам я подсудимого не знал, но как и весь Советский наро...
Матиматика тоже забавная. Правда не учитывает кое чего и кое в чем основывается на недоказанных предположениях, но выглядит внушитешьно. Остается только доказать состоятельность всех рассчетов. Сделай таблички и померяй скорость. Причем таблички сделай не две, а хотя бы пять и джоинов не один, а тоже пять или десять. Померяй скорость и потом расскажи свои ощущения.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Спасибо, прмерно это я и имел ввиду, просто с Владом спорить бывает очень утомительно, да и на работе завал...
С владом не нжуно спорить. Ему нужно предъявлять разумные не противоречивые аргументы и факты. В данном случае аргументы не убедительны. Самое убедительное — это банальный тест. Хочешь убедить сделай его и вопрос отпадет. А до тех пор я лично вижу кучу ошибок в логических рассуждениях.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
VD>> Как минимум нужно хотя бы сделать микро-индес внутри страницы. M>Вот это действительно маразм...
Маразм... Нда. Веский аргумент ничего не скажешь. Значит по твоему тупой перебор быстрее будет?
VD>>Я вот видил вставку из одноко потока. И разница была очень явная. M>Да никого не интересует вставка из одного потока, я для таких задач какой-нибудь MySQL возьму или FB embedded, они и сиквел и Оракл и DB2 порвут, всех вместе на таких задачах. Реально то с базой работает много потоков и каждому свое надо.
В большинстве OLTP-систем вставка не такое уж частое действие. И конкурентная вставка может вообще быть рдкостью, а вот выборка идет постоянно. И ее в разы больше. Бывают конечно исключения, но они все же не так часты.
VD>>Ну, и что ты доказываешь? M>Что гуиды ничем не хуже identity.
Вот только в твоих словах все меньше и меньше о ГУИД-ах. И все больше и больше ухода от темы.
Открой любой TPC-C тест и погляди какие там типы данных используются. Если хоть один ГУИД найдешь можно будет дальше обсудить эту теорию, а так в эту сказку я без тестов не поверю.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, VladD2, Вы писали:
VD>>Никаких существенных изменений с тех времен не было. M>Ну так, Storage Engine периписали,
Ничего там серьезно не переписывали.
M> оптимизатор переписали,
Он тут вообще никакой роли не играет.
M> а больше действительно, особо серьезных изменений не было..
Текст который я привел до сих пор лежит в BOL.
VD>>Где там тесты? M>Там показано, что реально кластерный индекс ничего физически не упорядочивает.
А какая разница физически не физически? Факт в том, что кластерный индекс намного экономичнее обычного (хотя бы потому, что обычный один фиг будет еще и кластерным пользоваться). И факт в том, что кластерный индекс должен быть на тех полях по которым чаще всего делается выборка. А это с обычно и есть ПК. Для некоторых таблиц это не так вот на них можно и заняться оптимизацией. В остальных же ПК — это лучший выбор. Иначе ты ускоришь один запрос, а все остальные замедлишь. Гуидя же — это вообще никому ненужный удар по производительности. Применять их в обычных случях можно только от большой к ним любьви. Вот если нужна репликация или это резко упрощает реализацию какого-нить сервера приложений с O/R-мапингом, то другое дело. Но нужно понимать, что за это платишь решьной производительностью и объемами данных. Любой индекс при этом увеличится минимум в четверо. Нафиг оно надо?
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, VladD2, Вы писали:
VD>> Но если таблица постоянно идет через джоин, то как раз ПК самый лучший выбор. M>Джоин чего? джойны тоже разные бывают, и как правило джойнится все тот же диапазон.
Джоины в большинстве случаев идут с ПК на ФК. Криворуких программистов и АБД в рассчет не берем. Причем ПК то на таблице будет один, а вот ФК будет скоре всего куча. И если это так, и нет явного перекоса в сторону выборки по одному ФК, то смысла делать кластерный индекс на этом ФК нет. А вот КИ на ПК при этом даст повышение производительности, так как лукап по ПК будет наиболее шустрым, ПК, а стало быь и ссылки на записи в обычных индексах будут иметь минимальный размер (и будут совпадать со словом процессора). Стало быть вся система станет быстрее. Собственно потому МС и делает по умолчанию на ПК КИ.
VD>> Кластреные индексы не только ускоряют последовательную выборку. Они ускоряют и вообще выбогрку по индексу, так как не нужно лазить по таблице. M>Правильно, но при выборке по диапазону лазить по таблице приходится гораздо дольше и потери в этом случае гораздо больше — как правило это самое узкое место, поэтому оно и является первым кандидатом на затыкание кластерным индексом.
Блин. И что даст ускорение выборки по одному ФК если таблица постоянно лукапится по ПК и на нае есть еще с десяток ФК? Да, один запрос сужественно ускоряется. А 100 других замедляется.
Представь себе справочник в тотором, ну, 1000-3000 записей. Его прямая выборка с сотрировкой не представляет никаих проблем. Даже отсуствие индекса по текстовому полю не проблема, ведь сортировка таких объемов не проблема. Но справочник очень часто используется по ссылке в других таблицах. При этом справочник постоянно участвует в джоинах. Причем так как объемы в нем никакие, то сервер постоянно лупапит по его ПК. Далее мы следуя твоему мудрому совету делаем КИ по текстовому полю аргументируя это тем, что мол это даст нам невероятное увеличение скорости фильтрации справочника. А на деле мы получаем огромные объемы скана по НКИ с лукапом по текстовому полю. В итоге мелкая табличка становится причиной снижения общего быстродействия системы на несколько процентов. И все почему? А потому что мы следуем идеи панацеизма. Далее мы следуя другому мудрому совету меняем целочисленный ID на гуид и получаем четырехкратное увеличение объема индеков (ФК-шных) во всех таблицах ссылающийся на наш справочник плюс увеличение индексов самой таблицы, да и размера самой таблицы (хотя по стравнению с огромными ссылающимися таблицами это уже все фигня). Итоно получаем нехилый удар по производительности. Уря товарищи! За то мы выполнили все мудрые советы!
Да бесспорно, что в некоторых случаях и КИ может быть выгодно сделать на ФК. Например, таблицы в которых хранятся нижнии уровни иерархий вроде таблиц товаров очень даже могут выиграть от этого, так как в них большой процент выборки делается именно по ФК, а доступ по ПК обычно ведется по одной записи. Но таких таблиц 2-3, ну 5. А правило то мы предлагаем как универсальное.
M>Ну надыбай.
Из БОЛ я тебе уже приводил.
M> Просто просмотрел первые попавшиеся статьи и книги у меня на диске, пока время было, все пишут о том, что PK вовсе не первый кандидат на кластеризацию.
Но все же кандидат? Ты читаешь об оптимизации, и воспринимаешь эти слова как паттерн который нужно использовать по умолчанию. В общем, выше я все сказал и повторться не охота.
ЗЫ
Самое забавное, что твои слова будут расценены многими не опытными людьми как призыв вообще не исполозовать кластерный индекс. А без него скорость работы будет принципиально хуже. В общем, не подменяй идею частной оптимизации для конкретной таблицы и паттерн для повседневного применения. Именно ПК первый претендент, а если найдутся претенденты по лучше, то нужно все как следует взвесить, лучше даже провести эксперементы, и уже тогда принимать решение о переносе КИ на ФК или еще куда.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Кстати, приведи год выпуска книг и версии серверов о которых идет речь. А то не ясно о каких хотспотах идет речь применительно к sql2k где есть блокировка на строку, да и в семерке где есть блокировка на строку при вставке.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
L>>>>В описанное верится очень слабо. M>>>Тем не менее это так. L>>Конкретный пример и цифры в студию, pls (если есть). Пока мой здравый смысл утверждает ровно обратное. M>Вот цитаты из умных книжек: M>Ken Henderson, "Guru's Guide To T-SQL":
Что-то закрались у меня сегодня сомнения насчет чистоты выводов этого эксперимента.
M>Никакого физического упорядочивания нет и в помине, страницы расположены, как байт на душу положит.
В общем, я так понимаю, что упорядочивание страниц никто и не обещал. Главное, что они слинкованы в правильном порядке.
M>Смотрим первую страницу:
Судя по prevPageID = 346, это не первая страница.
M>
Ну и что? А внутри страницы данные похоже упорядочены.
Вот если окажется, что внутри одной страницы порядок записей может быть любым, тогда твое утверждение будет верным.
VD>Джоины в большинстве случаев идут с ПК на ФК.
Правильно.
VD> Причем ПК то на таблице будет один, а вот ФК будет скоре всего куча.
Правильно.
VD>И если это так, и нет явного перекоса в сторону выборки по одному ФК,
Что значит перекос в сторону выборки по одному ФК?
VD> то смысла делать кластерный индекс на этом ФК нет.
Как раз в этом случае смысл есть.
VD> А вот КИ на ПК при этом даст повышение производительности, так как лукап по ПК будет наиболее шустрым, ПК, а стало быь и ссылки на записи в обычных индексах будут иметь минимальный размер (и будут совпадать со словом процессора). Стало быть вся система станет быстрее.
Все не так. ПК — один, один раз сходить за записью — не проблема, FK много, много раз ходить за записью накладно, поэтому имеет смысл делать кластерным FK.
VD>Собственно потому МС и делает по умолчанию на ПК КИ.
MS делает ПК кластерным вовсе не по этому, я уже писал почему, и не только я.
VD>Блин. И что даст ускорение выборки по одному ФК если таблица постоянно лукапится по ПК и на нае есть еще с десяток ФК?
Ты чего-то путаешь. Таблица по ПК не постоянно не ищется. И при чем здесь ФК на таблицу? Если есть ФК у таблицы то он, скорее всего один.
VD>Да, один запрос сужественно ускоряется. А 100 других замедляется.
Никто не замедлится.
VD>Представь себе справочник в тотором, ну, 1000-3000 записей.
Эээээ... Справочник — это особый случай, ты же не будешь утверждать, что правильная база вся состоит из справочников? Скорее наоборот. Справочники — это скорее исключение.
Больше как раз запросов таких, как привел KisA: Re[16]: GUID и кластерны
VD> Итоно получаем нехилый удар по производительности. Уря товарищи! За то мы выполнили все мудрые советы!
Ну не надо передергивать, я нигде не говорил, что всегда надо использовать не ПК? Я говорил, что ПК наименее вероятный кандидат.
VD>Из БОЛ я тебе уже приводил.
В БОЛ из пяти или шести примеров ПК только один.
VD>Но все же кандидат?
Я где-то утверждал обратное?
VD>Самое забавное, что твои слова будут расценены многими не опытными людьми как призыв вообще не исполозовать кластерный индекс.
Ну нееет, это ж каким не опытным надо быть..
VD> А без него скорость работы будет принципиально хуже.
Совершенно верно.
VD> В общем, не подменяй идею частной оптимизации для конкретной таблицы и паттерн для повседневного применения.
Паттерн для повседневного применения заключается в том, чтобы не пихать тупо кластерный индекс на ПК, о чем я и говорил с самого начала.
VD>Именно ПК первый претендент,
Последний. Ну предпоследний, за вычетом текстовых и длинных варчарных полей.
Здравствуйте, VladD2, Вы писали:
VD> А до тех пор я лично вижу кучу ошибок в логических рассуждениях.
Да бога ради, Влада убеждать просто не интересно, он последний раз в серьез дело с сиквелом имел, когда я его еще в глаза не видел
А кого надо я уже давно убедил.
Здравствуйте, VladD2, Вы писали:
VD>Кстати, приведи год выпуска книг и версии серверов о которых идет речь. А то не ясно о каких хотспотах идет речь применительно к sql2k где есть блокировка на строку, да и в семерке где есть блокировка на строку при вставке.
Во всех цитатах, за исключением одной речь идет о 2k, в одной о 7.
Года выпуска с 2000 по 2003, где 7 там не знаю, отдельная глава про индексы взятая с сайта MS.
Здравствуйте, VladD2, Вы писали:
VD>Ничего там серьезно не переписывали.
Ты это сотрудникам MS расскажи.. Почему-то я им больше верю..
VD> И факт в том, что кластерный индекс должен быть на тех полях по которым чаще всего делается выборка.
На сколько чаще?
VD> А это с обычно и есть ПК.
Это не так. Так только в справочниках и некоторых других специальных случаях.
VD> Гуидя же — это вообще никому ненужный удар по производительности.
Да нет там никакого удара.
VD>Применять их в обычных случях можно только от большой к ним любьви. Вот если нужна репликация или это резко упрощает реализацию какого-нить сервера приложений с O/R-мапингом, то другое дело.
Никогда нельзя быть уверенным, что какой-то таблице не потребуется репликация.
Здравствуйте, VladD2, Вы писали:
VD>Маразм... Нда. Веский аргумент ничего не скажешь. Значит по твоему тупой перебор быстрее будет?
Это твой аргумент.
VD>Вот только в твоих словах все меньше и меньше о ГУИД-ах. И все больше и больше ухода от темы.
Я лишь отвечаю на твои возражения.
VD>Открой любой TPC-C тест и погляди какие там типы данных используются. Если хоть один ГУИД найдешь можно будет дальше обсудить эту теорию, а так в эту сказку я без тестов не поверю.
В tpc-c тесты примитивны, там пять таблиц и десять запросов.
VD> ни в коем случае не делать ПК кластерным...".
А вот этого я не говорил.
VD>Очень просты. ПК в иде ГУИД-а и без кластра вс. Инт + КИ. Плюс табличек 5 связанных между собой и набор запросов по ним. Естественно хотя бы в одной табличне нужно залить пару миллионов данных (чтобы хоть было видно не вооруженным взглядом).
И ничего этот тест не покажет.
VD>Ну, вот и покажешь как это будет выглядеть при реальных джоинах.
При реальных джойнах тебе уже показали.
VD>Ну, поехали еще в одну степь. Не нужно отвлекаться. Речь идет об общей производительности, а не одного специально оптимизированного запроса.
Это ты поехал, а я по прежнему про оптимальность выбора ПК на конкретной таблице.
Здравствуйте, Merle, Вы писали:
VD>>Ничего там серьезно не переписывали. M>Ты это сотрудникам MS расскажи.. Почему-то я им больше верю..
Ну, у нас закрытой информации нет. А в открыто нет ни одного слова о изменении. Я уже устал говорить, что вся информация в БОЛ. Если БОЛ врет, то я уже мало чем могу помочь.
VD>> И факт в том, что кластерный индекс должен быть на тех полях по которым чаще всего делается выборка. M>На сколько чаще?
На 324.
VD>> А это с обычно и есть ПК. M>Это не так. Так только в справочниках и некоторых других специальных случаях.
Таких специализированных случаев 70%.
VD>> Гуидя же — это вообще никому ненужный удар по производительности. M>Да нет там никакого удара.
Цифры в студию. А до тех пор я все же буду верить банальной логике. В четыре раза больше данных == минимум в 4 раза медленее. И минимум в 4 раза болше данных.
M>Никогда нельзя быть уверенным, что какой-то таблице не потребуется репликация.
Вот когда понадобится, тогда и введеш еще один ключик в одну таблицу. А совать на этом основании всюду гуиды не разумно.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Во всех цитатах, за исключением одной речь идет о 2k, в одной о 7. M>Года выпуска с 2000 по 2003, где 7 там не знаю, отдельная глава про индексы взятая с сайта MS.
И какие нафиг хотспоты при вставке в семерке и 2000-ом? Блокировки на строки уже отменили что ли?
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
VD>>И если это так, и нет явного перекоса в сторону выборки по одному ФК, M>Что значит перекос в сторону выборки по одному ФК?
Значит, что в 60% случаев и более делается выборка по ФК.
VD>> то смысла делать кластерный индекс на этом ФК нет. M>Как раз в этом случае смысл есть.
В каком? Речь идет о равномерном использовании ФУ и ПК... в случае где нет явно часто используемого одного способа выборки данных... т.е. в 90% случаев.
VD>> А вот КИ на ПК при этом даст повышение производительности, так как лукап по ПК будет наиболее шустрым, ПК, а стало быь и ссылки на записи в обычных индексах будут иметь минимальный размер (и будут совпадать со словом процессора). Стало быть вся система станет быстрее. M>Все не так. ПК — один, один раз сходить за записью — не проблема, FK много, много раз ходить за записью накладно, поэтому имеет смысл делать кластерным FK.
Нда. Логика на грани фантастики. "FK много" ... "поэтому имеет смысл делать кластерным [b]один[/b]] FK".
А ПК тем временм будет лукапиться в два приема. И джоины на него будут дормозить.
VD>>Собственно потому МС и делает по умолчанию на ПК КИ. M>MS делает ПК кластерным вовсе не по этому, я уже писал почему, и не только я.
А. Ну, да. Вокруг все идеоты... в МС тоже... От того видимо во всех их БД ПК тоже кластерные.
VD>>Блин. И что даст ускорение выборки по одному ФК если таблица постоянно лукапится по ПК и на нае есть еще с десяток ФК? M>Ты чего-то путаешь.
Или ты.
M> Таблица по ПК не постоянно не ищется.
Блин. Ну, сделай джоин на этот самый ПК и погляди. В БД 1-2 таблицы которые не соеденяются по ПК.
M> И при чем здесь ФК на таблицу? Если есть ФК у таблицы то он, скорее всего один.
Это ж почему он один? Где ты такие БД берешь? В таблице может быть дцат полей ссылочного типа. Каждый из них будет ФК.
VD>>Да, один запрос сужественно ускоряется. А 100 других замедляется. M>Никто не замедлится.
Ну, ну. В общем, надоело. Делай тесты поглядим. А так пустая трата времени. Рассказы по таблицы с одним ФК к которым редко обращаются по ПК меня как то не убеждают.
VD>>Представь себе справочник в тотором, ну, 1000-3000 записей. M>Эээээ... Справочник — это особый случай, ты же не будешь утверждать, что правильная база вся состоит из справочников?
Порой к справочникам можно отнести все таблицы кроме 3-5 где лежит основная информация. Возми к примеру БД нашего сайта. Там есть две таблицы где лежат основные данные и море справочных таблиц.
M> Скорее наоборот. Справочники — это скорее исключение.
Ну, т.е. ты держишь в голове какую-то свою модешь БД для которой и предлагаешь решения. При этом другие они все исключения. А я вот не вдел БД где справочники не были бы в большом количестве.
VD>> Итоно получаем нехилый удар по производительности. Уря товарищи! За то мы выполнили все мудрые советы! M>Ну не надо передергивать, я нигде не говорил, что всегда надо использовать не ПК? Я говорил, что ПК наименее вероятный кандидат.
А я и не передергиваю. Я просто уверен, что послушав тебя не очень опытные орлы так и сделают. Ты не раз тут заявлял, что делать КИ по ПК плохое решение и что КИ на ФК — это верный выбор в большинстве случаев. Теперь вот утверждаешь еще, что один ФК на таблицу — это самое частно втречающийся случай и что мол все таблицы нужно делать такими плюя на понятия вроде дизайна. Если бы ты сказал: "Иногда <в таких то условиях> выгоднее сделать КИ на ФК по причине того, что чтение КИ быстнее.", то с тобой никто и не стал бы спорить. А ты возвел этот в догму. Попутно наговорив на идентети и воспев ГУИД-ы.
VD>>Из БОЛ я тебе уже приводил. M>В БОЛ из пяти или шести примеров ПК только один.
По крайней мере в БД нет бреда про хотспоты при вставке в одну таблицу и есть разумная аргументация.
VD>>Но все же кандидат? M>Я где-то утверждал обратное?
По-моему постоянно. Слова худший выбор вроде точно звучали.
VD>>Самое забавное, что твои слова будут расценены многими не опытными людьми как призыв вообще не исполозовать кластерный индекс. M>Ну нееет, это ж каким не опытным надо быть..
И тем не менее именно так и будет. Моло кто обратит внимание, на тонкости организации КИ и на то, что они все равно остаются уникальными и исползуются в других индексах.
VD>> В общем, не подменяй идею частной оптимизации для конкретной таблицы и паттерн для повседневного применения. M>Паттерн для повседневного применения заключается в том, чтобы не пихать тупо кластерный индекс на ПК, о чем я и говорил с самого начала.
А по-моему как раз нужно тупо пихать кластерный индекс в ПК и если есть более подходящий кандидат, менять на него. Но это я уже говорил.
VD>>Именно ПК первый претендент, M>Последний. Ну предпоследний, за вычетом текстовых и длинных варчарных полей.
В общем, ты явно не верно понимашь принципы оптимизации.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
VD>> ни в коем случае не делать ПК кластерным...". M>А вот этого я не говорил.
И чем это отличается от заявления что ПК последний притендент на кластерный индекс?
VD>>Очень просты. ПК в иде ГУИД-а и без кластра вс. Инт + КИ. Плюс табличек 5 связанных между собой и набор запросов по ним. Естественно хотя бы в одной табличне нужно залить пару миллионов данных (чтобы хоть было видно не вооруженным взглядом). M>И ничего этот тест не покажет.
Он покажет твою не правоту. Я просто уверен, что и скорость вставки и темболее скорость выборки, а уж объем данных подавно, будут значительно хуже у твоего решения. Да похоже ты и сам это понимашь.
VD>>Ну, вот и покажешь как это будет выглядеть при реальных джоинах. M>При реальных джойнах тебе уже показали.
Где? Что-то ты путаешь.
VD>>Ну, поехали еще в одну степь. Не нужно отвлекаться. Речь идет об общей производительности, а не одного специально оптимизированного запроса. M>Это ты поехал, а я по прежнему про оптимальность выбора ПК на конкретной таблице.
А я про скорость джоинов (всех а не одного). И про не оптимальность ГУИД-ов.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
VD>> А до тех пор я лично вижу кучу ошибок в логических рассуждениях. M>Да бога ради, Влада убеждать просто не интересно,
Не. Как раз очень интересно. Я бы даже сказал, если ты убедил Влада, то значит привел дейсвительно неоспоримые и веские доказательства, а не растекса мыслью по древу.
M> он последний раз в серьез дело с сиквелом имел, когда я его еще в глаза не видел
Ну, предположим он у меня постоянно крутится. Но видел его я действительно раньше тебя. Мы и 7-ку и 2000 еще в бетаверсиях тестировали. 2000-ый еще тогда 7.5 назывался. Так что изменерия членов будут не в твою пользу.
M>А кого надо я уже давно убедил.
Понятно агитация с целью обращения в свою веру.
... << RSDN@Home 1.1.4 beta 2 >>
Есть логика намерений и логика обстоятельств, последняя всегда сильнее.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, KisA, Вы писали:
KA>>Отсюда приминимость IOT в Oracle, на мой взгляд, достаточно мала. M>Упс, странно... А причины такого ограничения известны? И что, в Оракле PK — особенный? Или тут имелась ввиду уникальность? тогда ничего страшного...
Уникальность и not null, внутренний идентификатор записи ( RowId ) для IOT строится как функция от ключа по которому строится IOT, соответственно этот ключь должен быть уникальным, а поскольку null ключи Oracle в индексе не хранит, то null значения не разрешены. Т.е Unique+NOT NULL = "primary key sorted manner".
Как я понял в отличии от кластерного индекса в mssql,в IOT идет реальное упорядочивание внутри блока.
Здравствуйте, VladD2, Вы писали:
VD>И какие нафиг хотспоты при вставке в семерке и 2000-ом? Блокировки на строки уже отменили что ли?
Еще раз. При добавлении ключа в страницу индекса блокируется вся страница индекса. Эта кратковременная низкоуровневая блокировка (latch) держится только на время вставки, а не до конца транзакци.
KA>>В общем, ни хрена в MS-SQL не смыслю но скажу из общетехнических соображений,
VD>Хорошая аргументация. Сам я подсудимого не знал, но как и весь Советский наро...
Аргументация шла ниже, а это , типа, литературное вступление
VD>Матиматика тоже забавная. Правда не учитывает кое чего и кое в чем основывается на недоказанных предположениях, но выглядит внушитешьно.
"если кто то, кое где у нас порой...", так раскройте как кое что кое в чём учтенное кое как изменит кой какую ситуацию.
Опять же какие предположения требуется доказать, попробуем доказать ну или дать ссылку.
VD>Остается только доказать состоятельность всех рассчетов. Сделай таблички и померяй скорость. Причем таблички сделай не две, а хотя бы пять и джоинов не один, а тоже пять или десять. Померяй скорость и потом расскажи свои ощущения.
Боюсь, что измерения на Oracle тебя не устроят
Здравствуйте, KisA, Вы писали:
KA> Т.е Unique+NOT NULL = "primary key sorted manner".
Ну тогда можно в ручную делать то, что делает MSSQL, то есть искуственно добавлять уникальность в случае необходимости...
KA>Как я понял в отличии от кластерного индекса в mssql,в IOT идет реальное упорядочивание внутри блока.
А вот это уже хуже, в некоторых случаях вставка и модификация могут серьезно проседать... К тому же на сколько я помню в Оракле Read-Ahead нет и на больших выборках оптимизатор начинает сканировать страницы не в логическом порядке, а в физическом...
Здравствуйте, VladD2, Вы писали:
VD> Правда не учитывает кое чего и кое в чем основывается на недоказанных предположениях, но выглядит внушитешьно. Остается только доказать состоятельность всех рассчетов. Сделай таблички и померяй скорость. Причем таблички сделай не две, а хотя бы пять и джоинов не один, а тоже пять или десять. Померяй скорость и потом расскажи свои ощущения.
create table r (i int identity, a uniqueidentifier, b varchar(100), c varchar(100))
create table r1(i1 int identity, fk int, d varchar(100), e varchar(100))
-- заполнение r
--declare @i int
set @i=0
while @i<10000 begin
insert into r (a,b,c)
values (newID(), cast(newid() as varchar(50))+cast(newid() as varchar(50)),
cast(newid() as varchar(50))+cast(newid() as varchar(50)))
set @i=@i+1
end-- заполнение r1
--declare @i int
set @i=0
while @i<100000 begin
insert into r1 (fk,d,e)
values (ceiling(@i/10), cast(newid() as varchar(50))+cast(newid() as varchar(50)),
cast(newid() as varchar(50))+cast(newid() as varchar(50)))
set @i=@i+1
end-- создание временной таблички
--select top 50 a into r_tmp from r
-- создание индексов, сначала вариант с кластеризацией по ПК.
--create clustered index ixr on r(i)
create clustered index ixr1 on r1(i1)
create index ix2 on r(a)
create index ix3 on r1(fk)
set statistics io on
select * from r inner join r1 on r.i=r1.fk
where r.a in(select a from r_tmp)
-- Table 'r1'. Scan count 50, logical reads 1694, physical reads 0, read-ahead reads 0.
-- Table 'r'. Scan count 50, logical reads 224, physical reads 0, read-ahead reads 0.
-- А теперь с кластеризацией по FK
--drop index r.ixr
drop index r.ix2
drop index r1.ixr1
drop index r1.ix3
create clustered index ixr on r(a)
create clustered index ixr1 on r1(fk)
create index ix2 on r(i)
create index ix3 on r1(i1)
select * from r inner join r1 on r.i=r1.fk
where r.a in(select a from r_tmp)
-- Table 'r1'. Scan count 50, logical reads 190, physical reads 0, read-ahead reads 0.
-- Table 'r'. Scan count 50, logical reads 114, physical reads 0, read-ahead reads 0.
И зто все для случая, когда из главной таблицы выборка хаотическая и записи короткие и все влезают на одну страницу.
Хочешь сказать, что 304 чтения будут выполняться дольше чем 1918? Можно еще подчиненных таблиц понавесить с джойнами, ситуация не изменится.
Вообщем спор у нас свелся к тому, какого рода запросы выполняются в БД чаще... Тут уж пусть каждый сам для себя решает.
Здравствуйте, VladD2, Вы писали:
VD>Значит, что в 60% случаев и более делается выборка по ФК.
Ну так обычно и происходит.
VD>Нда. Логика на грани фантастики. "FK много" ... "поэтому имеет смысл делать кластерным [b]один[/b]] FK".
Я не о том.. Не FK много, а записей одинаковых в FK много, ссылающихся на один PK.
VD>Порой к справочникам можно отнести все таблицы кроме 3-5 где лежит основная информация. Возми к примеру БД нашего сайта. Там есть две таблицы где лежат основные данные и море справочных таблиц.
Вот БД нашего сайта лучше не брать. Там ровно один запрос по ПК, который одно сообщение достает, а и ещ еодин есть, который кажет профиль конкретного пользователя. Все остальное — изнурительное сканирование диапазонов, в которых ПК вообще не при делах.
VD>А я и не передергиваю. Я просто уверен, что послушав тебя не очень опытные орлы так и сделают.
Как так? Вообще от кластеризации откажутся? Это они зря...
VD> Ты не раз тут заявлял, что делать КИ по ПК плохое решение
Да, так и есть, правда с оговоркой, что в большинстве случаев... Я не никогда не утверждал, что кластеризация по ПК — это всегда плохо, один пример, где это оправдано ты привел — справочники.
VD> и что КИ на ФК — это верный выбор в большинстве случаев.
Не в большинстве, это лишь один из примеров, когда выгоднее сделать кластеризацию не по ПК.
VD> Теперь вот утверждаешь еще, что один ФК на таблицу — это самое частно втречающийся случай и что мол все таблицы нужно делать такими плюя на понятия вроде дизайна.
Я так не говорил, это ты уже за меня додумал. Справочники, как я уже говорил — особый случай.
VD> Если бы ты сказал: "Иногда <в таких то условиях> выгоднее сделать КИ на ФК по причине того, что чтение КИ быстнее.", то с тобой никто и не стал бы спорить. А ты возвел этот в догму.
Я не возвел это в догму. Я возвел в догму, что не надо тупо делать кластеризацию по ПК, а надо подумать, и с очень хорошей вероятностью окажется, что выгоднее сделать кластеризацию по другому полю.
VD>Попутно наговорив на идентети и воспев ГУИД-ы.
Я не наговорил на identity, я лишь описал реальное положение вещей, identity тоже не безгрешен и с ним тоже могут быть проблемы.
VD>По крайней мере в БД нет бреда про хотспоты при вставке в одну таблицу и есть разумная аргументация.
Про хот-споты что-то не один я брежу, и аргументация БОЛ ни чем не отличается от моей.
VD>По-моему постоянно. Слова худший выбор вроде точно звучали.
Скорее всего худший выбор, если есть другие кандидаты.
VD>В общем, ты явно не верно понимашь принципы оптимизации.
Или ты принципы построения БД..
Вообщем спор у нас свелся к тому, какие запросы по БД выполняются чаще, а это уж каждый пусть за себя решает.
VD>Он покажет твою не правоту. Я просто уверен, что и скорость вставки и темболее скорость выборки, а уж объем данных подавно, будут значительно хуже у твоего решения. Да похоже ты и сам это понимашь. Re[18]: GUID и кластерны
Здравствуйте, Merle, Вы писали:
M>Логически записи по прежнему отсортированы — Slot 0, Slot 1, ect... А вот физически запись 250 разместилась не по адресу 0x6b, где была раньше 200, ну или по крайней мере не между 0x60 и 0x76, а по адресу 0x8c, то есть добавилась в конец страницы.
OK, теперь точно убедил. Получается, что они внутри страницы linked list строят что-ли?
Здравствуйте, Lexey, Вы писали:
L>OK, теперь точно убедил. Получается, что они внутри страницы linked list строят что-ли?
Конечно, а как бы они еще предикатные блокировки реализовывали?
Самый эффективный способ на данный момент — это Key Range lock, который без ссылок листьевых ключей друг на друга не сделаешь...
Да и для всяких сканирований штука полезная...
Здравствуйте, Merle, Lexey, Вы столько понаписали...
Казалось бы, логично выделить следующие аспекты вопроса:
1) GUID длиннее. Со всеми вытекающими.
2) В отличие от IDENTITY, GUID не генерируется монотонно (функцией NEWID(), но можно написать процедуру, которая бы использовала UuidCreateSequential()). Опять-таки со всеми вытекающими.
3) Новые GUID-значения уникальны на всех машинах.
Все эти аспекты достаточно ясны.
Мне кажется, также, что вопрос влияния длины данных на производительность можно выделить в отдельную ветку, так же как и вопрос генерации новых GUIDов внутри SQL, так же как и тему репликации.
ИМХО, у вас прозвучало много интересных моментов, которые тем не менее никак не связаны ТОЛЬКО с GUID'ами или ТОЛЬКО с кластерными индексами по таковым полям.
Здравствуйте, 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> Почему ?
Здравствуйте, 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. может служить причиной выбора оптимизатором сканирования таблицы/кластерного индекса, что в свою очередь приводит к торможению других запросов из-за ожидания на блокировках и резкому увеличению вероятности дедлока, ввиду того что запрос лезет к чужим записям, которые на самом деле ему не нужны.
Мы уже победили, просто это еще не так заметно...
.
