Some tips on improving query efficiency of MySQL database [PHP programmer must See]_php tutorial

MySQL is more and more used in database applications because of its small size and efficient operation. I used MySQL to save the peer node when I was working on a peer application, and because of the tens of thousands of nodes in the application, the node changes frequently, So be sure to keep your queries and insertions efficient. Here are three effective attempts to improve the efficiency of my use process.

L
Bind a query using statement
The query syntax tree can be built in advance using statement, and queries are no longer required to construct a syntax tree when querying. Therefore, the efficiency of the query can be improved very well. This method is suitable for situations where query conditions are fixed but queries are very frequent.
Use this method:

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 pass in the MYSQL_STMT variable executed with the available connection handle.
The code is as follows:

//1. Bind
BOOL Cdbmanager::bindinsertstmt (MYSQL * connecthandle)
{
//binding for 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;
//Populates the BIND structure array, M_sinsertparam is the statement associated structure 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. Enquiry
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 fetch of records
In some database applications, we are not going to get all the records that meet the criteria, but just randomly pick the records that meet the criteria. This situation is common in statistical analysis of data services, where small amounts of data are obtained from large-capacity databases.

There are two ways to do this.
1. The general method, first of all to find out all the conditions of the records, and then randomly pick out some records. This method is not satisfactory when the number of records satisfying the condition is very large.
2. Using the limit syntax, first obtain the number of record bars that meet the criteria, and then add a limit in the SQL query statement to restrict querying only a record that satisfies the requirements. Although this method is queried two times, it is more efficient when the amount of data is large.
The sample code is as follows:

1. General methods
Performance bottleneck, 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)//Search
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)); /// <所有的搜索结果数
<>
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 Records
for (int i = 0; i
{
Get field by column
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 selects the specified bar record to return
int iremainder = iallnumrows%ireturnnumrows; /// <余数
<>
int iquotient = iallnumrows/ireturnnumrows; /// <商
<>
int istartindex = rand ()% (Iremainder + 1); /// <开始下标< p> <>< p=""><>

//Get record by
for (int iselectedindex = 0; Iselectedindex < ireturnnumrows; iselectedindex++)
{
mysql_data_s Eek (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]);
}
}
//Free result set contents
Mysql_free_result (m_presultset);
return ireturnnumrows;
}

//2. Using the limit version
int Cdbmanager::queryhostcache (MYSQL * connecthandle, char * channelid, unsigned int myexternalip, int Isptype, chostcachetable * hostcache)
{
//First get the number of record bars 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)//search
return 0;
Presultset = Mysql_sto Re_result (Connecthandle);
if (!presultset)
return 0;
row = Mysql_fetch_row (presultset);
int iallnumrows = atoi (row[0]);
Mysql_f Ree_result (Presultset);
//Calculate the upper and lower range of the pending record
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;

//Use limit as 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_sto Re_result (Connecthandle);
if (!presultset)
return 0;
//Get record-by-article
for (int i = 0; I {
//Get field by column
Row = Mysql_fetch_row (Presultset);
if (row[0]! = null)
strcpy (Hostcache.sessionid, row[0]),
if (row[1]! = null)
Hostcache.externalip = Atoi (r OW[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]);
}
//Free result set contents
Mysql_free_result (presultset);
return ireturnnumrows;
}

L
Use connection pooling to manage connections.
In a database design with a large number of nodes access, 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 for use, and queues that are in use.
When you want to query, get a handle from the idle queue, insert it into the queue you are using, and use the handle for database operations, and then make sure to remove it from the queue and insert it into the idle queue.
The design code is as follows:

Defining a handle queue
typedef std::list Connection_handle_list;
typedef std::list :: Iterator connection_handle_list_it;

Parameter structure of the connected database
Class Cdbparameter

{
Public
Char *host; ///<>
Char *user; ///<>
Char *password; ///<>
Char *database; ///<>
unsigned int port; ///<>
const char *unix_socket; ///<>
unsigned int client_flag; ///<>
};

Create two queues
Connection_handle_list m_lsbusylist; ///<>
Connection_handle_list m_lsidlelist; ///<>< p=""><>

All connection handles are connected to the database, joined to the idle queue, and are waiting to be used.
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); Initializing a connection handle
if (!mysql_real_connect (Pconnecthandle, Lpdbparam->host, Lpdbparam->user, Lpdbparam->password,
LPDBPARAM-&GT;DATABASE,LPDBPARAM-&GT;PORT,LPDBPARAM-&GT;UNIX_SOCKET,LPDBPARAM-&GT;CLIENT_FLA))
return false;
Join to 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 busy queue, return empty
{
Pconnecthandle = 0;
}
M_listmutex.release ();

return pconnecthandle;
}

To release a used handle from the queue, insert it into the idle queue
void Cdbmanager::setidleconnecthandle (MYSQL * connecthandle)
{
M_listmutex.acquire ();
M_lsbusylist.remove (Connecthandle);
M_lsidlelist.push_back (Connecthandle);
M_listmutex.release ();
}
Using the example, first get the idle handle, use this handle to do a real operation, 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 a 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;
}

http://www.bkjia.com/PHPjc/445134.html www.bkjia.com true http://www.bkjia.com/PHPjc/445134.html techarticle MySQL is more and more used in database applications because of its small size and efficient operation. I used MySQL to save the peer node when I was developing a peer- to-peer application ...

