Android Sqlite查询优化之一---运用索引

2018-03-31 09:37:33

前言

  • 最近笔者在做聊天功能模块,发现当本地聊天数据记录过大,以10万行数据进行了检索测试,发现时间太长了,要6s左右,但学着运用了下索引,时间大大提升,紧要几百毫秒就能完成.

  • 以下内容,摘抄至网络

参考链接

内容

假如你有一个表,

SQL> CREATE TABLE test_tab ( 
2 id INT, 
3 name VARCHAR(10), 
4 age INT, 
5 val VARCHAR(10) 
6 );

你的业务,有一个查询,是 
SELECT * FROM test_tab WHERE name = 一个外部输入的数据

刚开始,数据不多的时候,执行效果还不错。 
随着数据量的增加,这个查询,执行起来,越来越慢了。

然后在 name 上面 建立了索引 
CREATE INDEX idx_test4_name ON test_tab (name ); 
这样, 可以加快前面那个查询的速度。

但是,某天,你执行了下面这个SQL, 发现速度又慢了 
SELECT * FROM test_tab WHERE age = 25 
为啥呢? 因为 age 字段上面,没有索引 
索引只在 name 上面有

换句话说, 也就是 WHERE 里面的条件, 会自动判断,有没有 可用的索引,如果有, 该不该用。

多列索引,就是一个索引,包含了2个字段。

例如: 
CREATE INDEX idx_test_name_age ON test_tab (name, age);

那么 
SELECT * FROM test_tab 
WHERE 
name LIKE ‘张%’ 
AND age = 25 
这样的查询,将能够使用上面的索引。

多列索引,还有一个可用的情况就是, 某些情况下,可能查询,只访问索引就足够了, 不需要再访问表了。例如:

SELECT 
AVG( avg ) AS 平均年龄 
FROM 
test_tab 
WHERE 
name LIKE ‘张%’

这个时候, name 与 age 都包含在索引里面。 查询不需要去检索表中的数据。

  1. 创建索引

在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。 
1.ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。 
2.CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。 
3.索引类型

在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。

下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid) 
4. 删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

其中,前两条语句是等价的,删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

5.查看索引

mysql> show index from tblname;

mysql> show keys from tblname;

  · Table

  表的名称。

  · Non_unique

  如果索引不能包括重复词,则为0。如果可以,则为1。

  · Key_name

  索引的名称。

  · Seq_in_index

  索引中的列序列号,从1开始。

  · Column_name

  列名称。

  · Collation

  列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。

  · Cardinality

  索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。

  · Sub_part

  如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。

  · Packed

  指示关键字如何被压缩。如果没有被压缩,则为NULL。

  · Null

  如果列含有NULL,则含有YES。如果没有,则该列含有NO。

  · Index_type

  用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

  · Comment 
mysql 创建索引和删除索引

索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。 
(1)使用ALTER TABLE语句创建索引。 
语法如下: 
alter table table_name add index index_name (column_list) ; 
alter table table_name add unique (column_list) ; 
alter table table_name add primary key (column_list) ; 
其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式,table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。 
创建索引的示例如下: 
mysql> alter table table_test add index index_test1(name) ; 
Query OK, 2 rows affected (0.08 sec) 
(2)使用CREATE INDEX语句对表增加索引。 
能够增加普通索引和UNIQUE索引两种。其格式如下: 
create index index_name on table_name (column_list) ; 
create unique index index_name on table_name (column_list) ;

创建索引的示例如下:

mysql>create index index_test2 on table_test(age);

Query OK, 2 rows affected (0.08 sec)

说明:table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。 
(3)删除索引。 
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理,其格式如下: 
drop index index_name on table_name ; 
alter table table_name drop index index_name ; 
alter table table_name drop primary key ; 
其中,在前面的两条语句中,都删除了table_name中的索引index_name。而在最后一条语句中,只在删除PRIMARY KEY索引中使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。 
如果从表中删除某列,则索引会受影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。 
删除索引的操作,如下面的代码: 
mysql> drop index name on table_test ; 
Query OK, 2 rows affected (0.08 sec)


  • 2018-11-17 21:05:48

    Android ANR发生的原因总结和解决办法

    ANR的全称是application not responding,是指应用程序未响应,Android系统对于一些事件需要在一定的时间范围内完成,如果超过预定时间能未能得到有效响应或者响应时间过长,都会造成ANR。一般地,这时往往会弹出一个提示框,告知用户当前xxx未响应,用户可选择继续等待或者Force Close。

  • 2018-11-17 21:08:09

    Android 单个应用的内存限制

    获取Android手机应用内存大小 手机不同其性能也不同,手机本身内存可能有大有小,所以针对每个应用的内存大小也不相同。

  • 2018-11-17 21:11:14

    Android中App可分配内存的大小

     结果:(1)未设定属性android:largeheap = "true"时,可以申请到的最大内存空间为221M。      (2)设定属性android:largeheap = "true"时, 可以申请的最大内存空间为478M,是原来的两倍多一些。

  • 2018-11-17 22:44:53

    LeakCanary,30分钟从入门到精通

    在性能优化中,内存是一个不得不聊的话题;然而内存泄漏,显示已经成为内存优化的一个重量级的方向。当前流行的内存泄漏分析工具中,不得不提的就是LeakCanary框架;这是一个集成方便, 使用便捷,配置超级简单的框架,实现的功能却是极为强大的。

  • 2018-11-17 22:53:01

    gc for alloc freed

    在数组中选择图片然后显示,然后。。。logcat不断显示GC回收。最后程序黑屏。

  • 2018-11-17 23:25:38

    Android高效内存1:一张图片占用多少内存

    在做内存优化的时候,我们发现除了解决内存泄露问题,剩下的就只有想办法减少真实的内存占用。而在App中,大部分内存可能被我们图片占用了,所以减少图片的内存占用可以带来直接的效果。本文就简单介绍一张图片到底占用多少内存,我们先假设我们有一张图片时 600 * 800 的,图片占用空间大小假设是 100KB。另外本文知识点也是面试官喜欢问的一个点,看看自己的回答到什么级别了。

  • 2018-11-18 09:06:06

    Android子线程中更新UI的3种方法

    UI的更新必须在主线程中完成,所以不管上述那种方法,都是将更新UI的消息发送到了主线程的消息对象,让主线程做处理。

  • 2018-11-19 15:10:23

    nodemailer的使用,nodejs发送邮件

    前段时间有个很普通的项目需要发邮件的功能,而且是刚开始学nodejs,所以只是搜索了下用什么好的库能实现,就找到了nodemailer了。这篇文章主要是记录一下使用的过程和经验。