日志管理表设计的核心诉求
日志管理功能通常需要支持高并发写入、按时间范围快速查询、按需扩展存储等能力，在设计MySQL表结构时，需要优先满足这些核心诉求，避免后续出现性能问题后再做重构。日志数据本身具有写入量大、查询多为时间维度、数据价值随时间递减的特点，这些特点会直接影响表结构的设计方向。
基础字段选型设计
日志表的基础字段需要根据实际业务需求选择，既要覆盖必要的日志信息，又要避免过度冗余。以下是通用的基础字段设计示例：
-- 创建基础日志表 CREATE TABLE `system_log` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志自增ID', `log_type` TINYINT NOT NULL COMMENT '日志类型 1操作日志 2异常日志 3访问日志', `user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '操作用户ID 未登录时为NULL', `ip_address` VARCHAR(45) DEFAULT NULL COMMENT '操作端IP地址 兼容IPv6', `module` VARCHAR(50) NOT NULL COMMENT '操作的业务模块名称', `content` TEXT NOT NULL COMMENT '日志具体内容', `create_time` DATETIME NOT NULL COMMENT '日志生成时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统日志表';
字段选型时需要注意几个要点：自增主键优先使用BIGINT类型，避免日志量过大时INT溢出；时间字段使用DATETIME类型，避免TIMESTAMP的时间范围限制；IP地址字段长度设置为45是为了兼容IPv6格式，避免后续扩展时需要修改表结构。
索引设计优化
日志查询最常见的场景是按时间范围查询，其次是按日志类型、用户ID等条件过滤，因此索引设计需要围绕这些查询场景展开。
- 首先为
create_time字段添加普通索引，支持按时间范围快速筛选数据：
-- 添加时间索引 ALTER TABLE `system_log` ADD INDEX `idx_create_time` (`create_time`);
- 如果经常按日志类型和用户ID组合查询，可以添加联合索引，注意联合索引的顺序要符合最左匹配原则：
-- 添加类型+用户ID的联合索引 ALTER TABLE `system_log` ADD INDEX `idx_type_user` (`log_type`, `user_id`);
不要盲目添加过多索引，因为日志表写入量很大，每个索引都会增加写入的开销，只需要保留高频查询需要的索引即可。
分区表设计应对海量数据
当日志数据量达到千万级甚至亿级时，单表查询性能会明显下降，这时候可以使用MySQL的分区表功能，按时间维度对表进行分区，常见的分区方式是按月份分区。
-- 创建按时间分区的日志表
CREATE TABLE `system_log_partition` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志自增ID',
`log_type` TINYINT NOT NULL COMMENT '日志类型 1操作日志 2异常日志 3访问日志',
`user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '操作用户ID 未登录时为NULL',
`ip_address` VARCHAR(45) DEFAULT NULL COMMENT '操作端IP地址 兼容IPv6',
`module` VARCHAR(50) NOT NULL COMMENT '操作的业务模块名称',
`content` TEXT NOT NULL COMMENT '日志具体内容',
`create_time` DATETIME NOT NULL COMMENT '日志生成时间',
PRIMARY KEY (`id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分区系统日志表'
PARTITION BY RANGE (TO_DAYS(`create_time`)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
分区表的优势在于查询时如果带了时间条件，MySQL会自动扫描对应的分区，避免全表扫描，同时可以按分区删除过期数据，删除分区的效率远高于DELETE语句。
归档与清理策略
日志数据通常不需要永久保存，超过一定时间的日志可以归档到历史表或者对象存储中，减少线上表的存储压力。可以定期执行以下操作清理过期数据：
- 如果是分区表，直接删除过期分区：
-- 删除2024年1月的分区数据 ALTER TABLE `system_log_partition` DROP PARTITION p202401;
- 如果是单表，按时间范围删除过期数据，建议分批删除避免锁表：
-- 分批删除3个月前的日志 每次删除1000条 DELETE FROM `system_log` WHERE `create_time` < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 1000;
常见设计误区规避
在设计日志表时，有几个常见的误区需要规避：
- 不要在日志表中添加外键约束，外键会增加写入开销，而且日志表通常不需要强一致性关联；
- 不要将日志内容拆分到多个关联表中，日志查询通常需要获取完整信息，关联查询会降低性能；
- 不要使用
SELECT *查询日志，只查询需要的字段，减少数据传输和解析开销； - 不要忽略字符集设置，建议统一使用
utf8mb4字符集，避免特殊字符存储报错。
通过以上几个维度的设计，就能得到一个兼顾写入性能和查询效率的日志管理MySQL表结构，能够支撑大部分业务场景下的日志存储需求。