提高MySQL 查询效率的三个技巧.doc

提高MySQL 查询效率的三个技巧MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试. 1. 使用statement进行绑定查询 2. 随机的获取记录 3. 使用连接池管理连接. MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试. l 使用statement进行绑定查询使用statement可以提前构建查询语法树,在查询时不再需要构建语法树就直接查询.因此可以很好的提高查询的效率. 这个方法适合于查询条件固定但查询非常频繁的场合.使用方法是:绑定, 创建一个MYSQL_STMT变量,与对应的查询字符串绑定,字符串中的问号代表要传入的变量,每个问号都必须指定一个变量. 查询, 输入每个指定的变量, 传入MYSQL_STMT变量用可用的连接句柄执行. 代码如下: /1.绑定bool CDBManager:BindInsertStmt(MYSQL * connecthandle) /作插入操作的绑定 MYSQL_BIND insertbindFEILD_NUM; if(m_stInsertParam = NULL) m_stInsertParam = new CHostCacheTable; m_stInsertStmt = mysql_stmt_init(connecthandle); /构建绑定字符串 char insertSQLSQL_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); insertbind0.buffer_type = MYSQL_TYPE_STRING; insertbind0.buffer_length = ID_LENGTH /* -1 */; insertbind0.buffer = (char *)m_stInsertParam-sessionid; insertbind0.is_null = 0; insertbind0.length = 0; insertbind1.buffer_type = MYSQL_TYPE_STRING; insertbind1.buffer_length = ID_LENGTH /* -1 */; insertbind1.buffer = (char *)m_stInsertParam-channelid; insertbind1.is_null = 0; insertbind1.length = 0; insertbind2.buffer_type = MYSQL_TYPE_TINY; insertbind2.buffer = (char *)&m_stInsertParam-ISPtype; insertbind2.is_null = 0; insertbind2.length = 0; insertbind3.buffer_type = MYSQL_TYPE_LONG; insertbind3.buffer = (char *)&m_stInsertParam-externalIP; insertbind3.is_null = 0; insertbind3.length = 0; insertbind4.buffer_type = MYSQL_TYPE_SHORT; insertbind4.buffer = (char *)&m_stInsertParam-externalPort; insertbind4.is_null = 0; insertbind4.length = 0; insertbind5.buffer_type = MYSQL_TYPE_LONG; insertbind5.buffer = (char *)&m_stInsertParam-internalIP; insertbind5.is_null = 0; insertbind5.length = 0; insertbind6.buffer_type = MYSQL_TYPE_SHORT; insertbind6.buffer = (char *)&m_stInsertParam-internalPort; insertbind6.is_null = 0; insertbind6.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; l 随机的获取记录在某些数据库的应用中, 我们并不是要获取所有的满足条件的记录,而只是要随机挑选出满足条件的记录. 这种情况常见于数据业务的统计分析,从大容量数据库中获取小量的数据的场合. 有两种方法可以做到1. 常规方法,首先查询出所有满足条件的记录,然后随机的挑选出部分记录.这种方法在满足条件的记录数很多时效果不理想.2. 使用limit语法,先获取满足条件的记录条数, 然后在sql查询语句中加入limit来限制只查询满足要求的一段记录. 这种方法虽然要查询两次,但是在数据量大时反而比较高效.示例代码如下: /1.常规的方法/性能瓶颈,10万条记录时,执行查询140ms, 获取结果集500ms,其余可忽略int CDBManager:QueryHostCache(MYSQL* connecthandle, char * channelid, int ISPtype, CDBManager:CHostCacheTable * &hostcache) char selectSQLSQL_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; iiReturnNumRows; i+) /获取逐个字段 m_Row = mysql_fetch_row(m_pResultSet); if(m_Row0 != NULL) strcpy(hostcachei.sessionid, m_Row0); if(m_Row1 != NULL) strcpy(hostcachei.channelid, m_Row1); if(m_Row2 != NULL) hostcachei.ISPtype = atoi(m_Row2); if(m_Row3 != NULL) hostcachei.externalIP = atoi(m_Row3); if(m_Row4 != NULL) hostcachei.externalPort = atoi(m_Row4); if(m_Row5 != NULL) ernalIP = atoi(m_Row5); if(m_Row6 != NULL) ernalPort = atoi(m_Row6); 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_Row0 != NULL) strcpy(hostcacheiSelectedIndex.sessionid, m_Row0); if(m_Row1 != NULL) strcpy(hostcacheiSelectedIndex.channelid, m_Row1); if(m_Row2 != NULL) hostcacheiSelectedIndex.ISPtype = atoi(m_Row2); if(m_Row3 != NULL) hostcacheiSelectedIndex.externalIP = atoi(m_Row3); if(m_Row4 != NULL) hostcacheiSelectedIndex.externalPort = atoi(m_Row4); if(m_Row5 != NULL) hostcacheiSelectedIernalIP = atoi(m_Row5); if(m_Row6 != NULL) hostcacheiSelectedIernalPort = atoi(m_Row6); /释放结果集内容 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 selectSQLSQL_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(row0); 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; iiReturnNumRows; i+) /获取逐个字段 row = mysql_fetch_row(pResultSet); if(row0 != NULL) strcpy(hostcachei.sessionid, row0); if(row1 != NULL) hostcachei.externalIP = atoi(row1); if(row2 != NULL) hostcachei.externalPort = atoi(row2); if(row3 != NULL) ernalIP = atoi(row3); if(row4 != NULL) ernalPort = atoi(row4); /释放结果集内容 mysql_free_result(pResultSet); return iReturnNumRows; l 使用连接池管理连接.在有大量节点访问的数据库设计中,经常要使用到连接池来管理所有的连接.一般方法是:建立两个连接句柄队列,空闲的等待使用的队列和正在使用的队列.当要查询时先从空闲队列中获取一个句柄,插入到正在使用的队列,再用这个句柄做数据库操作,完毕后一定要从使用队列中删除,再插入到空闲队列.设计代码如下: /定义句柄队列typedef std:list CONNECTION_HANDLE_LIST;typedef std:list: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; /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 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(