MySQL Connector C++ ResultSet缓冲及多模块复用性能优化问询

Great question—handling ResultSet buffering efficiently is a huge win for C++ database frontends, especially when reusing shared pointers across modules. Let’s break down practical, actionable optimizations tailored to your setup:

1. Enable Full Client-Side Buffering (Immediate Result Fetch) #

By default, MySQL Connector/C++ uses a streaming ResultSet, which fetches rows from the server one at a time as you call next(). This is terrible for performance when reusing the ResultPtr across modules—every traversal triggers network roundtrips.

Fix this by forcing the driver to load the entire result set into client memory upfront:

// When creating your Statement/PreparedStatement
stmt->setResultSetType(sql::ResultSet::TYPE_SCROLL_INSENSITIVE);
stmt->setResultSetConcurrency(sql::ResultSet::CONCUR_READ_ONLY);

// Alternatively, use storeResult() explicitly for Statement objects
sql::ResultSet* rawResult = stmt->executeQuery("SELECT ...");
rawResult->storeResult();
ResultPtr myResultPtr(rawResult);

With full buffering, all subsequent calls to next(), getMetadata(), or column accesses are purely in-memory, eliminating network overhead entirely. Just be mindful of memory usage for extremely large result sets.

2. Cache ResultSet Metadata #

Calling myResultPtr->getMetadata() repeatedly incurs unnecessary internal overhead—each call may re-initialize metadata structures. Cache this metadata once and reuse it across modules:

// Define a cached result struct to bundle ResultSet and its metadata
typedef boost::shared_ptr<sql::ResultSetMetaData> MetaDataPtr;
struct CachedQueryResult {
    ResultPtr result;
    MetaDataPtr meta;
};

// Initialize once after fetching the result
CachedQueryResult createCachedResult(ResultPtr result) {
    return {
        result,
        MetaDataPtr(result->getMetadata())
    };
}

Now modules can access cachedResult.meta directly instead of calling getMetadata() every time.

3. Convert ResultSet to Native C++ Containers for Repeated Access #

If multiple modules need to process the same data, avoid traversing the ResultSet multiple times. Convert the results to a native container (like std::vector of custom structs) once, then reuse that container:

struct User {
    int id;
    std::string name;
};

std::vector<User> parseUsers(ResultPtr result) {
    std::vector<User> users;
    while (result->next()) {
        User u;
        u.id = result->getInt("user_id");
        u.name = result->getString("username");
        users.push_back(u);
    }
    // Reset cursor if you still need to reuse the original ResultSet
    result->beforeFirst();
    return users;
}

Native containers have faster access times than the ResultSet abstraction, and you avoid repeated type conversions and cursor operations.

4. Tune Partial Buffering for Large Datasets #

For result sets too big to load entirely into memory, use partial buffering to balance memory usage and network efficiency:

// Set a fetch size (e.g., 1000 rows per batch)
stmt->setFetchSize(1000);

This tells the driver to fetch batches of rows from the server instead of one at a time. If you need to reuse the ResultPtr across modules, note that the cursor position is shared—so either:

• Reset the cursor to the start with beforeFirst() after each module finishes, or
• Create an independent copy of the ResultSet using myResultPtr->clone() (if supported by your Connector version) so each module can traverse its own cursor without interference.

5. Avoid Thread Safety Pitfalls with boost::shared_ptr #

While boost::shared_ptr is thread-safe for reference counting, the underlying sql::ResultSet is not thread-safe. If multiple threads are accessing the same ResultPtr, you have two options:

• Clone the ResultSet for each thread using clone() to create independent instances, or
• Add a mutex around all accesses to the shared ResultPtr to prevent race conditions on the cursor.

6. Minimize Type Conversion Overhead #

Each call to getInt(), getString(), etc., involves type conversion and possibly memory allocation (e.g., for std::string). If multiple modules need the same column values, cache the converted values in your native structs (as shown in step 3) instead of calling these methods repeatedly.

内容的提问来源于stack exchange，提问作者M.Winkens