Кластерный некластерный индекс в чем отличие
Кластерные и «обычные» индексы MySQL (InnoDB)
Все мы помним хрестоматийное объяснение «что такое индексы в БД и как они облегчают задачи поиска нужных строк». Уверен, у большинства из вас перед глазами встаёт нечто подобное:
И сразу становится очевидно, насколько меньше данных нужно перелопатить для поиска двух-трёх нужных строк. Гениально. Просто. Понятно.
И лично мне всегда казалось, что улучшать эту схему некуда… Пока я не познакомился с кластерными индексами. Оказалось, что всё не так уж радужно с «обычными» индексами.
Итак, что же такое кластерный индекс, чем он лучше некластерного, и как с ним обстоит дело у MySQL.
Некластерные индексы
Чтобы не запутаться, до поры до времени будем рассматривать простой индекс по одному полю. Упрощённо некластерный индекс можно представить как отдельную таблицу, каждая строка в которой ссылается на одну или несколько строк в таблице с данными. Строки в индексной таблице упорядочены и сгруппированы по значениям ключевых полей. Представим элементарный запрос:
Совсем без индексации будет прочитана и проверена каждая строка, и неудовлетворяющие условию строки просто не попадут в результат. Но прочитаны они будут.
При использовании «обычного», некластерного индекса, задача поиска сильно ускоряется. Во-первых, индексная таблица весит много меньше таблицы с данными, а значит элементарно может быть прочитана быстрее. Во-вторых, СУБД чаще всего стараются кешировать индексы в оперативную память, которая сама по себе много шустрее жёсткого диска*. В-третьих, в индексах отсутствуют дублирующиеся строки. А значит, как только мы нашли первое значение, поиск можно прекращать — оно же и последнее. В-четвёртых, данные в индексе отсортированы. А в-третьих и в-четвёртых вместе позволяют использовать алгоритм бинарного поиска (он же метод деления пополам), эффективность которого многократно превосходит простой перебор.
* Если ресурсы позволяют, таблицу данных тоже можно (и нужно) кешировать в оперативную память. Однако индексам и месту для них в оперативной памяти, по понятным причинам, принято уделять больше внимания.
Индексация — великая сила. Но если представить все указатели индексной таблицы на строки в таблице данных ОДНОВРЕМЕННО, получится достаточно сложная «паутина»:
И эта паутина, со множеством пересекающихся стрелок, подводит нас к проблеме (просто таки наглядно её демонстрирует), которую создаёт некластерный индекс.
Фрагментация
Оптимизатор MySQL может принять решение вообще не использовать индексы для поиска по небольшим таблицам (до пары десятков записей — зависит от конкретной структуры данных и индекса). Почему? Потому что поиск простым перебором читает данные последовательно. А указатель в индексе ссылается на разрозненные участки данных. И прыжки по ссылкам из индекса в конечном итоге могут стоить дороже полного перебора.
Итак, что мы имеем на данном этапе эволюции индексирования. Представьте большую, фрагментированную с точки зрения индексации, таблицу. Как данные приходили хаотичными и неотсортированными, так они и сохранялись. Теперь представьте индексную таблицу к ней. И наш старый добрый запрос:
Что происходит? Находится значение в индексе — это быстро и просто — и из таблицы данных читаются строки, на которые этот индекс ссылается. Естественно, при большой фрагментированности таблицы накладные расходы на чтение из разных её частей становятся ощутимыми.
И вот тут-то нам и пригодятся…
Кластерные индексы
Кластерные индексы отличаются от некластерных точно так же, как оглавление книги отличается от алфавитного указателя. Алфавитный указатель (некластерный индекс) для точного слова (значения) даёт точные номера страниц (строки в БД). Оглавление же указывает диапазон страниц, соответствующих определённой главе, в которой уже найдётся искомое слово. Причём каждая глава, если она достаточно велика, может содержать собственное оглавление.
Кластерный индекс — это древовидная структура данных, при которой значения индекса хранятся вместе с данными, им соответствующими. И индексы, и данные при такой организации упорядочены. При добавлении новой строки в таблицу, она дописывается не в конец файла*, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке.
* В разных движках и при разных настройках это может быть вовсе и не конец, и вовсе и не файла. Слово файл здесь означает «некую единицу измерения данных, соответствующую одной таблице», а «конец файла» употребляется как символ последовательной, линейной записи.
Один из самых мощных и производительных движков для MySQL — InnoDB. Тому много причин, и одна из них — кластерные индексы. Проще всего понять как устроены кластерные индексы, если представить их в динамике: как они разрастаются по мере добавления данных, и как начинает ветвиться таблица.
Первый этап: плоский список
Данные в InnoDB хранятся страницами по 16 Кб. Размер одной страницы — это предельный размер узла нашей древовидной структуры, от которого зависит в какой момент начнётся ветвление. Если вся таблица помещается в одну страницу, то она хранится в виде плоского списка, отсортированного по ключевому полю, без отдельной индексной таблицы.
Точно такими же маленькими табличками в будущем будут представлены все наши данные, а соединять их в дерево будут цепочки индексных страниц.
Второй этап: дерево
Когда данные перестают помещаться в одну страницу, список превращается в дерево. Страница с данными разделяется на две, причём в том узле (на той странице), где раньше были данные, теперь располагается индекс, охватывающий обе новые страницы. Конкретный узел такого дерева обязан включать в себя индексы всех дочерних узлов или конечные данные, если узел последний. Узлы могут ссылаться друг на друга только в одном направлении: от родителя к потомку.
По мере добавления всё новых и новых данных, дерево будет усложняться и углубляться. И чем больше оно будет и ветвистее, тем больший выйгрышь даст такая схема хранения данны.
Серые страницы идентичны странице первого этапа — это просто отсортированные данные, листья (конечные узлы) нашего дерева. Голубые страницы — это промежуточные узлы дерева, содержащие только индекс и не содержащие данных. Стрелками помечены пути поиска определённых значений ключа.
Вспомним наш запрос (зелёная стрелка):
Обращаясь к таблице, запрос попадает на первую страницу и получает индекс, тут же отправляющий его на конечную страницу с данными, где находятся строки, удовлетворяющие критериям поиска. Страница уже прочитана на этапе поиска, все данные собраны, БД может вернуть ответ.
Однако индекс, указывающий на другую страницу, не обязательно ведёт сразу на страницу с данными. Индекс может указывать на страницу с промежуточным индексом. Возможно, при больших объёмах таблицы, БД придётся провести больше итераций поиска, но каждая такая итерация включает минимальный объём данных, а потому в целом всё равно поиск проходит быстрее.
Здесь действует простое правило, актуальное для любого типа индекса: чем разнообразнее данные, тем эффективнее использовать индекс для поиска конкретных значений.
Поскольку данные являются частью индекса, отсортированы и целенаправленно фрагментированы, очевидно что для одной таблицы может использоваться только один кластерный ключ. Из такой, достаточно сложной логики хранения индексов и данных, есть ещё одно важное следствие: операции записи, а особенно изменение имеющихся данных ключевых полей — крайне ресурсоёмкий процесс. Старайтесь использовать для кластерных индексов редко изменяемые поля.
Что касается сложных (составных) кластерных ключей, для них действует абсолютно такая же схема, только сортировка данных осуществляется по двум полям. Сам же индекс мало отличается от некластерного составного ключа.
Кластерные ключи в InnoDB
Здесь всё просто. Каждая таблица InnoDB имеет кластерный ключ. Каждая. Без исключения.
Гораздо интереснее, какие поля для этого выбираются.
До третьего пункта лучше не доводить свой многострадальный сервер, и добавить таки ID самостоятельно.
И не забывайте, что InnoDB во вторичных ключах хранит полный набор значений полей кластерного ключа в качестве ссылки на конечную строку в таблице. Чем больше первичный ключ, тем больше вторичные ключи.
ВЛИЯНИЕ ИНДЕКСОВ НА ПРОИЗВОДИТЕЛЬНОСТЬ 1С:ПРЕДПРИЯТИЕ 8
— Ну у вас и запросы! — сказала база данных и повисла…
Краткий ответ на вопрос заголовка заключается в том, что это позволит выполнять запросы быстро и уменьшать негативное влияние блокировок на производительность в многопользовательском режиме.
Что такое индекс?
Подобно содержанию в книге, индекс в базе данных позволяет быстро искать конкретные сведения в таблице.
Сначала поговорим про индексы в MS SQL Server.
Индексы представляют собой структуру, позволяющую выполнять ускоренный доступ к строкам таблицы на основе значений одного или более ее столбцов.
Индекс содержит ключи, построенные из одного или нескольких столбцов таблицы или представления, и указатели, которые сопоставляются с местом хранения заданных данных.
Индексы сокращают объем данных, которые необходимо считать, чтобы возвратить результирующий набор.
Хотя индекс и связан с конкретным столбцом (или столбцами) таблицы, все же он является самостоятельным объектом базы данных.
Просто объекта «Индекс» в платформе 1С:Предприятие 8 нет.
Индексы таблиц в базе данных 1С:Предприятие создаются неявным образом при создании объектов конфигурации, а также при тех или иных настройках объектов конфигурации.
Явным способом включением свойства «Индексировать» реквизитов и измерений с значение «Индексировать» и «Индексировать с доп. Упорядочиванием». Вариант ««Индексировать с доп. Упорядочиванием»» включает обычно колонку «код» или «наименование» в индекс.
Еще одним явным способом можно считать добавление объекта метаданных в объект метаданных «критерий отбора».
Можно указать индекс для таблицы значений и в запросах для временных таблиц.
ВЫБРАТЬ
Код,
Наименование
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ Справочник.Номенклатура
ИНДЕКСИРОВАТЬ ПО Код
В любом случае, надо понимать, что говоря об индексах, мы фактически подразумеваем индексы СУБД, которая используется для 1С:Предприятие. Исключению составляют объекты типа Таблица значений, когда индексы находятся в RAM (оперативной памяти).
Физическая сущность индексов в MS SQL Server.
Физически данные хранятся на 8Кб страницах. Сразу после создания, пока таблица не имеет индексов, таблица выглядит как куча (heap) данных. Записи не имеют определенного порядка хранения.
Когда вы хотите получить доступ к данным, SQL Server будет производить сканирование таблицы (table scan). SQL Server сканирует всю таблицу, что бы найти искомые записи.
Отсюда становятся понятными базовые функции индексов:
— увеличение скорости доступа к данным,
— поддержка уникальности данных.
Несмотря на достоинства, индексы так же имеют и ряд недостатков. Первый из них – индексы занимают дополнительное место на диске и в оперативной памяти. Каждый раз когда вы создаете индекс, вы сохраняете ключи в порядке убывания или возрастания, которые могут иметь многоуровневую структуру. И чем больше/длиннее ключ, тем больше размер индекса. Второй недостаток – замедляются операции вставки, обновления и удаления записей.
В среде MS SQL Server реализовано несколько типов индексов:
Некластерный индекс
Некластерные индексы – не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки.
Для идентификации нужной строки в таблице некластерный индекс организует специальные указатели, включающие в себя:
Некластерных индексов может быть несколько для одной таблицы.
Некластеризованный индекс по таблице, не имеющей кластеризованного индекса
Некластеризованный индекс по таблице, имеющей кластеризованный индекс
Кластерный (кластеризованный) индекс
Принципиальным отличием кластерного индекса от индексов других типов является то, что при его определении в таблице физическое расположение данных перестраивается в соответствии со структурой индекса. Логическая структура таблицы в этом случае представляет собой скорее словарь, чем индекс. Данные в словаре физически упорядочены, например по алфавиту.
Кластерные индексы могут дать существенное увеличение производительности поиска данных даже по сравнению с обычными индексами. Увеличение производительности особенно заметно при работе с последовательными данными. Если в таблице определен некластерный индекс, то сервер должен сначала обратиться к индексу, а затем найти нужную строку в таблице. При использовании кластерных индексов следующая порция данных располагается сразу после найденных ранее данных. Благодаря этому отпадают лишние операции, связанные с обращением к индексу и новым поиском нужной строки в таблице.
Естественно, в таблице может быть определен только один кластерный индекс. Кластерный индекс может включать несколько столбцов.
Необходимо избегать создания кластерного индекса для часто изменяемых столбцов, поскольку сервер должен будет выполнять физическое перемещение всех данных в таблице, чтобы они находились в упорядоченном состоянии, как того требует кластерный индекс. Для интенсивно изменяемых столбцов лучше подходит некластерный индекс.
При создании в таблице первичного ключа (PRIMARY KEY) сервер автоматически создает для него кластерный индекс, если его не существовало ранее или если при определении ключа не был явно указан другой тип индекса.
Когда же в таблице определен еще и некластерный индекс, то его указатель ссылается не на физическое положение строки в базе данных, а на соответствующий элемент кластерного индекса, описывающего эту строку, что позволяет не перестраивать структуру некластерных индексов всякий раз, когда кластерный индекс меняет физический порядок строк в таблице.
Уникальный индекс
Уникальность значений в индексируемом столбце гарантируют уникальные индексы. При их наличии сервер не разрешит вставить новое или изменить существующее значение таким образом, чтобы в результате этой операции в столбце появились два одинаковых значения.
Уникальный индекс является своеобразной надстройкой и может быть реализован как для кластерного, так и для некластерного индекса. В одной таблице может существовать один уникальный кластерный и множество уникальных некластерных индексов.
Уникальные индексы следует определять только тогда, когда это действительно необходимо. Для обеспечения целостности данных в столбце можно определить ограничение целостности UNIQUE или PRIMARY KEY, а не прибегать к уникальным индексам. Их использование только для обеспечения целостности данных является неоправданной тратой пространства в базе данных. Кроме того, на их поддержание тратится и процессорное время.
1С:Предприятие 8 активно использует кластерные уникальные индексы. Это означает, что можно получить ошибку не уникального индекса.
Если не уникальность заключается в датах с нулевыми значениями, то проблема решается созданием базы с параметром смещения равным 2000.
«Рыба» скрипта для определения не уникальных записей:
SELECT COUNT(*) Counter, from
GROUP BY
HAVING Counter > 1
Понятие первичного и внешнего ключа
Первичный ключ (primary key) – это набор столбцов таблицы, значения которых уникально определяют строку.
Ограничения индексов
Индекс может быть создан на основании нескольких полей. В этом случае существует ограничение – длина ключа индекса не должна превышать 900 байтов и не более 16 ключевых столбцов. На практике это означает что при создании индекса, включающего более 16 полей, индекс усекается. Это может оказать влияние на производительность при количестве субконто составного типа более 4х.
В актуальных релизах платформы выполнена оптимизация данного случая и используется хэш по ключу полей, но это медленней «полноценных» индексов.
Статистика индексов
Microsoft SQL Server собирает статистику по индексам и полям данных, хранимых в базе. Эта статистика используется оптимизатором запроса SQL Server при выборе оптимального плана исполнения запросов на выборку или обновление данных.
При создании индекса оптимизатор запросов автоматически сохраняет данные статистики о проиндексированых столбцах.
Просмотр статистики — sp_helpstats.
Фрагментация индексов
Чрезмерная фрагментация создает проблемы для больших операций ввода-вывода. Фрагментация не должна превышать 25%. От снижения фрагментации индексов могут выиграть операции сканирования больших диапазонов данных. Для этого рекомендуется выполнять периодическую дефрагментацию индексов. Обратите внимание, что при дефрагментации индексов (по умолчанию) автоматически обновляется статистика.
Смотреть степень фрагментированности индексов можно штатными средствами СУБД или в разрезе объектов метаданных можно например с помощью бесплатного онлайн-сервиса http://www.gilev.ru/sqlsize/
Оптимизация размещения индексов
При объеме таблиц не позволяющем им «разместиться» в оперативной памяти сервера, на первое место выходит скорость дисковой подсистемы (I/O). И здесь можно обратить внимание возможность размещать индексы в отдельных файлах расположенных на разных жестких дисках.
Подробное описание действий http://technet.microsoft.com/ru-ru/library/ms175905.aspx
Использование индекса из другой файловой группы повышает производительность некластерных индексов в связи с параллельностью выполнения процессов ввода/вывода и работы с самим индексом.
Для определения размеров можно использовать выше упомянутую обработку.
Влияние индексов на блокировки
Отсутствие необходимого индекса для запроса означает перебор всех записей таблицы, что в свою очередь приводит к избыточным блокировкам, т.е. блокируются лишние записи. Кроме того, чем дольше выполняется запрос из-за отсутствующих индексов, тем больше время удержания блокировок.
Другая причина блокировок — малое количество записей в таблицах. В связи с этим SQL Server, при выборе плана выполнения запроса, не использует индексы, а обходит всю таблицу(Table Scan), блокируя целиком. Для того, чтобы избежать подобных блокировок, необходимо увеличить количество записей в таблицах до 1500-2000. В этом случае сканирование таблицы становится долее дорогостоящей операцией и SQL Server начинает использовать индексы. Конечно это можно сделать не всегда, ряд справочников как «Организации», «Склады», «Подразделения» и т.п. обычно имеют мало записей. В этих случаях индексирование не будет улучшать работу.
Эффективность индексов
Мы уже отметили в заголовке статьи, что нас интересуют влияние индексов на быстродействие запросов. Итак, индексы наиболее подходят для задач следующего типа:
Правда при всей полезности индексов, есть одно очень важное НО – индекс должен быть «эффективно используемым» и должен позволять находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов. И наоборот, неиспользуемые (редко используемые) индексы скорее ухудшают скорость записи данных (поскольку каждая операция, изменяющая данные, должна также обновлять страницы индексов) и создают избыточный объем базы.
Покрывающим (для данного запроса), называется индекс в котором есть все необходимые поля для этого запроса. Например, если индекс создан по колонкам a, b и c, а оператор SELECT запрашивает данные только из этих колонок, то требуется доступ только к индексу.
Каковы различия между кластеризованным и некластеризованным индексом?
10 ответов
оба типа индекса улучшат производительность при выборе данных с полями, которые используют индекс, но замедлят обновление и операция вставки.
из-за медленной вставки и обновления кластеризованные индексы должны быть установлены на поле, которое обычно является инкрементным идентификатором ie или меткой времени.
SQL Server обычно использует индекс, только если его избирательность превышает 95%.
кластеризованные индексы физически упорядочивают данные на диске. Это означает, что для индекса не требуется дополнительных данных, но может быть только один кластеризованный индекс (очевидно). Доступ к данным с помощью кластеризованного индекса самый быстрый.
все остальные индексы должны быть некластерный. Некластеризованный индекс имеет дубликат данных из индексированных столбцов, упорядоченных вместе с указателями на фактические строки данных (указатели на кластеризованный индекс, если он есть). Это означает, что доступ к данным через некластеризованный индекс должен пройти дополнительный слой косвенности. Однако, если вы выбираете только данные, доступные в индексированных столбцах, вы можете получить данные обратно непосредственно из дублированных индексных данных (поэтому рекомендуется выбирать только те столбцы, которые вам нужны, а не использовать *)
кластеризованные индексы хранятся физически в таблице. Это означает, что они являются самыми быстрыми, и вы можете иметь только один кластеризованный индекс на таблицу.
некластерные индексы хранятся отдельно, и вы можете иметь столько, сколько вы хотите.
лучший вариант-установить кластеризованный индекс на наиболее используемый уникальный столбец, обычно PK. Вы всегда должны иметь хорошо выбранный кластеризованный индекс в своих таблицах, если только очень веская причина-не может придумать ни одного, но Эй, он может быть там не так приходит.
Кластерный Индекс
Некластеризованный Индекс
Кластерный Индекс
Некластеризованный Индекс
помимо этих различий, которые вы должны знать, что, когда таблица некластерный (когда таблица не имеет кластеризованного индекса) файлы данных неупорядочены и он использует структуру кучи данных, структура данных.
кластеризация в основном означает, что данные находятся в этом физическом порядке в таблице. Вот почему у вас может быть только один стол.
Unclustered означает, что это» только » логический порядок.
кластеризованные индексы отлично работают для диапазонов (например, выберите * из my_table, где my_key между @min и @max)
в некоторых условиях СУБД не придется выполнять работу по сортировке, если вы используете оператор orderby.
кластеризованные индексы могут замедлять вставки, потому что физические макеты записей должны быть изменены по мере ввода записей, если новые ключи не находятся в последовательном порядке.
кластеризованный индекс фактически описывает порядок, в котором записи физически хранятся на диске, поэтому у вас может быть только один.
некластеризованный индекс определяет логический порядок, который не соответствует физическому порядку на диске.
кластеризованный индекс по существу представляет собой отсортированную копию данных в индексированных столбцах.
основным преимуществом кластеризованного индекса является то, что когда ваш запрос (поиск) находит данные в индексе, то для извлечения этих данных не требуется никакого дополнительного ввода-вывода.
накладные расходы на поддержание кластеризованного индекса, особенно в часто обновляемой таблице, могут привести к низкой производительности, и по этой причине может быть предпочтительнее создать некластеризованный индекс.
индексированная база данных состоит из двух частей: набора физических записей, которые расположены в некотором произвольном порядке, и набора индексов, которые определяют последовательность, в которой записи должны быть прочитаны для получения результата, отсортированного по некоторому критерию. Если нет никакой корреляции между физическим расположением и индексом, то считывание всех записей по порядку может потребовать выполнения множества независимых операций чтения одной записи. Потому что база данных может считывать десятки последовательных записей за меньшее время, чем потребовалось бы для чтения двух не последовательных записей, производительность может быть улучшена, если записи, последовательные в индексе, также хранятся последовательно на диске. Указание того, что индекс кластеризован, приведет к тому, что база данных приложит некоторые усилия (разные базы данных отличаются друг от друга), чтобы упорядочить вещи так, чтобы группы записей, последовательные в индексе, были последовательными на диске.
например, если начать с пустого некластеризованного база данных и добавить 10 000 записей в случайной последовательности, записи, вероятно, будут добавлены в конце в порядке они были добавлены. Чтение базы данных по порядку по индексу потребует 10 000 считываний одной записи. Однако если использовать кластеризованную базу данных, то при добавлении каждой записи система может проверить, хранится ли предыдущая запись сама по себе; если окажется, что это так, она может записать эту запись с новой в конце базы данных. Затем он может посмотреть на физическая запись перед слотами, в которых находились перемещенные записи, и посмотреть, сохранена ли последующая запись сама по себе. Если бы он обнаружил, что это так, он мог бы переместить эту запись на это место. Использование такого подхода приведет к тому, что многие записи будут сгруппированы в пары, что потенциально почти удвоит скорость последовательного чтения.
на самом деле кластерные базы данных используют более сложные алгоритмы, чем это. Однако важно отметить, что существует компромисс между временем, необходимым для обновления базы данных, и временем, необходимым для ее последовательного чтения. Ведение кластеризованной базы данных значительно увеличит объем работы, необходимой для добавления, удаления или обновления записей любым способом, который повлияет на последовательность сортировки. Если база данных будет читаться последовательно гораздо чаще, чем будет обновляться, кластеризация может быть большой победой. Если он будет часто обновляться, но редко считываться последовательно, кластеризация может быть большой утечкой производительности, особенно если последовательность добавления элементов в базу данных не зависит от порядка их сортировки по кластеризованному индексу.