разработка веб-сайтов под ключ
обучающие программы для веб-разработчиков

До сих пор мы использовали операторы SHOW и DESCRIBE для получения списков таблиц в базе данных и столбцов в этих таблицах. В последующих разделах мы кратко рассмотрим другие способы применения этих операторов, а также оператора EXPLAIN для получения дополнительной информации о способе выполнения SELECT.

Получение информации с помощью оператора SHOW

Ранее мы использовали следующую конструкцию:

show tables;

для получения списка таблиц базы данных.

Оператор

show databases;

отображает список доступных баз данных. Затем с помощью оператора SHOW NABLES можно просмотреть список таблиц в одной из этих баз данных:

show tables from books;

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

Если имена столбцов известны, можно получить список столбцов:

show columns from orders from books;

Если имя базы данных опустить, оператор SHOW COLUMNS выведет список изпользуемой в текущей момент базы данных. Можно использовать также форму записи таблица. столбец:

show columns from books.orders;

Еще одну полезную вариацию оператора SHOW можно применять для выяснения полномочий, выданных пользователю. Например, оператор

show grants for svd ;

Приведенные в этом примере операторы GRANT не являются обязательными для предоставления полномочий конкрктному пользователю, а представляют собой скорее эквивалентные обобщенные операторы, которые создают текущий уровень полномочий пользователя.

Получение информации о столбцах с помощью оператора DESCRIBE

Вместо оператора SHOW COLUMNS можно использовать оператор DESCRIBE. Основной синтаксис этого оператора выглядит следующим образом:

DESCRIBE table [column];

Таблица. Синтаксис оператора SHOW

Эта команда выводит информацию обо всех столбцах таблицы table или о конкретном столбце, если указан параметр column. При желании в column можно использовать групповые символы.

Получение информации о способе выполнения запросов с помощью оператора EXPLAIN

Оператор EXPLAIN можно применять двумя способами. Во-первых, можно использовать команду:

EXPLAIN table;

Результат выполнения этой команды аналогичен результату выполнения оператора DESCRIBE table или SHOW COLUMNS FROM table.

Второй и более интересный способ применения оператора EXPLAIN позволяет выяснить, как именно MySQL вычисляет запрос SELECT. Чтобы использовать EXPLAIN именно так, достаточно поместить слово EXPLAIN перед оператором SELECT.

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

Например, попробуйте выполнить следующий запрос в базе данных интернет-магазина:

explain
select customers.name
from customers, orders, order_items, books
where customers.customerid = orders.customerid
and orders.orderid = order_items.orderid
and order_items.isbn = books.isbn
and books.title like '%Разработка%';

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

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

Первый столбец, id, отображает идентификационный номер оператора SELECT внутри запроса, на который ссылается данная строка.

Столбец select_type содержит информацию о типе использованногго запроса. Допустимый набор значений этого столбца приведен в таблице.

Таблица. Возможные типы запроса SELECT, отображаемые в результате выполнения оператора EXPLAIN

Столбец table содержит список всех таблиц, которые использовались для формирования ответа на запрос. Каждая строка результата представляет дополнительную информацию о способе использования конкретной таблицы в этом запросе. В данном случае мы видим, что в запросе использовались таблицы customers, orders, order_items, books.

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

В предыдущем примере две таблицы соединены с использованием eq_ref (orders и customers), одна — посредством index (order_items) и еще одна (books) —с помощью ALL —то есть за счет просмотра каждой отдельной строки таблицы.

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

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

Таблица. Возможные типы соединения, отображаемые в результате выполнения запроса EXPLAIN

Столбец possible_keys, как можно догадаться по его названию (возможные ключи), содержит имена ключей, которые MySQL может использовать для соединения таблиц. Несложно заметить, что в данном случае возможными ключами являются все типы ключей типа PRIMARY.

Столбец key содержит либо ключ таблицы, действительно использованный MySQL, либо значение NULL, если ключи вообще не использовались. Обратите внимание, что несмотря на существование допустимого первичного ключа для таблицы books, он не был задействован в данном запросе.

Столбец key_len отображает длину используемого ключа. Это число служит для определения того, использовалась ли эта часть ключа. Упомянутая информация важна при наличии ключей, которые состоят из более чем одного столбца. В данном случае все использованные ключи были полными.

Столбец ref содержит информацию о столбцах, которые использовались с ключом для выбора строк из таблицы.

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

Таблица. Возможные значения столбца Extra в результате выполнения запроса EXPLAIN

Существует несколько возможных способов решения проблем, обнаруженных на основе анализа результатов выполнения EXPLAIN. Во-первых, можно проверить типы столбцов и убедиться в том, что они одинаковы. В частности, это относится к ширине столбцов. Индексы нельзя использовать для сопоставления столбцов, если те имеют различную ширину. Эту проблему можно решить, изменяя типы столбцов, или закладывая это в архитектуру таблиц с самого начала.

Во-вторых, можно указать оптимизатору соединения о необходимости проверять распределение ключей и тем самым эффективнее оптимизировать соединения с помщью улиты myisamchk или оператора ANALYZE TABLE, которые эквивалентны. Эту улиту можно вызвать с помощью следующей команды:

myisamchk --analyze путь_к_базе_данных_MySQL/таблица

Несколько таблиц можно проверить, перечисляя их в командной строке, или используя команду:

myisamchk --analyze путь_к_базе_данных_MySQL/ *.MYI

Для проверки всех таблиц во всех базах данных можно воспользоваться такой командой:

myisamchk --analyze путь_к_базе_данных_MySQL/ */ *.MYI

Альтернативный способ выполнения этой задачи состоит в перечислении таблиц в операторе ANALYZE TABLE в среде монитора MySQL:

analyze table customers, orders, order_items, books;

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

Ускорение выполнения запросов за счет использования индексов

Если столбец possible_keys в результатах выполнения оператора EXPLAIN содержит ряд значений NULL скорость выполнения запроса, возможно, удастся повысить, добавив индекс в соответствующую таблицу. Если столбец, используемый в выражении WHERE, пригоден для индексации, новый индекс для него можно создать с помощью оператора ALTER TABLE:

ALTER TABLE таблица ADD INDEX (столбец);