测试表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这样的聚合函数,有索引照样很慢。