sqlite3数据库查询、索引

Question

select COUNT(*) from SHTwflFault_Info where (endStn_id = 2 and Act_Time_sec > 1318212419 and Act_Time_sec < 1381370820) or (startStn_id = 2 and Act_Time_sec > 1318212419 and Act_Time_sec < 1381370820) GROUP BY FltLine_id

select FltLine_name, FltLine_lev, COUNT(*) AS nums from SHTwflFault_Info where (endStn_id = 2 and Act_Time_sec > 1318212419 and Act_Time_sec < 1381370820) or (startStn_id = 2 and Act_Time_sec > 1318212419 and Act_Time_sec < 1381370820) GROUP BY FltLine_id LIMIT 0, 200
上面两句都执行的很慢，要怎么改或创建索引加快速度呢？QQ：601367847

Answer 1

select COUNT(*) 
from SHTwflFault_Info 
where Act_Time_sec > 1318212419 and  Act_Time_sec < 1381370820
  and (startStn_id = 2 or endStn_id = 2)
group by FltLine_id

可以在Act_Time_sec上建索引

第2个sql同第1个，只是后面多了个limit

追问

不行啊，还是慢

别外如果建立了索引，数据有增加的话，索引要怎么处理下呢

追答

你索引建了？
Act_Time_sec > 1318212419 and Act_Time_sec < 1381370820这个条件选出的记录有多少？SHTwflFault_Info表中一共有多少记录？

追问

共有160w条以上，索引已经建立了，group by 用的时间长，如果去掉会快点，但功能就没有了

追答

Act_Time_sec > 1318212419 and Act_Time_sec < 1381370820这个条件筛选出多少记录？

追问

160w

追答

那SHTwflFault_Info表里一共有多少条记录呢？楼主为啥不能一次答全呢

追问

160万条以上啊

那个筛选就包含了全部的信息，就是想查下大数据量得多久

追答

对于第2个查询，如果选出了表中大多数记录，那数据库引擎在选择执行计划时会排除索引，因为即便用了索引进行过滤，它依然要去访问表中的FltLine_name和FltLine_lev，这样会导致硬盘IO比全表扫描表数据还要多，但对于第1个查询应该有效，因为索引数据占磁盘更小，只要查出记录条数即可

追问

是的，第二个有没有办法优化没

追答

sqlite把元数据、表、索引等对象全写在一个大文件中，而你又要读出表中所有记录，所以只能看看能否减小磁盘IO量，如果表中列比较多，而一些列或比较长的列不经常使用，可考虑把表进行横向划分，即分成2个表，把经常使用的放在一个表中，另一些不常用的放在另一个表里。
以你这里为例，把FltLine_name, FltLine_lev,Act_Time_sec,startStn_id,endStn_id放在一个表里，这样可使你的sql进行全表扫描时硬盘IO量比较小。

追问

数据更新的话，索引要处理么？

追答

sqlite我没用过，像oracle、sqlserver、db2等都是透明的，即索引是由数据库引擎动态维护的，我想sqlite也应该不需要

追问

也就是说只要创建一次就可以不用管了，增加数据时会自动修改索引？

追答

是这个意思

Answer 2

..
where 1=1
and (endStn_id = 2 or startStn_id = 2)
and Act_Time_sec > 1318212419
and Act_Time_sec < 1381370820
..

追问

"where 1=1"是什么意思

追答

没有实际意义，只是一种格式化的模式

追问

要不是有影响没？

追答

1=1 是一个恒True的逻辑表达式；没有影响。
where 1=1 之后可以每个"and .."条件表达式占一行，方便阅读而已

追问

也就相当于方便阅读？

那速度怎么优化呢

追答

从前面的查询条件分析：

and Act_Time_sec > 1318212419
and Act_Time_sec < 1381370820
“Act_Time_sec”是一定要有索引的

and (endStn_id = 2 or startStn_id = 2)
这里的or很讨厌，它将导致全表查询而非索引查询

另外：
FltLine_id 在group by 列表中，也建议建立索引

追问

那也就是说or弄的不能快查了

追答

似乎是这样