目录

一、什么是聚集索引

二、非聚集索引

索引失效的几种情况（MySQL）

什么情况下要避免使用索引？

索引的创建方式

索引的使用

一、什么是聚集索引

一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

　　聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

　　聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

二、非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

索引失效的几种情况（MySQL）

1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

2.如果是组合索引的话，如果不按照索引的顺序进行查找，比如直接使用第三个位置上的索引而忽略第一二个位置上的索引时，则会进行全表查询

3.条件中有or

4.索引无法存储null值，所以where的判断条件如果对字段进行了null值判断，将导致数据库放弃索引而进行全表查询，如


select id from t where num is null

5.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

6.in 和 not in 也要慎用，否则会导致全表扫描

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

–name以abc开头的id

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

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

select id from t where num/2=100

应改为: 

select id from t where num=100*2

什么情况下要避免使用索引？

虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。

使用索引时，需要考虑下列准则：

  • 索引不应该使用在较小的表上。
  • 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
  • 索引不应该使用在含有大量的 NULL 值的列上。
  • 索引不应该使用在频繁操作的列上。

查看索引的几种方式（pgsql库）

  1. select * from pg_indexes where tablename='prm_prom_bill_rule';

创建索引

create index tbl_bb_index on tbl_bb(id,name);

注：tbl_bb 为表名称， tbl_bb_index 为创建的索引名称， id 和 name 为 表里的字段

删除索引

DROP INDEX index_name;

执行计划（分析查询流程：耗时情况，索引情况）

explain analyze select * from table;