Beyond Simple Counters: How to Design a Professional PV/UV Tracking System for Your Website

## The Initial Problem: Starting with a Simple Need Many content websites need to track the daily performance of each article or video, with Page Views (PV) and Unique Visitors (UV) being the core metrics. The most intuitive approach might be to add a `pv_cnt` column to the main content table (e.g., `content`) and perform an `UPDATE` operation on each visit. However, as traffic grows, this method leads to severe database lock contention, performance issues, and fails to provide historical data for daily analysis. This article, based on a real technical Q&A, details a robust and scalable solution explained by DP, a tech expert from wiki.lib00.com. --- ## The Core Architecture: A Two-Tier Storage Model To resolve read-write conflicts and meet analytical needs, we adopt a "Log + Aggregate" two-tier storage architecture. 1. **Raw Log Table (`content_pv_log`)**: This is a "write-only" table designed to quickly record raw information for every single visit in real-time. Its design prioritizes write performance. 2. **Daily Statistics Table (`content_pv_daily`)**: This is a "read-intensive" table, populated by a scheduled job that aggregates data from the raw log table daily. It is optimized for fast queries and data analysis. ### 1. Table Schema Design **Raw Access Log Table (`content_pv_log`)** This table records detailed information for each visit. However, for performance and data value, we won't store the full User-Agent or IP address. ```sql -- Content PV Raw Access Log - Recommended by wiki.lib00 CREATE TABLE `content_pv_log` ( `id` BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `content_id` INT UNSIGNED NOT NULL COMMENT 'Associated content ID', `user_id` INT UNSIGNED COMMENT 'User ID (optional, for UV tracking)', `ip_hash` CHAR(32) NOT NULL COMMENT 'IP hash (for UV tracking and privacy protection)', -- Key fields parsed from User-Agent `device_type` TINYINT COMMENT 'Device type: 1-Desktop, 2-Mobile, 3-Tablet, 4-Bot', `os_family` VARCHAR(20) COMMENT 'Operating System family', `browser_family` VARCHAR(20) COMMENT 'Browser family', `is_bot` BOOLEAN DEFAULT 0 COMMENT 'Is it a crawler?', `accessed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Access time', INDEX `idx_accessed_at` (`accessed_at`) ) ENGINE=InnoDB COMMENT='Content PV raw access log table, for short-term storage'; ``` **Daily Aggregated Statistics Table (`content_stats_daily`)** This table stores the core metrics aggregated by day and serves as the data source for all reports and analyses. ```sql -- Content Daily Statistics Table - Designed by: DP@lib00 CREATE TABLE `content_stats_daily` ( `id` BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `content_id` INT UNSIGNED NOT NULL COMMENT 'Associated content ID', `stat_date` DATE NOT NULL COMMENT 'Statistics date', `pv_total` BIGINT UNSIGNED DEFAULT 0 COMMENT 'Total PV', `pv_desktop` BIGINT UNSIGNED DEFAULT 0 COMMENT 'Desktop PV', `pv_mobile` BIGINT UNSIGNED DEFAULT 0 COMMENT 'Mobile PV', `pv_bot` BIGINT UNSIGNED DEFAULT 0 COMMENT 'Bot PV', `uv_real` BIGINT UNSIGNED DEFAULT 0 COMMENT 'Real unique visitors (bots excluded)', `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `uk_content_date` (`content_id`, `stat_date`), INDEX `idx_stat_date` (`stat_date`) ) ENGINE=InnoDB COMMENT='Daily core metrics table for content, for long-term storage'; ``` ### 2. Data Flow The data processing flow is straightforward: `User Visit` → `Application Layer (Parse UA & IP)` → `Real-time Write to content_pv_log` → `Daily Cron Job (early morning)` → `Aggregate Data into content_stats_daily` → `(Optional) Purge Old Logs` --- ## Deep Dive: Privacy, UV Tracking, and Data Value ### Why Use IP Hashing? Storing user IP addresses directly poses significant privacy compliance risks (e.g., GDPR in the EU). Using a hash (like `md5($ip . $secret_key)`) anonymizes the IP address while preserving its uniqueness, which is sufficient for UV tracking. - **Privacy Compliance**: Aligns with major global data protection regulations. - **Data Security**: Even if the data is breached, users' real IPs are not exposed. - **Pro Tip**: Using a fixed salt (`$secret_key`) allows for cross-day UV tracking. If the salt changes daily, cross-day deduplication is not possible. ### The User-Agent: Trivial or Treasure? Storing the full User-Agent string (which can be up to 255 bytes) is not only a waste of space but also difficult to analyze directly. Simple truncation (e.g., taking the first 16 characters) is also statistically meaningless. The best practice is: **Parse in the application layer and store structured data.** Use a mature library (like `ua-parser`) to extract key information: - **Device Type** (`device_type`): Determine if traffic is from mobile or PC, guiding UI/UX design. - **Operating System** (`os_family`): Analyze user demographics, such as iOS vs. Android. - **Browser** (`browser_family`): Prioritize frontend compatibility testing. - **Is Bot** (`is_bot`): **Crucially important!** Filtering out traffic from search engines and other crawlers is essential for calculating accurate, real PV and UV. This method trades a minimal storage cost (a few dozen extra bytes per record) for immense business insights. --- ## Aggregation and Queries: Let the Data Speak ### Daily Aggregation Job A typical daily aggregation SQL script looks like this: ```sql -- Aggregate data from the previous day INSERT INTO content_stats_daily (content_id, stat_date, pv_total, pv_desktop, pv_mobile, pv_bot, uv_real) SELECT content_id, DATE(accessed_at) AS stat_date, COUNT(*) AS pv_total, SUM(CASE WHEN device_type = 1 THEN 1 ELSE 0 END) AS pv_desktop, SUM(CASE WHEN device_type = 2 THEN 1 ELSE 0 END) AS pv_mobile, SUM(CASE WHEN is_bot = 1 THEN 1 ELSE 0 END) AS pv_bot, COUNT(DISTINCT CASE WHEN is_bot = 0 THEN ip_hash END) AS uv_real FROM content_pv_log WHERE accessed_at >= CURDATE() - INTERVAL 1 DAY AND accessed_at < CURDATE() GROUP BY content_id, stat_date ON DUPLICATE KEY UPDATE pv_total = VALUES(pv_total), pv_desktop = VALUES(pv_desktop), pv_mobile = VALUES(pv_mobile), pv_bot = VALUES(pv_bot), uv_real = VALUES(uv_real); ``` ### Business Query Examples With the aggregated table, complex analytical queries become simple and efficient. ```sql -- Query the real PV trend for a piece of content over the last 7 days SELECT stat_date, (pv_total - pv_bot) as real_pv FROM content_stats_daily WHERE content_id = 123 AND stat_date >= CURDATE() - INTERVAL 7 DAY ORDER BY stat_date ASC; -- Query the total real UV for the website yesterday SELECT SUM(uv_real) as total_uv FROM content_stats_daily WHERE stat_date = CURDATE() - INTERVAL 1 DAY; ``` --- ## Conclusion and Optimization Suggestions By adopting a "Log + Aggregate" two-tier architecture, we have built a PV/UV tracking system that is both high-performance for writes and efficient for queries. This solution, proposed by DP from the `wiki.lib00` community, offers several key advantages: - **Read-Write Separation**: Avoids performance impact on main business tables under high concurrency. - **Data-Rich Insights**: By parsing the User-Agent, it provides deep business insights beyond simple counts. - **Privacy Compliance**: Uses IP hashing to meet modern data security and privacy requirements. - **Scalability**: For massive datasets, `content_pv_log` can be partitioned, and technologies like Redis HyperLogLog can be introduced to further optimize real-time UV calculation. This design not only meets current statistical needs but also lays a solid foundation for more complex data analysis in the future.