Master MySQL InnoDB: Indexes, Slow Queries, Pagination, Transactions & Logs

MySQL Logical Architecture

MySQL processes a client request through four logical layers:

Connection Layer : authenticates the client and manages connection pooling.

Server Layer : parses, validates, optimizes and executes SQL statements.

Storage Engine Layer : performs the actual data read/write. InnoDB is the default engine.

File System Layer : persists data and log files on disk.

InnoDB Physical Structure

Data is stored in a hierarchy:

Tablespace : the highest logical container, consisting of one or more data files.

Segment : a logical collection of pages (e.g., data segment, index segment).

Extent : a group of 64 consecutive 16 KB pages (default 1 MB).

Page : the smallest I/O unit (default 16 KB). A page contains:

File Header (global page info)

Page Header (page‑specific info)

Infimum and Supremum records (sentinel rows)

User Records (actual table rows)

Free Space (available space for new records)

Page Directory (offset index for records)

File Trailer (checksum)

When a row is inserted, InnoDB allocates space from the Free Space area, updates the Page Directory and links the new record into the singly‑linked list of user records. If a page becomes full, a page split creates a new page and redistributes records.

InnoDB Row Formats

InnoDB supports four row formats. The default for MySQL 5.7+ is Dynamic.

Compact : stores a variable‑length field list, a NULL‑bitmap, a record header and column values. Long column values (>768 bytes) are stored off‑page.

Redundant : older format that uses an offset table instead of a field list. It is rarely used after MySQL 5.0.

Dynamic : similar to Compact but stores only the first 768 bytes of long columns in the record; the rest is kept in overflow pages. This reduces the size of the primary‑key leaf node.

Compressed : Dynamic format with zlib compression applied to the whole page. It saves space at the cost of CPU.

Row format can be inspected or changed with:

SHOW TABLE STATUS LIKE 'table_name';
ALTER TABLE table_name ROW_FORMAT=Dynamic;

Index Types and Storage

InnoDB automatically creates a clustered primary key. The data rows are stored in B+Tree order by this primary key. A secondary index stores only the indexed columns plus the primary‑key value of the row. To retrieve a full row via a secondary index, MySQL must perform a “back‑table” lookup on the clustered index.

InnoDB vs. MyISAM

Transaction support : InnoDB is ACID‑compatible; MyISAM has none.

Locking : row‑level locks in InnoDB vs. table‑level locks in MyISAM.

Foreign keys : supported only by InnoDB.

Crash recovery : InnoDB uses redo logs; MyISAM requires manual repair.

Storage files : InnoDB stores data and indexes together in .ibd files; MyISAM uses separate .MYD (data) and .MYI (index) files.

Index type : clustered primary key in InnoDB vs. non‑clustered indexes in MyISAM.

How B+Tree Indexes Work

When a query uses an index, MySQL performs a binary search on the page directory to locate the appropriate slot, then follows the linked list of records within that slot. For a primary‑key lookup the record is found directly. For a secondary‑index lookup MySQL reads the leaf entry, obtains the primary‑key value, and then fetches the full row from the clustered index (the back‑table step).

Query Execution Plans

Full Table Scan : scans every row in the table. Worst performance for large tables.

Index Scan : walks the B+Tree to locate matching leaf nodes. Performance depends on selectivity and whether the query can be satisfied from the index alone.

Covering Index Scan : all columns required by the query are present in the index, so no back‑table lookup is needed. This is the fastest index‑based plan.

Back‑Table Query : secondary index lookup followed by a lookup in the clustered index. Adds extra I/O and is slower than a covering scan.

Typical performance order (best → worst): covering index scan, index scan, back‑table query, full table scan.

Slow‑Query Diagnosis

A query is considered slow when its execution time exceeds long_query_time (default 1 s). Common causes include full table scans, missing or ineffective indexes, large result sets, deep pagination, and complex joins.

Tools for locating the root cause:

Arthas : watch DispatcherServlet doDispatch '#cost>1000' to capture slow HTTP endpoints, then trace the controller and prepared statements.

SkyWalking : filter spans by duration, expand the MySQL/JDBC span, and view db.statement and db.execute_time.

Prometheus + mysqld_exporter : monitor mysql_global_status_slow_queries and set alerts.

MySQL Slow‑Query Log :

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

Analyze the log with mysqldumpslow or pt‑query‑digest :

mysqldumpslow -s t /var/log/mysql/mysql-slow.log
pt-query-digest /var/log/mysql/mysql-slow.log

Typical optimisation steps after identifying the slow statement: rewrite the SQL, add appropriate indexes, use covering indexes, or replace deep LIMIT offset, count with keyset pagination.

Transaction Processing in InnoDB

InnoDB implements ACID using two logs:

Undo Log : stores the before‑image of modified rows. It enables rollback and provides the historical versions needed for MVCC.

Redo Log : stores the after‑image of changes. It is flushed to disk on COMMIT to guarantee durability and is used for crash recovery.

During a transaction InnoDB writes to the redo log first, then to the undo log. COMMIT flushes the redo log; ROLLBACK applies the undo log to restore the original state.

MVCC (Multi‑Version Concurrency Control)

Each row contains two hidden fields: DB_TRX_ID: the ID of the transaction that last modified the row. DB_ROLL_PTR: a pointer to the undo log record that can reconstruct previous versions.

When a transaction reads data it creates a Read View that defines which transaction IDs are visible. The visibility rules differ by isolation level:

READ COMMITTED : a new read view is built for each statement; only rows committed before the statement are visible.

REPEATABLE READ (default): a single read view is kept for the whole transaction, guaranteeing that repeated reads see the same rows.

If the current version of a row is not visible, InnoDB follows DB_ROLL_PTR to older undo records until it finds a version that satisfies the read view.

Log Types and Their Roles

Undo Log (InnoDB engine): supports atomicity and MVCC; used for rollback.

Redo Log (InnoDB engine): ensures durability; used for crash recovery.

Binary Log (Binlog) (MySQL server): records logical changes (SQL statements or row events) for replication and point‑in‑time recovery.

Index Creation Guidelines

Create indexes only on large tables (e.g., > 100 k rows) that are queried frequently.

Index columns used in WHERE, ORDER BY, or GROUP BY.

Prefer high‑cardinality (high selectivity) columns; unique indexes are ideal.

For long VARCHAR columns use a prefix index (e.g., INDEX(col(10))).

Combine related columns into a composite index that follows the left‑most prefix rule.

Avoid excessive indexes – each index adds write overhead (record moves, page splits, page reclamation) and consumes storage.

Define columns as NOT NULL when possible; this helps the optimizer use the index.

When Indexes Fail – Ten Classic Scenarios

Using != or <> – the optimizer often cannot use the index.

Using OR where one operand lacks an index – the whole predicate may revert to a full scan.

Applying functions or expressions to indexed columns (e.g., YEAR(date)=2023 or id+1=100) – prevents index usage.

Leading wildcard in LIKE (e.g., LIKE '%abc') – index cannot be used; a trailing wildcard ( 'abc%') is index‑friendly.

Implicit type conversion (e.g., comparing a string column to a numeric literal) – may force a full scan.

Composite index not used with the leftmost prefix (e.g., index on (a,b,c) but query filters only b or c).

Query returns a large proportion of the table – optimizer may choose a full scan because it is cheaper than using the index.

Negation predicates such as NOT IN or NOT EXISTS – often lead to full scans.

Columns that allow NULL without proper handling – can affect index selectivity.

Stale statistics or corrupted indexes – the optimizer may make a wrong choice.

Deep Pagination Remedies

Traditional LIMIT offset, count becomes O(N) for large offsets because MySQL must scan and discard the preceding rows. Recommended alternatives:

Covering Index Scan : ensure the needed columns are in the index so the engine can satisfy the query without touching the table.

Keyset Pagination (a.k.a. “cursor” pagination): remember the last primary‑key value and query WHERE id > last_id LIMIT count.

Sub‑query : first fetch primary keys with a lightweight index, then join to the full table.

Table Partitioning : split a huge table into partitions so that only the relevant partition is scanned.

Cost of Indexes

Space: each index is a B+Tree stored in 16 KB pages; many indexes increase storage consumption.

Write overhead: INSERT/UPDATE/DELETE must modify every affected index, which may cause record moves, page splits and page reclamation.

Summary

MySQL’s layered architecture separates connection handling, SQL processing, storage engines and the file system. InnoDB stores data in a hierarchy of tablespace → segment → extent → page, with each page containing a header, record directory and user records. Row formats (Compact, Redundant, Dynamic, Compressed) affect how variable‑length columns are stored. InnoDB’s clustered primary key and secondary indexes form B+Tree structures; secondary indexes require a back‑table lookup to retrieve the full row.

Compared with MyISAM, InnoDB provides ACID transactions, row‑level locking, foreign‑key support and crash recovery via redo logs. Query execution plans range from full table scans to covering index scans, with the latter offering the best performance.

Slow queries are diagnosed using the slow‑query log, monitoring tools (Arthas, SkyWalking, Prometheus) and analysis utilities ( mysqldumpslow, pt‑query‑digest). Transaction durability relies on redo logs, while undo logs enable rollback and MVCC. MVCC uses hidden transaction IDs and undo pointers together with read views to provide consistent snapshots under READ COMMITTED and REPEATABLE READ isolation levels.

Effective indexing follows principles of selectivity, left‑most prefix usage, composite indexes, and avoiding excessive indexes. Common patterns that invalidate indexes include inequality operators, OR conditions, functions on columns, leading wildcards in LIKE, type casts, and improper use of composite indexes. For deep pagination, keyset pagination, covering indexes, sub‑queries or partitioning are preferred over large OFFSET values.