Кластерные индексы для неидентичных столбцов для ускорения массовых вставок?

Question

Мои два вопроса:

• Можно ли использовать кластерные индексы для ускорения массовых вставок в большие таблицы?
• Могу ли я по-прежнему эффективно использовать связи с внешним ключом, если мой столбец IDENTITY больше не является кластеризованным индексом?

Чтобы уточнить, у меня есть база данных с парой очень больших (от 100 до 1000 миллионов строк) таблиц, содержащих данные компании. Обычно в такой таблице содержатся данные о 20-40 компаниях, каждая из которых имеет свой собственный "чанк", помеченный как "CompanyIdentifier" (INT). Кроме того, в каждой компании имеется около 20 отделов, каждый со своим собственным "подразделом", помеченным "DepartmentIdentifier" (INT).

Часто случается, что целый "кусок" или "подчанк" добавляется или удаляется из таблицы. Моей первой мыслью было использование разбиения таблиц на эти блоки, но поскольку я использую SQL Server 2008 Standard Edition, я не имею на это права. Тем не менее, большинство моих запросов выполняются на "чанке" или "чанке", а не на таблице в целом.

Я работал над оптимизацией этих таблиц для следующих функций:

1. Запросы, которые выполняются на подразделах
2. "Бенчмаркинг" запросов, которые выполняются на таблице в целом
3. Вставка / удаление больших кусков данных.

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

Но для 3) я изо всех сил пытался найти хорошее решение. Моя первая стратегия состояла в том, чтобы всегда отключать индексы, массово вставлять большой кусок и перестраивать индексы. Сначала это было очень быстро, но теперь, когда в базе данных много компаний, каждый раз для перестройки индекса требуется очень много времени.

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

Кажется, я заметил, что при добавлении кластерного индекса, определенного в CompanyIdentifier + DepartmentIdentifier, загрузка новых "кусочков" в таблицу происходит быстрее. Прежде чем я отказался от этой стратегии в пользу добавления кластеризованного индекса к столбцу IDENTITY, несколько статей указывали мне, что кластеризованный индекс содержится во всех других индексах, и поэтому кластеризованный индекс должен быть как можно меньше. Но теперь я думаю о возрождении этой старой стратегии, чтобы ускорить вставки. Мой вопрос, будет ли это мудрым, или я пострадаю от снижения производительности в других областях? И это действительно ускорит мои вставки или это только мое воображение?

Я также не уверен, действительно ли в моем случае нужен столбец IDENTITY. Я хотел бы иметь возможность устанавливать отношения внешнего ключа с другими таблицами, но могу ли я использовать для этого что-то вроде схемы CompanyIdentifier+DepartmentIdentifier+[uniquifier]? Или это должен быть фрагментарный номер IDENTITY по всей таблице?

Большое спасибо за любые предложения или объяснения.

Answer 1

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

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

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

---

Обновление: После года опыта работы с этим дизайном я могу сказать, что для этого подхода необходимо запланировать регулярную перестройку всех индексов (мы делаем это один раз в неделю). В противном случае индексы очень скоро фрагментируются, и производительность теряется. Тем не менее, мы находимся в процессе перехода к новому дизайну базы данных с секционированными таблицами, что в принципе лучше во всех отношениях - за исключением стоимости лицензии Enterprise Server, но мы уже забыли об этом. По крайней мере, у меня есть.

Answer 2

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

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

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

В своем решении вы помещаете поле [uniquifier], но зачем это делать, если вы можете указать личность, которая будет делать именно это? Он будет уникальным, физически упорядоченным, небольшим (для внешних ключей в других таблицах означает меньший индекс), а в некоторых случаях быстрее.

Вы не можете попробовать это, эксперимент? У меня похожая ситуация здесь, когда у меня 4 миллиарда строк, постоянно вставляется еще больше (до 100 в секунду), таблица не имеет первичного ключа и кластерного индекса, поэтому предложения в этой теме ОЧЕНЬ интересны и для меня.

Answer 3

Посмотрите на System.Data.SqlClient.SqlBulkCopy API. Учитывая ваши требования для записи значительного количества строк в и из базы данных, это может быть то, что вам нужно?

Массовое копирование направляет данные в таблицу за одну операцию, а затем выполняет проверку индекса один раз. Я использую его для копирования 500 000 строк в таблицу базы данных и из нее, и ее производительность на порядок выше, чем у любого другого метода, который я пробовал, предполагая, что ваше приложение может быть структурировано для использования API?

Answer 4

Можно ли использовать кластерные индексы для ускорения массовых вставок в большие таблицы?

Никогда! Представьте себе еще миллион строк, которые нужно поместить в эту таблицу, и физически упорядочить их - это колоссальная потеря производительности в долгосрочной перспективе.

Могу ли я по-прежнему эффективно использовать связи с внешним ключом, если мой столбец IDENTITY больше не является кластеризованным индексом?

Абсолютно. Кстати, кластерный индекс не является серебряной пулей и может быть медленнее, чем ваш обычный индекс.

Answer 5

(Учитывая, что вы уже выбрали ответ и дали себе баллы, это предоставляется как бесплатная услуга, благотворительный акт!)

Немного знаний - опасная вещь. Есть много вопросов, которые необходимо рассмотреть; и они должны рассматриваться вместе. Взятие любого отдельного вопроса и изучение его в отдельности - очень фрагментированный способ администрирования базы данных: вы навсегда найдете новую истину и измените все, что думали раньше. Перед тем, как приступить к этому, прочтите этот ▶ вопрос / ответ ◀ для контекста.

Не забывайте, что в наши дни любой, у кого есть клавиатура и модем, может опубликовать свои "документы". Некоторые из них работают на РС, проповедуя последнее "улучшение"; другие публикуют яркие отчеты о функциях, которые они никогда не использовали или использовали только один раз, в одном контексте, но они публикуют, что это работает в любом контексте. (Посмотрите на ответ Спенса: он полон энтузиазма и "продан", но под пристальным вниманием утверждения ложны; он не плохой человек, просто типичный для масс в мире РС и как они работают; как они публикуются.)

• Примечание: я использую термин MicroSofties для описания тех людей, которые верят в гейтсовское представление о том, что любой неквалифицированный человек может управлять базой данных; и что MS все исправит. Он не предназначен как оскорбление, скорее нежность, из-за веры в магию и приостановки законов физики.

Кластерные индексы

Были разработаны для реляционных баз данных настоящими инженерами (Sybase, до того, как MS приобрела код), которые имеют больше мозгов, чем все MS вместе взятые. Реляционные базы данных имеют реляционные ключи, а не Id несколько ключей. Это многостолбцовые ключи, которые автоматически распределяют данные и, следовательно, загружают вставку, например. вставка счетов-фактур для различных компаний все время (хотя в нашем случае "кусков" это не обсуждалось).

• если у вас есть хорошие реляционные ключи, CI предоставляют диапазонные запросы (ваши (1) и (2)) и другие преимущества, которых у NCI просто нет.

• Начиная с Id столбцы перед моделированием и нормализацией данных сильно затрудняют процессы моделирования и нормализации.

• Если у вас есть Id Если база данных, то у вас будет больше индексов, чем нет. Содержимое многих баз данных MS не является "реляционным", обычно они представляют собой просто ненормализованные системы хранения, с гораздо большим количеством индексов, чем было бы в нормализованной базе данных. Поэтому есть большой толчок, множество "улучшений" MS, чтобы попытаться немного ускорить эти аборты. Исправьте симптом, но не приближайтесь к проблеме, вызвавшей симптом.

• В SQL 2005 и снова в 2008 году MS облажалась с CI, и в результате они теперь лучше в некоторых отношениях, но хуже в других; универсальность КИ была потеряна.

• Неправильно, что NCI несут CI (CI является базовой структурой с единым хранилищем; NCI являются вторичными и зависят от CI; поэтому при повторном создании CI все NCI автоматически воссоздаются). NCI имеют ключ CI на уровне листа.

• У Microsoft есть свои проблемы, которые меняются в основных выпусках (но не устраняются):

  • и в MS это сделано неэффективно, поэтому размер индекса NCI велик; в корпоративных СУБД, когда это эффективно сделано, это не рассматривается.

  • Следовательно, в мире MS это только наполовину правда, что ключ CI должен быть как можно короче. Если вы понимаете, что размер вознаграждения равен размеру NCI, и если вы готовы понести эти расходы, он вернется к таблице, которая очень быстра из-за тщательно сконструированного КИ, тогда это лучший вариант.

  • Общий совет, что CI должен быть Id Эта колонка совершенно и совершенно неверна. Наихудшим вариантом для ключа CI является монотонно увеличивающееся значение (IDENTITY, DATETIME и т. Д.). Зачем? потому что вы гарантировали, что все одновременные вставки будут бороться за текущее местоположение вставки, последнюю страницу в индексе.

  • Настоящая цель Partitioning (которую MS предоставила через 10 лет после поставщиков Enterprise) состоит в том, чтобы распределить эту нагрузку. Конечно, тогда они должны предоставить метод распределения разделов, если предположить, что ничего, кроме реляционного ключа; но для начала, теперь Id Этот ключ распределяется между 32 или 64 разделами, обеспечивая лучший параллелизм.

• CI должен быть уникальным. Реляционные БД требуют уникальных ключей, так что это не сложно.

  • Но для любителей, которые поместили нереляционное содержимое в базу данных, если они не знают этого правила, но знают, что КИ распространяет данные (немного знаний - опасная вещь), они сохраняют свои Id iot key в NCI (хорошо), но они создают CI на почти, но не совсем уникальном ключе. Смертельный. CI должны быть уникальными, это требование дизайна. Дублирующиеся (помните, мы говорим здесь ключ CI) строки вне страницы, расположенные на страницах переполнения и (затем) последней странице; и представляют собой метод плохой фрагментации цепочки страниц.

  • Обновление, так как этот вопрос подвергается сомнению в другом месте. Я уже говорил, что MS постоянно меняет методы, не решая проблему.

    • Руководство MS Online с их красивыми картинками (а не техническими диаграммами) говорит нам, что в 2008 году они заменили (заменили одну на другую) страницы переполнения восхитительным "уникализатором".

    • Это полностью удовлетворяет MicroSofties. Неуникальные КИ не являются проблемой. Это обрабатывается магией. Дело закрыто.

    • Но в заявлениях нет логики или полноты, и квалифицированные люди зададут очевидные вопросы: где находится этот "Уникализатор"? На каждом ряду или только на строках, нуждающихся в "Уникализации". DBBC PAGE показывает это на каждой строке. Поэтому MS только что добавила 4-байтовый секретный столбец (включая обработку служебных данных) в каждую строку вместо нескольких страниц переполнения только для неуникальных строк. Это MS идея инженерии.

    • Конец обновления

  • В любом случае, остается факт, что Неуникальные КИ имеют существенные накладные расходы (теперь больше, чем раньше), и их следует избегать. Вам лучше добавить 1- или 2-байтовый столбец самостоятельно, чтобы добиться уникальности.,

• Таким образом, без изменений с самого начала (1984 г.) лучшим кандидатом на КИ является уникальный многополюсный реляционный ключ (я не могу сказать, что ваш точно, но, безусловно, выглядит так).

• И поместите любые монотонно увеличивающиеся ключи (IDENTITY, DATETIME) в NCI.

• Помните также, что CI - это единая структура хранения, которая устраняет (иначе) кучу; CI B-Tree состоит в браке с рядами на уровне листьев; запись уровня листа - это строка. Это гарантирует меньше чтения при каждом доступе.

  • Поэтому невозможно, чтобы куча NCI+ была быстрее, чем CI. Другой распространенный в мире MS миф, который противоречит законам физики: навигация по B-Tree и запись в одно место, в котором вы уже находитесь, должна быть быстрее, чем дополнительная запись строки в отдельную структуру хранения. Но MicroSofties действительно верят в магию, они приостановили законы физики.
    ,

• Есть много других функций, которые необходимо изучить и использовать, я упомяну, по крайней мере, FILLFACTOR и RESERVEPAGEGAP, чтобы придать этому посту некоторую полноту. Не используйте эти функции, пока не поймете их. Все функции производительности имеют стоимость, которую вы должны понимать и принимать.

• CI также самонастраиваются как на уровне страницы, так и на уровне экстента. PageSplits - это то, что нужно отслеживать (только для случайных вставок), и это легко модулируется с помощью FILLFACTOR и RESERVEPAGEGAP.

• И прочитайте SO сайт Clustered Indices, но имейте в виду все вышеизложенное, esp. первые два пп.

Ваш конкретный случай

• Обязательно избавьтесь от своих суррогатных ключей (Id iot столбцы) и замените их настоящими естественными реляционными ключами. Суррогаты всегда являются дополнительным ключом и индексом; это цена, которую нельзя забывать или воспринимать легкомысленно.

• CompanyIdentifier + DepartmentIdentifier + [uniquiefier] - это именно то, о чем я говорю. Теперь обратите внимание, что они уже INT и очень быстрые, поэтому очень глупо добавлять NUMERIC(10,0) Id Ключ. Используйте 1- или 2-байтовый столбец для уникальности.

• Если вы понимаете это правильно, вам может не потребоваться лицензия на разделы.

• CompanyIdentifier+DepartmentIdentifier+[uniquifier] является идеальным кандидатом (не зная ничего о вашей БД, кроме той, которую вы опубликовали) для CI, в контексте того, что вы периодически выполняете массовое удаление / вставку. Подробно выше.

  • Вопреки тому, что говорили другие, это хорошо, и не фрагментирует CI. Допустим, у вас есть 20 компаний, и вы удаляете 1, что составляет 5% данных. Весь этот PageChain, который был достаточно смежным, теперь переходит в FreePageChain, смежный и нетронутый. Чтобы быть точным, у вас есть одна точка фрагментации, но не фрагментации в смысле обычного использования слова. И угадайте, что, если вы развернетесь и выполните массовую вставку, как вы думаете, куда пойдут данные? Это точно то же физическое местоположение, что и удаленные строки. И FreePage Chain перемещается к PageChain, экстенту и странице одновременно.
    ,

• но что настораживает, так это то, что вы не знали о требовании уникальности CI. Грустно, что MicroSofties пишут ерунду, но не то, почему / на чем основано каждое упрощенное правило; не основная информация. Точный признак неуникальных CI: таблица будет очень быстрой сразу после DROP/CREATE CI, а затем замедлится со временем. Хороший уникальный CI сохранит свою скорость, и для его замедления потребуется год (2 года на моих больших, активных банковских базах данных).

• 4 часа - это очень много времени для 1 миллиарда строк (я могу воссоздать CI для 16 миллиардов строк с ключом из 6 столбцов за 3 минуты на корпоративной платформе). Но в любом случае это означает, что вы должны запланировать его как регулярное еженедельное или требующее обслуживания.

• почему вы не используете опцию WITH SORTED_DATA? Разве ваши данные не были отсортированы до удаления? Эта опция переписывает не листовые страницы CI, но не листовые страницы (содержащие строки). Это можно сделать только в том случае, если он уверен, что данные были отсортированы. Не используя эту опцию перезаписывает каждую страницу в физическом порядке.

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

Answer 6

Я немного поиграл с некоторыми вещами из etl. я регулярно вставлял jsut в таблицу, затем удалял и читал индексы до и после вставки, пробовал операторы слияния, а потом наконец пробовал ssis. Я продан на ssis. Буквально вчера мне удалось сократить процесс etl (~24 миллиона записей, ~6 ГБ) с ~1-1 1/2 часа на прогон до ~ 24 минут, но просто позволив ssis обрабатывать вставки.

Я считаю, что с расширенными услугами вы должны быть в состоянии использовать ssis.