Информация о базах данных MySQL

Во время работы с базами данных возникает необходимость получить разнообразную информацию (размер, количество записей и пр.) о базах данных или определенных таблицах. Как получить интересующую информацию, читайте далее.

1. Теория

Вся информация о базах данных в MySQL хранится в базе information_schema, если Вы знаете в какой таблице искать, сможете найти необходимые данные.

Основные таблицы, которые Вам могут быть интересны:

COLUMNS - информация о столбцах всех таблиц баз данных. Доступна информация о имени столбца, кодировке, типе данных, правах доступа.

PROCESSLIST - таблица с информацией о текущих процессах базы данных, из этой таблицы получаем информацию о текущих запросах.

• USER - пользователь, который выполняет команду/запрос
• HOST - адрес хоста, с которого произведено подключение
• DB - имя базы данных, с которой происходит работа
• COMMAND - тип команды. Sleep - простой подключения, Query - выполняется запрос
• INFO - SQL-код команды, если он присутствует

SCHEMATA - таблица с перечнем баз данных

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

• TABLE_SCHEMA - база данных
• TABLE_NAME - имя таблицы
• TABLE_ROWS - количество записей
• DATA_LENGTH - размер данных в байтах
• INDEX_LENGTH - размер данных индекса в байтах
• AUTO_INCREMENT - "позиция автоинкримента"
• CREATE_TIME - дата создания таблицы
• TABLE_COLLATION - кодировка таблицы

2. От теории к практике

Далее я приведу примеры запросов, с помощью которых Вы сможете получить необходимую информацию о базах данных

2.1 Объем, занимаемый базами данных

SELECT

table_schema "database name",

sum( data_length + index_length )/1024/1024 "Объем БД в Мб",

sum( data_length )/1024/1024 "Размер данных БД в Мб",

sum( index_length )/1024/1024 "Объем данных индексов в Мб"

FROM information_schema.TABLES

GROUP BY table_schema;

Пример результата

database name Объем БД в Мб Размер данных БД в Мб Объем данных индексов в Мб

information_schema 0.15625000 0.15625000 0.00000000

mysql 2.44259167 2.22872448 0.21386719

performance_schema 0.00000000 0.00000000 0.00000000

sys 0.01562500 0.01562500 0.00000000

syslog 362.60937500 355.95312500 6.65625000

2.2. Получаем информацию о размерах таблиц базы данных и средних затратах на 1 запись

SELECT

table_name "Таблица",

(data_length + index_length )/1024/1024 "Объем Таблицы в Мб",

data_length/1024/1024 "Размер данных таблицы в Мб",

index_length /1024/1024 "Объем данных индексов таблицы в Мб",

table_rows "Количество записей",

(data_length + index_length )/1024/table_rows "Средние затраты на одну запись в Кб"

FROM information_schema.TABLES

WHERE TABLE_SCHEMA="ИМЯ_БД"

Пример результата

Таблица Объем Таблицы в Мб Размер данных таблицы в Мб Объем данных индексов таблицы в Мб Количество записей Средние затраты на одну запись в Кб

messages 0.15625000 0.09375000 0.06250000 108 1.48148148

messages_syslog_20171108 0.09375000 0.01562500 0.07812500 2 48.00000000

syslog 362.35937500 355.84375000 6.51562500 602968 0.61538257

2.3. Информация о текущих подключениях к БД

SELECT `USER`, `HOST`, `COMMAND`, `DB`, `STATE`,`INFO` FROM `PROCESSLIST`

Пример результата

USER HOST COMMAND DB STATE INFO

syslog localhost Sleep syslog NULL

root localhost Sleep NULL NULL

root localhost Query information_schema executing SELECT`USER`,`HOST`,`COMMAND`,`DB`,`STATE`,`I...

Данных запросов будет достаточно для получения минимальной информации о состоянии баз данных.

Если Вас интересует как получить определенную информацию о базах данных или таблицах MySQL, задавайте вопросы в комментариях.