mysql 如何使用年份分区,mysql – 大型会计表,两个日期分区的最佳方式

我需要创建一个日志表来存储到网络外的连接(最近3年,之后日志将进入备份).使用的硬件/软件是专有的,为了记帐,它只是用这样的一些参数调用我们的自定义脚本：

>当用户连接时(our_script START user mac ip);

>当用户断开连接时(our_script STOP用户mac ip in_bytes out_bytes more)

有时我们不会收到断开连接消息.所以我们需要适应这一点.

到目前为止,我想出了会计表的这个结构：

CREATE TABLE `accounting` (

`user` varchar(50) NOT NULL DEFAULT '',

`mac` varchar(20) NOT NULL DEFAULT '',

`ip` varchar(15) NOT NULL DEFAULT '',

`ipv6` varchar(39) DEFAULT NULL,

`start_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`stop_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`in_bytes` bigint(32) unsigned DEFAULT '0',

`out_bytes` bigint(32) unsigned DEFAULT '0',

`more_columns` varchar(255) default NULL,

PRIMARY KEY (`user`,`mac`,`ip`,`start_datetime`,`stop_datetime`),

KEY `prim_ipv6` (`user`,`mac`,`ipv6`,`start_datetime`,`stop_datetime`),

KEY `user` (`user`) USING HASH,

KEY `mac` (`mac`) USING HASH,

KEY `ip` (`ip`) USING HASH,

KEY `ipv6` (`ipv6`) USING HASH,

KEY `start_datetime` (`start_datetime`),

KEY `stop_datetime` (`stop_datetime`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

用于编写的查询是(这些需要非常快)：

>当用户连接简单插入时

>当用户断开更新记帐集stop_datetime = now(),in_bytes = $in_bytes,out_bytes = $out_bytes,more_columns = $more where user =“$user”and ip =“$ip”and stop_datetime =“0000-00- 00 00:00:00“按start_datetime DESC限制1的顺序排序;这是一个解决方法,如果我们有多行开始,我们只是更新最后一次启动.

一个更好的解决方法是,在每次启动时更新没有它的行的stop_datetime,并使用另一列存储这不是正常停止,我想我将采用第二种解决方法.

我们有两种方法可以从这个表中进行选择(这可能会更慢,最长1 – 2秒)：

>为用户选择最后50个连接：select … where user =“$user”order by start_datetime DESC limit 50 ;,为此,用户HASH键很方便,因为AFAIK HASH比BTREE更好.

>选择女巫用户连接ip = $ip on date = $date select … where ip =“$ip”和$date start_datetime和stop_datetime;

这个表将容纳0.5到1个bilion行,这就是为什么我想分区它.

最好的选择是按月分区,但是我有2个相关的日期,并且在月初或月末开始将在一个分区中,停止将在另一个分区中.

问题：

> Witch是分区的最佳方式,start_datetime,stop_datetime,start_datetime和stop_datetime的子分区？

>我真的需要分区吗？

>关于如何改进,您还有其他建议吗？

谢谢

解决方法:

由于在插入时不知道stop_datetime,因此分区必须由start_datetime完成.

我认为按日分区(date_format(start_datetime,’％Y-％m-％d’))就足够了,因为10亿行除以(3年* 365天)per每个分区900,000行

更新索引非常昂贵,它会在插入,更新和删除时发生.保持索引最小化.如果主要查询将是那些选择,那么我将保留主键如下,并删除所有其他键：

PRIMARY KEY (`user`,`start_datetime`,`stop_datetime`,`mac`,`ip`)

标签：performance,mysql,partitioning

来源： https://codeday.me/bug/20190806/1594679.html