问题描述

问题描述

客户的项目使用MySQL做持久化,MySQL部署在单机服务器上,前期在数据存取上没有问题。后来加了一个爬虫项目,爬取百度地图的数据,数据很快堆到了一亿多条,所有的数据都存储在单个的MySQL数据表中,整体的数据量超过了70GB。

查询时的效率极低,几分钟才能出来结果。除此之外,前期分配的磁盘空间不足,整体的数据占用量也到了95%以上。所以一方面需要迁移MySQL的存储位置,另一方面需要解决查询效率的问题。

解决过程

存储迁移

在解决线上问题的时候,我的宗旨一直是尽量别相信中文社区的解决方案(包括本文),不过在做数据迁移的时候图省事直接找了个CSDN照做了,过程都是泪,最后还是老老实实照着StackOverflow做,迁移MySQL存储位置的方案看这里,简要描述如下(假设你的迁移目标目录是/data/mysql):

1. sudo service mysql stop

2. sudo cp -R -p /var/lib/mysql /data/mysql

3. 打开/etc/mysql/mysql.conf.d/mysqld.conf, 将datadir指向/data/mysql

4. 打开/etc/apparmor.d/usr.sbin.mysqld,将其中所有的/var/lib/mysql修改为/data/mysql

5. sudo /etc/init.d/apparmor reload

6. sudo service mysql start

按照上述步骤就可以顺利完成存储的迁移,如果期间确实遇到了问题,那么就删除存储目录下的ib_logfile0和ib_logfile1这两个文件,重新启动MySQL。

查询优化

优化查询的第一个反应就是加索引,查询依据主要是一个varchar的列,所以最初考虑直接对这一列加索引,设置了索引之后一直等它运行完成,结果一直做了四个多小时仍然没有结束。

由于这个尝试早于存储迁移,而且加索引的过程中会产生大量的临时文件,所以直接撑爆了磁盘,搞了很久才救回来。也是由于数据量很大的原因,没有做备份就直接怼了索引,现在想起来也是大胆。这个尝试之后就加了块大磁盘,先做好了存储迁移,然后开始考虑单表切分的问题。

就现在的用户量而言,主要的压力并不在服务器本身,所以仍然考虑单机切分。数据表的字段之间没有特别强的关联,而且有几个字段的内容量很大,可是客户端需要的字段比较多,如果做垂直切分最后还是要Join,因此最后做了表的水平切分。

客户端在查询的时候总是会带一个地区参数,而且参数只是城市,可以根据区域做水平切分。如果按照省份做切分,理想状态下会把数据表均匀切分成30多份,按照目前的数据增长速度,估计几个月之后又会上升到现在的量级,所以干脆按照城市进行切分,并且这次直接在新表上加索引。

在准备阶段,给数据表一个统一的前缀,结尾加上城市的Canton Id,用代码批量生成Model类,然后Migrate即可(项目基于Django)。接下来就是切分过程,大致思路是按照id每次从旧表中捞出10000条数据,根据city字段判断应该插入的新表,放在临时列表中,然后批量插入整个临时列表。

在做切分的过程中还是遇到了一点小坑,首先是Django的查询集缓存问题,规范可以参考官方文档,做的时候有这个意识,但是还是没有足够细心,导致一开始速度慢了很多。

另外还有一个更慢的地方,是在拼装新的Model实例的时候,这个过程理论上应该一瞬间完成,可是却成了时间瓶颈,检查了很久发现是一句item.city.canton_id导致了每次都重新查询一次数据库,做了City表中id到canton_id的映射之后这个问题才得以解决。外键写起来是个好东西,可是用起来稍不注意就忘了其凶残的本质,以后尽量不设置外键而是自己维护关联关系,这样才能时刻记住自己在做什么。