• MySQL查询测试经验


    测试表geoinfo,整个表超过1100万行,表结构:

    CREATE TABLE `geoinfo` (
    `objectid`  int(11) NOT NULL AUTO_INCREMENT ,
    `latitude`  double NOT NULL ,
    `longitude`  double NOT NULL ,
    `occupancy`  bit(1) NOT NULL ,
    `time`  datetime NOT NULL ,
    `cabid`  varchar(16) NOT NULL ,
    PRIMARY KEY (`objectid`),
    INDEX `idx_geoinfo_cabid_time`( `cabid`,`time`) USING BTREE 
    )
    ENGINE=InnoDB
    AUTO_INCREMENT=1

    time字段为datetimes类型,建立了与cabid字段(varchar类型)的组合索引,整个表1100万+行。

    测试结果:

    1. between比"大于a and 小于b"效率稍高一点点,仅仅是一点点:

    sql1: select time,objectid,cabid from geoinfo where time(time) BETWEEN time('07:00:00') and time('12:00:00')# and cabid='acitva'
    sql2: select time,objectid,cabid from geoinfo where  time(time)>=time('07:00:00') and time(time)<=time('12:00:00')

    sql1耗时10.180秒,sql2耗时11.760秒。

    但一旦在where子句中加上cabid字段,即,select time,objectid,cabid from geoinfo where time(time) BETWEEN time('07:00:00') and time('12:00:00') and cabid='acitva'

    耗时立刻减少到0.040秒。

    2.or效率超过union all,且or的次数越多差距越明显(与网上多数的所谓"优化经验"不同):

    sql 3:select time,objectid,cabid from geoinfo where cabid like'a%' or cabid like 'b%' or cabid like 'e%'
    
    sql 4:
    select time,objectid,cabid from geoinfo where cabid like'a%' union all select time,objectid,cabid from geoinfo where cabid like'b%' union all select time,objectid,cabid from geoinfo where cabid like'e%'

    sql3的执行时间为6.590,7.090,6.880秒,多数为6.9秒以内;

    sql4的执行时间为7.892,8.452,7.912秒。两者相差1-1.5秒。

    sql 5: select time,objectid,cabid from geoinfo where cabid like'a%' or cabid like 'b%'
    sql 6:
    select time,objectid,cabid from geoinfo where cabid like'a%'
    union all select time,objectid,cabid from geoinfo where cabid like'b%'

    sql 5的执行时间依次为,3.050,3.089,3.200秒

    sql6的执行时间依次为,3.562,3.792,3.760秒,两者相差0.5秒左右。

    而把like改为'='号时,

    select time,objectid from geoinfo where cabid='udwadla' or cabid='osacmu' or cabid='unquekov'

    or与union all差不多。

    3.索引似乎只对简单的sql语句有提升,复杂一点的还是很慢。

    例如:

    select a.objectid as Aobjectid,b.objectid AS Bobjectid,a.time as Time from geoinfo_tiny a,geoinfo_tiny b
    where a.time=b.time and a.cabid='acitva' and b.cabid='abtyff'

    其中对于geoinfo_tiny这样只有280万行的表,该语句执行时间就为95.361-100.004秒。索引似乎没什么用了。

    试试连接查询:

    select a.objectid as Aobjectid,b.objectid AS Bobjectid,a.time as Time from geoinfo_tiny  a 
    inner join geoinfo_tiny b on a.time=b.time
    where a.cabid='acitva' and b.cabid='abjoolaw'

    多改几次cabid的值,防止缓存,测试结果为95.635,39.172,85.862秒,可见连接查询和多表查询区别不大。

    4.对于使用count这样的聚合函数,有索引照样很慢。

  • 相关阅读:
    JPA报错 javax.persistence.EntityNotFoundException: Unable to find XX类 with id xx问题
    Spring-Data-JPA api文档
    一道小数数学题
    pycharm 关联py之后sqlmap的使用
    base64和base32替换编码解密
    Mysql 启动失败
    Xshell连接linux时常见问题
    使用metasploit 框架保持持久性
    获得shell 、启用远程服务
    Java Class Loader
  • 原文地址:https://www.cnblogs.com/aaronhoo/p/5162811.html
Copyright © 2020-2023  润新知