百万级数据表查询速度过慢

我有这样一张数据表，结构如下：
CREATE TABLE IF NOT EXISTS AlarmInfo(
Guid VARCHAR(50) PRIMARY KEY NOT NULL
,DBM VARCHAR(6) NOT NULL
,AlarmType INT(12) NOT NULL
,AlarmLevel INT(12) NOT NULL
,BgnTime datetime NOT NULL
,RecoveryTime datetime NOT NULL
,DevType INT(12) NOT NULL
,DevDNA INT(12) NOT NULL
,DataType INT(12) NOT NULL
,IsInMaintain INT(12) NOT NULL
,Dealer VARCHAR(50) NOT NULL
,DealState INT(12) NOT NULL
,DealInfo VARCHAR(1024) NOT NULL
,Elaborate VARCHAR(1024) NOT NULL
,RecoveryInfo VARCHAR(1024) NOT NULL
,InActive INT(12) NOT NULL
,KEY AlarmInfo_Index(DBM,BgnTime,RecoveryTime)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

表里总共有150w左右的记录，每天2000多条，我的查询语句是：
select SQL_NO_CACHE * from alarminfo where DBM = 'TCC' and ((BgnTime >='2015-07-25 15:04:00' AND BgnTime <= '2015-07-26 15:04:00')  OR  (RecoveryTime >='2015-07-25 15:04:00' AND RecoveryTime <='2015-07-26 5:04:00')) order by bgntime desc; 
每次查询差多需要6.6s左右，我索引改了好几次，效果都不是很理想，麻烦各位高手帮看看还能怎么优化才能提高查询速度？

8 个解决方案

#1

OR改成UNION ALL
索引改成DBM和两个时间字段单独组合
排序改成延迟关联的方式

#2

索引：
KEY BgnTime_Index(DBM,BgnTime)
KEY RecoveryTime_Index(DBM,RecoveryTime);

语句是这样么？
(select * from alarminfo where DBM = 'TCC' and BgnTime BETWEEN '2015-01-03 08:20:00' AND '2015-01-04 08:20:00') union all (select * from alarminfo where dbm='TCC' and RecoveryTime BETWEEN '2016-01-03 08:20:00' AND'2016-01-04 08:20:00')  order by BgnTime desc;

延迟关联的语句怎么写的，刚接触MySQL，不是太熟悉，希望高手不吝赐教

#3

你的这个语句运行之后是返回多少条记录。

索引是对于返回少量数据，作用才会特别大。

另外，如果按照上面的方式修改了语句，把数据union all之后，还是 会有一个 排序的动作，可能也会导致慢，你先试试按照上面修改之后，语句是否更快了

#4

select * from alarminfo join
(
select guid from alarminfo where DBM = 'TCC' and BgnTime BETWEEN '2015-01-03 08:20:00' AND '2015-01-04 08:20:00'
 union all 
select guid from alarminfo where dbm='TCC' and RecoveryTime BETWEEN '2016-01-03 08:20:00' AND'2016-01-04 08:20:00'
) as t1 
on(t1.guid=alarminfo.guid) order by...
大概意思是这样，但是不一定有用，没有万金油法则，重点是用explain反复调试，找出最优方案
不过你的主键设计比较差，varchar(50)已经够糟糕了，如果生成规则还是随机字符串，那简直完全没起到聚簇的作用。如果不是业务必须还是改成int自增

#5

guid这个主键由于业务和程序处理，目前确实不太好改。

效果不是太理想，explain:
1 PRIMARY <derived2> ALL 2010 Using temporary; Using filesort
1 PRIMARY alarminfo eq_ref PRIMARY PRIMARY 152 t1.guid 1
2 DERIVED alarminfo range Index_BgnTime Index_BgnTime 25 2008 Using where; Using index
3 UNION alarminfo range Index_RecoveryTime Index_RecoveryTime 25 1 Using where; Using index
UNION RESULT <union2,3> ALL Using temporary

show profile:
starting 0.000070
checking permissions 0.000003
checking permissions 0.000001
checking permissions 0.000003
Opening tables 0.000135
init 0.000017
System lock 0.000006
optimizing 0.000002
optimizing 0.000008
statistics 0.008827
preparing 0.000016
optimizing 0.000009
statistics 0.000041
preparing 0.000009
statistics 0.000019
preparing 0.000007
Creating tmp table 0.000092
Sorting result 0.000003
executing 0.000006
Sending data 0.000009
executing 0.000001
Sending data 0.001645
executing 0.000005
Sending data 0.000023
optimizing 0.000003
statistics 0.000005
preparing 0.000005
executing 0.000001
Sending data 0.000618
removing tmp table 0.000008
Sending data 8.513545
converting HEAP to MyISAM 0.007497
Sending data 3.673658
Creating sort index 0.027564
end 0.000010
removing tmp table 0.004200
end 0.000010
query end 0.000015
closing tables 0.000003
removing tmp table 0.000023
closing tables 0.000014
freeing items 0.000177
logging slow query 0.000053
cleaning up 0.000020

而且有个问题一直搞不明白，为什么每次停止服务，再启动，执行该表的查询语句，都会慢到令人无法忍受，最长可达30s以上，修改查询条件再查询几次，才会稳定到某个值左右。

#6

我的表每天都是2000条记录，不过现在查一天的信息也不快，更别说查多天了，之前用union试过，也不是很理想，就像yupeigu说的，通过show profile查看，发现Create Sort Index比较费时，最快也得3s多才能执行完这个操作；

#7

而且有个问题一直搞不明白，为什么每次停止服务，再启动，执行该表的查询语句，都会慢到令人无法忍受，最长可达30s以上，修改查询条件再查询几次，才会稳定到某个值左右。
数据库缓存造成的。。。。。。。
你先试着用单个日期查询，即 BgnTime 
在用RecoveryTime 查询。
最后用2个日期一起查询 看看差别在哪里

#8

修改存储引擎为MyISAM，然后通过分页查询的方法算是暂时解决这个问题了。