你给数据库添加的索引是负优化吗--论如何合理使用SQLite索引.

索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。

1.前因.

在此之前我对于 数据库索引的认知一直停留在
给数据库字段加索引能提高查询速度,但是会影响新增和更新速度 这种程度上.
直到 网点反馈扫描卡顿,花时间排查原因,最后发现是 一条查询SQL,字段索引的问题上.

---

2.索引到底是个什么?

不清楚索引用法的,请看:索引是什么,有几种索引,如何创建,删除索引

我主要想说明的是 索引的原理,为什么我使用索引就能让我的查询变快,但是会影响新增和更新.

索引 是在主数据外 额外附加结构,主要是树.在查询的时候,以类似二分查找的形式提高查询速度.但是在新增和更新数据时,就需要修改索引对应的树结构,所以就比未添加索引要慢.

---

3.实际案例说明.

以这一次优化的数据作为实例,
说明 添加索引对查询带来的查询速度提升 以及 不合理添加索引对查询速度带来的影响

已知有一张表,表字段主要有 code(运单号),type(类型).
PDA巴枪每次扫描运单号操作,都会 根据扫描到的运单号和指定类型3 去查询表内有没有 对应的数据.

• 表数据数量:

数据不多,就8万条.

• 查询SQL

也很简单,使用GreenDao封装好的API进行查询操作.

QueryBuilder<TAddedServiceInfo> query = newQueryBuilder()
                .where(TAddedServiceInfoDao.Properties.Code.eq(billCode),
                        TAddedServiceInfoDao.Properties.Type.eq(type)
                );
return queryUnique(query);

现在直接在SQLite上面执行查询操作,通过耗时来说明问题.
用到的所有SQL语句:

//查询表数量
SELECT count(*) FROM bi_added_service_info 

//根据code和type去查询一行.
SELECT * FROM bi_added_service_info WHERE code='100000000011' AND type='3'

//创建code 单行索引
CREATE INDEX idx_code ON bi_added_service_info(code)
//创建type 单行索引
CREATE INDEX idx_type ON bi_added_service_info(type)
//创建 code,type 组合索引
CREATE INDEX idx_reject ON bi_added_service_info(code,type)

//删除对应的索引
DROP index idx_type
DROP index idx_code
DROP index idx_reject

• 不添加索引,直接查询

57毫秒

• 给Code字段添加索引

1毫秒

• 给Type字段添加索引

85毫秒

• 分别给Code 和 Type 字段添加单行索引

89毫秒

• 给Code和Type字段添加组合索引

1毫秒

这结果 是不是有几个 完全出乎意料,非常的 鹅妹子嘤.

---

4.案例结论说明.

这组数据有几个结论.

1. 合理的添加索引确实能够提升查询速度(未添加索引57毫秒,添加code字段索引1毫秒).
2. 添加一个不合适的索引反倒会 降低查询速度(type字段, 8W多条数据99.9%的数据type都一致,都为3).
3. 当查询字段为多个时,使用多个单行索引进行查询,不一定能够 提高查询速度.
4. 使用组合索引 能够提升查询速度.

为什么会这样呢?

结论1:合理的添加索引,如code字段, 表内数据 code内容均不相同,依靠 索引树结构,能够更快的查询到结果.

结论2:添加一个不合理的索引,如type字段, 表内数据99.9%的 type均一致,添加type索引反倒是让查询操作变的更加复杂,需要先根据索引树结构查询一遍,再全表查询一遍,反倒是 降低了查询速度.

结论3:当查询字段为多个,且存在多个单行索引,SQLite会根据查询优化策略选择其中一个单列索引,并不是每个单列索引都生效,且选取的不一定是最优索引。(如code和type2个索引,就选取了type索引,反倒是降低了速度).

结论4:当查询字段为多个,合理的判断数据,使用组合索引会更好.(需要考虑最左前缀原则,字段顺序很重要)

---

5.收获

在给数据库添加索引时,对数据的
规模(太少的数据就没必要)、
差异性(如type字段这种基本全表一致的就不要加索引,反倒是会拖慢查询速度)、
操作(更加频繁的查询才需要,如果数据新增和修改比查询还频繁就没必要)
多一份思考, 不要盲目的处理,有的时候这种操作更可能是 负优化.
事后可以对修改后的代码进行比对测试.

---

6.

网上找SQLite的资料找的很艰难,
最后把找到的几个资料贴在下面,感兴趣的可以看看.
也在这里感谢分享这些知识的朋友.

Android SQLite数据库查询优化方法
https://zhuanlan.zhihu.com/p/47313236

单列索引与联合索引
https://www.jianshu.com/p/7850b14c9e35

SQLite查询优化性能要点
https://www.cnblogs.com/dongweiq/p/5486433.html

通俗易懂 索引、单列索引、复合索引、主键、唯一索引、聚簇索引、非聚簇索引、唯一聚簇索引 的区别与联系
https://zhuanlan.zhihu.com/p/66553466