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

Выбор индекса

Синтаксис оператора создания индексов был рассмофен в разделе Создание и удаление индексов в главе 3, Синтаксис и использование языка SQL . Но знание синтаксиса само по себе не может помочь в индексировании таблицы. Это зависит от того, как будут использоваться таблицы. Эту главу можно рассматривать как руководство по идентификации и выбору столбцов - кандидатов на индексирование.

Необходимо индексировать искомые, а не выбираемые столбцы.

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

SELECT

со1 а <- не подходит на роль индексируемого столбца

FROM

tbll LEFT JOIN tbl2

ON tbll.col b = tbl2.col c <- подходит на роль индексируемого столбца WHERE

col d = ехрг <- подходит на роль индексируемого столбца

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

Столбцы, которые упоминаются в предложениях объединения или в выражениях вида coll = со12, можно назвать наиболее удачными кандидатами на индексирование. В качестве такого примера можно привести столбцы colb и cole из только что приведенного запроса. Если СУБД MySQL оптимизирует запросы с помошью объединения, число потенциальных комбинаций строк столбцов будет уменьшаться из-за Офаничения числа полных сканирований таблицы.

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

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

Используйте короткие индексы. При индексировании строкового столбца постарайтесь индексировать только часть данных, если в этом есть какой-то смысл. Например, если столбец имеет длину CHAR (280), нет необходимости индексировать по всей длине столбца, так как в больщинстве случаев для обеспечения уникальности ключа достаточно будет первых 10-20 символов. Индексирование по префиксу из первых 10-20 символов сэкономит пространство в индексе и, вероятно, также ускорит выполнение запросов. Обработка небольшого индекса требует меньше операций ввода/вывода на диск, меньшие значения сравниваются быстрее. Важно также то, что при меньших объемах ключей в кэш-памяти будет храниться больше ключевых значений, таким образом, СУБД MySQL сможет хранить больше значений одновременно. Это увеличивает вероятность того, что СУБД MySQL сможет осуществить выборку, не подчитывая дополнительные блоки индекса с диска. (При этом не следует терять чувство здравого смысла: индексирование только по первому символу вряд ли сможет вам помочь.)

Как можно более эффективно используйте левый крайний префикс .

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

Предположим, что у нас есть таблица, индексированная по столбцам state, city И zip. Строки в индексе отсортированы в порядке state/city/zip. Это значит, что они также будут автоматически отсортированы в порядке state/city И порядке state. Это означает, что СУБД MySQL получит преимущество, если в запросе будут определены только значения state или только значения state и city. Таким образом, индекс Можно использовать для выборки по следующей комбинации столбцов.

state, city, zip state, city state

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

Не надо путать это с индексированием по первым п символам строкового столбца.

Глава 4. Оптимизация запросов 233

указанному штату и почтовому индексу (столбцы 1 и 3 индекса). Однако поиск данных по определенному штату будет работать очень быстро.

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

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

Тип операции сравнения, который производится со столбцом. Индексы применяются при выполнении операций <, <=, = , >=, > и BETWEEN. Индексы также используются в операциях LIKE, когда образец имеет литеральный префикс. Если же значения столбца используются в других операциях, таких как STRCMP (), их индексировать нет смысла.

Оптимизатор MySQL

После запуска запроса интерпретатор MySQL анализирует запрос на предмет его оптимизации. Оптимизация может привести к ускорению обработки запроса Рассмотрим основные принципы работы оптимизатора. Дополнительная информация по этой теме изложена в справочном руководстве по СУБД MySQL В ней даются разные оценки производительности. Эта информация постоянно дополняется потому, что разработчики СУБД MySQL постоянно дорабатывают оптимизатор, и я рекомендую делать периодический анализ новых возможностей оптимизатора. (Постоянно модифицируемое руководство можно найти по адресу http: www.mysql. com/.)

Оптимизатор запросов СУБД MySQL позволяет максимально эффективно использовать индексы, но при этом в своей работе он использует и другую информацию. Например, такой запрос будет выполнен очень быстро, независимо от размера таблицы:

SELECT * FROM tbl name WHERE 1=0