W Cat - SQL за 24 часа Страница 39

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

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

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

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

• Не следует использовать индексы для небольших таблиц.

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

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

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

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

Следует избегать создания индексов для таблиц с ключами очень большой длины, поскольку скорость работы с такими таблицами заметно падает из-за больших объемов ввода/вывода

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

SELECT *

FROM ИМЯ_ТАБЛИЦЫ

WHERE GENDER = 'ЖЕН';

Взглянув на рис 16.2, вы увидите, что этот запрос вызывает непрерывный поток обращений от таблицы к индексу и наоборот. Из-за того, что условием WHERE GENDER = 'ЖЕН' (или МУЖ) возвращается большой объем данных, серверу базы данных придется постоянно читать сначала данные из индекса, затем соответствующую строку из таблицы и т. д. В данном случае гораздо более эффективным было бы простое сканирование всех данных таблицы, поскольку значительная ее часть все равно должна быть прочитана.

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

Рис. 16.2. Случай, когда создавать индекс не следует

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

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

Вы узнали о том, что использование индексов может повысить скорость выполнения запросов и транзакций базы данных. Индексы базы данных, как и предметный указатель книги, позволяют быстрее найти нужные данные по ссылкам на них. Чаще всего для создания индексов используется команда CREATE INDEX. Существует несколько типов индексов, зависящих от конкретной реализации SQL. Уникальные индексы, простые индексы и составные индексы относятся к наиболее часто встречающимся. При выборе типа индекса для использования в базе данных приходится учитывать целый рад факторов. Эффективное решение часто можно найти только в результате экспериментирования на базе четкого понимания структуры данных и связей между ними, а также терпения: все это поможет вам сэкономить силы и время.

Увеличивает ли индекс объем дискового пространства, необходимый для хранения данных таблицы?

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

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

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

Должны ли все индексы быть уникальными?

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

Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих урйков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".

1. Каковы главные недостатки использования индексов?

2. Почему важен порядок столбцов в составном индексе?

3. Следует ли создавать индекс по столбцу, в котором часто встречается значение NULL?

4. Является ли основной целью использования индекса недопущение повторений данных в таблице?

5. Верно ли следующее утверждение: "Главной причиной использования составных индексов является использование в таких индексах значений, по отношению к которым используются итоговые функции?"

1. Следует ли создавать индекс в следующих случаях, и если да, то какого типа индекс будет предпочтительнее?

а. Таблица имеет не много записей и несколько столбцов.

б. Таблица средней величины, но в ней не допускаются повторения.

в. Очень большая таблица, несколько столбцов которой используется в фильтрахключевого слова WHERE.

г. Большая таблица с множеством столбцов, предполагающая частые обновления данных.

17-й час Повышение эффективности работы с базой данных

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

Основными на этом уроке будут следующие темы.

• Что означает оптимизация операторов SQL?

• Оптимизация базы данных и оптимизация операторов SQL

• Форматирование операторов SQL

• Правильное связывание таблиц

• Наиболее ограничительные условия

• Полное сканирование таблиц

• Необходимость использования индексов

• Как избежать использования OR и HAVING

• Как избежать долгих операций сортировки

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