access что такое индекс
Сводные таблицы Excel
Каждая таблица реляционной базы данных состоит из строк и столбцов и предназначена для хранения данных об объектах информационной системы (рис. 1.3.). В дальнейшем будем придерживаться терминов «поле» и «запись».
Поле может содержать данные только одного из допустимых типов (тип данных) для конкретно используемой базы данных, например MS Access, MS SQL Server, Oracle и т. д.
Рис. 1.3. Схема таблицы реляционной базы данных
Значения некоторых терминов, применяемых в реляционной модели данных и приведенных выше, легко понять, изучив содержимое конкретной таблицы (рис. 1.4).
Рис. 1.4. Таблица Здания
Сущность — объект любого происхождения, данные о котором хранятся в базе данных.
Атрибут — свойство, характеризующее сущность. В таблице представляет собой заголовок столбца.
Схема отношения — список имен атрибутов.
Ключ представляет собой поле или группу полей, причем таких, которые однозначно определяют любую запись в таблице реляционной базы данных. Ключ предназначен для:
В реляционных базах данных ключ реализуется с помощью индекса.
Индекс — указатель на данные, размещенные в реляционной таблице. Он предоставляет информацию о точном физическом их расположении.
Одним из основных требований, предъявляемых к СУБД, является возможность быстрого поиска требуемых записей. В реляционных базах данных для реализации этого требования как раз и служат индексы. Индекс очень похож на алфавитный указатель в книге.
Например, у вас в руках книга по Microsoft Office Access 2010 и вы хотите узнать о том, что написано в ней об индексах. Загляните в конец книги и найдите в предметном указателе слово «индекс». Так как указатель отсортирован по алфавиту, вы без труда найдете нужное слово и ссылки на страницы, где оно встречается в книге. Индекс работает с таблицей по такому же принципу. Он содержит отсортированные значения указанного поля таблицы и ссылки на номера записей таблицы, где эти значения находятся. При поиске записи система управления базами данных сначала просматривает индекс, что занимает совсем немного времени, т. к. для этого используется специальный алгоритм, находит ссылку на номер записи и по ней — нужную строчку в таблице. Таким образом, отпадает необходимость последовательного просмотра всех записей в таблице. Аналогичный способ применяется при поиске по списку прокси-серверов на сайте proxy ua.
При создании индекса в нем располагается информация о местонахождении записей, относящихся к индексированному полю. При добавлении новых записей в таблицу или удалении имеющихся индекс модифицируется в реальном времени.
Рассмотрим работу индекса на конкретном примере. На рис. 1.5 показан фрагмент таблицы Телефоны и индекса, построенного по полю Номер. Требуется найти владельца телефонного номера 4983217. При поиске СУБД в первую очередь находит номер телефона, расположенный ровно в центре столбца (номера в индексе отсортированы по возрастанию). Это номер 4987312, который больше требуемого. Вывод: рассматривать нижнюю половину индексного файла для дальнейшего поиска не имеет смысла. Для десятимиллионного телефонизированного города это пять миллионов записей. Второй шаг — обращение точно в центр верхней половины. Там расположен абонент 2791519. Его номер меньше искомого. Долой еще два с половиной миллиона записей на этот раз из верхней четверти столбца. Третий шаг исключит из рассмотрения восьмую часть абонентов. Четвертый — шестнадцатую и т. д. Очень скоро работа алгоритма будет закончена. Нужный нам владелец телефонного номера будет найден. Его фамилия — Степанов.
Рис. 1.5. Поиск владельца телефона по его номеру при помощи индекса
«Добавим 2 млн статей и посмотрим, что будет»: как ускорить базу данных с помощью индексов
Разбираемся, как ускорить работу базы данных приложений и сайтов, что такое индексы и как они устроены. Пособие для начинающих backend-разработчиков.
PHP-разработчик digital-агентства «Атвинта», в свободное время пишу на Go/C#/C++. Нравится проектировать и продумывать highload-системы.
Базы данных — это совсем не сложно, даже новички быстро вливаются в тему и начинают работать практически без проблем. А что сложного? Есть таблицы, в них записываем строки — всё просто. Да, и всё работает, никто не жалуется. Пока не наступит момент… когда данных будет много.
Тут нам и приходят на помощь индексы. Во всех базах данных они работают примерно по одному и тому же принципу. В этой статье я буду использовать MariaDB.
Запрос на выборку без индексов
Рассмотрим на простом примере. Есть таблица articles со следующей структурой:
Добавим в таблицу несколько записей:
И сделаем следующий запрос:
Ничего удивительного: простой запрос и выполняется быстро. Но что будет, если данных «чуть-чуть» больше? Давайте добавим, например, 2 млн статей.
И повторим запрос на выборку:
Как видим, время выполнения запроса увеличилось. Хоть и две секунды, но это долго. И нагрузка на диск высокая.
Две секунды на выполнение запроса — не предел; когда данных ещё больше, всё будет ещё хуже. Оптимизировать этот запрос можно с помощью индексов.
Запрос на выборку с индексом
Создаем индекс по колонке views из таблицы articles.
И повторяем запрос:
Вот! Так намного лучше. Выборка проходит так же быстро, как и с тремя записями. В чём же подвох? Как это работает и почему? Что может пойти не так?
Как устроен запрос без индекса и с ним
Запрос к выборке БД без индекса
Что происходит, когда мы запрашиваем данные? А что вы делаете, когда ищете нужную вам строку в таблице? Да, база данных сканирует всю таблицу и выбирает те записи, которые попадают под условия.
Это происходит быстро, когда у нас три записи, и долго, когда их очень много. Ведь наша таблица хранится на физическом носителе и, чтобы просмотреть её всю, нужно считать немало данных.
Запрос к выборке БД с индексом
Я часто встречаю, что индекс путают с id или уникальным идентификатором, считают, что это одно и то же. Это не так! Индекс в базах данных — это другое.
Индекс, который мы создали, представляет из себя такую структуру данных, как B-дерево. Но, например, в InnoDB используется B+-дерево. Всё зависит от подсистемы хранения, а в целом принцип их работы похож. Это дерево строится по колонке views из таблицы articles.
Чтобы понять, как происходит выборка с индексом, нужно знать, как работает B-дерево.
Перед нами B-дерево индекса. В каждом узле хранятся элементы со значениями; в нашем случае это значения из поля views. Также элементы хранят ссылку на строку в таблице.
Поиск начинается с корневого узла. Наша задача — пройти по каждому элементу в узле и сравнить его значение с искомым:
Дерево из примера выше не является копией того, которое построила БД в моём случае. Это я изобразил, чтобы показать, как проходит поиск по дереву.
Рассмотрим алгоритм на примере поиска значения 2001.
То, что мы и искали. А так как искомая ячейка содержит ссылку на место, где лежат наши данные, то мы можем легко и быстро прочитать их.
Ещё один способ запроса с индексом
В данной структуре можно легко делать выборку по диапазонам, например views >= 1000. В случае таких запросов индекс также поможет.
Хоть поиск и значительно ускорился, есть и свои нюансы. Изменения в В-дереве — не самая быстрая операция.
Необходимо, чтобы все конечные узлы (листья) дерева находились на одном уровне, а количество элементов в узлах было одинаковым — тогда мы получим наивысшую скорость выборки.
Чтобы придерживаться этих условий, нужно постоянно проводить перебалансировку дерева. Это и замедляет работу.
Если вы используете несколько десятков индексов в одной таблице, то при вставке или удалении из неё нужно проводить такие нехитрые манипуляции с деревьями. Из этого следует вывод, что не стоит увлекаться и создавать индексы по каждому полю.
Мы рассмотрели создание индекса по одной колонке (views), но в базах данных одной колонкой не ограничишься. Можно создавать составные индексы. Например, если есть поле views и дата created_at, и вы хотите делать подобные запросы: views = 1000 and created_at = «10.10.2019», то имеет смысл создать индекс по двум колонкам.
Инструкция CREATE INDEX (Microsoft Access SQL)
Область применения: Access 2013, Office 2013
Создает новый индекс в существующей таблице.
Ядро СУБД Microsoft Access не поддерживает использование CREATE INDEX (кроме как для создания псевдоиндекса в связанной таблице ODBC) или любых других инструкций DDL с базами данных, которые не основаны на ядре СУБД Microsoft Access. Используйте вместо этого методы DAO Create. Дополнительные сведения см. в разделе «Примечания».
Синтаксис
Инструкция CREATE INDEX включает в себя следующие элементы:
Имя создаваемого индекса.
Имя существующей таблицы, в которой будет создан индекс.
Имя одного или нескольких полей для индексации. Чтобы создать индекс по одному полю, укажите имя поля в круглых скобках после имени таблицы. Чтобы создать индекс по нескольким полям, укажите имена всех полей, включаемых в индекс. Чтобы создать индексы с упорядочением по убыванию, используйте зарезервированное слово DESC; в противном случае будут созданы индексы с упорядочением по возрастанию.
Примечания
Чтобы запретить появление повторяющихся значений в одном или нескольких индексированных полях, используйте зарезервированное слово UNIQUE.
Чтобы определить правила проверки данных, можно использовать необязательное предложение WITH. Вы можете:
Запретить значения NULL в индексированных полях новых записей с помощью параметра DISALLOW NULL.
Предотвратить индексирование записей со значениями NULL в одном или нескольких индексированных полях с помощью параметра IGNORE NULL.
Определить одно или несколько индексированных полей в качестве первичного ключа с помощью зарезервированного слова PRIMARY. Поскольку подразумевается, что первичный ключ уникален, зарезервированное слово UNIQUE можно опустить.
Инструкция CREATE INDEX может быть использована для создания псевдоиндекса в связанной таблице источника данных ODBC, такого как Microsoft SQL Server, если в ней еще нет индекса. Для создания псевдоиндекса не требуется разрешения или доступа к удаленному серверу, а на удаленном сервере никак не отразится наличие псевдоиндекса. Для связанных и исходных таблиц используется один и тот же синтаксис. Особенно полезным может быть создание псевдоиндекса в таблице, которая будет использоваться преимущественно для чтения.
Чтобы добавить индекс по одному полю или по набору полей в таблице, можно также воспользоваться инструкцией ALTER TABLE. Чтобы удалить индекс, созданный с помощью инструкции ALTER TABLE или CREATE INDEX, можно воспользоваться инструкцией ALTER TABLE или DROP.
Если в таблице уже есть первичный ключ, не используйте зарезервированное слово PRIMARY при создании в ней нового индекса: это приведет к ошибке.
Пример
В этом примере создается индекс, состоящий из полей Home Phone (Домашний телефон) и Extension (Расширение) в таблице Employees (Сотрудники).
В этом примере создается индекс в таблице Customers (Клиенты) с помощью поля CustomerID (КодКлиента). Никакие две записи не могут содержать одинаковые данные в поле CustomerID (КодКлиента), и не допускаются значения NULL.
Создание и использование индексов
С целью ускорения поиска и сортировки данных в любой СУБД используются индексы. Индекс является средством, которое обеспечивает быстрый доступ киданным в таблице на основе значений одного или нескольких столбцов. Индекс представляет собой упорядоченный список значений и ссылок на те записи, в которых хранятся эти значения. Чтобы найти нужные записи, СУБД сначала ищет требуемое значение в индексе, а затем по ссылкам быстро отбирает соответствующие записи. Индексы бывают двух типов: простые и составные. Простые индексы представляют собой индексы, созданные по одному столбцу. Индекс, построенный по нескольким столбцам, называется составным. Примером составного индекса может быть индекс, построенный по столбцам «Фамилия» и «Имя».
Однако применение индексов приносит не только преимущества, но и недостатки. Главным среди них является тот, что при добавлении и удалении записей или при обновлении значений в индексном столбце требуется обновлять индекс, что при большом количестве индексов в таблице может замедлять работу. Поэтому индексы обычно рекомендуется создавать только для тех столбцов таблицы, по которым наиболее часто выполняется поиск записей. Во многих СУБД (например, FoxPro) индексы хранятся в отдельных файлах и являются предметом заботы разработчиков, т. к. при нарушении индекса поиск данных выполняется некорректно. В Microsoft Access индексы хранятся в том же файле базы данных, что и таблицы и другие объекты Access. Индексировать можно любые поля, кроме МЕМО-полей, полей типа Гиперссылка и объектов OLE.
Чтобы создать простой индекс, необходимо:
Ключевое поле таблицы автоматически индексируется и свойству Индексированное поле (Indexed) присваивается значение Да (Совпадения не допускаются) (Yes (No duplicates)).
Составной индекс создается в специальном диалоговом окне. Чтобы создать составной индекс, необходимо:
По умолчанию устанавливается порядок сортировки По возрастанию (Ascending). Для сортировки данных полей по убыванию в поле Порядок сортировки (Sort Order) в окне индексов укажите значение По убыванию (Descending).
Диалоговое окно Индексы (Indexes) используется также для просмотра, изменения и удаления существующих индексов. Изменить можно:
Удаление индекса выполняется точно так же, как удаление поля в Конструкторе таблиц. Просто выделите строку с нужным индексом и нажмите клавишу или воспользуйтесь контекстным меню.
Введение в использование типов данных и свойств полей
Каждая таблица в Access состоит из полей. В свойствах поля описываются характеристики и поведение добавляемых в него данных. Тип данных поля — это самое важное свойство, которое определяет, какие данные могут храниться в поле. В этой статье описаны типы данных и другие свойства поля, доступные в Access, а также приведена дополнительная информация в разделе справочных сведений о типах данных.
В этой статье
Общие сведения
Иногда типы данных могут показаться неочевидными, например в поле с типом данных «Текст» могут храниться данные, состоящие из текста и чисел. Но в поле с типом данных «Число» могут храниться только числовые данные. Поэтому вам нужно знать, какие свойства используются для каждого типа данных.
Тип данных поля определяет много других важных характеристик поля, в частности:
форматы, которые можно использовать в поле;
максимальный размер значения в поле;
способ использования поля в выражениях;
возможность индексирования поля.
В зависимости от способа создания нового поля тип данных поля может быть задан заранее или его можно выбрать. Например, если при создании поля в режиме таблицы вы:
используете существующее поле из другой таблицы, типы данных уже определены в ней или в шаблоне;
вводите данные в пустом столбце (или поле), Access назначает полю тип данных, исходя из вводимых значений, или вы можете назначить тип данных и формат для поля;
на вкладке Изменение полей в группе Поля и столбцы выбираете команду Добавить поля, Access отображает список типов данных для выбора.
Когда какой тип данных использовать?
Тип данных поля можно обдумать как набор характеристик, которые применяются ко всем его значениям. Например, значения, которые хранятся в текстовом поле, могут содержать только буквы, цифры и ограниченный набор знаков препинания, а текстовое поле может содержать не более 255 знаков.
Совет: Иногда все выглядит так, как будто данные в поле имеют один тип, а на самом деле это данные другого типа. Например, поле вроде бы содержит числовые значения, но на самом деле это текстовые значения, представляющие номера комнат. Часто для сравнения или преобразования значений с разными типами данных используются выражения.
В таблицах ниже показаны форматы, доступные для каждого типа данных, и описаны результаты форматирования.
Основные типы
Короткие буквенно-цифровые значения, например фамилия или почтовый адрес. Помните, что начиная с версии Access 2013, текстовый тип данных переименован в Краткий текст.
Числовой, Большое число
Числовые значения, например расстояния. Помните, что для денежных значений есть отдельный тип данных.
Значения «Да» и «Нет», а также поля, содержащие только одно из двух значений.
Date/Time, Date/Time Extended
Дата/время: значения даты и времени для лет от 100 до 9999.
Дата/время с расширением: значения даты и времени для лет с 1 по 9999.
Текст или сочетание текста и чисел, которые отформатированы с помощью элементов управления цветом и шрифтом.
Результаты вычисления. Вычисление может ссылаться на другие поля в той же таблице. Вычисления создаются с помощью построителя выражений. Вычисляемые поля впервые появились в Access 2010.
Вложенные изображения, файлы электронных таблиц, документы, диаграммы и другие файлы поддерживаемых типов в записях базы данных (как и в сообщениях электронной почты).
Текст или сочетание текста и чисел, сохраненное как текст и используемое в качестве адреса гиперссылки.
Длинные блоки текста. Типичный пример использования поля MEMO — подробное описание продукта. Помните, что начиная с версии Access 2013, тип данных MEMO переименован в «Длинный текст».
Список значений, которые получены из таблицы или запроса, или набор значений, которые вы указали при создании поля. Запускается мастер подстановок, с помощью которого можно создать поле подстановки. В зависимости от выбора, сделанного в мастере, данные в поле подстановки могут иметь текстовый или числовой тип.
У полей подстановки есть дополнительный набор свойств, которые находятся на вкладке Подстановка в области Свойства поля.
Примечание: В файлах формата MDB недоступны вложения и вычисляемые данные.
Числовой
Числа без дополнительного форматирования (точно в том виде, в котором хранятся).
Обычные денежные значения.
Обычные денежные значения в формате ЕС.
Числовые данные с десятичными знаками.
Значения в процентах.
Дата и время
Краткий формат даты
Дата в кратком формате. Зависит от региональных параметров даты и времени. Например, 14.03.2001 для России.
Средний формат даты
Дата в среднем формате. Например, 03-апр-09 для России.
Длинный формат даты
Дата в длинном формате. Зависит от региональных параметров даты и времени. Например, 14 марта 2001 г. для России.
Время только в 12-часовом формате, который будет соответствовать изменениям в региональных параметрах даты и времени.
Средний формат времени
Время в 12-часовом формате, после которого указываются символы AM (до полудня) или PM (после полудня).
Время только в 24-часовом формате, который будет соответствовать изменениям в региональных параметрах даты и времени.
Логический
Объект OLE Объекты OLE, например документы Word.
Свойство «Размер поля»
После создания поля и указания типа данных для него можно настроить дополнительные свойства поля. Набор доступных дополнительных свойств зависит от типа данных поля. Например, вы можете настроить размер текстового поля с помощью свойства Размер поля.
Для числовых и денежных полей свойство Размер поля особенно важно, поскольку определяет диапазон значений поля. Например, одноразрядное числовое поле может содержать только целые числа в диапазоне от 0 до 255.
Свойство Размер поля определяет также, сколько места на диске занимает каждое значение числового поля. В зависимости от размера поля число может занимать 1, 2, 4, 8, 12 или 16 байт.
Примечание: В полях MEMO и текстовых полях возможны значения переменных размеров. Для этих типов данных свойство Размер поля задает максимальный размер доступного пространства для одного значения.
Дополнительные сведения о свойствах полей и той роли, которую они выполняют для различных типов данных, см. в разделе Справочные сведения о типах данных. Ознакомьтесь также со статьей Задание размера поля.
Типы данных в связях и соединениях
Связь между таблицами — это связи между общими полями в двух таблицах. Связь может быть одного из следующих типов: один к одному, один ко многим, многие ко многим.
Объединение — это SQL, которая объединяет данные из двух источников в одну запись в запросе набор записей на основе значений в указанном поле, которые есть у них общие. Присоединиться может быть внутреннее соединение, левое внешнее соединение или правое внешнее соединение.
Когда вы создаете связь между таблицами или добавляете соединение в запрос, типы данных в соединяемых полях должны быть одинаковые или совместимые. Например, вы не сможете создать соединение между числовым и текстовым полями, даже если значения в этих полях совпадают.
При использовании связи или соединения поля с типом данных «Счетчик» совместимы с полями числового типа, если для свойства Размер поля последних задано значение Длинное целое.
Для поля, участвующего в связи между таблицами, нельзя изменить тип данных или свойство Размер поля. Чтобы изменить свойство Размер поля, временно удалите связь. Но после изменения типа данных вы не сможете снова создать связь, пока не измените тип данных связанного поля. Дополнительные сведения о таблицах см. в статье Общие сведения о таблицах.
Справочные сведения о типах данных
Тип данных, применяемый к полю, содержит набор свойств, которые вы можете выбрать. Чтобы получить дополнительные сведения, щелкните типы данных ниже.