Исследуем структуру базы данных MySQL с помощью функций SHOW

Отредактировано

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

2К открытий14К показов

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

1. SHOW DATABASES - "Покажи мне все свои хранилища"

Первым делом, когда вы открываете MySQL, вы можете воспользоваться SHOW DATABASES, чтобы увидеть все доступные базы данных. Это как список папок на компьютере.

Пример ниже (и все следующие) с сайта sqlize.online

			mysql> SHOW DATABASES; 

|--------------------|
| Database           |
|--------------------|
| information_schema |
| performance_schema |
| sakila             |

Как вы видите эта команда выводит список всех доступных вам баз данных. Если у вашего пользователя нет доступа к какой-то из баз то вы не увидите её в списке. Это нужно учесть при вашем исследовании.

2. SHOW TABLES - "Покажи, что у тебя есть"

Когда вы хотите узнать, какие таблицы существуют в определенной базе данных, просто используйте SHOW TABLES. Это похоже на просмотр содержимого папки на компьютере. Для того что бы получить список таблиц в нужной нам базе перед выполнением команды SHOW нужно выполнить USE dbname;

			mysql> USE sakila;
mysql> SHOW TABLES;

|----------------------------|
| Tables_in_sakila           |
|----------------------------|
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |

Таким образом мы получили список таблиц.

3. SHOW COLUMNS - "Что у тебя внутри?"

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

			mysql> SHOW COLUMNS FROM city;|-------------|--------------|------|-----|-------------------|-----------------------------------------------|
| Field       | Type         | Null | Key | Default           | Extra                                         |
|-------------|--------------|------|-----|-------------------|-----------------------------------------------|
| city_id     | int unsigned | NO   | PRI | [null]            | auto_increment                                |
| city        | varchar(50)  | NO   |     | [null]            |                                               |
| country_id  | int unsigned | NO   | MUL | [null]            |                                               |
| last_update | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |

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

Хочу стать наставником для разработчиков: как правильно передавать знания

tproger.ru

Данное исследование может дать нам ответ на вопрос: Почему я не могу добавить запись не указав название, или почему в поле last_update появилось значение которое я не указывал.

4. SHOW INDEX - "Покажи мне твои секретные индексы"

С SHOW INDEX вы можете посмотреть, какие индексы установлены в таблице. Это поможет вам понять, какие столбцы можно использовать для быстрого доступа к данным.

			mysql> SHOW INDEX FROM city;

|-------|------------|-------------------|--------------|-------------|-----------|-------------|----------|--------|------|------------|---------|---------------|
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|-------|------------|-------------------|--------------|-------------|-----------|-------------|----------|--------|------|------------|---------|---------------|
| city  | 0          | PRIMARY           | 1            | city_id     | A         | 600         | [null]   | [null] |      | BTREE      |         |               |
| city  | 1          | idx_fk_country_id | 1            | country_id  | A         | 109         | [null]   | [null] |      | BTREE      |         |               |

Здесь мы видим что таблица имеет первичный ключ по полю city_id а также не уникальный индекс по полю country_id. Этой информации нам будет достаточно чтобы понять что выборка из таблицы городов по идентификатору страны должна быть очень быстрой.

5. SHOW CREATE TABLE - "Покажи, как создать это"

Хотите узнать, как создать определенную таблицу? Просто используйте SHOW CREATE TABLE, и MySQL покажет вам SQL-запрос для создания таблицы.

			mysql> SHOW CREATE TABLE city;

CREATE TABLE `city` (                                                                                            
 `city_id` int(10) unsigned NOT NULL AUTO_INCREMENT,                                                            
 `city` varchar(50) NOT NULL,                                                                                   
 `country_id` int(10) unsigned NOT NULL,                                                                        
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,                        
 PRIMARY KEY (`city_id`),                                                                                       
 KEY `idx_fk_country_id` (`country_id`),                                                                        
 CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE  
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8

			SHOW CREATE DATABASE / EVENT/FUNCTION / PROCEDURE / TRIGGER / USER / VIEW

Очень удобная штука! Таким образом вы видите всю структуру в компактном виде. Это удобно для сравнения структуры таблиц например на тестовом сервере и на боевом. Так же эта команда не заменив если кто-то просит вас показать DDL таблиц. Одна команда и всё готово. Кстати если вам нужно поделиться с коллегой вы можете воспользоваться сайтом sqlize.online - наберите или скопируйте свой SQL код, нажмите кнопку RUN чтобы код был выполнен системой, а затем кнопку SHARE, чтобы получить в буфер обмена короткую ссылку на ваш код. После этого любой, кто получит ссылку, сможет продолжить работать с вашим кодом!

В дополнение к SHOW CREATE TABLE вы можете использовать

			SHOW CREATE DATABASE / EVENT/FUNCTION / PROCEDURE / TRIGGER / USER / VIEW

то есть получить код создания любого объекта в базе данных

6. SHOW TABLE STATUS - "Сколько у тебя строк?"

Наконец, SHOW TABLE STATUS позволяет вам получить различную информацию о таблице, такую как количество строк или размер таблицы. Это полезно для оценки производительности и размера таблиц.

			mysql> SHOW TABLE STATUS LIKE 'city';

|------|--------|---------|------------|------|----------------|-------------|-----------------|--------------|-----------|----------------|---------------------|-------------|------------|--------------------|----------|----------------|---------|
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
|------|--------|---------|------------|------|----------------|-------------|-----------------|--------------|-----------|----------------|---------------------|-------------|------------|--------------------|----------|----------------|---------|
| city | InnoDB | 10      | Dynamic    | 600  | 109            | 65536       | 0               | 16384        | 0         | 601            | 2024-03-15 09:57:44 | [null]      | [null]     | utf8mb3_general_ci | [null]   |                |         |

Из полученной таблицы мы можем извлечь массу полезной информации: когда была создана таблица, когда была изменена, сколько в ней строк и каково значение автоинкремента о котором я писал здесь. В общем ещё один ценный инструмент в руках исследователя!

7. SHOW GRANTS - "Кто имеет доступ?"

С помощью SHOW GRANTS можно узнать привилегии, предоставленные пользователям в MySQL. Это полезно для проверки безопасности и доступа к данным.

			mysql> SHOW GRANTS FOR 'user'@'host';

Если у вас достаточно привилегий в базе, то при помощи этой команды можно узнать, кто ещё может подключаться к базе и какие действия выполнять. Это очень полезная команда для сисадминов и пен-тестеров.

8. SHOW MASTER / REPLICA STATUS - "Эй! Кто отстаёт?"

Эта команда необходима в случае репликации для того чтобы понять всё ли с ней в порядке и если нет - насколько всё плохо.

8. SHOW [GLOBAL] VARIABLES - "Покажи настройки"

Данная команда позволяет увидеть параметры конфигурации базы данных. Если вам нужно выбрать только нужные из большого списка - отфильтруйте их при помощи LIKE как в примере ниже:

			mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool%';

|-------------------------------------|----------------|
| Variable_name                       | Value          |
|-------------------------------------|----------------|
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |

Эти значения могут помочь вам в оптимизации работы сервера. Далее без лишних подробностей перечислю ещё несколько полезных команд которыми мне приходилось пользоваться:

SHOW ERRORS - показывает список ошибок.
SHOW ENGINE STATUS - статус движка базы данных - позволяет увидеть DEAD LOCKS

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

2К открытий14К показов

Также рекомендуем

ChatGPT или YandexGPT: у какой нейросети больше возможностей?

ChatGPT или YandexGPT - наиболее известные и востребованные чат-боты в России. Какой из них выбрать? В этом материале разберем конкретные возможности обеих ИИ для задач маркетинга.

В чём разница между Data Scientist, Data Analyst и Data Engineer

Англоязычный блогер Daniel Dan сравнил должностные роли в мире данных: Data Scientist, Data Analyst и Data Engineer.

Почему не стоит учить Python

Роман Сакутин развенчивает мифы о Python, утверждая, что язык не универсален, не подходит для всех областей IT и не является лучшим для новичков.

Проверь свои знания PostgreSQL (для джунов)

Этот тест поможет проверить ваши знания SQL на уровне Junior. Вопросы касаются основ работы с базами данных и создания оптимизированных запросов.