Skills for improving MySQL database query efficiency (3)

Use the connection pool to manage connections.

In the database design with a large number of nodes to access, it is often necessary to use a connection pool to manage all the connections.

The general method is to create two connection handle queues, idle waiting queues and used queues.

To query, first obtain a handle from the idle queue, insert it to the queue in use, and then use the handle for database operations. After the query, delete it from the queue, then insert it to the idle queue.

The design code is as follows:

Reference content is as follows:

// Define the handle queue
Typedef std: list <MYSQL *> CONNECTION_HANDLE_LIST;
Typedef std: list <MYSQL *>: iterator CONNECTION_HANDLE_LIST_IT;

// Parameter structure for database connection
Class CDBParameter

{
Public:
Char * host; // Char * user; // <user Name
Char * password; // <password
Char * database; // <database Name
Unsigned int port; // <port, usually 0
Const char * unix_socket; // <socket, usually NULL
Unsigned int client_flag; // <generally 0
};

// Create two queues
CONNECTION_HANDLE_LIST m_lsBusyList; // <connection handle in use
CONNECTION_HANDLE_LIST m_lsIdleList; // <unused connection handle

// All connection handles are first connected to the database and added to the idle queue for use.
Bool CDBManager: Connect (char * host/* = "localhost" */, char * user/* = "chenmin "*/,\
Char * password/* = "chenmin" */, char * database/* = "HostCache "*/)
{
CDBParameter * lpDBParam = new CDBParameter ();
LpDBParam-> host = host;
LpDBParam-> user = user;
LpDBParam-> password = password;
LpDBParam-> database = database;
LpDBParam-> port = 0;
LpDBParam-> unix_socket = NULL;
LpDBParam-> client_flag = 0;
Try
{
// Connection
For (int index = 0; index <CONNECTION_NUM; index ++)
{
MYSQL * pConnectHandle = mysql_init (MYSQL *) 0); // initialize the connection handle
If (! Mysql_real_connect (pConnectHandle, lpDBParam-> host, lpDBParam-> user, lpDBParam-> password ,\
LpDBParam-> database, lpDBParam-> port, lpDBParam-> unix_socket, lpDBParam-> client_fla ))
Return false;
// Add to the idle queue
M_lsIdleList.push_back (pConnectHandle );
}
}
Catch (...)
{
Return false;
}
Return true;
}

// Extract an idle handle for use
MYSQL * CDBManager: GetIdleConnectHandle ()
{
MYSQL * pConnectHandle = NULL;
M_ListMutex.acquire ();
If (m_lsIdleList.size ())
{
PConnectHandle = m_lsIdleList.front ();
M_lsIdleList.pop_front ();
M_lsBusyList.push_back (pConnectHandle );
}
Else // In special cases, the idle queue is empty, and the return value is empty.
{
PConnectHandle = 0;
}
M_ListMutex.release ();

Return pConnectHandle;
}

// Release a used handle from the queue and insert it to the idle queue
Void CDBManager: SetIdleConnectHandle (MYSQL * connecthandle)
{
M_ListMutex.acquire ();
M_lsBusyList.remove (connecthandle );
M_lsIdleList.push_back (connecthandle );
M_ListMutex.release ();
}
// Usage example: first obtain the idle handle, use this handle for real operations, and then insert it back to the idle queue.
Bool CDBManager: DeleteHostCacheBySessionID (char * sessionid)
{
MYSQL * pConnectHandle = GetIdleConnectHandle ();
If (! PConnectHandle)
Return 0;
Bool bRet = DeleteHostCacheBySessionID (pConnectHandle, sessionid );
SetIdleConnectHandle (pConnectHandle );
Return bRet;
}
// Input an idle handle for real Deletion
Bool CDBManager: DeleteHostCacheBySessionID (MYSQL * connecthandle, char * sessionid)
{
Char deleteSQL [SQL _LENGTH];
Memset (deleteSQL, 0, sizeof (deleteSQL ));
Sprintf (deleteSQL, "delete from HostCache where SessionID = '% S'", sessionid );
If (mysql_query (connecthandle, deleteSQL )! = 0) // Delete
Return false;
Return true;
}