[大数据]从数据库读取大量数据的方式对比

数据库读取大量数据方式

当从数据库里读大量的数据时，容易导致连接异常或者程序内存崩溃。
下文将对比"直接读取",“分页读取”,"游标读取"等方式的性能和操作方式。
游标读取可以参照：python中数据库游标的使用总结

性能比较

编程语言：python

使用的库： psycopg2

数据库：postgresql

数据量: 接近900w条

直接全部读取：

语句：

select * from xxxx 

代码：

 with psycopg2_conn.cursor() as cursor:  # 获取数据库连接的对象
            first_time = time.time()
            sql_script = sql_script.strip(" ").strip(";")
            print(u'执行查询前的内存使用：%.4f GB' % (psutil.Process(os.getpid()).memory_info().rss / 1024 / 1024 / 1024))
            cursor.execute(sql_script)
            rows = cursor.fetchall()
            print("总的查询耗时: {:.6f}秒".format(time.time() - first_time),
                  '查询后内存使用：%.4f GB' % (psutil.Process(os.getpid()).memory_info().rss / 1024 / 1024 / 1024))

可以看到查询所消耗的时间和程序占用的内存爆炸,内存使用超过10G，耗时也来到了80s。

分页读取：

语句：

select * from xxxx  limit {count} OFFSET {page * count}

代码：

        with psycopg2_conn.cursor() as cursor:  # 获取数据库连接的对象
            first_time = time.time()
            sql_script = sql_script.strip(" ").strip(";")
            print(u'执行查询前的内存使用：%.4f GB' % (
                psutil.Process(os.getpid()).memory_info().rss / 1024 / 1024 / 1024))
            while True:
                start_time = time.time()
                current_sql_script = f"{sql_script} limit {count} OFFSET {page * count}"
                cursor.execute(current_sql_script)
                rows = cursor.fetchall()
                print( f'limit:{count},OFFSET:{page * count}',
                       "本次查询耗时: {:.6f}秒".format(time.time() - start_time),
                       "总的查询耗时: {:.6f}秒".format(time.time() - first_time),
                       '目前内存使用：%.4f GB' % (psutil.Process(os.getpid()).memory_info().rss / 1024 / 1024 / 1024))
                if len(rows) == count:
                    page += 1
                    continue
                else:
                    break

一开始的情况：

查询至500w数据时：可以观察到单次查询的耗时明显上升，但内存使用情况正常

查询至最后的数据时，单次查询耗时已经超过1秒，总耗时也已经达到532秒。

原因：当查询500w处的后1w条数据时，数据库会先查出501w的数据，然后丢掉500w，返回剩下的1w条数据。这里优化可以考虑利用索引or条件，具体可以谷歌搜下优化方式。

使用游标读取：

代码:

def test_database_time(self):
    with psycopg2_conn.cursor(name='my_test_cursor') as cursor:  # 获取数据库连接的对象
        first_time = time.time()
        sql_script = sql_script.strip(" ").strip(";")
        print(u'执行查询前的内存使用：%.4f GB' % (psutil.Process(os.getpid()).memory_info().rss / 1024 / 1024 / 1024))
        cursor.execute(sql_script)
        rows = cursor.fetchmany(10000)
        count = len(rows)
        while len(rows) == 10000:
            start_time = time.time()
            rows = cursor.fetchmany(10000)
            count += len(rows)
            print( f'获取总数:{count}',
                   "本次查询耗时: {:.6f}秒".format(time.time() - start_time),
                   "总的查询耗时: {:.6f}秒".format(time.time() - first_time),
                   '目前内存使用：%.4f GB' % (psutil.Process(os.getpid()).memory_info().rss / 1024 / 1024 / 1024))

一开始情况:

查询至500w数据时：单次查询耗时无明显变化

查询至最后数据时，单次查询耗时依旧保持稳定，总的耗时略微超过“直接全部读取的方式”

大致原理：这里数据库会保留一个类似于游标的东西，当查询500w处的后1w条数据时，游标正处于500w处，可以直接获取到接下来的1w条数据，所以单次查询耗时依旧很快。这种也是有对应的缺点的，可能会影响数据库本身的内存以及其他查询链接。 这里具体的实现方式得看数据库的类型以及我们编程语言中的操作方式，网上众说纷纭，这里只做大致原理的解析。