如何验证是否为关系创建了内部索引？

lh80um4z 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(214)

从https://stackoverflow.com/a/51181742/3284469
如果表没有主键或合适的唯一索引，innodb会在包含行id值的合成列上内部生成一个名为gen\u clust\u index的隐藏聚集索引。这些行是按innodb分配给这样一个表中的行的id排序的。行id是一个6字节的字段，随着新行的插入而单调增加。因此，按行id排序的行在物理上是按插入顺序排列的。
我的mysql版本是：

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

我按照那里的命令来验证内部索引是否已创建，但最后一个命令没有显示任何索引已创建。为什么？谢谢。
注意，我对上一个命令做了一点修改，因为原来的命令 Unknown table 'INNODB_INDEX_STATS' in information_schema 错误。


# 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

来源：https://stackoverflow.com/questions/51271907/how-can-we-verify-an-internal-index-is-created-for-a-relation

2条答案

按热度按时间

9udxz4iz1#

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

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的Angular 来看，这个索引不是一个“真正的”索引（它不会出现在 DESCRIBE ，并且不能修改或删除），因此它不会显示在中 INFORMATION_SCHEMA.STATISTICS .

赞(0）回复(0）举报 2021-06-21

e5nszbig2#

对于版本8.0.11，表 innodb_index_stats 位于 mysql 模式代替 INFORMATION_SCHEMA . 按照这些命令，最后一个查询给出如下结果：

mysql> select VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.11    |
+-----------+

mysql> select * from mysql.innodb_index_stats where database_name='test' and 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 18:57:45 | n_diff_pfx01 |          5 |           1 | DB_ROW_ID                         |
| test          | check_table | GEN_CLUST_INDEX | 2018-07-10 18:57:45 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | check_table | GEN_CLUST_INDEX | 2018-07-10 18:57:45 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

此外，问题中引用的post还创建了第二个表，其中指定了主键。该查询的索引验证提供：

mysql> create table test.check_table_2 (id int, description varchar(10), PRIMARY KEY(id)) ENGINE = INNODB;

mysql> desc check_table_2;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | NO   | PRI | NULL    |       |
| description | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

mysql> select * from mysql.innodb_index_stats where database_name='test' and table_name = 'check_table_2';
+---------------+---------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name    | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+---------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | check_table_2 | PRIMARY    | 2018-07-10 19:00:39 | n_diff_pfx01 |          0 |           1 | id                                |
| test          | check_table_2 | PRIMARY    | 2018-07-10 19:00:39 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | check_table_2 | PRIMARY    | 2018-07-10 19:00:39 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+---------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

赞(0）回复(0）举报 2021-06-21

我来回答

如何验证是否为关系创建了内部索引？

2条答案

相关问题

热门标签

最新问答