CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章提高MySQL 查询效率的三个技巧由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

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_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)) 。

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 。

{ 。

//获取逐个字段 。

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 。

{ 。

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,

} 。

//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 。

{ 。

//获取逐个字段 。

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 CONNECTION_HANDLE_LIST,

typedefstd::list ::iteratorCONNECTION_HANDLE_LIST_IT,

//连接数据库的参数结构 。

classCDBParameter 。

{ 。

public

char*host;///<主机名 。

char*user;///<用户名 。

char*password;///<密码 。

char*database;///<数据库名 。

unsignedintport;///<端口，一般为0 。

constchar*unix_socket;///<套接字，一般为NULL 。

unsignedintclient_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 。

{ 。

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,

} 。

最后此篇关于提高MySQL 查询效率的三个技巧的文章就讲到这里了,如果你想了解更多关于提高MySQL 查询效率的三个技巧的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。