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）比较频繁时也不适合作为索引列。主要考量的是索引主要作为查询速度的优化，如果列的操作比较频繁，可能刚插入该列，马上又被删除或更新，会导致索引要经常更新或压根没起到该有的作用