数据库索引

数据库索引

定义

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

本人对于索引理解较浅，如有错误或不足的地方，欢迎指正，共同学习。

优缺点

优点

• 在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系，增加数据的惟一性特点。

• 能提高数据的搜索及检索速度，符合数据库建立的初衷。

• 能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。

• 在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

• 建立索引之后，在信息查询过程中可以使用优化隐藏器，这对于提高整个信息检索系统的性能具有重要意义。

  缺点

• 在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。

• 在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，如果有必要建立起聚簇索引，所占用的空间还将进一步的增加

• 在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

索引类型

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。

提示：尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。

唯一索引

UNIQUE
create unique index 索引名 on 表名（表中的列[(length)]）
alter table 表名 add UNIQUE 索引名 (表中的列[(length)])

唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

主键索引

PRIMARY KEY  -- 建表时自接指定
alert table 表名 add primary key (表中的列[(length)])

数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序（物理顺序），读者如知道某个字的读音可根据字母顺序快速定位（索引顺序与物理顺序相同）。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。

建立普通索引或组合索引

create index 索引名 on 表名(列名);
create index 索引名 on 表名(列名1,列名2，..); -- 组合索引

组合索引 最左前缀 原则

个人理解 例如 ： create index index_abc on TEST_TAB(a,b,c); 相当于建立了 a, ab, abc 三组索引。

只要某查询条件中包含复合索引中的第一个列，该查询就会走索引，如果不包含，就不会走索引

有博主的实验 https://blog.csdn.net/tw7752/article/details/44595281

适合建立索引的情况

• 经常作为查询条件的列 （where）
• 经常用于表连接的列，如外键 （join）
• 经常用于排序的列 （order）

索引失效的sql

• 组合索引使用 or 索引失效 如 a=1 or b=2 or c=3

• 索引条件为 is null / is not null 索引失效(看清况，测试过 null 比较多时， not null 会走索引)

• 索引条件 like ‘%xxx’, 索引失效； like ‘xxxx%’ 索引生效

• 索引列参加计算 如 t.score/10 > 10 失效， 应改成 t.score > 10*10

• 索引列不要使用NOT （ != 、 <> ）如 t.score! = 10 失效，改成：t.score > 10 or t.score < 10

• 索引列上发生类型转换, 例如 VARCHAR2 类型的索引列 ，写成 where id = 1 ,应该 改成 where id = ‘1’ ( oracle实验)

最好是 看一下sql的执行计划，看看是否走了索引。

Oracle查看sql执行计划

explain plan for  
select xxx from tablename where xxx ;

select * from table(dbms_xplan.display); 

索引的原理 请看大神博客 https://blog.csdn.net/sinat_30186009/article/details/52169057

组合索引设计 https://blog.csdn.net/bless2015/article/details/84035845