Home > Database > Mysql Tutorial > 提高MYSQL查询效率的三个有效的尝试(2)_MySQL

提高MYSQL查询效率的三个有效的尝试(2)_MySQL

WBOY
Release: 2016-06-01 13:57:26
Original
691 people have browsed it

    提示:MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试.

 

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)   //检索
              return 0;
       //获取结果集
       m_pResultSet = mysql_store_result(connecthandle);
       if(!m_pResultSet)   //获取结果集出错
              return 0;
       int iAllNumRows = (int)(mysql_num_rows(m_pResultSet));      ///       //计算待返回的结果数
       int iReturnNumRows = (iAllNumRows        if(iReturnNumRows        {
              //获取逐条记录
              for(int i = 0; i              {
                     //获取逐个字段
                     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
       {
              //随机的挑选指定条记录返回
              int iRemainder = iAllNumRows%iReturnNumRows;    ///              int iQuotient = iAllNumRows/iReturnNumRows;      ///              int iStartIndex = rand()%(iRemainder + 1);         ///              //获取逐条记录
        for(int iSelectedIndex = 0; 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               0:(rand()%(iAllNumRows - RETURN_QUERY_HOST_NUM));
       int iLimitUpper = (iAllNumRows               iAllNumRows:(iLimitLower + RETURN_QUERY_HOST_NUM);
       //计算待返回的结果数
       int iReturnNumRows = (iAllNumRows                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[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]);            
       }
       //释放结果集内容
       mysql_free_result(pResultSet);
       return iReturnNumRows;
}

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template