Создайте тестовую базу данных и таблицу t1:

mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE t1 (
       col1 INT,
       col2 CHAR(10),
       col3 VARCHAR(10))
       ENGINE = InnoDB;

mysql> CREATE INDEX i1 ON t1(col1);

После создания таблицы t1, запросите таблицу INNODB_TABLES, чтобы найти метаданные для test/t1:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: test/t1
         FLAG: 1
       N_COLS: 6
        SPACE: 57
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
 INSTANT_COLS: 0

Таблица t1 имеет размер (TABLE_ID) в 71. Поле FLAG содержит информацию на уровне битов о формате и характеристиках хранения таблицы. Существует шесть столбцов, три из которых являются скрытыми столбцами, созданными InnoDB (DB_ROW_ID, DB_TRX_ID и DB_ROLL_PTR). Идентификатор табличного SPACE составляет 57 (значение 0 указывает, что таблица находится в системном табличном пространстве). Формат ROW_FORMAT — Compact. ZIP_PAGE_SIZE применяется только к таблицам с форматом строк Compressed. INSTANT_COLS показывает количество столбцов в таблице до добавления первого мгновенного столбца с помощью ALTER TABLE ... ADD COLUMN с ALGORITHM=INSTANT.

Используя информацию из таблицы INNODB_TABLES, запросите таблицу INNODB_COLUMNS для получения информации о столбцах таблицы.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS where TABLE_ID = 71\G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: col1
          POS: 0
        MTYPE: 6
       PRTYPE: 1027
          LEN: 4
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 2. row ***************************
     TABLE_ID: 71
         NAME: col2
          POS: 1
        MTYPE: 2
       PRTYPE: 524542
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 3. row ***************************
     TABLE_ID: 71
         NAME: col3
          POS: 2
        MTYPE: 1
       PRTYPE: 524303
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL

В дополнение к TABLE_ID и столбцу NAME, таблица INNODB_COLUMNS предоставляет порядковый номер (POS) каждого столбца (начиная с 0 и увеличиваясь последовательно), тип столбца или “основной тип” (6 = INT, 2 = CHAR, 1 = VARCHAR), PRTYPE или “точный тип” (бинарное значение с битами, представляющими тип данных MySQL, код набора символов и возможность нулевых значений) и длину столбца (LEN). Столбцы HAS_DEFAULT и DEFAULT_VALUE применяются только к столбцам, добавленным мгновенно с помощью ALTER TABLE ... ADD COLUMN с ALGORITHM=INSTANT.

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE TABLE_ID = 71 \G
*************************** 1. row ***************************
       INDEX_ID: 111
           NAME: GEN_CLUST_INDEX
       TABLE_ID: 71
           TYPE: 1
       N_FIELDS: 0
        PAGE_NO: 3
          SPACE: 57
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       INDEX_ID: 112
           NAME: i1
       TABLE_ID: 71
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 57
MERGE_THRESHOLD: 50

INNODB_INDEXES возвращает данные для двух индексов. Первый индекс — GEN_CLUST_INDEX, кластеризованный индекс, созданный InnoDB, если у таблицы нет пользовательского кластеризованного индекса. Второй индекс (i1) — это пользовательский вторичный индекс.

INDEX_ID — это идентификатор индекса, уникальный во всех базах данных в экземпляре. TABLE_ID определяет таблицу, к которой относится индекс. Значение индекса TYPE указывает тип индекса (1 = Кластеризованный индекс, 0 = Вторичный индекс). Значение N_FILEDS — количество полей, составляющих индекс. PAGE_NO — номер корневой страницы индексного B-дерева, а SPACE — идентификатор табличного пространства, где находится индекс. ненулевое значение указывает, что индекс не находится в системном табличном пространстве. MERGE_THRESHOLD определяет процентный пороговый уровень данных на странице индекса. Если количество данных на странице индекса опускается ниже этого значения (по умолчанию 50%), когда удаляется строка или строка укорачивается операцией обновления, InnoDB пытается объединить страницу индекса с соседней страницей индекса.

Используя информацию из INNODB_INDEXES, запросите таблицу INNODB_FIELDS для получения информации о полях индекса i1.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FIELDS where INDEX_ID = 112 \G
*************************** 1. row ***************************
INDEX_ID: 112
    NAME: col1
     POS: 0

INNODB_FIELDS предоставляет NAME индексированного поля и его порядковый номер в индексе. Если индекс (i1) был определен для нескольких полей, INNODB_FIELDS предоставит метаданные для каждого из индексированных полей.

Используя информацию из таблицы INNODB_TABLES, запросите таблицу INNODB_TABLESPACES для получения информации о табличном пространстве таблицы.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE = 57 \G
*************************** 1. row ***************************
          SPACE: 57
          NAME: test/t1
          FLAG: 16417
    ROW_FORMAT: Dynamic
     PAGE_SIZE: 16384
 ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Single
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 114688
ALLOCATED_SIZE: 98304
AUTOEXTEND_SIZE: 0
SERVER_VERSION: 8.4.0
 SPACE_VERSION: 1
    ENCRYPTION: N
         STATE: normal

Помимо идентификатора (SPACE) и размера (NAME) связанной таблицы, таблица INNODB_TABLESPACES предоставляет данные о табличном пространстве (FLAG), информацию о формате (ROW_FORMAT) и другие метаданные табличного пространства (PAGE_SIZE).

Используя информацию из таблицы INNODB_TABLES, запросите таблицу INNODB_DATAFILES для получения расположения файла данных табличного пространства.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57 \G
*************************** 1. row ***************************
SPACE: 57
 PATH: ./test/t1.ibd

Файл данных расположен в каталоге test внутри каталога данных MySQL (data). Если табличное пространство создано в расположении вне каталога данных MySQL с помощью ключевого слова DATA DIRECTORY в операторе CREATE TABLE, путь к табличному пространству (PATH) будет полным путем к каталогу.

В качестве заключительного шага вставьте строку в таблицу t1 (TABLE_ID = 71) и просмотрите данные в таблице INNODB_TABLESTATS. Данные в этой таблице используются оптимизатором MySQL для вычисления индекса, который следует использовать при запросе к таблице InnoDB. Эта информация получена из данных в оперативной памяти.

mysql> INSERT INTO t1 VALUES(5, 'abc', 'def');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71 \G
*************************** 1. row ***************************
         TABLE_ID: 71
             NAME: test/t1
STATS_INITIALIZED: Initialized
         NUM_ROWS: 1
 CLUST_INDEX_SIZE: 1
 OTHER_INDEX_SIZE: 0
 MODIFIED_COUNTER: 1
          AUTOINC: 0
        REF_COUNT: 1

Поле STATS_INITIALIZED указывает, были ли собраны статистические данные для таблицы. NUM_ROWS — это текущее оценочное количество строк в таблице. Поля CLUST_INDEX_SIZE и OTHER_INDEX_SIZE сообщают о количестве страниц на диске, хранящих кластеризованные и вторичные индексы для таблицы соответственно. Значение MODIFIED_COUNTER показывает количество строк, измененных операциями DML и каскадными операциями от внешних ключей. Значение AUTOINC — это следующее число, которое будет выдано для любой операции, основанной на автоинкременте. В таблице t1 нет столбцов с автоинкрементом, поэтому значение равно 0. Значение REF_COUNT — это счётчик. Когда счётчик достигает 0, это означает, что метаданные таблицы могут быть удалены из кэша таблицы.

Создайте тестовую базу данных с родительской и дочерней таблицами:

mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE parent (id INT NOT NULL,
       PRIMARY KEY (id)) ENGINE=INNODB;

mysql> CREATE TABLE child (id INT, parent_id INT,
    ->     INDEX par_ind (parent_id),
    ->     CONSTRAINT fk1
    ->     FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->     ON DELETE CASCADE) ENGINE=INNODB;

После создания родительской и дочерней таблиц выполните запрос к INNODB_FOREIGN и найдите данные внешнего ключа для отношения внешнего ключа test/child и test/parent:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

Метаданные включают внешний ключ ID (fk1), который назван в честь CONSTRAINT, который был определён в дочерней таблице. FOR_NAME — имя дочерней таблицы, где определён внешний ключ. REF_NAME — имя родительской таблицы (таблицы “ссылка”). N_COLS — количество столбцов в индексе внешнего ключа. TYPE — числовое значение, представляющее флаги битов, которые предоставляют дополнительную информацию о столбце внешнего ключа. В данном случае значение TYPE равно 1, что указывает на то, что опция ON DELETE CASCADE была указана для внешнего ключа. Обратитесь к определению таблицы INNODB_FOREIGN для получения дополнительной информации о значениях TYPE.

Используя внешний ключ ID, выполните запрос к INNODB_FOREIGN_COLS, чтобы просмотреть данные о столбцах внешнего ключа.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE ID = 'test/fk1' \G
*************************** 1. row ***************************
          ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0

FOR_COL_NAME — имя столбца внешнего ключа в дочерней таблице, а REF_COL_NAME — имя столбца ссылки в родительской таблице. Значение POS — порядковый номер поля ключа в индексе внешнего ключа, начиная с нуля.