InnoDB Storage Engine - Primary and Secondary Indexes
This chapter provides tutorial examples and notes on the InnoDB storage engine. Topics include InnoDB data rows and pages; the primary index and secondary indexes; full table scan; performance of index ranges; tablespace files; InnoDB initialization errors; 'ibd2sdi' command to extract SDI.
These sections are omitted from this Web preview version. To view the full content,
see information on how to obtain the full version this book.
Primary Key Index on InnoDB Table
InnoDB Primary Key Index on Large Table
"SELECT COUNT(*)" on InnoDB Table
InnoDB Table with Long Rows
Secondary Index on InnoDB Table
Unique Index on InnoDB Table
Performance of Index Range on InnoDB Table
Workaround on InnoDB "SELECT COUNT(*)" Problem
InnoDB Tablespace Files
Moving InnoDB Data Files or Directories
Exporting and Importing InnoDB Tablespace Files
InnoDB Initialization Fails to Finish
ibd2sdi — InnoDB SDI Extraction Utility
- The default InnoDB storage page size is 16,384 bytes = 16 KB.
- The maximum row length is limited by the storage page size of 16 KB.
- InnoDB uses a buffer pool to cache index pages and data pages.
- The default InnoDB buffer size is 16,777,216 bytes = 16 MB.
- Avoid using "SELECT COUNT(*)" statements on InnoDB tables.
They are very expensive!
- Data pages of an secondary index store pointers from secondary index values
to primary index values.
- Querying data rows based on a secondary index require 2 rounds of
searches: one searching primary index value in the secondary index data structure,
and one searching data rows in the primary index structure.
- Unique secondary index performs better than non-unique secondary index.
- Performance of querying data rows using index ranges is proportional to the range size.
- "SELECT COUNT(sid)" using unique secondary index "sid" performs much better
than "SELECT COUNT(*)" statement.
- InnoDB uses 5 types of tablespace files:
System Tablespace, innodb_file_per_table Tablespace, Generate Tablespace,
Undo Tablespaces, and Temporary Tablespaces.
- InnoDB tablespace files are self-contained.
You can move them to different locations.
- innodb_force_recovery=n can be used to
to start the MySQL server without background processes to repair data.
- 'ibd2sdi' command allows you to extract SDI (Serialized Dictionary Information)
from InnoDB Tablespace files.
- SDI includes definitions of tables and their indexes stored
in the InnoDB Tablespace file.
Table of Contents
About This Book
Introduction of SQL
MySQL Introduction and Installation
Introduction of MySQL Programs
PHP Programs and MySQL Server
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Datatypes and Data Literals
Operations and Expressions
Character Strings and Bit Strings
Commonly Used Functions
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
Locks Used in MySQL
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
MySQL Server Administration
Storage Engines in MySQL Server
►InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Bulk Changes on Large Tables
MySQL Server on macOS
Installing MySQL Server on Linux
Connection, Performance and Second Instance on Linux
Full Version in PDF/EPUB