Optimizing SpringBoot MySQL Indexes: From Slow Query Logs to InnoDB Explain Analysis

Performance bottlenecks in production are usually caused by inefficient MySQL queries rather than missing caches. The recommended optimization order is: first tune SQL statements and indexes, then consider caching, and finally apply architectural changes such as sharding or read/write splitting.

1. Enable MySQL Slow Query Log in SpringBoot

Locate slow SQL statements by turning on MySQL’s built‑in slow‑query log.

Method 1 – Dynamic configuration (no MySQL restart)

-- Enable slow query log
SET GLOBAL slow_query_log = ON;
-- Set threshold to 0.5 seconds
SET GLOBAL long_query_time = 0.5;
-- Record queries that do not use indexes
SET GLOBAL log_queries_not_using_indexes = ON;
-- Set log file path (ensure MySQL can write)
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
-- Output to file and table
SET GLOBAL log_output = 'FILE,TABLE';
-- Verify settings
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

After changing GLOBAL variables you must reconnect (restart SpringBoot, Navicat, etc.) for the new settings to take effect.

Method 2 – Permanent configuration file

Edit the MySQL configuration file and restart the service.

Linux (CentOS, Ubuntu): /etc/my.cnf or /etc/mysql/my.cnf Windows: MySQL安装目录\my.ini Docker: mount a custom config or edit /etc/my.cnf inside the container

[mysqld]
slow_query_log = ON
long_query_time = 0.5
slow_query_log_file = /var/lib/mysql/slow.log
log_queries_not_using_indexes = ON
log_output = FILE,TABLE
ignore_db_dirs = mysql,information_schema,performance_schema,sys
log_slow_admin_statements = ON
log_slow_slave_statements = ON

2. Analyzing Slow Queries

Use command‑line tools to extract the most time‑consuming statements.

Raw log inspection

# Real‑time view
tail -f /var/lib/mysql/slow.log
# First 10 lines
head -n 10 /var/lib/mysql/slow.log
# Count slow queries
grep -c "Query_time" /var/lib/mysql/slow.log
# Find queries > 1 s
grep "Query_time>1" /var/lib/mysql/slow.log

mysqldumpslow

# Top 10 by execution time
mysqldumpslow -s t -n 10 /var/lib/mysql/slow.log
# Top 10 by frequency
mysqldumpslow -s c -n 10 /var/lib/mysql/slow.log
# By lock time
mysqldumpslow -s l -n 10 /var/lib/mysql/slow.log
# Filter by database
mysqldumpslow -d springboot_demo /var/lib/mysql/slow.log
# Detailed output
mysqldumpslow -v /var/lib/mysql/slow.log

pt‑query‑digest (Percona Toolkit)

# Analyze and print to screen
pt-query-digest /var/lib/mysql/slow.log
# Save report to file
pt-query-digest /var/lib/mysql/slow.log > slow_query_analysis.log

For production prefer command‑line tools to avoid extra load on the database.

3. Using EXPLAIN to Diagnose SQL

Prefix a statement with EXPLAIN to view the optimizer’s execution plan. The result contains 12 columns; the most important ones are described below.

Basic usage

EXPLAIN SELECT id, name, age FROM user WHERE age > 20;
EXPLAIN SELECT u.id, u.name, o.order_no FROM user u LEFT JOIN `order` o ON u.id = o.user_id WHERE u.age > 20;
EXPLAIN UPDATE user SET name = '李四' WHERE id = 1;

Key fields

id : execution order of query blocks.

select_type : SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, etc.

table : table involved in the block.

type : access method – system, const, eq_ref, ref, range, index, ALL. Lower is better.

possible_keys : indexes that could be used.

key : index actually used (NULL means full table scan).

key_len : length of the used index in bytes.

ref : column or constant matched to the index.

rows : estimated rows scanned.

Extra : additional info such as Using where, Using index, Using filesort, Using temporary.

Practical case

Slow query:

SELECT * FROM product WHERE category_id = 10 AND price > 100;
-- 800 000 rows, execution time 1.2 s

EXPLAIN shows type=ALL, key=NULL, rows=800000, Extra=Using where. The optimizer discards the existing idx_category_id because the query also filters on price, which is not indexed, making a full scan cheaper.

Optimization : create a composite index idx_category_price (category_id, price). After rebuilding, EXPLAIN reports type=range, key=idx_category_price, rows≈5000, Extra=Using index condition; Using where. Execution time drops from 1.2 s to ~20 ms.

4. InnoDB B+Tree Index Fundamentals

Balanced height (typically 3‑4 levels) → only a few disk I/Os per lookup.

Leaf nodes are linked in order, enabling efficient range scans.

Non‑leaf nodes store only index keys; leaf nodes store either the full row (clustered index) or the primary‑key value (secondary index).

Ordered keys allow MySQL to satisfy ORDER BY or GROUP BY without extra sorting.

Clustered (primary) index

Each table has one clustered index, usually the primary key. The leaf nodes contain the entire row, so a lookup by primary key returns the row directly without a “back‑lookup”.

Secondary (non‑clustered) index

All other indexes store the indexed columns plus the primary‑key value. To retrieve other columns MySQL must first read the secondary index to get the primary key, then fetch the row from the clustered index – this extra step is called a “back‑lookup”.

Covering index

If a secondary index contains every column required by the SELECT, MySQL can satisfy the query using only the index, eliminating the back‑lookup. Example:

CREATE INDEX idx_category_name_price ON product (category_id, name, price);
-- Query selecting id, name, price where category_id=10 uses the index only.

Guidelines:

Avoid SELECT * when you want a covering index.

Place filter columns (WHERE) first in the index, then the columns you need to return.

Do not include large TEXT or BLOB columns in a covering index.

5. Common Reasons Indexes Fail

Functions or expressions on indexed columns (e.g., WHERE LEFT(name,3)='ABC') break the ordered key, forcing a full scan.

Low‑cardinality columns (status, gender) provide poor selectivity; the optimizer may skip the index.

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

6. InnoDB Index Types and Back‑Table Queries

InnoDB stores indexes as B+ trees. A secondary index’s leaf node holds the primary‑key value, not the full row. Querying a secondary index therefore requires a second lookup on the clustered index (back‑table). A covering index avoids this extra lookup.

Back‑table example

SELECT * FROM product WHERE category_id = 10;
-- Uses secondary index idx_category_id → obtains primary keys → back‑lookup each row.

Covering index example

SELECT id, name, price FROM product WHERE category_id = 10;
-- Create covering index
CREATE INDEX idx_category_name_price ON product (category_id, name, price);
-- Query can be satisfied from the index alone; EXPLAIN shows Extra=Using index.

7. Index Failure Root Causes (Bottom‑Up)

Ordered‑key mismatch: functions, arithmetic, or left‑most‑prefix violations prevent the B+ tree from being used.

Poor selectivity: low‑cardinality columns make full scan cheaper than index scan.

Improper composite index order: if the query does not reference the leftmost column, the index cannot be used.

Understanding these mechanisms allows you to design effective indexes, avoid hidden performance traps, and achieve dramatic query‑time reductions.