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 /optimize-query command.
IMPORTANT: All output must be in Traditional Chinese.
Rule: Equality first, Range last, ORDER BY in between
-- Query: customer_id = equality, status = equality, created_at > = range
-- Optimal index: equality columns first, range column last
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);
Index stops working after range condition: (a, b, c, d) with WHERE a=1 AND b>10 AND c=5 — only a and b are used. c is skipped because b is a range.
Include all SELECT columns in the index to avoid table lookup. EXPLAIN shows Using index when covering index is hit.
High selectivity (many distinct values) = better index candidate.
SELECT COUNT(DISTINCT customer_id) / COUNT(*) AS selectivity FROM orders;
-- 0.85 = good candidate, 0.001 = poor candidate
// BAD - N+1 queries (1 + N)
List<Order> orders = orderRepository.findByCustomerId(customerId);
for (Order order : orders) {
List<OrderItem> items = order.getItems(); // Lazy load = 1 query per order!
}
Solutions:
// Solution 1: JOIN FETCH
@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
OFFSET scans all preceding rows — becomes slow with large offsets.
// BAD: OFFSET 200000 = MySQL scans 200,020 rows
Page<Order> findAll(Pageable pageable);
// GOOD: Keyset pagination - O(log n) regardless of page
@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);
Always LAZY for collections. Consider LAZY for @ManyToOne to avoid joins when not needed.
@Entity
public class Order {
@ManyToOne(fetch = FetchType.LAZY) // Single entity - LAZY to avoid unnecessary join
private Customer customer;
@OneToMany(fetch = FetchType.LAZY) // Collection - ALWAYS LAZY
private List<OrderItem> items;
}
Enable in application.yml:
spring.jpa.properties.hibernate.jdbc.batch_size: 50
spring.jpa.properties.hibernate.order_inserts: true
spring.jpa.properties.hibernate.order_updates: true
See references/jpa-hibernate-tuning.md for batch insert code template and connection pool config.
Formula: CPU cores * 2 + disk spindles (e.g., 4-core server = pool size 9).
| Column | Good | Bad |
|---|---|---|
| type | const, eq_ref, ref | ALL, index |
| rows | Low | High |
| Extra | Using index | Using filesort, Using temporary |
Type values best to worst: const → eq_ref → ref → range → index → ALL
Red flags: Using filesort (needs index for ORDER BY), Using temporary (GROUP BY without index)
| Issue | Detection | Solution |
|---|---|---|
| N+1 Query | Multiple SELECTs per request in logs | 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 / projection |
| Missing Index | Slow query log, EXPLAIN | Analyze query pattern, add index |
| OR on different columns | Each OR = separate scan | UNION or redesign |
HHH000104 警告，Hibernate 先載入全表再記憶體分頁。改用子查詢取 ID 清單 + JOIN FETCH，或用 @QueryHints + CountQueryEXPLAIN ANALYZE 看真實執行數據，兩者差距可達 10 倍default_batch_fetch_size: 1000 會產生巨大 IN(...) clause。max_allowed_packet 可能不足，建議 50-200WITH 每次引用都重新執行（不像 PostgreSQL）。需要多次引用時改用臨時表