SQLite 数据库索引的知识、建立、使用方法和优化

索引的目的是加速数据的查询，下面介绍一些SQLite 数据库索引的知识、建立、使用方法和优化的相关操作。

在SQLite中，有以下几种索引：

• 单列索引
• 多列索引
• 唯一性索引
• PRIMARY KEY 主键

对于声明为：INTEGER PRIMARY KEY的主键来说，这列会按默认方式排序，所以虽然在数据字典中没有对它生成索引，但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话，这样既浪费空间也没有任何好处。

运用索引的注意事项

• 对于一个很小的表来说没必要建立索引
• 在一个表上如果经常做的是插入更新操作，那么就要节制使用索引
• 也不要在一个表上建立太多的索引，如果建立太多的话那么在查询的时候SQLite可能不会选择最好的来执行查询，一个解决办法就是建立聚蔟索引

索引的运用时机

1) 操作符：=、>、<、IN等

2) 操作符BETWEEN、LIKE、OR不能用索引，

   如BETWEEN：SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20;<br/>
   这时就应该将其转换成：<br/>
   SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20;<br/>
   此时如果在myfield上有索引的话就可以用了，大大提高速度<br/>
   再如LIKE：SELECT * FROM mytable WHERE myfield LIKE 'sql%';<br/>
   此时应该将它转换成：<br/>
   SELECT * FROM mytable WHERE myfield >= 'sql' AND myfield < 'sqm';<br/>
   此时如果在myfield上有索引的话就可以用了，大大提高速度<br/>
   再如OR：SELECT * FROM mytable WHERE myfield = 'abc' OR myfield = 'xyz';<br/>
   此时应该将它转换成：<br/>
   SELECT * FROM mytable WHERE myfield IN ('abc', 'xyz');<br/>
   此时如果在myfield上有索引的话就可以用了，大大提高速度<br/>

3) 有些时候索引都是不能用的，这时就应该遍历全表

SQL code: 不能使用索引的查询

SELECT * FROM mytable WHERE myfield % 2 = 1;
       SELECT * FROM mytable WHERE substr(myfield, 0, 1) = 'w';
       SELECT * FROM mytable WHERE length(myfield) < 5;

SQLite 创建索引

plain code: SQLite 创建索引

sqlite> CREATE TABLE testtable (first_col integer,second_col integer);
    --创建最简单的索引，该索引基于某个表的一个字段。
    sqlite> CREATE INDEX testtable_idx ON testtable(first_col);
    --创建联合索引，该索引基于某个表的多个字段，同时可以指定每个字段的排序规则(升序/降序)。
    sqlite> CREATE INDEX testtable_idx2 ON testtable(first_col ASC,second_col DESC);
    --创建唯一性索引，该索引规则和数据表的唯一性约束的规则相同，即NULL和任何值都不同，包括NULL本身。
    sqlite> CREATE UNIQUE INDEX testtable_idx3 ON testtable(second_col DESC);
    sqlite> .indices testtable
    testtable_idx
    testtable_idx2    
    testtable_idx3

从.indices命令的输出可以看出，三个索引均已成功创建。

SQLite 删除索引

SQLite 删除索引

sqlite> DROP INDEX testtable_idx;
    --如果删除不存在的索引将会导致操作失败，如果在不确定的情况下又不希望错误被抛出，可以使用"IF EXISTS"从句。
    sqlite> DROP INDEX testtable_idx;
    Error: no such index: testtable_idx
    sqlite> DROP INDEX IF EXISTS testtable_idx;

SQLite 重建索引

重建索引用于删除已经存在的索引，同时基于其原有的规则重建该索引。这里需要说明的是，如果在REINDEX语句后面没有给出数据库名，那么当前连接下所有Attached数据库中所有索引都会被重建。如果指定了数据库名和表名，那么该表中的所有索引都会被重建，如果只是指定索引名，那么当前数据库的指定索引被重建。

SQLite 重建索引

--当前连接attached所有数据库中的索引都被重建。
    sqlite> REINDEX;
    --重建当前主数据库中testtable表的所有索引。
    sqlite> REINDEX testtable;
    --重建当前主数据库中名称为testtable_idx2的索引。
    sqlite> REINDEX testtable_idx2;

SQLite 数据分析

SQLite中的ANALYZE命令用于分析数据表和索引中的数据，并将统计结果存放于SQLite的内部系统表中，以便于查询优化器可以根据分析后的统计数据选择最优的查询执行路径，从而提高整个查询的效率。见如下示例：

SQLite 数据分析

--如果在ANALYZE命令之后没有指定任何参数，则分析当前连接中所有Attached数据库中的表和索引。
    sqlite> ANALYZE;
    --如果指定数据库作为ANALYZE的参数，那么该数据库下的所有表和索引都将被分析并生成统计数据。
    sqlite> ANALYZE main;
    --如果指定了数据库中的某个表或索引为ANALYZE的参数，那么该表和其所有关联的索引都将被分析。
    sqlite> ANALYZE main.testtable;
    sqlite> ANALYZE main.testtable_idx2;

SQLite 数据清理

当某个数据库中的一个或多个数据表存在大量的插入、更新和删除等操作时，将会有大量的磁盘空间被已删除的数据所占用，在没有执行VACUUM命令之前，SQLite并没有将它们归还于操作系统。由于该类数据表中的数据存储非常分散，因此在查询时，无法得到更好的批量IO读取效果，从而影响了查询效率。

在SQLite中，仅支持清理当前连接中的主数据库，而不能清理其它Attached数据库。VACUUM命令在完成数据清理时采用了和PostgreSQL相同的策略，即创建一个和当前数据库文件相同大小的新数据库文件，之后再将该数据库文件中的数据有组织的导入到新文件中，其中已经删除的数据块将不会被导入，在完成导入后，收缩新数据库文件的尺寸到适当的大小。该命令的执行非常简单，如：

sqlite> VACUUM;

看看自己需不需要建立索引

总的来说，索引是用空间换性能、速度，看看自己的应用需不需要建立索引。

一个测试数据参考：10w条数据，没有索引的情况下，查询一条数据大约需要550ms以上。建立索引后，数据库的体积增大了3倍左右，但是同样的查询却减少到8ms的级别，提升了70倍。