Индексирование в базах данных. Базы данных индексы

3. Создание индексов и ограничений для базы данных

3.1. Индексы в базах данных

Принцип индексирования широко применяется при работе с таблицами базы данных. Когда таблица содержит большое число записей, ее полный просмотр с целью поиска определенных записей может занять у Oracle (как и у любой другой СУБД) много времени — сравните это с перелистыванием всей книги при поиске страниц, где обсуждается определенная тема. Oracle позволяет создавать вторую, скрытую таблицу, содержащую один или несколько важных столбцов из главной таблицы вместе с указателями на строки основной таблицы. В данном случае вместо номеров страниц указателями являются номера строк. Эту скрытую таблицу принято называть индексом. Просматривая индекс, Oracle может узнать, какие строки содержат запрашиваемые данные (при условии, что эти данные находятся в столбцах, составляющих индекс). Поскольку индекс намного меньше таблицы, на которую он ссылается (подобно тому как книжный индекс намного меньше полного текста книги), поиск данных в таблице с индексом будет выполняться, существенно быстрее, чем в таблице без индекса.

На рис. 3.1 показано, как выглядел бы стандартный индекс для таблицы PERSON. В данном случае таблица проиндексирована по столбцу PERSON_CODE. Важно, что индекс отсортирован по PERSON_CODE, хотя таблица хранит записи в последовательности их ввода.

Таблица PERSON Индекс PERSON_CODE

PERSON_CODE	FIRST_NAME	LAST_NAME	HIRE_DATE	PERSON_CODE	ROWNUM
СА	Chariene	Adas	01-Feb-02	BB	3
GA	Gary	Anderson	15-Feb-02	CA	1
ВВ	Bobby	Barkenhagen	01-Feb-02	GA	2
LВ	Laren	Baxter	01-Feb-02	LB	4

Рис. 3.1. Таблица с одним индексом

Записи индекса всегда сортируются по тем столбцам, которые он содержит. Столбец ROWNUM, входящий в индекс, отслеживает положение каждой строки в исходной таблице.

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

Таблица PURCHASE Индекс PRODUCT_NAME Индекс SALESPERSON

PRODUCT_NAME	QUANTITY	PURCHASE_DATE	SALES PERSON	PERSON_CODE	ROWNUM	SALESPERSON	ROW NUM
Small Widget	1	14-Jul-03	CA	Chrome Phoobar	3	BB	2
Medium Wodget	75	14-Jul-03	BB	Medium Wodget	2	CA	1
Chrome Phoobar	2	14-Jul-03	GA	Medium Wodget	5	CA	6
Small Widget	8	14-Jul-03	GA	Round Snaphoo	6	GA	3
Medium Wodget	20	14-JuMB	LB	Small Widget	1	GA	4
Round Snaphoo	5	14-Jul-03	CA	Small Widget	4	LB	5

Рис. 3.2. Таблица с индексами по двум разным столбцам

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

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

Добавление индексов к таблице не ускоряет ввод данных командами INSERT; фактически это дает обратный эффект, замедляя вставку. Поскольку индекс тоже является таблицей, то когда добавляется запись к проиндексированной таблице, Oracle должен добавить запись и к самой таблице, и к ее индексу, т.е. выполнить две вставки для каждой записи. В результате добавление индекса к таблице вызовет более чем двукратное увеличение времени вставки (собственно две вставки плюс небольшое дополнительное время на их координацию). Добавление двух индексов замедлит операцию вставки в три раза, трех индексов — в четыре раза, и т.д.

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

Чем больше таблица, тем больший выигрыш можно получить от ее индексирования. В качестве примера можно взять таблицу с миллионом записей. В табл. 3.1 показано, сколько времени занимали различные операции DML при наличии и отсутствии индекса. Первая строка содержит статистику по оператору SELECT, а последующие строки демонстрируют влияние индекса на команды UPDATE и DELETE.

Таблица 3.1

Время (секунды) выполнения операций DML при наличии и отсутствии индекса

Операция	Без индекса	С индексом	Увеличение скорости
SELECT (50 записей)	18.9	0.6	в 31.5 раза
UPDATE (50 записей)	19.7	0.5	в 39.4 раза
DELETE (50 записей)	19.6	0.06	в 326.7 раза

studfiles.net

SQL Server : Базы данных и индексы

Управление индексами и обеспечение наличия нужных индексов — важная часть общей системы управления SQL Server.

Выдержка из книги «SQL Server DMV Starter Pack» (Red Gate Books, 2010).

Гленн Берри, Луи Девидсон и Тим Форд

Microsoft логически разделяет объекты управления БД (DMO) на уровне базы данных и файлов на две категории:

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

Имена всех представлений в этих двух категориях начинаются с «sys.dm_db_». Эти типы представлений DMV помогают определить эффективную стратегию индексирования, так как это один из лучших способов обеспечения, чтобы самые важные и часто исполняемые запросы могли читать нужные им данные упорядоченными образом и не создавать лишнюю нагрузку на подсистему ввода/вывода. Определение правильного баланса между слишком большим и слишком малым количеством индексов и реализация «правильного» набора индексов исключительно важны для обеспечения максимальной производительности SQL Server.

Нужно также следить за базой данных TempDB. TempDB — глобальный ресурс, хранящий временные данные пользователя и внутренних объектов всех пользователей, подключенных к данному экземпляру SQL Server. Сюда, в частности, входят внутренние рабочие таблицы, в которых хранятся результаты курсоров, а также пользовательские объекты, такие как временные таблицы и табличные переменные.

Поиск отсутствующих индексов

Для обнаружения индексов, которых не хватает в базе данных, применяют три тесно связанных представления DMV. Первое, sys.dm_db_missing_index_group_stats, описывается следующим образом:

«Возвращает сводку сведений о группах отсутствующих индексов, за исключением пространственных индексов. Сведения, возвращаемые представлением sys.dm_db_missing_index_group_stats, обновляются при каждом выполнении запроса, а не при каждой компиляции или повторной компиляции запроса. Статистика использования не сохраняется и хранится только до перезапуска SQL Server. Администраторы базы данных должны периодически делать резервные копии сведений об отсутствующих индексах, если необходимо сохранить статистику использования после перезагрузки сервера».

А вот описание второго, sys.dm_db_missing_index_groups:

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

Это в сущности таблица соединения sys.dm_db_missing_index_group_stats и третьего представления DMV, sys.dm_db_missing_index_details, которое описывается так:

«Возвращает подробные сведения об отсутствующих индексах, за исключением пространственных индексов».

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

Рис. 1. Выявление индексов, которые могут быть полезны в базе данных

-- Missing Indexes in current database by Index Advantage SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] , migs.last_user_seek , mid.[statement] AS [Database.Schema.Table] , mid.equality_columns , mid.inequality_columns , mid.included_columns , migs.unique_compiles , migs.user_seeks , migs.avg_total_user_cost , migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID() ORDER BY index_advantage DESC ;

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

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

Нужно иметь в виду, что у этого подхода есть ряд ограничений. Во-первых, этот запрос не всегда указывает на наилучший порядок столбцов в индексе. Если в equality_columns or inequality_columns указывается несколько столбцов, нужно посмотреть на избирательность этих столбцов, чтобы определить оптимальный столбец в предполагаемом индексе. Во-вторых, не учитываются фильтруемые индексы, которые появились в SQL Server 2008. Наконец, в общем случае запрос стремится предложить включенные столбцы и новые индексы.

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

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

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

Не забывайте, что системная хранимая процедура sp_helpindex не показывает информацию включенных столбцов. Это означает, что нужно воспользоваться заменой или просто создать предложение CREATE INDEX для существующих индексов.

Анализ использования индексов

Одно из самых полезных представлений DMV в категории индексации — dm_db_index_usage_stats, которое описывается следующим образом:

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

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

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

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

Рис. 2. Эти сценарии позволяют выяснить, как используются индексы

--- Index Read/Write stats (all tables in current DB) SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] , i.name AS [IndexName] , i.index_id , user_seeks + user_scans + user_lookups AS [Reads] , user_updates AS [Writes] , i.type_desc AS [IndexType] , i.fill_factor AS [FillFactor] FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID() ORDER BY OBJECT_NAME(s.[object_id]) , writes DESC , reads DESC ;

Это полезный запрос для понимания имеющейся рабочей нагрузки. Он позволяет оценить изменчивость того или другого индекса, а также соотношение операций чтения и записи. Благодаря этому вы можете уточнить и точнее настроить свою стратегию индексации. Например, если у вас сравнительно статическая таблица (очень немного операций записи во всех индексах), можно увереннее добавлять индексы, из числа предлагаемых SQL Server.

В редакции SQL Server 2008 Enterprise Edition этот запрос позволяет решить, стоит ли включать сжатие данных (на уровне страниц или строк). Индекс с небольшим числом операций записи лучше подходит для сжатия данных, чем более активно изменяющийся индекс.

В следующем сценарии (рис. 3) sys.indexes и sys.objects используются для обнаружения в текущей базе данных таблиц и индексов, которые отсутствуют в результатах sys.dm_db_index_usage_stats. Это означает, что в этих индексах не было операций чтения или записи с момента последнего запуска SQL Server или с момента закрытия или отключения текущей БД (выбирается самая поздняя дата).

Рис. 3. Обнаружение неиспользуемых индексов

-- List unused indexes SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] , i.name FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] WHERE i.index_id NOT IN ( SELECT s.index_id FROM sys.dm_db_index_usage_stats AS s WHERE s.[object_id] = i.[object_id] AND i.index_id = s.index_id AND database_id = DB_ID() ) AND o.[type] = ‘U’ ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

Если SQL Server работал достаточно долго, чтобы рабочую нагрузку за этот период можно было считать полной и репрезентативной, скорее всего эти индексы (или таблицы) «мертвые». Это означает, что они больше не используются в базе данных и после небольшого исследования их можно удалить.

В последнем запросе результаты sys.dm_db_index_usage_stats фильтруются по текущей базе данных (рис. 4). Сюда входят некластеризованные индексы, Это позволяет решить, оправдывает ли сохранение индекса затраты на его поддержку.

Рис. 4. Обнаружение редко используемых индексов

-- Possible Bad NC Indexes (writes > reads) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i.name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference] FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1 AND s.database_id = DB_ID() AND user_updates > ( user_seeks + user_scans + user_lookups ) AND i.index_id > 1 ORDER BY [Difference] DESC , [Total Writes] DESC , [Total Reads] ASC ;

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

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

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

		Гленн Берри (Glenn Berry) работает архитектором баз данных в компании NewsGator Technologies из города Денвера, штат Колумбия. Он обладает званием MVP в области SQL Server, и обладает целым набором сертификатов Microsoft, в том числе MCITP, MCDBA, MCSE, MCSD, MCAD и MCTS, что означает, что ему действительно нравится сдавать сертификационные экзамены.

		Луи Девидсон (Louis Davidson) работает в области ИТ уже более 16 лет в качестве разработчика и архитектора корпоративных баз данных. Он носил звание SQL Server MVP на протяжении шести лет и написал четыре книги по проектированию баз данных. В настоящее время он занимается разработкой архитектуры баз данных и немного администрированием БД в компании Christian Broadcasting Network, поддерживая работу филиалов в г. Вирджиния-Бич, штат Вирджиния, и г. Нешвилл, штат Теннеси.

		Тимоти Форд (Timothy Ford) обладает званием SQL Server MVP и занимается SQL Server уже более десяти лет. Он является основным администратором баз данных и экспертом по платформе SQL Server в компании Spectrum Health. С 2007 года его статьи по вопросам технологии публикуются на различных веб-сайтах, а также Тим ведет собственный блог (thesqlagentman.com), в котором рассказывает о SQL, а также об удаленной работе и профессиональных секретах разработчиков.

technet.microsoft.com

Индексирование в базах данных

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

Термины «индекс» и «ключ» тесно связаны между собой.

Определение 1

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

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

Организация физического доступа к данным зависит от:

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

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

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

При создании ссылки на запись таблицы возможно использование 3 типов адресов: символического (идентификатора), относительного и абсолютного (действительного).

В практической деятельности зачастую используют 2 поисковых метода: бинарный (базируется на делении поискового интервала пополам) и последовательный.

Одноуровневая схема индексирования таблиц

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

Записи каждого блока располагаются по возрастанию значения ключа или свертки. Ключ последней записи каждого блока является его старшим ключом.

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

Двухуровневая схема индексирования таблиц

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

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

Виды индексов

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

Индексы, которые создаются пользователем для неключевых полей, называются вторичными (или пользовательскими) индексами. Индексные файлы, которые создаются для поддержания вторичных индексов, называют файлами вторичных индексов.

Некоторые СУБД (к примеру, Access), индексы на первичные и вторичные не делят.

Замечание 1

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

spravochnick.ru