提高MySQL 查询效率的三个技巧第1/2页

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

l        使用statement进行绑定查询
使用statement可以提前构建查询语法树,在查询时不再需要构建语法树就直接查询.因此可以很好的提高查询的效率. 这个方法适合于查询条件固定但查询非常频繁的场合.
使用方法是:
绑定, 创建一个MYSQL_STMT变量,与对应的查询字符串绑定,字符串中的问号代表要传入的变量,每个问号都必须指定一个变量. 
查询, 输入每个指定的变量, 传入MYSQL_STMT变量用可用的连接句柄执行. 
代码如下:

//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;
}

l         随机的获取记录
在某些数据库的应用中, 我们并不是要获取所有的满足条件的记录,而只是要随机挑选出满足条件的记录. 这种情况常见于数据业务的统计分析,从大容量数据库中获取小量的数据的场合.
      有两种方法可以做到
1.       常规方法,首先查询出所有满足条件的记录,然后随机的挑选出部分记录.这种方法在满足条件的记录数很多时效果不理想.
2.       使用limit语法,先获取满足条件的记录条数, 然后在sql查询语句中加入limit来限制只查询满足要求的一段记录. 这种方法虽然要查询两次,但是在数据量大时反而比较高效.
示例代码如下:

//1.常规的方法
//性能瓶颈,10万条记录时,执行查询140ms, 获取结果集500ms,其余可忽略
int CDBManager::QueryHostCache(MYSQL* connecthandle, char * channelid, int ISPtype, CDBManager::CHostCacheTable * &hostcache)
{     
       char selectSQL[SQL_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; 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;
}

当前1/2页 12下一页阅读全文

(0)

相关推荐

  • 提高MySQL 查询效率的三个技巧第1/2页

    MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试. l        使用statement进行绑定查询 使用statement可以提前构建查询语法树,在查询时不再需要构建语法树就直接查询.因此可以很好的提高查询的效率. 这个方法适合于查询条件固定但查询非常频繁的场合. 使

  • mysql in语句子查询效率慢的优化技巧示例

    表结构如下,文章只有690篇. 文章表article(id,title,content) 标签表tag(tid,tag_name) 标签文章中间表article_tag(id,tag_id,article_id) 其中有个标签的tid是135,查询标签tid是135的文章列表. 690篇文章,用以下的语句查询,奇慢: select id,title from article where id in( select article_id from article_tag where tag_id=

  • 提高MySQL深分页查询效率的三种方案

    开发经常遇到分页查询的需求,但是当翻页过多的时候,就会产生深分页,导致查询效率急剧下降.有没有什么办法,能解决深分页的问题呢?本文总结了三种优化方案,查询效率直接提升10倍,一起学习一下. 开发经常遇到分页查询的需求,但是当翻页过多的时候,就会产生深分页,导致查询效率急剧下降. 有没有什么办法,能解决深分页的问题呢? 本文总结了三种优化方案,查询效率直接提升10倍,一起学习一下. 1. 准备数据 先创建一张用户表,只在create_time字段上加索引: CREATE TABLE `user`

  • 提升MYSQL查询效率的10个SQL语句优化技巧

    MySQL数据库执行效率对程序的执行速度有很大的影响,有效的处理优化数据库是非常有用的.尤其是大量数据需要处理的时候. 1. 优化你的MySQL查询缓存 在MySQL服务器上进行查询,可以启用高速查询缓存.让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一.当同一个查询被执行多次时,如果结果是从缓存中提取,那是相当快的. 但主要的问题是,它是那么容易被隐藏起来以至于我们大多数程序员会忽略它.在有些处理任务中,我们实际上是可以阻止查询缓存工作的. // query cache does NOT

  • mysql中提高Order by语句查询效率的两个思路分析

    因为可能需要对数据库的记录进行重新排序.在这篇文章中,笔者就谈谈提高Order By语句查询效率的两个思路,以供大家参考. 在MySQL数据库中,Order by语句的使用频率是比较高的.但是众所周知,在使用这个语句时,往往会降低数据查询的性能.因为可能需要对数据库的记录进行重新排序.在这篇文章中,笔者就谈谈提高Order By语句查询效率的两个思路,以供大家参考. 498)this.width=498;" border=0> 一.建议使用一个索引来满足Order By子句. 在条件允许的

  • MySQL查询优化:LIMIT 1避免全表扫描提高查询效率

    在某些情况下,如果明知道查询结果只有一个,SQL语句中使用LIMIT 1会提高查询效率. 例如下面的用户表(主键id,邮箱,密码): 复制代码 代码如下: create table t_user( id int primary key auto_increment, email varchar(255), password varchar(255) ); 每个用户的email是唯一的,如果用户使用email作为用户名登陆的话,就需要查询出email对应的一条记录. SELECT * FROM t

  • MySQL 重写查询语句的三种策略

    在优化存在问题的查询时,我们需要改变方式去获取查询结果--但这并不意味着从 MySQL获取同样的结果集.有些时候我们可以将查询转换为获取相同结果,但更好性能的查询形式.然而,我们也需要考虑重写查询去获取不同的结果,因为这样可以提高开发效率.也可以通过修改应用程序代码来取得相同的效果.本篇文章将介绍如何重写查询的技巧. 复杂查询与分步查询 一个重要的查询设计课题是将复杂查询分解为多个简单查询是否会更好.在传统的数据库设计中强调尽可能地用更少的查询解决大量工作.在过往,这种方式会更好.这是因为以前的

  • 分享50个提高PHP执行效率的技巧

    1.用单引号代替双引号来包含字符串,这样做会更快一些.因为PHP会在双引号包围的字符串中搜寻变量, 单引号则不会,注意:只有echo能这么做,它是一种可以把多个字符串当作参数的"函数"(译注:PHP手册中说echo是语言结构,不是真正的函数,故 把函数加上了双引号). 2.如果能将类的方法定义成static,就尽量定义成static,它的速度会提升将近4倍. 3.$row['id'] 的速度是$row[id]的7倍. 4.echo 比 print 快,并且使用echo的多重参数(译注:

  • MySQL查询优化之查询慢原因和解决技巧

    在做开发的朋友特别是和mysql有接触的朋友会碰到有时mysql查询很慢,当然我指的是大数据量百万千万级了,不是几十条了, 下面我们来看看解决查询慢的办法 会经常发现开发人员查一下没用索引的语句或者没有limit n的语句,这些没语句会对数据库造成很大的影响,例如一个几千万条记录的大表要全部扫描,或者是不停的做filesort,对数据库和服务器造成io影响等.这是镜像库上面的情况. 而到了线上库,除了出现没有索引的语句,没有用limit的语句,还多了一个情况,mysql连接数过多的问题.说到这里

  • MySql数据库单表查询与多表连接查询效率对比

    这段时间在做项目的过程中,遇到一个模块,数据之间的联系很复杂,在建表的时候就很纠结,到底该怎么去处理这些复杂的数据呢,是单表查询,然后在业务层去处理数据间的关系,还是直接通过多表连接查询来处理数据关系呢? 通过查阅资料和阅读博客,有以下两个回答: 一.<高性能mysql>中的回答 很多高性能的应用都会对关联查询进行分解.简单地,可以对每个表进行一次单表查询,然后将结果在应用程序中进行关联.例如,下面这个查询: select * from tag join tag_post on tag_pos

随机推荐