Home » Code » 千万级数据表折腾鼓捣

千万级数据表折腾鼓捣

在一些面试过程中,经常被问到诸如大数据分页、索引优化之类的,由于没有实际操作过,一直不知道怎么回答好,或者回答得没有底气。现在就来实际操作一翻,看看所谓大点的数据表要如何做才能愉快的CRUD。我选择的应用场景是文章及文章标签,一张普通的文章表,存储几千万条记录,并为文章添加标签,具体功能有文章列表展示、文章全文搜索以及通过文章找标签和通过标签找文章。

注:以下操作都是在Mysql中进行的,版本为5.6或5.7,引擎统一为InnoDB。

数据准备

先来看数据表的设计。关于文章表topic,一开始把文章详情content字段也添加了进来,结构如下:

CREATE TABLE `topic` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
  `content` text NOT NULL COMMENT '内容',
  `publish_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '发表时间',
  `update_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
  `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '作者ID',
  `topic_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
  `tags` varchar(255) NOT NULL DEFAULT '' COMMENT '标签ID列表',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10179001 DEFAULT CHARSET=utf8;

对于文章详情这种很长的文本,一般用text类型来存储。我为详情添加的是10~1000个中英文字符,如果是博客类文章,可能偏少,但实际博客类文章也难达到千万级。如果是当成论坛帖子来看,很多水帖根本达不到1000个字。要是当门户CMS类的新闻文章来看,也是偏少了点,但无论是哪种场景下,详情都不应该跟文章的基本信息存一起,因为,text类型的详情实在太占空间了。这个结构的文章我添加了1000万,占用空间15.7G。

topic-ipd

带详情content,1000万数据占用空间

在添加到500万的时候,看到接近8G的空间占用就觉得不太妙,因此复制了一份,把详情content去掉。体积瞬间下降到了500M。有详情content字段的500万表现在没有了,记得当时进行过count(*)操作,约摸是28s,完全无法看。现在只有去掉了详情content字段的500万数据表,一次count(*)只需要2.5s。

topic_500

去掉详情content,500万数据count(*)耗时

这时标题title,类型是varchar(255),应该是占用体积第二大的了,它也去掉,对于500万数据,体积由500M下降到330M,count(*)耗时2.13s,变化不大。再把tags这个varchar(255)也去掉,是200M,count(*)耗时1.73s。都没有去掉详情text这个变化得大,因此,这些字段是可以保留的。

详情content这个大文本是必须去掉的。在Discuz中,帖子详情跟帖子基本信息就是分开存的,它的详情是跟回复存一起,只是标识一下,因此它的详情其实是1楼,下边的回复沙发、地板、板凳,然后就是5楼了。而在WordPress中,文章详情跟文章基本信息是在一起的,网上说法“WordPress表结构就注定它适应不了大数据”是有一定道理的,或许跟它定位有关。把详情content去掉后,插入了8000万数据,占用空间5.9G,一次count(*)需要接近2分钟。

topic_8000

去掉详情content,8000万数据count(*)耗时

至于数据是如何插入的,是使用的存储过程,由于要生成随机中英文字符(方法见这里),比较耗时,这8000万在服务器上给跑了21个小时,或许配置有点低吧,阿里云普通ECS,单核,1G内存,数据库是Mysql 5.7.13。

delimiter $$
drop procedure if exists insert_topic $$
create procedure insert_topic(num int,type int)
begin
declare i,j,tag_num,tmp_tag_num int default 0;
declare title varchar(255);
declare title_len,publish_time,update_time,uid,topic_status int;
declare per_size int default 1000;
declare sqlinit,tags varchar(255);

set sqlinit = 'insert ignore into `topic` (`title`, `publish_time`, `update_time`, `uid`, `status`, `tags`) values ';
set @sqlstr=sqlinit;

myloop:LOOP
set i=i+1;
if i > num then leave myloop;
end if;
#type,数据类型,0表示比较假的相同的几个字符,其他则是较真的汉字+字符。
if type=0 then
set title = 'title';
else
set title_len = floor(5+15*rand());
set title = generate_text(title_len);
end if;
set publish_time = generate_time();
set update_time = publish_time + 100000*rand();
set uid = ceil(rand()*1000000);
set topic_status = ceil(rand()*10);
set tags = '';
set tag_num = round(rand()*10);
set tmp_tag_num = 0;
while tmp_tag_num < tag_num do set tags=concat(tags,floor(rand()*10000),','); set tmp_tag_num=tmp_tag_num+1; end while; set @sqlstr=concat(@sqlstr, concat('(',concat_ws(',',concat('''',title,''''),publish_time,update_time,uid,topic_status,concat('''',tags,'''')),'),')); set j=j+1; if j>=per_size then
	set j=0;
	set @sqlstr=left(@sqlstr,char_length(@sqlstr)-1);
	select @sqlstr;
	prepare stmt from @sqlstr;
	execute stmt;
	deallocate prepare stmt;
	set @sqlstr = sqlinit;
end if;
end LOOP myloop;
end $$
delimiter ;

接下来就是标签表,这个简单。

CREATE TABLE `tag` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '' COMMENT '名称',
  `counts` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

至于插入的存储过程就不贴了,基本一样的,数据是1万。其实1万个标签,也挺多了。我这写了两年的博客,也才400多个标签,好多只有一篇文章相关。如果不是由用户随意填写而是由规定好让用户选的话,更是很难达到1万。

关联表也简单。

CREATE TABLE `topic_tag_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) unsigned NOT NULL DEFAULT '0',
  `tag_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

至于循环topic文章表把tags字段的标签再插入到关联表,Mysql存储过程处理起来实在麻烦,不想折腾了,使用PHP来搞了,结果只把3000多万文章的标签插入关联表就断开了,此时关联表数据量是1亿5千7百万。要是一开始插入文章的时候就把关联信息插入关联表就好了,失误!

topic_tag_map_topic_number

关联表中文章数量

topic_tag_map

关联表中数据总量

至于剩下的5000万文章的标签,算了,就当这些文章没有标签吧,这数量也足够来试验了。至于为什么要在文章表存标签,又在关联表存,主要是想对比一下这两方式哪种更好。由于关联表字段少又简单,1亿5千7百万,体积才4.9G。

至此,数据已准备好,文章表8000万,标签表1万,文章标签关联表1亿5千7百万。所有表只有一个主键索引。

小试索引

对于标签表,1万数据,不算数,略。

对于文章表,修改标题或者状态或者发表时间/更新时间。

topic_update_title

通过ID修改文章标题

update_topic_time

通过ID修改更新时间

对于使用主键ID来查找的操作,都是没有任何问题的。

如果更新某个用户的文章的发表时间:

update_topic_by_uid

通过uid修改发表时间

很明显的,不行了。很自然的想到该给uid上个索引,用户uid是从1~100万,数据量还算挺大。

topic_index_uid

8000万数据,100万uid,建立索引耗时7分26秒,数据大小由5.9G增长到9G。再来执行通过uid的更新:

topic_update_by_index_uid

通过uid更新-有uid索引

时间瞬间下来了,效果明显。如果再加一个条件status,是否一样快呢?status只有10种这里,实际情况也足够。

topic_update_time_status

通过uid和status更新,有uid索引

将条件顺序换掉,先写status再写uid,也是一样的。explain结果如下:

topic_explain_update_by_uid_status

通过uid确定下来的文章只有169篇了,再加一个状态条件当然也是很快的,在一个用户的文章数量不大的情况下,uid和status的联合索引感觉意义不大。为了看一下体积变化,还是加一下。

添加uid_status索引

添加uid_status索引

耗时基本是相同的,体积由9G增长到了11G。在一个用户文章数不大的情况下,这个空间换来的时间并不怎么划算。

状态status是一个很关键的条件,若要更新某一状态的文章,在没有uid的条件下,肯定很慢。

topic_update_by_status

通过status更新,没有status索引

一样的,加索引。

topic_add_index_status

添加status索引

完成后体积由11G增长到12G。

update_topic_by_status

通过status更新,有status索引

很可惜,还是很慢,对于数据量很小的status而言,这个索引没有什么用,感觉白白增加1G的体积。

现在回头想一下,uid和uid_status这两个索引有必要同时存在么?uid有100万数据,增加索引后体积由4.9G涨到9G,一看就是很猛的索引。而添加uid_status这个索引,从9G涨到11G,并不怎么猛,我想删掉uid索引,保留uid_status索引,这样的话单通过uid来更新是否还有效果呢。尝试一下。

topic_drop_index_uid

删除uid索引

删除居然很快,一看果然体积还是12G,一点儿变化也没有。

通过uid更新,使用uid_status索引

通过uid更新,无uid索引

效果是一样的快,有了uid_status索引,uid索引就没有必要存在了。那么status索引是否还有存在的必要呢?理论上是有的,因为mysql的索引有个最左前缀匹配原则(leftmost),在uid_status索引中,由于uid在前,当只有uid条件时是能用上uid_status索引的,当只有status条件时就用不上uid_status索引了,这时有单独的status索引就能用上它了。但又回到实际情况,status只有10种,即使用上了也快不了,也即索引区分度太低。至此,来看一些索引相关的小知识。

索引建立的原则

  • 使用区分度高的列作为索引
    区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,区分度越高,索引树的分叉也就越多,一次性找到的概率也就越高。
  • 尽量使用字段长度小的列作为索引
  • 使用数据类型简单的列(int 型,固定长度)
  • 选用NOT NULL的列
    在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
  • 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。这样也可避免索引重复。

索引使用的原则

  • 最左前缀匹配原则(leftmost),mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,=和in可以乱序,一个联合索引中,如uid,status的联合索引,使用status in()and uid = 的任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的形式
  • 索引列不能参与计算
    select * from topic where uid + 1 >10000 and uid < 50000
    这种方式uid的索引就不会再被使用,因为在进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。
    另外当使用<>,like通配符放置在最前面 如:like’%ddd’ ,not in, !=等运算符都不会使用索引。(<,<=,>,>=,=,in,between是会用到索引)
  • 查询数据库记录时,查询到的条目数尽量小,当通过索引获取到的数据库记录> 数据库总揭露的1/3时,SQL将有可能直接全表扫描,索引就失去了应有的作用。

以上小知识来自这里

关于索引的基础知识还可以参考:http://thephper.com/?p=142

总结:对于topic表,最终建立了uid_status联合普通索引和status单列普通索引,数据大小由只有主键索引的5.9增加到12G。

对于topic_tag_map表,显然的需要为topic_id和tag_id建立一个唯一索引,防止数据重复。但实际情况之前生成数据是之前生成文章时有文章里边的标签ID重复了,这万分之一的概率啊,最终导致关联表也有重复的了。要找出来太慢了,没办法,先为tag_id建一个索引吧。

map_add_index_topic_id

1亿5千7百万,tag_id只有1万的,这区分度看着好小。体积由4.9G涨到了7.2G。至于topic_id,有8000万,区分度杠杠的。这个也完成后,体积涨到了9.6G。

map_add_index_topic

如果要找出重复的,必须使用group by加having,我的天,用不到任何索引,还有临时表和文件排序,这是要慢出翔的节凑。

map_group_by_having

group by后没用到任何索引

只有执行建唯一索引的语句,会提示哪个有冲突,再找出来,手动删除。但发现实在太多了,这floor(rand()*10000)取1~10个数都能有这么多重复的,也是醉了。没办法,不建这个唯一索引了,实际查找也只是通过tag_id或者topic_id而已。为了数据对等,文章表8000万得删掉5000万才得,之前保持这个数据,主要是想看一下这个数据量建索引需要的时间以及体积大小。

实际上折腾到这里,使用service mysql stop已经无法停止了,提示超时。执行了这个命令后/var/run/mysqld/下的sock文件没了,pid还在,然后再连接也不行了(没了sock)。没办法,只有大招,reboot。重启后mysql自动启动,sock文件又回来了,可以登陆。如何大批量删除,这又是一个问题。首先,必须把topic表的索引都给干掉(除了主键索引),否则会慢很多,然后存储过程分批删除,每次10万,一次约耗时1s多点。

delete_topic_sql

删除的存储过程如下:

delimiter $$
drop procedure if exists delete_topic $$
create procedure delete_topic(min_topic_id int, size int)
begin
repeat
delete from topic where id > min_topic_id order by id desc limit size;
untill row_count()=0
end repeat;
end $$
delimiter ;

从8000万删除到5000万,用了10分钟。

delete_topic_8000_5000

从关联表中得出只关联到31420000,继续执行call delete_topic(31420000,100000)即可达到同步。好了,数据和索引都删除了,体积却还是12G,没有减少1KB,这。。。一翻搜索,执行optimize命令来进行“优化”:

optimize_topic

经过4分多钟的优化,体积由12G降到了3.6G,对于3142万数据,没有索引,还算正常。对于索引的添加,下边继续。

展示文章列表

开始实现具体的功能,首先就是文章列表。取文章列表,可能有where条件有可能没有。有where时,一般是取某个用户(uid)的文章,大多情况下还伴随着状态(status),以及时间段(publish_time),排序嘛,就是发表时间(publish_time)了,更新时间用得少。

One comment

  1. 千万级真的想都不敢想,厉害。

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.