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

2019-12-13 16:59:08 +08:00
 crafter

比如这个表

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> 
1934 次点击
所在节点    程序员
6 条回复
lihongjie0209
2019-12-13 17:01:57 +08:00
select age , height , weight from user

不过我估计的说的'索引值'不是指这个吧
crafter
2019-12-13 17:03:06 +08:00
@lihongjie0209 这是具体表中每条数据
taogen
2019-12-13 17:10:26 +08:00
InnoDB 引擎默认是 B+ Tree index,索引值应该是按索引定义中字段的顺序连接而成的字符串,插入 /删除 /查找都是按照最左前缀字符串匹配找到对应的节点。
b821025551b
2019-12-13 17:29:03 +08:00
explain 后的 key 字段不就是么?
wangyzj
2019-12-13 18:52:37 +08:00
explain 啊
37Y37
2019-12-14 09:28:22 +08:00
explain 顺便附上 explain 结果说明,带案例的那种
https://ops-coffee.cn/s/p5UKuh1yY3P4zrOzVBmY1w

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/628808

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX