我们如何验证为关系创建了内部索引? [英] How can we verify an internal index is created for a relation?

问题描述

来自https://stackoverflow.com/a/51181742/3284469

如果表没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB在内部生成一个名为 GEN_CLUST_INDEX 的隐藏聚集索引包含行 ID 值的合成列.行按以下顺序排序InnoDB 分配给此类表中行的 ID.行 ID 是一个 6 字节的字段，随着插入新行而单调增加.因此，按行 ID 排序的行在物理上处于插入状态订购.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

我的mysql版本是:

My mysql version is:

$ mysql --version
mysql  Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)

我按照那里的命令验证内部索引是否已创建，但最后一个命令未显示已创建任何索引.这是为什么?谢谢.

I followed the commands there to verify the internal index is created, but the last command doesn't show any index has been created. Why is that? Thanks.

请注意，我稍微更改了最后一个命令，因为原始命令给了我Unknown table 'INNODB_INDEX_STATS' in information_schema 错误.

Note that I changed the last command a little bit, because the original command gives me Unknown table 'INNODB_INDEX_STATS' in information_schema error.

# Create the table
create table test.check_table (id int, description varchar(10)) ENGINE = INNODB;

# Verify that there is no primary or unique column
desc test.check_table;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| description | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+


# Insert some values
insert into test.check_table values(1, 'value-1');
insert into test.check_table values(2, 'value-2');
insert into test.check_table values(null, 'value-3');
insert into test.check_table values(4, null);
insert into test.check_table values(1, 'value-1');


# Verify table
select * from test.check_table;
+------+-------------+
| id   | description |
+------+-------------+
|    1 | value-1     |
|    2 | value-2     |
| NULL | value-3     |
|    4 | NULL        |
|    1 | value-1     |
+------+-------------+


# Verify that the GEN_CLUST_INDEX index is auto-created.
select * from INFORMATION_SCHEMA.INNODB_INDEX_STATS where TABLE_SCHEMA='test' and TABLE_NAME = 'check_table';

ERROR 1109 (42S02): Unknown table 'INNODB_INDEX_STATS' in information_schema


SELECT DISTINCT     TABLE_NAME, COLUMN_NAME ,    INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME='check_table';
Empty set (0.00 sec)

推荐答案

在我能找到的所有 MySQL 版本中，INNODB_INDEX_STATS 表位于 mysql 数据库，而不是 INFORMATION_SCHEMA.这似乎是您引用的帖子中的错误.

In all versions of MySQL that I've been able to find, the INNODB_INDEX_STATS table is located in the mysql database, not INFORMATION_SCHEMA. This appears to be an error in the post you're referencing.

mysql> select * from mysql.innodb_index_stats where table_name = 'check_table';
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name  | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | check_table | GEN_CLUST_INDEX | 2018-07-10 11:34:01 | n_diff_pfx01 |          5 |           1 | DB_ROW_ID                         |
| test          | check_table | GEN_CLUST_INDEX | 2018-07-10 11:34:01 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | check_table | GEN_CLUST_INDEX | 2018-07-10 11:34:01 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

这个索引从 SQL 的角度来看不是真正的"索引(它不会出现在 DESCRIBE 的输出中，并且不能被修改或删除)，所以它不是'未显示在 INFORMATION_SCHEMA.STATISTICS 中.

This index isn't a "real" index from the perspective of SQL (it doesn't appear in the output of DESCRIBE, and can't be modified or dropped), so it isn't shown in INFORMATION_SCHEMA.STATISTICS.

这篇关于我们如何验证为关系创建了内部索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！