Mysql实例mysql数据库查询优化 mysql效率第1/3页
发布时间：2020-12-12 01:02:21 所属栏目：MySql教程 来源：网络整理
导读：《Mysql实例mysql数据库查询优化 mysql效率第1/3页》要点： 本文介绍了Mysql实例mysql数据库查询优化 mysql效率第1/3页，希望对您有用。如果有疑问，可以联系我们。 //1.绑定? bool?CDBManager::BindInsertStmt(MYSQL?*?connecthandle)? {? ???????//作插入
|
《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,?CDBManager::CHostCacheTable?*?&hostcache)? {?????? ???????char?selectSQL[SQL_LENGTH];? ???????memset(selectSQL,?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,?unsigned?int?myexternalip,?CHostCacheTable?*?hostcache)? {? ???????//首先获取满足结果的记录条数,再使用limit随机选择指定条记录返回? ???????MYSQL_ROW?row;? ???????MYSQL_RES?*?pResultSet;? ???????char?selectSQL[SQL_LENGTH];? ???????memset(selectSQL,?sizeof(selectSQL));? ???????sprintf(selectSQL,"select?count(*)?from?HostCache?where?ChannelID?=?'%s'?and?ISPtype?=?%d",?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,?InternalPort?"? ??????????????"from?HostCache?where?ChannelID?=?'%s'?and?ISPtype?=?%d?limit?%d,?%d"? ??????????????,?ISPtype,?iLimitLower,?iLimitUpper);? ???????if(mysql_real_query(connecthandle,?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,?sizeof(deleteSQL));? ???????sprintf(deleteSQL,"delete?from?HostCache?where?SessionID?=?'%s'",?sessionid);? ???????if(mysql_query(connecthandle,deleteSQL)?!=?0)?//删除? ??????????????return?false;? ???????return?true;? }? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |