VC+ADO+MySQL执行select count(*)效率问题

 

测试过程中发现select count(*) from t where f1='';执行的时间超出预期,更不可理解的是该时间甚至超过了查询出这些符合条件的记录所消耗的时间.

 

1.环境
.MyODBC驱动:MySQL ODBC 5.1 Driver.

.连接方式:DSN-Less

.游标位置:adUseClient

.连接串

DRIVER={MySQL ODBC 5.1 Driver};SERVER=192.168.0.125;DATABASE=cqq_kic;USER=test;PASSWORD=ttt;OPTION=524292
其中,OPTION:524292(FLAG_DEBUG|FLAG_LOG_QUERY)
详细的OPTION说明见:
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html

如果使用MySQL ODBC 3.51 Driver,则同样的连接设置不会产生c:/myodbc.sql文件.
FLAG_DEBUG:This option was removed in Connector/ODBC 3.51.18.

.测试数据

--tb_1118记录数:339861.
--tb_1118 primary key(object_id)
--tb_1118 索引: idx_1(src_orgid,sheet_id,goodsid)

 

2.现象

SQLyog下执行的效果
查询1:SELECT COUNT(*) FROM tb_1118
耗时:2.6(秒)
查询2:SELECT COUNT(*) FROM tb_1118 WHERE src_orgid=10008 AND sheet_id='123';
耗时:0(秒)
(查询2利用了idx_1索引---explain分析)

本地服务器执行10次查询2的平均耗时超过3秒,二者差别非常大.

 

3.分析与处理

经程序测试发现,执行ADO查询时加锁类型的指定影响了查询速度.
在现有框架中,默认的LockTypeEnum是adLockOptimistic,默认的CursorTypeEnum是adOpenStatic.
IDbAccessor接口调整(推荐使用以下方法)
    virtual IRecordset* Query(const char *szCmd,CursorTypeEnum CursorType=adOpenForwardOnly,LockTypeEnum LockType=adLockReadOnly,CommandTypeEnum cmdType=adCmdText,long option=0,ParameterInfo* pParameters=NULL,int sz=0) = 0;
    virtual bool Execute(const char *szCmd,CommandTypeEnum cmdType=adCmdText,long option=0,ParameterInfo* pParameters=NULL,int sz=0)=0;

以下是对各种不同的游标类型和锁类型的效率比较的测试代码和结果:

测试代码:
int CXboxPlugin::Test3() {
    GETDBC(pdbor,this->local_dbc_.c_str());
    string sql = "SELECT COUNT(*) FROM tb_1118 WHERE src_orgid=10008 AND sheet_id='123'";
    st_key_name_pair v_lt[] = {
        {adLockUnspecified,"adLockUnspecified"},
        {adLockReadOnly,"adLockReadOnly"},
        {adLockPessimistic,"adLockPessimistic"},
        {adLockOptimistic,"adLockOptimistic"},
        {adLockBatchOptimistic,"adLockBatchOptimistic"},
    };
    st_key_name_pair v_ct[] =
    {
        {adOpenUnspecified,"adOpenUnspecified"},
        {adOpenForwardOnly ,"adOpenForwardOnly"},
        {adOpenKeyset,"adOpenKeyset"},
        {adOpenDynamic,"adOpenDynamic"},
        {adOpenStatic,"adOpenStatic"},
    };

    int count = 10;
    nlogger_->sync_log(LO_FILE|LO_STDOUT,SEVERITY_DEBUG,"ADO性能比较/n");
    nlogger_->sync_log(LO_FILE|LO_STDOUT,SEVERITY_DEBUG,"执行命令%s,取%d次的平均执行时间./n",sql.c_str(),count);
    for (int nct=0;nct<sizeof(v_ct)/sizeof(v_ct[0]);nct++) {
        for (int nlt=0;nlt<sizeof(v_lt)/sizeof(v_lt[0]);nlt++) {
            double f = 0;
            for (int i=0;i<count;i++) {
                clock_t begin = clock();
                CRecordset *prs = pdbor->Query(adCmdText,sql.c_str(),0,0,0,(CursorTypeEnum)v_ct[nct].key_,(LockTypeEnum)v_lt[nlt].key_);
                double duration = double(clock()-begin)/CLOCKS_PER_SEC;
                f += duration;
                pdbor->ReleaseRecordset(prs);
            }
            double v = f/count;
            nlogger_->sync_log(LO_FILE|LO_STDOUT,SEVERITY_DEBUG,"(%s,%s)耗时%.2f(秒)./n",v_ct[nct].name,v_lt[nlt].name,v);
        }

    }
    return 0;
}

测试输出结果:
[2011-05-04 18:36:05:359](线程2592)ADO性能比较
[2011-05-04 18:36:05:359](线程2592)正在加载插件[xbox.dll]......
[2011-05-04 18:36:05:375](线程2592)执行命令SELECT COUNT(*) FROM tb_1118 WHERE src_orgid=10008 AND sheet_id='123',取10次的平均执行时间.
[2011-05-04 18:36:05:406](线程2592)(adOpenUnspecified,adLockUnspecified)耗时0.00(秒).
[2011-05-04 18:36:05:421](线程2592)(adOpenUnspecified,adLockReadOnly)耗时0.00(秒).
[2011-05-04 18:36:37:593](线程2592)(adOpenUnspecified,adLockPessimistic)耗时3.22(秒).
[2011-05-04 18:37:09:125](线程2592)(adOpenUnspecified,adLockOptimistic)耗时3.15(秒).
[2011-05-04 18:37:40:843](线程2592)(adOpenUnspecified,adLockBatchOptimistic)耗时3.17(秒).
[2011-05-04 18:37:40:921](线程2592)(adOpenForwardOnly,adLockUnspecified)耗时0.01(秒).
[2011-05-04 18:37:40:937](线程2592)(adOpenForwardOnly,adLockReadOnly)耗时0.00(秒).
[2011-05-04 18:38:11:953](线程2592)(adOpenForwardOnly,adLockPessimistic)耗时3.10(秒).
[2011-05-04 18:38:44:359](线程2592)(adOpenForwardOnly,adLockOptimistic)耗时3.24(秒).
[2011-05-04 18:39:16:062](线程2592)(adOpenForwardOnly,adLockBatchOptimistic)耗时3.17(秒).
[2011-05-04 18:39:16:109](线程2592)(adOpenKeyset,adLockUnspecified)耗时0.00(秒).
[2011-05-04 18:39:16:140](线程2592)(adOpenKeyset,adLockReadOnly)耗时0.00(秒).
[2011-05-04 18:39:48:140](线程2592)(adOpenKeyset,adLockPessimistic)耗时3.20(秒).
[2011-05-04 18:40:19:140](线程2592)(adOpenKeyset,adLockOptimistic)耗时3.10(秒).
[2011-05-04 18:40:53:015](线程2592)(adOpenKeyset,adLockBatchOptimistic)耗时3.39(秒).
[2011-05-04 18:40:53:109](线程2592)(adOpenDynamic,adLockUnspecified)耗时0.01(秒).
[2011-05-04 18:40:53:140](线程2592)(adOpenDynamic,adLockReadOnly)耗时0.00(秒).
[2011-05-04 18:41:24:453](线程2592)(adOpenDynamic,adLockPessimistic)耗时3.13(秒).
[2011-05-04 18:41:55:750](线程2592)(adOpenDynamic,adLockOptimistic)耗时3.13(秒).
[2011-05-04 18:42:27:375](线程2592)(adOpenDynamic,adLockBatchOptimistic)耗时3.16(秒).
[2011-05-04 18:42:27:453](线程2592)(adOpenStatic,adLockUnspecified)耗时0.01(秒).
[2011-05-04 18:42:27:484](线程2592)(adOpenStatic,adLockReadOnly)耗时0.00(秒).
[2011-05-04 18:42:59:625](线程2592)(adOpenStatic,adLockPessimistic)耗时3.21(秒).
[2011-05-04 18:43:31:687](线程2592)(adOpenStatic,adLockOptimistic)耗时3.17(秒).
[2011-05-04 18:44:03:187](线程2592)(adOpenStatic,adLockBatchOptimistic)耗时3.15(秒).

 

检查myodbc.sql文件,发现锁类型为adLockPessimistic,adLockOptimistic,adLockBatchOptimistic时执行的SQL命令变成了2条:

(1)SELECT * FROM tb_1118;
(2)SELECT COUNT(*) FROM tb_1118 WHERE src_orgid=10008 AND sheet_id='123';
命令(1)的时间开销可想而知.

 

4.补充

对"SELECT * FROM tb_1118 WHERE goodsid='123"测试,发现所有的组合所耗时间都很接近,介于1.2-1.4秒之间.

 

这种调整能显著提高(不当使用锁类型情况下)select count(*)的效率,但除此之外并没有额外的改善。