Создайте тестовую базу данных и таблицу 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_SYS_TABLES, чтобы найти метаданные для test/t1:

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

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

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

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

В дополнение к TABLE_ID и столбцу NAME, INNODB_SYS_COLUMNS предоставляет порядковый номер (POS) каждого столбца (начиная с 0 и увеличиваясь последовательно), столбец MTYPE или “основной тип” (6 = INT, 2 = CHAR, 1 = VARCHAR), PRTYPE или “точный тип” (двоичное значение с битами, которые представляют тип данных MySQL, код набора символов и возможность наличия NULL), и длину столбца (LEN).

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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_SYS_INDEXES возвращает данные для двух индексов. Первый индекс — GEN_CLUST_INDEX, который является кластеризованным индексом, созданным InnoDB, если у таблицы нет определенного пользователем кластеризованного индекса. Второй индекс (i1) — это определенный пользователем вторичный индекс.

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

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

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

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

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 57 \G
*************************** 1. row ***************************
        SPACE: 57
         NAME: test/t1
         FLAG: 0
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact or Redundant
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0

В дополнение к SPACE идентификатору табличного пространства и NAME связанной таблицы, INNODB_SYS_TABLESPACES предоставляет данные табличного пространства FLAG, которые представляют собой информацию на уровне битов о формате и характеристиках хранения табличного пространства. Также представлены данные табличного пространства FILE_FORMAT, ROW_FORMAT, PAGE_SIZE и несколько других элементов метаданных табличного пространства.

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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_SYS_TABLESTATS. Данные в этой таблице используются оптимизатором MySQL для расчета, какой индекс использовать при запросе таблицы InnoDB. Эта информация получена из структур данных в оперативной памяти. Нет соответствующей внутренней таблицы системы InnoDB.

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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_SYS_FOREIGN и найдите данные внешнего ключа для отношения внешнего ключа между test/child и test/parent:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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_SYS_FOREIGN для получения дополнительной информации о значениях TYPE.

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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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 — порядковый номер поля ключа в индексе внешнего ключа, начиная с нуля.