我们正在对 MySQL 进行一个小型基准测试，希望了解它对我们的数据的执行情况。
该测试的一部分是看看当多个并发线程用各种查询攻击服务器时它是如何工作的。
MySQL 文档 (5.0) 并不清楚多线程客户端。我应该指出，我确实链接了线程安全库（libmysqlclient_r.so），
我使用准备好的语句并执行读取（SELECT）和写入（UPDATE、INSERT、DELETE）。
- 我应该为每个线程打开一个连接吗？如果是这样：我该怎么做......似乎 mysql_real_connect() 会返回我调用 mysql_init() 时得到的原始数据库句柄）
- 如果不是：如何我是否确保诸如 mysql_affected_rows 之类的结果和方法返回正确的值，而不是与其他线程的调用发生冲突（互斥/锁可以工作，但感觉不对）
We're doing a small benchmark of MySQL where we want to see how it performs for our data.
Part of that test is to see how it works when multiple concurrent threads hammers the server with various queries.
The MySQL documentation (5.0) isn't really clear about multi threaded clients. I should point out that I do link against the thread safe library (libmysqlclient_r.so)
I'm using prepared statements and do both read (SELECT) and write (UPDATE, INSERT, DELETE).
- Should I open one connection per thread? And if so: how do I even do this.. it seems
mysql_real_connect() returns the original DB handle which I got when I called mysql_init())
- If not: how do I make sure results and methods such as
mysql_affected_rows returns the correct value instead of colliding with other thread's calls (mutex/locks could work, but it feels wrong)
发布评论
评论（4）
作为一个相当大的 C 应用程序的维护者，该应用程序从多个线程进行 MySQL 调用，我可以说我在每个线程中简单地建立一个新连接没有任何问题。我遇到的一些警告：
libmysqlclient_r。mysql_library_init()（一次，来自main()）。阅读有关在多线程环境中使用的文档，了解为什么有必要。MYSQL结构>mysql_init() 在每个线程中。这会产生调用mysql_thread_init() 的副作用为您服务。mysql_real_connect()像往常一样在每个线程中使用其特定于线程的 MYSQL 结构。mysql_thread_end()在每个线程的末尾（以及main()末尾的mysql_library_end()）。无论如何，这是一个很好的做法。基本上，不要共享 MYSQL 结构或任何特定于该结构创建的内容（即 MYSQL_STMT），它将按您的预期工作。
对我来说，这似乎比创建连接池要少。
As maintainer of a fairly large C application that makes MySQL calls from multiple threads, I can say I've had no problems with simply making a new connection in each thread. Some caveats that I've come across:
libmysqlclient_r.mysql_library_init()(once, frommain()). Read the docs about use in multithreaded environments to see why it's necessary.MYSQLstructure usingmysql_init()in each thread. This has the side effect of callingmysql_thread_init()for you.mysql_real_connect()as usual inside each thread, with its thread-specific MYSQL struct.mysql_thread_end()at the end of each thread (andmysql_library_end()at the end ofmain()). It's good practice anyway.Basically, don't share
MYSQLstructs or anything created specific to that struct (i.e.MYSQL_STMTs) and it'll work as you expect.This seems like less work than making a connection pool to me.
您可以创建一个连接池。每个需要连接的线程都可以从池中请求一个空闲连接。如果没有可用的连接，那么您要么阻止，要么通过向池添加新连接来扩大池。
有一篇文章这里描述了连接池的优点和缺点（虽然它是基于java的）
编辑：这是一个关于C 中的连接池
Edit2：这是示例的链接 MySQL 连接池 用 C++ 编写。 （当您实现自己的语句时，您可能应该忽略 goto 语句。）
You could create a connection pool. Each thread that needs a connection could request a free one from the pool. If there's no connection available then you either block, or grow the pool by adding a new connection to it.
There's an article here describing the pro's and cons of a connection pool (though it is java based)
Edit: Here's a SO question / answer about connection pools in C
Edit2: Here's a link to a sample Connection Pool for MySQL written in C++. (you should probably ignore the goto statements when you implement your own.)
从 mySQL 文档中我可以清楚地看出，任何特定的 MYSQL 结构都可以毫无困难地在线程中使用 - 同时在不同线程中使用相同 MYSQL 结构显然会给你带来极其不可预测的结果，因为状态是存储在 MYSQL 连接内。
因此，要么为每个线程创建一个连接，要么使用上面建议的连接池，并使用某种互斥体保护对该池的访问（即保留或释放连接）。
Seems clear to me from the mySQL Docs that any specific MYSQL structure can be used in a thread without difficulty - using the same MYSQL structure in different threads simultaneously is clearly going to give you extremely unpredictable results as state is stored within the MYSQL connection.
Thus either create a connection per thread or used a pool of connections as suggested above and protect access to that pool (i.e. reserving or releasing a connection) using some kind of Mutex.
C 语言的 MySQL 线程客户端
它指出 mysql_real_connect( ) 默认情况下不是线程安全的。需要编译客户端库以进行线程访问。
MySQL Threaded Clients in C
It states that mysql_real_connect() is not thread safe by default. The client library needs to be compiled for threaded access.