MySQL Tutorials - Herong's Tutorial Examples - v4.43, by Herong Yang
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:
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 |
So running the above query takes about the same amount of time as running two separate queries.
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
►InnoDB Storage Engine - Primary and Secondary Indexes
►Primary Key Index on InnoDB Table
InnoDB Primary Key Index on Large Table
"SELECT COUNT(*)" on InnoDB Table
Secondary Index on InnoDB Table
Performance of Index Range on InnoDB Table
Workaround on InnoDB "SELECT COUNT(*)" Problem
Performance Tuning and Optimization
Installing MySQL Server on Linux