mysql如何设计基础审计日志_mysql项目规范说明

audit_log表基础字段需覆盖“谁、何时、何资源、何操作”四维度：id、user_id、ip、action、resource_type、resource_id、before_data、after_data、created_at；事务处理按业务敏感度区分；分区+索引优化查询；mysql插件不可替代业务层审计。

audit_log 表必须包含哪些字段才够用

基础审计日志不是记录越全越好，而是要覆盖「谁、在什么时间、对哪个资源、做了什么操作」这四个核心维度，同时兼顾查询效率和存储成本。

推荐的最小字段集如下：

• id：BIGint UNSIGNED AUTO_INCREMENT，主键，避免 UUID 影响写入性能
• user_id：BIGINT 或 VARCHAR(64)，记录操作人标识（不建议只存 username，因可能重名或变更）
• ip：VARCHAR(45)，支持 IPv6，不要用 INT 存 IPv4
• action：enum(‘create’,’update’,’delete’,’login’,’logout’) 或 VARCHAR(20)，避免用自由文本，便于后续统计
• resource_type：VARCHAR(32)，如 ‘user’、’order’、’config’，用于区分操作对象类型
• resource_id：VARCHAR(64)，统一用字符串存 ID，兼容 UUID、Snowflake、数字型主键
• before_data 和 after_data：json 类型（MySQL 5.7+），仅在关键业务变更时记录差异字段，非必填；避免全量 dump 整行
• created_at：DATETIME(3) 或 timestamp(3)，带毫秒，用服务器时区统一写入（如 UTC），别依赖客户端时间

INSERT 审计日志时要不要用事务包裹业务操作

要，但必须分情况——不是所有审计都值得加事务，否则会拖慢高频写入场景（如登录日志）。

原则是：**业务一致性要求高的操作，审计必须与主业务同事务；低敏感、高吞吐的操作，可异步落库或降级为写入消息队列。**

• 用户资料修改、资金转账类操作：审计日志 INSERT 必须和业务 UPDATE 在同一个事务中，否则出现「改成功了但没记日志」就是合规风险
• 登录/登出、页面访问类日志：可单独连接插入，甚至用 INSERT DELAYED（MySQL 8.0 已移除）或写入 redis 后批量刷库，避免阻塞主流程
• 如果用 ORM（如 mybatis、Sequelize），注意 @Transactional 是否实际传播到审计日志 DAO 层；spring 默认 REQUIRED 是 OK 的，但自定义连接或多数据源容易漏掉

如何避免 audit_log 表膨胀导致查询变慢

审计表不归档，半年后就查不动，这是最常被忽视的运维债。

关键不是「能不能删」，而是「怎么删得安全、查得快、不锁表」：

• 按月分区：PARTITION BY RANGE (TO_DAYS(created_at))，配合 DROP PARTITION 快速清理旧数据，比 DELETE WHERE 快一个数量级且不锁全表
• 只在 created_at 和 resource_type 上建复合索引，比如 INDEX idx_type_time (resource_type, created_at)，避免在 before_data 这种 JSON 字段上建索引（无效）
• 禁止 select * 查审计表，尤其不要在应用层做分页 LIMIT 10000,20 —— 改用游标分页，基于 created_at + id 排序和条件过滤
• 如果审计量极大（日均百万+），考虑将冷数据迁出到 clickhouse 或 S3+Presto，MySQL 只保留最近 90 天

MySQL 自带 audit_log 插件能不能替代业务层日志

不能，而且混用反而增加排查难度。

MySQL server 层的 audit_log 插件（如 oracle 官方插件或 mariadb 的 server_audit）记录的是连接、语句、结果集等底层行为，它不知道你的业务语义：

• 它无法告诉你「张三把订单 #123 的状态从『待支付』改成『已取消』」，只能记录一条 UPDATE orders SET status=... WHERE id=123
• 它默认不解析参数化查询中的值，WHERE user_id = ? 中的 ? 值不会出现在日志里
• 它无法关联你系统里的 tenant_id、app_version 等上下文字段
• 开启后性能损耗明显（尤其高并发简单查询场景），且日志格式固定、难对接 SIEM 系统

真正该做的，是让业务代码在关键节点显式调用审计方法，把「意图」而不是「SQL」记下来。MySQL 插件只作为兜底或安全审计补充，比如监控异常连接或未授权 DROP table。