Создайте тестовую базу данных и таблицу 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, код набора символов и возможность наличия NULL), и длину столбца (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 в каталоге data MySQL. Если табличное пространство было создано в расположении вне каталога данных 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 — порядковый номер поля ключа в индексе внешнего ключа, начиная с нуля.