数据库索引

数据库索引

定义

数据库索引，是数据库管理系统中一个排序的数据结构，将数据库表中的某一列或几列以特定的数据结构独立于表外存起来，以协助快速查询、更新数据库表中数据。

分类

• 唯一索引
  不允许两行具有相同的索引值，可能存在空值
• 主键索引
  为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
• 聚集索引
  表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
• 非聚集索引
  非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

优缺点

• 优点

  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

    • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
    • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

• 缺点

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  • 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

常见问题

最左匹配原则

MySQL中key和index的区别和联系

区别： 索引（index）和约束（key）的区别主要在于二者的出发点不同：

• 索引(index)负责维护表的查找和操作速度，是数据库的物理结构，处于实现层面的；
• 约束(key)负责维护表的完整性，是关系模型理论中的一部份。
  在理论上是不能将MySQL的key和index划等号的，他们不是一回事，但在实际使用中，他们基本没有区别。

联系： MySQL中有一个奇怪现象：

• MySQL中的索引是约束性索引（即创建索引自动也会创建约束）
• 并且MySQL中创建约束也会自动附带索引。如MySQL中的约束效果是通过索引来实现的，MySQL数据库判断是否当前列是否unique就是通过unique索引判断的。

• primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引；
• unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引；
• foreign key 也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；

主键和索引的关系

	主键	索引
本质	唯一索引（索引的一种），但是重点在于唯一	重点在于检索（查询）
功能上	用于标识数据库记录唯一性	提高查询速度
特点	不能为空	可以为空（唯一索引也可以为空）
和表的关系	0个或1个	>=0个
两者关系	主键必定是唯一索引，创建主键时，DBMS对主键自动生成唯一索引	一个表可以有多个唯一索引，创建主键后，索引必定存在

主键和聚集索引的关系

	主键（即唯一索引）	聚集索引
表中个数	最多1个	最多1个
对应属性值	可以多个，必须唯一非空	可以多个，可以不唯一，可以为空
两者关系	主键可以是聚集索引或者非聚集索引。数据库在创建主键同时，会自动建立一个唯一索引。如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引

聚集索引和非聚集索引的区别

	聚集索引	非聚集索引
本质区别	用于确定表中记录的物理顺序，记录存放的物理位置和聚集索引一致	记录存放的物理位置不需要和非聚集索引一致
个数	表中唯一	表中不唯一
实现结构	B+树，索引的叶节点就是数据记录	B+树，索引的叶节点仍然是索引节点，只不过有一个指针指向对应数据块
检索特点	查询速度快，一旦第一个具有索引值的记录被找到，具有连续索引值的记录也一定物理地紧随其后；对表进行增删速度比非聚集慢，因为需要保持表中记录的物理顺序和索引一致，增删数据项是，必须对数据页重排	增删记录不会引起数据顺序的重组
适合的列	此列包含的不同值不是很多；此列查询结果返回一个区间的值，或者返回结果集较大	此列包含的不同值数量较大；此列查询结果返回的结果集较小；order by使用了该列

为什么聚集索引只能有一个

因为聚集索引定义了表中数据的物理存储顺序。数据文件中数据记录的排列顺序与聚集索引文件中索引项的排列顺序相一致，或者说，聚集索引文件按照其查找码指定的顺序与数据文件中数据记录的排列顺序相一致。

• 表在没有加主键和索引时，他都数据无序的放置在磁盘存储器上，一行一行的排列很整齐，跟认知中的“表”很接近。
• 如果给表加上了聚集索引，那么表在磁盘上的存储结构就由整齐排列的结构变成了树状结构，也就是整个表编程了一个索引。也就是说，聚集索引把表的数据格式转换成了“索引（平衡树）”的格式放置。

哪些列适合建立索引，哪些类不适合创建索引

• 适合创建索引的列：
  • 在经常需要搜索的列上，可以加快搜索的速度；
    （1）表经常进行 SELECT 操作
    （2）列名经常在 WHERE 子句
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  • 表很大(记录超多)，记录内容分布范围很广
• 不适合创建索引的列：
  • 查询中很少使用，不需要在此列上加快搜索速度
  • 当增删改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。
  • 有很少数据值的列。这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。只增加索引，并不能加快检索速度。
  • 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大（占用存储空间大），要么取值很少。

---

参考文献

https://blog.csdn.net/hxpjava1/article/details/55803923 索引
https://www.jianshu.com/p/4c2a2b0ef3e0 索引
https://troywu0.gitbooks.io/spark/content/shu_ju_ku_suo_yin.html 索引
http://www.nowamagic.net/librarys/veda/detail/1954 主键和索引
https://segmentfault.com/q/1010000000667375 主键或索引是否必须
https://blog.csdn.net/pang040328/article/details/4164874 索引的优缺点
https://blog.csdn.net/zhangsuiwen/article/details/5087154 主键和聚集索引的区别
https://www.zhihu.com/question/36996520 MySQL最左匹配原则
https://segmentfault.com/q/1010000005766771 MySQL中key和index的区别