提高MySQL 查询效率的三个技巧第1/2页
2022-11-12 09:20:57
内容摘要
这篇文章主要为大家详细介绍了提高MySQL 查询效率的三个技巧第1/2页，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！MySQL由于它本身的小
文章正文
这篇文章主要为大家详细介绍了提高MySQL 查询效率的三个技巧第1/2页，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
MySQL由于它本身的小巧和操作的高效,在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试.l使用statement进行绑定查询使用statement可以提前构建查询语法树,在查询时不再需要构建语法树就直接查询.因此可以很好的提高查询的效率.这个方法适合于查询条件固定但查询非常频繁的场合.使用方法是:绑定,创建一个MYSQL_STMT变量,与对应的查询字符串绑定,字符串中的问号代表要传入的变量,每个问号都必须指定一个变量.查询,输入每个指定的变量,传入MYSQL_STMT变量用可用的连接句柄执行.代码如下://1.绑定boolCDBManager::BindInsertStmt(MYSQL*connecthandle){//作插入操作的绑定MYSQL_BINDinsertbind[FEILD_NUM];if(m_stInsertParam==NULL)m_stInsertParam=newCHostCacheTable;m_stInsertStmt=mysql_stmt_init(connecthandle);//构建绑定字符串charinsertSQL[SQL_LENGTH];strcpy(insertSQL,"insertintoHostCache(SessionID,ChannelID,ISPType,""ExternalIP,ExternalPort,InternalIP,InternalPort)""values(?,?,?,?,?,?,?)");mysql_stmt_prepare(m_stInsertStmt,insertSQL,strlen(insertSQL));intparam_count=mysql_stmt_param_count(m_stInsertStmt);if(param_count!=FEILD_NUM)returnfalse;//填充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))returnfalse;returntrue;}//2.查询boolCDBManager::InsertHostCache2(MYSQL*connecthandle,char*sessionid,char*channelid,intISPtype,\unsignedinteIP,unsignedshorteport,unsignedintiIP,unsignedshortiport){//填充结构变量m_sInsertParamstrcpy(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))returnfalse;returntrue;}l随机的获取记录在某些数据库的应用中,我们并不是要获取所有的满足条件的记录,而只是要随机挑选出满足条件的记录.这种情况常见于数据业务的统计分析,从大容量数据库中获取小量的数据的场合.有两种方法可以做到1.常规方法,首先查询出所有满足条件的记录,然后随机的挑选出部分记录.这种方法在满足条件的记录数很多时效果不理想.2.使用limit语法,先获取满足条件的记录条数,然后在sql查询语句中加入limit来限制只查询满足要求的一段记录.这种方法虽然要查询两次,但是在数据量大时反而比较高效.示例代码如下://1.常规的方法//性能瓶颈,10万条记录时,执行查询140ms,获取结果集500ms,其余可忽略intCDBManager::QueryHostCache(MYSQL*connecthandle,char*channelid,intISPtype,CDBManager::CHostCacheTable*&hostcache){charselectSQL[SQL_LENGTH];memset(selectSQL,0,sizeof(selectSQL));sprintf(selectSQL,"select*fromHostCachewhereChannelID='%s'andISPtype=%d",channelid,ISPtype);if(mysql_real_query(connecthandle,selectSQL,strlen(selectSQL))!=0)//检索return0;//获取结果集m_pResultSet=mysql_store_result(connecthandle);if(!m_pResultSet)//获取结果集出错return0;intiAllNumRows=(int)(mysql_num_rows(m_pResultSet));///<所有的搜索结果数//计算待返回的结果数intiReturnNumRows=(iAllNumRows<=RETURN_QUERY_HOST_NUM)?iAllNumRows:RETURN_QUERY_HOST_NUM;if(iReturnNumRows<=RETURN_QUERY_HOST_NUM){//获取逐条记录for(inti=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{//随机的挑选指定条记录返回intiRemainder=iAllNumRows%iReturnNumRows;///<余数intiQuotient=iAllNumRows/iReturnNumRows;///<商intiStartIndex=rand()%(iRemainder+1);///<开始下标//获取逐条记录for(intiSelectedIndex=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);returniReturnNumRows;}12下一页阅读全文
对此感兴趣的朋友，看看idc笔记做的技术笔记！
//2.使用limit版intCDBManager::QueryHostCache(MYSQL*connecthandle,char*channelid,unsignedintmyexternalip,intISPtype,CHostCacheTable*hostcache){//首先获取满足结果的记录条数,再使用limit随机选择指定条记录返回MYSQL_ROWrow;MYSQL_RES*pResultSet;charselectSQL[SQL_LENGTH];memset(selectSQL,0,sizeof(selectSQL));sprintf(selectSQL,"selectcount(*)fromHostCachewhereChannelID='%s'andISPtype=%d",channelid,ISPtype);if(mysql_real_query(connecthandle,selectSQL,strlen(selectSQL))!=0)//检索return0;pResultSet=mysql_store_result(connecthandle);if(!pResultSet)return0;row=mysql_fetch_row(pResultSet);intiAllNumRows=atoi(row[0]);mysql_free_result(pResultSet);//计算待取记录的上下范围intiLimitLower=(iAllNumRows<=RETURN_QUERY_HOST_NUM)?0:(rand()%(iAllNumRows-RETURN_QUERY_HOST_NUM));intiLimitUpper=(iAllNumRows<=RETURN_QUERY_HOST_NUM)?iAllNumRows:(iLimitLower+RETURN_QUERY_HOST_NUM);//计算待返回的结果数intiReturnNumRows=(iAllNumRows<=RETURN_QUERY_HOST_NUM)?iAllNumRows:RETURN_QUERY_HOST_NUM;//使用limit作查询sprintf(selectSQL,"selectSessionID,ExternalIP,ExternalPort,InternalIP,InternalPort""fromHostCachewhereChannelID='%s'andISPtype=%dlimit%d,%d",channelid,ISPtype,iLimitLower,iLimitUpper);if(mysql_real_query(connecthandle,selectSQL,strlen(selectSQL))!=0)//检索return0;pResultSet=mysql_store_result(connecthandle);if(!pResultSet)return0;//获取逐条记录for(inti=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);returniReturnNumRows;}l使用连接池管理连接.在有大量节点访问的数据库设计中,经常要使用到连接池来管理所有的连接.一般方法是:建立两个连接句柄队列,空闲的等待使用的队列和正在使用的队列.当要查询时先从空闲队列中获取一个句柄,插入到正在使用的队列,再用这个句柄做数据库操作,完毕后一定要从使用队列中删除,再插入到空闲队列.设计代码如下://定义句柄队列typedefstd::list<MYSQL*>CONNECTION_HANDLE_LIST;typedefstd::list<MYSQL*>::iteratorCONNECTION_HANDLE_LIST_IT;//连接数据库的参数结构classCDBParameter{public:char*host;///<主机名char*user;///<用户名char*password;///<密码char*database;///<数据库名unsignedintport;///<端口，一般为0constchar*unix_socket;///<套接字，一般为NULLunsignedintclient_flag;///<一般为0};//创建两个队列CONNECTION_HANDLE_LISTm_lsBusyList;///<正在使用的连接句柄CONNECTION_HANDLE_LISTm_lsIdleList;///<未使用的连接句柄//所有的连接句柄先连上数据库,加入到空闲队列中,等待使用.boolCDBManager::Connect(char*host/*="localhost"*/,char*user/*="chenmin"*/,\char*password/*="chenmin"*/,char*database/*="HostCache"*/){CDBParameter*lpDBParam=newCDBParameter();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(intindex=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))returnfalse;//加入到空闲队列中m_lsIdleList.push_back(pConnectHandle);}}catch(...){returnfalse;}returntrue;}//提取一个空闲句柄供使用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();returnpConnectHandle;}//从使用队列中释放一个使用完毕的句柄,插入到空闲队列voidCDBManager::SetIdleConnectHandle(MYSQL*connecthandle){m_ListMutex.acquire();m_lsBusyList.remove(connecthandle);m_lsIdleList.push_back(connecthandle);m_ListMutex.release();}//使用示例,首先获取空闲句柄,利用这个句柄做真正的操作,然后再插回到空闲队列boolCDBManager::DeleteHostCacheBySessionID(char*sessionid){MYSQL*pConnectHandle=GetIdleConnectHandle();if(!pConnectHandle)return0;boolbRet=DeleteHostCacheBySessionID(pConnectHandle,sessionid);SetIdleConnectHandle(pConnectHandle);returnbRet;}//传入空闲的句柄,做真正的删除操作boolCDBManager::DeleteHostCacheBySessionID(MYSQL*connecthandle,char*sessionid){chardeleteSQL[SQL_LENGTH];memset(deleteSQL,0,sizeof(deleteSQL));sprintf(deleteSQL,"deletefromHostCachewhereSessionID='%s'",sessionid);if(mysql_query(connecthandle,deleteSQL)!=0)//删除returnfalse;returntrue;}上一页12阅读全文
注：关于提高MySQL 查询效率的三个技巧第1/2页的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
