mysql database queries optimize mysql efficiency
- 2020-05-06 11:45:32
- OfStack
Three tips for improving the query efficiency of MySQL summarize
MySQL due to its small and efficient operation, more and more used in the database application. When I was in a P2P application used MySQL to save P2P node, due to the application of P2P, quick tens of thousands of nodes, and nodes change frequently, so be sure to keep the query and insert efficient. The following is in use process I do attempt to improve the efficiency of three effective.
l USES statement for binding query
statement can be used to build the query syntax tree in advance, so it is no longer necessary to build the syntax tree when the query is made Usage:
Bind, creates an MYSQL_STMT variable bound to the corresponding query string. The question mark in the string represents the variable to be passed in, and a variable must be specified for each question mark Query, input for each specified variable, pass in MYSQL_STMT variable with the available connection handle.
The code is as follows:
random acquisition of records
In some database applications, we do not obtain all the records that meet the criteria, but simply select the records that meet the criteria at random has two ways to do
general method first looks up all the records that satisfy the condition and then selects some records at random USES limit syntax to obtain the number of records that meet the criteria, then adds limit to the sql query statement to limit the number of records that meet the requirements The sample code is as follows:
USES connection pooling to manage connections.
In a database design with lots of node access, it is common to use the connection pool to manage all connections.
The general approach is to create two connection handle queues, an idle queue waiting to be used and an in-use queue.
When the query to get a handle from the free queue, insert into the queue in use, and then use this handle to do database operations, after the completion of the use of the queue must be deleted, and then inserted into the free queue.
The design code is as follows:
MySQL due to its small and efficient operation, more and more used in the database application. When I was in a P2P application used MySQL to save P2P node, due to the application of P2P, quick tens of thousands of nodes, and nodes change frequently, so be sure to keep the query and insert efficient. The following is in use process I do attempt to improve the efficiency of three effective.
l USES statement for binding query
statement can be used to build the query syntax tree in advance, so it is no longer necessary to build the syntax tree when the query is made Usage:
Bind, creates an MYSQL_STMT variable bound to the corresponding query string. The question mark in the string represents the variable to be passed in, and a variable must be specified for each question mark Query, input for each specified variable, pass in MYSQL_STMT variable with the available connection handle.
The code is as follows:
//1. The binding
bool CDBManager::BindInsertStmt(MYSQL * connecthandle)
{
// Bind for the insert operation
MYSQL_BIND insertbind[FEILD_NUM];
if(m_stInsertParam == NULL)
m_stInsertParam = new CHostCacheTable;
m_stInsertStmt = mysql_stmt_init(connecthandle);
// Build binding string
char insertSQL[SQL_LENGTH];
strcpy(insertSQL, "insert into HostCache(SessionID, ChannelID, ISPType, "
"ExternalIP, ExternalPort, InternalIP, InternalPort) "
"values(?, ?, ?, ?, ?, ?, ?)");
mysql_stmt_prepare(m_stInsertStmt, insertSQL, strlen(insertSQL));
int param_count= mysql_stmt_param_count(m_stInsertStmt);
if(param_count != FEILD_NUM)
return false;
// fill bind Structure of the array , m_sInsertParam Is this statement Associated structure variables
memset(insertbind, 0, sizeof(insertbind));
insertbind[0].buffer_type = MYSQL_TYPE_STRING;
insertbind[0].buffer_length = ID_LENGTH /* -1 */;
insertbind[0].buffer = (char *)m_stInsertParam->sessionid;
insertbind[0].is_null = 0;
insertbind[0].length = 0;
insertbind[1].buffer_type = MYSQL_TYPE_STRING;
insertbind[1].buffer_length = ID_LENGTH /* -1 */;
insertbind[1].buffer = (char *)m_stInsertParam->channelid;
insertbind[1].is_null = 0;
insertbind[1].length = 0;
insertbind[2].buffer_type = MYSQL_TYPE_TINY;
insertbind[2].buffer = (char *)&m_stInsertParam->ISPtype;
insertbind[2].is_null = 0;
insertbind[2].length = 0;
insertbind[3].buffer_type = MYSQL_TYPE_LONG;
insertbind[3].buffer = (char *)&m_stInsertParam->externalIP;
insertbind[3].is_null = 0;
insertbind[3].length = 0;
insertbind[4].buffer_type = MYSQL_TYPE_SHORT;
insertbind[4].buffer = (char *)&m_stInsertParam->externalPort;
insertbind[4].is_null = 0;
insertbind[4].length = 0;
insertbind[5].buffer_type = MYSQL_TYPE_LONG;
insertbind[5].buffer = (char *)&m_stInsertParam->internalIP;
insertbind[5].is_null = 0;
insertbind[5].length = 0;
insertbind[6].buffer_type = MYSQL_TYPE_SHORT;
insertbind[6].buffer = (char *)&m_stInsertParam->internalPort;
insertbind[6].is_null = 0;
insertbind[6].is_null = 0;
// The binding
if (mysql_stmt_bind_param(m_stInsertStmt, insertbind))
return false;
return true;
}
//2. The query
bool CDBManager::InsertHostCache2(MYSQL * connecthandle, char * sessionid, char * channelid, int ISPtype, \
unsigned int eIP, unsigned short eport, unsigned int iIP, unsigned short iport)
{
// Fill structure variable m_sInsertParam
strcpy(m_stInsertParam->sessionid, sessionid);
strcpy(m_stInsertParam->channelid, channelid);
m_stInsertParam->ISPtype = ISPtype;
m_stInsertParam->externalIP = eIP;
m_stInsertParam->externalPort = eport;
m_stInsertParam->internalIP = iIP;
m_stInsertParam->internalPort = iport;
// perform statement, Performance bottleneck
if(mysql_stmt_execute(m_stInsertStmt))
return false;
return true;
}
random acquisition of records
In some database applications, we do not obtain all the records that meet the criteria, but simply select the records that meet the criteria at random has two ways to do
general method first looks up all the records that satisfy the condition and then selects some records at random USES limit syntax to obtain the number of records that meet the criteria, then adds limit to the sql query statement to limit the number of records that meet the requirements The sample code is as follows:
//1. Conventional method
// Performance bottleneck ,10 Ten thousand recording hours , Execute the query 140ms, Get the result set 500ms, The rest can be ignored.
int CDBManager::QueryHostCache(MYSQL* connecthandle, char * channelid, int ISPtype, CDBManager::CHostCacheTable * &hostcache)
{
char selectSQL[SQL_LENGTH];
memset(selectSQL, 0, sizeof(selectSQL));
sprintf(selectSQL,"select * from HostCache where ChannelID = '%s' and ISPtype = %d", channelid, ISPtype);
if(mysql_real_query(connecthandle, selectSQL, strlen(selectSQL)) != 0) // retrieve
return 0;
// Get the result set
m_pResultSet = mysql_store_result(connecthandle);
if(!m_pResultSet) // Error getting result set
return 0;
int iAllNumRows = (int)(mysql_num_rows(m_pResultSet)); ///< Total number of search results
// Calculate the number of results to be returned
int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM)? iAllNumRows:RETURN_QUERY_HOST_NUM;
if(iReturnNumRows <= RETURN_QUERY_HOST_NUM)
{
// Get itemized records
for(int i = 0; i<iReturnNumRows; i++)
{
// Get field by field
m_Row = mysql_fetch_row(m_pResultSet);
if(m_Row[0] != NULL)
strcpy(hostcache[i].sessionid, m_Row[0]);
if(m_Row[1] != NULL)
strcpy(hostcache[i].channelid, m_Row[1]);
if(m_Row[2] != NULL)
hostcache[i].ISPtype = atoi(m_Row[2]);
if(m_Row[3] != NULL)
hostcache[i].externalIP = atoi(m_Row[3]);
if(m_Row[4] != NULL)
hostcache[i].externalPort = atoi(m_Row[4]);
if(m_Row[5] != NULL)
hostcache[i].internalIP = atoi(m_Row[5]);
if(m_Row[6] != NULL)
hostcache[i].internalPort = atoi(m_Row[6]);
}
}
else
{
// Randomly selected specified bar records are returned
int iRemainder = iAllNumRows%iReturnNumRows; ///< remainder
int iQuotient = iAllNumRows/iReturnNumRows; ///< shang
int iStartIndex = rand()%(iRemainder + 1); ///< Start the subscript
// Get itemized records
for(int iSelectedIndex = 0; iSelectedIndex < iReturnNumRows; iSelectedIndex++)
{
mysql_data_seek(m_pResultSet, iStartIndex + iQuotient * iSelectedIndex);
m_Row = mysql_fetch_row(m_pResultSet);
if(m_Row[0] != NULL)
strcpy(hostcache[iSelectedIndex].sessionid, m_Row[0]);
if(m_Row[1] != NULL)
strcpy(hostcache[iSelectedIndex].channelid, m_Row[1]);
if(m_Row[2] != NULL)
hostcache[iSelectedIndex].ISPtype = atoi(m_Row[2]);
if(m_Row[3] != NULL)
hostcache[iSelectedIndex].externalIP = atoi(m_Row[3]);
if(m_Row[4] != NULL)
hostcache[iSelectedIndex].externalPort = atoi(m_Row[4]);
if(m_Row[5] != NULL)
hostcache[iSelectedIndex].internalIP = atoi(m_Row[5]);
if(m_Row[6] != NULL)
hostcache[iSelectedIndex].internalPort = atoi(m_Row[6]);
}
}
// Release the result set contents
mysql_free_result(m_pResultSet);
return iReturnNumRows;
}
//2. use limit version
int CDBManager::QueryHostCache(MYSQL * connecthandle, char * channelid, unsigned int myexternalip, int ISPtype, CHostCacheTable * hostcache)
{
// First, get the number of bars that satisfy the result , To use limit Randomly select the specified record to return
MYSQL_ROW row;
MYSQL_RES * pResultSet;
char selectSQL[SQL_LENGTH];
memset(selectSQL, 0, sizeof(selectSQL));
sprintf(selectSQL,"select count(*) from HostCache where ChannelID = '%s' and ISPtype = %d", channelid, ISPtype);
if(mysql_real_query(connecthandle, selectSQL, strlen(selectSQL)) != 0) // retrieve
return 0;
pResultSet = mysql_store_result(connecthandle);
if(!pResultSet)
return 0;
row = mysql_fetch_row(pResultSet);
int iAllNumRows = atoi(row[0]);
mysql_free_result(pResultSet);
// Calculates the upper and lower ranges of records to be retrieved
int iLimitLower = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
0:(rand()%(iAllNumRows - RETURN_QUERY_HOST_NUM));
int iLimitUpper = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
iAllNumRows:(iLimitLower + RETURN_QUERY_HOST_NUM);
// Calculate the number of results to be returned
int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
iAllNumRows:RETURN_QUERY_HOST_NUM;
// use limit As the query
sprintf(selectSQL,"select SessionID, ExternalIP, ExternalPort, InternalIP, InternalPort "
"from HostCache where ChannelID = '%s' and ISPtype = %d limit %d, %d"
, channelid, ISPtype, iLimitLower, iLimitUpper);
if(mysql_real_query(connecthandle, selectSQL, strlen(selectSQL)) != 0) // retrieve
return 0;
pResultSet = mysql_store_result(connecthandle);
if(!pResultSet)
return 0;
// Get itemized records
for(int i = 0; i<iReturnNumRows; i++)
{
// Get field by field
row = mysql_fetch_row(pResultSet);
if(row[0] != NULL)
strcpy(hostcache[i].sessionid, row[0]);
if(row[1] != NULL)
hostcache[i].externalIP = atoi(row[1]);
if(row[2] != NULL)
hostcache[i].externalPort = atoi(row[2]);
if(row[3] != NULL)
hostcache[i].internalIP = atoi(row[3]);
if(row[4] != NULL)
hostcache[i].internalPort = atoi(row[4]);
}
// Release the result set contents
mysql_free_result(pResultSet);
return iReturnNumRows;
}
USES connection pooling to manage connections.
In a database design with lots of node access, it is common to use the connection pool to manage all connections.
The general approach is to create two connection handle queues, an idle queue waiting to be used and an in-use queue.
When the query to get a handle from the free queue, insert into the queue in use, and then use this handle to do database operations, after the completion of the use of the queue must be deleted, and then inserted into the free queue.
The design code is as follows:
// Define a handle queue
typedef std::list<MYSQL *> CONNECTION_HANDLE_LIST;
typedef std::list<MYSQL *>::iterator CONNECTION_HANDLE_LIST_IT;
// Parameter structure for connecting to the database
class CDBParameter
{
public:
char *host; ///< The host name
char *user; ///< The user name
char *password; ///< password
char *database; ///< The database name
unsigned int port; ///< Port, generally 0
const char *unix_socket; ///< Socket, usually NULL
unsigned int client_flag; ///< As a general rule, be 0
};
// Create two queues
CONNECTION_HANDLE_LIST m_lsBusyList; ///< The connection handle in use
CONNECTION_HANDLE_LIST m_lsIdleList; ///< Unused connection handle
// All connection handles connect to the database first , Join the idle queue , Waiting 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
{
// The connection
for(int index = 0; index < CONNECTION_NUM; index++)
{
MYSQL * pConnectHandle = mysql_init((MYSQL*) 0); // Initializes 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;
// Join the idle queue
m_lsIdleList.push_back(pConnectHandle);
}
}
catch(...)
{
return false;
}
return true;
}
// Extract a free 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 // A special case , The idle queue is empty , Returns null
{
pConnectHandle = 0;
}
m_ListMutex.release();
return pConnectHandle;
}
// Releases a used handle from the use queue , Insert into the idle queue
void CDBManager::SetIdleConnectHandle(MYSQL * connecthandle)
{
m_ListMutex.acquire();
m_lsBusyList.remove(connecthandle);
m_lsIdleList.push_back(connecthandle);
m_ListMutex.release();
}
// Use the sample , First, get the free handle , Use this handle to do the actual operation , And then plug it back into the free queue
bool CDBManager::DeleteHostCacheBySessionID(char * sessionid)
{
MYSQL * pConnectHandle = GetIdleConnectHandle();
if(!pConnectHandle)
return 0;
bool bRet = DeleteHostCacheBySessionID(pConnectHandle, sessionid);
SetIdleConnectHandle(pConnectHandle);
return bRet;
}
// Pass in the idle handle , Do the actual delete
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;
}