Skills for improving MySQL database query efficiency (2)

ImproveMySQL database query efficiencyIn the previous article, we have introduced one of the methods, that is, techniques for improving the query efficiency of MySQL databases ), we will continue to introduce how to improve the query efficiency of MySQL databases.

Random acquisition records

In some database applications, we do not need to obtain all the records that meet the conditions, but just randomly select the records that meet the conditions. this situation is common in the statistical analysis of data services, where a small amount of data is obtained from large-capacity databases.

There are two ways to do this:

1. The conventional method first queries all records that meet the conditions, and then selects some records randomly. This method does not work well when many records meet the conditions.

2. use the limit syntax to obtain the number of records that meet the conditions first, and then add limit to the SQL query statement to query only the records that meet the requirements. although this method needs to be queried twice, it is more efficient when the data volume is large.

The sample code is as follows:

Reference content is as follows:

// 1. General Method
// Performance bottleneck. When there are 0.1 million records, the query is performed for 140 ms, and the result set is obtained for 500 ms. Others 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) // search
Return 0;
// Obtain the result set
M_pResultSet = mysql_store_result (connecthandle );
If (! M_pResultSet) // An error occurred while obtaining the result set.
Return 0;
Int iAllNumRows = (int) (mysql_num_rows (m_pResultSet); // <Number of all 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)
{
// Obtain records one by one
For (int I = 0; I <iReturnNumRows; I ++)
{
// Obtain fields one by one
M_Row = mysql_fetch_row (m_pResultSet );
If (m_Row [0]! = NULL)
Strcpy (hostcache. sessionid, m_Row [0]);
If (m_Row [1]! = NULL)
Strcpy (hostcache. channelid, m_Row [1]);
If (m_Row [2]! = NULL)
Hostcache. ISPtype = atoi (m_Row [2]);
If (m_Row [3]! = NULL)
Hostcache. externalIP = atoi (m_Row [3]);
If (m_Row [4]! = NULL)
Hostcache. externalPort = atoi (m_Row [4]);
If (m_Row [5]! = NULL)
Hostcache. internalIP = atoi (m_Row [5]);
If (m_Row [6]! = NULL)
Hostcache. internalPort = atoi (m_Row [6]);
}
}
Else
{
// Randomly select a specified record to return
Int iRemainder = iAllNumRows % iReturnNumRows; // <Remainder
Int iQuotient = iAllNumRows/iReturnNumRows; // <Operator
Int iStartIndex = rand () % (iRemainder + 1); // <start subscript

// Obtain records one by one
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 content
Mysql_free_result (m_pResultSet );
Return iReturnNumRows;
}

// 2. Use limit
Int CDBManager: QueryHostCache (MYSQL * connecthandle, char * channelid, unsigned int myexternalip, int ISPtype, CHostCacheTable * hostcache)
{
// Obtain the number of records that meet the results, and then use limit to randomly select a 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) // search
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 );
// Calculate the upper and lower ranges of the 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 for 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) // search
Return 0;
PResultSet = mysql_store_result (connecthandle );
If (! PResultSet)
Return 0;
// Obtain records one by one
For (int I = 0; I <iReturnNumRows; I ++)
{
// Obtain fields one by one
Row = mysql_fetch_row (pResultSet );
If (row [0]! = NULL)
Strcpy (hostcache. sessionid, row [0]);
If (row [1]! = NULL)
Hostcache. externalIP = atoi (row [1]);
If (row [2]! = NULL)
Hostcache. externalPort = atoi (row [2]);
If (row [3]! = NULL)
Hostcache. internalIP = atoi (row [3]);
If (row [4]! = NULL)
Hostcache. internalPort = atoi (row [4]);
}
// Release the result set content
Mysql_free_result (pResultSet );
Return iReturnNumRows;
}

The above is mainly in the form of code. It may not be easy for beginners who are just getting started, but I hope you can understand it carefully, how can this method be applied to practical work to improve work efficiency?