MySQL performance optimization guide for Spring Boot/JPA. Use when reviewing database code, discussing index design, query optimization, N+1 problems, JPA/Hibernate tuning, or analyzing EXPLAIN plans. Complements /mysql-performance and /optimize-query commands.
This skill inherits all available tools. When active, it can use any tool Claude has access to.
references/index-design.mdreferences/jpa-hibernate-tuning.mdreferences/query-patterns.mdIMPORTANT: All output must be in Traditional Chinese.
Rule: Equality first, Range last, ORDER BY in between
-- Query pattern
SELECT * FROM orders
WHERE customer_id = ? -- Equality
AND status = ? -- Equality
AND created_at > ? -- Range
ORDER BY total DESC;
-- Optimal index
CREATE INDEX idx_orders_customer_status_created_total
ON orders (customer_id, status, created_at, total);
-- ↑ equality ↑ equality ↑ range ↑ for sorting
Index stops working after range condition:
-- This index: (a, b, c, d)
WHERE a = 1 AND b > 10 AND c = 5
-- ✓ uses a ✓ uses b ✗ c not used (after range)
Include all columns in SELECT to avoid table lookup:
-- Query
SELECT order_id, total, status FROM orders
WHERE customer_id = ? AND created_at > ?;
-- Covering index (includes all needed columns)
CREATE INDEX idx_covering
ON orders (customer_id, created_at, order_id, total, status);
-- EXPLAIN shows "Using index" = covering index used
High selectivity = more distinct values = better index
-- Check selectivity
SELECT
COUNT(DISTINCT customer_id) / COUNT(*) AS customer_selectivity,
COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM orders;
-- Result: customer_id = 0.85, status = 0.001
-- customer_id is better for leading column
The Problem:
// BAD - N+1 queries
List<Order> orders = orderRepository.findByCustomerId(customerId);
for (Order order : orders) {
List<OrderItem> items = order.getItems(); // Lazy load = 1 query per order!
}
// Total: 1 + N queries
Solutions:
// Solution 1: JOIN FETCH (JPQL)
@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.customerId = :customerId")
List<Order> findByCustomerIdWithItems(@Param("customerId") String customerId);
// Solution 2: @EntityGraph
@EntityGraph(attributePaths = {"items", "items.product"})
List<Order> findByCustomerId(String customerId);
// Solution 3: Batch fetching (application.yml)
spring:
jpa:
properties:
hibernate:
default_batch_fetch_size: 100
Problem: OFFSET becomes slow with large pages
-- Slow for page 10000
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 200000;
-- MySQL must scan 200,020 rows!
Solution: Keyset Pagination
// Instead of page number, use last seen ID
@Query("SELECT o FROM Order o WHERE o.id > :lastId ORDER BY o.id LIMIT :size")
List<Order> findNextPage(@Param("lastId") Long lastId, @Param("size") int size);
// Usage
Long lastId = 0L;
List<Order> page;
do {
page = repository.findNextPage(lastId, 20);
process(page);
lastId = page.isEmpty() ? lastId : page.get(page.size() - 1).getId();
} while (!page.isEmpty());
-- Slow: exact count
SELECT COUNT(*) FROM orders WHERE status = 'PENDING';
-- Fast: approximate count (for UI "1000+ results")
SELECT COUNT(*) FROM orders WHERE status = 'PENDING' LIMIT 1001;
-- If returns 1001, display "1000+"
-- Subquery (often slower)
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE region = 'ASIA');
-- JOIN (often faster, check EXPLAIN)
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'ASIA';
-- EXISTS (good for checking existence)
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
// Default: LAZY for collections, EAGER for single
@Entity
public class Order {
@ManyToOne(fetch = FetchType.LAZY) // Single entity - consider LAZY
private Customer customer;
@OneToMany(fetch = FetchType.LAZY) // Collection - always LAZY
private List<OrderItem> items;
}
# application.yml
spring:
jpa:
properties:
hibernate:
jdbc:
batch_size: 50
batch_versioned_data: true
order_inserts: true
order_updates: true
// Batch insert
@Transactional
public void saveAll(List<Order> orders) {
int batchSize = 50;
for (int i = 0; i < orders.size(); i++) {
entityManager.persist(orders.get(i));
if (i % batchSize == 0) {
entityManager.flush();
entityManager.clear();
}
}
}
spring:
datasource:
hikari:
maximum-pool-size: 10 # CPU cores * 2 + disk spindles
minimum-idle: 5
connection-timeout: 30000 # 30 seconds
idle-timeout: 600000 # 10 minutes
max-lifetime: 1800000 # 30 minutes
EXPLAIN SELECT * FROM orders WHERE customer_id = ? AND status = ?;
| Column | Good | Bad |
|---|---|---|
| type | const, eq_ref, ref | ALL, index |
| rows | Low number | High number |
| Extra | Using index | Using filesort, Using temporary |
const - Single row (primary key lookup)eq_ref - One row per join (unique index)ref - Multiple rows (non-unique index)range - Index range scanindex - Full index scan (better than ALL)ALL - Full table scan (Bad!)Using filesort - Sorting without indexUsing temporary - Creating temp tableUsing where with high rows - Filter after scan| Issue | Detection | Solution |
|---|---|---|
| N+1 Query | Multiple SELECT in logs for one request | JOIN FETCH, @EntityGraph, batch_fetch_size |
| Full Table Scan | EXPLAIN type = ALL | Add appropriate index |
| Large OFFSET | LIMIT x OFFSET large_number | Keyset pagination |
| SELECT * | Fetching unused columns | Select only needed columns |
| Missing Index | Slow query log, EXPLAIN | Analyze query pattern, add index |
| Cartesian Join | Missing JOIN condition | Add proper ON clause |
| OR on different columns | Each OR branch = separate scan | UNION or redesign |
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries > 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- Find missing indexes
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
-- Find queries without indexes
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC LIMIT 10;
@QueryHints(@QueryHint(name = "org.hibernate.readOnly", value = "true"))
List<Order> findByStatus(OrderStatus status);
For detailed guidance: