Digging into MariaDB's Source 9: Execution and the Handler API — How the Nested-Loop Join Calls the Engine

What this post covers

This is the ninth post in the "Digging into MariaDB's Source" series. In part 8 we watched the optimizer, inside JOIN::optimize(), choose a join order and an access method for each table (the type column of EXPLAIN). That result is still only a "plan". This post follows the place where that plan actually reads rows, and the handler API through which the server layer says "give me the next row" to a storage engine.

On the map from part 2, this post zooms into the ⑥ Execution and ⑦ handler boundary zones of a query's life, stepping into ⑧ Engine only as far as its entrance. The inside of the engine (how InnoDB pulls rows out of pages and B-trees) starts in part 10.

The questions we want to answer:

• After JOIN::exec() is called, which functions does the first row pass through on its way to the client?
• Is a join really a "nested loop"? What do two- and three-table joins look like in the source?
• What actually changes at execution time between EXPLAIN's ALL, ref and eq_ref, and how do the Handler_read_* counters show the difference?
• What do the ha_ wrappers — which the server layer must go through to reach an engine — actually do, and what stays the same or changes when you switch engines?

All line numbers in this post refer to this commit. How to get the same source and build the Docker image used for the examples is in part 1.

sql_select.cc is over 36,000 lines, and the execution part alone runs to thousands. Subqueries, temporary tables, GROUP BY and window functions all sit on the same framework. This post picks only the main line that a first-time reader can follow. What I left out is listed by name and location in "What this post leaves out" at the end.

Blue boxes are what this post reads. The yellow box (⑧ Engine) is the other side of the handler boundary; here we only look at its entrance. ⑨ Sending results belongs to part 5, but the hand-off from ⑥ to ⑨ (end_send() → select_send) is covered in section 5.

Terms to know first

JOIN is the object that executes one SELECT (strictly, one SELECT_LEX). Preparation (JOIN::prepare(), part 7) → optimization (JOIN::optimize(), part 8) → execution (JOIN::exec(), this post) are all methods of this object.

JOIN_TAB is one table slot in join order (typedef struct st_join_table {...} JOIN_TAB in sql/sql_select.h). One line of EXPLAIN usually corresponds to one JOIN_TAB. It holds the access method the optimizer chose, the piece of condition attached to that table (select_cond), and the stars of this post: two function pointers — read_first_record, which reads the first row, and next_select, which is called when a row is found in this slot.

READ_RECORD is a small struct that means "read the next row from this table" (sql/records.h). Its key member is a single function pointer, read_record_func: rr_sequential for a full scan, join_read_next_same when following the same key through an index, and so on. Each JOIN_TAB has one.

handler is, as we saw in part 2, the engine-side object for one open table (class handler in sql/handler.h). When a TABLE is opened, the engine creates an object of its own subclass (ha_innobase, ha_heap, ha_maria, …) and hangs it on TABLE::file. The server layer only calls this object through table->file->..., without knowing which engine it is.

ha_ wrappers. handler has functions that come in pairs: ha_rnd_next() and rnd_next(), ha_write_row() and write_row(), and so on. The first is the common entry point the server layer calls (implemented once, in handler.cc); the second is the virtual function the engine implements. Section 6 covers this in detail.

record[0] is the row buffer owned by a TABLE. The engine fills it with the row it read, in the server's format, and the server layer's Item_field (an Item that refers to one column, part 6) reads values out of it. Writing goes the other way: the server fills the buffer and hands it over with ha_write_row(record[0]). In the end, what the engine and the server layer exchange is this one buffer.

Nested-loop join. The simplest way to join t1 and t2: "for each row of t1, find its matches in t2". In pseudo-code: for r1 in t1: for r2 in t2 where cond(r1,r2): emit(r1,r2). MariaDB uses this as its basic shape and layers variants such as the join buffer (section 4) and hash joins on top.

1. The entrance to execution — from JOIN::exec() to do_select()

As we saw in part 6 and part 7, a SELECT goes down mysql_execute_command() → execute_sqlcom_select() → handle_select() → mysql_select(). mysql_select() builds a JOIN, calls prepare() and optimize(), and finally reaches this line.

  exec_error= join->exec();

sql/sql_select.cc line 5461

JOIN::exec() just wraps exec_inner() with timing for ANALYZE statements (lines 4924–4945); the real work is in exec_inner(). exec_inner() first filters out cases such as a table-less SELECT 1, a constant condition that is false, and a query pushed whole to an external engine (pushdown). In the ordinary case, it ends like this.

  THD_STAGE_INFO(thd, stage_sending_data);
  DBUG_PRINT("info", ("%s", thd->proc_info));
  result->send_result_set_metadata(
                 procedure ? procedure_fields_list : *fields,
                 Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);

  error= result->view_structure_only() ? false : do_select(this, procedure);

sql/sql_select.cc lines 5141–5147

Two things stand out.

1. THD_STAGE_INFO(thd, stage_sending_data) — this is where the "Sending data" you see in the State column of SHOW PROCESSLIST is set. At the top of exec_inner() it was set to stage_executing ("Executing") (line 4954). If a slow query sits in "Sending data", it is inside do_select() below — reading rows and filtering them. Despite the name, it does not only mean "sending".
2. Column information (metadata) goes out before any row. result is the object that receives the result (a subclass of select_result); for an ordinary SELECT it is select_send, which sends to the client. The client protocol goes "column definition packets → row packets → EOF" (part 5), so column definitions are sent before a single row is read.

do_select() carries the comment "Make a join of all tables and write it on socket or to table". It handles the all-constant-tables case separately; in the ordinary case these few lines are all there is.

    JOIN_TAB *join_tab= join->join_tab +
                        (join->tables_list ? join->const_tables : 0);
    if (join->outer_ref_cond && !join->outer_ref_cond->val_bool())
      error= NESTED_LOOP_NO_MORE_ROWS;
    else
      error= join->first_select(join,join_tab,0);
    if (error >= NESTED_LOOP_OK && likely(join->thd->killed != ABORT_QUERY))
      error= join->first_select(join,join_tab,1);

sql/sql_select.cc lines 25081–25088

first_select is a function pointer, set to first_select= sub_select; when the JOIN is initialized (line 578). join_tab is the first slot after skipping constant tables. Constant tables (const and system in EXPLAIN) already had their single row read by the optimizer inside make_join_statistics() (via join_read_const_table() at line 5912 and elsewhere), so execution does not read them again.

Look at the last argument. It is called once with 0 and once with 1. When this third argument, end_of_records, is true, it is the signal "no more rows are coming". Stages that collect rows (the join buffer, GROUP BY) only release what they collected when they get this signal. We will meet it again with the join buffer in section 4. When do_select() finishes, it closes the tables' cursors with join->join_free() and, if there was no error, announces the end of the result with join->result->send_eof() (lines 25133–25142).

The main line of the execution stage. The sections below check each arrow against the source.

Easy to get wrong: there is no separate executor that interprets the plan

When you hear that the optimizer produces an "execution plan", you might picture a separate executor that interprets it. On the ordinary execution path this post follows, there is no such interpreter. What the optimizer leaves behind is the JOIN_TAB array and the function pointers plugged into each slot (section 3). Execution is just calling those pointers in turn. The "plan" is "which functions to call, in which order".

2. The nested loop — sub_select() and evaluate_join_record()

sub_select() is in charge of one slot. "Read the slot's table one row at a time and hand each row to evaluate_join_record()" is all it does. With comments it is long, but the skeleton is this.

  if (rc != NESTED_LOOP_NO_MORE_ROWS)
  {
    error= (*join_tab->read_first_record)(join_tab);
    if (!error && join_tab->keep_current_rowid)
      join_tab->table->file->position(join_tab->table->record[0]);    
    rc= evaluate_join_record(join, join_tab, error);
  }

  bool skip_over= FALSE;
  READ_RECORD *info= &join_tab->read_record;

  while (rc == NESTED_LOOP_OK && join->return_tab >= join_tab)
  {
    // ... (LooseScan semi-join optimization omitted)
    error= info->read_record();
    // ... (LooseScan omitted)
    if (join_tab->keep_current_rowid && likely(!error))
      join_tab->table->file->position(join_tab->table->record[0]);
    
    rc= evaluate_join_record(join, join_tab, error);
  }

sql/sql_select.cc lines 25570–25613

The first row is read with read_first_record, and every row after that with read_record() (that is, READ_RECORD::read_record_func). The read function's return value error means "got a row" when 0, "no more" when negative, and "error" when positive. Judging that value is the first thing evaluate_join_record() does.

  if (error > 0 || unlikely(join->thd->is_error())) // Fatal error
    DBUG_RETURN(NESTED_LOOP_ERROR);
  if (error < 0)
    DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
  if (unlikely(join->thd->check_killed()))       // Aborted by user
  {
    DBUG_RETURN(NESTED_LOOP_KILLED);            /* purecov: inspected */
  }

  join_tab->tracker->r_rows++;

  if (select_cond)
  {
    select_cond_result= MY_TEST(select_cond->val_bool());
    // ... (check for errors while evaluating the condition)

sql/sql_select.cc lines 25663–25676

• This is one of the places where KILL QUERY takes effect. check_killed() is checked for every row. That is why even a long full scan stops at the next row when you send KILL QUERY. (We saw how connection threads relate to KILL in part 4.)
• tracker->r_rows++ is where the r_rows (rows actually read) shown by ANALYZE SELECT comes from.
• select_cond is the piece of WHERE the optimizer split off and attached to this slot (make_join_select() in part 8). It is evaluated with val_bool() — the moment the Item tree from part 6 is actually computed. Item_field reads its value out of the record[0] the engine has just filled.

If the condition passes (after outer-join and semi-join handling), the row goes on to the next slot.

    if (found)
    {
      enum enum_nested_loop_state rc;
      /* A match from join_tab is found for the current partial join. */
      rc= (*join_tab->next_select)(join, join_tab+1, 0);

sql/sql_select.cc lines 25804–25808

This is where the "nesting" happens. With one row fixed for each earlier slot (this is called a partial row, or partial join), next_select calls the sub_select() for join_tab+1 — the next slot — again. The next slot starts reading its own table from the beginning, and whenever it finds a row it calls the slot after it. The last slot's next_select is not sub_select but end_send(), which emits the result (section 5).

for c in customer: for o in orders where o.customer_id = c.id: send(c, o) becomes the recursive calls sub_select() → evaluate_join_record() → sub_select() in the source.

The loop's state is passed around as a single enum_nested_loop_state (sql/sql_select.h lines 442–447). Its values are NESTED_LOOP_KILLED (-2), NESTED_LOOP_ERROR (-1), NESTED_LOOP_OK (0), NESTED_LOOP_NO_MORE_ROWS (1), NESTED_LOOP_QUERY_LIMIT (3) and NESTED_LOOP_CURSOR_LIMIT (4). Negative values mean stop (error, KILL), NO_MORE_ROWS means "this slot is exhausted", and QUERY_LIMIT means the LIMIT is filled and there is no need to read further. A query with LIMIT 10 finishes right after sending 10 rows because end_send() returns QUERY_LIMIT and every sub_select() sees it and leaves its loop (the comment in end_send() calls this "Abort nicely").

Example 1: the recursion in a backtrace

I checked with gdb that it really looks like this. This example and the others in this post were run on MariaDB 13.2.0 (13.2.0-MariaDB-debug), a Debug build of the reference commit (c894d97781) as is, in a Docker container (Ubuntu 24.04). The example folder is ex/u09-gdb-read.

There are three tables: 10 customers (customer, primary key id), 30 orders (orders, 3 per customer, secondary index on customer_id), and a 4-row note table with no index (city_note).

-- An example written for this post (not MariaDB source)
-- 10 customers, 30 orders (3 per customer, each customer's orders stored next to each other), a 4-row note table with no index.
CREATE DATABASE lab;
USE lab;
CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(10)) ENGINE=InnoDB;
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, amount INT, KEY (customer_id)) ENGINE=InnoDB;
CREATE TABLE city_note (city VARCHAR(10), note VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO customer SELECT seq, CONCAT('cust', seq), ELT(1 + seq % 4, 'Seoul', 'Busan', 'Daegu', 'Incheon')
  FROM seq_1_to_10;
INSERT INTO orders SELECT seq, (seq + 2) DIV 3, seq * 100 FROM seq_1_to_30;
INSERT INTO city_note VALUES ('Seoul','capital'), ('Busan','port'), ('Daegu','inland'), ('Incheon','airport');
ANALYZE TABLE customer, orders, city_note PERSISTENT FOR ALL;

SELECT STRAIGHT_JOIN SUM(o.amount) FROM customer c JOIN orders o ON o.customer_id = c.id — a join that reads customer first (STRAIGHT_JOIN fixes the order) and looks up orders through the customer_id index. I attached gdb to the running mariadbd, set break ha_innobase::rnd_next (the outer table) and break ha_innobase::index_read if key_len > 0 (a call with a key value, i.e. the lookup into the inner table), and printed bt at each stop. Here is the backtrace at the second stop (argument lists and addresses removed).

=== stop 2: index_read with a key
#0  ha_innobase::index_read at /usr/src/mariadb/storage/innobase/handler/ha_innodb.cc:9144
#1  handler::index_read_map at /usr/src/mariadb/sql/handler.h:4443
#2  handler::ha_index_read_map at /usr/src/mariadb/sql/handler.cc:4215
#3  join_read_always_key at /usr/src/mariadb/sql/sql_select.cc:26392
#4  sub_select at /usr/src/mariadb/sql/sql_select.cc:25572
#5  evaluate_join_record at /usr/src/mariadb/sql/sql_select.cc:25808
#6  sub_select at /usr/src/mariadb/sql/sql_select.cc:25575
#7  do_select at /usr/src/mariadb/sql/sql_select.cc:25086
#8  JOIN::exec_inner at /usr/src/mariadb/sql/sql_select.cc:5147
#9  JOIN::exec at /usr/src/mariadb/sql/sql_select.cc:4935
#10 mysql_select at /usr/src/mariadb/sql/sql_select.cc:5461
#11 handle_select at /usr/src/mariadb/sql/sql_select.cc:652
#12 execute_sqlcom_select at /usr/src/mariadb/sql/sql_parse.cc:6266
#13 mysql_execute_command at /usr/src/mariadb/sql/sql_parse.cc:4038
#14 mysql_parse at /usr/src/mariadb/sql/sql_parse.cc:8005
#15 dispatch_command at /usr/src/mariadb/sql/sql_parse.cc:1903
#16 do_command at /usr/src/mariadb/sql/sql_parse.cc:1437

Read from the bottom up, this is the map of the whole post: do_command (part 5) → mysql_parse (part 6) → mysql_execute_command (part 7) → JOIN::exec → do_select line 25086 (first_select(join,join_tab,0)) → sub_select (customer slot) → evaluate_join_record line 25808 (next_select) → sub_select (orders slot) → line 25572 read_first_record = join_read_always_key → handler::ha_index_read_map → (handler.h's default index_read_map computes the key length) → ha_innobase::index_read. Seeing sub_select twice is the proof of the nested loop.

The first stop (the full scan of the outer customer) was ha_innobase::rnd_next ← handler::ha_rnd_next (handler.cc line 4129) ← rr_sequential (records.cc line 517) ← READ_RECORD::read_record ← join_init_read_record (sql_select.cc line 26694) ← sub_select (line 25572), with the same frames below.

3. A different read function per slot — where EXPLAIN's type becomes a function pointer

sub_select() has no idea how its table is read. It only calls read_first_record and read_record_func. Those two pointers are plugged into each slot by make_join_readinfo(), at the tail end of optimization.

      tab->next_select= sub_select;		/* normal select */
    // ...
    table->status=STATUS_NO_RECORD;
    pick_table_access_method (tab);

    if (jcl)
       tab[-1].next_select=sub_select_cache;

sql/sql_select.cc lines 16702–16714

next_select defaults to sub_select, and if a slot uses a join buffer (jcl, a non-zero join cache level), the previous slot's next_select is changed to sub_select_cache (section 4). The last slot's next_select is later overwritten by setup_end_select_func() with an end function such as end_send (lines 4389–4390).

The read functions are chosen by pick_table_access_method() according to the access method (tab->type).

static void
pick_table_access_method(JOIN_TAB *tab)
{
  switch (tab->type) 
  {
  case JT_REF:
    tab->read_first_record= join_read_always_key;
    tab->read_record.read_record_func= join_read_next_same;
    break;
  // ...
  case JT_EQ_REF:
    tab->read_first_record= join_read_key;
    tab->read_record.read_record_func= join_no_more_records;
    break;

sql/sql_select.cc lines 15822–15845

For JT_ALL (full scan) and JT_RANGE (range), make_join_readinfo() sets read_first_record= join_init_read_record itself, and for JT_NEXT (full index scan, index in EXPLAIN) it sets join_read_first (lines 16750–16800). In summary:

eq_ref's next-row function, join_no_more_records, simply returns -1 (lines 26461–26465). The lookup was by a unique key, so there is at most one match and no point in even trying a second row.

+ is public, # protected, - private, and the italic rnd_next() is pure virtual. What the optimizer leaves behind is the top-left of this picture — the JOIN_TAB array with its function pointers plugged in.

Full scan: join_init_read_record → rr_sequential

join_init_read_record() first builds a rowid filter if one is needed, sorts if a sort (filesort) is needed, prepares a range read, and then calls init_read_record(). For a plain full scan it ends up in this branch.

  else
  {
    DBUG_PRINT("info",("using rr_sequential"));
    info->read_record_func= rr_sequential;
    if (unlikely(table->file->ha_rnd_init_with_error(1)))
      DBUG_RETURN(1);

sql/records.cc lines 327–332

It tells the engine to "get ready to scan from the start" with ha_rnd_init(), and plugs in rr_sequential as the next-row function. On its last line, join_init_read_record() calls that function once to read the first row (return tab->read_record.read_record();, line 26694). rr_sequential is short.

int rr_sequential(READ_RECORD *info)
{
  int tmp;
  while ((tmp= info->table->file->ha_rnd_next(info->record())))
  {
    tmp= rr_handle_error(info, tmp);
    break;
  }
  return tmp;
}

sql/records.cc lines 514–523

info->record() is table->record[0] (records.h line 78). If the engine fills that buffer with the next row the result is 0; at the end it is HA_ERR_END_OF_FILE, which rr_handle_error() turns into -1.

ref: join_read_always_key → join_read_next_same

ref builds a key from the previous slot's value and looks it up in an index.

static int
join_read_always_key(JOIN_TAB *tab)
{
  // ... (opening the index once with ha_index_init() omitted)
  if (unlikely(cp_buffer_from_ref(tab->join->thd, table, &tab->ref)))
    return -1;
  // ...
  if ((error= table->file->ha_index_read_map(table->record[0],
                                             tab->ref.key_buff,
                                             make_prev_keypart_map(tab->ref.key_parts),
                                             HA_READ_KEY_EXACT)))
  {
    if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
      return report_error(table, error);
    return -1; /* purecov: inspected */
  }
  return 0;
}

sql/sql_select.cc lines 26366–26402

cp_buffer_from_ref() copies the previous slot's value (c.id) into the key buffer ref.key_buff, and ha_index_read_map(..., HA_READ_KEY_EXACT) positions the cursor on "the first key equal to this value". From the next row on, join_read_next_same reads "the next row with the same key value" with ha_index_next_same() and stops when it gets HA_ERR_END_OF_FILE because the value changed (lines 26468–26485).

eq_ref: join_read_key and a one-slot cache

eq_ref's join_read_key() calls join_read_key2(), which has a device that ref lacks.

  if (cmp_buffer_with_ref(thd, table, table_ref) ||
      (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW)))
  {
    // ...
    error=table->file->ha_index_read_map(table->record[0],
                                  table_ref->key_buff,
                                  make_prev_keypart_map(table_ref->key_parts),
                                  HA_READ_KEY_EXACT);

sql/sql_select.cc lines 26276–26296

cmp_buffer_with_ref() compares the key to look up now with the key looked up last time and returns true only if they differ (lines 29707–29726). If they are the same, the engine is not called and the previous row still sitting in record[0] is reused. The function's comment calls this a "one-element lookup cache" (lines 26229–26231). The shortcut works because the key is unique: the same key is guaranteed to give the same row.

Example 2: counting calls with Handler_read_*

The Handler_read_* counters in SHOW STATUS are bumped once each time the handler's ha_ wrapper is called (we'll see that line in section 6). So for each access method we can count how many times the engine was called. For each query I printed the main EXPLAIN columns on one line, then sent FLUSH STATUS; <query>; SHOW SESSION STATUS WHERE Variable_name LIKE 'Handler_read%' AND Value <> '0'; in the same session and kept only non-zero values (example folder ex/u09-handler-counts, same data as example 1). The results (the join buffer part comes in section 4):

--- 0. no table
SELECT 1
  EXPLAIN: table=NULL type=NULL key=NULL rows=NULL extra=No tables used

--- 1. full scan (ALL)
SELECT SUM(amount) FROM orders WHERE amount > 1000
  EXPLAIN: table=orders type=ALL key=NULL rows=30 extra=Using where
  Handler_read_rnd_next  31

--- 2. primary key (const)
SELECT amount FROM orders WHERE id = 7
  EXPLAIN: table=orders type=const key=PRIMARY rows=1 extra=
  Handler_read_key       1

--- 3. secondary key (ref)
SELECT SUM(amount) FROM orders WHERE customer_id = 4
  EXPLAIN: table=orders type=ref key=customer_id rows=3 extra=
  Handler_read_key       1
  Handler_read_next      3

--- 4. join, eq_ref
SELECT STRAIGHT_JOIN SUM(o.amount), COUNT(c.name) FROM orders o JOIN customer c ON c.id = o.customer_id
  EXPLAIN: table=o type=ALL key=NULL rows=30 extra=Using where
  EXPLAIN: table=c type=eq_ref key=PRIMARY rows=1 extra=
  Handler_read_key       10
  Handler_read_rnd_next  31

--- 5. join, ref
SELECT STRAIGHT_JOIN SUM(o.amount), COUNT(c.name) FROM customer c JOIN orders o ON o.customer_id = c.id
  EXPLAIN: table=c type=ALL key=NULL rows=10 extra=
  EXPLAIN: table=o type=ref key=customer_id rows=3 extra=
  Handler_read_key       10
  Handler_read_next      30
  Handler_read_rnd_next  11

Let's hold each one up against the source.

• No. 0: with no table, nothing goes up. It also serves as a baseline showing that SHOW STATUS itself does not disturb the numbers.
• No. 1, ALL: a 30-row table gives Handler_read_rnd_next = 31. rr_sequential called ha_rnd_next() once per row, plus one last time to discover "the end" (HA_ERR_END_OF_FILE). The engine also fetched the 20 rows that WHERE amount > 1000 rejects; the filtering was done in the server layer by evaluate_join_record().
• No. 2, const: Handler_read_key 1. That one call happened not in execution but in the optimizer (join_read_const_table()).
• No. 3, ref: read_key 1 (the first row), read_next 3. There are 3 matching rows, yet next is 3, because join_read_next_same got a row twice and on the third call got "the value changed (end)". ha_index_next_same() bumps the counter regardless of the result (sql/handler.cc lines 4373–4392).
• No. 5, ref join: the full scan of the outer customer is rnd_next 11 (10 rows + the end), and the inner orders is read_key 1 + read_next 3 per customer, so 10 × (1 + 3) = 10 + 30. The nested loop shows up in the numbers exactly.

Easy to get wrong: an eq_ref join, yet Handler_read_key is 10, not 30

No. 4 is an eq_ref that looks up customer by primary key for each of the 30 rows of orders. You'd expect 30 lookups, but Handler_read_key is 10. In the example data each customer's 3 orders are stored next to each other (customer_id = 1,1,1,2,2,2,…), so for 20 of the 30 rows cmp_buffer_with_ref() decided "same key as last time" and did not call the engine. Had the orders been shuffled with respect to customer, the number would have been closer to 30. Handler_read_key is not "the number of logical lookups" but the number of times the engine was actually called.

4. The join buffer — sub_select_cache() and JOIN_CACHE

When the inner table has no usable index, a nested loop is expensive: for every outer row the inner table has to be read again from start to finish. So MariaDB collects outer rows in a join buffer and then scans the inner table once, matching each of its rows against everything in the buffer. That is EXPLAIN's Using join buffer (flat, BNL join). BNL stands for Block Nested Loop — a nested loop done in blocks.

As we saw in section 3, for a slot that uses a join buffer, the previous slot's next_select has been changed to sub_select_cache. So when the previous slot finds a row, instead of reading the next slot right away it comes here.

  join_tab->cache->reset_join(join);

  if (end_of_records)
  {
    rc= cache->join_records(FALSE);
    if (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS ||
        rc == NESTED_LOOP_QUERY_LIMIT)
      rc= sub_select(join, join_tab, end_of_records);
    DBUG_RETURN(rc);
  }
  // ...
  if (!(err= test_if_use_dynamic_range_scan(join_tab)))
  {
    if (!cache->put_record())
      DBUG_RETURN(NESTED_LOOP_OK); 
    // ... (comment: the buffer is full, so find matches for every row in it)
    rc= cache->join_records(FALSE);
    DBUG_RETURN(rc);
  }

sql/sql_select.cc lines 25314–25342

• put_record() copies the partial row into the buffer. If there is still room it returns false, and sub_select_cache() returns without reading anything — the previous slot goes off to read its next row.
• If the buffer is full, join_records() finds all matches right there and empties the buffer.
• This is where section 1's first_select(join, join_tab, 1) matters. When the previous slot is done and end_of_records arrives, a final join_records() runs over the rows left in the buffer. Without that signal, the leftover rows would never be processed.

The heart of JOIN_CACHE::join_matching_records(), which join_records() calls, is this double loop.

  /* Prepare to retrieve all records of the joined table */
  if (unlikely((error= join_tab_scan->open())))
  // ...
  while (!(error= join_tab_scan->next()))   
  {
    // ...
    uchar *rec_ptr;
    /* Read each possible candidate from the buffer and look for matches */
    while ((rec_ptr= get_next_candidate_for_match()))
    {

sql/sql_join_cache.cc lines 2358–2388

The outer loop is over rows of the inner table (join_tab_scan->next()), the inner loop over candidate rows in the buffer — the order is flipped. JOIN_TAB_SCAN::open() ends up calling section 3's join_init_read_record() (lines 3497–3503), and next() calls read_record(). From the engine's point of view it is one full scan per buffer.

The yellow cylinder is the join buffer. Its size is join_buffer_size (default 256KB, Sys_join_buffer_size in sys_vars.cc), and which kinds of buffer may be used is decided by join_cache_level (default 2).

The kinds of join buffer are classes in sql/sql_join_cache.h. Built on JOIN_CACHE are JOIN_CACHE_BNL (block nested loop), JOIN_CACHE_BNLH (a hash join that builds a hash table over the buffer; it inherits through JOIN_CACHE_HASHED), and JOIN_CACHE_BKA·JOIN_CACHE_BKAH (Batched Key Access — looking up the index in a batch with the keys collected in the buffer; BKAH inherits from BNLH). What differs is how rows go into the buffer (the hashed ones override put_record()), how match candidates are picked from the buffer (get_next_candidate_for_match() and friends), and how the inner table is read (BKA uses JOIN_TAB_SCAN_MRR); join_records() and the sub_select_cache() framework above are the same.

Example 3: with and without the buffer

In a three-table join, the last slot city_note (no index, 4 rows) uses the join buffer. I also ran the same query with join_cache_level=0 (join buffers forbidden).

--- 6. join buffer (BNL)
SELECT STRAIGHT_JOIN COUNT(*) FROM orders o JOIN customer c JOIN city_note n ON n.city = c.city AND c.id = o.customer_id
  EXPLAIN: table=o type=index key=customer_id rows=30 extra=Using where; Using index
  EXPLAIN: table=c type=eq_ref key=PRIMARY rows=1 extra=
  EXPLAIN: table=n type=ALL key=NULL rows=4 extra=Using where; Using join buffer (flat, BNL join)
  Handler_read_first     1
  Handler_read_key       10
  Handler_read_next      30
  Handler_read_rnd_next  5

=== join_cache_level=0 (no join buffer)
  Handler_read_first     1
  Handler_read_key       10
  Handler_read_next      30
  Handler_read_rnd_next  150

Handler_read_rnd_next for reading city_note is 5 with the buffer (4 rows + the end, one scan) and 150 without (30 rows from the previous slots × 5). All 30 rows fit into one 256KB buffer, so a single full scan was enough.

o's type=index (Handler_read_first 1 + Handler_read_next 30) is exactly section 3's table: JT_NEXT → join_read_first → join_read_next. The customer_id index alone provides every column needed from o (customer_id, hence Using index), so the optimizer chose to scan the index instead of the table. And c's eq_ref again shows Handler_read_key 10, thanks to section 3's one-slot cache.

5. Sending results — end_send() → select_send → Protocol

A row that passes the conditions of the last slot goes to end_send(), its next_select (chosen by setup_end_select_func(); if rows have to be grouped before sending it is end_send_group(), and stages that collect into a temporary table get a different end function such as end_write() plugged in separately). end_send() checks HAVING, handles LIMIT … WITH TIES, and then passes the row on.

  if (join->do_send_rows)
  {
    int error;
    /* result < 0 if row was not accepted and should not be counted */
    if (unlikely((error= join->result->send_data_with_check(*fields,
                                                            join->unit,
                                                            join->send_records))))
    {
      if (error > 0)
        DBUG_RETURN(NESTED_LOOP_ERROR);
      // error < 0 => duplicate row
      join->duplicate_rows++;
    }
  }

sql/sql_select.cc lines 26971–26984

Right after this it counts send_records and returns NESTED_LOOP_QUERY_LIMIT once the LIMIT is reached (section 2). send_data_with_check() skips, without sending, as many rows as the LIMIT's OFFSET and then calls send_data() (sql/sql_class.cc lines 3371–3387). This is where you can confirm the saying that OFFSET 1000 reads 1000 rows and throws them away. select_send::send_data(), which sends to the client, looks like this.

int select_send::send_data(List<Item> &items)
{
  Protocol *protocol= thd->protocol;
  DBUG_ENTER("select_send::send_data");

  protocol->prepare_for_resend();
  if (protocol->send_result_set_row(&items))
  {
    // ... (on failure, drop the half-built row and return an error)
  }

  thd->inc_sent_row_count(1);

  /* Don't return error if disconnected, only if write fails */
  if (likely(thd->vio_ok()))
    DBUG_RETURN(protocol->write());
  // ...
}

sql/sql_class.cc lines 3477–3496

send_result_set_row() computes the value of each Item in the SELECT list and puts it in a packet, and protocol->write() writes the packet to the network buffer (packet layout and the text/binary protocol difference are in part 5). Rows go out one at a time as soon as they are found, not after everything is collected. The way the innermost level of the nested loop leads straight into the protocol is a defining feature of this design.

The green path is select_send, which goes to the client; the gray boxes are other receivers that fit the same slot. The class relationships were checked in sql/sql_class.h (lines 6827, 6934, 6964 and 7145).

Easy to get wrong: the receiver isn't always the client

join->result is the select_result interface, and select_send is just one implementation. For INSERT … SELECT it is select_insert, which puts rows into a table; for CREATE TABLE … SELECT it is select_create; and when collecting a UNION's result into a temporary table it is select_unit — subclasses that slot into the same place (all declared in sql/sql_class.h). So the same do_select()/end_send() framework handles both "send to the client" and "write into another table".

6. The handler's ha_ wrappers — the one door from the server layer to the engine

Now the other side: the boundary with the engine. Inside the handler class there is a private: section with this comment.

private:
  /*
    Low-level primitives for storage engines.  These should be
    overridden by the storage engine class. To call these methods, use
    the corresponding 'ha_*' method above.
  */

sql/handler.h lines 5428–5433

Engine-side functions such as write_row(), update_row(), delete_row(), rnd_init(), index_init() and external_lock() are declared private below it. In C++, a private virtual function can be overridden by a subclass but cannot be called from outside (the server layer). So the server layer has no choice but to go through public wrappers such as ha_write_row(). "Hide the virtual function; let a public function do the work before and after, then call it" is a common C++ design (the Non-Virtual Interface idiom).

Not every engine function is private. On the read side, index_read_map(), index_next() and index_read() are protected (the first two under the protected: on line 4437, index_read() under the one on line 5573), and rnd_next() is a public pure virtual function (lines 4564–4566). Even so, the SQL layer's execution code, as we have seen throughout this post, always calls the ha_ side.

Easy to get wrong: engines declare the same functions public

ha_innobase declares write_row() with override in a public: section (ha_innodb.h line 120, under the public: at line 65). ha_heap does the same. C++ lets an override widen access. But the server layer always calls through a handler* (TABLE::file), so access is checked against handler's declaration. Reading only the engine source, it looks like "just a public function", but the only way the server layer can call it is through the ha_ wrapper.

What ha_rnd_next() does

Here is the skeleton of ha_rnd_next(), called once per row of a full scan.

int handler::ha_rnd_next(uchar *buf)
{
  int result;
  DBUG_ENTER("handler::ha_rnd_next");
  DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE ||
              m_lock_type != F_UNLCK);
  DBUG_ASSERT(inited == RND);

  // ... (test code that fakes errors via debug_dbug omitted)
  do
  {
    TABLE_IO_WAIT(tracker, PSI_TABLE_FETCH_ROW, MAX_KEY, result,
      { result= rnd_next(buf); })
    if (result != HA_ERR_RECORD_DELETED)
      break;
    status_var_increment(table->in_use->status_var.ha_read_rnd_deleted_count);
  } while (!table->in_use->check_killed(1));

  if (result == HA_ERR_RECORD_DELETED)
    result= HA_ERR_ABORTED_BY_USER;
  else
  {
    // ... (on success: row statistics and virtual column computation)
    increment_statistics(&SSV::ha_read_rnd_next_count);
  }

  table->status=result ? STATUS_NOT_FOUND: 0;
  // ... (DEBUG_SYNC omitted)
  DBUG_RETURN(result);
}

sql/handler.cc lines 4110–4154

The wrapper does six things around the engine call.

1. Checking the usage rules. Unless it is a temporary table, the table must be locked (m_lock_type != F_UNLCK), and ha_rnd_init() must have been called first (inited == RND). These are DBUG_ASSERTs, so only Debug builds check them, but for engine authors they are a promise: "calls come in this order".
2. Instrumentation. The TABLE_IO_WAIT macro wraps the engine call (handler.h lines 6031–6041). The per-table timing (tracker), which is only switched on for ANALYZE statements or log_slow_verbosity=engine (sql_select.cc lines 32085–32090), and performance_schema's table I/O wait events (MYSQL_TABLE_IO_WAIT) are recorded here.
3. The engine call. rnd_next(buf) — a virtual function, so what actually runs is ha_innobase::rnd_next() or ha_heap::rnd_next().
4. Skipping deleted rows. If the engine returns HA_ERR_RECORD_DELETED (a deleted slot), it bumps Handler_read_rnd_deleted and reads again, checking check_killed() in between.
5. Statistics. increment_statistics(&SSV::ha_read_rnd_next_count) — this is Handler_read_rnd_next (the status variable table in mysqld.cc binds "Handler_read_rnd_next" to this field, line 8067). It goes up whether the result is a row or the end. That is why the 30-row scan in example 2 showed 31.
6. Status. It sets table->status, which section 3's join_read_key2() looks at.

The increment_statistics() in step 5 does more than bump a number.

inline void handler::increment_statistics(ulong SSV::*offset) const
{
  status_var_increment(table->in_use->status_var.*offset);
  table->in_use->check_limit_rows_examined();
}

sql/sql_class.h lines 8163–8167

The limit on rows read for SELECT … LIMIT ROWS EXAMINED n is checked right here, inside the wrapper. This is where the set_limit_rows_examined() switched on at the top of section 1's exec_inner() is used.

Only the yellow boxes differ per engine; every engine goes through the blue boxes the same way.

The index lookup ha_index_read_map() has the same shape: it calls index_read_map() inside TABLE_IO_WAIT, bumps Handler_read_key, and if nothing was found bumps ha_read_key_miss for user statistics too (sql/handler.cc lines 4204–4231). If an engine does not override index_read_map(), handler's default implementation computes the key length with calculate_key_len() and passes it on to index_read() (sql/handler.h lines 4438–4444); frame #1 handler::index_read_map at handler.h:4443 in example 1's backtrace is that spot. InnoDB uses this default as is.

On the engine side: ha_innobase::rnd_next()

Past the wrapper is the engine. The body of InnoDB's rnd_next() is a single if.

        if (m_start_of_scan) {
                error = index_first(buf);

                if (error == HA_ERR_KEY_NOT_FOUND) {
                        error = HA_ERR_END_OF_FILE;
                }

                m_start_of_scan = false;
        } else {
                error = general_fetch(buf, ROW_SEL_NEXT, 0);
        }

storage/innobase/handler/ha_innodb.cc lines 9670–9680 (InnoDB source is indented with tabs; here they are converted to 8 spaces.)

In InnoDB, a "full table scan" is walking the clustered index (the primary-key B-tree) from start to finish. rnd_init() switches the active index to the clustered index (the primary key, or a hidden row-ID index InnoDB generates when there is no primary key) (lines 9605–9642); the first rnd_next() puts the cursor at the very front with index_first() → index_read(), and from then on general_fetch() fetches the next record. Both index_read() and general_fetch() end up calling row_search_mvcc() (line 9220, lines 9471–9472). Inside it, the B-tree is walked (part 10) and the version of the row appropriate for the transaction is chosen (part 11).

When I first ran example 1 with an unconditional breakpoint on ha_innobase::index_read, it did not stop at the inner table's ref lookup I expected, but first at the outer table's full scan. The backtrace was ha_innobase::index_read ← ha_innobase::index_first ← ha_innobase::rnd_next ← handler::ha_rnd_next …. That is how I learned that, inside InnoDB, a full scan is an index read too, and I fixed the example by adding the condition if key_len > 0 (index_first() calls index_read(buf, NULL, 0, …) with no key).

Example 4: same spots, different engines

I made five copies of the same table differing only in engine, and sent the same INSERT and the same SELECT to each (example folder ex/u09-engines). BLACKHOLE is a dynamic plugin, so I loaded it first with INSTALL SONAME 'ha_blackhole' (part 3). The tables are CREATE TABLE t_<engine> (id INT PRIMARY KEY, v INT) ENGINE=<engine>, and the statements are INSERT INTO t_<engine> SELECT seq, seq * 10 FROM seq_1_to_5 and SELECT COUNT(*) FROM t_<engine> WHERE v > 0 (the WHERE makes every engine actually read the rows one by one). The last line, for comparison, sends SUM() to the SEQUENCE engine (seq_1_to_5).

=== 1. same statements, different engines
InnoDB     INSERT 5 rows:  Handler_write=5
InnoDB     SELECT      :  result=5  Handler_read_rnd_next=6
Aria       INSERT 5 rows:  Handler_write=5
Aria       SELECT      :  result=5  Handler_read_rnd_next=6
MyISAM     INSERT 5 rows:  Handler_write=5
MyISAM     SELECT      :  result=5  Handler_read_rnd_next=6
MEMORY     INSERT 5 rows:  Handler_write=5
MEMORY     SELECT      :  result=5  Handler_read_rnd_next=6
BLACKHOLE  INSERT 5 rows:  Handler_write=5
BLACKHOLE  SELECT      :  result=0  Handler_read_rnd_next=1
SEQUENCE   SELECT      :  result=15

Then, with gdb, I set breakpoints on each engine's rnd_next in turn and printed bt 3 (top three frames only).

=== 2. who answers handler::ha_rnd_next
#0  ha_heap::rnd_next at /usr/src/mariadb/storage/heap/ha_heap.cc:425
#1  handler::ha_rnd_next at /usr/src/mariadb/sql/handler.cc:4129
#2  rr_sequential at /usr/src/mariadb/sql/records.cc:517
#0  ha_maria::rnd_next at /usr/src/mariadb/storage/maria/ha_maria.cc:2646
#1  handler::ha_rnd_next at /usr/src/mariadb/sql/handler.cc:4129
#2  rr_sequential at /usr/src/mariadb/sql/records.cc:517
#0  ha_blackhole::rnd_next at /usr/src/mariadb/storage/blackhole/ha_blackhole.cc:138
#1  handler::ha_rnd_next at /usr/src/mariadb/sql/handler.cc:4129
#2  rr_sequential at /usr/src/mariadb/sql/records.cc:517

• The four engines give identical numbers. A 5-row table scan gives Handler_read_rnd_next 6 (5 rows + the end), because the counting happens not in the engine but in one place, handler::ha_rnd_next().
• gdb's #1 and #2 are the same lines whatever the engine (handler.cc line 4129 = the rnd_next(buf) call inside TABLE_IO_WAIT, and records.cc line 517); only #0 changes. The server layer just calls a virtual function without knowing which engine is behind it.
• BLACKHOLE accepts writes and has nothing to read. The INSERT succeeds and Handler_write goes up by 5, but the SELECT returns 0 rows with a single rnd_next (immediately the end). The source says exactly that — ha_blackhole::write_row() only handles the auto-increment column and returns 0 (success), and rnd_next() returns HA_ERR_END_OF_FILE right away unless it is applying row-based replication (storage/blackhole/ha_blackhole.cc lines 104–145). As long as it keeps the handler API contract, even "an engine that stores nothing" is an engine.

Easy to get wrong: the SEQUENCE engine's SUM() has no Handler_read_rnd_next

The last line, SELECT SUM(seq) FROM seq_1_to_5 WHERE seq > 0, returns 15, yet Handler_read_rnd_next does not appear at all (this example prints only Handler_write and Handler_read_rnd_next). At first my gdb example also had a breakpoint on ha_seq::rnd_next; it was never reached, so gdb sat waiting and the example never finished. The reason is that the seq column of a SEQUENCE table is its primary key. Run EXPLAIN on this query separately in the same container and you get type=range, key=PRIMARY, Extra=Using where; Using index; print every Handler_read% and you get Handler_read_key 1 and Handler_read_next 5. The optimizer treats seq > 0 as a range condition on the primary key and picks range from the table in section 3, so the full-scan function rnd_next() is never called. Stop gdb at ha_seq::index_read_map and the stack climbs handler::ha_index_read_map ← handler::read_range_first ← handler::multi_range_read_next ← QUICK_RANGE_SELECT::get_next ← rr_quick. That is why I dropped SEQUENCE from the gdb example.

Drop the WHERE and yet another route opens. The SEQUENCE engine implements a group_by_handler that takes over the whole aggregation. create_group_by_handler() in storage/sequence/sequence.cc builds and returns an ha_seq_group_by_handler only when there is no WHERE and no GROUP BY (line 408) and the SELECT list is nothing but SUM()·COUNT() over the primary key (lines 397–450). Then do_select() takes the join->pushdown_query->execute(join) branch at its very top instead of the nested loop from section 1 (lines 24963–24970). Indeed, EXPLAIN SELECT SUM(seq) FROM seq_1_to_5 shows Storage engine handles GROUP BY in Extra, and running it raises no Handler_* status variable at all: the engine computes the sum of 1 through 5 without reading rows. Just remember that besides the handler API, there are routes through which an engine takes over a big chunk of a query (the group by handler, select_lex->pushdown_select).

The entrance to locking: ha_external_lock()

Besides the row functions, the handler API has a function that announces "this statement starts / has finished using this table". Part 7's open_and_lock_tables() opens the tables and then calls it for each table in mysql_lock_tables() → lock_external().

    if (unlikely((error=(*tables)->file->ha_external_lock(thd,lock_type))))

sql/lock.cc line 443

lock_type is F_RDLCK for reads and F_WRLCK for writes, and when the statement ends and the tables are closed (close_thread_tables() → mysql_unlock_tables()) it is called once more with F_UNLCK. The name says "external lock", but, as handler.h's comment explains, that name merely comes from the first implementation, in which MyISAM used fcntl here to set an advisory lock on the data file (sql/handler.h lines 5542–5544); for InnoDB it is the signal for the start and end of a statement.

        switch (lock_type) {
        case F_UNLCK:
                // ...
                if (--trx->n_mysql_tables_in_use) {
                        break;
                }

                /* If the lock count drops to zero we know that the
                current SQL statement has ended */
                // ...
                if (!not_autocommit) {
                        if (!not_started) {
                                innobase_commit(thd, TRUE);
                        }
                } else if (trx->isolation_level <= TRX_ISO_READ_COMMITTED) {
                        trx->read_view.close();
                }
                break;
        case F_WRLCK:
                /* If this is a SELECT, then it is in UPDATE TABLE ...
                or SELECT ... FOR UPDATE */
                m_prebuilt->select_lock_type = LOCK_X;
                m_prebuilt->stored_select_lock_type = LOCK_X;
                goto set_lock;

storage/innobase/handler/ha_innodb.cc lines 16797–16825 (tabs converted to 8 spaces.)

• With F_WRLCK, it marks that rows read by this statement should get exclusive locks (LOCK_X). This is where UPDATE and SELECT … FOR UPDATE start locking the rows they read. In the set_lock: that follows, innobase_register_trx() registers the transaction in the server's list of transactions (line 16834).
• When F_UNLCK releases the last table this statement used, InnoDB considers "the statement finished". In autocommit mode, if an InnoDB transaction is still there it commits it with innobase_commit(thd, TRUE); inside an explicit transaction (BEGIN or autocommit=0) at READ COMMITTED or lower it closes the read view (which is why each statement gets a fresh snapshot). Earlier in the function it also marks "a new statement starts" with m_prebuilt->sql_stat_start = TRUE (line 16730). Transactions and read views come in part 11.

Easy to get wrong: Handler_external_lock is always 0

SHOW STATUS has a variable called Handler_external_lock, which you would expect to count ha_external_lock() calls. But at the reference commit, no code increments it. The field ha_external_lock_count is bound in the status variable table (sql/mysqld.cc line 8052) and declared in STATUS_VAR (sql/sql_class.h line 1038), and that is all: searching the whole repository (.cc, .h, .ic) for external_lock_count finds nothing else. The body of ha_external_lock() we saw above has no increment_statistics() either. On the last line of example 5, on a server that had run several INSERTs, UPDATEs and DELETEs, SHOW GLOBAL STATUS LIKE 'Handler_external_lock' was 0. (I did not check since when other versions behave this way.)

7. The write path — from mysql_insert() to ha_innobase::write_row()

Now the opposite of reading: INSERT. The big switch in mysql_execute_command() we saw in part 7 sends SQLCOM_INSERT to mysql_insert(). mysql_insert() opens and locks the table (open_and_lock_tables(), which is where section 6's ha_external_lock(F_WRLCK) happens), fills table->record[0] with the values for each row of VALUES, and calls Write_record::write_record() (sql/sql_insert.cc line 1232). write_record() branches on how duplicates are handled (DUP_ERROR, DUP_UPDATE for ON DUPLICATE KEY UPDATE, DUP_REPLACE for REPLACE); for a plain INSERT it is single_insert().

int Write_record::single_insert(ha_rows *inserted)
{
  // ... (DBUG_EXECUTE_IF for replication tests omitted)
  int error= table->file->ha_write_row(table->record[0]);

sql/sql_insert.cc lines 2387–2396

ha_write_row() is the same framework as the read wrapper, plus what writing needs.

int handler::ha_write_row(const uchar *buf)
{
  int error;
  DBUG_ENTER("handler::ha_write_row");
  // ... (DEBUG_SYNC and WSREP test code omitted)
  DBUG_ASSERT(table_share->tmp_table || m_lock_type == F_WRLCK);
  DBUG_ASSERT(buf == table->record[0]);

  if (!(ha_table_flags() & HA_CHECK_UNIQUE_AFTER_WRITE) &&
      (error= ha_check_inserver_constraints(NULL, buf)))
    goto err;

  MYSQL_INSERT_ROW_START(table_share->db.str, table_share->table_name.str);
  mark_trx_read_write();
  increment_statistics(&SSV::ha_write_count);

  TABLE_IO_WAIT(tracker, PSI_TABLE_WRITE_ROW, MAX_KEY, error,
                      { error= write_row(buf); })
  // ...
  rows_stats.inserted++;
  error= binlog_log_row(0, buf,
                        Write_rows_log_event::binlog_row_logging_function);

sql/handler.cc lines 8642–8702

• It checks the promise that writes only go to tables locked with F_WRLCK.
• ha_check_inserver_constraints() checks the two constraints the server layer is responsible for: WITHOUT OVERLAPS on period keys (ha_check_overlaps()) and UNIQUE on long columns that an engine index cannot enforce (ha_check_long_uniques()).
• mark_trx_read_write() marks "this engine has written in this transaction" (the real work happens once per statement in mark_trx_read_write_internal(), handler.cc lines 5807–5831). It is the mark that lets read-only engines be left out of two-phase commit at commit time; it comes back in part 12's two-phase commit.
• Handler_write is bumped before the engine is called. So a failed INSERT counts as one too (example 5).
• binlog_log_row() — the row events of the row-based binlog (binlog_format=ROW) are built after the engine call (when the write succeeded), inside the wrapper. So binlog handling is the same whichever engine you write to. The binlog comes in part 12.
• ha_update_row() and ha_delete_row() follow the same order: mark_trx_read_write() → bump Handler_update/Handler_delete → update_row()/delete_row() inside TABLE_IO_WAIT → binlog_log_row() (lines 8725–8798, lines 8828–8877).

On the engine side, ha_innobase::write_row() checks the transaction state, gets a value with update_auto_increment() if there is an auto-increment column, prepares the "template" for converting between the server's format and InnoDB's, and then hands off to InnoDB's insert function.

        vers_set_fields = table->versioned_write(VERS_TRX_ID) ?
                ROW_INS_VERSIONED : ROW_INS_NORMAL;

        /* Execute insert graph that will result in actual insert. */
        error = row_insert_for_mysql((byte*) record, m_prebuilt, vers_set_fields);

storage/innobase/handler/ha_innodb.cc lines 7966–7970 (tabs converted to 8 spaces.)

From row_insert_for_mysql() on, we are inside InnoDB. Putting records into the clustered and secondary indexes is part 10, undo records and locks are part 11, and the redo log is part 12.

Like reading, writing enters the engine through a single ha_ wrapper.

Example 5: the INSERT backtrace and the Handler_* numbers for DML

I set a breakpoint on ha_innobase::write_row and sent INSERT INTO t VALUES (1,10) (example folder ex/u09-gdb-write).

=== 1. INSERT INTO t VALUES (1,10)
#0  ha_innobase::write_row at /usr/src/mariadb/storage/innobase/handler/ha_innodb.cc:7919
#1  handler::ha_write_row at /usr/src/mariadb/sql/handler.cc:8666
#2  Write_record::single_insert at /usr/src/mariadb/sql/sql_insert.cc:2396
#3  Write_record::write_record at /usr/src/mariadb/sql/sql_insert.cc:2438
#4  mysql_insert at /usr/src/mariadb/sql/sql_insert.cc:1232
#5  mysql_execute_command at /usr/src/mariadb/sql/sql_parse.cc:4549
#6  mysql_parse at /usr/src/mariadb/sql/sql_parse.cc:8005
#7  dispatch_command at /usr/src/mariadb/sql/sql_parse.cc:1903
#8  do_command at /usr/src/mariadb/sql/sql_parse.cc:1437

Unlike a SELECT, there is no JOIN and no sub_select. An INSERT that gets its rows from VALUES has no table to read, so its execution stage is much shorter. handler.cc line 8666 is the write_row(buf) call inside TABLE_IO_WAIT.

Next, in the same session, I printed the non-zero Handler_* values with FLUSH STATUS → DML → SHOW SESSION STATUS. The last INSERT deliberately uses a primary key that already exists.

=== 2. Handler_* for DML
--- INSERT INTO t VALUES (2,20),(3,30),(4,40)
  Handler_commit         1
  Handler_write          3
--- UPDATE t SET v = v + 1 WHERE id >= 3
  Handler_commit         1
  Handler_read_key       1
  Handler_read_next      2
  Handler_update         2
--- DELETE FROM t WHERE id = 2
  Handler_commit         1
  Handler_delete         1
  Handler_read_key       1
--- INSERT INTO t VALUES (1,99)
  ERROR 1062 (23000) at line 1: Duplicate entry '1' for key 'PRIMARY'
  Handler_rollback       1
  Handler_write          1
Handler_external_lock	0

• The three-row INSERT gives Handler_write 3 — three ha_write_row() calls.
• UPDATE … WHERE id >= 3 found id=3 through a primary-key range (read_key 1), read the next row 4 and then the end (read_next 2), and changed two rows (Handler_update 2). The first half of an UPDATE rides exactly the read path of this post.
• The INSERT that failed on a duplicate key still shows Handler_write 1. As we saw above, ha_write_row() bumps the counter before calling write_row(). The failed statement is rolled back, so Handler_rollback 1 shows up alongside it. Handler_write is not "rows written" but "write attempts".
• The last line is the Handler_external_lock 0 mentioned in section 6.

What this post leaves out

• GROUP BY, aggregation and temporary tables — the end functions end_send_group(), end_write() and end_write_group(), the temporary-table stage for aggregation sub_select_postjoin_aggr() (sql/sql_select.cc) and AGGR_OP (sql/sql_select.h), internal temporary tables via create_tmp_table().
• Sorting — JOIN_TAB::sort_table() → create_sort_index() → filesort() (sql/filesort.cc), and rr_from_pointers·rr_unpack_from_buffer for reading sorted results (sql/records.cc).
• Range reads and MRR — rr_quick, QUICK_RANGE_SELECT::get_next() (sql/opt_range.cc), Multi-Range Read handler::multi_range_read_* (sql/multi_range_read.cc) and the Handler_mrr_* status variables.
• The rest of the join buffer — the functions BNLH, BKA and BKAH override, such as put_record() and get_next_candidate_for_match(), and what join_cache_level 1–8 mean (sql/sql_join_cache.cc), and JOIN_CACHE::set_constants(), which decides the buffer size.
• Pushing conditions down to the engine — Index Condition Pushdown push_index_cond() (sql/opt_index_cond_pushdown.cc, Handler_icp_*), the rowid filter Range_rowid_filter (sql/rowid_filter.cc), engine condition pushdown handler::cond_push().
• Executing outer joins and semi-joins, and routes that hand the whole query over — evaluate_null_complemented_join_record(), FirstMatch·LooseScan·DuplicateWeedout (sql/opt_subselect.cc), group_by_handler·select_handler·derived_handler (sql/group_by_handler.h, sql/select_handler.h, sql/derived_handler.h).
• The rest of the handler contract — handler::info() (how the optimizer asks for row counts and statistics, part 8), table_flags() (the engine announcing what it can do as bits, cached in cached_table_flags by ha_external_lock()), ha_innobase::table_flags() (ha_innodb.cc line 5201), bulk insert ha_start_bulk_insert().
• The execution functions for UPDATE and DELETE — Sql_cmd_update::update_single_table() (sql/sql_update.cc) and Sql_cmd_delete::delete_from_single_table() (sql/sql_delete.cc). The old names mysql_update() and mysql_delete() do not exist as functions at the reference commit (they survive only in a few old comments in engine sources).

Summary

What's next

The next post, part 10, InnoDB Part 1 — Pages, the Buffer Pool and B-trees, goes where this post stopped: inside row_search_mvcc() and row_insert_for_mysql(). We said ha_innobase::rnd_next() "walks the clustered index"; starting from ha_innodb.cc and row_prebuilt_t in row0mysql.cc (the bridge between the server layer and InnoDB), we follow how that index is a B-tree made of 16KB pages and how those pages live in the buffer pool.

The handler API resembles the extension structures from earlier series. In httpd part 6, modules register function pointers on hooks to join request processing; in MariaDB, engines implement handler's virtual functions to take over reading and writing rows. Both share the idea that "the core calls a fixed entrance without knowing who is on the other side"; the difference is that an httpd hook is a chain calling several modules in turn, while in MariaDB each table has exactly one engine. From the application side, the rows that Spring part 18's JdbcTemplate pulled out one at a time with ResultSet.next() are the packets this post's end_send() → select_send::send_data() sent one by one as soon as each row was found.