Android SQLite数据库进行查询优化的方法
作者:想飞的鱼 发布时间:2022-10-03 09:09:00
前言
数据库的性能优化行业里面普遍偏少,今天这篇希望给大家带来点帮助
SQLite是个典型的嵌入式DBMS,它有很多优点,它是轻量级的,在编译之后很小,其中一个原因就是在查询优化方面比较简单
我们在使用SQLite进行数据存储查询的时候,要进行查询优化,这里就会用到索引,C端的数据量大部分情况下面虽然不是很大,但良好的索引建立习惯往往会带来不错的查询性能提升,同时在未知的将来经得住更大数据的考验,那如何优化数据库查询呢,下面我们用例子一一演示下。
先建个测试表table1,包含了三个索引:
sqlite> .schem
CREATE TABLE table1(id integer primary key not null default 0,a integer,b integer, c integer);
CREATE INDEX a_i on table1 (a);
CREATE INDEX a_i2 on table1 (a,b);
CREATE INDEX a_i3 on table1 (c);
在常见的数据库系统里面,进行SQL查询检验都是用explain关键字,比如:
sqlite> explain select * from table1;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 10 0 00 Start at 10
1 OpenRead 0 2 0 4 00 root=2 iDb=0; table1
2 Rewind 0 9 0 00
3 Rowid 0 1 0 00 r[1]=rowid
4 Column 0 1 2 00 r[2]=table1.a
5 Column 0 2 3 00 r[3]=table1.b
6 Column 0 3 4 00 r[4]=table1.c
7 ResultRow 1 4 0 00 output=r[1..4]
8 Next 0 3 0 01
9 Halt 0 0 0 00
10 Transaction 0 0 4 0 01 usesStmtJournal=0
11 Goto 0 1 0 00
立马就会得到输出,这些输出表示SQLite执行这条SQL用到的每句指令,这个其实不怎么直观,我们用到更多的是EXPLAIN QUERY PLAN,如下:
sqlite> explain QUERY PLAN select * from table1;
0|0|0|SCAN TABLE table1
这条SQL语句是查询了整张表,所以结果关键字SCAN表示要完整遍历,这种效率是最低的,接下来我们试试加个查询条件:
sqlite> explain QUERY PLAN select * from table1 where a=1;
0|0|0|SEARCH TABLE table1 USING INDEX a_i2 (a=?)
加上where a=1之后关键字变成了SEARCH,表示不再需要遍历了,而是使用了索引进行了部分检索,另外这条输出还有更多信息,比如使用了索引a_i2,而括号里面的a=?则表示是这个查询条件引起的
我们稍微修改下SQL:
sqlite> explain QUERY PLAN select a from table1 where a=1;
0|0|0|SEARCH TABLE table1 USING COVERING INDEX a_i (a=?)
把select 变成了select a,发现explain输出有细微变化,从INDEX变成了COVERING INDEX,CONVERING INDEX表示直接使用索引查询就可以得到结果,不需要再次回查数据表,这样效率更高。而之前的查询因为是使用,索引里面只有a记录,所以必须要查询原始记录才能得到b,c字段。我们再试下这条SQL:
sqlite> explain QUERY PLAN select a,b from table1 where a=1 and b=1;
0|0|0|SEARCH TABLE table1 USING COVERING INDEX a_i2 (a=? AND b=?)
同意因为索引a_i2已经包含a和b了,所以也是使用CONVERING INDEX。那有同学可能会问了,那我们建索引的时候都把其他字段都加进去呗,虽然查询用不到,但不用二次查询原始记录效率高。理论上这样是可行的,但这里有个重要问题就是数据冗余太严重了,导致索引和原始数据一样大,在海量数据存储的数据库里面磁盘消耗是个问题,所以如何选择可能要做个平衡。
接下来我们把and换成or:
sqlite> explain QUERY PLAN select a,b from table1 where a=1 or b=1;
0|0|0|SCAN TABLE table1 USING COVERING INDEX a_i2
发现又变回SCAN了,但仍然使用到了索引a_i2,对比下这条SQL:
sqlite> explain QUERY PLAN select a,b from table1 where a=1;
0|0|0|SEARCH TABLE table1 USING COVERING INDEX a_i2 (a=?)
多了个查询条件b=1之后效率变差了,这是为什么呢?这里要引出我们创建索引使用的最关键的原则:前缀索引。
索引一般是使用B树,前缀索引简单来讲,就是要想能使用这个索引,查询条件必须满足索引建立涉及到的字段,并且和查询使用的顺序一致。
我们回头看刚才那个or的例子,对于查询条件a=1,他能使用a_i2(a,b)这个索引,因为索引顺序也是a开头的。但or的例子里面还或上一个查询条件b=1,对于这个查询就没有索引可以用了,因为没有b开头的索引存在。a_i2(a,b)这个索引里面虽然有b,但b对于b=1这个查询条件来说不是在前面,不满足前缀索引原则。
而对于刚才那个and的例子,则能够完全使用索引,因为存在索引a_i2(a,b),可以想象成先按索引a过滤数据,剩下数据再用索引b过滤数据。对于and条件来说,索引里面字段的顺序换一下也是没有关系的,数据库会自动优化选择,比如:
sqlite> .schem
CREATE INDEX a_i22 on table2 (b,a);
sqlite> explain QUERY PLAN select a,b from table2 where a=1 and b=1;
0|0|0|SEARCH TABLE table2 USING COVERING INDEX a_i22 (b=? AND a=?)
如果or查询也要充分使用索引,聪明的读者一定想到了,那就是要建2个索引,如下:
CREATE TABLE table3(id integer primary key not null default 0,a integer,b integer, c integer);
CREATE INDEX a_i222 on table3(a);
CREATE INDEX a_i2222 on table3(b);
sqlite> explain QUERY PLAN select a,b from table3 where a=1 or b=1;
0|0|0|SEARCH TABLE table3 USING INDEX a_i222 (a=?)
0|0|0|SEARCH TABLE table3 USING INDEX a_i2222 (b=?)
我们再来看一个进阶的,加上一个排序:
CREATE TABLE table1(id integer primary key not null default 0,a integer,b integer, c integer);
CREATE INDEX a_i2 on table1 (a,b);
sqlite> explain QUERY PLAN select a,b from table1 where a=1 order by b;
0|0|0|SEARCH TABLE table1 USING COVERING INDEX a_i2 (a=?)
CREATE TABLE table3(id integer primary key not null default 0,a integer,b integer, c integer);
CREATE INDEX a_i222 on table3(a);
CREATE INDEX a_i2222 on table3(b);
sqlite> explain QUERY PLAN select a,b from table3 where a=1 order by b;
0|0|0|SEARCH TABLE table3 USING INDEX a_i222 (a=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY
对比这2个查询,发现下面这个多了个USE TEMP B-TREE FOR ORDER BY。对于第一个查询来说,我们可以看到排序也是同样满足前缀索引原则(先按索引a过滤数据,剩下数据用索引b排序)。对于第二个查询来说,因为不满足这个原则导致多了个临时表来做排序。看到这里大家应该理解前缀索引的意思了。
我们再看这个样子,把查询条件和排序换下:
sqlite> explain QUERY PLAN select a,b from table1 where b=1 order by a;
0|0|0|SCAN TABLE table1 USING COVERING INDEX a_i2
显然不满足前缀索引原则了,因为需要先按索引b过滤数据,但b不是第一个。
常规的查询语句大部分是and,or,order的组合使用,只需要掌握上面说的原则,一定能写出高性能的数据库查询语句来。
而对于更高级的一些连表可以继续翻阅官方文档:
https://www.sqlite.org/eqp.html
https://www.sqlite.org/lang_e...
来源:https://segmentfault.com/a/1190000016754594
猜你喜欢
- @AutoConfiguration读取所有jar包下的 /META-INF/spring.factories 并追加到一个 LinkedM
- 如果有哪一个做程序员的小伙伴说自己没有遇到中文乱码问题,我是不愿意相信的。今天在做微信订阅号的智能回复时,又一时迷乱的跳进了中文乱码这个火坑
- 本文主要对SpringBoot2.x参数校验进行简单总结,其中SpringBoot使用的2.4.5版本。一、引入依赖<dependen
- 累加数累加数 是一个字符串,组成它的数字可以形成累加序列。一个有效的 累加序列 必须 至少 包含 3 个数。除了最开始的两个数以外,序列中的
- 目录前言实现思路实现方法最简单的实现方法如果多线程乱入?线程安全的单例模式Lock版本静态构造器版本Lazy版本总结前言Singleton是
- Criteria的and和or进行联合查询DemoExample example=new DemoExample ();DemoExampl
- 参考:How to catch an Exception from a threadIs there a way to make Runna
- 在J2EE项目的开发中,不管是对底层的数据库操作过程,还是业务层的处理过程,还是控制层的处理过程,都不可避免会遇到各种可预知的、不可预知的异
- 有很多同学肯定想学习opencv相关的知识,但是有些情况下每建一次项目都要重新引入下各种文件是不是很苦恼,所以我也面临了这个问题,在网上看到
- 本文实例汇总了DevExpress SplitContainerControl的用法,希望对大家进行C#项目开发能起到一定的帮助作用。具体用
- 1. 场景描述本节结合springboot2、springmvc、mybatis、swagger2等,搭建一个完整的增删改查项目,希望通过这
- 由于GitHub上面的zxing功能太多,有的用不到,我就抽取了重要的出来使用,这个可以生成二维码,扫描二维码和相册中的二维码Demo效果:
- 这篇文章主要介绍了Spring 事件Application Event原理详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定
- 本文实例讲述了C#中static静态变量的用法。分享给大家供大家参考。具体如下:使用 static 修饰符声明属于类型本身而不是属于特定对象
- 1.初始化顺序当Java创建一个对象时,系统先为该对象的所有实例属性分配内存(前提是该类已经被加载过了),接着程序开始对这些实例属性执行初始
- 前言之前我们探讨过一个.class文件是如何被加载到jvm中的。但是jvm内又是如何划分内存的呢?这个内被加载到了那一块内存中?jvm内存划
- Java压缩文件与解压缩zip文件在日常的使用中经常会使用到像WinRAR或WinZIP这样的压缩文件,通过这些软件可以把一个很大的文件进行
- 2018年3月20日,Oracle发布java10。java10为java带来了很多新特性,其中让人眼前一亮的便是var关键字的引入。wha
- 一. 概念简介在开始学习今天的知识之前,有必要先给大家讲解一下与今天内容相关的一些概念,否则可能会让一些小白产生迷惑。1. 日期和时间的区别
- Java 实现汉字转换为拼音转换类public class PINYINChinese { private static int