Python和MYSQL性能: Writing a massive number of SQL query results to file

Question

我有一个文件，其中每一行都包含一个值字典，我抓取并使用它来使用每个键作为查询来查询 mysql 数据库。每个查询的结果都会放置在一个字典中，一旦生成了查询字典的所有值，该行就会被写出。

IN > foo bar someotherinfo {1: 'query_val', 2: 'query_val', 3: 'query_val'
OUT > foo bar someotherinfo 1_result 2_result 3_result

整个过程似乎有点慢，因为我每个文件执行大约 200,000 个 mysql 查询，每个样本大约有 10 个文件，总共大约 30 个样本，所以我希望加快整个过程。

我只是想知道 fileIO 是否会造成瓶颈。我不是在返回的每个结果字典后写入 line_info (foo,bar,somblah) ，而是在将这些结果批量写入文件之前将它们分块到内存中会更好吗？

或者这只是一个必须等​​待的情况......？

Example Input line and output line
INPUT
 XM_006557349.1  1       -       exon    XM_006557349.1_exon_2   10316   10534   {1: 10509:10534', 2: '10488:10508', 3: '10467:10487', 4: '10446:10466', 5: '10425:10445', 6: '10404:10424', 7: '10383:10403', 8: '10362:10382', 9: '10341:10361', 10: '10316:10340'}
OUTPUT
 XM_006557349.1  1       -       exon    XM_006557349.1_exon_2   10316   105340.7083  0.2945  0.2     0.2931  0.125   0.1154  0.2095  0.5833  0.0569  0.0508


CODE
def array_2_meth(sample,bin_type,type,cur_meth):                                        
        bins_in = open('bin_dicts/'+bin_type,'r')
        meth_out = open('meth_data/'+bin_type+'_'+sample+'_plus_'+type+'_meth.tsv','w')                                                                                           
        for line in bins_in.readlines():
                meth_dict = {}                                                                                                                                                                       
                # build array of data from each line
                array = line.strip('\n').split('\t')                                                                                                                             
                mrna_id = array[0]
                assembly = array[1] 
                strand = array[2]
                bin_dict = ast.literal_eval(array[7]) 
                for bin in bin_dict:
                        coords = bin_dict[bin].split(':')
                        start = int(coords[0]) -1
                        end = int(coords[1]) +1
                        cur_meth.execute('select sum(mc)/sum(h) from allc_'+str(sample)+'_'+str(assembly) + ' where strand = \'' +str(strand) +'\' and class = \''+str(type)+'\' and position between '+str(start)+' and ' +str(end) + ' and h >= 5')
                        for row in cur_meth.fetchall():
                                if str(row[0]) == 'None':
                                         meth_dict[bin] = 'no_cov'
                                else:
                                         meth_dict[bin] = float(row[0])
                 meth_out.write('\t'.join(array[:7]))
                 for k in sorted(meth_dict.keys()):
                        meth_out.write('\t'+str(meth_dict[k]))
                 meth_out.write('\n')
        meth_out.close()  

不确定添加此代码是否会有很大帮助，但它应该显示我处理此问题的方式。您可以就我在方法中犯的错误或如何优化的提示提供任何建议将不胜感激!

谢谢^_^

Answer 1

我认为 fileIO 不应该花费太长时间，主要瓶颈可能是您正在进行的查询量。但从您提供的示例中，我看不到这些开始和结束位置的模式，因此我不知道如何减少您正在进行的查询量。

根据你的测试结果，我有一个可能令人惊奇或愚蠢的想法。(而且我不知道关于Python的shxt，所以忽略语法哈哈)

似乎每个查询都只会返回一个值？也许你可以尝试类似的事情

SQL = ''
for bin in bin_dict:
    coords = bin_dict[bin].split(':')
    start = int(coords[0]) -1
    end = int(coords[1]) +1
    SQL += 'select sum(mc)/sum(h) from allc_'+str(sample)+'_'+str(assembly) + ' where strand = \'' +str(strand) +'\' and    class = \''+str(type)+'\' and position between '+str(start)+' and ' +str(end) + ' and h >= 5'
    SQL += 'UNION ALL'
    //somehow remove the last UNION ALL at end of loop

cur_meth.execute(str(SQL))
for row in cur_meth.fetchall():
    //loop through the 10 row array and write to file

核心思想是使用 UNION ALL 将所有查询连接为 1，因此您只需要执行 1 个事务，而不是示例中显示的 10 个事务。您还可以将 10 个写入文件操作减少为 1 个。可能的缺点是 UNION ALL 可能会很慢，但据我所知，它不应该比 10 个单独的查询花费更多的处理时间正如您在我的示例中保留 SQL 格式一样。

第二个明显的方法是多线程。如果您没有使用机器的所有处理能力，您可能会尝试同时启动多个脚本/程序，因为您所做的只是查询数据并且不修改任何内容。这会导致单个脚本稍微慢一些，但总体上更快，因为它应该减少查询之间的等待时间。