Кластерные индексы по столбцам, не являющимся идентификаторами, для ускорения массовой вставки?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Если у вас есть база данных Idiot, то у вас будет больше индексов, чем нет. Содержимое многих баз данных MS не является «реляционным», это обычно просто ненормализованные файловые системы с гораздо большим количеством индексов, чем было бы в нормализованной базе данных. Поэтому есть большой толчок, много «улучшений» MS, чтобы попытаться придать этим абортам немного скорости. Устраните симптом, но не приближайтесь к проблеме, вызвавшей этот симптом.

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

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

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

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

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

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

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

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

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

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

    • Онлайн-руководство MS с их красивые картинки (не технические диаграммы) говорят нам о том, что в 2008 году они заменили (заменили одну на другую) страницы переполнения очаровательным «Уникализатором».

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

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

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

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

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

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

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

  • So it is not possible, that a NCI+Heap can be faster than a CI. Anther common myth in the MS world that defies the laws of physics: navigating a B-Tree and writing to the one place you are already in, has got to be faster than additionally writing the row to a separate storage structure. But MicroSofties do believe in magic, they've suspended the laws of physics.
    .

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

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

• И прочтите сайт SO для кластерных индексов, но имейте в виду все вышеперечисленное, особенно. первые два пп.