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