数据库索引是什么

今天学习了一些数据库索引的知识简单整理一下，做个记录：

一、什么是索引？

1.一句话总结：索引是用来存储数据表中指定列值的一种数据结构，可以有效提高检索速度。

2.既然索引是一种数据结构，哪些数据结构用于索引：

  1).B-Tree（B-树）--是最常用的用于索引的数据结构

      特性：⑴时间复杂度低， 查找、删除、插入操作都可以在对数时间内完成。

                 ⑵ 在B-Tree中的数据是有序的。

  2).哈希索引--寻找值时哈希表效率极高，对于比较字符串是否相等的查询能够极快的检索出值

      特性：⑴哈系索引是将列的值作为索引的键值（key），和键值相对应实际的值（value）是指向该表中相应行的指针

      缺点：⑵无顺的数据结构，对于对比数据大小时间长短等方面不如B-tree效率高 。

  3).R-Tree

      特性：常用来解决空间问题（比如“查询出所有距离我两公里之内的星巴克”）

  4).位图索引

      特性：这类索引适合放在包含布尔值(true 和 false)的列上。

3.索引的类型

主键索引（PRIMAY KEY）

唯一索引（UNIQUE）

常规索引（INDEX）

全文索引（FULLTEXT）

normal即常规的B-Tree索引，字段值没限制

unique是唯一索引，列中字段值不允许重复

二、索引是如何提高查询性能的？

1、有序性：

     假设我们在表Employee的 Employee_Name这一列上创建一个B-Tree索引。这意味着当我们用SQL(SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’) 查找姓名是‘Jesus’的雇员时，不需要再扫描全表。而是用索引查找去查找名字为‘Jesus’的雇员，因为索引已经按照按字母顺序排序。索引已经排序意味着查询一个名字会快很多，因为名字少字母为‘J’的员工都是排列在一起的。

2、指针：

   索引中除了存储列的值，还存储着一个指向在行数据的索引。也就是说，索引中的Employee_Name这列的某个值（或者节点）可以描述为 (“Jesus”, 0x82829)， 0x82829 就是包含 “Jesus”那行数据在硬盘上的地址。如果没有这个引用，你就只能访问到一个单独的值（“Jesus”），而这样没有意义，因为你不能获取这一行记录的employee的其他值-例如地址（address）和年龄（age）。

3、数据库自动识别

   当这个SQL （SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’ ）运行时，数据库会检查在查询的列上是否有索引。假设Employee_Name列上确实创建了索引，数据库会接着检查使用这个索引做查询是否合理。（补充但是有些场景下，使用索引比起全表扫描会更加低效）

三、如何创建索引？

1、创建索引

创建标准索引： CREATE  INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 

创建唯一索引: CREATE unique INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 

创建组合索引: CREATE INDEX 索引名 ON 表名 (列名1,列名2)  TABLESPACE 表空间名; 

创建反向键索引: CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名; 
2、索引使用原则

索引字段建议建立NOT NULL约束 

经常与其他表进行连接的表，在连接字段上应该建立索引； 

经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引； 

可选择性高的关键字 ，应该建立索引； 

可选择性低的关键字，但数据的值分布差异很大时，选择性数据比较少时仍然可以利用索引提高效率 

复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的第一个字段，一般是选择性较好的且在where子句中常用的字段上； 

B、复合索引的几个字段经常同时以AND方式出现在Where子句中可以建立复合索引；否则单字段索引； 

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引； 

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； 

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 

频繁DML的表，不要建立太多的索引； 

不要将那些频繁修改的列作为索引列； 

四、索引的缺点以及什么情况下索引会失效？
1、索引会占用空间 - 你的表越大，索引占用的空间越大

2、性能损失（主要值更新操作），当你在表中添加、删除或者更新行数据的时候， 在索引中也会有相同的操作

3、如果条件中有or，即使其中有条件带索引也不会使用（要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引）

4、.like查询是以%开头

5、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

6、如果mysql估计使用全表扫描要比使用索引快,则不使用索引

查看索引的使用情况：

show status like ‘Handler_read%’;
注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数

handler_read_rnd_next:这个值越高，说明查询低效

7、not in ,not exist

8、单独引用复合索引里非第一位置的索引列. 

9、使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引. 
错误的例子：select * from test where round(id)=10; 
说明，此时id的索引已经不起作用了

正确的例子：首先建立函数索引， 
create index test_id_fbi_idx on test(round(id)); 
然后 select * from test where round(id)=10; 这时函数索引起作用了

10、<> 或者单独的>,<

 

学习文章：

https://www.jianshu.com/p/b72d3ab9e54a