`

提高MySQL 查询效率的三个技巧

MySQL数据结构SocketSQLUnix 
阅读更多
MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试. 1. 使用statement进行绑定查询 2. 随机的获取记录 3. 使用连接池管理连接.
MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试.
l 使用statement进行绑定查询
使用statement可以提前构建查询语法树,在查询时不再需要构建语法树就直接查询.因此可以很好的提高查询的效率. 这个方法适合于查询条件固定但查询非常频繁的场合.
使用方法是:
绑定, 创建一个MYSQL_STMT变量,与对应的查询字符串绑定,字符串中的问号代表要传入的变量,每个问号都必须指定一个变量.
查询, 输入每个指定的变量, 传入MYSQL_STMT变量用可用的连接句柄执行.
代码如下: 
//1.绑定   
bool CDBManager::BindInsertStmt(MYSQL * connecthandle)   
{   
       //作插入操作的绑定   
       MYSQL_BIND insertbind[FEILD_NUM];   
       if(m_stInsertParam == NULL)   
              m_stInsertParam = new CHostCacheTable;   
       m_stInsertStmt = mysql_stmt_init(connecthandle);   
       //构建绑定字符串   
       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;   
       //填充bind结构数组, m_sInsertParam是这个statement关联的结构变量   
       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)   //检索   
              return 0;   
       //获取结果集   
       m_pResultSet = mysql_store_result(connecthandle);   
       if(!m_pResultSet)   //获取结果集出错   
              return 0;   
       int iAllNumRows = (int)(mysql_num_rows(m_pResultSet));      ///<所有的搜索结果数   
       //计算待返回的结果数   
       int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM)? iAllNumRows:RETURN_QUERY_HOST_NUM;   
       if(iReturnNumRows <= RETURN_QUERY_HOST_NUM)   
       {   
              //获取逐条记录   
              for(int i = 0; i<iReturnNumRows; 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 < 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<iReturnNumRows; 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;   
}

l 使用连接池管理连接.
在有大量节点访问的数据库设计中,经常要使用到连接池来管理所有的连接.
一般方法是:建立两个连接句柄队列,空闲的等待使用的队列和正在使用的队列.
当要查询时先从空闲队列中获取一个句柄,插入到正在使用的队列,再用这个句柄做数据库操作,完毕后一定要从使用队列中删除,再插入到空闲队列.
设计代码如下: 
//定义句柄队列   
typedef std::list<MYSQL *> CONNECTION_HANDLE_LIST;   
typedef std::list<MYSQL *>::iterator CONNECTION_HANDLE_LIST_IT;   
    
//连接数据库的参数结构   
class CDBParameter                
{   
public:   
       char *host;                                 ///<主机名   
       char *user;                                 ///<用户名   
       char *password;                         ///<密码   
       char *database;                           ///<数据库名   
       unsigned int port;                 ///<端口，一般为0   
       const char *unix_socket;      ///<套接字，一般为NULL   
       unsigned int client_flag; ///<一般为0   
};   
    
//创建两个队列   
CONNECTION_HANDLE_LIST m_lsBusyList;                ///<正在使用的连接句柄   
CONNECTION_HANDLE_LIST m_lsIdleList;                  ///<未使用的连接句柄   
    
//所有的连接句柄先连上数据库,加入到空闲队列中,等待使用.   
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  
       {   
              //连接   
              for(int index = 0; index < CONNECTION_NUM; index++)   
              {   
                     MYSQL * pConnectHandle = mysql_init((MYSQL*) 0);     //初始化连接句柄   
                     if(!mysql_real_connect(pConnectHandle, lpDBParam->host, lpDBParam->user, lpDBParam->password,\   
       lpDBParam->database,lpDBParam->port,lpDBParam->unix_socket,lpDBParam->client_fla))   
                            return false;   
//加入到空闲队列中   
                     m_lsIdleList.push_back(pConnectHandle);   
              }   
       }   
       catch(...)   
       {   
              return false;   
       }   
       return true;   
}   
    
//提取一个空闲句柄供使用   
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 //特殊情况,闲队列中为空,返回为空   
       {   
              pConnectHandle = 0;   
       }   
       m_ListMutex.release();   
    
       return pConnectHandle;   
}   
    
//从使用队列中释放一个使用完毕的句柄,插入到空闲队列   
void CDBManager::SetIdleConnectHandle(MYSQL * connecthandle)   
{   
       m_ListMutex.acquire();   
       m_lsBusyList.remove(connecthandle);   
       m_lsIdleList.push_back(connecthandle);   
       m_ListMutex.release();   
}   
//使用示例,首先获取空闲句柄,利用这个句柄做真正的操作,然后再插回到空闲队列   
bool CDBManager::DeleteHostCacheBySessionID(char * sessionid)   
{   
       MYSQL * pConnectHandle = GetIdleConnectHandle();   
       if(!pConnectHandle)   
              return 0;   
       bool bRet = DeleteHostCacheBySessionID(pConnectHandle, sessionid);   
       SetIdleConnectHandle(pConnectHandle);   
       return bRet;   
}   
//传入空闲的句柄,做真正的删除操作   
bool 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) //删除   
              return false;   
       return true;   
}
分享到：
| ORACLE的基本语法集锦
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    提高MySQL 查询效率的三个技巧.rar

    本资料“提高MySQL 查询效率的三个技巧”旨在帮助数据库管理员和开发者提升查询速度，降低资源消耗，从而提升应用程序的响应时间和用户体验。下面将详细阐述这三个核心技巧。 一、**索引优化** 索引是MySQL查询...

    mysql查询优化之索引优化

    - **覆盖索引**：如果查询只需要索引中的信息，覆盖索引可以避免回表操作，提高查询效率。 - **使用索引提示**：在某些复杂查询中，可以使用SQL的FORCE INDEX或USE INDEX提示强制MySQL使用特定的索引。 - **避免全...

    提高MySQL 查询效率的三个技巧第1/2页

    以下是我在使用过程中做的提高效率的三个有效的尝试. l 使用statement进行绑定查询 使用statement可以提前构建查询语法树,在查询时不再需要构建语法树就直接查询.因此可以很好的提高查询的效率. 这个方法适合于...

    设计高效合理的MySQL查询语句

    首先，合理使用索引是提高查询效率的关键。索引是一种加速查询的数据结构，通常基于ISAM（Indexed Sequential Access Method）索引结构。应该在经常进行连接但未定义为外键的列上创建索引，而在频繁进行排序或分组...

    MySQL调优讨论：原理 优化 技巧

    4. **适当冗余**：在不影响数据一致性的前提下，适当的数据冗余可以减少JOIN操作，提高查询效率。 5. **定期审查和调整配置**：随着业务的发展，数据库参数可能需要不断调整，以适应新的负载。 总的来说，MySQL...

    MySQL数据库查询优化

    MySQL中怎么写WHERE子句有利于提高查询效率？ 预计时间1小时 第6课 查询优化技术理论与MySQL实践（四）------条件化简 什么是条件化简？MySQL中对什么样的条件自动进行优化？如何写出可利用索引的条件语句？ 预计...

    MySQL数据库使用技巧三例.pdf

    本文将探讨三个实用的MySQL使用技巧，帮助提升数据库管理和维护的效率。 首先，删除指定日期内的日志文件是数据库维护的重要环节。在处理大量日志数据时，及时清理过期信息能有效释放存储空间并提高查询速度。例如...

    MySQL必知必会常识技巧实战宝典

    分区表通过将大表分割成多个小部分来提高查询性能和管理效率。然而，并不是所有的应用场景都适合使用分区表。例如，在数据访问模式不明确或者表大小不足以证明分区必要性的情况下，使用分区表可能不会带来明显的好处...

    MYSQL数据查询技巧.pdf

    总之，掌握这些MySQL数据查询技巧对于优化数据处理流程和提高工作效率至关重要。在处理中文数据、批量字符串替换以及解决ORDER BY错误时，理解并运用上述方法，可以有效避免和解决实际操作中遇到的问题。同时，定期...

    MYSQL查询、优化原理

    为了进一步提高查询效率，MySQL 还支持多种索引合并策略，包括 INTERSECTION、UNION 和 SORT-UNION。这些策略可以帮助 MySQL 更有效地利用索引来减少需要扫描的记录数量。 ##### 1.2 JOIN 连接原理 连接查询是 SQL...

    mysql数据库查询优化 mysql效率第1/3页

    这里，我们聚焦于提高MySQL查询效率的三个有效技巧。 首先，使用预编译的`Statement`进行绑定查询。这是提升效率的一个重要策略，尤其适用于查询条件固定而查询频率高的情况。`Statement`允许我们提前构建SQL语句的...

    藏经阁-MySQL表和索引优化实战-11页

    MySQL 表和索引优化实战是指在 MySQL 数据库中对表和索引进行优化，以提高查询效率和存储空间利用率。本文将从 MySQL 表和索引的基本概念出发，探讨 MySQL 表和索引优化的实战经验和技巧。 一、 MySQL 表和索引的...

    高性能MySQL_ch04_查询性能优化.pdf

    随着业务量的增长，数据库面临的查询压力也越来越大，如何有效地提高查询效率成为了一个亟待解决的问题。 #### 二、查询性能优化的基本原则 1. **SQL语句优化**：合理构建SQL语句，减少不必要的数据检索和处理。 2...

    MySQL数据库优化SQL篇PPT课件.pptx

    这些技巧可以帮助我们提高数据库的性能和效率。 本PPT课件对MySQL数据库优化的重要知识点进行了详细的讲解，并提供了许多实用的优化技巧和方法。通过学习本PPT课件，可以提高数据库的性能和效率，提高开发效率和...

    mysql优化技巧

    ### MySQL优化技巧详解 #### 一、选择正确的存储引擎 MySQL提供了多种存储引擎，每种引擎都有其特点和适用场景。对于不同的业务需求，选择...通过以上几个方面的优化措施，可以显著提升MySQL的查询效率和整体性能。

    MySQL迁移、拆分技巧.pptx

    在实际操作中，经常需要对大型表进行拆分以提高查询效率和系统性能。常见的拆分方式包括水平拆分和垂直拆分。 ##### 水平拆分 水平拆分是指将一张大表按照某种规则拆分成多张表，每张表包含原表的一部分数据。这种...

    mysql优化笔记+资料

    这些笔记涵盖了MySQL优化的主要方面，包括查询优化、SQL编写技巧、数据库设计、存储引擎选择、服务器配置、硬件升级、定期维护以及使用各种工具进行监控和调优。通过这些方法，你可以有效地提升MySQL数据库的运行...

    MyBatis+mysql查询和添加数据

    MyBatis由SqlSessionFactoryBuilder、SqlSessionFactory和SqlSession三个主要部分构成。SqlSessionFactoryBuilder用于创建SqlSessionFactory，SqlSessionFactory则是创建SqlSession的工厂，SqlSession则代表与数据库...

    MySQL性能优化 SQL优化方法技巧

    - 尽量使用索引来避免全表扫描，提高查询效率。 - 对于频繁查询的字段建立索引。 **2. 选择合适的数据类型** - 选择最适合数据特性的数据类型，以减少存储空间的使用并提高性能。 **3. 限制返回结果集** - 使用...

Magicbox
Global site tag (gtag.js) - Google Analytics