測試表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這樣的聚合函數，有索引照樣很慢。