Netkiller MySQL 手札
MySQL MariaDB...
文档始创于2010-11-18
版权 © 2011, 2012, 2013 Netkiller(Neo Chan). All rights reserved.
版权声明
转载请与作者联系，转载时请务必标明文章原始出处和作者信息及本声明。
|
|
$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=InnoDB
AUTO_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) */$$
