V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
crafter
V2EX  ›  程序员

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

  •  
  •   crafter · 2019-12-13 16:59:08 +08:00 · 1804 次点击
    这是一个创建于 1567 天前的主题,其中的信息可能已经有所发展或是发生改变。

    比如这个表

    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> 
    
    6 条回复    2019-12-14 09:28:22 +08:00
    lihongjie0209
        1
    lihongjie0209  
       2019-12-13 17:01:57 +08:00
    select age , height , weight from user

    不过我估计的说的'索引值'不是指这个吧
    crafter
        2
    crafter  
    OP
       2019-12-13 17:03:06 +08:00
    @lihongjie0209 这是具体表中每条数据
    taogen
        3
    taogen  
       2019-12-13 17:10:26 +08:00 via Android
    InnoDB 引擎默认是 B+ Tree index,索引值应该是按索引定义中字段的顺序连接而成的字符串,插入 /删除 /查找都是按照最左前缀字符串匹配找到对应的节点。
    b821025551b
        4
    b821025551b  
       2019-12-13 17:29:03 +08:00
    explain 后的 key 字段不就是么?
    wangyzj
        5
    wangyzj  
       2019-12-13 18:52:37 +08:00
    explain 啊
    37Y37
        6
    37Y37  
       2019-12-14 09:28:22 +08:00
    explain 顺便附上 explain 结果说明,带案例的那种
    https://ops-coffee.cn/s/p5UKuh1yY3P4zrOzVBmY1w
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5364 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 07:30 · PVG 15:30 · LAX 00:30 · JFK 03:30
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.