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;
}
