SQLite中的索引

简介

最近在编写一个小工具库,主要为了简化使用SQLite,避免一些重复性代码的编写。在这个过程中比较详细的了解了SQLite中的一些技术点,比如索引,对自己之前的认知也有蛮大提升,所以记录如下~

之前自己对索引的认知只停留在可以提高查询速度上,也没有完整的去了解索引的技术点,导致错误的使用索引。记得有一次,想为开发的APP进行调优,在数据库这块想提升下查询速度,为所有的主键都加上索引:)(SQLite数据库会为主键自动加上索引,不需要自己手动去加)。

索引的创建

索引的创建比较直观

1
CREATE INDEX index_name ON table_name(column_name...);

索引列可以指定单列或多列

1
2
CREATE INDEX name_index ON user(name); // 单列
CREATE INDEX name_address_index ON user(name, address); // 多列

UNIQUE 索引

索引也可以指定UNIQUE约束,跟在表的列上指定UNIQUE约束功能差不多,也可以对列进行约束,确保唯一性。当然还有一个功能是可以加快查询速度。其实可能想象一下,我们在图书馆找书时,有些书名字一样,但作者不一样,还得过去再看看哪本是我们要找的。但如果加入了唯一性约束,就可以减少这个步骤了,查找速度会更快

1
CREATE UNIQUE INDEX name_index ON user(name);

SQLite数据库会为表的主键自动创建一个索引,不需要自己主动去创建

1
sqlite_autoindex_tableName_1

在SQLite命令行中可以使用.indices table_name查看

创建索引时应该参考的几点意见

最让人迷惑的是索引应该在什么时候使用为好,怎样才能正确的使用索引,这个值得我们去推敲,下面是几点参考意见

  • 当表中的数据量比较少时,应该避免使用索引。其实也好理解,就那么一些数据,可以很快的找到我们要的数据,确实没有必要再建一个索引,因为索引也是需要维护的
  • 当表update或insert操作比较频繁时是不适合建索引的。可以拿我们的图书管来理解,如果有些类型的书籍基本上没什么人借阅,但经常会有新的书籍存入,这个时候就需要去更新索引,徒耗劳力
  • NULL出现次数比较多的列并不适合作为索引列。为NULL出现次数比较多的列建索引其实没有多大意义
  • 当某列或某几个列操作(CRUD)比较频繁时也不适合作为索引列。主要考量的是索引主要作为查询速度的优化,如果列的操作比较频繁,可能刚插入该列,马上又被删除或更新,会导致索引要经常更新或压根没起到该有的作用