Several techniques to improve the efficiency of MySQL database query [PHP programmers will see]

MySQL is more and more used in database applications because of its small size and efficient operation. I used to develop a peer-to-peer application of the use of MySQL to save Peer-to-peer nodes, due to peer-to-peer applications, knot points are tens of thousands of times, and node changes frequently, So be sure to keep the query and insert efficient. Here are three effective attempts to improve efficiency in the course of my use.

L
Using statement to bind queries
The query syntax tree can be built in advance by using statement, and queries are no longer required to construct the syntax tree. Therefore, it can improve the efficiency of query. This method is suitable for situations where the query condition is fixed but the query is very frequent.
The use method is:

Bindings, creates a mysql_stmt variable, binds to the corresponding query string, the question mark in the string represents the variable to pass in, and each question mark must specify a variable.
Query, enter each specified variable, and the incoming mysql_stmt variable is executed with the available connection handle.
The code is as follows:

1. Binding


BOOL Cdbmanager::bindinsertstmt (MYSQL * connecthandle)


{


The binding for the insert operation


Mysql_bind Insertbind[feild_num];


if (M_stinsertparam = NULL)


M_stinsertparam = new Chostcachetable;


m_stinsertstmt = Mysql_stmt_init (Connecthandle);


Building a 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;


Populate the BIND structure array, M_sinsertparam is the statement associated with the struct variable


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;
Binding
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 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 bottlenecks


if (Mysql_stmt_execute (m_stinsertstmt))


return false;


return true;


}

L
Random access to records
In some database applications, we do not want to get all the records that satisfy the conditions, but only randomly select the records that meet the criteria. This situation is common in the data business statistical analysis, from the large-capacity database to obtain a small amount of data on the occasion.

There are two ways to do this.
1. The general method, first query all the records to meet the conditions, and then randomly selected part of the record. This method does not work well when the number of records that satisfies the condition is very large.
2. Using the limit syntax, first get the number of records that meet the criteria, and then add limit to the SQL query to restrict the query to only the section of the record that satisfies the requirement. Although this method is queried two times, it is more efficient in the case of large amount of data.
The sample code is as follows:

1. The conventional approach
Performance bottlenecks, 100,000 records, execute query 140ms, get 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 result set


M_presultset = Mysql_store_result (Connecthandle);


if (!m_presultset)//Get result set error


return 0;


int iallnumrows = (int) (Mysql_num_rows (M_presultset)); < number of search results


Calculate the number of results to return


int ireturnnumrows = (iallnumrows <= return_query_host_num)? Iallnumrows:return_query_host_num;


if (ireturnnumrows <= return_query_host_num)


{


Get a record


for (int i = 0; i<ireturnnumrows; i++)


{


Get field-by-individual


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


{


Random Selection The specified record returns


int iremainder = iallnumrows%ireturnnumrows; < remainder


int iquotient = iallnumrows/ireturnnumrows; < business


int istartindex = rand ()% (Iremainder + 1); < start subscript

Get a record


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]);


}


}


Releasing the content of a result set


Mysql_free_result (M_presultset);


return ireturnnumrows;


}

2. Using the Limit version
int Cdbmanager::queryhostcache (MYSQL * connecthandle, char * channelid, unsigned int myexternalip, int isptype, Chostcache Table * Hostcache)
{
First get the number of records that satisfy the result, and then use limit to 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);


Calculate the up and down ranges of the pending records


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 return


int ireturnnumrows = (iallnumrows <= return_query_host_num)?


Iallnumrows:return_query_host_num;

Using limit as a 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 a record


for (int i = 0; i<ireturnnumrows; i++)


{


Get field-by-individual


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]);


}


Releasing the content of a result set


Mysql_free_result (Presultset);


return ireturnnumrows;


}

L
Use connection pooling to manage connections.
In database design with a large number of node accesses, it is often necessary to use a connection pool to manage all connections.
The general approach is to establish two connection handle queues, idle queues to be used, and queues that are in use.
When you want to query the first from the idle queue to get a handle, inserted into the queue in use, and then do the database operation with this handle, must be removed from the use of the queue, and then inserted into the free queue.
The design code is as follows:

Defining a handle queue
typedef std::list<mysql *> Connection_handle_list;
typedef std::list<mysql *>::iterator Connection_handle_list_it;

The parameter structure of the connection database
Class Cdbparameter

{


Public


Char *host; &lt; host name


Char *user; &lt; user name


Char *password; &lt; password


Char *database; &lt; database name


unsigned int port; &lt; port, typically 0


const char *unix_socket; &lt; sockets, generally null


unsigned int client_flag; &lt; is 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 connected to the database and are 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-&gt;host = host;


Lpdbparam-&gt;user = user;


Lpdbparam-&gt;password = password;


Lpdbparam-&gt;database = database;


Lpdbparam-&gt;port = 0;


Lpdbparam-&gt;unix_socket = NULL;


Lpdbparam-&gt;client_flag = 0;


Try


{


Connection


for (int index = 0; index &lt; connection_num; index++)


{


MYSQL * Pconnecthandle = Mysql_init ((mysql*) 0); Initializing connection handles


if (!mysql_real_connect (Pconnecthandle, Lpdbparam-&gt;host, Lpdbparam-&gt;user, lpdbparam-&gt;password,\


LPDBPARAM-&GT;DATABASE,LPDBPARAM-&GT;PORT,LPDBPARAM-&GT;UNIX_SOCKET,LPDBPARAM-&GT;CLIENT_FLA))


return false;


Join in the idle queue


M_lsidlelist.push_back (Pconnecthandle);


}


}


catch (...)


{


return false;


}


return true;


}

Extracts 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//special case, empty in the busy queue, returned as empty


{


Pconnecthandle = 0;


}


M_listmutex.release ();

return pconnecthandle;
}

To free a used handle from the use queue and insert it into the free queue


void Cdbmanager::setidleconnecthandle (MYSQL * connecthandle)


{


M_listmutex.acquire ();


M_lsbusylist.remove (Connecthandle);


M_lsidlelist.push_back (Connecthandle);


M_listmutex.release ();


}


Using the example, get the idle handle first, do the actual operation with this handle, and then plug it back into the idle queue


BOOL Cdbmanager::D eletehostcachebysessionid (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 real delete operation


BOOL Cdbmanager::D eletehostcachebysessionid (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;


}