MySQL 单机单表切分实践

描述

客户的项目使用MySQL做持久化，MySQL部署在单机服务器上，前期在数据存取上没有问题。后来加了一个爬虫项目，爬取百度地图的数据，数据很快堆到了一亿多条，所有的数据都存储在单个的MySQL数据表中，整体的数据量超过了70GB，查询时的效率极低，几分钟才能出来结果。除此之外，前期分配的磁盘空间不足，整体的数据占用量也到了95%以上。所以一方面需要迁移MySQL的存储位置，另一方面需要解决查询效率的问题。

过程

存储迁移

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

1. 假设你的迁移目标目录是/data/mysql
2. 假设你的MySQL配置文件的目录是/etc/mysql/mysql.conf.d/mysqld.conf

1. $ sudo /etc/init.d/mysql stop # 或 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 /etc/init.d/mysql restart # 或 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的映射之后这个问题才得以解决。外键写起来是个好东西，可是用起来稍不注意就忘了其凶残的本质，以后尽量不设置外键而是自己维护关联关系，这样才能时刻记住自己在做什么。

结果

截至目前，迁移工作仍然在进行中，做完之后再来补…