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

[峰白]

我有这样一张数据表，结构如下：
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左右，我索引改了好几次，效果都不是很理想，麻烦各位高手帮看看还能怎么优化才能提高查询速度？

[峰白]

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以上，修改查询条件再查询几次，才会稳定到某个值左右。

[峰白]

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

[ylovep]

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

[大雨将至]

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

[峰白]

索引： 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，不是太熟悉，希望高手不吝赐教

[LongRui888]

你的这个语句运行之后是返回多少条记录。 索引是对于返回少量数据，作用才会特别大。 另外，如果按照上面的方式修改了语句，把数据union all之后，还是 会有一个 排序的动作，可能也会导致慢，你先试试按照上面修改之后，语句是否更快了

[大雨将至]

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自增

[峰白]

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