PHP 用户行为记录表设计

本文目录导读：

1. 核心设计原则
2. 具体表结构 (MySQL DDL)
3. 字段设计说明
4. PHP (ThinkPHP/Laravel) 写入示例
5. 高级优化与注意事项

设计一个用户行为记录表（通常称为事件追踪、埋点日志或审计日志）是后端开发中非常常见的需求，一个好的设计需要兼顾写入性能（因为数据量巨大）、查询灵活性（用于分析）和可扩展性（未来可能新增事件类型）。

由于 PHP 通常配合 MySQL 使用，以下我将提供一套基于 MySQL 的通用且高效的设计方案，并针对不同场景（简单 vs 复杂）给出建议。

核心设计原则

• 只追加，不修改：行为日志是事实记录，设计上只允许 INSERT，不允许 UPDATE 和 DELETE（除非合规要求清理）。
• 冗余存储：为了查询效率，避免在查询时 JOIN 用户表或商品表，把需要的字段（如用户名、商品名）直接冗余存储。
• 分区表：随着数据量增长，按时间（created_at）进行分区,以便快速删除历史数据和加速查询。
• 分表策略（可选）：如果单表数据量超过千万级，可以按 user_id 进行哈希取模分表（如 user_log_0, user_log_1），或者按日期分表（user_log_20250315）。在此推荐按月分表，配合 PHP 的模型层动态切换表名。

具体表结构 (MySQL DDL)

CREATE TABLE `user_behavior_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  -- 核心标识
  `user_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID (冗余，0代表游客/未登录)',
  `session_id` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '会话ID (用于未登录用户的追踪)',
  `trace_id` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '全局追踪ID (用于全链路排查)',
  -- 行为内容
  `event_type` VARCHAR(50) NOT NULL COMMENT '事件类型 (如: page_view, click, add_to_cart, purchase, search)',
  `event_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '事件名称 (如: 首页轮播图点击)',
  `page_url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '当前页面URL',
  `page_title` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '页面标题',
  `referer_url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '来源页面URL (用于归因)',
  -- 目标对象 (多态关联)
  `target_type` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '目标类型 (如: product, article, sku)',
  `target_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '目标对象ID (如: 商品ID)',
  `target_name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '目标对象名称快照 (冗余，防止商品改名导致统计不准)',
  -- 业务数值
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '涉及金额 (如: 下单金额, 支付金额)',
  `quantity` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '数量 (如: 购买件数)',
  -- 设备与环境
  `device_type` VARCHAR(20) NOT NULL DEFAULT 'PC' COMMENT '设备类型 (PC, Mobile, Tablet, App)',
  `os` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '操作系统 (如: iOS 17.0, Android 14, Windows)',
  `browser` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '浏览器信息 (User-Agent 精简版)',
  `ip` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '客户端IP (支持IPv6)',
  `location` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'IP解析的粗略地理位置 (如: 广东省深圳市)',
  -- 扩展字段 (JSON类型)
  `extra_data` JSON DEFAULT NULL COMMENT '扩展数据 (存储自定义参数, 如: 搜索关键词, 停留时长)',
  -- 时间戳
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '行为发生时间',
  `created_date` DATE GENERATED ALWAYS AS (DATE(created_at)) STORED COMMENT '按天分区字段',
  PRIMARY KEY (`id`),
  KEY `idx_user_time` (`user_id`, `created_at`),
  KEY `idx_type_time` (`event_type`, `created_at`),
  KEY `idx_target` (`target_type`, `target_id`),
  KEY `idx_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户行为日志表';
-- 按月分区的示例 (运行在 MySQL 5.7+)
ALTER TABLE `user_behavior_log` 
PARTITION BY RANGE COLUMNS(`created_date`) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
    PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
    PARTITION p202503 VALUES LESS THAN ('2025-04-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

字段设计说明

A. 索引设计（关键）

• 联合索引 (user_id, created_at)：用于查看“某个用户的行为轨迹”,这是最常见的查询。
• 联合索引 (event_type, created_at)：用于查看“某种事件的总数变化趋势”。
• 不要在 extra_data JSON 字段上建索引，如果需要查询 JSON 内的某个值,建议将该值提取出来单独建列。

B. extra_data (JSON)

这是扩展性的核心，PHP 后端接收前端传参后，将不定长的数据（如：点击的坐标，搜索的筛选条件，页面停留时长）打包成 JSON 存入，在 PHP 中，使用 json_encode 存入，使用 json_decode 读取。

PHP (ThinkPHP/Laravel) 写入示例

建议封装一个统一的日志写入类，避免在业务代码中到处写 DB::table。

<?php
namespace App\Services;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Request;
class BehaviorLogService
{
    public static function record(array $data)
    {
        // 1. 自动填充基础环境信息 (避免每次业务代码都传)
        $data['ip']       = $data['ip'] ?? Request::ip();
        $data['session_id'] = $data['session_id'] ?? session()->getId();
        $data['user_id']  = $data['user_id'] ?? (auth()->id() ?? 0);
        $data['page_url'] = $data['page_url'] ?? Request::fullUrl();
        $data['device_type'] = $data['device_type'] ?? (isMobile() ? 'Mobile' : 'PC');
        // 2. 处理额外数据
        $json_extra = $data['extra_data'] ?? [];
        unset($data['extra_data']);
        // 3. 插入库
        DB::table('user_behavior_log')->insert([
            'user_id'     => $data['user_id'],
            'session_id'  => $data['session_id'],
            'trace_id'    => $data['trace_id'] ?? uniqid('trace_', true),
            'event_type'  => $data['event_type'],
            'event_name'  => $data['event_name'] ?? '',
            'page_url'    => $data['page_url'],
            'target_type' => $data['target_type'] ?? '',
            'target_id'   => $data['target_id'] ?? 0,
            'target_name' => $data['target_name'] ?? '',
            'amount'      => $data['amount'] ?? 0,
            'quantity'    => $data['quantity'] ?? 1,
            'device_type' => $data['device_type'],
            'os'          => detectOS(Request::userAgent()),
            'browser'     => detectBrowser(Request::userAgent()),
            'ip'          => $data['ip'],
            'location'    => getIpLocation($data['ip']),
            'extra_data'  => empty($json_extra) ? null : json_encode($json_extra, JSON_UNESCAPED_UNICODE),
            // created_at 由数据库默认值生成
        ]);
    }
}
// 业务代码中的调用:
BehaviorLogService::record([
    'event_type' => 'add_to_cart',
    'target_type' => 'sku',
    'target_id' => 123456,
    'target_name' => '红色T恤 M码',
    'extra_data' => ['sku_spec' => '红色/M', 'origin' => 'detail_page'],
]);

高级优化与注意事项

A. 异步写入 (消峰)

用户行为日志的请求量极大，如果在 HTTP 请求中同步写 MySQL，极易拖垮主库。

• 方案1：Redis 队列，PHP 只把日志写入 Redis List 左侧（lpush），由定时任务（Crontab/Workerman）批量取数据（rpop）攒够 100 条后，一次性 insert 到 MySQL。
• 方案2：消息队列，投递到 Kafka/RabbitMQ，由专门的消费者 PHP 进程写入数据库。

B. 大字段的处理

page_url、user_agent 这类字段占空间大且重复率高,如果觉得表过于庞大：

• 将 page_url 和 referer_url 拆分成单独的 url_mapping 表，主表中只存 url_id。

C. 冷热数据分离

• 当月或最近 3 个月的数据属于“热数据”放 MySQL（InnoDB）。
• 历史数据（如 6 个月前）定期通过脚本迁移到 ElasticSearch 或 ClickHouse 用于 BI 分析，MySQL 中删除。

D. 用户隐私合规

• IP 地址建议在存储前进行脱敏处理（例如将 168.1.1 存为 168.*.*），或者存储经过哈希加密的版本，用于去重统计，不做反查。不要存原始 UA 字符串，存解析后的 os 和 browser 字段。

E. 必须防刷

• 对于 click 事件，如果用户 1 秒内点击 100 次，服务器同样会记录 100 条，容易造成数据膨胀，PHP 层可以结合 Redis 做简单的频率过滤（1 秒内同类型事件只记 1 条，或统计累计次数）。

这版设计兼顾了通用性（支持任意事件类型）和查询性能（冗余字段 + 联合索引），在 PHP 开发中，建议辅助使用 Monolog 等日志组件将日志先写到文件，再做异步入库,以彻底保障主流程的响应速度。