MYSQL 8.0.30源码分析INNODB的PAGE_NO跟物理文件关系_innodb page no

目录

1、画架构图背景

2、INNODB物理用户表空间整体架构图

3、INNODB 物理INDEX PAGE整体架构图

4、 INNODB 物理系统表空间整体架构图

5、问题：PAGE_NO跟物理数据文件是什么关系

6、8.0.30源码分析PAGE_NO跟物理数据文件关系

6.1 构造数据

6.2 debug package的尝试：失败

6.3 源码调式

6.4  额外收获

7、参考：

1、画架构图背景

       最近开始学习MYSQL的INNODB文件系统整体架构，书本上的文章都是一段一段地讲解，架构图比较零散，很容易看到后面就忘了前面的知识点。于是决定努力用一张图画出整体架构，方便自己以后忘记时快速查看。

2、INNODB物理用户表空间整体架构图

3、INNODB 物理INDEX PAGE整体架构图

4、 INNODB 物理系统表空间整体架构图

该架构图跟INNODB物理用户表空间整体架构图很相似

5、问题：PAGE_NO跟物理数据文件是什么关系

       翻完《mysql是怎样运行的》整本书、查看了很多网上的文章，始终理不清MYSQL是怎么根据space_id跟page_no找到对应的物理数据页。

        查阅官方内部参考文章《MySQL :: MySQL Internals Manual :: 22.2.1.1 Fil Header》

还是一头雾水。

        猜测：难道page_no*数据页大小就是文件里偏移量？

        决定动手调式源码解疑。

6、8.0.30源码分析PAGE_NO跟物理数据文件关系

6.1 构造数据

drop table if exists ldc;
create table ldc(id int primary key,ye varchar(12) default 'hello world');
 
insert into ldc(id) values (1),(2),(3),(4);
insert into ldc(id) select id+4 from ldc;
insert into ldc(id) select id+8 from ldc;
insert into ldc(id) select id+16 from ldc;
insert into ldc(id) select id+32 from ldc;
insert into ldc(id) select id+64 from ldc;
insert into ldc(id) select id+128 from ldc;
insert into ldc(id) select id+256 from ldc;
insert into ldc(id) select id+512 from ldc;
insert into ldc(id) select id+1024 from ldc;
insert into ldc(id) select id+2048 from ldc;
insert into ldc(id) select id+4096 from ldc;
insert into ldc(id) select id+8192 from ldc;
insert into ldc(id) select id+16384 from ldc;
insert into ldc(id) select id+32768 from ldc;
insert into ldc(id) select id+34464 from ldc where id>=31073;

然后重启mysql，确保下次select记录时，需要从DISK读取。

6.2 debug package的尝试：失败

希望通过trace文件获取到IO的信息，发现根本没有打印IO相关的栈调用日志。失败。

set debug = 'd,info:n:N:F:i:L:o,/tmp/mysqld.trace';
select * from ldc where id=100000;

6.3 源码调式

1、重启数据库后，直接使用execute函数下断点

然后执行

select * from ldc where id=100000;

2、经过根据函数名的一番猜测和调试，终于定位到PAGE_NO跟物理数据文件关系。可以看出：page_no*数据页大小=文件里偏移量

storage/innobase/fil/fil0fil.cc:7824
  auto offset = (os_offset_t)page_no * page_size.physical();

整个栈的调用关系：

Fil_shard::do_io(Fil_shard * const this, const IORequest & type, bool sync, const page_id_t & page_id, const page_size_t & page_size, ulint byte_offset, ulint len, void * buf, void * message) (\data\mysql-8.0.30\storage\innobase\fil\fil0fil.cc:7826)
fil_io(const IORequest & type, bool sync, const page_id_t & page_id, const page_size_t & page_size, ulint byte_offset, ulint len, void * buf, void * message) (\data\mysql-8.0.30\storage\innobase\fil\fil0fil.cc:7981)
buf_read_page_low(dberr_t * err, bool sync, ulint type, ulint mode, const page_id_t & page_id, const page_size_t & page_size, bool unzip) (\data\mysql-8.0.30\storage\innobase\buf\buf0rea.cc:125)
buf_read_page(const page_id_t & page_id, const page_size_t & page_size) (\data\mysql-8.0.30\storage\innobase\buf\buf0rea.cc:290)
Buf_fetch<Buf_fetch_normal>::read_page(Buf_fetch<Buf_fetch_normal> * const this) (\data\mysql-8.0.30\storage\innobase\buf\buf0buf.cc:3889)
Buf_fetch_normal::get(Buf_fetch_normal * const this, buf_block_t *& block) (\data\mysql-8.0.30\storage\innobase\buf\buf0buf.cc:3512)
Buf_fetch<Buf_fetch_normal>::single_page(Buf_fetch<Buf_fetch_normal> * const this) (\data\mysql-8.0.30\storage\innobase\buf\buf0buf.cc:4084)
buf_page_get_gen(const page_id_t & page_id, const page_size_t & page_size, ulint rw_latch, buf_block_t * guess, Page_fetch mode, ut::Location location, mtr_t * mtr, bool dirty_with_no_latch) (\data\mysql-8.0.30\storage\innobase\buf\buf0buf.cc:4279)
btr_cur_search_to_nth_level(dict_index_t * index, ulint level, const dtuple_t * tuple, page_cur_mode_t mode, ulint latch_mode, btr_cur_t * cursor, ulint has_search_latch, const char * file, ulint line, mtr_t * mtr) (\data\mysql-8.0.30\storage\innobase\btr\btr0cur.cc:961)
btr_pcur_t::open_no_init(btr_pcur_t * const this, dict_index_t * index, const dtuple_t * tuple, page_cur_mode_t mode, ulint latch_mode, ulint has_search_latch, mtr_t * mtr, ut::Location location) (\data\mysql-8.0.30\storage\innobase\include\btr0pcur.h:616)
row_search_mvcc(byte * buf, page_cur_mode_t mode, row_prebuilt_t * prebuilt, ulint match_mode, const ulint direction) (\data\mysql-8.0.30\storage\innobase\row\row0sel.cc:4842)
ha_innobase::index_read(ha_innobase * const this, uchar * buf, const uchar * key_ptr, uint key_len, ha_rkey_function find_flag) (\data\mysql-8.0.30\storage\innobase\handler\ha_innodb.cc:10269)
handler::index_read_map(handler * const this, uchar * buf, const uchar * key, key_part_map keypart_map, ha_rkey_function find_flag) (\data\mysql-8.0.30\sql\handler.h:5282)
handler::ha_index_read_map(handler * const this, uchar * buf, const uchar * key, key_part_map keypart_map, ha_rkey_function find_flag) (\data\mysql-8.0.30\sql\handler.cc:3152)
read_const(TABLE * table, TABLE_REF * ref) (\data\mysql-8.0.30\sql\sql_executor.cc:3583)
join_read_const_table(JOIN_TAB * tab, POSITION * pos) (\data\mysql-8.0.30\sql\sql_executor.cc:3486)
JOIN::extract_func_dependent_tables(JOIN * const this) (\data\mysql-8.0.30\sql\sql_optimizer.cc:5706)
JOIN::make_join_plan(JOIN * const this) (\data\mysql-8.0.30\sql\sql_optimizer.cc:5226)
JOIN::optimize(JOIN * const this, bool finalize_access_paths) (\data\mysql-8.0.30\sql\sql_optimizer.cc:625)
Query_block::optimize(Query_block * const this, THD * thd, bool finalize_access_paths) (\data\mysql-8.0.30\sql\sql_select.cc:1818)
Query_expression::optimize(Query_expression * const this, THD * thd, TABLE * materialize_destination, bool create_iterators, bool finalize_access_paths) (\data\mysql-8.0.30\sql\sql_union.cc:701)
Sql_cmd_dml::execute_inner(Sql_cmd_dml * const this, THD * thd) (\data\mysql-8.0.30\sql\sql_select.cc:771)
Sql_cmd_dml::execute(Sql_cmd_dml * const this, THD * thd) (\data\mysql-8.0.30\sql\sql_select.cc:586)
mysql_execute_command(THD * thd, bool first_level) (\data\mysql-8.0.30\sql\sql_parse.cc:4604)
dispatch_sql_command(THD * thd, Parser_state * parser_state) (\data\mysql-8.0.30\sql\sql_parse.cc:5239)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (\data\mysql-8.0.30\sql\sql_parse.cc:1959)
do_command(THD * thd) (\data\mysql-8.0.30\sql\sql_parse.cc:1362)
handle_connection(void * arg) (\data\mysql-8.0.30\sql\conn_handler\connection_handler_per_thread.cc:302)
pfs_spawn_thread(void * arg) (\data\mysql-8.0.30\storage\perfschema\pfs.cc:2942)
libpthread.so.0!start_thread (Unknown Source:0)
libc.so.6!clone (Unknown Source:0)

6.4  额外收获

发现 判断数据页是否在buffer pool中的入口

storage/innobase/buf/buf0buf.cc:3480
dberr_t Buf_fetch_normal::get(buf_block_t *&block) noexcept {
  /* Keep this path as simple as possible. */
  for (;;) {
    /* Lookup the page in the page hash. If it doesn't exist in the
    buffer pool then try and read it in from disk. */
 
    ut_ad(
        !rw_lock_own(buf_page_hash_lock_get(m_buf_pool, m_page_id), RW_LOCK_S));
 
    block = lookup();
 
    if (block != nullptr) {
      if (block->page.was_stale()) {
        if (!buf_page_free_stale(m_buf_pool, &block->page, m_hash_lock)) {
          /* The page is during IO and can't be released. We wait some to not go
           into loop that would consume CPU. This is not something that will be
           hit frequently. */
          std::this_thread::sleep_for(std::chrono::microseconds(100));
        }
        /* The hash lock was released, we should try again lookup for the page
         until it's gone - it should disappear eventually when the IO ends. */
        continue;
      }
 
      buf_block_fix(block);
 
      /* Now safe to release page_hash S lock. */
      rw_lock_s_unlock(m_hash_lock);
      break;
    }
 
    /* Page not in buf_pool: needs to be read from file */
    read_page();
  }
 
  return DB_SUCCESS;
}

7、参考：

1、《MySQL是怎样运行的 (豆瓣)》 发现该书基本脱离源码在讲解，不适合研究源码的同学阅读。

2、《MySQL · 引擎特性 · InnoDB 文件系统之文件物理结构》提供核心源码入口，更权威完整。