不正确的collate造成sqlite索引失效?
问题描述:
例如Sqlite中有一张表A,分别有字段ID,field1,field2,field3,field4,主键为ID,在field4上建立索引:create index [index_A] on [A] ([field4] collate nocase); field4为varchar类型。
A表数据量15万左右,假设有查询语句:select * from A where field4='具体值' 和 select * from A where field4 like '%具体值%'
发现两者的执行时间竟然都差不多,select * from A where field4='具体值' 中field4索引并没有起作用。此时select * from A where field4 like '具体值' 执行时间反而会更短。
可能原因:
建索引时指定了"collate nocase",可能导致索引失效了。另:"collate rtrim"也可能会造成索引失效。
解决方法:
只需在创建索引时去掉"collate nocase"即可,默认为"collate binary"。去掉"collate nocase"之后,查询时间明显缩短,索引起作用了。
附:
CREATE INDEX命令由“CREATE INDEX”关键字后跟新索引的名字,关键字“ON”,待索引表的名字,以及括弧内的用于索引键的字段列表构成。每个字段名可以跟随“ASC”或“DESC”关键字说明排序法则,但在当前版本中排序法则被忽略。排序总是按照上升序。
每个字段名后跟COLLATE子句定义文本记录的比较顺序。缺省的比较顺序是由CREATE TABLE语句说明的比较顺序。若不定义比较顺序,则使用内建的二进制比较顺序。
附加到单个表上的索引数目没有限制,索引中的字段数也没有限制。
若UNIQUE关键字出现在CREATE和INDEX之间,则不允许重名的索引记录。试图插入重名记录将会导致错误。
每条CREATE INDEX语句的文本储存于sqlite_master或sqlite_temp_master表中,取决于被索引的表是否临时表。
每次打开数据库时,所有的CREATE INDEX语句从sqlite_master表中读出,产生SQLite的索引样式的内部结构。
若使用可选的IF NOT EXISTS子句,且存在同名索引,则该命令无效。