Что такое индекс?

Индекс — это структура данных, которая создается в одном или нескольких столбцах таблицы. В большинстве случаев индексы имеют структуру B-дерева. Когда индекс создается для столбца таблицы, он фактически создает таблицу поиска со столбцом и указателем на адрес памяти, где фактически хранится строка с этим столбцом. Поэтому, когда мы запрашиваем таблицу со столбцом в предложении WHERE, синтаксический анализатор сначала проверяет, является ли этот столбец частью индекса и существует ли для него таблица поиска по индексу. Если да, то он проверяет адрес памяти, где хранится запись. Затем он напрямую переходит к этому адресу памяти и возвращает результат пользователю. Вот почему сканирование индекса или если у столбца есть индекс, то данные извлекаются быстрее.

Какая структура данных представляет собой индекс?

В большинстве случаев для хранения индексов используется структура данных B-Tree. Это из-за временной эффективности B-деревьев. Другими словами, B-деревья просматриваются, ищутся, вставляются, удаляются и обновляются за логарифмическое время. Кроме того, данные B-Tree всегда сортируются и сохраняются. Следовательно, он выполняет поиск и вставку данных за известную долю времени. Значения данных, хранящиеся в B-деревьях, сбалансированы — все значения, меньшие, чем конкретный узел, можно найти слева от узла, а значения, превышающие значение узла, находятся справа от узла. Следовательно, легко искать любое значение или запись в индексах B-дерева.

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

Как работает индекс хеш-таблицы?

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

Например, предположим, что мы создали хэш-индекс для столбца PHONE_NUMBER таблицы EMPLOYEES. Пусть эта хеш-функция будет любой простой функцией для любой сложной функции. Предположим, в этом случае хэш-функция представляет собой сумму всех цифр в PHONE_NUMBER, умноженную на 1000. Затем, если нам нужно найти какой-либо конкретный номер телефона, скажем, 546.897.231, мы получим его в местоположении 45000. Следовательно, указатель пойдет в это место и получит необходимые сведения о записи. Если нам нужно немедленно увидеть информацию о следующем номере телефона, то он будет по адресу 46000, который находится далеко от предыдущего номера телефона.

Каковы недостатки хеш-индекса?

Индексы хеширования создаются путем создания хеш-функции в столбце, для которого необходимо создать индекс. Следовательно, каждому столбцу будут выделены разные несмежные ячейки памяти. Следовательно, если нам нужно выполнить поиск любых смежных записей с такими условиями, как «меньше чем» или «больше чем», то хеш-индекс не сможет получить все записи за один раз. Он должен искать записи в разных местах, чтобы получить все записи. Следовательно, он неэффективен для таких поисков. Это хорошо, только если нам нужно искать пары ключ-значение. Это означает, что запрос с предложением WHERE с условием ‘=’ будет иметь лучшую производительность.

Какие еще типы индексов?

Что именно находится внутри индекса базы данных?

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

Как база данных узнает, когда использовать индекс?

Когда мы запускаем запрос с условием 'WHERE COLUMN_NAME =' XYZ ', тогда база данных проверит, есть ли у этой COLUMN_NAME какой-либо индекс. Если в этом конкретном столбце есть индекс, он проверит избирательность столбца и решит, следует ли использовать индекс. Если избирательность столбца больше 0.33, то для извлечения данных будет использоваться индекс.

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

Да, в Oracle мы можем заставить базу данных использовать индекс с помощью HINTS. Эти СОВЕТЫ перенаправят путь выполнения для использования индекса.

Как создать индекс в SQL?

Общий синтаксис для создания индекса:

CREATE INDEX index_name

ON TABLE_NAME (COLUMN_NAME);

– Пример:

CREATE INDEX idx_phone

ON EMPLOYEES (PHONE_NUMBER);

Как создать многоколоночный индекс в SQL:

Мы можем создать индекс по комбинации столбцов.

Общий синтаксис для создания индекса с несколькими столбцами:

CREATE INDEX index_name

ON TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2,.. COLUMN_NAMEN);

– Пример:

CREATE INDEX idx_emp_name

ON EMPLOYEES (FIRST_NAME, LAST_NAME);

Что является хорошей аналогией для индекса базы данных?

Очень хороший пример индексации в реальном времени — это индекс в книге и каталоги в библиотеке. Когда мы хотим выполнить поиск / прочитать по определенной теме, мы смотрим на указатель книги и открываем эту конкретную страницу, а не просматриваем всю книгу по этой теме. Поиск в индексе с последующим переходом на эту страницу более эффективен.

Что такое растровый индекс?

Индексы Bitmap очень эффективны при извлечении записей с ограниченным количеством значений для столбца. В этом методе индексы для столбцов с менее уникальными значениями используются в виде битов. Попробуем разобраться в этом методе по порядку на примере.

В приведенном выше примере, если мы заметили столбец GENDER, он может иметь только два значения — Male или Female. По сравнению со всей таблицей СТУДЕНТ, они не являются уникальным значением. Точно так же, скажем, у нас есть только четыре семестра для курса, и тогда у нас может быть только четыре значения — sem1, sem2, sem3 и sem4. Эти типы столбцов называются столбцами с менее уникальными значениями или столбцами с меньшей мощностью. Несмотря на то, что эти столбцы имеют менее частые значения, они запрашиваются чаще всего.

Биты — как всем известно, это наименьшая единица представления данных. Он может иметь значение 0 или 1. Что, если мы будем использовать эти биты для представления этих менее уникальных столбцов значений? Но как? Этот метод хранения менее часто используемых столбцов в виде битов называется индексами битовой карты.

Этот метод используется для очень больших таблицы с менее уникальными столбцами значений и доступ к нему несколько раз с различными поисковыми запросами. В этом методе у нас будет

  • Столько битов, сколько строк в таблице для каждого столбца с менее уникальным значением. Например, если в таблице STUDENT 10K записей, то у нас будет 10K бит — по одному биту на каждую строку.

  • Количество индексов битовой карты, созданных в столбце, будет равно количеству различных значений в столбце. Например, для столбца GENDER у нас будет создано два индекса растрового изображения — один для мужчин и один для женщин, а для столбца семестр у нас будет создано четыре индекса растровых изображений — 1, 2, 3 и 4.

  • Если у нас есть какое-либо совпадающее значение в столбце для строки, тогда этот бит строки будет иметь «1», иначе «0». Это означает, что для столбца GENDER у нас будет 2 индекса растрового изображения — один для мужчин и один для женщин. Значение бита для индекса «мужской» битовой карты будет равно 1, если в этой строке GENDER указано как «M», в противном случае — «0».

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

  • Согласно правилу 1 у нас будет четыре строки в таблице и, следовательно, у нас будут биты — по одному биту на каждую строку.

  • В столбце GENDER всего два значения — «M» и «F». Следовательно, у нас будет два индекса растрового изображения — один для «M» и один для «F».

  • Теперь индекс растрового изображения для столбца GENDER выглядит следующим образом. Здесь индекс растрового изображения ‘M’ имеет значение ‘1000’, указывающее, что первая строка имеет пол как ‘M’, а остальные строки не имеют пола как ‘M’. Точно так же индекс растрового изображения ‘F’ указывает, что первая строка не является ‘F’, а остальные во всех строках пол обозначен буквой F.

Аналогичным образом растровый индекс для семестра может быть следующим:

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

SELECT * FROM STUDENT WHERE GENDER = ‘F’ AND SEMESTER =2;

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

Посмотрите на таблицу, чтобы убедиться, что она верна. Да, это привело к правильной строке. Таким образом, СУБД переходит к третьей строке файла и отображает результат для пользователя.

Здесь выборка индекса битовой карты и выполнение логической операции «И» для получения результата происходит сравнительно быстрее. Следовательно, этот метод хранения полезен для такого рода данных.

Если нам нужно удалить запись из таблицы, будет сгенерирован временный индекс удаления. Затем он выполнит логическую операцию «И» над столбцами фильтра и временным индексом, чтобы удалить данные из таблицы.

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

SELECT * FROM STUDENT WHERE GENDER = ‘F’ AND SEMESTER =4;

Как создать индекс битовой карты?

Синтаксис для создания индекса битовой карты следующий:

CREATE BITMAP INDEX index_name

ON table_name (column_name);

Например,

CREATE BITMAP INDEX idx_gender

ON STUDENT (GENDER);

Каковы преимущества и недостатки Bitmap Index?

(плюс) Преимущества растровых индексов:

  • Как мы уже видели, этот метод помогает быстрее извлекать записи, когда количество столбцов меньше и эти столбцы наиболее часто используются в запросе. Этот метод эффективен, даже если у нас очень большая таблица.

  • Этот метод более эффективен, когда столбцы меньше всего участвуют в операциях вставки / обновления / удаления.

  • Это позволяет объединить несколько индексов растрового изображения вместе для запуска запроса, как мы видели в примерах выше.

(минус) Недостатки растровых индексов:

  • Они не подходят для маленьких столиков. В небольших таблицах СУБД вынуждает использовать полное сканирование таблицы вместо использования растрового индекса.

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

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

В чем разница между B-деревом и растровым индексом?

В методе B-дерева каждый корень будет разветвляться только на два узла, и каждый промежуточный узел также будет иметь данные. И листовой узел будет иметь самый низкий уровень данных. Однако и в этом методе записи будут отсортированы. Поскольку все промежуточные узлы также имеют записи, это сокращает обход данных до конечного узла. Простое B-дерево можно представить следующим образом:

Индексы Bitmap очень эффективны при извлечении записей с ограниченным количеством значений для столбца. В этом методе индексы для столбцов с менее уникальными значениями используются в виде битов.

Что такое индекс на основе функций?

Рассмотрим запрос, чтобы найти сведения о сотруднике, имя которого «Стивен». Здесь имя дано с маленькой буквы. В таблице EMPLOYEES имя хранится в формате initcap. Следовательно, нам нужно преобразовать FIRST_NAME в EMPLOYEES в нижний регистр.

SELECT * FROM EMPLOYEES WHERE LOWER (FIRST_NAME) = ‘steven’;

Но представьте, что у нас есть индекс, созданный для столбца FIRST_NAME. Тогда указанный выше запрос не будет использовать индекс, созданный для FIRST_NAME, для поиска записи. Если нам нужно использовать индекс, тогда мы должны создать индекс вместе с функцией LOWER (). Такие индексы по столбцам называются индексами на основе функций.

CREATE INDEX idx_lwr_firstname ON EMPLOYEES (LOWER (FIRST_NAME));

Что такое таблица с индексированием?

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

В общем, будет создан индекс по первичному ключу таблицы. Но представьте, что есть небольшая таблица, и мы также создаем индекс для остальных столбцов. Что случится? Излишне записи этих столбцов также хранятся в индексной таблице. Это пустая трата памяти. Итак, что делает Oracle, он хранит всю таблицу как индекс и сортирует на основе ее первичного ключа. Другими словами, вместо того, чтобы хранить только значения столбцов и их адрес в индексной таблице, записи всей таблицы хранятся в индексной таблице на основе ее первичного ключа. Такие таблицы известны как таблица с индексированием (IOT).

Ниже приведен пример создания таблицы с индексированием.

CREATE TABLE iot_example (
		NAME VARCHAR (50),
		ADDRESS VARCHAR (70), 
		CONSTRAINT PK_NAME PRIMARY KEY (NAME))
	ORGANIZATION INDEX;

Каковы несколько советов по настройке индексов SQL для повышения производительности?

В таблицах создаются индексы для повышения производительности запроса к таблице. Индексы обычно создаются путем анализа запросов. Это означает, что он проверяет наиболее часто используемые столбцы в предложении WHERE, количество записей в таблице, уникальность значений столбца, избирательность столбца и т. Д. Но существует несколько рекомендаций при создании индексов в таблице, чтобы запрос выполнялся лучше. .

  • Не используйте слишком много индексов: Когда мы создаем индекс, БД создаст другую таблицу поиска для хранения значений столбцов и указателя на расположение адреса. Следовательно, если мы создадим много индексов, для хранения этой информации потребуется много места. Кроме того, это дублирование данных. Кроме того, если мы попытаемся ОБНОВИТЬ или УДАЛИТЬ запись, нам также придется обновить все эти индексные таблицы. По мере увеличения числа индексов увеличивается и обновление этих индексных таблиц, что требует времени. Следовательно, производительность запроса ухудшается. Поэтому создайте индекс, который действительно нужен. Удалите все ненужные индексы в таблице.

  • Старайтесь не включать в индекс столбцы, которые постоянно обновляются: Если мы создаем индекс для столбца, который часто обновляется, то нам придется обновить и таблицу индекса. Это будет дополнительная задача, увеличивающая время, затрачиваемое на обновление запроса. Это означает, что фактическое время, необходимое для обновления столбца, увеличится. Следовательно, не рекомендуется иметь индекс для часто обновляемых столбцов.

  • Создание индексов для столбца (столбцов) внешнего ключа может улучшить производительность: Каждый раз, когда мы пишем запрос с несколькими таблицами, мы соединяем первичный ключ одной таблицы с внешним ключом из другой таблицы (сопоставление родитель-потомок). Однако у нас будет индекс по умолчанию для столбца первичного ключа. Но наличие индекса в столбце внешнего ключа повысит производительность запроса. Другими словами, скажем, что DEPARTMENT_ID в таблице EMPLOYEES — это внешний ключ. Таблица EMPLOYEES очень большая, и соединение DEPARTMENT_ID этой таблицы с DEPARTMENT_ID из DEPARTMENTS займет много времени. Если у нас есть индекс на DEPARTMENT_ID таблицы EMPLOYEES, это повысит производительность.

  • Создайте индексы для столбцов, которые многократно используются в предикатах ваших SQL-запросов: Если некоторые столбцы повторно используются в предложении WHERE, то лучше создать индекс для таких столбцов. Это повысит производительность запроса.

  • Избавьтесь от перекрывающихся индексов: Индексы могут быть созданы на комбинации столбцов — нескольких столбцов. Если у нас есть индекс для нескольких столбцов, то в запросе он сначала проверяет первый столбец и использует этот индекс. Если в предложении WHERE указаны все столбцы, то по умолчанию будет использоваться этот индекс. Если у него только первый столбец, то также будет использоваться индекс. Предположим, у нас есть несколько индексов, использующих тот же столбец, что и его первый столбец, тогда запрос будет запутан при выборе индекса. Это создаст проблемы в исполнении. Следовательно, не создавайте перекрывающийся индекс.

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

  • Убедитесь, что создаваемые вами индексы имеют высокую селективность: Индексы с высокой селективностью обеспечат лучшую производительность. Следовательно, создайте индексы для столбца, который имеет большую избирательность. В общем, любой индекс с селективностью более 0.33 даст лучшую производительность. Если столбец имеет уникальные значения, то он будет иметь избирательность 1 — обычно столбец первичного ключа. Поэтому при создании индекса для столбца проверьте его избирательность, а затем создайте индекс.

Что такое кластерный индекс?

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

Предположим, мы создали кластерный индекс для столбца PHONE_NUMBER в таблице EMPLOYEES. Затем все записи EMPLOYEES сортируются на основе PHONE_NUMBER на диске, а затем создается сопоставление индекса. Итак, теперь, если мы напишем любой запрос для поиска некоторой последовательности телефонных номеров сотрудников — скажем, меньше или равно, больше или равно, меньше, больше и т. д., тогда будет легко получить все записи. Потому что, как только первая запись выбрана, остальные записи присутствуют непосредственно рядом с ней. Для остальных записей повторная выборка не требуется.

Когда имеет смысл использовать кластерный индекс?

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

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

В чем недостаток использования кластерного индекса?

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

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

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

В чем разница между кластеризованным и некластеризованным индексом?


Источники:

(1) Индексы в вопросах собеседования по SQL https://www.tutorialcup.com/ru/%D0%B8%D0%BD%D1%82%D0%B5%D1%80%D0%B2%D1%8C%D1%8E/sql-%D0%B2%D0%BE%D0%BF%D1%80%D0%BE%D1%81%D1%8B-%D1%81%D0%BE%D0%B1%D0%B5%D1%81%D0%B5%D0%B4%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D1%8F/%D0%B8%D0%BD%D0%B4%D0%B5%D0%BA%D1%81%D1%8B-sql.htm#1_What_is_an_index

Tags

Нет комментариев

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.