MySQL Improve query speed instance

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 using. 1. Bind to query using statement 2. Random access to record 3. Use connection pooling to manage connections.

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:

1. 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.
2. 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)

{

// bindings for insert operations

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;

// Populate 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. 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 be returned

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 fields by field

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 of the specified record returned

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

}

     }

// release result set content

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 range 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 be returned

int ireturnnumrows = (iallnumrows <= return_query_host_num)?

Iallnumrows:return_query_host_num;

// use limit for enquiries

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 fields by field

row = Mysql_fetch_row (Presultset);

if (Row[0]!= NULL)

strcpy (Hostcache[i].sessionid, row[0]);

if (row[1]!= NULL)

Hostcache[i].externalip = Atoi (row[1]);

if (row[2]!= NULL)

Hostcache[i].externalport = Atoi (row[2]);

if (Row[3]!= NULL)

Hostcache[i].internalip = Atoi (row[3]);

if (Row[4]!= NULL)

Hostcache[i].internalport = Atoi (row[4]);

}

// release result set content

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; < host Name

Char *user; < user name

Char *password; < password

Char *database; < database name

unsigned int port; < port, typically 0

const char *unix_socket; < sockets, generally null

unsigned int client_flag;///< is generally 0

};

Create two queues

Connection_handle_list m_lsbusylist; ///< connection handle in use

Connection_handle_list m_lsidlelist; ///< connection handle not used

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->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 connection handles

if (!mysql_real_connect (Pconnecthandle, Lpdbparam->host, Lpdbparam->user, Lpdbparam->password,

LPDBPARAM->DATABASE,LPDBPARAM->PORT,LPDBPARAM->UNIX_SOCKET,LPDBPARAM->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;