查看MySQL InnoDB 表索引的高度

墨蓝 2022-03-18 01:21 259阅读 0赞

在看[《MySQL技术内幕：InnoDB存储引擎》B+树索引章节][MySQL_InnoDB_B]中看到这么一句话：

> 但是B+索引在数据库中有一个特点就是高扇出性，因此在数据库中，B+树的高度一般都在2-4层，也就是说查找某一键值的行记录时最多只需要2-4次IO。因为当前一般的机械磁盘每秒至少可以做100次IO，2-4次的IO意味着查询时间只需要0.02-0.04秒。

那么，当一个表很大的时候，索引还是是2-4层吗？那么这时搜索子节点会不会很慢？

下面通过user库中的uc\_users表，来验证一下。  
表结构如下：

CREATE TABLE `uc_users` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `username` varchar(40) DEFAULT NULL COMMENT 'username (login principal)',
      `connection_id` bigint(20) DEFAULT NULL COMMENT 'username (login principal)',
      `email` varchar(254) DEFAULT NULL COMMENT 'email (login principal)',
      `email_verified` tinyint(1) NOT NULL DEFAULT '0',
      `phone_number` varchar(20) DEFAULT NULL COMMENT 'mobile phone number (login principal?)',
      `phone_verified` tinyint(1) NOT NULL DEFAULT '0',
      `display_name` varchar(40) DEFAULT NULL COMMENT 'name for displaying',
      `nickname` varchar(40) DEFAULT NULL COMMENT 'nickname',
      `given_name` varchar(40) DEFAULT NULL COMMENT 'given name or first name',
      `family_name` varchar(40) DEFAULT NULL COMMENT 'family name or surname',
      `middle_name` varchar(40) DEFAULT NULL COMMENT 'middle name',
      `avatar_url` varchar(2000) DEFAULT NULL COMMENT 'avatar image url',
      `password` varchar(255) DEFAULT NULL COMMENT 'password hash (login credential)',
      `password_strength` int(11) DEFAULT NULL COMMENT 'password strength',
      `enabled` tinyint(1) NOT NULL DEFAULT '1',
      `locked` tinyint(1) NOT NULL DEFAULT '0',
      `type` smallint(6) NOT NULL COMMENT 'type (for lite-auth)',
      `source` varchar(100) DEFAULT NULL COMMENT 'where user come from',
      `last_login_at` timestamp NULL DEFAULT NULL,
      `gender` varchar(10) DEFAULT NULL,
      `birth_date` varchar(10) DEFAULT NULL,
      `zone_info` varchar(20) DEFAULT NULL,
      `locale` varchar(20) DEFAULT NULL,
      `website` varchar(2000) DEFAULT NULL,
      `address` varchar(1000) DEFAULT NULL,
      `metadata` varchar(5000) DEFAULT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      `external_source` varchar(255) DEFAULT NULL,
      `external_id` varchar(255) DEFAULT NULL,
      `reg_client_id` varchar(128) DEFAULT NULL,
      `version` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_phone_number` (`phone_number`),
      KEY `index_email` (`email`),
      KEY `index_username` (`username`),
      KEY `index_created_at` (`created_at`),
      KEY `index_updated_at` (`updated_at`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2022228456 DEFAULT CHARSET=utf8 COMMENT='user table';

索引信息如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bnphb3NpeWVjYW8_size_16_color_FFFFFF_t_70]  
数据量：5968 8046  
![在这里插入图片描述][20190218204218806.png]  
查看index level 通过[innodb\_ruby][innodb_ruby]查看。  
具体语法参见：[https://github.com/jeremycole/innodb\_ruby/wiki][https_github.com_jeremycole_innodb_ruby_wiki]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bnphb3NpeWVjYW8_size_16_color_FFFFFF_t_70 1]

## 方法一 ##

1、登陆mysql对应机器，找到mysql数据存放位置

ps -ef | grep mysql

![在这里插入图片描述][20190218204516846.png]  
2、找到ibdata文件位置  
![在这里插入图片描述][20190218204816743.png]  
3、执行如下命令

innodb_space -s ibdata1 -T user/uc_users space-indexes

执行结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bnphb3NpeWVjYW8_size_16_color_FFFFFF_t_70 2]

*  id：表示此索引的ID。
 *  name：索引的名称，PRIMARY代表的就是聚集索引，因为InnoDB表是聚集索引组织表，行记录就是聚集索引；idx\_c就是辅助索引的名称。
 *  root：索引中根节点的page号，可以看出聚集索引的Root节点是第3号page（前0、1、2号Page已经被使用），辅助索引的根节点是第4、5、6、7、8个page。
 *  fseg：page的说明，internal表示非叶子节点或属于根节点，leaf表示叶子节点（也就是数据页）。
 *  used：索引使用了多少个page，可以看出聚集索引的非叶子节点使用了1819个page，叶子节点使用了1112265个page。
 *  allocated：索引分配了多少个page，可以看出聚集索引的非叶子节点分配了2522个page，叶子节点分配了1271136个page。
 *  fill\_factor：索引的填充度，used/allocated表示填充度，也就是实际使用的大小百分比。

现在我们知道了Root节点页后，就可以使用innodb\_ruby的另外一个功能，打印页结构信息，需要了解InnoDB页结构。

# 这里查看是索引中根节点的page号为3的索引信息 即主键索引id列
    # 从返回结果中可以看到index_id=>55
    innodb_space -s ibdata1 -T user/uc_users -p 3 page-dump

执行结果如下：

[root@uoc-db2 mysql3306]# innodb_space -s ibdata1 -T user/uc_users -p 3 page-dump
    #<Innodb::Page::Index:0x00000002dc04b8>:
    
    fil header:
    {:checksum=>2973709054,
     :offset=>3,
     :prev=>nil,
     :next=>nil,
     :lsn=>47538913608,
     :type=>:INDEX,
     :flush_lsn=>0,
     :space_id=>31}
    
    fil trailer:
    {:checksum=>2973709054, :lsn_low32=>294273352}
    
    page header:
    {:n_dir_slots=>2,
     :heap_top=>225,
     :garbage_offset=>192,
     :garbage_size=>42,
     :last_insert_offset=>0,
     :direction=>:left,
     :n_direction=>1,
     :n_recs=>3,
     :max_trx_id=>0,
     :level=>3,
     :index_id=>55,
     :n_heap=>7,
     :format=>:compact}
    
    fseg header:
    {:leaf=>
      <Innodb::Inode space=<Innodb::Space file="user/uc_users.ibd", page_size=16384, pages=2044416>, fseg=2>,
     :internal=>
      <Innodb::Inode space=<Innodb::Space file="user/uc_users.ibd", page_size=16384, pages=2044416>, fseg=1>}
    
    sizes:
      header           120
      trailer            8
      directory          4
      free           16189
      used             195
      record            63
      per record     21.00
    
    page directory:
    [99, 112]
    
    system records:
    {:offset=>99,
     :header=>
      {:next=>129,
       :type=>:infimum,
       :heap_number=>0,
       :n_owned=>1,
       :min_rec=>false,
       :deleted=>false,
       :length=>5},
     :next=>129,
     :data=>"infimum\x00",
     :length=>8}
    {:offset=>112,
     :header=>
      {:next=>112,
       :type=>:supremum,
       :heap_number=>1,
       :n_owned=>4,
       :min_rec=>false,
       :deleted=>false,
       :length=>5},
     :next=>112,
     :data=>"supremum",
     :length=>8}
    
    garbage records:
    {:format=>:compact,
     :offset=>192,
     :header=>
      {:next=>171,
       :type=>:node_pointer,
       :heap_number=>5,
       :n_owned=>0,
       :min_rec=>false,
       :deleted=>false,
       :nulls=>[],
       :lengths=>{},
       :externs=>[],
       :length=>5},
     :next=>171,
     :type=>:clustered,
     :key=>[{:name=>"id", :type=>"BIGINT", :value=>2002372990}],
     :row=>[],
     :sys=>[],
     :child_page_number=>1206485,
     :length=>12}
    
    {:format=>:compact,
     :offset=>171,
     :header=>
      {:next=>171,
       :type=>:node_pointer,
       :heap_number=>4,
       :n_owned=>0,
       :min_rec=>false,
       :deleted=>false,
       :nulls=>[],
       :lengths=>{},
       :externs=>[],
       :length=>5},
     :next=>171,
     :type=>:clustered,
     :key=>[{:name=>"id", :type=>"BIGINT", :value=>55861094}],
     :row=>[],
     :sys=>[],
     :child_page_number=>948104,
     :length=>12}
    
    
    records:
    {:format=>:compact,
     :offset=>129,
     :header=>
      {:next=>150,
       :type=>:node_pointer,
       :heap_number=>2,
       :n_owned=>0,
       :min_rec=>true,
       :deleted=>false,
       :nulls=>[],
       :lengths=>{},
       :externs=>[],
       :length=>5},
     :next=>150,
     :type=>:clustered,
     :key=>[{:name=>"id", :type=>"BIGINT", :value=>1}],
     :row=>[],
     :sys=>[],
     :child_page_number=>375704,
     :length=>12}
    
    {:format=>:compact,
     :offset=>150,
     :header=>
      {:next=>213,
       :type=>:node_pointer,
       :heap_number=>3,
       :n_owned=>0,
       :min_rec=>false,
       :deleted=>false,
       :nulls=>[],
       :lengths=>{},
       :externs=>[],
       :length=>5},
     :next=>213,
     :type=>:clustered,
     :key=>[{:name=>"id", :type=>"BIGINT", :value=>47377252}],
     :row=>[],
     :sys=>[],
     :child_page_number=>948104,
     :length=>12}
    
    {:format=>:compact,
     :offset=>213,
     :header=>
      {:next=>112,
       :type=>:node_pointer,
       :heap_number=>6,
       :n_owned=>0,
       :min_rec=>false,
       :deleted=>false,
       :nulls=>[],
       :lengths=>{},
       :externs=>[],
       :length=>5},
     :next=>112,
     :type=>:clustered,
     :key=>[{:name=>"id", :type=>"BIGINT", :value=>68265101}],
     :row=>[],
     :sys=>[],
     :child_page_number=>1644800,
     :length=>12}

页结构信息中有一个level字段，表示的就是Root节点页的高度，同样，level + 1就等于这个索引的高度。

比如再看看phone\_number索引列（page号为4），结果如下：  
[https://github.com/jiankunking/backups/blob/master/mysql/index/phone\_number.page.contents][https_github.com_jiankunking_backups_blob_master_mysql_index_phone_number.page.contents]

## 方法二 ##

1、查看innodb\_page\_size

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

执行结果如下：  
![在这里插入图片描述][20190218205708393.png]  
2、通过hexdump这样的工具就可以快速定位到所需要的树高度信息

hexdump -s 49216 -n 02 uc_users.ibd

执行结果如下：  
![在这里插入图片描述][20190218210823573.png]

查看uc\_users表，49216表示的是3*16384+64（这里innodb\_page\_size设置为了16384，如果是8192就是3*8192），即第3个页偏移量64位置开始读取2个字节，这里PAGE\_LEVEL为00 03，那么索引的高度就为4。

本文参考：[《查看InnoDB表每个索引的高度？》][InnoDB]

个人微信公众号：  
![这里写图片描述][SouthEast]

> 作者：jiankunking 出处：[http://blog.csdn.net/jiankunking][http_blog.csdn.net_jiankunking]

[MySQL_InnoDB_B]: https://blog.csdn.net/jiankunking/article/details/83044312
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bnphb3NpeWVjYW8_size_16_color_FFFFFF_t_70]: /images/20220318/dca0f02577154b83a355d84f1e1f3b43.png
[20190218204218806.png]: /images/20220318/d1a17644e8a14665951900d2d2ae3e1c.png
[innodb_ruby]: https://github.com/jiankunking/innodb_ruby
[https_github.com_jeremycole_innodb_ruby_wiki]: https://github.com/jeremycole/innodb_ruby/wiki
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bnphb3NpeWVjYW8_size_16_color_FFFFFF_t_70 1]: /images/20220318/1b999f7693b241f287ad5cf78e2cc5d9.png
[20190218204516846.png]: /images/20220318/36557baec2c6410ea90d4830d24bc4d1.png
[20190218204816743.png]: /images/20220318/cb46de2be39f4ae497ed400f490c2401.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bnphb3NpeWVjYW8_size_16_color_FFFFFF_t_70 2]: /images/20220318/2766ba1764df48f09d0d782586e2f176.png
[https_github.com_jiankunking_backups_blob_master_mysql_index_phone_number.page.contents]: https://github.com/jiankunking/backups/blob/master/mysql/index/phone_number.page.contents
[20190218205708393.png]: /images/20220318/92ab11950d1444559c0aa3ef6f7dc6d4.png
[20190218210823573.png]: /images/20220318/94ca9beaf1c64f73b626053227334cb0.png
[InnoDB]: http://www.ywnds.com/?p=14670
[SouthEast]: /images/20220318/7e7ee0097b2945a78921fcfe6f0dc229.png
[http_blog.csdn.net_jiankunking]: http://blog.csdn.net/jiankunking