Netkiller MySQL 手札

MySQL MariaDB...

Mr. Neo Chan, 陈景峰(BG7NYT)

中国广东省深圳市龙华新区民治街道溪山美地

518131

+86 13113668890

+86 755 29812080

<>

文档始创于2010-11-18

转载请与作者联系，转载时请务必标明文章原始出处和作者信息及本声明。

文档出处:

$Date: 2013-04-10 15:03:49 +0800 (Wed, 10 Apr 2013) $

我的系列文档

4.11. logging 日志表的设计

CREATE TABLE `logging` (	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,	`tag` ENUM('unknow','www','user','admin') NOT NULL DEFAULT 'unknow' COMMENT '日志标签',	`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '产生时间',	`facility` ENUM('bank','unionpay','sms','email') NOT NULL COMMENT '类别',	`priority` ENUM('info','warning','error','critical','exception','debug') NOT NULL COMMENT '级别',	`message` VARCHAR(512) NOT NULL COMMENT '内容',	PRIMARY KEY (`id`))COMMENT='日志表'COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=2;

分区日志表

delimiter $$CREATE TABLE `logging` (  `tag` enum('unknow','login','info','admin','cron','manual') NOT NULL DEFAULT 'unknow' COMMENT '日志标签',  `asctime` datetime NOT NULL COMMENT '产生时间',  `facility` enum('account','bank','unionpay','sms','email','unknow') NOT NULL DEFAULT 'unknow' COMMENT '类别',  `priority` enum('info','warning','error','critical','exception','debug') NOT NULL DEFAULT 'debug' COMMENT '级别',  `message` varchar(512) NOT NULL COMMENT '内容',  `operator` varchar(50) NOT NULL DEFAULT 'computer' COMMENT '操作者') ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (YEAR(asctime))SUBPARTITION BY HASH (MONTH(asctime))(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */$$

分表+分区，每年分表一次，每个分区中保存一个月的数据

delimiter $$CREATE TABLE `logging_2013` (  `tag` enum('unknow','login','info','admin','cron','manual') NOT NULL DEFAULT 'unknow' COMMENT '日志标签',  `asctime` datetime NOT NULL COMMENT '产生时间',  `facility` enum('account','bank','unionpay','sms','email','unknow') NOT NULL DEFAULT 'unknow' COMMENT '类别',  `priority` enum('info','warning','error','critical','exception','debug') NOT NULL DEFAULT 'debug' COMMENT '级别',  `message` varchar(512) NOT NULL COMMENT '内容',  `operator` varchar(50) NOT NULL DEFAULT 'computer' COMMENT '操作者') ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY LIST (MONTH(asctime))SUBPARTITION BY KEY (facility)(PARTITION part0 VALUES IN (1) ENGINE = InnoDB, PARTITION part1 VALUES IN (2) ENGINE = InnoDB, PARTITION part2 VALUES IN (3) ENGINE = InnoDB, PARTITION part3 VALUES IN (4) ENGINE = InnoDB, PARTITION part4 VALUES IN (5) ENGINE = InnoDB, PARTITION part5 VALUES IN (6) ENGINE = InnoDB, PARTITION part6 VALUES IN (7) ENGINE = InnoDB, PARTITION part7 VALUES IN (8) ENGINE = InnoDB, PARTITION part8 VALUES IN (9) ENGINE = InnoDB, PARTITION part9 VALUES IN (10) ENGINE = InnoDB, PARTITION part10 VALUES IN (11) ENGINE = InnoDB, PARTITION part11 VALUES IN (12) ENGINE = InnoDB) */$$

命名分区

delimiter $$CREATE TABLE `logging_2012` (  `tag` enum('unknow','login','info','admin','cron','manual') NOT NULL DEFAULT 'unknow' COMMENT '日志标签',  `asctime` datetime NOT NULL COMMENT '产生时间',  `facility` enum('account','bank','unionpay','sms','email','unknow') NOT NULL DEFAULT 'unknow' COMMENT '类别',  `priority` enum('info','warning','error','critical','exception','debug') NOT NULL DEFAULT 'debug' COMMENT '级别',  `message` varchar(512) NOT NULL COMMENT '内容',  `operator` varchar(50) NOT NULL DEFAULT 'computer' COMMENT '操作者') ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY LIST (MONTH(asctime))SUBPARTITION BY KEY (facility)(PARTITION January VALUES IN (1) ENGINE = InnoDB, PARTITION February VALUES IN (2) ENGINE = InnoDB, PARTITION March VALUES IN (3) ENGINE = InnoDB, PARTITION April VALUES IN (4) ENGINE = InnoDB, PARTITION May VALUES IN (5) ENGINE = InnoDB, PARTITION June VALUES IN (6) ENGINE = InnoDB, PARTITION July VALUES IN (7) ENGINE = InnoDB, PARTITION August VALUES IN (8) ENGINE = InnoDB, PARTITION September VALUES IN (9) ENGINE = InnoDB, PARTITION October VALUES IN (10) ENGINE = InnoDB, PARTITION November VALUES IN (11) ENGINE = InnoDB, PARTITION December VALUES IN (12) ENGINE = InnoDB) */$$