📊 数据库初始化脚本

🔄 数据结构设计

📝 MySQL 脚本

注意事项

需要为表创建分区以优化查询性能

操作日志主表

CREATE TABLE `operation_log` (
  -- 主键信息
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT,
  
  -- 请求信息
  `request_date` datetime DEFAULT NULL COMMENT '请求时间',
  `request_id` varchar(64) DEFAULT NULL COMMENT '请求唯一标识',
  `request_url` varchar(128) DEFAULT NULL COMMENT 'url地址',
  `request_method` varchar(20) DEFAULT NULL COMMENT '请求类型',
  `request_execute_time` decimal(14,4) DEFAULT NULL COMMENT '执行时间(ms)',
  `request_name` varchar(255) DEFAULT NULL COMMENT '接口名称',
  
  -- 用户信息
  `user_id` varchar(40) DEFAULT NULL COMMENT '用户id',
  `username` varchar(128) DEFAULT NULL COMMENT '用户姓名',
  `request_ip` varchar(64) DEFAULT NULL COMMENT '请求ip',
  
  -- 错误信息
  `error_no` int(11) DEFAULT NULL COMMENT '错误码',
  `error_info` varchar(255) DEFAULT NULL COMMENT '错误消息',
  `error_status` tinyint(2) DEFAULT '0' COMMENT '系统异常状态(1:是,0:否)',
  
  -- 服务信息
  `server_name` varchar(64) DEFAULT NULL COMMENT '服务名称',
  `server_ip` varchar(64) DEFAULT NULL COMMENT '服务ip',
  `server_prot` int(11) DEFAULT NULL COMMENT '服务端口',
  
  -- 其他信息
  `alarm_send_status` tinyint(2) DEFAULT '0' COMMENT '报警状态(1:已发送,0:未发送)',
  `author` varchar(128) DEFAULT NULL COMMENT '作者',
  
  -- 索引定义
  PRIMARY KEY (`log_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_execute_time` (`request_execute_time`) USING BTREE,
  KEY `idx_error_no` (`error_no`) USING BTREE,
  KEY `idx_request_id` (`request_id`) USING BTREE,
  KEY `idx_request_date` (`request_date`) USING BTREE,
  KEY `idx_server_name` (`server_name`) USING BTREE,
  KEY `idx_request_name` (`request_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

操作日志详情表

CREATE TABLE `operation_log_detail` (
  -- 主键信息
  `log_id` bigint(20) NOT NULL,
  `request_date` datetime NOT NULL COMMENT '请求时间',
  
  -- 详细信息
  `request_param` longtext COMMENT '请求参数',
  `error_stack_trace` longtext COMMENT '异常堆栈信息',
  `content` longtext COMMENT '响应内容',
  
  -- 索引定义
  PRIMARY KEY (`log_id`) USING BTREE,
  KEY `idx_request_date` (`request_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

📊 TimescaleDB 脚本

操作日志主表

-- 创建主表
CREATE TABLE operation_log (
  -- 主键信息
  log_id BIGSERIAL,
  request_date TIMESTAMPTZ NOT NULL,
  
  -- 请求信息
  request_id VARCHAR(64),
  request_url VARCHAR(128),
  request_method VARCHAR(20),
  request_execute_time decimal(10,4),
  request_name VARCHAR(255),
  
  -- 用户信息
  user_id VARCHAR(40),
  username VARCHAR(128),
  request_ip VARCHAR(64),
  
  -- 错误信息
  error_no int4,
  error_info VARCHAR(255),
  error_status int2 DEFAULT 0,
  
  -- 服务信息
  server_name VARCHAR(64),
  server_ip VARCHAR(64),
  server_prot int4,
  
  -- 其他信息
  alarm_send_status int2 DEFAULT 0,
  author VARCHAR(128)
);

-- 创建超表
SELECT create_hypertable('operation_log', 
    by_range('request_date', INTERVAL '1 day'));

-- 创建索引
CREATE UNIQUE INDEX idx_log_id ON operation_log(log_id, request_date);
CREATE INDEX idx_request_id ON operation_log(request_id);
CREATE INDEX idx_user_id ON operation_log(user_id);
CREATE INDEX idx_request_url ON operation_log(request_url);
CREATE INDEX idx_ip ON operation_log(request_ip);

操作日志详情表

-- 创建详情表
CREATE TABLE operation_log_detail (
  -- 主键信息
  log_id int8,
  request_date TIMESTAMPTZ NOT NULL,
  
  -- 详细信息
  request_param text,
  error_stack_trace text,
  content text
);

-- 创建超表
SELECT create_hypertable('operation_log_detail', 
    by_range('request_date', INTERVAL '1 day'));

-- 创建索引
CREATE UNIQUE INDEX idx_detail_log_id 
    ON operation_log_detail(log_id, request_date);

💡 优化建议

MySQL优化

1. 分区策略

   -- 按月分区示例
   ALTER TABLE operation_log
   PARTITION BY RANGE (TO_DAYS(request_date)) (
       PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
       PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01'))
   );

2. 索引优化

   • 根据查询模式调整索引
   • 定期维护索引统计信息
   • 监控索引使用情况

TimescaleDB优化

1. 分片策略

   • 根据数据量调整分片间隔
   • 设置合适的压缩策略
   • 配置数据保留策略

2. 查询优化

   -- 开启并行查询
   SET max_parallel_workers_per_gather = 4;
   
   -- 使用时间范围过滤
   SELECT * FROM operation_log 
   WHERE request_date >= NOW() - INTERVAL '7 days';

❗ 注意事项

1. 数据库维护

   • 定期清理历史数据
   • 定期优化表结构
   • 监控表空间增长

2. 性能考虑

   • 合理设置字段长度
   • 适当使用压缩
   • 控制索引数量

建议

• 根据实际数据量选择合适的数据库
• 定期进行性能测试
• 建立数据备份策略