服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - 提高MySQL 查询效率的三个技巧

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

2019-10-22 14:13mysql技术网 Mysql

MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使

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
  • 查询效率

    • 延伸 · 阅读

    精彩推荐
    • Mysqlmysql 不能插入中文问题

      mysql 不能插入中文问题

      当向mysql5.5插入中文时，会出现类似错误 ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column ...

      MYSQL教程网5722019-11-25
    • MysqlMySQL数据库varchar的限制规则说明

      MySQL数据库varchar的限制规则说明

      本文我们主要介绍了MySQL数据库中varchar的限制规则，并以一个实际的例子对限制规则进行了说明，希望能够对您有所帮助。 ...

      mysql技术网4192019-11-23
    • Mysql解决MySQl查询不区分大小写的方法讲解

      解决MySQl查询不区分大小写的方法讲解

      今天小编就为大家分享一篇关于解决MySQl查询不区分大小写的方法讲解，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起...

      Veir_dev5592019-06-25
    • Mysql浅谈mysql 树形结构表设计与优化

      浅谈mysql 树形结构表设计与优化

      在诸多的管理类，办公类等系统中，树形结构展示随处可见，本文主要介绍了mysql 树形结构表设计与优化，具有一定的参考价值，感兴趣的小伙伴们可以参...

      小码农叔叔5242021-11-16
    • MysqlMySQL锁的知识点总结

      MySQL锁的知识点总结

      在本篇文章里小编给大家整理了关于MySQL锁的知识点总结以及实例内容，需要的朋友们学习下。...

      别人放弃我坚持吖4362020-12-14
    • MysqlMySQL 数据备份与还原的示例代码

      MySQL 数据备份与还原的示例代码

      这篇文章主要介绍了MySQL 数据备份与还原的相关知识,本文通过示例代码给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下...

      逆心2972019-06-23
    • MysqlERROR: Error in Log_event::read_log_event()

      ERROR: Error in Log_event::read_log_event()

      ERROR: Error in Log_event::read_log_event(): read error, data_len: 438, event_type: 2 ...

      MYSQL教程网6412020-03-13
    • Mysql详解MySQL中的分组查询与连接查询语句

      详解MySQL中的分组查询与连接查询语句

      这篇文章主要介绍了MySQL中的分组查询与连接查询语句,同时还介绍了一些统计函数的用法,需要的朋友可以参考下 ...

      GALAXY_ZMY5442020-06-03
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1449 　 1053 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 2003 　 1175 　 1215 　 1615 　 1054 　 2006 　 1130 　 1366 　 10048 　 28000 　 1786 　 1135 　 1577 　 1665 　 1067 　 1093 　
    © 2019-2024 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图