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-05-21 13:54:06

    laravel-nestedset:多级无限分类正确姿势

    Nested Set Model 是一种实现有序树的高明的方法,它快速且不需要递归查询,例如不管树有多少层,你可以仅使用一条查询来获取某个节点下的所有的后代,缺点是它的插入、移动、删除需要执行复杂的sql语句,但是这些都在这个插件内处理了!

  • 2018-06-02 00:29:58

    Android实现沉浸式状态栏的那些坑

    最近项目需要实现沉浸式的状态栏,其实我在之前就了解过Android的沉浸式,发现有些棘手就放弃了,但是此次是公司的项目需要的,就花了几天把这个问题搞定了,在此记录一下,并mark几个坑。 首先,沉浸式是Android 4.4及以上才有的,在后续的5.0及6.0上面都增加了一些相关支持,于是问题就不太好办了。先看我实现的效果:

  • 2018-06-02 00:30:35

    Android 解决沉浸式状态栏下,输入法弹出,布局不会自动调整的BUG

    在开发中,如果输入框在布局的底部。在弹出输入发时,为了使输入法不遮挡输入框通常有两种做法: 1.将布局压缩(Activity的android:windowSoftInputMode属性设置为”adjustResize”)。 2.移动布局,将布局顶到输入框之上(Activity的android:windowSoftInputMode属性设置为”adjustPan”)

  • 2018-06-02 00:31:27

    Android layout实现输入法弹出后,布局整体上移

    那如果要实现沉浸式状态栏又要保持布局不会被输入法遮挡,怎么办呢? 只要在根布局加上android:fitsSystemWindows=”true”即可,效果如下(为方便看效果我把背景改成了黄色):

  • 2018-06-04 10:06:43

    mysql查询数据的同时对它进行删除操作

    今天遇见一个问题,需要把mysql数据库里面的 商品主表 和它的每一个条目对应不上的数据给全部删除(数据如下图);也就是整理一下数据库里的数据保证数据的可用;