Primary Key Index on InnoDB Table

MySQL Tutorials - Herong's Tutorial Examples

∟InnoDB Storage Engine - Primary and Secondary Indexes

∟Primary Key Index on InnoDB Table

This section provides a tutorial example on how to create an InnoDB table with a single index page, and see relations between the index page and table data pages.

In previous tutorials, we have learned enough on how the clustered index data structure is used to store InnoDB tables in physical files. We also learned how to gather statistical information related to InnoDB table.

Now it's time to confirm what we have learned with InnoDB table examples. Let's start on an InnoDB table with a simple primary key index.

1. Create a simple InnoDB table example with two columns: "id" as the primary key and "subject" with a fixed length of 212 bytes. The total data length is 216 bytes.

mysql> CREATE TABLE Head (ID INT, subject CHAR(212), PRIMARY KEY (ID))
    -> ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Add 6400 rows to the table.

mysql> DELIMITER '/';
mysql> CREATE PROCEDURE InsertHead(IN X INT, IN Y INT)
    -> BEGIN
    ->   DECLARE I INT;
    ->   SET I = X;
    ->   WHILE I <= Y DO
    ->     INSERT INTO Head VALUES (I, 'Hello world!');
    ->     SET I = I + 1;
    ->   END WHILE;
    -> END/
ERROR 1304 (42000): PROCEDURE InsertHead already exists
mysql> DELIMITER ';'/

mysql> CALL InsertHead(1, 6400);

3. Look at the InnoDB table's stats by looking at the InnoDB buffer, since the entire table should be in the buffer on a quiet server.

mysql> show variables like 'innodb_page_size';
  Variable_name     Value
  ----------------  -----
  innodb_page_size  16384

mysql> select * from sys.schema_table_statistics_with_buffer
    -> where table_name='Head';
  | innodb_buffer_pages | io_read_requests | io_write_requests |
  +---------------------+------------------+-------------------+
  |                 102 |                0 |               114 |

mysql> select * from information_schema.innodb_buffer_page
    -> where table_name rlike 'Head';
  | page_number | page_type | number_records | data_size |
  +-------------+-----------+----------------+-----------+
  |          75 | INDEX     |             64 |     15040 |
  |         107 | INDEX     |             64 |     15040 |
  ...
  |           5 | INDEX     |             32 |      7520 |
  |          34 | INDEX     |             64 |     15040 |
  |          30 | INDEX     |             64 |     15040 |
  ...
  |         132 | INDEX     |             32 |      7520 |
  |           4 | INDEX     |            101 |      1414 |
  |          15 | INDEX     |             64 |     15040 |
  |          13 | INDEX     |             64 |     15040 |
  ...
  |          83 | INDEX     |             64 |     15040 |
  |          26 | INDEX     |             64 |     15040 |

4. Verify index and data page counts:

Number of data pages: 101 - With 99 pages holding 64 rows in each page, and 2 pages holding 32 rows in each page. Based on the data size in each page, I see that each row is using 235 bytes with 216 bytes for data columns and 9 bytes as overhead.
Number of index pages: 1 - It holds 101 records with 14 bytes in each record. Each record is mapping a data page to a starting value of the primary key. So it has two columns: a 4-byte primary key and 10-byte page address.

5. Do not look at the page estimates in the mysql.innodb_index_stats table. They are inaccurate and misleading.

mysql> select * from mysql.innodb_index_stats where table_name='Head';
  index_name  stat_name     stat_value  stat_description
  ----------  ------------  ----------  ---------------------------------
  PRIMARY     n_diff_pfx01        6363  id
  PRIMARY     n_leaf_pages         101  Number of leaf pages in the index
  PRIMARY     size                 161  Number of pages in the index

6. Retrieve some rows and see how many pages InnoDB is reading. This needs to be performed on a quiet MySQL server with nothing else is running.

mysql> show status like 'Innodb_buffer_pool_read_requests';
  | Innodb_buffer_pool_read_requests | 34924940 |

mysql> select * from Head where id=1;
  |  1 | Hello world! |

mysql> show status like 'Innodb_buffer_pool_read_requests';
  | Innodb_buffer_pool_read_requests | 34924942 |

mysql> select * from Head where id=1000;
  | 1000 | Hello world! |

mysql> show status like 'Innodb_buffer_pool_read_requests';
  | Innodb_buffer_pool_read_requests | 34924944 |

So those counts confirm that retrieving a row with a given primary key will read 2 pages, the only index page and the target data page pointed by the index record.

What will happen if we retrieve 2 rows? Is InnoDB smart enough to read the index page only once? The answer is no.

mysql> show status like 'Innodb_buffer_pool_read_requests';
  | Innodb_buffer_pool_read_requests | 35071958 |

mysql> select * from Head where id in (1, 1000);
  |    1 | Hello world! |
  | 1000 | Hello world! |

mysql> show status like 'Innodb_buffer_pool_read_requests';
  | Innodb_buffer_pool_read_requests | 35071962 |