Индексы: первый шаг на&nb p;пути к&nb p;скорости

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

 

 

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

При использовании индексов сервер баз данных гораздо быстрей извлечет данные Рассмотрим, каким образом индекс таблицы может ускорить обработку запросов. В таблице, не имеющей индекса, записи хранятся беспорядочным образом, и при попытке извлечения информации сервер баз данных просканирует все записи с целью установления совпадения с условиями. В качестве примера возьмем базу данных сервера MySQL, в которой хранится информация о товарах. Предположим, что нам нужно получить все строки о товарах, произведенных в конкретной стране, и если эта операция осуществляется часто, то вместо перебора всех строк мы можем проиндексировать таблицу с товаром items по полю, содержащему номера стран country_id. Созданный индекс будет содержать запись о каждой строке в таблице, причем его записи будут отсортированы по полю country_id. Теперь при выполнении запросов сервер баз данных может узнать, какие записи понадобятся из индекса, причем если нам нужна страна с номером 5, то, дойдя в индексе до номера 6, сервер баз данных может прекратить поиск — ведь строк с номером 5 в индексе явно больше не будет.

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

При выборке информации из нескольких таблиц индексы еще более эффективны Описанный выше пример сокращал время просмотра в одной таблице, но использование индексов даст еще большую скорость при обработке запросов, в которых информация извлекается из нескольких таблиц и объединяется. В этих случаях сначала происходит поиск строки в одной таблице, на основе полученной информации ищется строка в следующей таблице и так далее. Поэтому для полей, которые задействованы в условиях WHERE, крайне настоятельно рекомендуется использовать индексы. Понятно, что при объединении таблиц наиболее часто используются поля, обеспечивающие уникальность строк в таблицах, поэтому еще при создании таблиц надо побеспокоиться о создании индексов для этих полей. Это сделать достаточно просто — достаточно указать при объявлении поля ключевого поля PRIMARY KEY или UNIQUE, и для таких полей индекс будет создан автоматически. В нашей базе данных в каждой таблице существует поле, объявленное с таким ключом, что позволяет сразу решить проблему индексации для самых ресурсозатратных запросов.

При использовании индексов необходимо помнить об их недостатках Помимо оптимизации запросов с условиями WHERE, индексы могу принести пользу и в других случаях. Очевидно, что сортировка выбранных данных тоже будет осуществляться быстрее при индексировании по этому полю. Также быстрее будет происходить выборка данных при использовании агрегатных функций MIN() и MAX() для получения максимального и минимального значений при группировках строк. В некоторых случаях, если требуется извлечь информацию, которую может предоставить сам индекс, обращения к таблице базы данных вообще не осуществляются. Может сложиться неверное представление, что необходимо индексировать каждое поле в таблице — хуже не будет. Это не совсем так — индексы, несмотря на всю их очевидную пользу, имеют и некоторые недостатки. Самая бросающаяся в глаза проблема заключается в том, что файл любого индекса занимает определенное место на диске, и так как необходимость использования индексов наиболее актуальна для таблиц больших размеров, дополнительные индексные файлы будут расти вместе с ней.

Вторая проблема заключается в том, что индексы как ускоряют выборку информации, так и замедляют операции добавления, редактирования и удаления записей — ведь при этом необходимо вносить изменения во все индексы изменяемой таблицы. Существует также еще несколько рекомендаций, которые позволяют увеличить эффективность применения индексов, сводя к минимуму их недостатки. Одна рекомендация уже была упомянута: индексировать следует поля, которые ищутся, а не выбираются, то есть хорошим кандидатом на индексирование будет поле, упомянутое в условии WHERE. Также следует помнить, что гораздо эффективнее индексы используются для полей с уникальными значениями. Если же поле содержит много одинаковых значений, то индекс может себя и не оправдать. При создании индексов особенно для длинных символьных полей, необходимо проанализировать — нельзя ли обеспечить уникальность ключа не по всему полю, а первым 10-20 символам этого поля, что существенно сэкономит размер файла индекса и обеспечит ускорение выполнения запросов. Подобное ограничение для символьных полей рекомендуется, а для полей типа TEXT и BLOB является обязательной.

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

Создавайте индексы по мере необходимости Создать индекс для таблицы можно как во время ее создания, так и добавить потом, следуя простому правилу — индексы надо создавать по мере необходимости. При создании таблицы поле, обеспечивающее уникальность, надо объявить с ключевыми полями PRIMARY KEY или UNIQUE, что автоматически создаст индекс для этого поля. Такие поля не могут содержать повторяющихся значений или равняться NULL, и поэтому обрабатываются очень быстро. Точно так же при создании таблицы можно указать ключевое слово

INDEX имя_индекса список_полей,

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

ALTER TABLE имя_таблицы INDEX имя_индекса список_полей;

Индекс создается по столбцам, указанным в списке_полей. Если имя_индекса не заданно, то оно создается автоматически по имени первого индексируемого поля. Для символьных полей можно уменьшить длину индексированных значений, указав количество символов n для индексирования в имени_поля(n) Таким образом, мы для нашей таблицы можем создать индекс:

ALTER TABLE items INDEX country_id;

Если необходимость в индексе отпала, то его можно легко удалить, указа имя индекса:

ALTER TABLE items DROP INDEX country_id;

Существуют также синонимы этих команд с более понятным синтаксисом:

CREATE INDEX имя_индекса ON имя_таблицы список_полей;
DROP INDEX имя_индекса ON имя_таблицы;

Ссылки по теме

• Базы данных в веб-проектах
• Создание базы данных в MySQL
• MySQL в веб-проектах
• Выборка информации в MySQL

Статья получена: hostinfo.ru

загрузка...