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 Use 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[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
{
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;
}
Current 1/2 page
12 Next read the full text
