这是一个创建于 1801 天前的主题，其中的信息可能已经有所发展或是发生改变。
比如这个表
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>
 |
1
lihongjie0209 2019-12-13 17:01:57 +08:00
select age , height , weight from user
不过我估计的说的'索引值'不是指这个吧 |
 |
2
crafter OP @lihongjie0209 这是具体表中每条数据
|
 |
3
taogen 2019-12-13 17:10:26 +08:00 via Android
InnoDB 引擎默认是 B+ Tree index，索引值应该是按索引定义中字段的顺序连接而成的字符串，插入 /删除 /查找都是按照最左前缀字符串匹配找到对应的节点。
|
 |
4
b821025551b 2019-12-13 17:29:03 +08:00
explain 后的 key 字段不就是么？
|
 |
5
wangyzj 2019-12-13 18:52:37 +08:00
explain 啊
|
 |
6
37Y37 2019-12-14 09:28:22 +08:00
explain 顺便附上 explain 结果说明，带案例的那种
https://ops-coffee.cn/s/p5UKuh1yY3P4zrOzVBmY1w |
Select Language