1. The "culprit" of index failure TOP 8
1. Function operation causes index failure
Error cases：
-- Using functions on index columns results in full table scan SELECT * FROM orders WHERE DATE(create_time) = '2023-01-01'; -- even thoughcreate_timeIndexes will also fail
Execution plan：
type: ALL (Full table scan) key: NULL (Unused index)
Java optimization solution：
// Use scope query alternative function operations @Query("SELECT o FROM Order o WHERE >= :startDate AND < :endDate")
List<Order> findByDateRange(@Param("startDate") LocalDateTime start,
@Param("endDate") LocalDateTime end);
2. Implicit type conversion
Error cases：
-- Comparison of strings and numbers causes index failure SELECT * FROM users WHERE phone = 13800138000; -- phoneyesVARCHARtype
Execution plan：
type: ALL (Full table scan) key: NULL (Unused index)
Java optimization solution：
// Make sure the parameter type is consistent with the database field type @Query("SELECT u FROM User u WHERE = :phone")
User findByPhone(@Param("phone") String phone); // Use String instead of Long3. Abuse of OR conditions
Error cases：
-- ORConditions cause index failure SELECT * FROM products WHERE category_id = 1 OR price > 1000; -- even thoughcategory_idIndexes will also fail
Execution plan：
type: ALL (Full table scan) key: NULL (Unused index)
Java optimization solution：
//Use UNION ALL instead of OR conditions @Query("SELECT p FROM Product p WHERE = :categoryId " +
"UNION ALL " +
"SELECT p FROM Product p WHERE > :price AND != :categoryId")
List<Product> findByCategoryOrPrice(@Param("categoryId") Long categoryId,
@Param("price") BigDecimal price);
4. NOT IN/!=/<> Operation
Error cases：
-- NOT INCauses index failure SELECT * FROM orders WHERE status NOT IN (1, 2, 3); -- even thoughstatusIndexes will also fail
Execution plan：
type: ALL (Full table scan) key: NULL (Unused index)
Java optimization solution：
// Use LEFT JOIN + IS NULL instead of NOT IN @Query("SELECT o FROM Order o " +
"LEFT JOIN OrderStatus os ON = AND IN (1,2,3) " +
"WHERE IS NULL")
List<Order> findByStatusNotIn(@Param("statusList") List<Integer> statusList);
5. Composite index violates the leftmost prefix
Error cases：
-- Create a composite index (user_id, status, create_time) ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time); -- The query does not use the leftmost prefix, causing the index to fail SELECT * FROM orders WHERE status = 1 AND create_time > '2023-01-01'; -- Lackuser_idcondition
Execution plan：
type: ALL (Full table scan) key: NULL (Unused index)
Java optimization solution：
// Make sure the query condition contains the leftmost prefix of the composite index @Query("SELECT o FROM Order o WHERE = :userId AND = :status " +
"AND > :startTime")
List<Order> findByUserStatusAndTime(@Param("userId") Long userId,
@Param("status") Integer status,
@Param("startTime") LocalDateTime startTime);
6. LIKE query starts with a wildcard
Error cases：
-- LIKE '%keyword%'Causes index failure SELECT * FROM articles WHERE title LIKE '%MySQL%'; -- even thoughtitleIndexes will also fail
Execution plan：
type: ALL (Full table scan) key: NULL (Unused index)
Java optimization solution：
// Use full text index instead of LIKE fuzzy query @Entity
@Table(indexes = {
@Index(name = "idx_title_fulltext", columnList = "title",
type = ) // MySQL 5.6+ support
})
public class Article {
// ...
}
// Query example @Query(value = "SELECT a FROM Article a WHERE MATCH() AGAINST(:keyword IN BOOLEAN MODE)",
nativeQuery = true)
List<Article> searchByKeyword(@Param("keyword") String keyword);
7. Index columns participate in calculation
Error cases：
-- Index columns participate in calculations resulting in failure SELECT * FROM users WHERE YEAR(birthday) = 1990; -- even thoughbirthdayIndexes will also fail
Execution plan：
type: ALL (Full table scan) key: NULL (Unused index)
Java optimization solution：
// Move the calculation logic to the Java side or use scope query @Query("SELECT u FROM User u WHERE >= :start AND < :end")
List<User> findByBirthYear(@Param("start") LocalDate start,
@Param("end") LocalDate end);
// Call example
LocalDate start = (1990, 1, 1);
LocalDate end = (1991, 1, 1);
List<User> users = (start, end);
8. Uneven data distribution leads to index failure
Error cases：
-- Gender field（Very low distinction）Even if there is an index, it will be invalid SELECT * FROM users WHERE gender = 'M'; -- Assuming that the ratio of men and women is close1:1
Execution plan：
type: ALL (Full table scan) key: NULL (优化器选择Full table scan)
Java optimization solution：
// Avoid indexing of low-distinguishing fields
// Or use other high-discrimination conditions instead
@Query("SELECT u FROM User u WHERE = :gender AND = :status")
List<User> findByGenderAndStatus(@Param("gender") String gender,
@Param("status") Integer status);
2. The "diagnostic toolbox" for index failure
2.1 Deep Analysis of EXPLAIN Command
EXPLAIN SELECT * FROM orders WHERE user_id = 12345;
Key field description：
-
type: Access type (ALL=full table scan, index=index scan, range=range scan, ref=index reference) -
key: The actual index used -
rows: Estimate the number of rows to be checked -
Extra: Additional information (Using index=overrides index, Using where=return to the table)
2.2 Slow query monitoring in Java
// Spring Boot Configuration Example () -test-query=SELECT 1
.generate_statistics=true
.LOG_QUERIES_SLOWER_THAN_MS=100
// Custom interceptor record slow query
@Component
public class SlowQueryInterceptor implements HandlerInterceptor {
@Override
public boolean preHandle(HttpServletRequest request,
HttpServletResponse response,
Object handler) {
long startTime = ();
("startTime", startTime);
return true;
}
@Override
public void afterCompletion(HttpServletRequest request,
HttpServletResponse response,
Object handler,
Exception ex) {
long startTime = (Long) ("startTime");
long duration = () - startTime;
if (duration > 500) { // Record queries over 500ms
("Slow query detected: {}ms, URL: {}",
duration, ());
}
}
}
3. Index optimization best practices
3.1 Three principles of index design
The principle of selectivity: Priority is given to indexes with high distinction (such as user ID, order number)
Composite index order: Put the high-frequency query conditions in front, and put the range query conditions in the last
-- Correct example：Equivalent query first，Post-scope query ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);
- Override index optimization: Let the query get data completely through the index
-- Before optimization SELECT user_id, order_no FROM orders WHERE user_id = 12345; -- After optimization（Add toorder_noTo compound index） ALTER TABLE orders ADD INDEX idx_user_order (user_id, order_no);
3.2 Index protection in Java code
// Force indexing using @Query annotation (MySQL 5.7+)
@Query(value = "SELECT * FROM orders FORCE INDEX(idx_user_status_time) " +
"WHERE user_id = :userId AND status = :status",
nativeQuery = true)
List<Order> findByUserIdAndStatus(@Param("userId") Long userId,
@Param("status") Integer status);
// Paging query optimization (avoid large offsets)
public interface OrderRepository extends JpaRepository<Order, Long> {
@Query("SELECT o FROM Order o WHERE = :userId " +
"AND ( < :lastCreateTime OR " +
"( = :lastCreateTime AND < :lastId)) " +
"ORDER BY DESC, DESC")
List<Order> findAfterCursor(@Param("userId") Long userId,
@Param("lastCreateTime") Date lastCreateTime,
@Param("lastId") Long lastId,
Pageable pageable);
}
4. Summary and pit avoidance guide
4.1 "Three-pronged" diagnostic method for index failure
- Execution plan analysis: Confirm whether the expected index is used through EXPLAIN
- Data type check: Make sure that the Java parameter type matches the database field type
- SQL rewrite test: Equivalent rewrite of suspicious SQL and compare performance
4.2 Common Mistakes
- The more indexes, the better (causes a degradation in writing performance)
- Indexing all query conditions (wastes storage space)
- Relying on the ORM framework to automatically generate SQL (may generate inefficient SQL)
4.3 Ultimate Suggestions
"Diagnose first, then optimize" principle: Positioning problems through slow query logs, EXPLAIN and performance monitoring tools, and selecting the optimal indexing solution based on business scenarios.
Through the systematic explanation of this article, Java developers can master the core causes and solutions for MySQL index failure. In actual projects, it is recommended to verify the optimization effect in combination with A/B tests to bring the system performance to a new level!
The above is the detailed content of the eight common scenarios and solutions for MySQL index failure. For more information about MySQL index failure scenarios, please pay attention to my other related articles!