多线程数据库读取访问 [英] Multi-threaded database read access

问题描述

我有一个从数据库(Oracle)读写的进程(c++代码).但过程需要很长时间才能完成.

I have a process(c++ code) that reads and writes from database (Oracle). But it takes long time for process to finish.

我想在这个进程查询的表中创建分区.然后使进程多线程化，以便每个线程(每个分区一个)可以并行读取/写入数据.

I was thinking of creating partitions in the tables that this process queries. And then making the process multi-threaded so that each thread(one for each partition) can read/write the data in parallel.

我将为每个线程创建一个数据库连接.

I will be creating a DB connection per thread.

编写会减慢它的速度吗?
这会起作用吗?有没有其他提高性能的方法(所有查询都已经过调整和优化)?

Will write slow it down?
Will this work? Is there any other way of improving performance (all queries are tuned and optimized already)?

谢谢，尼基尔

推荐答案

如果当前瓶颈是将数据写入数据库，那么创建更多线程来写入更多数据可能有帮助，也可能无济于事，具体取决于数据的分区方式，以及写入是否可以并发发生，或者它们是否相互干扰(在数据库锁级别，或在数据库磁盘 IO 级别).

If the current bottleneck is writing the data to the database then creating more threads to write more data may or may not help, depending on how the data is partitioned, and whether or not the writes can occur concurrently, or whether they interfere with each other (either at the database lock level, or at the database disk IO level).

如果有足够的硬件并发(例如在多核机器上)来处理额外的线程，那么创建更多线程将允许应用程序处理更多数据，并将其排队以写入数据库.

Creating more threads will instead allow the application to process more data, and queue it up for writing to the database, assuming that there is sufficient hardware concurrency (e.g. on a multicore machine) to handle the additional threads.

分区可能会提高数据库性能，因为可能会更改相关表上的索引.如果您可以将单独的分区放在单独的物理磁盘上，那么当给定的 SQL 语句只需要访问一个分区时，这可以提高 IO.

Partitioning may improve the database performance, as may changing the indexes on the relevant tables. If you can put separate partitions on separate physical disks then that can improve IO when only one partition needs to be accessed by a given SQL statement.

删除不需要的索引，更改索引列的顺序以匹配查询，甚至更改索引类型也可以提高性能.

Dropping indexes that aren't needed, changing the order of index columns to match the queries, and even changing the index type can also improve performance.

与所有内容一样:在每次提议的更改之前和之后对其进行分析.

As with everything: profile it before and after every proposed change.

这篇关于多线程数据库读取访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！