SQL性能优化案例：分享一次系统迁移获得的经验

云栖社区 2016-12-06 22:20

最近刚做了系统迁移，需要将老系统的数据迁移到新的系统中，老系统中是分了100张表，表的数据量分配不均匀，有些表有40G左右的数据，而有些表的数据又比较少。

刚开始处理的方式是，先获取该表数据的总量，然后进行分页处理，迁移的sql如下：

第一步：

select count(1) from some_table where type_id = #type_id#;

第二步：

select * from some_table where type_id = #type_id# limit #offset#,#pageSize#;

如果对于小名单数量在100万以内的，这个sql的性能还是挺能满足的，但是当越到后面的名单的时候，SQL性能越来越差。

原因

limit有两个参数，第一个参数是offset，第二个参数是取的数据量。当offset很大时，mysql需要扫描从头到offset的数据量，这个时间会随着offset越大而越大。

第一次优化

1. 使用id分区. 先获取最小的id，然后按照id进行过滤，取最近100条。然后下次循环的时候，重新设置id值。

2. 再次循环获取下一个100条数据。

select min(id) from some_table where type_id = #type_id#;

select * from some_table where type_id = #type_id# and id > #min# order by id asc limit #pageSize#;

3. 通过上面的优化后，在100万的名单只需要10分钟就可以查询完毕，这个优化解决了大部分的名单迁移，但是在迁移到一些表的数据仍然不行，因为增加id的排序，导致每次的排序，需要做几亿的数据的扫描：

第二次优化

因为我们的目标就是将所有的数据全部读出来，所以可以先把min和max的id取出来，然后通过id增加固定步调的方式来进行处理。

select min(id) from some_table where type_id = #typeId#

select max(id) from some_table where type_id = #typeId#

伪代码:

int pageSize = 500;

for(int i = minId; i<= maxId; i+=pageSize){

select * from some_table where type_id =#type_id# where id between i and i+ pageSize;

}

由于没有排序的操作，所以每次的取的数据范围非常小，通过执行计划也是可以看出：

总结

sql的写法不同，执行计划完全不同，作为开发人员，不能只单纯的满足业务功能就可以了，而是要想想自己写得sql会怎么执行，会走那些索引，数据量大得情况下会怎么样。并且要善于用一些工具帮助自己分析，(比如idb上得执行计划的功能就非常棒), 共勉。