数据库-索引

使用索引是提高数据库查询效率的主要方式。需要注意的是，在创建索引时要谨慎，因为索引会占用磁盘空间，并在写操作时增加数据库的负担。

 

一、索引结构

　　在MySql 5.5版本以上的默认存储引擎是 InnoDB，只显式支持 BTree (MySql中称之为BTree，在数据结构上来说是B+树)索引。对于被频繁访问的表，innodb 会自动建立自适应 hash 索引。(即在B树索引上建立 hash 索引，可以显著提高查找效率，用户无法自己指定)。除此之外，还有 Hash 索引和全文索引。

　　BTree 索引

　　其所有的索引节点都按照 balance tree 的数据结构来存储。BTree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。BTree中，每个节点包含：

1. 本节点中所含关键字个数；
2. 指向父结点的指针；
3. 关键字；
4. 指向子结点的指针。

　　Hash 索引

　　由于 Hash 索引使用了 hash 表结构，其检索效率很高，索引的检索可以一次定位，不需要像B+树那样从根节点到枝节点。所以 Hash 索引的查询效率要远高于 B+ 树索引。但是，Hash 索引的使用范围非常有限。

1. 在MySql 中，只有HEAP/MEMOPY 引擎表才能支持哈希索引，InnoDB 引擎的自适应哈希索引(adaptive hash index)不能在创建索引时指定；
2. Hash 索引仅支持=，>=，<= 这几种条件运算，不支持排序、范围内查找、like等查询；
3. Hash 索引不支持组合索引中部分索引的查找；
4. 当 Hash 值重复较多时，索引速度不如BTree 索引。

　　FullText 索引

　　即平时所说的全文索引，其存储结构也是BTree。主要是为了解决 LIKE 查询的低效问题。

 

二、索引类型

　　普通索引、唯一索引、复合索引

　　注意：

　　唯一索引要求字段中不会出现重复数据。

　　复合索引：将多个字段组合起来作为索引，必须使用到该索引中的第一个字段作为条件时才能保证系统使用到该索引，否则该索引不会被使用。

 

三、索引操作

　　创建索引：create index 索引名 on 表明 （索引字段名）

　　创建唯一索引：create unique index 索引名 on 表明 （索引字段名）

　　创建复合索引：create index 索引名 on 表明 （索引字段名1，索引字段名2.....）

　　查看表中索引：show index from 表名

　　删除索引：drop index 索引名 on 表名

　　查看索引是否命中：

　　MySql:当结果的type为index时索引命中，explain + sql语句

　　Oracle: explain plan for + sql语句

 

四、命中索引

1. 避免判断 null 值

　　　　　　应尽量避免在 where 子句中对字段进行null值判断，因为会导致引擎放弃使用索引而进行全表扫描。如：

　　　　　　select id from t where num is null

　　　　　　可以在num值上设置默认值0，确保表中num列没有null值，然后这样查询：

　　　　select id from t where num = 0

2. 避免不等值判断

　　　　　　应尽量避免在 where 子句中使用 != 或 <>操作符，因为这样会导致引擎放弃使用索引而进行全表扫描。

3. 避免使用 or 逻辑

　　　　　　应尽量避免在 where 子句中使用 or 来连接条件，因为这样会导致引擎放弃使用索引而进行全表扫描，如：

　　　　　　select id from t where num = 1 or num = 2

　　　　　　可以使用 union all 查询来命中索引：

　　　　　　

　　　　　　select id from t where num = 1 union all select id from t where num = 2

4. 避免 in 和 not in 逻辑

　　　　　　in 和 not in 也要慎用，因为也会导致全表扫描，如：

　　　　　　select id from t1 where num in (select id from t2 where id > 10)

　　　　　　此时外层查询会全表扫描，不使用索引。

　　　　　　可以修改为：

　　　　　　select id from t1,(select id from t1 where num > 10)t2 where t1.id = t2.id

5. 注意模糊查询

　　　　　　此模糊查询也会导致全表扫描：

　　　　　　select id from t where name like '%abc%'

　　　　　　不要在 LIKE 条件左边加 '%'，因为这样不会走索引。如果模糊查询是必要条件时，可以使用

　　　　　　select id from t where name like 'abc%'

　　　　　　来实现模糊查询，此时索引将被使用。

　　　　　　如果头匹配是必要的，可以使用 elasticsearch 等全文搜索引擎。

6. 避免查询条件中字段计算

　　　　　　应尽量避免在 where 子句中对字段进行表达式操作，因为这样会导致引擎放弃使用索引而进行全表扫描，如

　　　　　　select id from t where num / 2 =100

　　　　　　应改为

　　　　　　select id from t where num = 100*2

7. 避免查询条件中对字段进行函数操作

　　　　　　应尽量避免在 where 子句中对字段进行函数操作，因为这样会导致引擎放弃使用索引而进行全表扫描，如：

　　　　　　select id from t where substring(name,1,3) = 'abc'

　　　　　　应改为：

　　　　　　select id from t where name like 'abc%'

8. "=" 左边避免表达式计算

　　　　　　不要在 where 子句中的'='左边进行函数、算数运算或其他表达式运算，因为这样会导致系统可能无法正确使用索引。

9. 组合索引使用

　　　　　　在使用索引字段作为条件时，若该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用到该索引，否则该索引不会被使用，并且应尽可能的让字段顺序与索引顺序一致。

10. exists

　　　　　　select num from a where num in(select num from b)

　　　　　　很多时候要用 exists 代替 in 是一个很好的选择

　　　　　　select num from a where exists(select 1 from b where num = a.num)

 

五、使用场景

　　要对数据库的某列或某几列创建索引，需要考虑以下几点：

1. 数据量较大，且经常对这些列进行条件查询；
2. 该数据库表的插入操作、以及对这些列的修改操作频率较低；
3. 索引会占用额外的磁盘空间

　　反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。