海量数据迁移中碰到的分页性能问题

       最近在做一个近千万级别的数据迁移(从oracle到mysql)，由于数据在迁移前后表结构发生很大的变化，比如原来几个用户相关的信息表中若干个字段要转成一个vcard格式的xml存到新表的一个字段中，没办法使用数据库的迁移工具完成，必须通过写代码的方式进行转换迁移。这样就存在一个问题，不可能将旧表的近千万的数据一次性加载到内存中进行转换迁移，必须进行分页。

       一开始，我使用oracle数据库的自己的分页方式(不需要任何排序，只要分页，遍历完所有数据即可)，即使用SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM < ?) WHERE RN >= ?进行分页，在主线程中每次取1000条旧库的数据放到队列中，然后多个子线程从队列中消费，进行数据转换及插入到新数据库（mysql）中，主线程判断队列中的数目如果大于某个值，就进行sleep，防止因子线程消费不过来而导致内存溢出。

       测试中发现，开始的一段时间内运行迁移代码的服务器cpu使用率非常高，基本上在100%以上（2个CPU的服务器），看日志也可以看得出主线程生产得够快，子线程忙不过来。可过了一段时间后，运行迁移代码的服务器cpu使用率明显降下来，看日志发现，子线程基本上没事做，而主线程却好像被阻塞了一样，几秒钟才出来一个日志。我意识到分页可能有性能问题了，我把迁移程序停掉，直接拿上边的sql语句到sqlplus执行查找SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM < 1000) WHERE RN >= 0，发现速度非常快，几十毫秒就出来了，可当我再次执行SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM < 1001000) WHERE RN >= 1000000，同样是取1000条记录，时间却差不多要1秒，我还测试了几次，发现分页下标值越大，花费时间越多。这个是无法忍受的。必须要使用另一种分页方式。

     我想到了用表的主键，因为表的主键是整型的，递增惟一，并且有索引。即分页语句变成SELECT * FROM TABLE_NAME WHERE ID >=? AND ID < ?。这回我先在sqlplus执行，发现不管id从多少开始，取1000条记录的时间基本上是一致的，都是在几十毫钞内。于是我修改了迁移代码中主线程分页的方式，后来的迁移也证明我的想法是对的。

     我还测试了一下mysql的分页（limit ?,?）的性能，在同样数量级别的表中，同样取1000条记录，发现它的分页性能比oracle的还要差，当分页下标到百万级别时，要10多秒才出来一个结果。但是如果用主键分页的方式则不存在此问题。

评论

3 楼 wangxinpier 2009-10-17  

你好，我也刚我做这部分内容，可以 交流一下吗，我正好有几个问题想请教一下，
我的QQ：303183986
MSN：[e-mail]
现用邮箱：[e-mail]
谢谢

2 楼 jwin 2009-02-02  

yiwujiaoyu 写道

SELECT * FROM TABLE_NAME WHERE ID &gt;=? AND ID &lt; ? 你这是 按照主键来查询的 实际上 有时候，主键并不是连续自增的，这时在分页中，按照id来查，不好判断 到底是查出了多少条

你说的分页指的是查询的分页,需要精确查出当前页的条目数,所以是不能用id的.而我这边的分页是为了不想将整个表的数据一下子全部查出来(数据量太大),而是通过id进行分页的方式,分批查出来.

1 楼 yiwujiaoyu 2009-02-01  

SELECT * FROM TABLE_NAME WHERE ID >=? AND ID < ?
你这是 按照主键来查询的
实际上 有时候，主键并不是连续自增的，这时在分页中，按照id来查，不好判断
到底是查出了多少条