Several tips to improve MySQL database query efficiency [A must-read for PHP programmers]_PHP tutorial

MySQL is increasingly used in database applications due to its compactness and efficient operation. When I was developing a P2P application, I used MySQL to save P2P nodes. Because in P2P applications, the number of nodes is constantly changing. There are tens of thousands, and the nodes change frequently, so it is necessary to keep the query and insertion efficient. The following are three effective attempts I made to improve efficiency during use.

l
Use statement Bind query
Using statement can build the query syntax tree in advance. When querying, you no longer need to build the syntax tree and query directly. Therefore, it can greatly improve the efficiency of query. This method is suitable for fixed query conditions but very frequent queries. Occasion. The usage method of
is:

binding, create a MYSQL_STMT variable and bind it to the corresponding query string. The question mark in the string represents the variable to be passed in. Each question mark must be specified. A variable.
Query, enter each specified variable, pass in the MYSQL_STMT variable and execute it 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_stIns ertParam = 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;
//Fill the bind structure array, m_sInsertParam is associated with this statement 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;
       //绑定
       if (mysql_stmt_bind_param(m_stInsertStmt, insertbind))
              return false;
       return true;
}

//2.查询
bool CDBManager::InsertHostCache2(MYSQL * connecthandle, char * sessionid, char * channelid, int ISPtype,
              unsigned int eIP, unsigned short eport, unsigned int iIP, unsigned short iport)
{
       //填充结构变量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;
       //执行statement,性能瓶颈处
       if(mysql_stmt_execute(m_stInsertStmt))
              return false;
       return true;
}

l
随机的获取记录
在某些数据库的应用中, 我们并不是要获取所有的满足条件的记录,而只是要随机挑选出满足条件的记录. 这种情况常见于数据业务的统计分析,从大容量数据库中获取小量的数据的场合.

有两种方法可以做到
1.       常规方法,首先查询出所有满足条件的记录,然后随机的挑选出部分记录.这种方法在满足条件的记录数很多时效果不理想.
2.       使用limit语法,先获取满足条件的记录条数, 然后在sql查询语句中加入limit来限制只查询满足要求的一段记录. 这种方法虽然要查询两次,但是在数据量大时反而比较高效.
示例代码如下:

//1.常规的方法
//性能瓶颈,10万条记录时,执行查询140ms, 获取结果集500ms,其余可忽略
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 the result set
m_pResultSet = mysql_ store_result(connecthandle );
if(!m_pResultSet) //Error in 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)
{
//Get records one by one
for(int i = 0; i {
                                                                       ow = mysql_fetch_row(m_pResultSet);
                                                                                      )
strcpy(hostcache.sessionid, m_Row[0]);
if(m_Row[1] != NULL)
          strcpy(hostcache.channelid, m_Row[1]); [2] != NULL)
                                                       hostcache                                                                                                                                        Hostcache.externalIP = atoi(m_Row[3]);
                   if(m_Row[4]                                                                                                                                                _Row[5] != NULL)
                                                              hostcache. m_Row[5]);
if(m_Row[6] != NULL)
hostcache.internalPort = atoi(m_Row[6]); }
}
else
{
//Randomly select the specified record and return
              int iRemainder = iAllNumRows%iReturnNumRows;    ///<余数
              int iQuotient = iAllNumRows/iReturnNumRows;      ///<商
              int iStartIndex = rand()%(iRemainder + 1);         ///<开始下标

              //获取逐条记录
        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]);
        }
      }
       //释放结果集内容
       mysql_free_result(m_pResultSet);
       return iReturnNumRows;
}

//2.使用limit版
int CDBManager::QueryHostCache(MYSQL * connecthandle, char * channelid, unsigned int myexternalip, int ISPtype, CHostCacheTable * hostcache)
{
       //首先获取满足结果的记录条数,再使用limit随机选择指定条记录返回
       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)   //检索
              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);
       //计算待取记录的上下范围
       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);
       //计算待返回的结果数
       int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
               iAllNumRows:RETURN_QUERY_HOST_NUM;

       //使用limit作查询
       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)   //检索
              return 0;
       pResultSet = mysql_store_result(connecthandle);
       if(!pResultSet)
              return 0;
       //获取逐条记录
       for(int i = 0; i       {
              //获取逐个字段
              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]);            
       }
       //释放结果集内容
       mysql_free_result(pResultSet);
       return iReturnNumRows;
}

l
Use a connection pool to manage connections.
In database designs that are accessed by a large number of nodes, connection pools are often used to manage all connections.
The general method is: create two connection handles Queue, idle queue waiting to be used and queue in use.
When querying, first obtain a handle from the idle queue, insert it into the queue in use, and then use this handle to perform database operations. After completion, be sure to obtain a handle from the idle queue. Use the queue to delete and then insert into the free 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 connecting to the database
class CDBParameter

{
public:
char *host; / // char *user;                               char *database;                                                                   /// unsigned int port; /// 🎜>};

//Create two queues
CONNECTION_HANDLE_LIST m_lsBusyList; The connection handle

//All connection handles are first connected to the database, added to the idle queue, waiting 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
{
//Connect
              for(int index = 0; index < CONNECTION_NUM; index++)
                                                                                                     if(!mysql_real_connect(pConnectHandle, lpDBParam->host, lpDBParam- >user, lpDBParam->password,
lpDBParam->database,lpDBParam->port,lpDBParam->unix_socket,lpDBParam->client_fla))
return false;
//Join To the idle queue
m_lsIdleList.push_back(pConnectHandle);
                              return false;                ;
}

//Extract a free 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 circumstances, the idle queue is empty, and the return is empty
{
pConnecthandle = 0;
}
m_listmutex.release (); pConnectHandle;
}

//Release a used handle from the use queue and 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();
}
//Usage example, first obtain the free handle, and use this handle to perform real operations, Then insert it back into the idle queue
bool CDBManager::DeleteHostCacheBySessionID(char * sessionid)
{
MYSQL * pConnectHandle = GetIdleConnectHandle();
if(!pConnectHandle)
return 0;
       bool bRet = DeleteHostCacheBySessionID(pConnectHandle, sessionid); 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;
}

http://www.bkjia.com/PHPjc/445134.html

www.bkjia.com

truehttp: //www.bkjia.com/PHPjc/445134.htmlTechArticleMySQL is increasingly used in database applications due to its compactness and efficient operation. I am When developing a P2P application, I used MySQL to save P2P nodes. Due to the application of P2P...