Как работает индексация базы данных?

голоса
1k

Учитывая , что indexingэто так важно , как ваши данные установлено увеличение размера, может кто - нибудь объяснить , как делает индексацию работу на database-agnosticуровне?

Для получения информации о запросах индексировать поля, проверить Как я индексировать столбец базы данных .

Задан 04/08/2008 в 11:07
источник пользователем
На других языках...                            


10 ответов

голоса
2k

Почему это необходимо?

Когда данные хранятся на дисковых устройствах хранения, она хранится в виде блоков данных. Эти блоки доступны в полном объеме, что делает их атомарная операция доступа к диску. Дисковые блоки структурированы так же, как связанные списки; оба содержат раздел для данных, указатель на местоположение следующего узла (или блока), и оба не должны быть сохранены смежно.

В связи с тем , что количество записей можно отсортировать только на одном поле, мы можем утверждать , что поиск на поле, не отсортированных требует линейного поиска , который требует N/2блок доступы (в среднем), где Nэто число блоков, таблица охватывает. Если это поле не является ключевым полем (т.е. не содержит уникальные записи) , то все табличное нужно искать в Nблоке доступов.

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

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

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

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

Как это работает?

Во-первых, давайте наметить схему таблицы базы данных выборки;

Имя поля Тип данных Размер на диске
идентификатор (первичный ключ) Неподписанный INT 4 байта
Firstname Char (50) 50 байт
ЬавЬЫате Char (50) 50 байт
EMAILADDRESS CHAR (100) 100 байт

Примечание : символ был использован вместо VARCHAR для обеспечения точного размера на величину диска. Эта база данных содержит образец пять миллионов строк и проиндексированных. Выполнение нескольких запросов теперь будет проанализировано. Это запрос с использованием идентификатора (отсортированное ключевым полем) и один с помощью Firstname (не-ключ несортированного поля).

Пример 1 - отсортировано против несортированных полей

Учитывая наш образец базы данных r = 5,000,000записей фиксированного размера дает длину записи R = 204байт , и они хранятся в таблице , используя MyISAM двигатель , который использует размер блока по умолчанию B = 1,024байт. Блокирующий фактор таблицы будет bfr = (B/R) = 1024/204 = 5записью на диск блок. Общее количество блоков , необходимых для хранения таблицы является N = (r/bfr) = 5000000/5 = 1,000,000блоками.

Линейный поиск в поле идентификатора требует в среднем N/2 = 500,000блоке доступы , чтобы найти значение, учитывая , что поле ID является ключевым полем. Но поскольку поле идентификатор также сортируются, бинарный поиск может проводиться потребует в среднем log2 1000000 = 19.93 = 20обращений к блокам. Немедленно мы можем видеть , что это резкое улучшение.

Теперь ПгвЬЫат поле ни отсортированное , ни ключевое поле, поэтому бинарный поиск невозможно, равно как и ценности уникальны, и поэтому таблица потребует поиск до конца для точных N = 1,000,000обращений к блокам. Именно такая ситуация , что индексирование стремится исправить.

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

Имя поля Тип данных Размер на диске
Firstname Char (50) 50 байт
(Запись указатель) Специальные 4 байта

Примечание : Указатели в MySQL 2, 3, 4 или 5 байт в длину в зависимости от размера таблицы.

Пример 2 - индексация

Учитывая наш пример базы данных r = 5,000,000записей с длиной индекса рекордных R = 54байт и используя размер блока по умолчанию B = 1,024байт. Блокирующий фактор индекса будет bfr = (B/R) = 1024/54 = 18записью на диск блок. Общее количество блоков , необходимых для хранения индекса является N = (r/bfr) = 5000000/18 = 277,778блоками.

Теперь поиск , используя ПгвЬЫат поле может использовать индекс для увеличения производительности. Это позволяет двоичный поиск индекса со средним значением log2 277778 = 18.08 = 19блока доступов. Чтобы найти адрес фактической записи, которая требует дальнейшего доступа блока для чтения, в результате чего их общего числа 19 + 1 = 20обращений к блокам, далеко от 1,000,000 блока доступов требуется найти ПгвЬЫат матч в Неиндексированной таблице.

Когда следует использовать?

Учитывая, что создание индекса требует дополнительного дискового пространства (277,778 блоков дополнительного из приведенного выше примера, а ~ 28% больше), и что слишком много индексов могут вызвать проблемы, вытекающие из ограничений на размере файловых систем, продумано должен использоваться, чтобы выбрать правильные поля для индексирования.

Поскольку индексы используются только для ускорения поиска для поля соответствия в записях, само собой разумеется, что индексирование поля, используемые только для вывода будет просто трата дискового пространства и времени обработки при выполнении вставки или удаления, и, таким образом, необходимо избегать. Кроме того, учитывая характер бинарного поиска, мощность или уникальность данных важна. Индексирование на поле с мощностью 2 будет разделить данные в два раз, в то время как мощность 1000 будет возвращать около 1000 записей. При такой низкой мощности эффективность сводится к линейному рода, а оптимизатор запросов будет избегать использования индекса, если мощность составляет менее 30% от рекордного числа, эффективно делая индекс траты пространства.

Ответил 04/08/2008 d 11:41
источник пользователем

голоса
168

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

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

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

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

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

например: - Если у вас есть много противоборствующей запись, но только один по ночам читали в нерабочее время для отчетности.

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

Помогал мне: - Что кластерные и не кластерный индекс на самом деле означает?

Ответил 30/04/2013 d 15:31
источник пользователем

голоса
124

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

Для получения дополнительной информации, я рекомендую: Как работают индексы базы данных? И, как индексы помогают?

Ответил 20/02/2014 d 15:40
источник пользователем

голоса
86

Теперь, давайте предположим, что мы хотим, чтобы выполнить запрос, чтобы найти все подробности любых сотрудников, которые названы «Abc»?

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

Что произошло бы без индекса?

Программное обеспечение баз данных буквально должны смотреть на каждую строку в таблице Employee , чтобы увидеть , если Employee_Name для этой строки является «Abc». И, потому что мы хотим , чтобы каждая строка с именем «Abc» внутри него, мы не можем просто перестать смотреть , как только мы находим только одну строку с именем «Abc», потому что могут быть и другие строки с именем Abc . Таким образом, каждая строка вплоть до последней строки , нужно искать - это значит , тысячи строк в этом случае должно быть рассмотрено в базе данных , чтобы найти строки с именем «Abc». Это то , что называется полное сканирование таблицы

Как индекс базы данных может помочь производительности

Весь смысл иметь индекс, чтобы ускорить поисковые запросы, по существу, сокращая количество записей / строк в таблице, которые должны быть рассмотрены. Индекс представляет собой структуру данных (наиболее обычно представляет собой B- дерево), которое хранит значения для конкретного столбца в таблице.

Как работает индекс B-дерева?

Причина B- дерева являются наиболее популярной структура данных для индексов связана с тем, что они время эффективны - потому что взгляд окно, делеция и вставка могут быть сделаны в логарифмическое время. И еще одна важная причина, B- деревья чаще используются потому, что данные, которые хранятся внутри дерева B- может быть отсортирован. СУБД, как правило, определяет, какая структура данных фактически используется для индекса. Но, в некоторых случаях с некоторым РСУБДОМ, вы можете указать, какие структуры данных вы хотите, чтобы ваша базу данных для использования при создании самого индекса.

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

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

Например, запрос мы обсуждали ранее могли извлечь выгоду из индекса хеш, созданного на колонке Employee_Name. Путь индекс хэш будет работать в том, что значение столбца будет ключ в хэш-таблицу и фактическое значение отображается на этот ключ будет просто указатель на данные строки в таблице. Поскольку хэш-таблица в основном ассоциативный массив, типичная запись будет выглядеть как «Abc => 0x28939", где 0x28939 является ссылкой на строку таблицы, где Abc хранятся в памяти. Глядя значение, как «Abc» в индексе хеш-таблицы и получить назад ссылку на строку в памяти, очевидно, намного быстрее, чем сканирование таблицы, чтобы найти все строки со значением «Abc» в колонке Employee_Name.

Недостатки индекса хэша

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

Что именно находится внутри индекса базы данных? Итак, теперь вы знаете , что индекс базы данных создается на столбец в таблице, а индекс хранит значения в этой конкретной колонке. Но важно понимать , что индекс базы данных не хранит значения в других столбцах той же самой таблицы. Например, если мы создадим индекс на столбце Employee_Name, это означает , что значения столбцов Employee_Age и Employee_Address не также хранятся в индексе. Если мы не просто хранить все остальные столбцы в индексе, то это было бы так же , как создать еще одну копию всей таблицы - что заняло бы слишком много места , и было бы очень неэффективно.

Как знать , базы данных , когда использовать индекс? Когда запрос типа «SELECT * FROM Employee WHERE Employee_Name =„Abc“» запускается, база данных будет проверять , чтобы увидеть , если есть индекс столбца (ов) выполняется запрос. Если предположить , что столбец Employee_Name действительно имеет индекс , созданный на нем, базе данных будет решить , что на самом деле имеет ли смысл использовать индекс для поиска значения ищется - потому что есть некоторые ситуации , в которых он является на самом деле менее эффективно использовать индекс базы данных и эффективнее просто сканировать всю таблицу.

Какова стоимость наличия индекса базы данных?

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

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

Смотрите также

  1. Какие столбцы обычно делают хорошие показатели?
  2. Как индексы базы данных работают
Ответил 13/08/2016 d 18:36
источник пользователем

голоса
64

Классический пример «Индекс в книгах»

Рассмотрим «Книга» 1000 страниц, разделенный на 100 секций, каждая секция с X-страниц.

Просто, да?

Теперь, без индексной страницы, чтобы найти конкретный раздел, который начинается с буквой «S», у вас нет другого выбора, кроме сканирования через всю книгу. то есть: 1000 страниц

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

Но тогда, в дополнение к 1000 страниц, вам потребуется еще ~ 10 страниц для отображения на индексную страницу, так тотально 1010 страниц.

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

Вещи просто в школах, не так ли? :П

Ответил 23/04/2017 d 14:43
источник пользователем

голоса
40

Простое описание !!!!!!!!!!

Индекс не что иное, как структура данных, которая хранит значения для конкретного столбца в таблице. Индекс создается на столбце таблицы.

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

Теперь, давайте предположим, что мы хотим, чтобы выполнить запрос, чтобы найти все подробности любых пользователей, которые названы «Джон». Если выполнить следующий запрос.

SELECT * FROM User 
WHERE Name = 'John'

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

CREATE INDEX name_index
ON User (Name)

Индекс состоит из значений столбцов (например: Джон) из одной таблицы, и что эти значения сохраняются в структуре данных.
Так что теперь база данных будет использовать индекс для поиска сотрудников по имени Джон , так как индекс будет предположительно быть отсортированы в алфавитном порядке по имени пользователей. И, так как он сортируется, это означает поиск по имени намного быстрее , потому что все имена , начиная с «J» будет прямо рядом друг с другом в индексе!

Ответил 02/08/2016 d 01:30
источник пользователем

голоса
18

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

Ответил 14/01/2015 d 06:44
источник пользователем

голоса
14

Подумать только индекс базы данных, как индекс книги. Если у вас есть книга о собаках, и вы хотите найти информацию о, скажем, немецкие овчарки, вы могли бы, конечно, пролистать все страницы книги и найти то, что вы ищете, но это, конечно, отнимает много времени и не очень быстро. Другой вариант заключается в том, что вы можете просто перейти в раздел индекса книги, а затем найти то, что вы ищете, используя имя объекта, которого вы ищете (в данном случае, немецкие овчарки), а также смотреть на номер страницы быстро найти то, что вы ищете. В базе данных, номер страницы, называется указателем, который направляет базу данных по адресу, указанному на диске, где находится объект. Используя ту же немецкую овчарку аналогию, мы могли бы иметь что-то вроде этого ( «Немецкой овчарки»,

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

Ответил 21/12/2016 d 17:16
источник пользователем

голоса
7

Индекс SQL является то, что связано с ускорив поиск в SQL базе данных. Индекс позволяет программисту извлекать данные из базы данных очень быстро. Предположим, вы студент или какой-нибудь книги. Ваша книга содержит 50000 страниц. Первый день вы читали некоторые темы «ABC» на следующий день вы хотите прочитать некоторые другую тему «А». вы никогда не будете вручную пройти через постранично. Что вы будете делать в этой ситуации является использование индекса книги, чтобы посмотреть некоторые конкретные темы, а затем перейти непосредственно к теме. Список сохранил много времени, чтобы найти тему. То же самое в индексе SQL, индекс позволяет очень быстро найти миллионы записей из базы данных.

Ответил 15/02/2018 d 10:17
источник пользователем

голоса
2

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

Ответил 09/07/2018 d 05:33
источник пользователем

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more