Дополнительно о базах данных. Получение информации с помощью операторов SHOW, DESCRIBE, EXPLAIN

До сих пор мы использовали операторы 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

Вариация Описание
SHOW DATABASES
[LIKE database]
Выводит список доступных баз данных, с необязательным отображением их имен, которые подобны database.
SHOW [OPEN] TABLES
[FROM database]
[LIKE table]
Выводит список таблиц испоьзуемой в текущий момент либо указанной базы данных database, с необязательным отображением имен таблиц, которые подобны table.
SHOW COLUMNS FROM table
[FROM database]
[LIKE column]
Выводит список всех столбцов конкретной таблицы используемой в текущий момент либо указанной базы данных, с необязательным отображением имен столбцов, которые подобны column. Вместо оператора SHOW COLUMNS можно использовать оператор SHOW FIELDS.
SHOW INDEX FROM table
[FROM database]
Отображает детальную информацию по всем индексам конкретной таблицы используемой базы данных или детали индексов базы данных, если она указана. Также можно использовать SHOW KEYS.
SHOW STATUS
[LIKE status_item]
Предоставляет информацию о количестве системных элементов, такую как число выполняющихся потоков. Выражение LIKE используется для сопоставления имен этих элементов. Так, например, 'Thread%' соответствует элементам 'Threads_connected', 'Threads_cached' и 'Threds_running'.
SHOW [GLOBAL|SESSION]
[LIKE variable_name]
Отображает имена и значения системных переменных MySQL наподобие номера версии. Выражение LIKE можно использовать для их сопоставления аналогично тому, как это делается в операторе SHOW STATUS.
SHOW [FULL] PROCESSLIST Отображает все выполняющиеся в системе процессы, то есть выполняющиеся в данный момент запросы. Большинство пользователей будут видеть информацию о своих собственных потоках, но приналичии у них полномочий PROCESS они могут видеть информацию о процессах любых пользователей — в том числе, присутствующие в запросах пароли. По умолчанию запросы должны усекаться до 100 символов. Применение необязательного ключевого слова FULL ведет к отображению полных запросов.
SHOW TABLE STATUS
[FROM database]
[LIKE database]
Отображает информацию о каждой таблице в используемой либо в указанной базе данных database. Допускается применение групповых символов. Информация включает в себя сведения о типе таблицы и времени последнего обновления каждой таблицы.
SHOW GRANTS FOR user Отображает операторы GRANT, необходимые для предоставления указанному пользователю user его текущего уровня полномочий.
SHOW PRIVILEGES Отображает различные подтверждаемые сервером полномочия.
SHOW CREATE DATABASE db Отображает оператор CREATE DATABASE, который создал бы указанную базу данных.
SHOW CREATE
TABLE tablename
Отображает оператор CREATE TABLE, который создал бы указанную таблицу.
SHOW [STORAGE] ENGINES Отображает механизмы хранения, доступные в данный момент системе, с указанием механизма, используемого по умолчанию.
SHOW INNODB STATUS Отображает сведения о текущем состоянии механизма хранения InnoDB.
SHOW [BDB] LOGS Выводит информацию о журнальных файлах механизма хранения BDB.
SHOW WARNINGS [LIMIT
[offset,] row_count]
Отображает любые сообщения об ошибках, предупреждения или уведомления, сгенерированные последним выполненным оператором.
SHOW ERRORS [LIMIT
[offset,] row_count]
Отображает только сообщения об ошибках, сгенерированные последним выполненным оператором.

Эта команда выводит информацию обо всех столбцах таблицы 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, а не точкой с запятой.

Вывод запроса EXPLAIN

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

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

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

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

Тип Описание
SIMPLE Обычный старый тип запроса SELECT, как в рассматриваемом примере
PRIMARY Внешний (первый) запрос, в котором используются подзапросы и соединения.
UNION Второй или последний запрос в соединении.
DEPENDENT UNION Второй или последний запрос в соединении, зависящий от первичного запроса.
SUBQUERY Внутренний подзапрос.
DEPENDENT SUBQUERY Внутренний подзапрос, зависящий от первичного запроса (то есть связанный подзапрос).
DERIVED Подзапрос, используемый в выражении FROM.

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

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

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

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

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

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

Тип Описание
const или system Таблица считывается только однажды. Это имеет место, если таблица содержить только одну строку. Тип system используется, если данная таблица является системной, а тип const — dj всех других случаях.
eq_ref Для каждого набора строк из других таблиц, участвующих в соединении, выполняется считывание одной строки из данной таблицы. Этот тип применяется, когда для соединения используются все части индекса таблицы, и индекс имеет тип UNIQUE (уникальный ключ) или является первичным ключом.
ref Для каждого набора строк из других таблиц, участвующих в соединении, выполняется считывание набора строк таблицы, соответствующих критерию отбора. Этот тип применяется, когда условие соединения не позволяет выбрать одну строку — то есть, когда в соединении используется только часть ключа, либо он не является ключом типа UNIQUE или первичным ключом.
ref_or_null Этот запрос подобен запросу ref, но для его выполнения MySQL ищет также строки NULL (Этот тип в основном используется в подзапросах)
index_merge Свидетельствует об использовании специфической оптимизации Index Merge (Слияние индексов).
unique_subquery Этот тип соединения используется вместо соединения ref в некоторых подзапросоах IN, возвращающих одну уникальную строку.
index_subquery Этот тип соединения аналогичен соединению unique_subquery, но используется для индексирования неуникальных подзапросов.
range Для каждого набора строк из других таблиц, которые участвуют в соединении, выполняется считывание набора строк таблицы, относящихся к конкретному диапазону.
index Осуществляется сканирование всего индекса.
ALL Осуществляется сканирование всех строк таблицы.

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

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

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

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

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

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

Значение Описание
Distinct После нахождения первой совпадающей строки MySQL прекращает дальнейшие попытки поиска строк.
Not exits Запрос оптимизирован для выполнения соединения LEFT JOIN.
Range checked for each record Для каждой строки в наборе строк из других таблиц MySQL пытается найти наилучший для использования индекс, если таковые существуют.
Using filesort Для сортировки данных требуются два прохода. (Понятно , что выполнение этой операции требует в два раза больше времени)
Using index Вся информация о таблице поступает из индексов, то есть в действительности просмотр строк не выполняется.
Using temporary Для выполнения данного запроса требуется создание временной таблицы.
Using where Для выбора строк используется выражение WHERE.

Существует несколько возможных способов решения проблем, обнаруженных на основе анализа результатов выполнения 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 (столбец);




  • Другие |
назадвверхвперед
Rambler's Top100