记一次 excel 数据导入的性能优化

需求交待

  1. 公司业务和肿瘤相关。目前相关的肿瘤类型共35种。
  2. 有 Excele 文件,大概5万条数据。有20列左右。 导入数据库。
  3. Excel中A列为肿瘤类型列,其值在35个肿瘤类别中。
    1. A 列值为『通用』值时,需要根据35个肿瘤类型,插入35条记录。
    2. A 列值为『其它』值时,则根据『B\C\D\E\F\G\E』列的值进行组合,查询与组合值相同的肿瘤类别,与肿瘤类型库取差集,根据差集肿瘤类型,填充数据,再插入数据库中。

第一回合:从入门到放弃

  1. 使用 Laravel 的 Exeel处理库,maatwebsite/excel ,很不幸运,最新版的 3.0 不支持读取,Fuck

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    2. 使用原始的Exel处理库,phpoffice/phpspreadsheet,php最强大的 excel 类库,没有之一。
    3. 读取 100 条 xlsx 文件的记录,需要2秒。1K数据,近2分钟。原始的5万条数据,我是没有耐性等它读完。再次Fuck~~~『ps: 这个测试过程很累,因为 Fuck 了的缘故吗』
    4. 学习过GoLang,那试试 GoLang的处理效果,使用 ```Luxurioust/excelize``` 5w条数据,大约需要20秒,还不错,非常理想。
    5. 但导入数据还有其它业务,毕竟项目组还是PHP项目组,怎么办呢...
    6. 试了试,用GoLang把数成导出成json,PHP解析这5w数据的 json ,秒级别解析,效果还可以。
    7. 导入原始数据啦,5w多条分批次插入,本机测试3分钟,服务器1分钟完成。还是可以接受的。

    > 第一回合结束,Go 编写 excel2json 程序, win\linux\mac各一个,cnv配置路径

    ### 第二回合:xlsx到cvs

    1. 找了一个 GoLang 的 ORM gohouse/gorose,尝试插入,2秒钟完成5w数据插入,Fuck

  2. 观察到其它同事处理上百万数据的 excel 文件,后缀都是 csv,那也来试试 csv 好了

  3. PHP和GOLang都内置了 csv的解析器,这一次PHP并没有输的那么难看,5秒内都能解析完成。那还用xlsx干毛线
  4. csv 的话,需要注意编码,用 Microsoft Excel 导出,貌似是 GBK编码。用 Macos的Number导出,貌似是UTF8。当然,我们需要的是UTF8
  5. 现在爽了,挺快的了。绝对时间了,PHP也能接受(30秒左右),但GO只需要2秒啊~~~

第三回合:拆数据

  1. 需求中,『通用』的情况,在导入原始数据时,稍加处理,一并插入即可。
  2. 『其它』就比较麻烦,因为需要按值查询,所以最初的想法就是导入后,二次处理。
  3. 大概思路就是一次找出所有其它,再对每一个其它进行一次查询,一次插入。算下来,有10k的其它,一共需要10k+1次查询,10k次插入
  4. 那就干吧,写完代码,跑起来,加了进度条,估算一次本机需要3小时才能完成…,,服务器上跑,也要跑1小时…要疯了
  5. 也想用Go去写这估逻辑,试试速度,但没有写完
  6. 慢,找到慢在哪里,尝试解决吧。
    1. 批量插入,收益甚微
    2. 增加一列,保留组合查询列的摘要,将直接查摘要。这一次速度有飞的提升
  7. 最后的测试数据,服务器上从头执行,100秒内 可以完成。

总结

从最开始可能需要1到2小时执行的程序,到最后100秒,效果还是符合要求的。总结关键点,欢迎讨论噢:

  1. excel处理,尽量选择 csv 文件
  2. 数据插入,一定要批量插入
  3. 查询,特别是组合查询,创建好索引,或者特定场合使用冗余列