Незаметно для пользователей программа MySQL оптимизирует предложения WHERE инструкции SELECT. Обычно не нужно заботиться о том, сколько скобок указано в выражении или каков порядок таблиц в объединении. Вместо этого сосредоточьтесь на индексах. Они позволяют ускорить операции выборки данных за счет замедления операций записи. Конечно, индексы занимают дополнительное место на диске, но они незаменимы с точки зрения эффективной организации таблиц.
Когда программа MySQL извлекает данные из таблицы, ей достаточно просмотреть один индексный столбец, чтобы найти нужные записи и не сканировать всю таблицу. Если к объединенной таблице применимы два индекса, программа выбирает из них тот который позволит прочесть меньшее число записей.
Разрешается создавать индекс, охватывающий несколько столбцов. Программа MySQL может работать с частями индекса, но они должны просматриваться строго слева направо. Например, если индекс включает столбцы имени и фамилии, то при обращении к первому столбцу индекс будет использован, а ко второму — нет (при условии, что перед этим не было обращения к первому столбцу). Это правило применимо и к символам индексируемого столбца, содержащего текстовые данные (тип CHAR, VARCHAR или BLOB). Когда в предложении WHERE присутствует оператор LIKE, индекс задействуется лишь в том случае, если шаблон сравнения содержит все литеральные символы слева, а метасимволы — справа. Так, шаблон ‘abc %’ разрешает использование индекса, а шаблон ‘abc % xyz’ — нет.
В листинге 10.2 приведены инструкции, создающие две таблицы. Таблица word будет содержать 14346 записей, а таблица dictionary — 104237. В первую таблицу слова заносятся пользователями, а вторая таблица содержит список известных программе слов. Пользователи часто вводят несуществующие слова. Запрос, анализируемый в листинге 10.3, предназначен для выяснения количества распознанных слов. Условию отбора соответствуют 911 записей.
mysql> EXPLAIN SELECT word.word, dictionary.word-> FROM word LEFT JOIN dictionary-> ON word.word=dictionary.word-> WHERE word.class = '_VERBO' \G*************************** 1.row ***************************table: wordtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 14346Extra: where usedtable: dictionarytype: ALLpossible_keys: NULLkey: NULLkey_len: NULL
Листинг 10.3. (html, txt)
В запросе участвуют три столбца: столбцы word и class таблицы word и столбец word таблицы dictionary. Известно, что тестовому условию отбора соответствуют 911 записей таблицы word, поэтому наша задача состоит в том, чтобы сократить диапазон сканирования первой таблицы до соответствующего уровня. Для этого необходимо создать индекс по столбцу class. Сначала я планировал включить в индекс только упомянутый столбец, но потом подумал о других запросах, которые приходится направлять таким таблицам. Я, например, часто создаю отчет, в который включается все содержимое таблицы, отсортированное сначала по классам, а затем — по словам. Разумнее будет включить в индекс сразу два столбца (листинг 10.4).
ALTER TABLE wordADD INDEX (class, word)
Листинг 10.4. (html, txt)
Теперь инструкция EXPLAIN выдает другие результаты (листинг 10.5). В поле Key len сообщается о том, что индекс охватывает 16 символов столбца class. По оценке программы MySQL, ей придется просмотреть 1517 записей, хотя мы знаем, что их всего 911
Mysql > EXPLAIN SELECT word. word, dictionary. Word-> FROM word LEFT JOIN dictionary-> ON word. Word = dictionary. Word-> WHERE word. Class = "_VERBO" \G table: word type: refpossible keys: class key: class key len: 16 ref: const rows: 1517 Extra: where used; Using index table: dictionary type: ALLpossible keys: NULL key: NULL key len: NULL ref: NULL rows: 104237 Extra:2 rows in set (0.00 sec)
Листинг 10.5. (html, txt)
Итак, появление индекса привело к сокращению диапазона сканирования в 15 раз, но инструкция все же вынуждена просматривать 45 миллионов записей. Осталось еще учесть столбцы word в обеих таблицах. Разберемся сначала с таблицей word. В процессе объединения таблиц программа MySQL использует не более одного индекса от каждой таблицы. Если появляются варианты, то выбирается индекс с более узким диапазоном. Созданный нами индекс уже охватывает столбец word, к тому же, как видно из листинга 10.5, диапазон поиска существенно сузился. Теперь перейдем к таблице dictionary. Пока что инструкция SELECT вынуждена сканировать ее целиком. Добавление индекса к столбцу word позволит программе сразу же находить нужную запись (листинг 10.6).
ALTER TABLE dictionaryADD INDEX (word)
Листинг 10.6. (html, txt)
Эффект этого действия продемонстрирован в листинге 10.7. Как видите, количество просматриваемых записей таблицы dictionary сократилось до одной!
Mysql > EXPLAIN SELECT word. word, dictionary. Word-> FROM word LEFT JOIN dictionary-> ON word. Word = dictionary. Word-> WHERE word. Class = "_VERBO" \G*********************** 1. row ******************** table: word type: refpossible keys: class key: class key len: 16 ref: const rows: 1517 Extra: where used; Using index table: dictionary type: refpossible keys: word key: word key len: 64 ref: word. word rows: 1 Extra: Using index2 rows in set (0.26 sec)
