如果查看 mysql 中一条数据对应的索引值?

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1806 天前的主题，其中的信息可能已经有所发展或是发生改变。

比如这个表

create table user(
    id int primary key,
    age int,
    height int,
    weight int,
    name varchar(32)
)engine = innoDb;

创建一个联合索引:

create index idx_obj on user(age asc,height asc,weight asc)

我想看一下这个表中每一条数据对应的具体的索引值是多少? 怎么搞?

我试过用命令

mysql> SELECT * FROM mysql.innodb_index_stats a WHERE a.database_name = 'learn_sql' and a.table_name like '%user%';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| learn_sql     | user       | PRIMARY    | 2019-12-13 10:18:16 | n_diff_pfx01 |          5 |           1 | id                                |
| learn_sql     | user       | PRIMARY    | 2019-12-13 10:18:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| learn_sql     | user       | PRIMARY    | 2019-12-13 10:18:16 | size         |          1 |        NULL | Number of pages in the index      |
| learn_sql     | user       | idx_obj    | 2019-12-13 15:47:28 | n_diff_pfx01 |          4 |           1 | age                               |
| learn_sql     | user       | idx_obj    | 2019-12-13 15:47:28 | n_diff_pfx02 |          5 |           1 | age,height                        |
| learn_sql     | user       | idx_obj    | 2019-12-13 15:47:28 | n_diff_pfx03 |          5 |           1 | age,height,weight                 |
| learn_sql     | user       | idx_obj    | 2019-12-13 15:47:28 | n_diff_pfx04 |          5 |           1 | age,height,weight,id              |
| learn_sql     | user       | idx_obj    | 2019-12-13 15:47:28 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| learn_sql     | user       | idx_obj    | 2019-12-13 15:47:28 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
9 rows in set (0.00 sec)

mysql>

int

asc

索引

weight

6 条回复 • 2019-12-14 09:28:22 +08:00