mysql 数据库查询测试_MySQL查询测试经验

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

CREATE TABLE`geoinfo` (

`objectid`int(11) NOT NULLAUTO_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%'sql4:

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%'sql6: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 fromgeoinfo_tiny a,geoinfo_tiny bwhere 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 fromgeoinfo_tiny ainner join geoinfo_tiny b on a.time=b.timewhere a.cabid='acitva' and b.cabid='abjoolaw'

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

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