mysql的分表分库,以归档,以及redis

首先什么时候分表分库?

https://www.zhihu.com/question/316036176
MySQL数据库,数据表超过百万了查询速度有点慢。之后怎么存储呢?

对于 MySQL 来讲,百万的数据量其实还好,只要建好适合的索引,一般查询不会慢,阿里巴巴手册里也给出了建议

如果数据量不是很大,表容量很大,看看是不是有大字段,比如 text、longtext 这种类型的字段,那就可以把这种大字段单独拆分出来,放另一张表,或者其实存储数据库,比如 MongoDB。要是实在没有其他优化空间了,那就要考虑分库分表了。

对mysql归档

使用MySQL的过程,经常会遇到一个问题,比如说某张”log”表,用于保存某种记录,随着时间的不断的累积数据,但是只有最新的一段时间的数据是有用的;这个时候会遇到性能和容量的瓶颈,需要将表中的历史数据进行归档。

下面描述一种典型的做法:

比如说表结构如下:

CREATE TABLE `history` (
  `id` int(11) NOT NULL,
  `value` text,
  `addtime` timestamp default current_timestamp,
  PRIMARY KEY (`id`),
  index idx_addtime(`addtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

这张表中保存有2012年2013年两年的数据,现在需要将2012年的数据备份归档起来,但是2013年年初的数据还需要被查询,因此不能简单的进行如下的动作:

create table history_tmp like history;
rename table history to history_2012,history_tmp to history;

需要在新表中保留2013年年初的数据,可以参照下面的流程进行:

create table history_tmp like history;
maxid=select max(id) from history;
minid=select id from history where addtime>"2013-01-01 00:00" order by addtime asc limit 1;
last=0;
set autocommit=1;
for(i=minid;i<maxid+1000;i+=1000)
{
  insert into history_tmp select * from history where id>=last and id<i lock in share mode;
  last=i;
}
begin;
lock table history_tmp write,history write;
maxid=select max(id) from history;
insert into history_tmp select * from history where id>=last and id<=maxid;
alter table history rename to history_2012;
alter table history_tmp rename to history;
unlock tables;
commit;

说明:

1.使用alter table xx rename to xx,而不是rename是因为mysql的一个bug, bug地址,直接rename会出现”ERROR 1192 (HY000): Can’t execute the given command because you have active locked tables or an active transaction”错误.
2.需要使用lock history write来防止新的写入。
3.这个方式是假设这个表在有插入和查询操作,如果有update、delete操作可以通过类似OSC的算法使用trigger来实现。
4.不能直接使用insert select where id>minid这种方式,因为这样会导致slave的延迟,而且迟迟不能提交的事务会导致undo log无法purge。

posted @ 2020-11-30 03:31  风意不止  阅读(471)  评论(0编辑  收藏  举报