MySQL 记录、页、索引的数据结构简析

/** Structure for an SQL data tuple of fields (logical record) */
struct dtuple_t {
 ulint  n_fields; /*!< number of fields in dtuple */
 ulint  n_fields_cmp; /*!< number of fields which should
     be used in comparison services
     of rem0cmp.*; the index search
     is performed by comparing only these
     fields, others are ignored; the
     default value in dtuple creation is
     the same value as n_fields */
 dfield_t* fields;  /*!< fields */
 UT_LIST_NODE_T(dtuple_t) tuple_list;
     /*!< data tuples can be linked into a
     list using this field */
};

/** Structure for an SQL data field */
struct dfield_t{
 void*  data; /*!< pointer to data */
 unsigned ext:1; /*!< TRUE=externally stored, FALSE=local */
 unsigned len; /*!< data length; UNIV_SQL_NULL if SQL null */
 dtype_t  type; /*!< type of data */
};

/* We define the physical record simply as an array of bytes */
typedef byte rec_t;

/** The buffer control block structure */

struct buf_block_t{

 buf_page_t page;  /*!< page information; this must
     be the first field, so that
     buf_pool->page_hash can point
     to buf_page_t or buf_block_t */
 byte*  frame;
 BPageLock lock;  /*!< read-write lock of the buffer frame */

/** The common buffer control block structure
for compressed and uncompressed frames */

class buf_page_t {
public:
  
  page_id_t id; // page id
  buf_page_t* hash;  /*!< node used in chaining to
     buf_pool->page_hash or
     buf_pool->zip_hash */
  lsn_t  newest_modification; // 当前Page最新修改lsn
  lsn_t  oldest_modification; // 当前Page最老修改lsn，即第一条修改lsn
  
};

/** Data structure for an index.  Most fields will be
initialized to 0, NULL or FALSE in dict_mem_index_create(). */
struct dict_index_t{
 index_id_t id; /*!< id of the index */
 mem_heap_t* heap; /*!< memory heap */
 id_name_t name; /*!< index name */
 const char* table_name;/*!< table name */
 dict_table_t* table; /*!< back pointer to table */
  unsigned space:32;
    /*!< space where the index tree is placed */
 unsigned page:32;/*!< index tree root page number */
  unsigned allow_duplicates:1;
      /*!< if true, allow duplicate values
      even if index is created with unique
      constraint */
  unsigned n_uniq:10;/*!< number of fields from the beginning
    which are enough to determine an index
    entry uniquely */
 unsigned n_def:10;/*!< number of fields defined so far */
 unsigned n_fields:10;/*!< number of fields in the index */
 unsigned n_nullable:10;/*!< number of nullable fields */
  dict_field_t* fields; /*!< array of field descriptions */
};

/** The tree cursor: the definition appears here only for the compiler
to know struct size! */
struct btr_cur_t {
 btr_cur_t() { memset(this, 0, sizeof(*this)); }

 dict_index_t* index;  /*!< index where positioned */
 page_cur_t page_cur; /*!< page cursor */
 purge_node_t* purge_node; /*!< purge node, for BTR_DELETE */
 buf_block_t* left_block; /*!< this field is used to store
     a pointer to the left neighbor
     page, in the cases
     BTR_SEARCH_PREV and
     BTR_MODIFY_PREV */
 /*------------------------------*/
 que_thr_t* thr;  /*!< this field is only used
     when btr_cur_search_to_nth_level
     is called for an index entry
     insertion: the calling query
     thread is passed here to be
     used in the insert buffer */
 /*------------------------------*/

};

/** Index page cursor */

struct page_cur_t{
 const dict_index_t* index;
 rec_t*  rec; /*!< pointer to a record on page */
 ulint*  offsets;
 buf_block_t* block; /*!< pointer to the block containing rec */
};

/* The persistent B-tree cursor structure. This is used mainly for SQL
selects, updates, and deletes. */

struct btr_pcur_t{
 btr_pcur_t() { memset(this, 0, sizeof(*this)); }

 /** a B-tree cursor */
 btr_cur_t btr_cur;
  
  rec_t*  old_rec;
   
  /** Return the index of this persistent cursor */
 dict_index_t* index() const { return(btr_cur.index); }
};

// storage/innbase/handler/ha_innodb.cc

/* Stores a row in an InnoDB database, to the table specified in this handle. */
int
ha_innobase::write_row(
/*===================*/
 uchar* record) /*!< in: a row in MySQL format */
{
  /* Step-4: Prepare INSERT graph that will be executed for actual INSERT
 (This is a one time operation) */
 if (m_prebuilt->mysql_template == NULL
     || m_prebuilt->template_type != ROW_MYSQL_WHOLE_ROW) {

  /* Build the template used in converting quickly between
  the two database formats */

  build_template(true);
 }
  
  /* Step-5: Execute insert graph that will result in actual insert. */
 // 插入数据
 error = row_insert_for_mysql((byte*) record, m_prebuilt);
}

typedef unsigned char uchar; /* Short for unsigned char */

/* Note that inside MySQL 'byte' is defined as char on Linux! */
#define byte   unsigned char

/** Save CPU time with prebuilt/cached data structures */
row_prebuilt_t*  m_prebuilt;

/** A struct for (sometimes lazily) prebuilt structures in an Innobase table
handle used within MySQL; these are used to save CPU time. */

struct row_prebuilt_t {
 dict_table_t* table;  /*!< Innobase table handle */
 dict_index_t* index;  /*!< current index for a search, if any */
 trx_t*  trx;  /*!< current transaction handle */
  unsigned n_template:10; /*!< number of elements in the
        template */
  ins_node_t* ins_node; /*!< Innobase SQL insert node
        used to perform inserts
        to the table */
  byte*  ins_upd_rec_buff;/*!< buffer for storing data converted
        to the Innobase format from the MySQL
        format */
  mysql_row_templ_t* mysql_template;/*!< template used to transform
        rows fast between MySQL and Innobase
        formats; memory for this template
        is not allocated from 'heap' */
  btr_pcur_t* pcur;  /*!< persistent cursor used in selects
        and updates */
};

// storage/innobase/row/row0mysql.cc

static
dberr_t
row_insert_for_mysql_using_ins_graph(
 const byte* mysql_rec,   /* row in the MySQL format */
 row_prebuilt_t* prebuilt)  /* prebuilt struct in MySQL handle */
{
  que_thr_t* thr;
  ins_node_t* node  = prebuilt->ins_node;
  
  // 主要构造用于执行插入操作的 2 个对象：
 // 1. ins_node_t 对象，保存在 prebuilt->ins_node 中
 // 2. que_fork_t 对象，保存在 prebuilt->ins_graph 中
 row_get_prebuilt_insert_row(prebuilt);
 node = prebuilt->ins_node;

 // 把 server 层的记录格式转换为 InnoDB 的记录格式
 row_mysql_convert_row_to_innobase(node->row, prebuilt, mysql_rec,
       &blob_heap);
  
  thr->run_node = node;
 thr->prev_node = node;
  
  // 执行插入操作，插入记录到主键索引、二级索引（包含唯一索引、非唯一索引）
 /*插入记录*/
 row_ins_step(thr);
}

/* Insert node structure */

struct ins_node_t{
 dtuple_t* row; /*!< row to insert */
 dict_table_t* table; /*!< table where to insert */
 sel_node_t* select; /*!< select in searched insert */
 que_node_t* values_list;/* list of expressions to evaluate and
    insert in an INS_VALUES insert */
 ulint  state; /*!< node execution state */
 dict_index_t* index; /*!< NULL, or the next index where the index
    entry should be inserted */
 dtuple_t* entry; /*!< NULL, or entry to insert in the index;
    after a successful insert of the entry,
    this should be reset to NULL */
}

static
void
row_mysql_convert_row_to_innobase(
/*==============================*/
 dtuple_t* row,  /*!< in/out: Innobase row where the
     field type information is already
     copied there! */
 row_prebuilt_t* prebuilt, /*!< in: prebuilt struct where template
     must be of type ROW_MYSQL_WHOLE_ROW */
 const byte* mysql_rec, /*!< in: row in the MySQL format; */
 mem_heap_t** blob_heap) /*!< in: FIX_ME, remove this after
     server fixes its issue */
{
 const mysql_row_templ_t*templ;
  
  for (i = 0; i < prebuilt->n_template; i++) {

  templ = prebuilt->mysql_template + i;
    
    // 获取 field
    dfield = dtuple_get_nth_field(row, n_col);
    
    // 格式转换，从 rec 写入 dtuple_t
  row_mysql_store_col_in_innobase_format(
      /* dfield_t* dfield, call to set field */
   dfield,
      /* byte* buf, buffer for a converted integer value */
   prebuilt->ins_upd_rec_buff + templ->mysql_col_offset,
      /* ibool row_format_col, TRUE if the mysql_data is from a MySQL row, FALSE if from a MySQL key value; */
   TRUE, 
      /* byte* mysql_data, MySQL row format data */
   mysql_rec + templ->mysql_col_offset,
      /* ulint col_len, MySQL column length */
   templ->mysql_col_len,        
      /*!< ulint comp, nonzero=compact format */
   dict_table_is_comp(prebuilt->table));
}

const byte* ptr = mysql_data;
 const dtype_t* dtype;
 ulint  type;

 dtype = dfield_get_type(dfield);

 type = dtype->mtype;
  
  // 计算 col_len
  ...
  
  dfield_set_data(dfield, ptr, col_len);

/* Sets pointer to the data and length in a field. */
UNIV_INLINE
void
dfield_set_data(
/*============*/
 dfield_t* field, /*!< in: field */
 const void* data, /*!< in: data */
 ulint  len) /*!< in: length or UNIV_SQL_NULL */
{
 field->data = (void*) data;
 field->ext = 0;
 field->len = static_cast<unsigned int>(len);
}

// 创建 ins_node_t
 node = static_cast<ins_node_t*>(thr->run_node); 

 // 给表加上意向锁
  err = lock_table(0, node->table, LOCK_IX, thr);
  
  /* DO THE CHECKS OF THE CONSISTENCY CONSTRAINTS HERE */
 // 调用 row_ins() 插入记录到主键索引、二级索引
 err = row_ins(node, thr);

// 遍历所有索引，向每个索引中插入记录
 while (node->index != NULL) {
    
    /* 向索引中插入记录 */
    err = row_ins_index_entry_step(node, thr);

  // 插入记录到主键索引或二级索引成功后获取下一个索引
    // node->index、entry 指向表中的下一个索引
  node->index = dict_table_get_next_index(node->index);
  node->entry = UT_LIST_GET_NEXT(tuple_list, node->entry);

// storage/innobase/row/row0insert.cc

/* Inserts a single index entry to the table. */
static MY_ATTRIBUTE((nonnull, warn_unused_result))
dberr_t
row_ins_index_entry_step(
/*=====================*/
 ins_node_t* node, /*!< in: row insert node */
 que_thr_t* thr) /*!< in: query thread */
{
  /*给索引项赋值*/
 err = row_ins_index_entry_set_vals(node->index, node->entry, node->row);

 /*插入索引项*/
 err = row_ins_index_entry(node->index, node->entry, thr);
}

/** Sets the values of the dtuple fields in entry from the values of appropriate columns in row. */
dberr_t
row_ins_index_entry_set_vals(
 const dict_index_t* index,
 dtuple_t*  entry,
 const dtuple_t*  row)
{
 n_fields = dtuple_get_n_fields(entry);

 for (i = 0; i < n_fields + num_v; i++) {
  dfield_t* field;
  const dfield_t* row_field;
    ulint  len;
    
    // 获取 field
    field = dtuple_get_nth_field(entry, i);
    row_field = dtuple_get_nth_field(
    row, ind_field->col->ind);
    len = dfield_get_len(row_field);
    
    // 写入 field
  dfield_set_data(field, dfield_get_data(row_field), len);
}

// storage/innbase/row/row0ins.cc

dberr_t
row_ins_clust_index_entry_low(
/*==========================*/
 ulint  flags, /*!< in: undo logging and locking flags */
 ulint  mode, /*!< in: BTR_MODIFY_LEAF or BTR_MODIFY_TREE,
    depending on whether we wish optimistic or
    pessimistic descent down the index tree */
 dict_index_t* index, /*!< in: clustered index */
 ulint  n_uniq, /*!< in: 0 or index->n_uniq */
 dtuple_t* entry, /*!< in/out: index entry to insert */
 ulint  n_ext, /*!< in: number of externally stored columns */
 que_thr_t* thr, /*!< in: query thread */
 bool  dup_chk_only)
    /*!< in: if true, just do duplicate check
    and return. don't execute actual insert. */
{
  btr_pcur_t pcur;
  btr_cur_t* cursor;
  
  // offsets
  ulint           offsets_[REC_OFFS_NORMAL_SIZE];
 ulint*          offsets         = offsets_;
  
  /* Note that we use PAGE_CUR_LE as the search mode, because then
 the function will return in both low_match and up_match of the
 cursor sensible values */
 // 插入操作的 search_mode 默认是 PAGE_CUR_LE，即插在最后一个小于等于该 dtuple 的 rec_t 后
 // btr_pcur_open 函数内部调用 btr_cur_search_to_nth_level 函数
 btr_pcur_open(index, entry, PAGE_CUR_LE, mode, &pcur, &mtr);
  // 从 btr_pcur_t 中获取 btr_cur_t（tree cursor）
 cursor = btr_pcur_get_btr_cur(&pcur);
 cursor->thr = thr;
  
  rec_t* insert_rec;
  
  /* 乐观插入 */
  err = btr_cur_optimistic_insert(
    flags, cursor, &offsets, &offsets_heap,
    entry, &insert_rec, &big_rec,
    n_ext, thr, &mtr);
}

page_cursor = btr_cur_get_page_cur(cursor);
 
 // 初始的，获得索引的根节点（space_id，page_no）
 const ulint  space = dict_index_get_space(index);
 const page_size_t page_size(dict_table_page_size(index->table));

 /* Start with the root page. */
 // 从 dict_index_t 元信息中拿到 root page 在物理文件的 page no（默认是 4）
 page_id_t  page_id(space, dict_index_get_page(index));
  
  // 从 buffer_pool 中根据 page no 拿 page（buf_page_get_gen），buffer pool 模块会根据 page 是否被缓存来决定是从内存中还是磁盘中读取，并根据加锁策略对 page 加锁
 block = buf_page_get_gen(page_id, page_size, rw_latch, guess,
     buf_mode, file, line, mtr);
 tree_blocks[n_blocks] = block;
  
  /* Search for complete index fields. */
  up_bytes = low_bytes = 0;
  // 对 page 内部的 record list 进行二分搜索 (page_cur_search_with_match)
  // page_cur_search_with_match：在一个数据页内“二分查找”（使用数据页中的 directory slot），定位到 record
  page_cur_search_with_match(
    block, index, tuple, page_mode, &up_match,
    &low_match, page_cursor,
    need_path ? cursor->rtr_info : NULL);

/* Perform binary search until the lower and upper limit directory
 slots come to the distance 1 of each other */
  
  // 在索引页内查找对于指定的 tuple，满足某种条件（依赖于传入的 mode，比如 PAGE_CUR_L）的 record
 // PAGE_CUR_G（>），PAGE_CUR_GE（>=），PAGE_CUR_L（<），PAGE_CUR_LE（<=）
 // 1. 二分查找
 // 在稀疏的 Page Directory 内使用二分查找
 low = 0;
 up = page_dir_get_n_slots(page) - 1;
  
  /* Perform linear search until the upper and lower records come to
 distance 1 of each other. */

 // 二分查找结束后，low / up是临近的两个 slot，这两个 slot 指向的 record 记为
  // low_rec 和 up_rec，满足：low_rec <= tuple <= up_rec，切记 tuple 为待插入的（逻辑）记录

  // 2. 线性查找，渐渐夹逼
  // 在两个相邻的 directory 内，进行线性查找。线性查找的实现即不断"增大 low"，"减小 up"

// storage/innobase/rem/rem0cmp.cc

/** Compare a data tuple to a physical record.
@param[in] dtuple data tuple
@param[in] rec B-tree record
@param[in] offsets rec_get_offsets(rec)
@param[in] n_cmp number of fields to compare
@param[in,out] matched_fields number of completely matched fields
@return the comparison result of dtuple and rec
@retval 0 if dtuple is equal to rec
@retval negative if dtuple is less than rec
@retval positive if dtuple is greater than rec */
int
cmp_dtuple_rec_with_match_low(
 const dtuple_t* dtuple, 
 const rec_t* rec,
 const ulint* offsets,
 ulint  n_cmp,
 ulint*  matched_fields)
{
  /* Match fields in a loop */
  // 遍历记录的每个字段
 for (; cur_field < n_cmp; cur_field++) {
    
    // 获取逻辑记录
    const dfield_t* dtuple_field
   = dtuple_get_nth_field(dtuple, cur_field);
    
    // 将逻辑记录转换为物理记录
    const byte* dtuple_b_ptr
   = static_cast<const byte*>(
    dfield_get_data(dtuple_field));
    
    // 根据 offset 获取物理记录
  rec_b_ptr = rec_get_nth_field(rec, offsets, cur_field,
           &rec_f_len);

  // 比较大小
  ret = cmp_data(type->mtype, type->prtype,
          dtuple_b_ptr, dtuple_f_len,
          rec_b_ptr, rec_f_len);
  if (ret) {
   goto order_resolved;
  }
    
  }
}

page_cur_t* page_cursor;
 buf_block_t* block;
 
 // 通过 cursor 获取 block
 block = btr_cur_get_block(cursor);
 // buf_block_t::frame 中保存 page
 page = buf_block_get_frame(block);
 index = cursor->index;

 // btr_cur_t->page_cursor
 page_cursor = btr_cur_get_page_cur(cursor);

 /* Check locks and write to the undo log, if specified */
  // 真正插入 entry 前，会检查事务锁和记录 undo
  // 其中修改 inherit 值，如果下一条记录上没有锁，就不需要锁分裂
  err = btr_cur_ins_lock_and_undo(flags, cursor, entry,
          thr, mtr, &inherit);

  if (err != DB_SUCCESS) {
    goto fail_err;
  }

  // 插入数据
  *rec = page_cur_tuple_insert(
    page_cursor, entry, index, offsets, heap,
    n_ext, mtr);

/*********************************************************//**
Builds a new-style physical record out of a data tuple and
stores it beginning from the start of the given buffer.
@return pointer to the origin of physical record */
static
rec_t*
rec_convert_dtuple_to_rec_new(
/*==========================*/
 byte*   buf, /*!< in: start address of the physical record */
 const dict_index_t* index, /*!< in: record descriptor */
 const dtuple_t*  dtuple) /*!< in: data tuple */
{
 ulint extra_size;
 ulint status;
 rec_t* rec;

 // 计算记录头大小，记录头大小用 extra_size 表示
 rec_get_converted_size_comp(
  index, status, dtuple->fields, dtuple->n_fields, &extra_size);
 // 因为 buf 是用来存储整个记录的开始位置的，这里的 buf + extra_size 表示存储的第一个列的位置，即 rec 所指的位置
 rec = buf + extra_size;

 // 真正转换格式
 rec_convert_dtuple_to_rec_comp(
  rec, index, dtuple->fields, dtuple->n_fields, NULL,
  status, false);
}

/* Store the data and the offsets */

 // 将每个列的值，以及 null 和 len 的信息存储到对应的位置
 for (i = 0; i < n_fields; i++) {
  const dict_field_t* ifield;
  dict_col_t*  col = NULL;
    
    // 获取每个 field 的值
  field = &fields[i];
    
    // 计算 null 信息，因为这个标志是通过位来存储的，所以对每一个字节都需要做位处理
    // 计算列的大小和存储其长度字节数动态匹配的位置，比如判断变长列的长度占用1个字节或2个字节
    
    // 将元组列信息，写入到 compact 记录的对应列中，len为其对应的存储长度
  memcpy(end, dfield_get_data(field), len);
  }

// 真正插入记录
  rec = page_cur_insert_rec_low(cursor->rec,
              index, rec, *offsets, mtr);