Исследуем структуру базы данных MySQL с помощью функций SHOW
Любая база данных это сложный набор всевозможных компонентов: таблиц, индексов, представлений, функций, процедур и других элементов. Для эффективной работы, и разработчику и тестировщику работающим с чужой базой данных важно иметь возможность исследовать все необходимые компоненты.
В базе данных MySQL для этой цели существует целое семейство функций SHOW.
Давайте ознакомимся с некоторыми из них и поймем, как они могут помочь QA специалистам и программистам.
1. SHOW DATABASES - "Покажи мне все свои хранилища"
Первым делом, когда вы открываете MySQL, вы можете воспользоваться SHOW DATABASES, чтобы увидеть все доступные базы данных. Это как список папок на компьютере. Пример ниже (и все следующие) с сайта sqlize.online
mysql> SHOW DATABASES; |--------------------| | Database | |--------------------| | information_schema | | sakila |
Как вы видите эта команда выводит список всех доступных вам баз данных. Если у вашего пользователя нет доступа к какой-то из баз то вы не увидите её в списке. Это нужно учесть при вашем исследовании.
2. SHOW TABLES - "Покажи, что у тебя есть"
Когда вы хотите узнать, какие таблицы существуют в определенной базе данных, просто используйте SHOW TABLES. Это похоже на просмотр содержимого папки на компьютере. Для того что бы получить список таблиц в нужной нам базе перед выполнением команды SHOW нужно выполнить USE dbname;
mysql> USE sakila; -- в данном случае мы будем исследовать базу данных 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(10) unsigned | NO | PRI | [null] | auto_increment | | city | varchar(50) | NO | | [null] | | | country_id | int(10) unsigned | NO | MUL | [null] | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
Данная команда возвращает более подробный результат. Вы видите не только наименования колонок но и их тип, возможность сохранения пустых значений (NULL) является ли поле ключевым, его значение по умолчанию и дополнительную информацию о колонке.
Данное исследование может дать нам ответ на вопрос: Почему я не могу добавить запись не указав название, или почему в поле 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
Очень удобная штука! Таким образом вы видите всю структуру в компактном виде. Это удобно для сравнения структуры таблиц например на тестовом сервере и на боевом. Так же эта команда не заменив если кто-то просит вас показать 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 которые могут сильно облегчить понимание устройства базы данных разработчиками и тестировщиками. Не стесняйтесь заглядывать в этот раздел команд если вам нужно вытащить какую либо информацию о имеющейся у вас базе.