Tips for Improving MySQL database query efficiency [required for php programmers] _ PHP Tutorial

Several tips for improving MySQL database query efficiency [required for php programmers]. Because of its small size and efficient operations, MySQL is increasingly used in database applications. I used MySQL to store P2P nodes when developing a P2P application. because of its small size and high efficiency, MySQL is increasingly used in database applications. when I was developing a P2P application, I used MySQL to store P2P nodes. because P2P applications have tens of thousands knots, and nodes change frequently, therefore, you must ensure efficient query and insertion. the following are three effective attempts to improve efficiency during use.

L
Use statement for binding query
You can use statement to build a query syntax tree in advance. you do not need to build a syntax tree to directly query a query. therefore, the query efficiency can be improved. this method is suitable for scenarios with fixed query conditions but frequent queries.
The usage is as follows:

Create a MYSQL_STMT variable and bind it to the corresponding query string. the question mark in the string indicates the variable to be passed in. each question mark must specify a variable.
Query, enter each specified variable, and input the MYSQL_STMT variable to be executed using the available connection handle.
The code is as follows:

// 1. bind
Bool CDBManager: BindInsertStmt (MYSQL * connecthandle)
{
// Insert binding
MYSQL_BIND insertbind [FEILD_NUM];
If (m_stInsertParam = NULL)
M_stInsertParam = new CHostCacheTable;
M_stInsertStmt = mysql_stmt_init (connecthandle );
// Construct the bind 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 in the bind structure array. m_sInsertParam is the structure variable associated with this statement.
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;
// Bind
If (mysql_stmt_bind_param (m_stInsertStmt, insertbind ))
Return false;
Return true;
}

// 2. 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 in the 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;
// Execute statement, performance bottleneck
If (mysql_stmt_execute (m_stInsertStmt ))
Return false;
Return true;
}

L
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:

// 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 ));/// <所有的搜索结果数
// 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 {
// 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 ;/// <余数
Int iQuotient = iAllNumRows/iReturnNumRows ;/// <商
Int iStartIndex = rand () % (iRemainder + 1 );/// <开始下标< p>

// 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 {
// 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;
}

L
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:

// Define the handle queue
Typedef std: list CONNECTION_HANDLE_LIST;
Typedef std: list : Iterator CONNECTION_HANDLE_LIST_IT;

// Parameter structure for database connection
Class CDBParameter

{
Public:
Char * host ;/// <主机名
Char * user ;/// <用户名
Char * password ;/// <密码
Char * database ;/// <数据库名
Unsigned int port ;/// <端口，一般为0
Const char * unix_socket ;/// <套接字，一般为null
Unsigned int client_flag ;/// <一般为0
};

// Create two queues
CONNECTION_HANDLE_LIST m_lsBusyList ;/// <正在使用的连接句柄
CONNECTION_HANDLE_LIST m_lsIdleList ;/// <未使用的连接句柄< p>

// 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;
}

Databases are increasingly used in database applications. I used MySQL to store P2P nodes when developing a P2P application. because of P2P applications...