提高M(jìn)ySQL查詢效率的三個(gè)技巧

1、提高M(jìn)ySQL查詢效率的三個(gè)技巧    提高M(jìn)ySQL 查詢效率的三個(gè)技巧正文：MySQL由于它本身的小巧和操作的高效, 在數(shù)據(jù)庫應(yīng)用中越來越多的被采用.我在開發(fā)一個(gè)P2P應(yīng)用的時(shí)候曾經(jīng)使用MySQL來保存P2P節(jié)點(diǎn),由于P2P的應(yīng)用中,結(jié)點(diǎn)數(shù)動(dòng)輒上萬個(gè),而且節(jié)點(diǎn)變化頻繁,因此一定要保持查詢和插入的高效.以下是我在使用過程中做的提高效率的三個(gè)有效的嘗試. 1. 使用statement進(jìn)行綁定查詢 2. 隨機(jī)的獲取記錄 3. 使用連接池管理連接.  MySQL由于它本身的小巧和操作的高效, 在數(shù)據(jù)庫應(yīng)用中越來越多的被采用.我在

2、開發(fā)一個(gè)P2P應(yīng)用的時(shí)候曾經(jīng)使用MySQL來保存P2P節(jié)點(diǎn),由于P2P的應(yīng)用中,結(jié)點(diǎn)數(shù)動(dòng)輒上萬個(gè),而且節(jié)點(diǎn)變化頻繁,因此一定要保持查詢和插入的高效.以下是我在使用過程中做的提高效率的三個(gè)有效的嘗試. l        使用statement進(jìn)行綁定查詢使用statement可以提前構(gòu)建查詢語法樹,在查詢時(shí)不再需要構(gòu)建語法樹就直接查詢.因此可以很好的提高查詢的效率. 這個(gè)方法適合于查詢條件固定但查詢非常頻繁的場合.使用方法是:綁定, 創(chuàng)建一個(gè)MYSQL_STMT變量,與對(duì)應(yīng)的查詢字符串綁定,字符串中的問號(hào)代表要傳入的

3、變量,每個(gè)問號(hào)都必須指定一個(gè)變量. 查詢, 輸入每個(gè)指定的變量, 傳入MYSQL_STMT變量用可用的連接句柄執(zhí)行. 代碼如下: /1.綁定bool CDBManager:BindInsertStmt(MYSQL * connecthandle)       /作插入操作的綁定       MYSQL_BIND insertbindFEILD_NUM;       if(m_stInsertParam =

4、NULL)              m_stInsertParam = new CHostCacheTable;       m_stInsertStmt = mysql_stmt_init(connecthandle);       /構(gòu)建綁定字符串       char insert

5、SQLSQL_LENGTH;       strcpy(insertSQL, "insert into HostCache(SessionID, ChannelID, ISPType, "              "ExternalIP, ExternalPort, InternalIP, InternalPort) "    

6、;          "values(?, ?, ?, ?, ?, ?, ?)");       mysql_stmt_prepare(m_stInsertStmt, insertSQL, strlen(insertSQL);       int param_count= mysql_stmt_param_count(m_stInsertStmt); 

7、60;     if(param_count != FEILD_NUM)              return false;       /填充bind結(jié)構(gòu)數(shù)組, m_sInsertParam是這個(gè)statement關(guān)聯(lián)的結(jié)構(gòu)變量       memset(insertbind, 0, sizeof(ins

8、ertbind);       insertbind0.buffer_type = MYSQL_TYPE_STRING;       insertbind0.buffer_length = ID_LENGTH /* -1 */;       insertbind0.buffer = (char *)m_stInsertParam->sessionid;     

9、  insertbind0.is_null = 0;       insertbind0.length = 0;        insertbind1.buffer_type = MYSQL_TYPE_STRING;       insertbind1.buffer_length = ID_LENGTH /* -1 */;       i

10、nsertbind1.buffer = (char *)m_stInsertParam->channelid;       insertbind1.is_null = 0;       insertbind1.length = 0;        insertbind2.buffer_type = MYSQL_TYPE_TINY;      

11、 insertbind2.buffer = (char *)&m_stInsertParam->ISPtype;       insertbind2.is_null = 0;       insertbind2.length = 0;        insertbind3.buffer_type = MYSQL_TYPE_LONG;     &

12、#160; insertbind3.buffer = (char *)&m_stInsertParam->externalIP;       insertbind3.is_null = 0;       insertbind3.length = 0;              insertbind4.buffer_type = MYSQL_TY

13、PE_SHORT;       insertbind4.buffer = (char *)&m_stInsertParam->externalPort;       insertbind4.is_null = 0;       insertbind4.length = 0;        insertbind5.buffer_type

14、 = MYSQL_TYPE_LONG;       insertbind5.buffer = (char *)&m_stInsertParam->internalIP;       insertbind5.is_null = 0;       insertbind5.length = 0;        insertbind6.buf

15、fer_type = MYSQL_TYPE_SHORT;       insertbind6.buffer = (char *)&m_stInsertParam->internalPort;       insertbind6.is_null = 0;       insertbind6.is_null = 0;       /綁定 

16、      if (mysql_stmt_bind_param(m_stInsertStmt, insertbind)              return false;       return true; /2.查詢bool CDBManager:InsertHostCache2(MYSQL * connecthandle, char * s

17、essionid, char * channelid, int ISPtype,               unsigned int eIP, unsigned short eport, unsigned int iIP, unsigned short iport)       /填充結(jié)構(gòu)變量m_sInsertParam       strcpy

18、(m_stInsertParam->sessionid, sessionid);       strcpy(m_stInsertParam->channelid, channelid);       m_stInsertParam->ISPtype = ISPtype;       m_stInsertParam->externalIP = eIP;   

19、0;   m_stInsertParam->externalPort = eport;       m_stInsertParam->internalIP = iIP;       m_stInsertParam->internalPort = iport;       /執(zhí)行statement,性能瓶頸處      

20、if(mysql_stmt_execute(m_stInsertStmt)              return false;       return true; l         隨機(jī)的獲取記錄在某些數(shù)據(jù)庫的應(yīng)用中, 我們并不是要獲取所有的滿足條件的記錄,而只是要隨機(jī)挑選出滿足條件的記錄. 這種情況常見于數(shù)據(jù)業(yè)

21、務(wù)的統(tǒng)計(jì)分析,從大容量數(shù)據(jù)庫中獲取小量的數(shù)據(jù)的場合.      有兩種方法可以做到1.       常規(guī)方法,首先查詢出所有滿足條件的記錄,然后隨機(jī)的挑選出部分記錄.這種方法在滿足條件的記錄數(shù)很多時(shí)效果不理想.2.       使用limit語法,先獲取滿足條件的記錄條數(shù), 然后在sql查詢語句中加入limit來限制只查詢滿足要求的一段記錄. 這種方法雖然要查詢兩次,但是在數(shù)據(jù)量大時(shí)反而比較高效.示例代碼如下: /1

22、.常規(guī)的方法/性能瓶頸,10萬條記錄時(shí),執(zhí)行查詢140ms, 獲取結(jié)果集500ms,其余可忽略int CDBManager:QueryHostCache(MYSQL* connecthandle, char * channelid, int ISPtype, CDBManager:CHostCacheTable * &hostcache)            char selectSQLSQL_LENGTH;       me

23、mset(selectSQL, 0, sizeof(selectSQL);       sprintf(selectSQL,"select * from HostCache where ChannelID = ''%s'' and ISPtype = %d", channelid, ISPtype);       if(mysql_real_query(connecthandle, selectSQL, strlen(sel

24、ectSQL) != 0)   /檢索              return 0;       /獲取結(jié)果集       m_pResultSet = mysql_store_result(connecthandle);       if(!m_pResultSet)

25、   /獲取結(jié)果集出錯(cuò)              return 0;       int iAllNumRows = (int)(mysql_num_rows(m_pResultSet);      /<所有的搜索結(jié)果數(shù)       /計(jì)算待返回的結(jié)果數(shù) 

26、0;     int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM)? iAllNumRows:RETURN_QUERY_HOST_NUM;       if(iReturnNumRows <= RETURN_QUERY_HOST_NUM)                &

27、#160;    /獲取逐條記錄              for(int i = 0; i<iReturnNumRows; i+)                          

28、         /獲取逐個(gè)字段                     m_Row = mysql_fetch_row(m_pResultSet);              &

29、#160;      if(m_Row0 != NULL)                            strcpy(hostcachei.sessionid, m_Row0);       

30、0;             if(m_Row1 != NULL)                            strcpy(hostcachei.channelid, m_Row1); &

31、#160;                   if(m_Row2 != NULL)                            hostc

32、achei.ISPtype      = atoi(m_Row2);                     if(m_Row3 != NULL)                 

33、           hostcachei.externalIP   = atoi(m_Row3);                     if(m_Row4 != NULL)        

34、60;                   hostcachei.externalPort = atoi(m_Row4);                     if(m_Row5 != NULL) 

35、0;                          ernalIP   = atoi(m_Row5);                &#

36、160;    if(m_Row6 != NULL)                            ernalPort = atoi(m_Row6);         

37、60;                                else                     /隨機(jī)的挑

38、選指定條記錄返回              int iRemainder = iAllNumRows%iReturnNumRows;    /<余數(shù)              int iQuotient = iAllNumRows/iReturnNumRows;   

39、60;  /<商              int iStartIndex = rand()%(iRemainder + 1);         /<開始下標(biāo)                /獲取逐條記錄  

40、      for(int iSelectedIndex = 0; iSelectedIndex < iReturnNumRows; iSelectedIndex+)                                &#

41、160;   mysql_data_seek(m_pResultSet, iStartIndex + iQuotient * iSelectedIndex);                            m_Row = mysql_fetch_row(m_pResultSet); 

42、0;                if(m_Row0 != NULL)                       strcpy(hostcacheiSelectedIndex.sessionid, m_Row0); 

43、                  if(m_Row1 != NULL)                              

44、;     strcpy(hostcacheiSelectedIndex.channelid, m_Row1);                   if(m_Row2 != NULL)                &

45、#160;      hostcacheiSelectedIndex.ISPtype      = atoi(m_Row2);                   if(m_Row3 != NULL)          

46、             hostcacheiSelectedIndex.externalIP   = atoi(m_Row3);                    if(m_Row4 != NULL)     &#

47、160;                 hostcacheiSelectedIndex.externalPort = atoi(m_Row4);                   if(m_Row5 != NULL)   &#

48、160;                   hostcacheiSelectedIernalIP   = atoi(m_Row5);                   if(m_Row6 != NUL

49、L)                       hostcacheiSelectedIernalPort = atoi(m_Row6);                     /釋

50、放結(jié)果集內(nèi)容       mysql_free_result(m_pResultSet);       return iReturnNumRows; /2.使用limit版int CDBManager:QueryHostCache(MYSQL * connecthandle, char * channelid, unsigned int myexternalip, int ISPtype, CHostCacheTable * hostcache) 

51、0;     /首先獲取滿足結(jié)果的記錄條數(shù),再使用limit隨機(jī)選擇指定條記錄返回       MYSQL_ROW row;       MYSQL_RES * pResultSet;       char selectSQLSQL_LENGTH;       memset(selectSQL, 0, sizeof(s

52、electSQL);        sprintf(selectSQL,"select count(*) from HostCache where ChannelID = ''%s'' and ISPtype = %d", channelid, ISPtype);       if(mysql_real_query(connecthandle, selectSQL, strlen(selectSQL) != 0)&

53、#160;  /檢索              return 0;       pResultSet = mysql_store_result(connecthandle);       if(!pResultSet)           

54、          return 0;       row = mysql_fetch_row(pResultSet);       int iAllNumRows = atoi(row0);       mysql_free_result(pResultSet);     &#

55、160; /計(jì)算待取記錄的上下范圍       int iLimitLower = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?              0:(rand()%(iAllNumRows - RETURN_QUERY_HOST_NUM);       int iLimitUpper = (iAl

56、lNumRows <= RETURN_QUERY_HOST_NUM)?              iAllNumRows:(iLimitLower + RETURN_QUERY_HOST_NUM);       /計(jì)算待返回的結(jié)果數(shù)       int iReturnNumRows = (iAllNumRows <= RETURN_QU

57、ERY_HOST_NUM)?               iAllNumRows:RETURN_QUERY_HOST_NUM;              /使用limit作查詢       sprintf(selectSQL,"select SessionID, Ext

58、ernalIP, ExternalPort, InternalIP, InternalPort "              "from HostCache where ChannelID = ''%s'' and ISPtype = %d limit %d, %d"           

59、0;  , channelid, ISPtype, iLimitLower, iLimitUpper);       if(mysql_real_query(connecthandle, selectSQL, strlen(selectSQL) != 0)   /檢索              return 0;     

60、60; pResultSet = mysql_store_result(connecthandle);       if(!pResultSet)              return 0;       /獲取逐條記錄       for(int i = 0; i<iReturnN

61、umRows; i+)                     /獲取逐個(gè)字段              row = mysql_fetch_row(pResultSet);         

62、;     if(row0 != NULL)                     strcpy(hostcachei.sessionid, row0);              if(row1 != NULL) &

63、#160;                   hostcachei.externalIP   = atoi(row1);              if(row2 != NULL)       &#

64、160;             hostcachei.externalPort = atoi(row2);              if(row3 != NULL)               &

65、#160;     ernalIP   = atoi(row3);              if(row4 != NULL)                     hostcac

66、ernalPort = atoi(row4);                           /釋放結(jié)果集內(nèi)容       mysql_free_result(pResultSet);       return iRet

67、urnNumRows; l         使用連接池管理連接.在有大量節(jié)點(diǎn)訪問的數(shù)據(jù)庫設(shè)計(jì)中,經(jīng)常要使用到連接池來管理所有的連接.一般方法是:建立兩個(gè)連接句柄隊(duì)列,空閑的等待使用的隊(duì)列和正在使用的隊(duì)列.當(dāng)要查詢時(shí)先從空閑隊(duì)列中獲取一個(gè)句柄,插入到正在使用的隊(duì)列,再用這個(gè)句柄做數(shù)據(jù)庫操作,完畢后一定要從使用隊(duì)列中刪除,再插入到空閑隊(duì)列.設(shè)計(jì)代碼如下: /定義句柄隊(duì)列typedef std:list<MYSQL *> CONNECTION_HANDLE_LIST;typedef std

68、:list<MYSQL *>:iterator CONNECTION_HANDLE_LIST_IT; /連接數(shù)據(jù)庫的參數(shù)結(jié)構(gòu)class CDBParameter             public:       char *host;             

69、60;                   /<主機(jī)名       char *user;                      

70、           /<用戶名       char *password;                         /<密碼    

71、60;  char *database;                           /<數(shù)據(jù)庫名       unsigned int port;         

72、        /<端口，一般為0       const char *unix_socket;      /<套接字，一般為NULL       unsigned int client_flag; /<一般為0; /創(chuàng)建兩個(gè)隊(duì)列CONNECTION_HANDLE_LIST m_lsBusyList;  

73、60;             /<正在使用的連接句柄CONNECTION_HANDLE_LIST m_lsIdleList;                  /<未使用的連接句柄 /所有的連接句柄先連上數(shù)據(jù)庫,加入到空閑隊(duì)列中,等待使用.bool CDBManager:Connect

74、(char * host /* = "localhost" */, char * user /* = "chenmin" */,                                     

75、;       char * password /* = "chenmin" */, char * database /* = "HostCache" */)       CDBParameter * lpDBParam = new CDBParameter();       lpDBParam->host = host;    &

76、#160;  lpDBParam->user = user;       lpDBParam->password = password;       lpDBParam->database = database;       lpDBParam->port = 0;       lpDBParam->unix_sock

77、et = NULL;       lpDBParam->client_flag = 0;       try                     /連接          

78、0;   for(int index = 0; index < CONNECTION_NUM; index+)                                   MYSQL * pConnectHandle = mysql_i

79、nit(MYSQL*) 0);     /初始化連接句柄                     if(!mysql_real_connect(pConnectHandle, lpDBParam->host, lpDBParam->user, lpDBParam->password,     

80、;  lpDBParam->database,lpDBParam->port,lpDBParam->unix_socket,lpDBParam->client_fla)                            return false;/加入到空閑隊(duì)列中  &#

81、160;                  m_lsIdleList.push_back(pConnectHandle);                            catch(.)                     return false;              return true;