Что такое кластерный индекс 1с
Сегодня речь пойдет о индексах СУБД MS SQL и их внутреннем устройстве. Я постараюсь рассказать о индексах и с точки зрения СУБД, и с точки зрения 1С 8.3.
Индексы — набор ссылок, упорядоченных по определенным столбцам, создаваемый с целью оптимизации производительности СУБД MS SQL.
Индексы в 1С
В системе 1С индексы создаются двумя способами — явным и неявным образом.
Создание индексов неявным образом:
Платформа создает индексы сама по заранее известным для каждого объекта метаданных ключам данных (ссылка, код, наименование, измерения и т.п.)
Создание индексов явным образом возможна тремя способами:
- Установка флага «Индексировать» у поля (реквизита/измерения). Вариант «Индексировать с доп. упорядочиванием» добавляет в индекс поле «Код» или «Наименование» (прежде всего для динамических списков).
- Добавление поля в «Критерии отбора«.
- Указание индексируемого поля в запрос с помощью конструкции «ИНДЕКСИРОВАТЬ ПО«.
Если вы только начинаете программировать в 1С или просто хотите систематизировать свои знания - попробуйте Школу программирования 1С нашего друга Владимира Милькина. Пошаговые и понятные уроки даже для новичка с поддержкой учителя.
Попробуйте бесплатно по ссылке >>
Индексы в СУБД Microsoft SQL
Индексы в СУБД MS SQL представляют из себя страницы с данными по 8 Кбайт каждая. Несмотря на то, что индексы призваны улучшить производительность СУБД, у них есть определенные недостатки — они занимают место на диске и замедляют работу СУБД на запись строк.
Виды индексов в СУБД MS SQL:
- Некластерные индексы — такие индексы не перестраивают таблицы, а лишь организуют ссылки.
- Кластерные индексы нужны для построения таблицы в соответствии с индексом. Данные упорядочены, например, по алфавиту. Недопустим для часто изменяющихся столбцов, т.к. СУБД постоянно физически перестраивает таблицу по этому индексу.
- Уникальные индексы — своего рода «надстройка» для кластерных и некластерных индексов. Такой индекс уникален по ключевым полям.
Виды ключей в СУБД:
- Первичный ключ (primary) — набор столбцов, уникально характеризующих строку.
- Внешний ключ (foreign) — поле таблицы, хранящее значение первичного ключа с целью организации связи между таблицами. 1С не использует данный вид ключей.
Важные нюансы использования индексов
Длина ключа индекса в основных СУБД (всех, кроме файлового варианта) — не более 900 байт и 16 различных полей.
Запускайте чаще дефрагментацию индексов на уровне СУБД MS SQL: при частом использовании индексов возможно появление эффекта фрагментации, нельзя допускать уровня фрагментации выше 25%.
Отсутствие индексов может привести к полному сканированию таблицы (table scan), что, в свою очередь, приведет к избыточной блокировке.
Однако не стоит включать индексацию по небольшим наборам данных (например, справочник Организации) — построение индекса может привести к обратному эффекту, быстродействие снизится.
Поддержите нас, расскажите друзьям!
СПРОСИТЕ в комментариях!
«Однако, не стоит включать индексацию по небольшим наборам данных(например, справочник Организации) – построение индекса может привести к обратному эффекту, не уменьшить скорость, а увеличит.».
Видимо ошибка: «… не уменьшить скорость, а увеличит.»
Вы правы. Спасибо большое!
«Внешний ключ (foreign) — 1С не использует данный вид ключей.»
Действительно, эти индексы не используются?
Из литературы вычитал что так, не верно?
Ну почему «Из литературы вычитал»?
Просто странно. По моему, теряются огромные возможности по использованию индексов.
Скорее всего из-за «трудностей перевода» при общении 1С с ms sql.
Согласен, интересно было бы спросить у официальных источников.
И не только с ms sql. У них «хранилищем» могут быть и другие СУБД. Наверное, не надо было изобретать свой язык запросов. Сейчас бы 1С была всеядной в смысле хранилища данных.
Не совсем так. Форэйнкеи в первую очередь нужны для обеспечения целостности данных на уровне СУБД. Так как в 1С используются составные типы данных, т.е в одном поле могут храниться ссылки на праймарикей разных таблиц справочников документов и т.д., то для ФК каждой из этих таблицы пришлось бы использовать отдельное поле, что чревато постоянной реорганизацией таблиц БД.
Недавно столкнулся с такой проблемой: потребовалось обработать большое количество строк (около 1 млн), с последующим left join. Пакетный запрос делал table scan, затем группировал выборку по полям А,Б,С…, выполнял некий отбор. Эти поля и есть поля связи. Сгруппированная выборка связывалась с исходной таблицей, полученной в результате table scan. Рассудив, что неплохо бы проиндексировать эти поля для быстрого поиска, создал индексы в первой и в группированной таблице: индекс(А,Б,С…). Однако, быстродействие вместо того чтобы улучшиться — ухудшилось. Ввиду большого количества строк индексы строился очень долго, занимая примерно 30 процентов времени от общего времени выполнения (судя по плану запроса), а выигрыш в поиске не смог это перекрыть. Без индексов отработало быстрее.
Я что-то сделал не так или можно утверждать, что выигрыш от использования индексов возможен только в случае единичного создания индекса и последующего многократного использования для поиска?
Не сомневаюсь, что Вы знаете о назначении индексов и уже использовали их в повседневной работе. Разбираетесь в какой-то мере в принципе их работы, на уровне, достаточном для оптимизации запросов и создания оптимальной структуры базы данных. Прочитали множество материала по этой теме. В общем, говорите с ними на "ты".
Тема индексов относится больше к теме администрирования базы данных и поддержания стабильности и производительности ее работы. В обычном представлении, разработчик 1С не имеет к их созданию и поддержке прямого отношения. В идеальном мире эта задача ложится на плечи администратора базы данных, но его не часто встретишь в штате. Скорее всего этим занят сисадмин, по крайней мере так многие считают. А у него своих проблем хватает, поэтому он просто копипастом настраивает обслуживание и забывает про 1С.
Когда совсем приспичит, разработчики 1С начинают добавлять индексы через настройки метаданных в конфигураторе. И Вам очень повезет, если индекс будет создан корректно, т.к. часто используется инновационный метод "тыка" при их настройке.
Все это к тому, что часто эта тема проходит мимо разработчиков и администраторов. В проблемах разбираются быстро, принимая "странные" решения и советуя их другим. После этого устоявшиеся подходы становятся "правильными" и "неоспоримыми".
Сегодня мы рассмотрим несколько самых распространённых заблуждений об индексах в контексте 1С, рожденных такими устоявшимися подходами, а также постараемся их объяснить и развеять.
Почему это важно
Придется стать героем! Изучить работу СУБД, в частности индексов, и встать на светлую сторону! Жизнь информационной базы в твоих руках! Расширь горизонты познания!
От простого к невероятному
Немного пафосно было сказано, но и правда кто, если не мы?! Вся эта ситуация и создает множество заблуждений про индексы, а в последствии и ошибки при работе с ними. Давайте по порядку рассмотрим самые распространенные из них, передвигаясь от простого к сложному.
Изучение работы индексов, если Вы с ними еще не сталкивались, можно начать с помощью материалов, ссылки на которые добавлены в конце публикации. Сейчас же принципы их работы и что это вообще такое мы рассматривать не будем, тема другая.
Индексы не нужны
Часто приходилось слышать, что об индексах в базе можно не заботиться, т.к. это специфичная тема и мы так уже 15 лет живем. То есть проблем никогда не было, так зачем об этом беспокоиться? Да, у нас система жутко тормозит в периоды закрытия месяца и формирования тяжелых отчетов, но это же 1С! Просто нужно смириться или купить сервер получше. Да и вообще, нет времени с этим копаться.
Слышал такое настолько часто, что удивляюсь до сих пор. Самое обидное, что все аргументы проходят всегда мимо и не воспринимаются всерьез. Вот он, дух 1С! То что явно не сказано в инструкциях к платформе и не проверяется на сдаче экзамена "1С:Специалист по платформе", то не должно удостаиваться внимания. К счастью, такое не везде, но удручающе часто. Даже в больших компаниях.
Но индексы конечно же нужны! Это одно из самых эффективных средств повышения скорости поиска данных в базе. Без них большинство запросов выполнялось бы неприемлемое количество времени. Чем больше база, тем больше было бы это время. Если бы индексы были не нужны совсем, то, думаю, разработчики платформы не добавляли штатные кластерные индексы на большинство таблиц, не было бы настроек индексирования в объектах метаданных и многого другого. Нужно ли еще что-то говорить по этому поводу.
Индексы - это сложно
Даже если индексы и нужны, то тема эта настолько сложная, что и браться за нее не стоит! Да, это еще один миф, который довольно часто встречается на просторах разработчиков 1С и системных администраторов. И это тоже заблуждение, т.к. достаточно один раз изучить основы работы и все встанет на свои места. В большинстве случаев, индекс представляет собой некоторый эффективно организованный указатель на значения, с помощью которого поиск осуществляется значительно быстрее по сравнению с полным перебором записей в таблице. Описывать подробно принцип работы индексов здесь смысла нет, да и это уже делали не раз на Инфостарт. Оставлю здесь несколько полезных ссылок:
Прочитав даже одну лишь из перечисленных статей, Вы навсегда поймете, что индексы - это просто. Когда знаешь - все становится просто. Знание - сила!
СУБД создает индексы автоматически
Помню не одну беседу, когда мне пытались доказать, что СУБД, в т.ч. Microsoft SQL Server, создает все необходимые индексы автоматически и полностью самостоятельно на основе собранной статистики. То есть если мы много много много раз выполним какой-либо запрос и SQL Server поймет, что для его эффективной работы нужен индекс, то она создаст его!
Это, конечно же, полностью не так! Создание, изменение и удаление индексов - это обязанность разработчика баз данных или администратора БД. Автоматически СУБД ничего не создает и не удаляет, и это очень хорошо. Вы только представьте ситуацию, когда SQL Server решит создать индекс автоматически во время рабочего дня, породив блокировку данных. Или автоматически создаст пару индексов и полностью займет свободное дисковое пространство, ведь индексы имеют накладные затраты в виде занимаемого места на диске и времени на их поддержание.
Но от части это все же правда. Но не в плане, что СУБД создает индексы автоматически, а в том, что она может подсказать каких индексов сейчас не хватает. Ранее в публикациях были рассмотрены скрипты для SQL Server и PostgreSQL, среди которых были и те, что показывали отсутствующие индексы. Для SQL Server статистика по отсутствующим индексам по умолчанию собирается наиболее подробная по сравнению с данными для PostgreSQL.
Список отсутствующих индексов по результатам собранной статистики SQL Server.
Не стоит перезапускать службу SQL Server каждую ночь, иначе полезной статистики Вы не соберете, не говоря уже об остальных недостатках такого подхода.
Для PostgreSQL также можно посмотреть отсутствующие индексы, но информация не такая точная и полная как для SQL Server.
Для анализа недостающих индексов в этом случае стоит собирать информацию о тяжелых запросах и планах их выполнения, чтобы по результатам изменить структуру БД.
Не стоит уповать на СУБД в части создания индексов в автоматическом режиме. Все же думать над этим придется, а SQL Server / PostgreSQL / др. СУБД дадут эффективные инструменты анализа недостающих индексов и средства их создания и поддержки.
Платформа 1С создает все индексы сама
Как Вы уже поняли, СУБД не создает индексы автоматически, адаптируясь под выполняемые запросы. НО! Значит платформа 1С сама создает недостающие индексы для оптимизации производительности!
На самом деле, конечно же, нет. Услышав такое, можно очень удивиться и пойти пить крепкий чай. Но столкнуться с таким до сих пор можно. Тут, на самом деле, возможно, появляется путаница, ведь платформа все же создает индексы в зависимости от типа объекта метаданных и его настроек. Это действительно так и по этой ссылке Вы можете изучить все возможные платформенные индексы.
В остальных же случаях разработчику приходится самостоятельно настраивать индексирование полей, чтобы добиться нужного результата. К сожалению, искусственный интеллект в платформу еще не завезли.
Чем больше индексов, тем лучше
Для быстрого поиска нужен индекс. Так почему же не добавить индекс на каждое поле. Например, есть справочник "Номенклатура" в конфигурации "Управление торговлей" ред. 11. В нем имеется несколько индексов, большинство из которых создается платформой 1С без каких-либо особых настроек. Есть и индексы, созданные специально для тех реквизитов, в которых свойство "Индексировать" установлено в "Индексировать" (извините за тавтологию, но такие уж названия):
- Артикул
- Вид номенклатуры
- Код для поиска
Но что, если нужно выполнить поиск по реквизиту "Код ОКВЭД" или "Код ОКП"? Или любому другому полю? Почему бы не добавить индексы на каждое поле?
Да, тут сразу можно понять, что что-то не так и добавление индексов на все поля таблицы дело не самое правильное. Как уже говорилось выше, индексы имеют свои накладные расходы для обслуживания. Кроме дополнительного дискового пространства, для их поддержки СУБД тратит дополнительное время при выполнении операций модификации данных. Когда Вы изменяете запись в таблице, СУБД обновляет данные индекса, что требует дополнительного времени и ресурсов. Чем больше индексов на таблице, тем больше времени на поддержку индекса тратится. Это время может быть незначительным относительно общего времени выполнения операции записи, но чем больше индексов, тем сильнее это время будет увеличиваться.
С помощью простого запроса можно проанализировать список индексов, на поддержание которых СУБД тратит значительные ресурсы в части операций ввода / вывода.
Не обязательно, что найденные индексы нужно удалять или изменять. Иногда такие индексы могут быть нормой.
Но задуматься все же стоит. Возможно, есть необходимость переписать запросы и оптимизировать структуру используемых индексов или даже таблиц.
К сожалению, такого простого запроса для PostgreSQL нет. Там требуется другой подход.
Плюс ко всему, неизвестно какие индексы для всех полей добавлять, ведь они могут быть составными, покрывающими, а еще для использования индексов значение отбора должно быть селективным. Какой смысл искать в индексе по полю "Пометка удаления" со значением Ложь, если 99% записей в таблице не помечены на удаление? Индекс не будет использоваться, т.к. смысла искать по неселективному значению нет.
Таким образом, смысла создавать индексы для всех полей просто нет, да и для большого числа полей тоже. При создании индекса нужно точно знать для каких целей он создается, иначе он будет висеть мертвым грузом и просто "съедать" ресурсы сервера. Если уж и стоит задача поиска по всем возможным полям, то скорее всего нужен другой подход в виде полнотекстового поиска и т.д.
Главное, чтобы поле входило в индекс
Перейдем к более практическим кейсам. Еще одним частым "фейлом" можно считать ситуацию, когда разработчики считают, что для эффективной работы индекса главное наличие в нем нужно поля. Например, в типовой конфигурации "Управление торговлей" ред. 11 имеется регистр сведений "Календарные графики" со следующей структурой.
Использование индексов 1С — важнейший момент в оптимизации работы информационной системы 1С. Но они очень часто игнорируются разработчиками.
Ниже я попытаюсь рассказать, что же это такое — индексы 1С 8.3, и как их использовать.
Что же такое индексы в 1С?
Индексы представляют собой структуру, позволяющую выполнять ускоренный доступ к строкам таблицы на основе значений одного или более ее столбцов. Индексы сокращают объем данных, которые необходимо считать, чтобы возвратить результирующий набор.
В 1С индексом таблицы является совокупность реквизитов с установленным флагом «индексировать». Но у каждой таблицы есть свои особенности. Например, в индекс у регистра бухгалтерии автоматически попадают поля период и счет. Их рекомендуют использовать для оптимизации работы системы.
Индекс хранится в отдельной таблице БД, где связывает определенные строки таблицы с ключами индексов. Т.е. при поиске не нужно обходить все строки базы данных, а последовательно по ключам отбирать наборы записей и перебирать уже небольшие наборы информации.
Из-за особенностей СУБД каждая из них накладывает свои ограничения на использования индексов:
Ограничения на индексы в файловой базе 1С
Если вы только начинаете программировать в 1С или просто хотите систематизировать свои знания - попробуйте Школу программирования 1С нашего друга Владимира Милькина. Пошаговые и понятные уроки даже для новичка с поддержкой учителя.
Попробуйте бесплатно по ссылке >>
В файловой базе единственным ограничением является максимальная длина ключа индекса, которая ограничивается 1920 байтами. При попытке реструктуризации базы данных с ключом большей длины появится ошибка — «Длина ключа индекса превышает максимально допустимую«.
Ограничения на индексы в базе 1С на MS SQL
На MS SQL, согласно документации, к ограничениям по длине индекса добавляется еще и количество ключей индекса, если количество полей будет превышено, индекс просто обрежется:
- количество ключей индекса — максимум 16
- суммарная длина индекса — 900 байт
Рекомендации по оптимизации работы 1С
- не рекомендуется использовать большое количество измерений и субконто в регистрах бухгалтерии;
- в планах счетов в рекомендациях устанавливать не более 4 субконто;
- нежелательно использовать поля составного типа — это сильно понижает скорость работы системы;
- не рекомендуется в измерениях регистров использовать поля с примитивным типом (число, строка и т.п.), если необходимо использовать значения примитивных видов, лучше создайте справочник, где будут указываться данные значения. Например: если Вы используете дату как измерение, лучше создать справочник «Даты», где единственным реквизитом будет реквизит с типом «дата»;
- не индексируйте строковые поля большой длины — это уменьшает быстродействие системы.
Все мы помним хрестоматийное объяснение «что такое индексы в БД и как они облегчают задачи поиска нужных строк». Уверен, у большинства из вас перед глазами встаёт нечто подобное:
И сразу становится очевидно, насколько меньше данных нужно перелопатить для поиска двух-трёх нужных строк. Гениально. Просто. Понятно.
И лично мне всегда казалось, что улучшать эту схему некуда… Пока я не познакомился с кластерными индексами. Оказалось, что всё не так уж радужно с «обычными» индексами.
Итак, что же такое кластерный индекс, чем он лучше некластерного, и как с ним обстоит дело у MySQL.
Некластерные индексы
Чтобы не запутаться, до поры до времени будем рассматривать простой индекс по одному полю. Упрощённо некластерный индекс можно представить как отдельную таблицу, каждая строка в которой ссылается на одну или несколько строк в таблице с данными. Строки в индексной таблице упорядочены и сгруппированы по значениям ключевых полей. Представим элементарный запрос:
Совсем без индексации будет прочитана и проверена каждая строка, и неудовлетворяющие условию строки просто не попадут в результат. Но прочитаны они будут.
При использовании «обычного», некластерного индекса, задача поиска сильно ускоряется. Во-первых, индексная таблица весит много меньше таблицы с данными, а значит элементарно может быть прочитана быстрее. Во-вторых, СУБД чаще всего стараются кешировать индексы в оперативную память, которая сама по себе много шустрее жёсткого диска*. В-третьих, в индексах отсутствуют дублирующиеся строки. А значит, как только мы нашли первое значение, поиск можно прекращать — оно же и последнее. В-четвёртых, данные в индексе отсортированы. А в-третьих и в-четвёртых вместе позволяют использовать алгоритм бинарного поиска (он же метод деления пополам), эффективность которого многократно превосходит простой перебор.
* Если ресурсы позволяют, таблицу данных тоже можно (и нужно) кешировать в оперативную память. Однако индексам и месту для них в оперативной памяти, по понятным причинам, принято уделять больше внимания.
Индексация — великая сила. Но если представить все указатели индексной таблицы на строки в таблице данных ОДНОВРЕМЕННО, получится достаточно сложная «паутина»:
И эта паутина, со множеством пересекающихся стрелок, подводит нас к проблеме (просто таки наглядно её демонстрирует), которую создаёт некластерный индекс.
Фрагментация
Оптимизатор MySQL может принять решение вообще не использовать индексы для поиска по небольшим таблицам (до пары десятков записей — зависит от конкретной структуры данных и индекса). Почему? Потому что поиск простым перебором читает данные последовательно. А указатель в индексе ссылается на разрозненные участки данных. И прыжки по ссылкам из индекса в конечном итоге могут стоить дороже полного перебора.
Итак, что мы имеем на данном этапе эволюции индексирования. Представьте большую, фрагментированную с точки зрения индексации, таблицу. Как данные приходили хаотичными и неотсортированными, так они и сохранялись. Теперь представьте индексную таблицу к ней. И наш старый добрый запрос:
Что происходит? Находится значение в индексе — это быстро и просто — и из таблицы данных читаются строки, на которые этот индекс ссылается. Естественно, при большой фрагментированности таблицы накладные расходы на чтение из разных её частей становятся ощутимыми.
И вот тут-то нам и пригодятся…
Кластерные индексы
Кластерные индексы отличаются от некластерных точно так же, как оглавление книги отличается от алфавитного указателя. Алфавитный указатель (некластерный индекс) для точного слова (значения) даёт точные номера страниц (строки в БД). Оглавление же указывает диапазон страниц, соответствующих определённой главе, в которой уже найдётся искомое слово. Причём каждая глава, если она достаточно велика, может содержать собственное оглавление.
Кластерный индекс — это древовидная структура данных, при которой значения индекса хранятся вместе с данными, им соответствующими. И индексы, и данные при такой организации упорядочены. При добавлении новой строки в таблицу, она дописывается не в конец файла*, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке.
* В разных движках и при разных настройках это может быть вовсе и не конец, и вовсе и не файла. Слово файл здесь означает «некую единицу измерения данных, соответствующую одной таблице», а «конец файла» употребляется как символ последовательной, линейной записи.
Один из самых мощных и производительных движков для MySQL — InnoDB. Тому много причин, и одна из них — кластерные индексы. Проще всего понять как устроены кластерные индексы, если представить их в динамике: как они разрастаются по мере добавления данных, и как начинает ветвиться таблица.
Первый этап: плоский список
Данные в InnoDB хранятся страницами по 16 Кб. Размер одной страницы — это предельный размер узла нашей древовидной структуры, от которого зависит в какой момент начнётся ветвление. Если вся таблица помещается в одну страницу, то она хранится в виде плоского списка, отсортированного по ключевому полю, без отдельной индексной таблицы.
Точно такими же маленькими табличками в будущем будут представлены все наши данные, а соединять их в дерево будут цепочки индексных страниц.
Второй этап: дерево
По мере добавления всё новых и новых данных, дерево будет усложняться и углубляться. И чем больше оно будет и ветвистее, тем больший выйгрышь даст такая схема хранения данны.
Серые страницы идентичны странице первого этапа — это просто отсортированные данные, листья (конечные узлы) нашего дерева. Голубые страницы — это промежуточные узлы дерева, содержащие только индекс и не содержащие данных. Стрелками помечены пути поиска определённых значений ключа.
Вспомним наш запрос (зелёная стрелка):
Однако индекс, указывающий на другую страницу, не обязательно ведёт сразу на страницу с данными. Индекс может указывать на страницу с промежуточным индексом. Возможно, при больших объёмах таблицы, БД придётся провести больше итераций поиска, но каждая такая итерация включает минимальный объём данных, а потому в целом всё равно поиск проходит быстрее.
Здесь действует простое правило, актуальное для любого типа индекса: чем разнообразнее данные, тем эффективнее использовать индекс для поиска конкретных значений.
Поскольку данные являются частью индекса, отсортированы и целенаправленно фрагментированы, очевидно что для одной таблицы может использоваться только один кластерный ключ. Из такой, достаточно сложной логики хранения индексов и данных, есть ещё одно важное следствие: операции записи, а особенно изменение имеющихся данных ключевых полей — крайне ресурсоёмкий процесс. Старайтесь использовать для кластерных индексов редко изменяемые поля.
Что касается сложных (составных) кластерных ключей, для них действует абсолютно такая же схема, только сортировка данных осуществляется по двум полям. Сам же индекс мало отличается от некластерного составного ключа.
Кластерные ключи в InnoDB
Здесь всё просто. Каждая таблица InnoDB имеет кластерный ключ. Каждая. Без исключения.
Гораздо интереснее, какие поля для этого выбираются.
- Если в таблице задан PRIMARY KEY — это он
- Иначе, если в таблице есть UNIQUE (уникальные) индексы — это первый из них
- Иначе InnoDB самостоятельно создаёт скрытое поле с суррогатным ID размером в 6 байт
До третьего пункта лучше не доводить свой многострадальный сервер, и добавить таки ID самостоятельно.
И не забывайте, что InnoDB во вторичных ключах хранит полный набор значений полей кластерного ключа в качестве ссылки на конечную строку в таблице. Чем больше первичный ключ, тем больше вторичные ключи.
Как устроены индексы в MySql, чем отличается индексирование в двух наиболее популярных движках MyISAM и InnoDb, чем первичные ключи отличаются от простого индекса, что такое кластерные индексы и покрывающие индексы, как с помощью них можно ускорить запросы. Вот как мне кажется наиболее интересные темы которые раскрою в этой статье. Тут же постараюсь подробно раскрыть тему с позиции того как работает этот механизм внутри. Буквально на пальцах и с позиции абстракций а не конкретики. В общем чтоб было минимум текста и максимум понятно.
Что представляет из себя индекс в MySql
Скорость чтения из индекса
Отличия в индексах MyISAM и InnoDb
Первичные и "вторичные" индексы в чем отличия
Вводная информация
В MySql существует несколько типов индексов и каждый из них хорош для выполнения своих специализированных задач. Например Hash индекс хорош для хранения данных в виде ключ - значение в оперативной памяти, FULLTEXT индекс для поиска по текстовым документам, SPATIAL для хранения информации о гео-объектах, UNIQUE для уникальных значений. Но все же в подавляющем большинстве случаев мы используем индекс на основе B-дерева (BTREE) или Balanced-Tree. Сбалансированное оно потому что высота каждого поддерева с общим корневым элементом может отличаться, но всегда не более чем на константную величину. Далее в статье речь пойдет именно про BTREE индексы, по умолчанию под индексами буду подразумевать именно их.
Что представляет из себя индекс в MySql
Индекс
На рисунке изобразил схематично как устроен индекс. Имеются узловые элементы (квадраты) и листья (круги). Предположим у нас есть таблица с колонками "Val" и "ID" как на рисунке. В этой таблице индекс построен по числовому полю "ID". Тогда получается что в узловых элементах находятся значения индекса и ссылки на другой более нижний узел или лист. В листовых же элементах точно так же лежат значения индекса которые уже ссылаются непосредственно на данные из таблицы.
Процесс поиска происходит примерно следующим образом. Например нужно найти строку с индексом 11.
начинаем просмотр корневого (верхнего) узла
первое значение в нем 10
идем к следующему 19, оно уже больше чем нам нужно
по ссылке слева от 19 переходим к следующему нижнему узлу
там первое значение 13, оно больше чем нам нужно
опять по ссылке слева переходим к более нижнему элементу
это уже будет листовой элемент, в нем уже лежат непосредственно данные
просматриваем данные по порядку
переходим по ссылке непосредственно к строке в таблице.
Скорость чтения из индекса
Такое устройство индекса позволяет обеспечить логарифмическую скорость поиска O(log n). Это очень быстро. Вот таблица где для наглядности посчитал сколько сравнений нужно сделать для поиска записи в таблице с разным количеством данных:
Количество элементов в таблице
Количество сравнений
Получается для поиска по миллиону значений нужно всего около 20 сравнений. Очень быстро. Одна небольшая ремарка - эти вычисления конечно же правдивы только для индексов с уникальными значениями. Построив индекс по значению, которое почти во всех строках одинаково, эффекта не будет так как все равно придется перебирать все эти строки с одинаковыми значениями.
Отличия в индексах MyISAM и InnoDb
MyIsam это более старый движок чем InnoDb и все описанное выше хорошо описывает устройство индекса именно в MyIsam. Более того для MyIsam можно сказать что первичный индекс и просто обычный индекс ни чем между собой не отличаются. В целом таблицы построенные на движке MyIsam вполне себе могут существовать даже без первичного ключа и без всякого индекса в целом. А вот InnoDb уже более свежий и продвинутый движок, и тут как раз есть отличия первичного ключа и просто индекса. Создать таблицу InnoDb тоже можно не указав первичный ключ, но в этом случае первичный ключ все равно создастся. Это называется суррогатный первичный ключ. InnoDb сам выберет поле по которому нужно этот ключ создать, если ни одно поле не подходит, то создаст новое числовое поле, которое конечно же будет скрыто и в структуре его не увидеть. Для разбора индексов InnoDb первым делом нужно начать с кластеризации.
Кластерный индекс
Индекс в InnoDb
Кластерный индекс отличается тем, что в отличии от предыдущей картинки, где от листьев шли ссылки непосредственно на строки в таблице, тут все данные строк хранятся непосредственно в самом индексе. Проиллюстрировал это на примере листьев 10, 11, 12. Это хорошо тем что позволяет избежать лишнего чтения диска при переходе по ссылке от листа на данные в строке. Тут непосредственно вся строка лежит в индексе. То есть получается что в InnoDb при создании таблицы и указании первичного ключа будет построено такое дерево, в котором все данные таблицы будут продублированы в листья индекса. Если первичный ключ не задать то колонка для него будет выбрана или создана автоматически и все равно по ней будет построен кластерный индекс.
Более того, если мы говорим о таблицах на основе движка InnoDb, то в целом понятие таблица довольно абстрактное. На картинке она нарисована просто для наглядности. На самом деле ни какой таблицы по сути не существует, а все данные просто хранятся в кластерном индексе.
Первичные и "вторичные" индексы в чем отличия
Выше было оговорено что для MyIsam нет разницы между первичными и "вторичными" ключами.
Первичный и вторичный индекс в MyIsam
На картинке нарисован первичный и вторичный ключ в MyIsam. Первичный ключ построен по полю "ID", вторичный по полю "Val". Видно что их структура одинакова. И в том и в другом в листьях расположены значения индекса и ссылки на строки в таблице.
В InnoDb это устроено немного по другому.
Первичный и вторичный индекс в InnoDb
Как уже говорил, таблица тут просто для наглядности. Все ее данные хранятся в первичном (кластерном ключе). Тут первичный ключ построен по полю "Id", вторичный по полю "Val". Видно что в листьях первичного ключа лежат значения индекса + все данные из строк таблицы. Во вторичном же ключе, в листьях лежат значения ключа + первичный ключ.
Можно резюмировать что для MyIsam нет различий между первичным и вторичными индексами. Для InnoDb первичный ключ содержит в себе все данные таблицы, вторичный же ключ содержит значения ключа плюс значение первичного ключа. Получается что при поиске по вторичному ключу, поиск будет произведен дважды. Первый раз непосредственно по самому индексу, будет найдено значение первичного индекса. И уже второй раз по найденому первичному индексу для поиска данных всей строки.
Покрывающие индексы
Последнее о чем хотелось бы упомянуть - это покрывающие индексы и как с их помощью можно немного ускорить производительность.
Смысл покрывающих индексов в том, что MySql может вытаскивать данные непосредственно из самого индекса, не читая при этом всю строку и вовсе не читая строку. Для такой оптимизации нужно чтобы все поля указанные в SELECT имелись в индексе. То есть например у нас имеется таблица с полями "id", "name", "surname", "age", "address". И мы проиндексировали ее по полю "id". В запросе мы хотим получить например "id" и "name". При таком условии MySql найдет по первичному ключу нужную строку, прочитает ее и отбросит все поля не указанные в SELECT. Если же мы немного оптимизируем этот запрос и построим индкес по двум полям "id" и "name", то в таком случае MySql найдя нужную строку по этому индексу не пойдет читать всю эту строку, а просто возьмет данные, которые нужны непосредственно из индекса. Правда есть обратная сторона такого подхода, а именно размер индекса в этом случае будет больше, по этому нужно грамотно подходить к построению покрывающих индексов.
Более подробно можно почитать в очень хорошей книге "MySQL по максимуму" Бэрон Шварц, Петр Зайцев, Вадим Ткаченко
Читайте также: