SQLite索引和数据分析/清理

作者:课课家教育更新于： 2018-03-09 11:50:43

　　回忆一下小时候查字典的步骤，索引和字典目录的概念是一致的。字典目录可以让我们不用翻整本字典就找到我们需要的内容页数，然后翻到那一页就可以。索引也是一样，索引是对记录按照多个字段进行排序的一种展现。对表中的某个字段建立索引会创建另一种数据结构，其中保存着字段的值，每个值还包括指向与它相关记录的指针。这样，就不必要查询整个数据库，自然提升了查询效率。同时，索引的数据结构是经过排序的，因而可以对其执行二分查找，那就更快了。

　　一、创建索引：

　　创建索引是一个经久不衰的话题，在SQLite中，创建索引的SQL语法和其他大多数关系型数据库基本相同，因为这里也仅仅是给出示例用法：

　　那么我们如何更好的创建索引呢？

　　1、较频繁的作为查询条件的字段应该创建索引.

　　2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件：

　　唯一性太差的字段：如状态字段，类型字段等。这些字段即使创建了单独的索引，MySQLQueryOptimizer大多数也不会选择使用，如果什么时候选择了这种索引，可能会带来极大的性能问题。由于索引字段中每个值都含有大量的记录，那么存储引擎在根据索引访问数据的时候会带来大量的随机IO，甚至有时候可能还好出现大量的重复IO

　　3、更新非常频繁的字段不适合创建索引：

　　更新字段数据，同时还要更新索引的数据，以确保索引信息是准确的，这会增加IO访问量较大的增加，不仅仅影响更新query的响应时间，还会影响真个存储系统的资源消耗，加大存储系统的负责。

　　单一索引还是复合索引

　　对于多个where条件的数据，组合索引会比单一索引的查询效率要高，因为通过单一索引所能过滤的数据并不完整，和通过组合索引相比，存储引擎需要访问更多的记录。但是组合索引在多个字段中存在，更新的频率会带来一定性能消耗。

　　对于建立多个单键索引，MySQLQueryOptimizer大多数时候只会选择一个单键值的索引，然后放弃其他索引。即使他选择了同时利用两个或者更多的索引通过INDEX_MERGE来优化查询，可能所收到的效果并不会比选择其中某一个单键值索引高效。

　　因为如果选择了INDEX_MERGE来优化查询，就需要访问多个索引，同时还要将通过访问到的几个索引进行merge操作，所带来的成本可能反而会比选择其中一个最有效的索引来完成查询更高。

　　在创建组合索引并不是说将查询条件中所有字段放在一个索引中，我们还应该尽量让一个索引被多个query语句使用，尽量减少同一个表上面索引的数量，减少因为数据更新所带来的索引更新成本。同时还可以减少因为索引所带来的存储空间的消耗。

　　大多数场景下比较适合：

　　1、对于单键索引，尽量选择针对当前query过滤性更好的索引；

　　2、在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中排列越靠前越好；

　　3、在选择组合索引的时候，尽量选择可以能够包含当前query的where子句中更多字段索引；

　　4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的而减少通过使用Hint人为控制索引的选择，因为这会是后期的维护成本增加，同时增加维护所带来的潜在风险。

　　建议：

　　1、表的主键、外键必须有索引；

　　2、数据量超过300的表应该有索引；

　　3、经常与其他表进行连接的表，在连接字段上应该建立索引；

　　4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

　　5、索引应该建在选择性高的字段上；

　　6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

　　7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

　　A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

　　B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

　　C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

　　D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

　　E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

　　8、频繁进行数据操作的表，不要建立太多的索引；

　　9、删除无用的索引，避免对执行计划造成负面影响；

　　二、删除索引：

　　索引的删除和视图的删除非常相似，含义也是如此，因此这里也只是给出示例：

　　三、重建索引：

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

　　四、数据分析：

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

　　五、数据清理：

　　和PostgreSQL中的VACUUM命令相比，他们的功能以及实现方式非常相似，不同的是PostgreSQL提供了更细的粒度，而SQLite只能将该命令作用于数据库，无法再精确到数据库中指定的数据表或者索引，然而这一点恰恰是PostgreSQL可以做到的。

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

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