网站各页面访客计数器及查看面板实现方案技术咨询

Hey Maxime, great call questioning your initial approach—while a MySQL + JavaScript increment works for tiny sites, it’ll hit scalability and performance walls as your traffic grows. Let’s break down better alternatives tailored to different traffic levels and needs:

Why Your Original Idea Isn’t Ideal #

First, let’s quickly cover the pain points:

• Database Lock Contention: Every UPDATE page_stats SET count = count +1 WHERE page_url = ? will trigger row-level locks in MySQL. With high traffic, this creates bottlenecks and slows down your site.
• Wasted Resources: Writing to disk (MySQL) for every single page view is overkill—most of these writes don’t need to be immediately persisted to a relational DB.
• Limited Flexibility: It’s hard to track more nuanced metrics like unique visitors (UV) vs page views (PV) with a simple counter.

Better Solutions #

1. Redis + MySQL (Best for Medium-High Traffic, Real-Time Needs) #

Redis is built for fast, atomic operations—perfect for high-volume counting. Here’s how to implement it:

• Step 1: Track Views in Redis: On each page load, use your backend (better than frontend JS to avoid tampering) to increment a Redis key, e.g.:

  // Example with Node.js + redis client
  const redis = require('redis');
  const client = redis.createClient();
  
  async function incrementPageView(pageUrl) {
    await client.incr(`page:views:${pageUrl}`);
  }
  

  Redis handles thousands of these increments per second without breaking a sweat, thanks to its in-memory storage and atomic operations.
• Step 2: Batch Sync to MySQL: Set up a cron job or scheduled task (e.g., every 5 minutes) to pull all the Redis counters, add them to your MySQL totals, and reset the Redis keys (or keep a running total). This reduces MySQL writes from thousands per minute to just a handful.
• Bonus: For unique visitors, use Redis Sets to store user identifiers (like a hashed cookie ID) and get the count with SCARD:

  async function trackUniqueVisitor(pageUrl, userId) {
    await client.sadd(`page:uv:${pageUrl}`, userId);
  }
  
  async function getUniqueVisitors(pageUrl) {
    return await client.scard(`page:uv:${pageUrl}`);
  }
  

2. Log-Based Offline Analysis (Best for High Traffic, Non-Real-Time Dashboards) #

If you don’t need real-time stats (e.g., daily/weekly reports), skip per-request writes entirely:

• Step 1: Log Page Views: Write each page view to a server log (with URL, timestamp, user ID if available) or a message queue. This is extremely lightweight and doesn’t impact your site’s performance.
• Step 2: Process Logs Offline: Use tools like Apache Spark or even a simple Python script to parse the logs, aggregate counts (PV/UV), and write the results to MySQL for your dashboard.
• Pros: No runtime performance hit; easy to scale to millions of views.
• Cons: Stats will be delayed (e.g., updated hourly instead of instantly).

3. Stick with MySQL (Only for Low-Traffic Sites) #

If your site gets just a few hundred views per day, your original approach is totally fine—you won’t notice any performance issues. Just make sure to:

• Index the page_url column in your page_stats table to speed up the UPDATE queries.
• Wrap the increment in a transaction to avoid race conditions.

Final Recommendations #

• Small sites (<1k views/day): Stick with MySQL + JS increment (keep it simple).
• Medium sites (1k-100k views/day): Use Redis for real-time counting, batch sync to MySQL.
• Large sites (>100k views/day): Combine Redis for real-time dashboards + log-based analysis for accurate historical stats.

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