simon

1. Problem Background#

A certain online interface has a response time of over 50 seconds, frequently timing out, severely affecting user experience. The testing environment runs smoothly, prompting an investigation.

2. Preliminary Investigation Process#

✅ 1. Link tracing to identify slow interface

Using a link tracing system (such as SkyWalking), it was quickly located that a certain database query was slowing down the interface response.

✅ 2. Log + SQL interceptor to print complete SQL

Combined with MyBatis's SQL interceptor, the complete SQL with parameters concatenated was obtained:

SELECT MAX(t.id)
FROM user_log t
WHERE t.deleted_at = '1970-01-01 00:00:00'
  AND LENGTH(t.platform_id) > 0
  AND t.platform_id != '0'
  AND t.platform_type = 'MOBILE'
  AND t.nickname LIKE CONCAT('%', 'abc', '%')
GROUP BY t.platform_id
LIMIT 20;

✅ 3. Execution plan analysis (EXPLAIN)

It was found that the production environment incorrectly hit an index starting with deleted_at:

idx_deletedat_memberaccountid_updatedat
→ Scanned rows: 2,126,736
→ filesort + temporary table
→ Total time: 50 seconds

In contrast, the testing environment used a more appropriate index idx_platformtype_deletedat_platformid_nickname, executing in just a few hundred milliseconds.

3. Root Cause of the Problem#

The optimizer in the production environment misjudged the optimal index, leading to a full table scan + temporary table sorting.

The reasons are as follows:

💡 Inference Result: The excessive matching data for deleted_at in the production environment led the optimizer to incorrectly judge it as a strongly filtering field, choosing a sub-optimal index.

4. Final Optimization Plan: Combining Two Strategies#

✅ Plan One: Conditional USE INDEX

Dynamically injected in MyBatis XML:

<if test="ro.platformType != null and ro.partialNickname != null and ro.talentPlatformIdList == null">
    USE INDEX (idx_platformtype_deletedat_platformid_nickname)
</if>

Effect:
• Avoids the optimizer choosing incorrectly when querying nickname lists and when there is no precise range for platform_id;
• Restrictive injection ensures that the index is enforced only under specific combinations, avoiding a one-size-fits-all approach.

✅ Plan Two: Range Optimization Replacing Function Filtering

Replaces the original redundant judgment logic:

-- Original

AND LENGTH(t.platform_id) > 0 AND t.platform_id != '0'

-- Optimized

AND t.platform_id > '0'

Advantages:

• length and != will not use the index, leading to a full table scan; changing to > allows for index usage.
• Avoids function interference with fields, increasing the probability of index usage;
• Concise and efficient, semantically equivalent.

5. Optimization Effect#

6. Subsequent Optimizations#

The following are further optional optimization directions summarized based on the current plan, suitable for future evolution considerations. Currently, no further optimizations will be made due to low ROI.

1. ✅ Use full-text index to optimize fuzzy search

• Current LIKE '%xxx%' queries cannot use B-Tree indexes.
• A FULLTEXT index can be used (suitable for VARCHAR or TEXT fields in InnoDB) to improve fuzzy search performance:

ALTER TABLE some_table ADD FULLTEXT INDEX idx_nickname_ft (nickname);

• Rewrite the query as:

SELECT ... FROM some_table
WHERE MATCH(nickname) AGAINST('+abc' IN BOOLEAN MODE);

2. ✅ Split SQL to handle OR conditions

• The original query contains:

WHERE platform_id IN (...) OR home_link IN (...)

• MySQL typically cannot utilize two indexes simultaneously when encountering OR, easily triggering a full table scan.
• It is recommended to split the SQL into two subqueries and then UNION ALL:

(SELECT ... FROM ... WHERE platform_id IN (...))
UNION ALL
(SELECT ... FROM ... WHERE home_link IN (...))

3. ✅ Design multiple composite indexes to handle different parameter combinations

• Design dedicated composite indexes for different query scenarios (e.g., whether it includes platform_type, whether it is a fuzzy search, whether there is a notification_id);
• Use MyBatis conditions + USE INDEX to dynamically control the path, ensuring the optimal execution plan is always followed.

4. ✅ Improve the optimizer's understanding of data distribution through histograms (MySQL 8+)

• If ANALYZE TABLE is still insufficient to accurately reflect the cardinality distribution of a certain field, consider using:

ANALYZE TABLE some_table UPDATE HISTOGRAM ON platform_type WITH 256 BUCKETS;

• This enhances the accuracy of the optimizer's index cost assessment.

7. Summary and Insights#

🎯 Optimization Keywords:
• USE INDEX conditional control
• Function filtering replaced by range judgment
• Analyze data distribution differences
• Focus on optimizer behavior
• Strong guidance under precise conditions to avoid global strong binding

🧠 Experience Summary:

⸻

A single wrong index choice can magnify query time by 50 times; a finely tuned combination can rejuvenate the system.

When facing the optimizer, don't "fully trust" or "forcefully confront"; use more detailed strategies to collaborate with it, which is the advanced way of SQL optimization.