• 请不要在回答技术问题时复制粘贴 AI 生成的内容
crafter
V2EX  ›  程序员

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

  •  
  •   crafter · Dec 13, 2019 · 3116 views
    This topic created in 2536 days ago, the information mentioned may be changed or developed.

    比如这个表

    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 replies  •  2019-12-14 09:28:22 +08:00
    lihongjie0209
    lihongjie0209
    Dec 13, 2019
    1
    select age , height , weight from user

    不过我估计的说的'索引值'不是指这个吧
    crafter
    crafter
    OP
    Dec 13, 2019
    2
    @lihongjie0209 这是具体表中每条数据
    taogen
    taogen
    Dec 13, 2019 via Android
    3
    InnoDB 引擎默认是 B+ Tree index,索引值应该是按索引定义中字段的顺序连接而成的字符串,插入 /删除 /查找都是按照最左前缀字符串匹配找到对应的节点。
    b821025551b
    b821025551b
    Dec 13, 2019
    4
    explain 后的 key 字段不就是么?
    wangyzj
    wangyzj
    Dec 13, 2019
    5
    explain 啊
    37Y37
    37Y37
    Dec 14, 2019
    6
    explain 顺便附上 explain 结果说明,带案例的那种
    https://ops-coffee.cn/s/p5UKuh1yY3P4zrOzVBmY1w
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Privacy   ·   Solana   ·   887 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 19:27 · PVG 03:27 · LAX 11:27 · JFK 14:27
    ♥ Do have faith in what you're doing.