Heroku部署NodeJS站点移动端SQL查询重复执行问题求助

Hey there, let's walk through how to diagnose and fix this tricky double SQL query issue you're seeing on mobile—especially with that nested follow-check logic. Here's a step-by-step breakdown:

1. First, Confirm if It's Duplicate Requests or Backend Logic #

Before diving into SQL, rule out frontend or request-level causes:

• Check for duplicate frontend triggers: Mobile browsers sometimes fire both touchstart and click events on button taps if you don't handle them properly. Add console logs in your frontend code when the follow-check request is sent, or use Chrome DevTools (toggle mobile device mode) to inspect the Network tab—look for two identical requests from the same user agent.
• Verify Heroku request logs: Run heroku logs --tail in your terminal to watch requests in real time. Look for matching request IDs, IPs, and timestamps for the double SQL logs. If you see two separate requests, the issue is frontend; if only one request but two SQL calls, it's backend logic.

2. Fix Backend Logic Issues (If It's a Single Request Triggering Double Queries) #

Since you mentioned nested SQL queries, the problem might be in how you're handling async logic:

• Audit nested async code: If you're using callbacks or async/await, make sure you aren't accidentally executing the same query twice. For example, a common mistake is calling the query both inside and outside a then() block, or having a conditional that incorrectly branches into the same query execution.

  // Bad example: Accidental double query
  async function checkFollowStatus(req, res) {
    const userId = req.user.id;
    const targetId = req.params.id;
    // First query
    db.query('SELECT * FROM follows WHERE follower_id = ?', [userId], (err, rows) => {
      if (err) throw err;
      // Second unnecessary query (same logic repeated)
      db.query('SELECT EXISTS(SELECT 1 FROM follows WHERE follower_id = ? AND followed_id = ?)', [userId, targetId], (err, result) => {
        res.send(result);
      });
    });
  }
  

• Encapsulate query logic: Wrap your follow-check logic into a single reusable function, then call it once per request. This eliminates accidental duplicate calls:

  // Better: Encapsulated single query
  async function isFollowing(userId, targetId) {
    const [result] = await db.query('SELECT EXISTS(SELECT 1 FROM follows WHERE follower_id = ? AND followed_id = ?)', [userId, targetId]);
    return result[0]['EXISTS(SELECT 1 FROM follows WHERE follower_id = ? AND followed_id = ?)'] === 1;
  }
  
  async function handleFollowCheck(req, res) {
    const isFollowed = await isFollowing(req.user.id, req.params.id);
    res.json({ isFollowing: isFollowed });
    console.log(`Follow check executed once for user ${req.user.id} -> ${req.params.id}`);
  }
  

3. Refactor Nested SQL for Stability & Performance #

Nested queries are prone to logic errors and performance hits. Replace them with efficient, single queries:

• Use EXISTS for follow checks: Instead of nested queries to check if a follow relationship exists, use a single EXISTS query—it's faster and less error-prone, as shown in the example above.
• Avoid unnecessary nested queries: If you're fetching related data, use JOIN statements instead of chaining multiple queries. This reduces the number of database calls and eliminates opportunities for duplicate execution.

4. Heroku-Specific Checks #

• Dyno process management: If you're using cluster mode for Node.js, ensure requests aren't being duplicated across processes. Most Heroku Node.js apps run in single-process mode by default, but it's worth confirming with heroku ps to see active dynos.
• Cache frequent queries: For follow status (which doesn't change constantly), add caching with Redis (Heroku has an official Redis add-on). Cache the result for a short period (e.g., 5 minutes) to reduce SQL calls entirely, and avoid any chance of double execution.

Final Tip #

Once you make changes, test on mobile using real devices or browser dev tools' mobile mode, and monitor heroku logs to confirm queries are only running once per request.

内容的提问来源于stack exchange，提问作者Soccerrlife