《Mysql實例mysql數據庫查詢優化 mysql效率第1/3頁》要點：
本文介紹了Mysql實例mysql數據庫查詢優化 mysql效率第1/3頁，希望對您有用。如果有疑問，可以聯系我們。

//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;?
}?
_baidu_page_break_tag_
//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;?
}?

_baidu_page_break_tag_
//定義句柄隊列?
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;?
}?

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4866.html