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
typedefstd::list
//连接数据库的参数结构
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;
}