mysql数据库查询优化 mysql效率

  • A+
所属分类：数据库

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

  1. //1.常规的方法  
  2. //性能瓶颈,10万条记录时,执行查询140ms, 获取结果集500ms,其余可忽略  
  3. int CDBManager::QueryHostCache(MYSQL* connecthandle, char * channelid, int ISPtype, CDBManager::CHostCacheTable * &hostcache)
  4. {
  5.        char selectSQL[SQL_LENGTH];
  6.        memset(selectSQL, 0, sizeof(selectSQL));
  7.        sprintf(selectSQL,"select * from HostCache where ChannelID = '%s' and ISPtype = %d", channelid, ISPtype);
  8.        if(mysql_real_query(connecthandle, selectSQL, strlen(selectSQL)) != 0)   //检索  
  9.               return 0;
  10.        //获取结果集  
  11.        m_pResultSet = mysql_store_result(connecthandle);
  12.        if(!m_pResultSet)   //获取结果集出错  
  13.               return 0;
  14.        int iAllNumRows = (int)(mysql_num_rows(m_pResultSet));      ///<所有的搜索结果数  
  15.        //计算待返回的结果数  
  16.        int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM)? iAllNumRows:RETURN_QUERY_HOST_NUM;
  17.        if(iReturnNumRows <= RETURN_QUERY_HOST_NUM)
  18.        {
  19.               //获取逐条记录  
  20.               for(int i = 0; i<iReturnNumRows; i++)
  21.               {
  22.                      //获取逐个字段  
  23.                      m_Row = mysql_fetch_row(m_pResultSet);
  24.                      if(m_Row[0] != NULL)
  25.                             strcpy(hostcache[i].sessionid, m_Row[0]);
  26.                      if(m_Row[1] != NULL)
  27.                             strcpy(hostcache[i].channelid, m_Row[1]);
  28.                      if(m_Row[2] != NULL)
  29.                             hostcache[i].ISPtype      = atoi(m_Row[2]);
  30.                      if(m_Row[3] != NULL)
  31.                             hostcache[i].externalIP   = atoi(m_Row[3]);
  32.                      if(m_Row[4] != NULL)
  33.                             hostcache[i].externalPort = atoi(m_Row[4]);
  34.                      if(m_Row[5] != NULL)
  35.                             hostcache[i].internalIP   = atoi(m_Row[5]);
  36.                      if(m_Row[6] != NULL)
  37.                             hostcache[i].internalPort = atoi(m_Row[6]);
  38.               }
  39.        }
  40.        else
  41.        {
  42.               //随机的挑选指定条记录返回  
  43.               int iRemainder = iAllNumRows%iReturnNumRows;    ///<余数  
  44.               int iQuotient = iAllNumRows/iReturnNumRows;      ///<商  
  45.               int iStartIndex = rand()%(iRemainder + 1);         ///<开始下标    
  46.               //获取逐条记录  
  47.         for(int iSelectedIndex = 0; iSelectedIndex < iReturnNumRows; iSelectedIndex++)
  48.         {
  49.                             mysql_data_seek(m_pResultSet, iStartIndex + iQuotient * iSelectedIndex);
  50.                             m_Row = mysql_fetch_row(m_pResultSet);
  51.                   if(m_Row[0] != NULL)
  52.                        strcpy(hostcache[iSelectedIndex].sessionid, m_Row[0]);
  53.                    if(m_Row[1] != NULL)
  54.                                    strcpy(hostcache[iSelectedIndex].channelid, m_Row[1]);
  55.                    if(m_Row[2] != NULL)
  56.                        hostcache[iSelectedIndex].ISPtype      = atoi(m_Row[2]);
  57.                    if(m_Row[3] != NULL)
  58.                        hostcache[iSelectedIndex].externalIP   = atoi(m_Row[3]);
  59.                     if(m_Row[4] != NULL)
  60.                        hostcache[iSelectedIndex].externalPort = atoi(m_Row[4]);
  61.                    if(m_Row[5] != NULL)
  62.                        hostcache[iSelectedIndex].internalIP   = atoi(m_Row[5]);
  63.                    if(m_Row[6] != NULL)
  64.                        hostcache[iSelectedIndex].internalPort = atoi(m_Row[6]);
  65.         }
  66.       }
  67.        //释放结果集内容  
  68.        mysql_free_result(m_pResultSet);
  69.        return iReturnNumRows;
  70. }
  72. //2.使用limit版  
  73. int CDBManager::QueryHostCache(MYSQL * connecthandle, char * channelid, unsigned int myexternalip, int ISPtype, CHostCacheTable * hostcache)
  74. {
  75.        //首先获取满足结果的记录条数,再使用limit随机选择指定条记录返回  
  76.        MYSQL_ROW row;
  77.        MYSQL_RES * pResultSet;
  78.        char selectSQL[SQL_LENGTH];
  79.        memset(selectSQL, 0, sizeof(selectSQL));
  81.        sprintf(selectSQL,"select count(*) from HostCache where ChannelID = '%s' and ISPtype = %d", channelid, ISPtype);
  82.        if(mysql_real_query(connecthandle, selectSQL, strlen(selectSQL)) != 0)   //检索  
  83.               return 0;
  84.        pResultSet = mysql_store_result(connecthandle);
  85.        if(!pResultSet)
  86.               return 0;
  87.        row = mysql_fetch_row(pResultSet);
  88.        int iAllNumRows = atoi(row[0]);
  89.        mysql_free_result(pResultSet);
  90.        //计算待取记录的上下范围  
  91.        int iLimitLower = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
  92.               0:(rand()%(iAllNumRows - RETURN_QUERY_HOST_NUM));
  93.        int iLimitUpper = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
  94.               iAllNumRows:(iLimitLower + RETURN_QUERY_HOST_NUM);
  95.        //计算待返回的结果数  
  96.        int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
  97.                iAllNumRows:RETURN_QUERY_HOST_NUM;
  99.        //使用limit作查询  
  100.        sprintf(selectSQL,"select SessionID, ExternalIP, ExternalPort, InternalIP, InternalPort "
  101.               "from HostCache where ChannelID = '%s' and ISPtype = %d limit %d, %d"
  102.               , channelid, ISPtype, iLimitLower, iLimitUpper);
  103.        if(mysql_real_query(connecthandle, selectSQL, strlen(selectSQL)) != 0)   //检索  
  104.               return 0;
  105.        pResultSet = mysql_store_result(connecthandle);
  106.        if(!pResultSet)
  107.               return 0;
  108.        //获取逐条记录  
  109.        for(int i = 0; i<iReturnNumRows; i++)
  110.        {
  111.               //获取逐个字段  
  112.               row = mysql_fetch_row(pResultSet);
  113.               if(row[0] != NULL)
  114.                      strcpy(hostcache[i].sessionid, row[0]);
  115.               if(row[1] != NULL)
  116.                      hostcache[i].externalIP   = atoi(row[1]);
  117.               if(row[2] != NULL)
  118.                      hostcache[i].externalPort = atoi(row[2]);
  119.               if(row[3] != NULL)
  120.                      hostcache[i].internalIP   = atoi(row[3]);
  121.               if(row[4] != NULL)
  122.                      hostcache[i].internalPort = atoi(row[4]);
  123.        }
  124.        //释放结果集内容  
  125.        mysql_free_result(pResultSet);
  126.        return iReturnNumRows;
  127. }

使用连接池管理连接.
在有大量节点访问的数据库设计中,经常要使用到连接池来管理所有的连接.
一般方法是:建立两个连接句柄队列,空闲的等待使用的队列和正在使用的队列.
当要查询时先从空闲队列中获取一个句柄,插入到正在使用的队列,再用这个句柄做数据库操作,完毕后一定要从使用队列中删除,再插入到空闲队列.
设计代码如下:

  1. //定义句柄队列  
  2. typedef std::list<MYSQL *> CONNECTION_HANDLE_LIST;
  3. typedef std::list<MYSQL *>::iterator CONNECTION_HANDLE_LIST_IT;
  5. //连接数据库的参数结构  
  6. class CDBParameter
  7. {
  8. public:
  9.        char *host;                                 ///<主机名  
  10.        char *user;                                 ///<用户名  
  11.        char *password;                         ///<密码  
  12.        char *database;                           ///<数据库名  
  13.        unsigned int port;                 ///<端口，一般为0  
  14.        const char *unix_socket;      ///<套接字，一般为NULL  
  15.        unsigned int client_flag; ///<一般为0  
  16. };
  18. //创建两个队列  
  19. CONNECTION_HANDLE_LIST m_lsBusyList;                ///<正在使用的连接句柄  
  20. CONNECTION_HANDLE_LIST m_lsIdleList;                  ///<未使用的连接句柄  
  22. //所有的连接句柄先连上数据库,加入到空闲队列中,等待使用.  
  23. bool CDBManager::Connect(char * host /* = "localhost" */, char * user /* = "chenmin" */, \
  24.                                            char * password /* = "chenmin" */, char * database /* = "HostCache" */)
  25. {
  26.        CDBParameter * lpDBParam = new CDBParameter();
  27.        lpDBParam->host = host;
  28.        lpDBParam->user = user;
  29.        lpDBParam->password = password;
  30.        lpDBParam->database = database;
  31.        lpDBParam->port = 0;
  32.        lpDBParam->unix_socket = NULL;
  33.        lpDBParam->client_flag = 0;
  34.        try
  35.        {
  36.               //连接  
  37.               for(int index = 0; index < CONNECTION_NUM; index++)
  38.               {
  39.                      MYSQL * pConnectHandle = mysql_init((MYSQL*) 0);     //初始化连接句柄  
  40.                      if(!mysql_real_connect(pConnectHandle, lpDBParam->host, lpDBParam->user, lpDBParam->password,\
  41.        lpDBParam->database,lpDBParam->port,lpDBParam->unix_socket,lpDBParam->client_fla))
  42.                             return false;
  43. //加入到空闲队列中  
  44.                      m_lsIdleList.push_back(pConnectHandle);
  45.               }
  46.        }
  47.        catch(...)
  48.        {
  49.               return false;
  50.        }
  51.        return true;
  52. }
  54. //提取一个空闲句柄供使用  
  55. MYSQL * CDBManager::GetIdleConnectHandle()
  56. {
  57.        MYSQL * pConnectHandle = NULL;
  58.        m_ListMutex.acquire();
  59.        if(m_lsIdleList.size())
  60.        {
  61.               pConnectHandle = m_lsIdleList.front();
  62.               m_lsIdleList.pop_front();
  63.               m_lsBusyList.push_back(pConnectHandle);
  64.        }
  65.        else //特殊情况,闲队列中为空,返回为空  
  66.        {
  67.               pConnectHandle = 0;
  68.        }
  69.        m_ListMutex.release();
  71.        return pConnectHandle;
  72. }
  74. //从使用队列中释放一个使用完毕的句柄,插入到空闲队列  
  75. void CDBManager::SetIdleConnectHandle(MYSQL * connecthandle)
  76. {
  77.        m_ListMutex.acquire();
  78.        m_lsBusyList.remove(connecthandle);
  79.        m_lsIdleList.push_back(connecthandle);
  80.        m_ListMutex.release();
  81. }
  82. //使用示例,首先获取空闲句柄,利用这个句柄做真正的操作,然后再插回到空闲队列  
  83. bool CDBManager::DeleteHostCacheBySessionID(char * sessionid)
  84. {
  85.        MYSQL * pConnectHandle = GetIdleConnectHandle();
  86.        if(!pConnectHandle)
  87.               return 0;
  88.        bool bRet = DeleteHostCacheBySessionID(pConnectHandle, sessionid);
  89.        SetIdleConnectHandle(pConnectHandle);
  90.        return bRet;
  91. }
  92. //传入空闲的句柄,做真正的删除操作  
  93. bool CDBManager::DeleteHostCacheBySessionID(MYSQL * connecthandle, char * sessionid)
  94. {
  95.        char deleteSQL[SQL_LENGTH];
  96.        memset(deleteSQL, 0, sizeof(deleteSQL));
  97.        sprintf(deleteSQL,"delete from HostCache where SessionID = '%s'", sessionid);
  98.        if(mysql_query(connecthandle,deleteSQL) != 0) //删除  
  99.               return false;
  100.        return true;
  101. }
1 2
图片引用自网络
1 2 3