服务器之家

服务器之家 > 正文

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

时间：2019-10-22 14:13     来源/作者：mysql技术网

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::listCONNECTION_HANDLE_LIST;

typedefstd::list::iteratorCONNECTION_HANDLE_LIST_IT;

//连接数据库的参数结构

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;

}

标签：
mysql 查询效率 

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思？女生说13579是什么意思？
男生常说24816是什么意思？女生说13579是什么意思？ 2019-09-17
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
抖音撒撒累累是什么歌 撒撒累累张艺兴歌曲名字
抖音撒撒累累是什么歌 撒撒累累张艺兴歌曲名字 2019-06-05
超A是什么意思 你好a表达的是什么
超A是什么意思 你好a表达的是什么 2019-06-06
返回顶部